88.0k views
5 votes
Write and execute a single query that will isplay 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 follwing tables in SQL Server. To create and populate these tables in SQL Server, run the queries that are listed below these tables.CUSTOMERCIDCNameAgeResid_CityBirthPlace1BLACK40ERIETAMPA2GREEN25CARYERIE3JONES30HEMETTAMPA4MARTIN35HEMETTAMPA5SIMON22ERIEERIE6VERNON60CARYCARY7WILSON25DENVERAUSTINIn the CUSTOMER table, CName is the primary key.RENTALSRtnCIDMakeDate_OutPickupDate_returnedReturn_city11FORD10-Oct-2010CARY12-Oct-2010CARY21GM01-Nov-2009TAMPA05-Nov-2009CARY31FORD01-Jan-2009ERIE10-Jan-2009ERIE42NISSAN07-Nov-2010TAMPA53FORD01-Oct-2010CARY31-Oct-2010ERIE63GM01-Aug-2009ERIE05-Aug-2009ERIE74FORD01-Aug-2010CARY12-Aug-2010ERIE85GM01-Sep-2010ERIEIn 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_returne is the date in which the vehicle was returned. If the car has not yet been returned, Date_returned and Return_city are null.RENTCOSTMAKECOSTFORD30GM40NISSAN30TOYOTA20VOLVO50The RENTCOST table stores the rates pe day of each vehicle. The primary key of this table is MAKE, and it is a foreign key in the RENTALS table.create database AutoRentalsgouse AutoRentalsgocreate table Customer(CID integer,CName varchar(20),Age integer,Resid_City varchar(20),BirthPlace varchar(20),Constraint PK_Customer Primary Key (CID))insert Customerselect 1, 'Black', 40, 'Erie', 'Tampa'insert Customerselect 2, 'Green', 25, 'Cary', 'Erie'insert Customerselect 3, 'Jones', 30, 'Hemet', 'Tampa'insert Customerselect 4, 'Martin', 35, 'Hemet', 'Tampa'insert Customerselect 5, 'Simon', 22, 'Erie', 'Erie'insert Customerselect 6, 'Vernon', 60, 'Cary', 'Cary'insert Customerselect 7, 'Wilson', 25, 'Denver', 'Austin'create table Rentcost(Make varchar(20),Cost float,constraint PK_Rentcost Primary Key (Make))insert Rentcostselect 'Ford', 30insert Rentcostselect 'GM', 40insert Rentcostselect 'Nissan', 30insert Rentcostselect 'Toyota', 20insert Rentcostselect 'Volvo', 50Create 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 Rentalsselect 1, 1, 'Ford', '10/10/2010', 'Cary', '10/12/2010', 'Cary'insert Rentalsselect 2, 1, 'GM', '11/1/2009', 'Tampa', '11/5/2009', 'Cary'insert Rentalsselect 3, 1, 'Ford', '1/1/2009', 'Erie', '1/10/2009', 'Erie'insert Rentalsselect 4, 2, 'Nissan', '11/7/2010', 'Tampa', null, nullinsert Rentalsselect 5, 3, 'Ford', '10/1/2010', 'Cary', '10/31/2010', 'Erie'insert Rentalsselect 6, 3, 'GM', '8/1/2009', 'Erie', '8/5/2009', 'Erie'insert Rentalsselect 7, 4, 'Ford', '8/1/2010', 'Cary', '8/12/2010', 'Erie'insert Rentalsselect 8, 5, 'GM', '9/1/2010', 'Erie', null, null

User Benny Mose
by
6.4k points

2 Answers

4 votes

Below is the query that displays all the information from the Customer, Rentals, and Rentcost tables in a single result set, ordered by Customer.CID and Rentcost.Make.

SELECT

C.CID, C.CName, C.Age, C.Resid_City, C.BirthPlace,

R.Rtn, R.Make, R.Date_Out, R.Pickup, R.Date_returned, R.Return_city,

RC.Cost

FROM

Customer C

JOIN

Rentals R ON C.CID = R.CID

JOIN

Rentcost RC ON R.Make = RC.Make

ORDER BY

C.CID ASC, RC.Make ASC;

This query joins the Customer, Rentals, and Rentcost tables using appropriate foreign key relationships, and then selects the required fields from each table.

User Elson Ramos
by
7.0k points
5 votes

Answer:

SELECT DISTINCT Customer.CID , Customer.CName , Customer.Age , Customer.Resid_City , Customer.BirthPlace , Rentals.Rtn, Rentals.data_out. Rentals.pickup, Rentals.data_returned, Rental.return_city, RentCost.make, RentCost.cost

FROM Customer

JOIN Rentals ON Customer.CID = Rentals.CID

JOIN RentCost ON Rentals.make = RentCost.make.

ORDER BY Customer.CID AND RentCost.make

Step-by-step explanation:

The returned output is a 12-field table, in ascending order of the customer's id and the make of the car rented.

User Rythmic
by
7.5k points