Description
Database setup
Enterprise schema for Lab 1.
To create the schema, first copy the createTables.sql script to the host where you are running the mysql utility (e.g., ecetesla0 or ecetesla1). This can be accomplished using tools like scp or sftp. Next, execute the script using the following command:
source createTables.sql;
Verify that the script ran to completion without producing any errors, and check that the required tables now exist in your database by running the following command:
show tables;
Problems
Note: The solution to each problem must be a single SQL statement terminated by a semicolon.
Single table queries
1a) Which departments are in located Cairo?
1b) How many people are employed in each job type in the company, ordered ascending by job type? The names of the output columns for the job type and number of people should be job and count, respectively. (Marked for submission)
1c) What are the names and salaries of the engineers?
1d) What is the average salary by job type?
1e) What is the ID of the department (or department(s) if there is tie) with the most engineers? The name of the output column should be deptID. (Marked for submission)
1f) For engineering departments, what is the percentage of engineers in each department (provide the corresponding department ID)?
1g) Return the empID of all employees earning the second-highest salary, as defined in Appendix A for k = 2. (Marked for submission)
Multi table queries
2a) What are the names and IDs of employees who are currently not assigned to any project? The names of the output columns should be empName and empID, respectively. (Marked for submission) 2b) What are the names, jobs and roles of all employees whose role is different than their job?
2c) What is the number of employees, by job, whose role is identical to their job?
2d) What is the total of the salaries of all employees assigned to each project?
2e) Repeat (2d) but include an additional row for the total of the salaries of all employees not assigned to any project. The names of the output columns should be projectSalary and projID. The projID for the additional row representing employees without projects should be the SQL null value. (Marked for submission) 2f) Identify the employees (if any), by name and ID, who are assigned to more than one project.
Data insertion, deletion, and update
To ensure that your results are reproducible, please ensure that you reload your database (i.e., rerun the provided SQL script) before running the code for each of the following problems.
3a) Raise the salary of everyone working on the compiler project by 10%. (Marked for submission)
3b) Raise the salary of all janitors by 5%, and all Waterloo employees by 8%; if there are any janitors in Waterloo, their pay should be raised by 8%, not by 13%. (Marked for submission)
3c) Add a nullable column shift of type VARCHAR(5) to the Employee table. (Marked for submission)
3d) Populate the shift column created in part (3c) with a value for each employee that satisfies the criteria defined in Figure 2. (Marked for submission)
Figure 2: Criteria for values in the new column shift.
A Definition of kth-highest salary
This appendix defines the meaning of kth-highest salary, taking into account cases where multiple employees may share the same salary. Consider the table shown below in Figure 3:
Figure 3: Projection of Employee table onto employee ID and salary.
Next, consider the distinct salaries in descending order, as shown in Figure 4:
Figure 4: Salaries sorted in descending order.
The kth-highest salary is then defined as the value in the kth row of the table in Figure 4. (We will assume that there are at least k distinct salaries in the Employee table.) For example, when k = 1, the kth-highest salary is the highest salary, or 50000. Similarly, when k = 2, the kth-highest salary is the second-highest salary, or 45000.
Note that when asked to compute the IDs of employees earning the kth-highest salary, the result set may contain more than one row. For example, for k = 1, two employees are tied for first place, as shown in Figure 5:
Figure 5: IDs of employees with highest salary (k = 1).
For k = 2, there is a three-way tie, as shown in Figure 6:
Figure 6: IDs of employees with second-highest salary (k = 2).
[1] The firewall blocks access to the MySQL port from outside, and so you will need to use the campus VPN if you are working from home.
