For this practice assignment you will need to utilize the Classic Cars informati

Question

For this practice assignment you will need to utilize the Classic Cars information located here - http://richardtwatson.com/dm6e/Reader/ClassicModels.html

Write the SQL to answer the following queries.

Single entity

a. Prepare a list of offices sorted by country, state, and city.

b. How many employees are there in the company?

c. What is the total of payments received?

d. List the product lines that contain 'Cars'.

e. Report total payments for October 28, 2004.

One to many relationship

f. Report the account representative for each customer.

g. Report total payments for Atelier graphique.

h. Report the total payments by date

i. Report the products that have not been sold.

j. List the amount paid by each customer.

Many to many relationship

k. List products sold by order date.

l. List all the orders for the 1940 Ford Pickup Truck.

m. List the names of customers and the corresponding order numbers where a particular order from that customer has a value greater than $5,000.

n. List the products that appear on all orders (if there are any).

Explanation / Answer

a.

Select * from Offices order by country,state,city;

b.

Select count(employeeNumber) from Employees;

c.

Select count(checkNumber) from Payments where paymentDate IS NOT NULL;

d.

Select * from ProductLines where textDescription LIKE '%Cars%';

e.

Select count(checkNumber) from Payments where paymentDate ='Oct-28-2004';

One to many relationship

f.

Select customerName,contactFirstName,contactLastName from Customers;

g.

Select sum(amount) from Payments inner join Customers on Payments.customerNumber = Customers.customerNumber where customerName = 'Atelier graphique';

h.

Select count(checkNumber) from Payments where date = &date;

i.

Select * from Products ,OrderDetails where where Products.productCode != OrderDetails.productCode;

j.

Select customerName,amount from Payments inner join Customers on Payments.customerNumber = Customers.customerNumber order by customerNumber;

Many to many relationship

k.

Select * from Products ,OrderDetails ,Orders where where Products.productCode = OrderDetails.productCode and OrderDetails.orderNumber = Orders.orderNumber order by orderDate;

l.

Select * from Orders ,OrderDetails,Products where productDescription = '1940 Ford Pickup Truck';

m.

Select customerName,Orders.orderNumber from Customers,Orders,OrderDetails where quantityOrdered*priceEach > 5000;

n.

Select * from Products ,OrderDetails where Products.productCode = OrderDetails.productCode;

Do ask if any doubt. Please upvote.

Related Questions

Navigate

• Browse (All)
• Browse F
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.