In the second part of coursework 3, you are required to write queries based on the model answer. The table design of the model answer is shown below, an SQL containing all CREATE TABLE statements is also available on the module page.
These questions are designed to be hard. Try to finish as many of the following tasks as possible.
Your SQL statements must be based on the model answer provided above.
You must use the keywords learned in this semester, untaught keywords are not allowed.
1. Get the retail store(s) that has sold the most amount of powerhouse laptops. In the result, list the name(s) of the retail store(s) and the amount sold.
2. Get the staff names and their associated department names for the email account ¡°manager.club@durian.pc¡±.
(4 marks)
(4 marks)
3. List all staff members in the finance department (¡®Finance¡¯, case sensitive) whose current baseline salary is higher than the highest base salary of the sales department (¡®Sales¡¯, also case sensitive). (4 marks)
4. Get the list of email accounts for those who work in an office alone (share no office with others). In the result, only list staff members¡¯ personal email accounts (email type is ¡®personal¡¯, case sensitive).
(4 marks)
5. Get the difference between the highest baseline salary and the second highest baseline salary of all staff members. Multiple cases of highest salaries should be considered as a single case, same for the second highest salaries (For example, for four salaries 2000, 2000, 1800 and 1800, the difference should be 200). The result should have a single column ¡°diff¡± and a single value. You are not allowed to use the LIMIT keyword
(6 marks)
6. Get the list of all staff members who once did quality checking in the factory and checked at least one computer, but then changed to a different role and have worked as the new role for at least for one month. Assume that the role does quality checking is called ¡°QA staff¡±, role names are case sensitive. In the result, list staff names and staff accounts.
(4 marks)
7. Assume we have a table called ¡°staff_awards¡± in the database:
This table stores the information about staff members who were considered as an outstanding performer in a certain month of this year. Please design a single query that finds out all staff names that was awarded as outstanding performer for 3 or more months consecutively. Staff names should only appear once in the result. In the example table above, Tessa Payne has more awards, but her name should not appear in the final result because her awards are not consecutive. While Lily Adel should be in the final result as she got awards in months 1, 2 and 3.
(6 marks)
8. Calculate the final salaries of all staffs. In the result, list staff names and their final salaries, taking all factors like bonuses, work-in-south bonuses (10%) and sick leave penalties (90%) into account. In the result, list staff account and final salary.
staff (varchar (50))
outstanding_month (int)
¡°Tessa Payne¡±
5
¡°Tessa Payne¡±
6
¡°Lily Adel¡±
1
¡°Lily Adel¡±
2
¡°Tessa Payne¡±
8
¡°Lily Adel¡±
3
¡°Tessa Payne¡±
9
Hint:
(8 marks)
CW3 PART2 SQL:
https://www.learningmall.cn/pluginfile.php/85830/mod_resource/content/0/cw3_2020_autumn_part2_S QL.txt