TD SQL

1. Basic Orders
1.1. SELECT statement
4. There are four coding mistakes in this statement:

SELECT employee_id, last_name sal x 12 ANNUAL SALARY FROM

employees;

Explication des erreurs :

 Virgule manquante
 Opérateur incorrect : x doit être remplacé par *
 Alias invalide pour le nom de colonne, doit être entre guillemets ou
précédé de AS
SELECT employee_id, last_name, salary * 12 AS "ANNUAL SALARY" FROM
employees;

7. The HR department wants a query to display the last name, job code, hire date, and employee number
for each employee, with employee number appearing first. Provide an alias STARTDATE for the hire_date
column.

SELECT employee_id, last_name, job_id, hire_date AS STARTDATE FROM

employees;
8. The HR department needs a query to display all unique job codes from the EMPLOYEES table.

SELECT DISTINCT job_id FROM employees;

9. The HR department wants more descriptive column headings for its report on employees. Get the
statement from lab_02_01.sql in iSQL*Plus. Name the column headings Emp #, Employee and Title (case-
sensitive).

SELECT employee_id AS "Emp #", last_name AS "Employee", job_id AS "Title",

hire_date AS STARTDATE FROM employees;
1.2. Restricting and sorting data
1. Due to budget issues, the HR department needs a report that displays the last name and salary of
employees earning more than $12,000.

SELECT last_name, salary FROM employees WHERE salary > 12000;

4. Create a report to display the last name, job id, and start date for the employees with the last names of
Matos and Taylor. Order the query in ascending order by start date.

SELECT e.last_name, e.job_id, j.start_date FROM employees e

join job_history j ON e.employee_id= j.employee_id

WHERE last_name IN ('Matos', 'Taylor') ORDER BY j.start_date ASC;

8. Create a report to display the last name and job title of all employees who do not have a manager

SELECT e.last_name, j.job_title FROM employees e

JOIN jobs j ON e.job_id= j.job_id WHERE manager_id IS NULL;

13. Display the last name of all employees who have both an “a” and an “e” in their last name.

SELECT last_name FROM employees

WHERE last_name LIKE '%a%' AND last_name LIKE '%e%';

14. Display the last name, job, and salary for all employees whose job is sales representative (SA_REP)
or stock clerk (ST_CLERK) and whose salary is not equal to $2,500, $3,500, or $7,000.

SELECT last_name, job_id, salary FROM employees

WHERE job_id IN ('SA_REP', 'ST_CLERK') AND salary NOT IN (2500, 3500, 7000);

2. Functions in SQL
2.1. Single Row Functions
2. The HR department needs a report to display the employee number, last name, salary, and salary
increased by 15.5% (expressed as whole number) for each employee. Label the column “New Salary”.

SELECT employee_id,last_name,salary,ROUND(salary * 1.155) AS "New

Salary"

FROM employees;
4. Write a query that displays the last name (with the first letter uppercased and all other letters
lowercased) and the length of the last name for all employees whose name starts with the letter J, A, or
M. Give each column an appropriate label. Sort by employees’ last names.

SELECT INITCAP(last_name) AS "Last Name", LENGTH(last_name) AS "LENGTH"

FROM employees

WHERE SUBSTR(last_name, 1, 1) IN ('J', 'A', 'M') ORDER BY last_name;

6. The HR dept’ wants to find the length of employment for each employee. Display the last name and
calculate the number of months between today and the date on which the employee was hired. Order by
the number of months worked. Round up to a whole number.

SELECT last_name, ROUND(MONTHS_BETWEEN(SYSDATE, hire_date)) AS "Mois de

service"
FROM employees ORDER BY "Mois de service";

9. Display each
employee’s last name, hire date, and salary review date, which is the first Monday after six months of
service. Label the column REVIEW. Format the dates to appear in the format similar to “Monday, the
Thirty-First of July, 2000”.

SELECT last_name, hire_date, TO_CHAR(NEXT_DAY(ADD_MONTHS(hire_date, 6),

'MONDAY'),

'Day, "le" Ddspth "de" Month, YYYY') AS REVIEW

FROM employees;

12. Create a query that displays the first eight characters of the employees’ last names and indicates the
amounts of their salaries with asterisks. Each asterisk signifies a thousand dollars. Sort the data in
descending order of salary. Label the column EMPLOYEES_AND_THEIR_SALARIES.

SELECT SUBSTR(last_name, 1, 8) || ' ' || RPAD('*', FLOOR(salary / 1000), '*') AS

"EMPLOYEES_AND_THEIR_SALARIES",salary

FROM employees ORDER BY salary DESC;

14. Rewrite the statement in the preceding exercise using the DECODE syntax.

SELECT last_name, job_id, DECODE(job_id, 'SA_MAN', 'Grade A', 'IT_PROG', 'Grade

B', 'Grade C') AS GRADE

FROM employees;

2.2. Group Functions

4.Find the highest, lowest, sum, and average salary of all employees. Label the columns Maximum,
Minimum, Sum, and Average, respectively. Round your results to the nearest whole number.

SELECT

ROUND(MAX(salary)) AS "Maximum",

ROUND(MIN(salary)) AS "Minimum",

ROUND(SUM(salary)) AS "Sum",

ROUND(AVG(salary)) AS "Average"

FROM employees;

7. Generalize the query so that the user in the HR department is prompted for a job title.

SELECT COUNT(*) AS "Nombre d'employés"

FROM employees

WHERE job_id = '&job';

Par exemple : AC_MGR

11. Create a query that will display the total number of employees and, of that total, the number of
employees hired in 1995, 1996, 1997, and 1998. Create appropriate column headings.

SELECT COUNT(*) AS "Total",

SUM(CASE WHEN EXTRACT(YEAR FROM hire_date) = 1995 THEN 1 ELSE 0 END) AS "Hired_1995",
SUM(CASE WHEN EXTRACT(YEAR FROM hire_date) = 1996 THEN 1 ELSE 0 END) AS "Hired_1996",
SUM(CASE WHEN EXTRACT(YEAR FROM hire_date) = 1997 THEN 1 ELSE 0 END) AS "Hired_1997",
SUM(CASE WHEN EXTRACT(YEAR FROM hire_date) = 1998 THEN 1 ELSE 0 END) AS "Hired_1998"
FROM employees;

12. Create a matrix query to display the job, the salary for that job based on department number, and the
total salary for that job, for departments 20, 50, 80, and 90, giving each column an appropriate heading.

SELECT job_id,
SUM(CASE WHEN department_id = 20 THEN salary END) AS "Dept_20",
SUM(CASE WHEN department_id = 50 THEN salary END) AS "Dept_50",
SUM(CASE WHEN department_id = 80 THEN salary END) AS "Dept_80",
SUM(CASE WHEN department_id = 90 THEN salary END) AS "Dept_90",
SUM(salary) AS "Total"
FROM employees WHERE department_id IN (20, 50, 80, 90)
GROUP BY job_id;
3. Retrieving Data from Several Tables
2. Write a query to display the last name, department number, and department name for all employees.

SELECT e.last_name, e.department_id, d.department_name

FROM employees e, departments d

WHERE e.department_id = d.department_id;

3.The HR department needs a report of employees in Toronto. Display the last name, job, department
number, and department name for all employees who work in Toronto.
SELECT e.last_name, e.job_id, e.department_id, d.department_name
FROM employees e, departments d, locations l
WHERE e.department_id = d.department_id
AND d.location_id = l.location_id
AND l.city = 'Toronto';

5.Modify your script to display all employees including King, who has no manager

SELECT e.last_name AS "Employee", e.employee_id AS "Emp#",

m.last_name AS "Manager", m.employee_id AS "Mgr#"
FROM employees e
LEFT OUTER JOIN employees m ON e.manager_id = m.employee_id
ORDER BY e.employee_id;
7. The HR department needs a report on job grades and salaries. To familiarize yourself with the
JOB_GRADES table, first show the structure of the JOB_GRADES table. Then create a query that displays
the name, job, department name, salary, and grade for all employees.

SELECT e.last_name, e.job_id, d.department_name, e.salary, jg.grade_level

FROM employees e, departments d, job_grades jg
WHERE e.department_id = d.department_id
AND e.salary BETWEEN jg.lowest_sal AND jg.highest_sal;

8. The HR department wants to determine the names of all employees who were hired after Davies.
Create a query to display the name and hire date of any employee hired after employee Davies.

SELECT last_name, hire_date

FROM employees
WHERE hire_date > (
SELECT hire_date
FROM employees
WHERE last_name = 'Davies'
);