Share
Explore BrainMass

Writing SQL Statements

1. List the name, city, state, and phone number of each customer not located in Washington (state). Organize the list alphabetized by state and city within each state. Within each city, order the customer names alphabetically with last name followed by first name.
2. List the names of the vendors who supply at least one of the products included in order number 706. List the vendors in alphabetical order. No duplicates please.
3. List the product name, price, vendor name, and wholesale price for all products in the Components category and all vendors who supply those products. Order the list alphabetically by product name and vendor within each product.
4. How many orders does each customer have? Include the customer's name and the number of orders, listed in alphabetical order of the customer's last name. (Don't forget both first and last names.)
5. For each product list the product number, product name, average wholesale price, and the number of vendors who supply that product. List the results in alphabetical order of product name.
6. How many orders include at least 7 different products? Provide the order number, customer name, and the number of products. Order the list by order number.
7. List the order number, customer name, shipping date, and state for each order shipped outside Washington during December 2002, ordered by shipping date and order number within each date. (Be careful.)
8. List the order number, product name, and order date for each order placed by customers named Hallmark. Organize the list by customer first name, order number and the product names in alphabetical order for each order number.
9. Which employees placed an order for a customer with the same last name as the employee? If so, list the common last name, employee first name, customer first name, and order number for each order. Order the list by employee name (last name and first name) and customer first name for each common last name.
10. The company is giving a bonus to the three employees who placed the most orders. List the employees and how many orders each placed. Organize the list so that the names of the employees earning the bonuses appear first.

1. List the name, address, and date of birth of all female employees. Order the list alphabetically by last name.
2. List the name and salary of all employees in the Research department. Order the list in decreasing order of salaries.
3. List the name and hours worked per week for each employee who works on the Reorganization project. Order the list by hours worked in decreasing order and by last name within each amount of hours.
4. List the name of all employees directly supervised by Franklin Wong. Order the list by employee last name.
5. List the average salary of the employees of the Research department.
6. List the name of each department and the name of the department manager. Order the list by department name
7. For every project located in Houston, list the project name, the controlling department name, and the department manager's name. Order the list by project name.
8. For all employees with children, list the (first and last) name of the employee and the first name of the child. Order the list by last name of the employee and first name of the child. Hint: Be careful that you do not list the spouses, only the children.
9. List the (first and last) name of each employee and the name of the employee's spouse, if known. Hint: Be careful here; this uses a different operation than the previous question. You want to list all the employees, not just the ones who list a spouse.
10. List the (first and last) name of each employee who has no dependents. Hint: Remember the set operations.

Solution Preview

List the name, city, state, and phone number of each customer not located in Washington (state). Organize the list alphabetized by state and city within each state. Within each city, order the customer names alphabetically with last name followed by first name.
SELECT LastName,, FirstName city, state, PhoneNumber FROM Customers WHERE State<>’Washington’ ORDER BY state, city, LastName, FirstName
List the names of the vendors who supply at least one of the products included in order number 706. List the vendors in alphabetical order. No duplicates please.
Select VendorName From Vendors WHERE VendorNumber in(select VendorNumber from Products WHERE ProductNumber in(SELECT ProductNumber from Order WHERE order=’706’) GROUP BY VendorName
List the product name, price, vendor name, and wholesale price for all products in the Components category and all vendors who supply those products. Order the list alphabetically by product name and vendor within each product.
SELECT Products.ProductName, Products.Price, Products.VendorID, Products.WholesalePrice, Vendors.VendorName FROM products INNER JOIN Vendors ON Products.VendorID=Vendors.VendorID WHERE Products.category=’Components’ ORDER BY Products.productname, Vendors.VendorName
How many orders does each customer have? Include the customer's name and the number of orders, listed in alphabetical order of the customer's last name. (Don't forget both first and last names.)
Select count(Order.OrderID) as NoOrders, Customers.LastName, Customers.FirstName FROM Order INNER JOIN Customers ON Order.Customers.CustomerID GROUP ON CustomerName ORDER BY Customers.LastName

5. For each product list the product number, product name, average wholesale price, and the number of vendors who supply that product. List the results in alphabetical order of product ...

Solution Summary

The solution is a detailed written answer of writing SQL statements

$2.19