Description

Individual work policy: In this lab, students are expected to work on their own. Please e-mail the TAs for help (see syllabus for contact info).
Weight: This lab is worth 10% of your final course grade (1/5 of the weight in the Assignments/Labs category).
Objective: In this lab, you will practice SQL statements on a small schema.
Submission: Certain problems are marked for submission in boldface. These will be graded automatically via grading scripts that check the output of your queries. When you are done, please enter your solutions into the provided template file (lab1.sql), and submit this file using the Lab 1 dropbox in LEARN. The remaining problems, which are not marked for submission, are practice problems and should not be submitted.
1 Database setup
You will use the mysql command line utility to connect to a MySQL DBMS on the server manta.uwaterloo.ca. You will then create some tables, add some data, and use SQL statements to manipulate this database.
Using SSH, you should first connect to ecetesla0.uwaterloo.ca or ecetesla3.uwaterloo.ca with your Nexus user name and password. Once you are connected by SSH, you can access the DBMS via the following command:
mysql -h manta -u user_<nexususerid> -p
For example, for user x12cool, the command will be:
mysql -h manta -u user_x12cool -p
Each student has a temporary MySQL password of the following form:
user <nexususerid> HeLLo<&F20!
For example, for user x12cool, the temporary password is:
user x12cool HeLLo<&F20!
Please change your temporary password as soon as possible. To do so, use a command of the following form:
set password = ’myCleverNewPassword’;
Remember to replace ‘myCleverNewPassword’ with your own clever password.
use ece356db_<nexususerid>;
For example, for user x12cool, the command will be:
use ece356db_x12cool;
Once you have chosen the correct database, you can create the required tables and add data to these tables. We provide a script for this purpose, called createTables.sql. Examine this file to familiarize yourself with the tables and data that your database will contain. The logical schema is illustrated below in Figure 1 for your convenience.

Figure 1: 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;
2 Submission guidelines
First, download the provided lab1.sql template file from LEARN.
Write your answers in this file for each problem in the appropriate place, which is indicated by the block comments (/* … */).
Do not remove the separator lines, which start with four dashes (e.g., —- 1b). Similarly, do not add any new lines containing four dashes. Standard SQL comments use double dashes, and are allowed.
Note 1: Submissions that do not comply with the above guidelines will be penalized up to 100%.
Note 2: The grading script will use the same schema as defined in createTables.sql, but with a different data set.
3 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
2c) What is the number of employees, by job, whose role is identical to their job?
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

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.

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).