Data Modelling & SQL Language .

Data Modelling & SQL Language
Task 1
1. Employees with their full names, hire date and title.
SELECT employees.first_name, employees.last_name, employees.hire_date, titles.title
/*selecting the data from table*/
FROM employees, titles /*querying data tables*/
WHERE employees.emp_no=titles.emp_no /*relating table employee and title using as the
foreign key in titles and its the primary key in employees*/
ORDER BY employees.emp_no /*format of ordering displaying data*/ (Galindo et al., 2011)
2. Salary of all employees and their department name.
SELECT salaries.salary, departments.dept_name /*data query*/
FROM salaries, departments /*db tables*/
Sample output

3. Full names and genders of HR department staff.
SELECT employees.first_name, employees.last_name, employees.gender FROM employees,
departments
WHERE dept_name IN ('Human Resources');

4. All departments’ name and their departments’ managers.
SELECT departments.dept_name, dept_manager.emp_no, dept_manager. from_date,
dept_manager.to_date
FROM departments, dept_manager
WHERE departments.dept_no=dept_manager.dept_no
ORDER BY departments.dept_no;

5. Department managers who were hired after 1986
SELECT employees.first_name, employees.last_name, employees.hire_date
FROM employees
WHERE hire_date > '1986-12-31' /*greater than date provided do it will start at 1987*/
6. SQL statement to change any employee’s title where the employee has just phoned in
with his/her last name.
UPDATE titles /*table name to be updates*/
SET title = 'New Name' /*set name to update*/
WHERE emp_no=10002; /*row to make changes to*/ (Mishra, Koudas and Zuzarte, 2010)