1. Display the contents of the departments table. 2. Display the last name of all employees who work for department 100. 3. Display the first name and last name of all employees who work for department 100 and have a salary greater than 3500. 4. Display the first name and last name of all employees who work for department 100 or have a salary greater than 3500. 5. Display the last name and salary for all employees whose salary is not in the range of 5000 and 12000. 6. Display the full name (first and last) of all employees in ascending order according to their first name. 7. Display the full name (first and last) of all employees in descending order according to their salary. 8. Display the first name, the last name, the department id and the salary of all employees in ascending order according to their department and descending order according to their salary. 9. Display the last name of all employees with the heading “Employees”. 10. Display the first name and last name of all employees whose last name start with “A‟. 11. Display the last name and manager id of all departments whose name ends with “S‟ and managed by manager with the id 204. 12. Display the first name and salary*10000 of all employees. That is the salary stored in the table is multiplied by 10000. Note that this does not change the stored value. 13. Repeat exercise 12 but list only those employees whose job is “IT_PRO G‟. Alter the headings so that headings will be “name” and “salary in TL”. 14. Display the first name, last name and salary of all employees hired after “01-JAN-2006‟. 15. Display the first name, last name and hire date of all employees who were hired between the dates “01-JAN- 2005” and “31-DEC-2005”. 16. Display the last name and department id of all employees in departments 20 or 50 in ascending order according to their last name. 17. Display the last name and salary of employees who earn between 5000 and 12000 and are in department 20 or 50. 18. Display the last name and hire date of every employee who was hired in 2014. 19. Display the last name and job id of all employees who do not have a manager. 20. Display the last name and job id of all employees who have a manager. 21. Display the last name, salary, and commission for all employees who earn commissions. Sort data in descending order of salary and commissions. 22. Find the average salary of all employees. 23. Find the minimum salary of all employees. 24. Find the sum of all salaries of all employees.
SQL
Table:
CREATE TABLE employees (
employee_id INT PRIMARY KEY,
first_name VARCHAR(255),
last_name VARCHAR(255),
email VARCHAR(255) UNIQUE,
phone_number VARCHAR(255),
hire_date DATE,
job_id VARCHAR(255),
salary DECIMAL(10,2),
commission_pct DECIMAL(5,2),
manager_id INT,
department_id INT,
FOREIGN KEY (manager_id) REFERENCES employees(employee_id),
FOREIGN KEY (department_id) REFERENCES departments(department_id)
);
CREATE TABLE departments (
department_id INT PRIMARY KEY,
department_name VARCHAR(255),
manager_id INT,
FOREIGN KEY (manager_id) REFERENCES employees(employee_id)
);
INSERT INTO employees (employee_id, first_name, last_name, email, phone_number, hire_date, job_id, salary, commission_pct, manager_id, department_id)
VALUES (1, 'John', 'Doe', 'john.doe@example.com', '555-555-5555', '2022-01-01', 'IT_PROG', 8000, 0.2, 3, 10),
(2, 'Jane', 'Smith', 'jane.smith@example.com', '555-555-5556', '2022-02-01', 'IT_PROG', 9000, 0.3, 3, 20),
(3, 'Bob', 'Johnson', 'bob.johnson@example.com', '555-555-5557', '2022-03-01', 'IT_PROG', 10000, 0.1, NULL, 30);
INSERT INTO departments (department_id, department_name, manager_id)
VALUES (10, 'IT', 3),
(20, 'HR', 2),
(30, 'Marketing', 1);
Exercises
1. Display the contents of the departments table.
2. Display the last name of all employees who work for department 100.
3. Display the first name and last name of all employees who work for department 100 and have a salary greater
than 3500.
4. Display the first name and last name of all employees who work for department 100 or have a salary greater
than 3500.
5. Display the last name and salary for all employees whose salary is not in the range of 5000 and 12000.
6. Display the full name (first and last) of all employees in ascending order according to their first name.
7. Display the full name (first and last) of all employees in descending order according to their salary.
8. Display the first name, the last name, the department id and the salary of all employees in ascending order
according to their department and descending order according to their salary.
9. Display the last name of all employees with the heading “Employees”.
10. Display the first name and last name of all employees whose last name start with “A‟.
11. Display the last name and manager id of all departments whose name ends with “S‟ and managed by manager
with the id 204.
12. Display the first name and salary*10000 of all employees. That is the salary stored in the table is multiplied
by 10000. Note that this does not change the stored value.
13. Repeat exercise 12 but list only those employees whose job is “IT_PRO G‟. Alter the headings so that
headings will be “name” and “salary in TL”.
14. Display the first name, last name and salary of all employees hired after “01-JAN-2006‟.
15. Display the first name, last name and hire date of all employees who were hired between the dates “01-JAN-
2005” and “31-DEC-2005”.
16. Display the last name and department id of all employees in departments 20 or 50 in ascending order
according to their last name.
17. Display the last name and salary of employees who earn between 5000 and 12000 and are in department 20
or 50.
18. Display the last name and hire date of every employee who was hired in 2014.
19. Display the last name and job id of all employees who do not have a manager.
20. Display the last name and job id of all employees who have a manager.
21. Display the last name, salary, and commission for all employees who earn commissions. Sort data in
descending order of salary and commissions.
22. Find the average salary of all employees.
23. Find the minimum salary of all employees.
24. Find the sum of all salaries of all employees.
Trending now
This is a popular solution!
Step by step
Solved in 2 steps
