Explore BrainMass
Share

# Writing SQL Statements

This content was STOLEN from BrainMass.com - View the original, and get the already-completed solution here!

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.

https://brainmass.com/computer-science/sql/writing-sql-statements-516526

#### 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

## Writing SQL Queries Using a Database and Tables

Write SQL queries (SELECT statements) using BETWEEN, LIKE, UNION, and GROUP.

Using the database and tables (see attachment) write SQL INSERT statements to insert records into the employee table for the workers identified in the Employee Files for the administrative offices and the Del Mar location. Check your results by selecting all of the columns from both of your tables.

Using the database and tables (see attachment), write SQL queries using BETWEEN, LIKE, and UNION:

- Write a SQL query that joins two tables in the example database and uses BETWEEN to restrict record selection. (Use Emp_salary to restrict the data.)
- Write a SQL query that joins two tables in the example database and uses BETWEEN to restrict record selection. (Use Hire_date to restrict the data.)
- Write a SQL query that joins two tables in the example database and uses LIKE to restrict record selection. (Use Telephone_area_code to restrict data.)
- Write a SQL query that joins two tables in the example database and uses LIKE to restrict record selection. (Use Emp_age to restrict data.)
- Write a SQL query that uses UNION of the two tables to produce a combined result set.

Using the updated database that includes employees from administrative offices and the Delmar location, write the following queries using the SQL GROUP BY statement:

- Group employees by job classification: Select the employees' last names and group them by EEO_1_classification.
- Group employees by salary: Select the employees' last names and group them by Emp_salary.
- Group employees by salary within their job classification: Select the employees' last names and group them by Emp_salary within their EEO_1_Classification.
- Select the employees' last names and group them by salary within job titles that are grouped into exempt and non-exempt.

In Microsoft SQL Server Management Studio, save your SQL statements as .SQL files. Save your SQL output as .RPT files. You will need to post both types of files (.SQL and .RPT).

View Full Posting Details