Menu
support@nursinghomework.org
+1 714-215-3097

(Solved) : Write Execute Single Query Display Information Customer Rentals Rentcost Tables Single Res Q35541871

Write and execute a single query that will displayall of the information in the Customer, Rentals,and Rentcost tables in a single resultset.   Be sure todisplay each field only once in your output. Order your results inascending order by Customer.CID and Rentcost.Make .

You should have 12 rows and 12 columns in your result.

In this lab, you will beworking with the following tables in SQL Server. To create andpopulate these tables in SQL Server, run the queries that arelisted below these tables.

CUSTOMER

CID

CName

Age

Resid_City

BirthPlace

1

BLACK

40

ERIE

TAMPA

2

GREEN

25

CARY

ERIE

3

JONES

30

HEMET

TAMPA

4

MARTIN

35

HEMET

TAMPA

5

SIMON

22

ERIE

ERIE

6

VERNON

60

CARY

CARY

7

WILSON

25

DENVER

AUSTIN

In the CUSTOMER table, CNameis the primary key.

RENTALS

Rtn

CID

Make

Date_Out

Pickup

Date_returned

Return_city

1

1

FORD

10-Oct-2010

CARY

12-Oct-2010

CARY

2

1

GM

01-Nov-2009

TAMPA

05-Nov-2009

CARY

3

1

FORD

01-Jan-2009

ERIE

10-Jan-2009

ERIE

4

2

NISSAN

07-Nov-2010

TAMPA

5

3

FORD

01-Oct-2010

CARY

31-Oct-2010

ERIE

6

3

GM

01-Aug-2009

ERIE

05-Aug-2009

ERIE

7

4

FORD

01-Aug-2010

CARY

12-Aug-2010

ERIE

8

5

GM

01-Sep-2010

ERIE

In the table RENTALS, Rtn isthe primary key and represents the rental number. CID is a foreignkey in the RENTALS table and refers to the CID in CUSTOMER; Pickupis the city where the car was picked up; and Date_Out is the datein which the car was rented out.   Return_city is thecity where the car was returned. Date_returned is the date in whichthe vehicle was returned. If the car has not yet been returned,Date_returned and Return_city are null.

RENTCOST

MAKE

COST

FORD

30

GM

40

NISSAN

30

TOYOTA

20

VOLVO

50

The RENTCOST table stores therates per day of each vehicle. The primary key of this table isMAKE, and it is a foreign key in the RENTALS table.

create database AutoRentals

go

use AutoRentals

go

create table Customer

(CID integer,

CName varchar(20),

Age integer,

Resid_City varchar(20),

BirthPlace varchar(20),

Constraint PK_Customer Primary Key (CID))

insert Customer

select 1, ‘Black’, 40, ‘Erie’, ‘Tampa’

insert Customer

select 2, ‘Green’, 25, ‘Cary’, ‘Erie’

insert Customer

select 3, ‘Jones’, 30, ‘Hemet’, ‘Tampa’

insert Customer

select 4, ‘Martin’, 35, ‘Hemet’, ‘Tampa’

insert Customer

select 5, ‘Simon’, 22, ‘Erie’, ‘Erie’

insert Customer

select 6, ‘Vernon’, 60, ‘Cary’, ‘Cary’

insert Customer

select 7, ‘Wilson’, 25, ‘Denver’, ‘Austin’

create table Rentcost

(Make varchar(20),

Cost float,

constraint PK_Rentcost Primary Key (Make))

insert Rentcost

select ‘Ford’, 30

insert Rentcost

select ‘GM’, 40

insert Rentcost

select ‘Nissan’, 30

insert Rentcost

select ‘Toyota’, 20

insert Rentcost

select ‘Volvo’, 50

Create table Rentals

(Rtn integer,

CID integer,

Make varchar(20),

Date_Out smalldatetime,

Pickup varchar(20),

Date_returned smalldatetime,

Return_city varchar(20),

Constraint PK_Rentals Primary Key (Rtn),

Constraint FK_CustomerRentals Foreign Key (CID) ReferencesCustomer,

Constraint FK_RentCostRentals Foreign Key (Make) ReferencesRentcost)

insert Rentals

select 1, 1, ‘Ford’, ’10/10/2010′, ‘Cary’, ’10/12/2010′,’Cary’

insert Rentals

select 2, 1, ‘GM’, ’11/1/2009′, ‘Tampa’, ’11/5/2009′, ‘Cary’

insert Rentals

select 3, 1, ‘Ford’, ‘1/1/2009’, ‘Erie’, ‘1/10/2009’, ‘Erie’

insert Rentals

select 4, 2, ‘Nissan’, ’11/7/2010′, ‘Tampa’, null, null

insert Rentals

select 5, 3, ‘Ford’, ’10/1/2010′, ‘Cary’, ’10/31/2010′,’Erie’

insert Rentals

select 6, 3, ‘GM’, ‘8/1/2009’, ‘Erie’, ‘8/5/2009’, ‘Erie’

insert Rentals

select 7, 4, ‘Ford’, ‘8/1/2010’, ‘Cary’, ‘8/12/2010’, ‘Erie’

insert Rentals

select 8, 5, ‘GM’, ‘9/1/2010’, ‘Erie’, null, null

Leave a Reply

Your email address will not be published. Required fields are marked *