Exercises (SQL LAB)

CLASS 01 (INTRODUCTION TO SELECT)

You have been hired as an SQL programmer at XYZW Corporation. Your first task is to create some reports based on
in the data of the human resources tables.

1. Your first task is to determine the structure of the DEPARTMENTS table and its content.

DESCRIBE departments

SELECT *

FROM departments;

2. You need to determine the structure of the EMPLOYEES table.

DESCRIBE employees

3. The human resources department wants to run an inquiry to display the surname, the code of
position, the date of admission, and the phone number of each employee, with the employee number displayed first. Provide the
nickname STARTDATE for the HIRE_DATE column.

SELECT employee_id, last_name, job_id, hire_date AS StartDate, phone_integer

FROM employees;

4. The human resources department needs a query to display all job codes
exclusive to the EMPLOYEES table.

SELECT DISTINCT job_id

FROM employees;

CLASS 02 (SELECT WITH RESTRICTIONS)

1. Due to budgetary issues, the department needs a report with the last name and the salary.
of employees who earn more than $12,000.

SELECT last_name, salary

FROM employees

WHERE salary > 12000;

2. Crie um relatório que exiba o sobrenome e o número do departamento do funcionário 176.

SELECT last_name, department_id

FROM employees

WHERE employee_id = 176;

3. The human resources department needs to locate employees with high and low salaries. Create a
query to display the last name and salary of all employees whose salary range is not between US$ 5,000 and US$
12,000.

SELECT last_name, salary

FROM employees

WHERE salary NOT BETWEEN 5000 AND 12000;

4. Create a report to display the surname, job ID, and hire date of employees whose
Last names are Matos and Taylor. Organize the query in ascending order by date of admission.

SELECT last_name, job_id, hire_date

FROM employees

WHERE last_name IN ('Matos', 'Taylor')

ORDER BY hire_date;

5. Display the last name and department number of all employees in departments 20 and 50.
ascending alphabetical order by name.
SELECT last_name, department_id

FROM employees

WHERE department_id IN (20, 50) ORDER BY last_name ASC;

6. Build a query to display the last name and salary of employees who earn between $5,000 and $
12.000 e estão no departamento 20 ou 50. Atribua às colunas os labels Employee e Monthly Salary, respectivamente.

SELECT last_name "Employee", salary "Monthly Salary"

FROM employees

WHERE salary BETWEEN 5000 AND 12000

AND department_id IN (20, 50);

7. The human resources department needs a report displaying the last name and the admission date.
of all the employees hired in 1994.

SELECT last_name, hire_date

FROM employees

WHERE hire_date LIKE '1994%';

8. Crie um relatório que exiba o sobrenome e o cargo de todos os funcionários não subordinados a um gerente.

SELECT last_name, job_id

FROM employees WHERE manager_id IS NULL;

9. Create a report to display the last name, salary, and commission of all employees who earn
commission. Sort the data in descending order of salary and commissions.

SELECT last_name, salary, commission_pct

FROM employees

WHERE commission_pct IS NOT NULL

ORDER BY salary DESC, commission_pct DESC;

10. Members of the human resources department wish to have more flexibility regarding inquiries.
created. They want a report that displays the last name and salary of employees who earn more than a
specified amount by the user after the prompt. (ONLY THE SCRIPT)

SELECT last_name, salary

FROM employees

WHERE salary > &sal_amt;

11. The human resources department wants to run reports based on a manager. Create a query.
that requests a manager ID from the user and generates the employee ID, surname, salary, and department of
employees of this manager. The human resources department wishes to have permission to classify the report in
a selected column. You can test the data with the following values: (ONLY THE SCRIPT)
ID do gerente = 103, classificado pelo sobrenome do funcionário:
ID do gerente = 201, classificado pelo salário:
ID do gerente = 124, classificado pelo ID do funcionário:

SELECT employee_id, last_name, salary, department_id

FROM employees

WHERE manager_id = &mgr_num

ORDER BY &order_col;

12. Show all last names of employees whose third letter of the name is 'a'.

SELECT last_name

FROM employees

WHERE last_name LIKE '__a%';

13. Display the surname of all employees that contain aee.

SELECT last_name

FROM employees

WHERE last_name LIKE '%a%'

AND last_name LIKE '%e%';

14. Exiba o sobrenome, o cargo e o salário de todos os funcionários cujo cargo seja representante de
sales (SA_REP) or stock clerk (ST_CLERK) whose salary is different from US$ 2,500, US$ 3,500 or US$ 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);

15. Build a query to display the surname, salary, and commission of all employees whose commission
be 20%.

SELECT last_name , salary , commission_pct

FROM employees

WHERE commission_pct = .20;

CLASS 03 (DATE, CHARACTER, AND NUMBER FUNCTIONS)

1. The human resources department requested a report of all employees and their respective IDs.
Display the last name concatenated with the job ID (separated by a comma and a space) and name the column
as Employee and Title.

SELECT CONCAT(last_name, ', ' , job_id) "Employee and Title"

FROM employees;

2. The human resources department needs a report to display the employee number, the
surname, the salary and the salary with a 15.5% increase (specified as an integer) of each employee.
Assign the label New Salary to the column.

SELECT employee_id, last_name, salary,

ROUND(salary * 1.155, 0) "New Salary"

FROM employees;

3. Modify the previous exercise to add a column that subtracts the old salary from the new salary.
Assign the label Increase to the column.

SELECT employee_id, last_name, salary

ROUND(salary * 1.155, 0) "New Salary",

ROUND(salary * 1.155, 0) - salary "Increase"

FROM employees;
4. Create a query that displays the last name and the length of the last name of all employees whose names
start with the letter J, A or M. Assign an appropriate label to each column. Classify the results by the
surnames of the employees.

SELECT last_name "Name"

LENGTH(last_name) "Length"

FROM employees

WHERE last_name LIKE 'J%'

OR last_name LIKE 'M%'

OR last_name LIKE 'A%'

ORDER BY last_name ;

5. Recreate the previous query so that the user is asked to provide the initial letter of the last name.
For example, if the user inputs H when a letter is requested, the output should show all employees whose
last names start with the letter H.

SELECT last_name "Name"

LENGTH(last_name) "Length"

FROM employees

WHERE last_name LIKE '&start_letter%'

ORDER BY last_name;

6. The human resources department wants to know the length of employment of each employee. For
for each employee, display the last name and calculate the number of months between today and the employee's hiring date.
Assign the label MONTHS_WORKED to the column. Sort the results by the number of months the employee has been.
employee. Round the number of months to the nearest whole number.

SELECT last_name, ROUND(datediff (curdate(), hire_date)/30,0) as MONTHS_WORKED

FROM employees

ORDER BY MONTHS_WORKED;

7. Create a report that produces this information only for employees with salaries between 2000 and
4000:
<employee's surname> receives <salary> monthly, but wishes for <3 times the salary>.

Assign the label Dream Salaries to the column.

SELECT CONCAT(last_name, 'receives ', salary, ' monthly, but desires ', salary * 3, '.')

as ‘Dream Salaries’

FROM employees WHERE salary BETWEEN 2000 and 4000;

8. Create a query that displays the last name and salary of all employees. Format the salary to define-
make it 15 characters long and pad it on the left with the $ symbol. Assign the label SALARY to the column.

SELECT last_name, LPAD(salary, 15, '$') SALARY FROM employees;

9. Display the last name, admission date, and end of the probation period (90 days after hiring) of everyone
the employees whose role is SALES REPRESENTATIVE (SA_REP). Assign the labels hiring date and end
from the experience to the respective columns. Format the dates to be displayed in the format '2000-july-23th'.

SELECT last_name,date_format(hire_date,'%Y-%M-%D') as 'data de contratacao',

date_format(hire_date+90, '%Y-%M-%D') as 'fim da experiencia'

FROM employees WHERE job_id='SA_REP';

NOTE: Function DATE_ADD: Correctly adds a certain number of days to a date.

DATE_ADD(<data>, INTERVAL <number> DAY);

SELECT last_name, date_format(hire_date,'%Y-%M-%D') as 'hire date'

date_format(date_add(hire_date,interval 90 day),'%Y-%M-%D') as 'fim da experiencia'

FROM employees WHERE job_id='SA_REP';

10. Create a query that displays the last names and commissions of the employees. If an employee does not earn
Commission, the information "No Commission" must be displayed. Assign the label COMM to the column.

SELECT last_name, COALESCE(commission_pct, 'No Commission') COMM FROM employees;

11. With a CASE function, create a query that displays the level of all employees based on the value of
column JOB_ID. Use this data:

Cargo Level

AD_PRES A

ST_MAN B

IT_PROG C

SA_REP D

ST_CLERK E

None of the previous options 0

SELECT job_id, CASE job_id

WHEN 'ST_CLERK' THEN 'E' WHEN 'SA_REP' THEN 'D'

WHEN 'IT_PROG' THEN 'C' WHEN 'ST_MAN' THEN 'B'

WHEN 'AD_PRES' THEN 'A ELSE '0' END GRADE FROM employees;
EXTRAS

1. For budgetary purposes, HR needs a report on the projected salary increases. The report must
display the employees who do not earn commission but will have a salary increase of 10% (round the salaries)
for the monetary format)
FORMAT:
The salary of <employee> after a 10% raise is <salary with raise>.

2. Crie um relatório contendo os funcionários , os salários e os respectivos tempos de emprega (em anos). Orden
the report by the length of employment of the employees. The employee who has been employed the longest should be in
start of the list.

3. Display the employees whose last names start with the letters J, K, L, or M (REQUIRED TO USE THE
FUNCTION IN). Sort by last name.

4. Create a report that displays the following employee data:

- EMPLOYEE FIRST AND LAST NAME (CONCATENATED AND IN UPPERCASE)

- Cargo

- Salary

- Hiring date (format 24/September/2009)

- Years of service (rounded to whole numbers)

- Participation in the company's profits based on time with the company, according to the following criteria:

Up to 13 years in the company: 20% of the salary

Up to 14 years at the company: 25% of the salary

Up to 15 years in the company: 30% of the salary

Up to 16 years in the company: 40% of the salary

More than 16 years in the company: 50% of the salary

- Employee level based on the position, according to the following criteria:

AD_PRES,AD_VP - 'HIGH MANAGEMENT'

AC_MGR,SA_MAN,ST_MAN,MK_MAN - "OPERATIONAL MANAGEMENT"

SA_REP,MK_REP - “NEGÓCIOS”

Other positions - 'SUPPORT'

The report should include employees hired between the years of 1990 and 1997.

The report should be ordered by time in the company in descending order and by salary in ascending order.
ANSWERS

1–

Select

concat('O salário de ',last_name,' depois de um aumento de 10% é ',round(salary*1.10,2)) as 'NOVO SALARIO'

from employees

where commission_pct is null;

2–

Select

last_name, salary, round(datediff(curdate(),hire_date)/365,0) as years

from employees

order by years desc ;

3–

Select last_name from employees

where

lower(last_name) like 'j%' or

lower(last_name) like 'k%' or

lower(last_name) like 'l%' or

lower(last_name) like 'm%'

order by last_name;

OU

Select last_name from employees

where

the first letter of last_name is in ('J','K','L','M')

order by last_name
4-

select

upper (concat (first_name,' ',last_name)) as employee,

job_id as cargo,

salary as salario,

date_format(hire_date,'%d/%M/%Y') as admissao,

round(datediff(curdate(),hire_date)/365,0) as tempo_de_casa,

case when round(datediff(curdate(),hire_date)/365,0) < 13 then salary*0.20

when round(datediff(curdate(), hire_date) / 365, 0) < 14 then salary * 0.25

when round(datediff(curdate(), hire_date) / 365, 0) < 15 then salary * 0.30

when round(datediff(curdate(),hire_date)/365,0) < 16 then salary*0.40

else salary*0.50

end

as Gratification,

case when lower(job_id) in('ad_pres','ad_vp') then 'HIGH MANAGEMENT'

when lower(job_id) in('ac_mgr','sa_man','st_man','mk_man') then 'OPERATIONAL MANAGEMENT'

when lower(job_id) in('sa_rep','mk_rep') then 'BUSINESS'

else 'SUPPORT'

end

as Escalao

FROM employees

where

year(hire_date) between 1990 and 1997

order by

length_of_service desc, salary ;