CIS 360 – Business Database Concepts Assignment 3
Hello, dear friend, you can consult us at any time if you have any questions, add WeChat: daixieit
CIS 360 – Business Database Concepts
Assignment 3 - SQL Join & Aggregate Queries
Write the SQL scripts (queries) to answer the following questions. Use the Sports Shop database.
1. What products do we sell? Names and price. Highest price first.
2. I need the name and city of the customers from zip code ending in 301. Alphabetically by city.
3. How many customers do we have? Label the result column Total Customers.
4. Which department is in Chicago? List the name of the department and its location.
5. Someone’s on the phone and wants to know what tennis balls we sell. I need the id number and price also. List cheapest first.
6. HR needs a list of employee names and the date they were hired for all employees hired in 2012. List by hire date within department, oldest first.
7. Which employees earn more than $2500? We don't need the President. Include the department name, employee name, and salary in the result - highest salary first. Keep all employees in the same department together.
8. List all employees that are in department 20. Include the location of the department and order the result by name. List the location first.
9. Display the employee name, salary, department name, and location of all employees who earned a commission. Highest commission first. Use Commission as the header for commissions.
10. Display the employee name and customer name for all employees who are in departments 20 and 30. List in alphabetical order of employee.
11. List the employees, their jobs and hire dates for those employees who work in Dallas. Order the output by most recent hire date. Include the location in the output.
12. List the number of days delay to ship each order out that was delayed (order date is not the same date as the ship date). Include order number and 'Shipment Delay' in the output - highest number first. What does the output tell you?
13. I was supposed to increase an employee’s salary by 20% but I’ve forgotten which salary I was to increase. I think the employee’s name started with the letter “M”. I also seem to recall that the employee worked in department 10. List any employees that match these conditions. Include department number and name so that I can check for sure.
14. List all employees and their managers alphabetically by manager name. Label the columns appropriately.
15. Which customers have placed orders? Display the customer name (alphabetically) only once if they have ordered multiple times.
16. Display the employee name, department number, and all the employee's colleagues that work in the same department. List alphabetically by department number first, then the Employee name, then the colleague names. Label the columns appropriately.
17. What is the annual salary for each employee? Include the employee's job, name, and annual salary. List the result by salary within job. Use appropriate column headings. (weekly salary is stored)
18. What is the difference between the highest and lowest salary? Label the result Salary Difference.
19. We finally hired a number of employees to work in our operations department. Add the following information to the database.
20. What is the average salary for the operations staff? Label output Average Salary for Operations. Show the output with 2 decimal places.
2022-03-16
SQL Join & Aggregate Queries