SQL Statements for Employee Database | Desklib

COVER PAGE

Task 1: SQL Statements;
1. SQL to list all employees with their full names, hire date and title.
SQL Code:
select concat(first_name,'
',last_name),hire_date,title from employees inner join titles o
ntitles.emp_no=employees.emp_no;
Results:
This query is used to fetch the full names of an employee, the date that the employee was hired,
and the title of the employee. Because the full names are saved as first name and last name the
query uses an inbuilt sql function called concat which concatenates the two columns while
adding a space between the two columns. To get the title a join between employees table and
titles table is needed. The type of join used is inner join using the emp_no column in both tables
thus the query returns all columns for which the emp_no in the employees table matches the
emp_no in the titles table.

2. SQL to show salaries and department name of all employees
SQL Code:
select concat(first_name,'
',last_name),salary,dept_name from employees inner join salaries o
nsalaries.emp_no=employees.emp_no inner join dept_emp on dept_emp.emp_no=employees.emp_no
innerjoin departments on departments.dept_no=dept_emp.dept_no;
Results:
This query is used to show the full names of each employee, their current salary and the
department name the employee works in. Concat function is used to show the full names of the
employee by concatenating the first name and the last name of the employee. The salary of an
employee is found in the salaries table thus an inner join is done between the salaries table and
the employee table using the emp_no column in both tables. To get the department name that
an employee works in, a join between employees table and departments table is done using the
joint table dept_emp.
3. SQL to show full names and genders of HR department staff
SQL Code:
select concat(first_name,'
',last_name),gender from employees inner join dept_emp ondept_emp.emp_no=employees.emp_no

inner join departments ondepartments.dept_no=dept_emp.dept_no where departments.dept_name=
'Human Resources';
Results:
This query is used to show the full names and the gender of all employees working in the human
resources department. The first name and the last name are concatenated using the concat
function. To join the employees and departments tables a join of the dept_emp supporting table
is done. Finally a condition is used to filter only employees working in the human resources
department.
4. SQL to show all department name and their department managers
SQL Code:
select dept_name,concat(first_name,'
',last_name) from departments inner join dept_manager o
ndept_manager.dept_no=departments.dept_no inner join employees onemployees.emp_no=dept_man
ager.emp_no;
Results: