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

Deliverables:

Write queries that will satisfy the following requirements. To receive credit for this assignment, all queries must be executed in SQL Server and be displayed appropriately. For each question:

TYPE the SQL query below the instructions.

Take a screenshot of your query that was executed in SQL server. Be sure to capture the query as well as the results in your screen shot.

Paste the screenshot below the typed query.

1. execute a query that will determine the total number of days that Black rented a GM on November 1, 2009.

You should have one cell: 4

2. execute a query that will determine the total cost of the automobile rented by Black on November 1, 2009.

You should have one cell: 160

3. execute a query that will determine the Total cost of all the automobiles that have ever been rented.

You should have one cell: 1880

4. execute a query that will determine the average number of days that automobiles are rented. Show your result broken out by makes. Do not include an automobile if it has not yet been returned.

You should have two rows: Ford and GM with average days rented 13 and 4, respectively

5. execute a query that will determine the average age of customers broken out by the city in which they reside. Note: Make sure that the average age is not truncated to an integer.

You should have 4 rows: Cary, Denver, Erie, and Hemet with average ages of 42.5, 25, 31, and 32.5

6. execute a query that will show a list of customers who reside in the same city in which they were born.

You should have 2 rows: Simon and Vern

7. Using a left outer join, execute a query that will display a unique list of the makes of automobiles that have never been rented.

You should have 2 rows: Toyota and Volvo

8. Using a Type I query, display a unique list of the makes of automobiles that have never been rented.

You should have 2 rows: Toyota and Volvo

9. Using a right outer join, execute a query that will display a unique list of customers who have never rented an automobile.

You should have two rows: Vernon and Wilson

10. Using a Type II query, display a unique list of customers who have never rented an automobile.

You should have two rows: Vernon and Wilson

11. execute a query that will display a list of the customers who picked up their rental from the same city in which they reside.

You should have 2 rows: Black and Simon

12. execute a query that will display a list of customers who have not returned their rentals.

You should have 2 rows: Green and Simon

13. Using a Type I query, show all of the names and ages of customers who have rented an automobile and returned it to Erie. Sort your results in ascending order by customer name.

You should have three rows: Black, Jones, Martin.

14. Using a Type II query, show all of the customers who rented an automobile and picked it up in Cary.

You should have three rows: Black, Jones, and Martin

15. 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.