SQL Queries for Employee Database and Importance of Database Testing

COVER PAGE

Task 1:
1. Write an SQL statement to list all employees with their full names, hire date and title
SQL Code and Result
For this sql Query a list of all employees with their dull names, the date they were hired and
their title is needed. Since the employee table stores the name of the employee as first name
and last name as two separate columns the two column have to be joined using a MySQL inbuilt
function called concat() is used to concatenate the first and the last name of the employee. To
get the title of the employee an inner join has to be done on the titles table.
2. Write an SQL statement to show the salary of all employees and their department name.
SQL Code and result

For this query the full names of the employee, salary and the department that the employee is
in are needed. Concatenation is needed to display the first name and the last name of the
employee as one name. The salary field is found on the salaries table thus an inner join between
salary table and employee table is needed. To get the department name that the employee is in
a join between three tables is needed. The first join is between the employee table and the
dept_emp table. The next join is between the dept_emp table and the department table . The
dept_emp table is a joint table linking the employee table and the departments table.
3. Write an SQL statement to show the full names and genders of HR department staff.
SQL Code and Results

This query gets the full names and gender of all employees working in the human resources
department name. To get the full name of an employee concatenation of the first and the last
name is needed. To apply the condition to filter only employees working in the human resources
department, join between three tables is needed. The first join is between the employee table
and the dept_emp table. The next join is between the dept_emp table and the department
table . The dept_emp table is a joint table linking the employee table and the departments
table. A conditional statement is then applied to fetch only employees working in the human
resources department.
4. Write an SQL statement to show the all departments’ name and their departments’
managers.
SQL Code and Results