Reference no: EM132861944

Assignment Introduction

In this assignment, the employees sample database (created by Fusheng Wang and Carlo Zaniolo at Siemens Corporate Research), a large base of data spread over six separate tables and consisting of 4 million records in total, will be used with the MySQL RDBMS. The following diagram provides an overview of the structure of the employees.db:

Assignment Instructions
• This exercise is to give you the ability to run SQL queries.
• Download employees.db.bz2 file from:
• Unarchive the file (with 7-Zip or similar) to get to employees.db
o File → Open DB → locate employees.db on your computer → Open

For each of the questions below (Part 1-3), write the SQL query in text (so this can be copied for verification), take a screenshot of your SQL statement AND the corresponding results. For example, the following is an example of how each a query and its results should be presented:

Part 1
For parts 1 to 5 below, you are required to write the SQL statements for each. Write SQL query to display and take a screenshot:

1. List all attributes present in the departments relation.

2. List all employee IDs of all past/current employees, their first and last names.

3. List all job titles held by any past/current employees.

4. List all unique job titles found in the database.

5. List all past/current employees' names ordered alphabetically in ascending order, i.e. first name and last name in alphabetical order.

Part 2 :
Write a query in SQL to display and take a screenshot:

1. List all department titles present in the database.

2. List the total number of all employees (past/current).

3. List female employees (past/current) together with all other relation attributes.

4. List past/current employees hired prior to 1986-01-01 with the surname Simmel

5. How many past/current employees' last name begins with the capital letter B?

Use a column alias total with B to output your results. For the following you are required to use SQL statements and provide a screenshot of your results:

6. Create a new table called emp_training with 3 columns:
• trainer_no: this should be the primary key and is of type integer and needs to implemented as an auto-increment.
• first_name: this data type is varchar(30) and should not be NULL
• last_name: this data type is varchar(30) and should not be NULL
• t_module: this data type is varchar(20)

7. Insert 2 new rows into the emp_training table:
Row 1: fname: Joe
lname: Bloggs
module: Google Docs
Row 2: fname: Fred
lname: Bloggs
module: Google Sheets [4 marks]

8. The organisation no longer wishes to record the employees training within the database. Therefore, delete the newly created emp_training table.

9. Alter the employees table to include an email_address field with a data type of varchar(20).

10. Update the email address of Georgi Facello to [email protected], where emp_no equals to 10001.

Part 3 :

Write a query in SQL to display:

1. The number of all employees that started on 1991-05-01.

2. List all emp_no who have had strictly more than 2 titles and display the total number of the titles they have had.

3. The number of employees that have a current salary (i.e. to_date equals to 9999-01-01) between 90000 and 90040.

4. List all unique employees' last and first names (using GROUP BY method) that have a current salary (i.e. to_date equals to 9999-01-01) greater than 90000, outputting both names in descending order (sort by the last name first and then the first name) and also displaying their current salaries (using the INNER JOIN method).

5. First name, last name, all salary dates and related amounts for the employee with employee number 10012.

6. In relation to the table named salaries in Figure 1 above:
a. What is the degree of this table?
b. What column(s), if any, make(s) up the primary key?
c. What column(s), if any, make(s) up the foreign key?

7. In the given schema, the tables dept_emp, dept_manager, salaries, titles have composite keys. Explain for each relation why this is the case?

Attachment:- DATABASES descriptor.rar