In this lab you will learn the following:
1. Write basic retrieval queries using SQL
The “Company” Database
Consider the “Company” database which includes the tables below. This database records employees of a company with their departments:
Table Employee with the following columns:
◦ Empno : Employee number on three digits. This column is the primary key of the table Employee.
◦ Ename: The employee name, which is variable string that cannot exceed 20 characters. This field cannot be empty.
◦ Salary: the employee salary, which is a number, with 10000 as the default value.
◦ Entry: the employee joining date. The default is today’s date.
Table Department with the following columns:
◦ Dnumber: The department number defined on one digit. This column is the primary key of the table Dep.
◦ Dname: the department name which is a fixed string of 15 characters. This attribute is a candidate key and should not be duplicated.
Apply the following steps:
1. Open MySQL
2. Go to File Menu New Query Tab
3. Open the ‘Company DB V3 SQL1’ script provided (in LMS) and paste it in the New Query Tab.
4. Execute the whole script.
5. Click on File Menu, open a New Query Tab. In this new tab type the following queries to check the content of your tables:
a. Select * From Employee;
b. Select * From Department;
6. Write the following SQL queries:
a. Display the name of employees who work in department 1.
b. Display the name of employees with the name of the departments they working in.
c. List all possible combinations of rows in both tables (Cross or Cartesian Product).
d. List the employee names in ascending order.
e. List employees having a name beginning with the letter K (use LIKE).
f. List employees’ salaries and remove duplicates.
g. Retrieve all information of employees working in the ‘Planning’ department
h. Which employees have a salary higher then Mohammed’s salary.
