Given the schema below write the SQL to answer the following questions. Each que

Question

Given the schema below write the SQL to answer the following questions.   Each query should include at least one join.

Table_Name: Column(s)

customers: CustomerNumber, CustomerName, ContactLastName, ContactFirstName, phone, addressLines1, addressLines2, city, state, postalCode, Country, SalesRepEmployeeNumber.

employees: EmployeeNumber, LastName, FirstName, Extension, Email, OfficeCode, reportsTo, JobTitle.

offices: officeCode, City, phone, addressLine1, addressLine2, State, Country, postalCode, territory.

orderdetails: orderNumber, productCode, quantityOrdered, priceEach, OrderLineNumber.

orders: orderNumber, orderDate, requiredDate, ShippedDate, Status, Comments, CustomerNumber.

payments: CustomerNumber, CheckNumber, PaymentDate Amount.

productlines: productLine, TextDescription, htmlDescription, Image.

products: ProductCode, ProductName, ProductLine, ProductScale, ProductVender, productDescription, quantityInStock, BuyPrice, MSRP.

1. What Dates did Customers from postalCode ‘51003’ make Payments?

2.What are the employeeNumbers for the employees that have the jobTitle of ‘Sales Rep’ and work in ‘Tokyo’?

3. How many products were not ordered?

4.Who reports to a ‘Sales Rep’?

5.What is the total quantityOrdered for products ordered on ‘2004-01-29’ for the productLine ‘Vintage Cars’?

Explanation / Answer

Answer)
1. What Dates did Customers from postalCode ‘51003’ make Payments?

select PaymentDate from payments where CustomerNumber in(
select CustomerNumber from customers where postalCode='51003'
);

2.What are the employeeNumbers for the employees that have the jobTitle of ‘Sales Rep’ and work in ‘Tokyo’?

select EmployeeNumber from employees where JobTitle = 'Sales Rep' and OfficeCode in(
select OfficeCode from offices where City='Tokyo'
);

3. How many products were not ordered?

select count(ProductCode) from products where ProductCode not in (
select productCode from orderdetails
);

4.Who reports to a ‘Sales Rep’?

select EmployeeNumber, LastName, FirstName from employees where reportsTo in(
select EmployeeNumber from employees where JobTitle='Sales Rep'
);

5.What is the total quantityOrdered for products ordered on ‘2004-01-29’ for the productLine ‘Vintage Cars’?

select sum(quantityOrdered) from orderdetails where orderNumber in(
select orderNumber from orders where orderDate ='2004-01-29'
) and productCode in(
select ProductCode from products where ProductLine='Vintage Cars')
;

Related Questions

Navigate

• Browse (All)
• Browse G
• Subjects

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