Please do not spam answer me like other chegg people have. the question is not complicated but does require you to read the SQL table which I have created and supplied to answer the question. (provided below the question) IF you are NOT capable of answering please do not answer it, leave it for someone who can actually help me and not use up 1 of my paid questions.. I really appreciate the last question help from CHEGG EXPERT "Anonymous

QUESTION BELOW *************************************************************************

8. Using OpenRowSet, Write a query that SELECTS the ReservationID (called BookingID in the Thai Database), Guest Last Name, Formatted Quoted Rate*, and Check-in Date from both databases, and presents all the details together, sorted by Guest Last Name.

SQL table information BELOW *************************************************************************

USE Master --this will switch to Master DB

GO

--Exec sp_addlinkedserver

--@server='TITAN',

--@srvproduct='',

--/*this is the product name of the OLE DB data source to add as a linked server.*/

--@provider='MSDASQL',

--@provstr='DRIVER={SQL Server};SERVER=titan.cs.weber.edu,10433;UID=Thai_User;PWD=Thai_Test;Initial Catalog=Thai_HOBS'

IF EXISTS (SELECT * FROM sysdatabases WHERE name = 'Farms')

DROP DATABASE Farms

GO --This will pause the script to make sure everything has happened

CREATE DATABASE Farms

GO

USE Farms

CREATE TABLE RESERVATION

(

ReservationID smallint NOT NULL IDENTITY(5000,1),

ReservationDate date NOT NULL,

ReservationStatus char(1) NOT NULL,

ReservationComments varchar(200) NULL,

CreditCardID smallint NOT NULL

)

CREATE TABLE GUEST

(

GuestID smallint NOT NULL IDENTITY(1500,1),

GuestFirst varchar(20) NOT NULL,

GuestLast varchar(20) NOT NULL,

GuestAddress varchar(30) NOT NULL,

guestAddress2 varchar(10) null,

GuestCity varchar(20) NOT NULL,

GuestState char(2) NULL,

GuestPostalCode char(10) NOT NULL,

GuestCountry varchar(20) NOT NULL,

GuestPhone varchar(20) NULL,

GuestEmail varchar(20) NULL,

GuestComments varchar(200) NULL

)

CREATE TABLE ROOMTYPE

(

RoomTypeID smallint NOT NULL IDENTITY(1,1),

RTDescription varchar(200) NOT NULL

)

CREATE TABLE CREDITCARD

(

CreditCardID smallint NOT NULL IDENTITY(1,1),

GuestID smallint NOT NULL,

CCType varchar(5) NOT NULL,

CCNumber varchar(16) NOT NULL,

CCCompany varchar(40) NULL,

CCCardHolder varchar(40) NOT NULL,

CCExpiration smalldatetime NOT NULL

)

CREATE TABLE DISCOUNT

(

DiscountID smallint NOT NULL IDENTITY(1,1),

DiscountDescription varchar(50) NOT NULL,

DiscountExperiation date NOT NULL,

DiscountRules varchar(100) NULL,

DiscountPercent decimal(4,2) NULL,

DiscountAmount smallmoney NULL

)

CREATE TABLE ROOM

(

RoomID smallint NOT NULL IDENTITY(1,1),

RoomNumber varchar(5) NOT NULL,

RoomDescription varchar(200) NOT NULL,

RoomSmoking bit NOT NULL,

RoomBedConfiguration char(2) NOT NULL,

HotelID smallint NOT NULL,

RoomTypeID smallint NOT NULL

)

CREATE TABLE RACKRATE

(

RackRateID smallint NOT NULL IDENTITY(1,1),

RoomTypeID smallint NOT NULL,

HotelID smallint NOT NULL,

RackRate smallmoney NOT NULL,

RackRateBegin date NOT NULL,

RackRateEnd date NOT NULL,

RackRateDescription varchar(200) NOT NULL

)

CREATE TABLE FOLIO

(

FolioID smallint NOT NULL IDENTITY(1,1),

ReservationID smallint NOT NULL,

GuestID smallint NOT NULL,

RoomID smallint NOT NULL,

QuotedRate smallmoney NOT NULL,

CheckinDate smalldatetime NOT NULL,

Nights tinyint NOT NULL,

FStatus char(1) NOT NULL,

Comments varchar(200) NULL,

DiscountID smallint NOT NULL

)

CREATE TABLE BILLING

(

FolioBillingID smallint NOT NULL IDENTITY (1,1),

FolioId smallint NOT NULL,

BillingCategoryID smallint NOT NULL,

BillingDescription char(30) NOT NULL,

BillingAmount smallmoney NOT NULL,

BillingItemQty tinyint NOT NULL,

BillingItemDate date NOT NULL,

)

CREATE TABLE HOTEL

(

HotelID smallint NOT NULL IDENTITY(2100,100),

HotelName varchar(30) NOT NULL,

HotelAddress varchar(30) NOT NULL,

HotelCity varchar(20) NOT NULL,

HotelState char(2) NULL,

HotelCountry varchar(20) NOT NULL,

HotelPostalCode char(10) NOT NULL,

HotelStarRating char(1) NULL,

HotelPictureLink varchar(100) NULL,

TaxLocationID smallint NOT NULL

)

CREATE TABLE PAYMENT

(

PaymentID smallint NOT NULL IDENTITY(8000,1),

FolioID smallint NOT NULL,

PaymentDate date NOT NULL,

PaymentAmount smallmoney NOT NULL,

PaymentComments varchar(200) NULL

)

CREATE TABLE BILLINGCATEGORY

(

BillingCategoryID smallint NOT NULL IDENTITY(1,1),

BillingCatDescription varchar(30) NOT NULL,

BillingCatTaxable bit NOT NULL

)

CREATE TABLE TAXRATE

(

TaxLocationID smallint NOT NULL IDENTITY(1,1),

TaxDescription varchar(30) NOT NULL,

RoomTaxRate decimal(6,4) NOT NULL,

SalesTaxRate decimal(6,4)

)

--Now lets ensure that all the tables were created correctly by using the GO command

--before altering the tables and adding in the contraints

GO

ALTER TABLE RESERVATION

ADD CONSTRAINT PK_ReservationID

PRIMARY KEY (ReservationID)

ALTER TABLE GUEST

ADD CONSTRAINT PK_GuestID

PRIMARY KEY (GuestID)

ALTER TABLE ROOMTYPE

ADD CONSTRAINT PK_RoomTypeID

PRIMARY KEY(RoomTypeID)

ALTER TABLE CREDITCARD

ADD CONSTRAINT PK_CreditCardID

PRIMARY KEY(CreditCardID)

ALTER TABLE DISCOUNT

ADD CONSTRAINT PK_DiscountID

PRIMARY KEY(DiscountID)

ALTER TABLE ROOM

ADD CONSTRAINT PK_RoomID

PRIMARY KEY(RoomID)

ALTER TABLE RACKRATE

ADD CONSTRAINT PK_RackRateID

PRIMARY KEY(RackRateID )

ALTER TABLE FOLIO

ADD CONSTRAINT PK_FolioID

PRIMARY KEY(FolioID)

ALTER TABLE BILLING

ADD CONSTRAINT PK_FolioBillingID

PRIMARY KEY(FolioBillingID)

ALTER TABLE HOTEL

ADD CONSTRAINT PK_HotelIDCheck

PRIMARY KEY(HotelID)

ALTER TABLE PAYMENT

ADD CONSTRAINT PK_PaymentID

PRIMARY KEY(PaymentID)

ALTER TABLE BILLINGCATEGORY

ADD CONSTRAINT PK_BillingCategoryID

PRIMARY KEY(BillingCategoryID)

ALTER TABLE TAXRATE

ADD CONSTRAINT PK_TaxLocationID

PRIMARY KEY(TaxLocationID)

-- Alter Foreign keys

GO

ALTER TABLE RESERVATION

ADD CONSTRAINT FK_RESERVATION

FOREIGN KEY (CreditCardID) REFERENCES CreditCard(CreditCardID)

ON UPDATE Cascade

ON DELETE Cascade

ALTER TABLE CREDITCARD

ADD CONSTRAINT FK_CREDITCARD

FOREIGN KEY (GuestID) REFERENCES Guest(GuestID)

ON UPDATE Cascade

ON DELETE Cascade

ALTER TABLE ROOM

ADD CONSTRAINT FK_ROOM

FOREIGN KEY (HotelID) REFERENCES HOTEL(HotelID)

ON UPDATE Cascade

ON DELETE Cascade,

CONSTRAINT FK_ROOMTYPEID

FOREIGN KEY (RoomTypeID) REFERENCES ROOMTYPE(RoomTypeID)

ON UPDATE No Action

ON DELETE No Action

ALTER TABLE RACKRATE

ADD CONSTRAINT FK_RACKRATE

FOREIGN KEY (RoomTypeID) REFERENCES ROOMTYPE(RoomTypeID)

ON UPDATE Cascade

ON DELETE Cascade,

CONSTRAINT FK_HOTELID2

FOREIGN KEY (HotelID) REFERENCES HOTEL(HotelID)

ON UPDATE No Action

ON DELETE No Action

ALTER TABLE HOTEL

ADD CONSTRAINT FK_HOTEL2

FOREIGN KEY (TaxLocationID) REFERENCES TAXRATE(TaxLocationID)

ON UPDATE Cascade

ON DELETE Cascade

ALTER TABLE BILLING

ADD CONSTRAINT FK_BILLING

FOREIGN KEY (FolioID) REFERENCES FOLIO(FolioID)

ON UPDATE Cascade

ON DELETE Cascade,

CONSTRAINT FK_Billingcat

FOREIGN KEY (BillingCategoryID) REFERENCES BILLINGCATEGORY(BillingCategoryID)

ON UPDATE No Action

ON DELETE No Action

ALTER TABLE FOLIO

ADD CONSTRAINT FK_BILLINGIt

FOREIGN KEY (ReservationID) REFERENCES RESERVATION(ReservationID)

ON UPDATE Cascade

ON DELETE Cascade,

CONSTRAINT FK_ROOMIEID

FOREIGN KEY (RoomID) REFERENCES ROOM(RoomID)

ON UPDATE No Action

ON DELETE No Action,

CONSTRAINT FK_THEDISCOUNT

FOREIGN KEY (DiscountID) REFERENCES DISCOUNT(DiscountID)

ON UPDATE No Action

ON DELETE No Action

ALTER TABLE PAYMENT

ADD CONSTRAINT FK_PAYFORIT

FOREIGN KEY (FolioID) REFERENCES FOLIO(FolioID)

ON UPDATE Cascade

ON DELETE Cascade

BULK INSERT RESERVATION FROM 'c:\stage\farms1-1\Reservation.txt' WITH (FIELDTERMINATOR = '|', FIRSTROW =1)

BULK INSERT GUEST FROM 'c:\stage\farms1-1\Guest.txt' WITH (FIELDTERMINATOR = '|', FIRSTROW =1)

BULK INSERT CREDITCARD FROM 'c:\stage\farms1-1\CreditCard.txt' WITH (FIELDTERMINATOR = '|', FIRSTROW =1)

BULK INSERT ROOMTYPE FROM 'c:\stage\farms1-1\RoomType.txt' WITH (FIELDTERMINATOR = '|', FIRSTROW =1)

BULK INSERT DISCOUNT FROM 'c:\stage\farms1-1\Discount.txt' WITH (FIELDTERMINATOR = '|', FIRSTROW =1)

BULK INSERT ROOM FROM 'c:\stage\farms1-1\Room.txt' WITH (FIELDTERMINATOR = '|', FIRSTROW =1)

BULK INSERT RACKRATE FROM 'c:\stage\farms1-1\RackRate.txt' WITH (FIELDTERMINATOR = '|', FIRSTROW =1)

BULK INSERT FOLIO FROM 'c:\stage\farms1-1\Folio.txt' WITH (FIELDTERMINATOR = '|', FIRSTROW =1)

BULK INSERT BILLING FROM 'c:\stage\farms1-1\Billing.txt' WITH (FIELDTERMINATOR = '|', FIRSTROW =1)

BULK INSERT HOTEL FROM 'c:\stage\farms1-1\Hotel.txt' WITH (FIELDTERMINATOR = '|', FIRSTROW =1)

BULK INSERT PAYMENT FROM 'c:\stage\farms1-1\Payment.txt' WITH (FIELDTERMINATOR = '|', FIRSTROW =1)

BULK INSERT BILLINGCATEGORY FROM 'c:\stage\farms1-1\BillingCategory.txt' WITH (FIELDTERMINATOR = '|', FIRSTROW =1)

BULK INSERT TAXRATE FROM 'c:\stage\farms1-1\TaxRate.txt' WITH (FIELDTERMINATOR = '|', FIRSTROW =1)

Exec sp_serveroption 'TITAN', 'data access', 'true'

Exec sp_serveroption 'TITAN', 'rpc', 'true'--enables from the REMOTE to LOCAL server

Exec sp_serveroption 'TITAN', 'rpc out', 'true' -- enables from the LOCAL to REMOTE server

Exec sp_serveroption 'TITAN', 'collation compatible', 'true'

Exec sp_addlinkedsrvlogin 'TITAN', 'true'

go

sp_configure 'show advanced options', 1;

RECONFIGURE;

GO

sp_configure 'Ad Hoc Distributed Queries', 1;

RECONFIGURE;

GO

-----------------------------------------------------------------------------------------------------------------------------

--5

--SELECT * FROM Hotels

--SELECT * FROM Bookings

--SELECT * FROM Guests

--SELECT * FROM Rooms

--SELECT * FROM TaxRates

SELECT *

FROM OPENROWSET('MSDASQL', 'DRIVER={SQL Server};SERVER=titan.cs.weber.edu,10433;UID=Thai_User;PWD=Thai_Test', 'Select * From Thai_HOBS.dbo.Hotels')

Go

SELECT *

FROM OPENROWSET('MSDASQL', 'DRIVER={SQL Server};SERVER=titan.cs.weber.edu,10433;UID=Thai_User;PWD=Thai_Test', 'Select * From Thai_HOBS.dbo.Bookings')

Go

SELECT *

FROM OPENROWSET('MSDASQL', 'DRIVER={SQL Server};SERVER=titan.cs.weber.edu,10433;UID=Thai_User;PWD=Thai_Test', 'Select * From Thai_HOBS.dbo.Guests')

Go

SELECT *

FROM OPENROWSET('MSDASQL', 'DRIVER={SQL Server};SERVER=titan.cs.weber.edu,10433;UID=Thai_User;PWD=Thai_Test', 'Select * From Thai_HOBS.dbo.Rooms')

Go

SELECT *

FROM OPENROWSET('MSDASQL', 'DRIVER={SQL Server};SERVER=titan.cs.weber.edu,10433;UID=Thai_User;PWD=Thai_Test', 'Select * From Thai_HOBS.dbo.TaxRates')

Go

----------------------------------------------------------------------------------------------------------------------------------

--6A. Continue your script by USING YOUR DATABASE (not Master) and creating a modified version of the stored procedure

-- (sp_InsertGuest) you created in HW 6. YOU MUST RENAME this modified stored procedure to sp_InsertGuest_YOURLASTNAME

-- (because I will be adding several student stored procedures on MY computer and they cannot all have the same name).

-- This modified stored procedure should INSERT INTO the Thai_HOBS database, guest table (using Linked Server),

-- instead of your own FARMS database .

GO

IF EXISTS(SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE SPECIFIC_NAME = 'sp_InsertGues')

DROP PROCEDURE sp_InsertGuest;

GO

--CREATE THE PROCEDURE

CREATE PROCEDURE sp_InsertGuest

@GuestFirst varchar(20)

, @GuestLast varchar(20)

, @GuestAddress varchar(30)

, @GuestCity varchar(20)

, @GuestPostalCode char(10)

AS

Begin

INSERT INTO [TITAN].[THAI_HOBS].[dbo].[Guests](GuestFirstName,GuestLastName,GuestAddress,GuestCity,GuestPostalCode)

VALUES(@GuestFirst,@GuestLast, @GuestAddress, @GuestCity,@GuestPostalCode)

END

GO

DECLARE @NewGuestID smallint;

--6B. Demonstrate this stored procedure by executing it, and adding yourself as a guest to the Thai_HOBS database. Note:

-- You may need to set RPC IN/OUT on your linked server (I've already done it on Thai_HOBS).

--EXEC sp_InsertGuest

-- @GuestFirst = 'jason'

-- ,@GuestLast = 'carter'

-- , @GuestAddress = '123'

-- , @GuestCity = 'Hawaii'

-- , @GuestPostalCode = '90210';

--GO

SELECT * FROM OPENROWSET('MSDASQL', 'DRIVER={SQL Server};SERVER=titan.cs.weber.edu,10433;UID=Thai_User;PWD=Thai_Test', 'Select * From Thai_HOBS.dbo.Guests')

Go

--------------------------------------------------------------------------------------------------------------------------------------------------------

--7. Using OpenQuery, Write a statement that SELECTS information containing the Property (or Hotel) Name, Room (or Unit) Type, and the

--formatted and converted ($*) rate for all the rooms from each hotel in BOTH databases (Hint: Simple UNION to group two separate query

-- results together and sort by Property / Hotel Name). Since Thai_HOBS doesn't use dates for their rates, do not include them when you

-- do the FARMS Rates (or else union will not work, because the number of columns will be off). Rather, just filter on the rates using an

--arbitrary date like '1 July 2017'.

SELECT * FROM OPENROWSET('MSDASQL', 'DRIVER={SQL Server};SERVER=titan.cs.weber.edu,10433;UID=Thai_User;PWD=Thai_Test', 'Select * From Thai_HOBS.dbo.Hotels')

SELECT * FROM OPENROWSET('MSDASQL', 'DRIVER={SQL Server};SERVER=titan.cs.weber.edu,10433;UID=Thai_User;PWD=Thai_Test', 'Select * From Thai_HOBS.dbo.ROOMS')

EXEC sp_addlinkedsrvlogin

@rmtsrvname = 'TITAN',

@useself='true',

@locallogin='DESKTOP-THDGK1R\k',

@rmtuser='Thai_User',

@rmtpassword='Thai_Test'