This mini-project has two parts; each part may be submitted independently. Results may be submitted using one of the documentation options identified in a separate document. Consult the document Documenting SQL in Class Content -> Oracle 11g XE. The files containing the schema and sample data for the Company database and the schema for the Sales database are posted in Class Content -> Scripts.
Part 1
Preparation
1. Start the SQL Command Line and create a user account for each part of the assignment. (Do not use the sys or system account for the SQL exercises after the account has been created.)
a. Login as user system to do this.
2. Start SQLDeveloper and define a connection for the user you just created.
3. The instructions for creating a user account and the SQL Developer connection are posted in Class Content -> Oracle 11g XE
4. Login as the new user for the rest of the exercise.
a. For the command line login as the username you created.
b. For SQLDeveloper, use the connection you defined.
5. Upload and execute the script CompanyTables.sql from the class CD.
6. Upload and execute the script CompanyData.sql from the class CD.
Instructions
“?? Write and execute the SQL query to solve each problem using the Company database.
“?? You must use the information given in each problem; do not use information that is contained in the sample data to simplify a query. You wouldn””?t have this information in a real-life problem with a much larger database.
“?? Include the results returned in your solution file.
Problems
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.
Part 2
Preparation
1. Start the SQL Command Line and create a user account for each part of the assignment. (Do not use the sys or system account for the SQL exercises after the account has been created.)
a. Login as user system to do this.
2. Start SQLDeveloper and define a connection for the user you just created.
3. The instructions for creating a user account and the SQL Developer connection are posted in Class Content -> Oracle 11g XE
4. Login as the new user for the rest of the exercise.
a. For the command line login as the username you created.
b. For SQLDeveloper, use the connection you defined.
5. Upload and execute the script SalesTables.sql from the class CD.
6. Upload and execute the script SalesData1.sql from the class CD.
7. Upload and execute the script SalesData2.sql from the class CD.
Instructions
“?? Write and execute the SQL query to solve each problem using the Company database.
“?? You must use the information given in each problem; do not use information that is contained in the sample data to simplify a query. You wouldn””?t have this information in a real-life problem with a much larger database.
“?? Include the total number of rows returned by the query.
“?? Include the results returned in your solution file.
“?? Include only the first ten rows of the results, if the query returns more than ten rows.
Problems
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.
