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

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

In this lab, you will be working with the following tables in SQL Server. To create and populate these tables in SQL Server, run the queries that are listed 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, CName is 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 is the primary key and represents the rental number. CID is a foreign key in the RENTALS table and refers to the CID in CUSTOMER; Pickup is the city where the car was picked up; and Date_Out is the date in which the car was rented out. Return_city is the city where the car was returned. Date_returned is the date in which the 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 the rates per day of each vehicle. The primary key of this table is MAKE, 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) References Customer,

Constraint FK_RentCostRentals Foreign Key (Make) References Rentcost)

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