BMIS 325 Quiz 3

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

select distinct c.cname from customer c where not exists(select * from rentals r where r.cname = c.cname)

List the customer number, the name (first and last), and the balance of customers who reside in Colorado (CustState is CO).

SELECT CustNo, CustFirstName, CustLastName, CustBal FROM Customer WHERE CustState = 'CO'

List the order number, order date, employee number, and employee name (first and last) of orders placed on January 23, 2007. List the order even if there is not an associated employee.

Access SQL SELECT OrdNo, OrdDate, Employee.EmpNo, EmpFirstName, EmpLastName FROM OrderTbl LEFT JOIN Employee ON OrderTbl.EmpNo = Employee.EmpNo WHERE OrdDate = #1/23/2007#

List all columns of the Product table that contain the words Ink Jet in the product name.

Access SQL solution: SELECT * FROM Product WHERE ProdName LIKE '*Ink Jet*'

List the order number, order date, employee number, employee name (first and last), customer number, and customer name (first and last) of orders placed on January 23, 2007. List the order even if there is not an associated employee.

Access SQL SELECT OrdNo, OrdDate, Employee.EmpNo, EmpFirstName, EmpLastName, Customer.CustNo, CustFirstName, CustLastName FROM ( OrderTbl LEFT JOIN Employee ON OrderTbl.EmpNo = Employee.EmpNo ) INNER JOIN Customer ON Customer.CustNo = OrderTbl.CustNo WHERE OrdDate = #1/23/2007#

List the union of customers and order recipients. Include the name, street, city, state, and zip in the result. You need to use the concatenation function to combine the first and last names so that they can be compared to the order recipient name. In Access SQL, the & symbol is the concatenation function. In Oracle SQL, the || symbol is the concatenation function.

Access SQL Solution SELECT CustFirstName & ' ' & CustLastName AS PersonName, CustStreet AS Street, CustCity AS City, CustState AS State, CustZip AS Zip FROM Customer UNION SELECT OrdName AS PersonName, OrdStreet AS Street, OrdCity AS City, OrdState AS State, OrdZip AS Zip FROM OrderTbl

4 24 List the customer number, customer name (first and last), the sum of the quantity of products ordered, and the total order amount (sum of the product price times the quantity) for orders placed in January 2007. Only include products in which the product name contains the string Ink Jet or Laser. Only include customers who have ordered more than two Ink Jet or Laser products in January 2007.

Access SQL Solution SELECT Customer.CustNo, CustFirstName, CustLastName, SUM(Qty) AS ProdQty, SUM(Qty*ProdPrice) AS TotOrdAmt FROM OrderTbl, OrdLine, Product, Customer WHERE OrdDate BETWEEN #1/1/2007# AND #1/31/2007# AND OrderTbl.OrdNo = OrdLine.OrdNo AND OrdLine.ProdNo = Product.ProdNo AND Customer.CustNo = OrderTbl.CustNo AND (ProdName LIKE '*Ink Jet*' OR ProdName LIKE '*Laser*') GROUP BY Customer.CustNo, CustFirstName, CustLastName HAVING SUM(Qty) > 2

4 35 For Colorado customers, compute the number of orders placed in January 2007. The result should include the customer number, last name, and number of orders placed in January 2007.

Access SQL Solution SELECT Customer.CustNo, CustLastName, COUNT(*) AS NumOrders FROM OrderTbl, Customer WHERE OrdDate BETWEEN #1/1/2007# AND #1/31/2007# AND Customer.CustNo = OrderTbl.CustNo GROUP BY Customer.CustNo, CustLastName

4 36 For Colorado customers, compute the number of orders placed in January 2007 in which the orders contain products made by Connex. The result should include the customer number, last name, and number of orders placed in January 2007.

Access SQL Solution SELECT Customer.CustNo, CustLastName, COUNT(*) AS NumOrders FROM OrderTbl, Customer, OrdLine, Product WHERE OrdDate BETWEEN #1/1/2007# AND #1/31/2007# AND OrderTbl.OrdNo = OrdLine.OrdNo AND OrdLine.ProdNo = Product.ProdNo AND Customer.CustNo = OrderTbl.CustNo AND ProdMfg = 'Connex' GROUP BY Customer.CustNo, CustLastName

Using the join operator style, list the product name and the price of all products ordered by Beth Taylor in January 2007. Remove duplicate rows from the result.

Access SQL Solution SELECT DISTINCT ProdName, ProdPrice FROM ( ( OrdLine INNER JOIN OrderTbl ON OrdLine.OrdNo = OrderTbl.OrdNo ) INNER JOIN Customer ON Customer.CustNo = OrderTbl.CustNo ) INNER JOIN Product ON OrdLine.ProdNo = Product.ProdNo WHERE OrdDate BETWEEN #1/1/2007# AND #1/31/2007# AND CustFirstName = 'Beth' AND CustLastName = 'Taylor'

4 28 List the employee number, the employee name (first and last), and total amount of commissions on orders taken in January 2007. The amount of a commission is the sum of the dollar amount of products ordered times the commission rate of the employee.

Access SQL Solution SELECT Employee.EmpNo, EmpFirstName, EmpLastName, SUM(EmpCommRate*Qty*ProdPrice) AS TotCommAmt FROM Employee, OrderTbl, OrdLine, Product WHERE OrderTbl.EmpNo = Employee.EmpNo AND OrderTbl.OrdNo = OrdLine.OrdNo AND OrdLine.ProdNo = Product.ProdNo AND OrdDate BETWEEN #1/1/2007# AND #1/31/2007# GROUP BY Employee.EmpNo, EmpFirstName, EmpLastName

4 37 For each employee with a commission rate of less than 0.04, compute the number of orders taken in January 2007. The result should include the employee number, employee last name, and number of orders taken.

Access SQL Solution SELECT Employee.EmpNo, EmpLastName, COUNT(*) AS NumOrders FROM OrderTbl, Employee WHERE OrdDate BETWEEN #1/1/2007# AND #1/31/2007# AND Employee.EmpNo = Employee.EmpNo AND EmpCommRate < 0.04 GROUP BY Employee.EmpNo, EmpLastName

4 23 List the order number, order date, customer name (first and last), and total amount for orders placed on January 23, 2007. The total amount of an order is the sum of the quantity times the product price of each product on the order.

Access SQL Solution SELECT OrderTbl.OrdNo, OrdDate, CustFirstName, CustLastName, SUM(Qty*ProdPrice) AS TotOrdAmt FROM OrderTbl, OrdLine, Product, Customer WHERE OrdDate = #1/23/2007# AND OrderTbl.OrdNo = OrdLine.OrdNo AND OrdLine.ProdNo = Product.ProdNo AND Customer.CustNo = OrderTbl.CustNo GROUP BY OrderTbl.OrdNo, OrdDate, CustFirstName, CustLastName

List the order number and total amount for orders placed on January 23, 2007. The total amount of an order is the sum of the quantity times the product price of each product on the order.

Access SQL Solution SELECT OrderTbl.OrdNo, SUM(Qty*ProdPrice) AS TotOrdAmt FROM OrderTbl, OrdLine, Product WHERE OrdDate = #1/23/2007# AND OrderTbl.OrdNo = OrdLine.OrdNo AND OrdLine.ProdNo = Product.ProdNo GROUP BY OrderTbl.OrdNo

4 25 List the product number, product name, sum of the quantity of products ordered, and total order amount (sum of the product price times the quantity) for orders placed in January 2007. Only include products that have more than five products ordered in January 2007. Sort the result in descending order of the total amount.

Access SQL Solution SELECT Product.ProdNo, ProdName, SUM(Qty) AS ProdQty, SUM(Qty*ProdPrice) AS TotOrdAmt FROM OrderTbl, OrdLine, Product WHERE OrdDate BETWEEN #1/1/2007# AND #1/31/2007# AND OrderTbl.OrdNo = OrdLine.OrdNo AND OrdLine.ProdNo = Product.ProdNo GROUP BY Product.ProdNo, ProdName HAVING SUM(Qty) > 5 ORDER BY SUM(Qty*ProdPrice) DESC

List all columns of the OrderTbl table for Internet orders placed in January 2007. An Internet order does not have an associated employee.

Access SQL solution: SELECT * FROM OrderTbl WHERE OrdDate BETWEEN #1/1/2007# AND #1/31/2007# AND EmpNo IS NULL

List all columns of the OrderTbl table for phone orders placed in February 2007. A phone order has an associated employee.

Access SQL solution: SELECT * FROM OrderTbl WHERE OrdDate BETWEEN #2/1/2007# AND #2/28/2007# AND EmpNo IS NOT NULL

List the average balance of customers by city and short zip code (the first five digits of the zip code). Include only customers residing in Washington State (WA). In Microsoft Access, the expression left(CustZip, 5) returns the first five digits of the zip code. In Oracle, the expression substr(CustZip, 1, 5) returns the first five digits.

Access SQL solution: SELECT CustCity, left(CustZip, 5) AS ShortZip, AVG(CustBal) AS AvgBal FROM Customer WHERE CustState = 'WA' GROUP BY CustCity, Left(CustZip, 5)

List the customer number, name (first and last), order number, order date, employee number, employee name (first and last), product number, product name, and order cost (OrdLine.Qty * ProdPrice) for products ordered on January 23, 2007, in which the order cost exceeds $150.

Access SQL solution: SELECT Customer.CustNo, CustFirstName, CustLastName, OrderTbl.OrdNo, OrdDate, Employee.EmpNo, EmpFirstName, EmpLastName, Product.ProdNo, ProdName, ProdPrice*Qty AS OrderCost FROM OrderTbl, OrdLine, Product, Customer, Employee WHERE OrdDate = #1/23/2007# AND ProdPrice*Qty > 150 AND OrderTbl.OrdNo = OrdLine.OrdNo AND OrdLine.ProdNo = Product.ProdNo AND OrderTbl.CustNo = Customer.CustNo AND Employee.EmpNo = OrderTbl.EmpNo

4 13 List the customer number, name (first and last), and balance of Washington customers who have placed one or more orders in February 2007. Remove duplicate rows from the result.

Access SQL solution: SELECT DISTINCT Customer.CustNo, CustFirstName, CustLastName, CustBal FROM OrderTbl, Customer WHERE CustState = 'WA' AND OrdDate BETWEEN #2/1/2007# AND #2/28/2007# AND OrderTbl.CustNo = Customer.CustNo

4 15 List the employee number, name (first and last), and phone of employees who have taken orders in January 2007 from customers with balances greater than $300. Remove duplicate rows in the result.

Access SQL solution: SELECT DISTINCT Employee.EmpNo, EmpFirstName, EmpLastName, EmpPhone FROM OrderTbl, Customer, Employee WHERE CustBal > 300 AND OrdDate BETWEEN #1/1/2007# AND #1/31/2007# AND OrderTbl.CustNo = Customer.CustNo AND OrderTbl.EmpNo = Employee.EmpNo

4 16 List the product number, name, and price of products ordered by customer number C0954327 in January 2007. Remove duplicate products in the result.

Access SQL solution: SELECT DISTINCT Product.ProdNo, ProdName, ProdPrice FROM OrderTbl, OrdLine, Product WHERE CustNo = 'C0954327' AND OrdDate BETWEEN #1/1/2007# AND #1/31/2007# AND OrderTbl.OrdNo = OrdLine.OrdNo AND OrdLine.ProdNo = Product.ProdNo

List the order number, order date, and customer number of orders placed after January 23, 2007, shipped to Washington recipients.

Access SQL solution: SELECT OrdNo, OrdDate, CustNo FROM OrderTbl WHERE OrdState = 'WA' AND OrdDate > #1/23/2007#

List the order number, order date, customer number, and customer name (first and last) of orders placed in January 2007 sent to Colorado recipients.

Access SQL solution: SELECT OrdNo, OrdDate, Customer.CustNo, CustFirstName, CustLastName FROM OrderTbl, Customer WHERE OrdState = 'CO' AND OrdDate BETWEEN #1/1/2007# AND #1/31/2007# AND OrderTbl.CustNo = Customer.CustNo

List the order number, order date, customer number, and customer name (first and last) of orders placed in January 2007 placed by Colorado customers (CustState) but sent to Washington recipients (OrdState).

Access SQL solution: SELECT OrdNo, OrdDate, Customer.CustNo, CustFirstName, CustLastName FROM OrderTbl, Customer WHERE OrdState = 'WA' AND OrdDate BETWEEN #1/1/2007# AND #1/31/2007# AND OrderTbl.CustNo = Customer.CustNo AND CustState = 'CO'

List the order number, order date, customer number, customer name (first and last), employee number, and employee name (first and last) of January 2007 orders placed by Colorado customers.

Access SQL solution: SELECT OrdNo, OrdDate, Customer.CustNo, CustFirstName, CustLastName, Employee.EmpNo, EmpFirstName, EmpLastName FROM OrderTbl, Customer, Employee WHERE CustState = 'CO' AND OrdDate BETWEEN #1/1/2007# AND #1/31/2007# AND OrderTbl.CustNo = Customer.CustNo AND OrderTbl.EmpNo = Employee.EmpNo

Using a Type I nested query, list the customer number, name (first and last), and city of each customer who has a balance greater than $150 and placed an order in February 2007.

Access SQL: SELECT CustNo, CustFirstName, CustLastName, CustCity FROM Customer WHERE CustBal > 150 AND CustNo IN ( SELECT CustNo FROM OrderTbl WHERE OrdDate BETWEEN #2/1/2007# AND #2/28/2007# )

Using a Type II nested query, list the customer number, name (first and last), and city of each customer who has a balance greater than $150 and placed an order in February 2007.

Access SQL: SELECT CustNo, CustFirstName, CustLastName, CustCity FROM Customer WHERE CustBal > 150 AND EXISTS ( SELECT CustNo FROM OrderTbl WHERE OrdDate BETWEEN #2/1/2007# AND #2/28/2007# AND Customer.CustNo = OrderTbl.CustNo )

Using a Type II nested query, list the customer number and name of Colorado customers who have not placed orders in February 2007.

Access SQL: SELECT Customer.CustNo, CustFirstName, CustLastName FROM Customer WHERE CustState = 'CO' AND NOT EXISTS ( SELECT * FROM OrderTbl WHERE OrdDate BETWEEN #2/1/2007# AND #2/28/2007# AND OrderTbl.CustNo = Customer.CustNo )

Repeat problem 9 using a Type I nested query with a NOT IN condition instead of a nested query. If the problem cannot be formulated in this manner, provide an explanation indicating the reason.

Access SQL: SELECT Customer.CustNo, CustFirstName, CustLastName FROM Customer WHERE CustState = 'CO' AND CustNo NOT IN ( SELECT CustNo FROM OrderTbl WHERE OrdDate BETWEEN #2/1/2007# AND #2/28/2007# )

9 14 Repeat problem 13 using a Type I nested query with a NOT IN condition instead of a nested query. If the problem cannot be formulated in this manner, provide an explanation indicating the reason. (Hint: you need to think carefully about the effect of null values in the OrderTbl.EmpNo column, i

Access SQL: SELECT EmpNo, EmpLastName, EmpFirstName FROM Employee WHERE EmpPhone LIKE '(720)*' AND EmpNo NOT IN ( SELECT EmpNo FROM OrderTbl WHERE EmpNo IS NOT NULL)

9 13 Repeat problem 9 using a one-sided outer join and an IS NULL condition. If the problem cannot be formulated in this manner, provide an explanation indicating the reason.

Access SQL: SELECT EmpNo, EmpLastName, EmpFirstName FROM Employee WHERE EmpPhone LIKE '(720)*' AND NOT EXISTS ( SELECT * FROM OrderTbl WHERE Employee.EmpNo = OrderTbl.EmpNo )

9 8 List all the people in the database. The resulting table should have all columns of the Customer. and Employee tables. Match the Customer and Employee tables on first and last names. If a customer does not match any employees, the columns pertaining to the Employee table will be blank. Similarly for an employee who does not match any customers, the columns pertaining to the Customer table will be blank.

Access SQL: SELECT OrderTbl.OrdNo, OrdDate, Employee.EmpNo, EmpFirstName, EmpLastName, Customer.CustNo, CustFirstName, CustLastName, OrdLine.Qty, Product.ProdNo, ProdName FROM ( ( ( OrderTbl LEFT JOIN Employee ON OrderTbl.EmpNo = Employee.EmpNo) INNER JOIN Customer ON Customer.CustNo = OrderTbl.CustNo ) INNER JOIN OrdLine ON OrderTbl.OrdNo = OrdLine.OrdNo ) INNER JOIN Product ON OrdLine.ProdNo = Product.ProdNo WHERE OrdDate BETWEEN #1/1/2007# AND #1/31/2007# AND ProdName LIKE '*Ink Jet*'

Using two Type I nested queries, list the product number, the name, and the price of products with a price greater than $150 that were ordered on January 23, 2007.

Access SQL: SELECT ProdNo, ProdName, ProdPrice FROM Product WHERE ProdPrice > 150 AND ProdNo IN ( SELECT ProdNo FROM OrdLine WHERE OrdNo IN ( SELECT OrdNo FROM OrderTbl WHERE OrdDate = #1/23/2007# ) )

Using two Type I nested queries and another join style, list the product number, name, and price of products with a price greater than $150 that were ordered in January 2007 by customers with balances greater than $400.

Access SQL: SELECT ProdNo, ProdName, ProdPrice FROM Product WHERE ProdPrice > 150 AND ProdNo IN ( SELECT ProdNo FROM OrdLine WHERE OrdNo IN ( SELECT OrderTbl.OrdNo FROM OrderTbl, Customer WHERE OrdDate BETWEEN #1/1/2007# AND #1/31/2007# AND OrderTbl.CustNo = Customer.CustNo AND CustBal > 400 ) )

9 9 For each Ink Jet product ordered in January 2007, list the order number, order date, customer number, customer name (first and last), employee number (if present), employee name (first and last), quantity ordered, product number, and product name. Include products containing Ink Jet in the product name. Include both Internet (no employee) and phone orders (taken by an employee).

Access SQL: SELECT Customer.*, Employee.* FROM Customer LEFT JOIN Employee ON Customer.CustFirstName = Employee.EmpFirstName AND Customer.CustLastName = Employee.EmpLastName UNION SELECT Customer.*, Employee.* FROM Customer RIGHT JOIN Employee ON Customer.CustFirstName = Employee.EmpFirstName AND Customer.CustLastName = Employee.EmpLastName

4 21 List the number of unique short zip codes and average customer balance by city. Only include customers residing in Washington State (WA). Eliminate cities in the result in which the average balance is less than $100. In Microsoft Access, the expression left(CustZip, 5) returns the first five digits of the zip code. In Oracle, the expression substr(CustZip, 1, 5) returns the first five digits. (Note: this problem requires two SELECT statements in Access SQL or a nested query in the FROM clause—see Chapter 9).

Oracle SQL solution: SELECT CustCity, COUNT(DISTINCT substr(CustZip, 1, 5)) AS NumShortZips, AVG(CustBal) AS AvgBal FROM Customer WHERE CustState = 'WA' GROUP BY CustCity HAVING AVG(CustBal) > 100

List all columns of the Product table for products costing more than $50. Order the result by product manufacturer (ProdMfg) and product name.

SELECT * FROM Product WHERE ProdPrice > 50 ORDER BY ProdMfg, ProdName

List the customer number, the name (first and last), and the balance of customers.

SELECT CustNo, CustFirstName, CustLastName, CustBal FROM Customer

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

SELECT CName FROM Customer WHERE EXISTS (SELECT CName FROM Rentals WHERE Customer.CName = Rentals.CName AND Pickup = 'CARY')

List the average balance and number of customers by city. Only include customers residing in Washington State (WA). Eliminate cities in the result with less than two customers.

SELECT CustCity, AVG(CustBal) AS AvgBal, COUNT(*) AS NumCustomers FROM Customer WHERE CustState = 'WA' GROUP BY CustCity HAVING COUNT(*) > 1

4 30 List the first and last name of customers who have the same name (first and last) as an employee.

SELECT CustFirstName, CustLastName FROM Customer, Employee WHERE CustFirstName = EmpFirstName AND CustLastName = EmpLastName

List the average balance of customers by city. Include only customers residing in Washington State (WA).

SELECT CustCity, AVG(CustBal) AS AvgBal FROM Customer WHERE CustState = 'WA' GROUP BY CustCity

List the customer number, the name (first and last), the city, and the balance of customers who reside in Denver with a balance greater than $ 150 or who reside in Seattle with a balance greater than $300.

SELECT CustNo, CustFirstName, CustLastName, CustCity, CustBal FROM Customer WHERE (CustCity = 'Denver' AND CustBal > 150) OR (CustCity = 'Seattle' AND CustBal > 200)

9 12 Repeat problem 9 using the MINUS keyword. Note that Access does not support the MINUS keyword. If the problem cannot be formulated in this manner, provide an explanation indicating the reason.

SELECT Customer.CustNo, CustFirstName, CustLastName FROM Customer WHERE CustState = 'CO' MINUS SELECT Customer.CustNo, CustFirstName, CustLastName FROM Customer WHERE CustState = 'CO' AND CustNo IN ( SELECT CustNo FROM OrderTbl WHERE OrdDate BETWEEN '1-Feb-2007' AND '28-Feb-2007' );

Using a mix of the join operator and the cross product styles, list the names (first and last) of customers who have placed orders taken by Amy Tang. Remove duplicate rows in the result. Note that the join operator style is supported only in Oracle versions 9i and beyond.

SELECT DISTINCT CustFirstName, CustLastName FROM Employee INNER JOIN OrderTbl ON Employee.EmpNo = OrderTbl.EmpNo, Customer WHERE Customer.CustNo = OrderTbl.CustNo AND EmpFirstName = 'Amy' AND EmpLastName = 'Tang'

List the cities and states where orders have been placed. Remove duplicates from the result.

SELECT DISTINCT OrdCity, OrdState FROM OrderTbl

Using a Type 1 query, show all of the customers who have rented a make of car that has ever been returned to Erie

SELECT DISTINCT c.Cname, r.Return_city FROM Customer c, Rentals r WHERE c.CName = r.CName and Make IN (SELECT DISTINCT Make FROM Rentals r1 WHERE r1.Return_city = 'ERIE')

4 27 List the employee number, the employee name (first and last), the commission rate, the supervising employee name (first and last), and the commission rate of the supervisor.

SELECT E.EmpNo, E.EmpFirstName, E.EmpLastName, E.EmpCommRate, Supr.EmpFirstName, Supr.EmpLastName, Supr.EmpCommRate FROM Employee E, Employee Supr WHERE E.SupEmpNo = Supr.EmpNo

List the order number, order date, customer number, and customer name (first and last) of orders placed in January 2007 sent to Colorado recipients.

SELECT OrdNo, OrdDate, Customer.CustNo, CustFirstName, CustLastName FROM OrderTbl INNER JOIN Customer ON OrderTbl.CustNo = Customer.CustNo WHERE OrdState = 'CO' AND OrdDate BETWEEN #1/1/2007# AND #1/31/2007#

List the order number, order date, and shipping name (OrdName) of orders sent to addresses in Denver or Englewood.

SELECT OrdNo, OrdDate, OrdName FROM OrderTbl WHERE OrdCity IN ('Denver', 'Englewood')

4 26 List the order number, the order date, the customer number, the customer name (first and last), the customer state, and the shipping state (OrdState) in which the customer state differs from the shipping state.

SELECT OrdNo, OrdDate, OrdState, Customer.CustNo, CustFirstName, CustLastName, CustState FROM OrderTbl, Customer WHERE OrdState <> CustState AND OrderTbl.CustNo = Customer.CustNo

4 32 List the employee number and the name (first and last) of the first- and second-level subordinates of the employee named Thomas Johnson. To distinguish the level of subordinates, include a computed column with the subordinate level (1 or 2).

SELECT Subr1.EmpNo, Subr1.EmpFirstName, Subr1.EmpLastName, 1 AS SubLevel FROM Employee E, Employee Subr1 WHERE Subr1.SupEmpNo = E.EmpNo AND E.EmpFirstName = 'Thomas' AND E.EmpLastName = 'Johnson' UNION SELECT Subr2.EmpNo, Subr2.EmpFirstName, Subr2.EmpLastName, 2 AS SubLevel FROM Employee E, Employee Subr1, Employee Subr2 WHERE Subr1.EmpNo = Subr2.SupEmpNo AND Subr1.SupEmpNo = E.EmpNo AND E.EmpFirstName = 'Thomas' AND E.EmpLastName = 'Johnson'

4 31 List the employee number and the name (first and last) of second-level subordinates (subordinates of subordinates) of the employee named Thomas Johnson.

SELECT Subr2.EmpNo, Subr2.EmpFirstName, Subr2.EmpLastName FROM Employee E, Employee Subr1, Employee Subr2 WHERE Subr1.EmpNo = Subr2.SupEmpNo AND Subr1.SupEmpNo = E.EmpNo AND E.EmpFirstName = 'Thomas' AND E.EmpLastName = 'Johnson'

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

select cname from customer where resid_city = birthplace

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

select convert(integer, date_returned - date_out) as 'Total Days' from rentals r where cname = 'Black' and make = 'GM' and date_out = '11/1/2009'

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

select cost * convert(integer, date_returned - date_out) as 'Total Cost' from rentals r, rentcost rc where r.make = rc.make and cname = 'Black' and r.make = 'GM' and date_out = '11/1/2009'

Write a query that will return the unique list of birth places of everyone who has ever rented a Ford

select distinct birthplace from customer c, rentals r where c.cname = r.cname and make = 'Ford'

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

select distinct c.cname from customer c, rentals r where c.cname = r.cname and r.pickup = c.resid_city

Using a right outer join, display a unique list of customers who have never rented an automobile.

select distinct c.cname from rentals r right outer join customer c on r.cname = c.cname where rtn is null

Write a query that will return the Names and ages of customers who have rented any automobile during 2009. Make sure that each customer is listed only once in your output.

select distinct c.cname, c.age from customer c, rentals r where c.cname = r.cname and year(date_out) = 2009

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

select distinct cname from rentals where date_returned is null

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

select distinct rc.make from rentcost rc where rc.make not in(select distinct make from rentals)

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

select distinct rc.make from rentcost rc left outer join rentals r on r.make = rc.make where r.cname is null

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

select r.make, avg(convert(integer, date_returned - date_out)) as 'Average Days Rented' from rentals r, rentcost rc where r.make = rc.make and date_returned is not null group by r.make

Write a query that will determine the average age of customers broken out by the city in which they reside.

select resid_city, avg(convert(float,age)) from customer group by resid_city

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

select sum(cost * convert(integer, date_returned - date_out)) as 'Total Cost' from rentals r, rentcost rc where r.make = rc.make