Please use following table information to answer the exercises. SQL> DESC DEPARTMENTS Name Null? Type ----------------------------------------- -------- ---------------------------- DEPARTMENT_ID NOT NULL NUMBER(4) DEPARTMENT_NAME NOT NULL VARCHAR2(30) MANAGER_ID NUMBER(6) LOCATION_ID NUMBER(4) SQL> DESC EMPLOYEES Name Null? Type ----------------------------------------- -------- ---------------------------- EMPLOYEE_ID NOT NULL NUMBER(6) FIRST_NAME VARCHAR2(20) LAST_NAME NOT NULL VARCHAR2(25) EMAIL NOT NULL VARCHAR2(25) PHONE_NUMBER VARCHAR2(20) HIRE_DATE NOT NULL DATE JOB_ID NOT NULL VARCHAR2(10) SALARY NUMBER(8,2) COMMISSION_PCT NUMBER(2,2) MANAGER_ID NUMBER(6) DEPARTMENT_ID NUMBER(4) 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.

Please use following table information to answer the exercises. SQL> DESC DEPARTMENTS Name Null? Type ----------------------------------------- -------- ---------------------------- DEPARTMENT_ID NOT NULL NUMBER(4) DEPARTMENT_NAME NOT NULL VARCHAR2(30) MANAGER_ID NUMBER(6) LOCATION_ID NUMBER(4) SQL> DESC EMPLOYEES Name Null? Type ----------------------------------------- -------- ---------------------------- EMPLOYEE_ID NOT NULL NUMBER(6) FIRST_NAME VARCHAR2(20) LAST_NAME NOT NULL VARCHAR2(25) EMAIL NOT NULL VARCHAR2(25) PHONE_NUMBER VARCHAR2(20) HIRE_DATE NOT NULL DATE JOB_ID NOT NULL VARCHAR2(10) SALARY NUMBER(8,2) COMMISSION_PCT NUMBER(2,2) MANAGER_ID NUMBER(6) DEPARTMENT_ID NUMBER(4) 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.

Database Systems: Design, Implementation, & Management

11th Edition

ISBN:9781285196145

Author:Steven, Steven Morris, Carlos Coronel, Carlos, Coronel, Carlos; Morris, Carlos Coronel and Steven Morris, Carlos Coronel; Steven Morris, Steven Morris; Carlos Coronel

Publisher:Steven, Steven Morris, Carlos Coronel, Carlos, Coronel, Carlos; Morris, Carlos Coronel and Steven Morris, Carlos Coronel; Steven Morris, Steven Morris; Carlos Coronel

Chapter11: Database Performance Tuning And Query Optimization

Section: Chapter Questions

Problem 2P: Problem 1 and 2 are based on the following query: SELECT EMP_LNAME, EMP_FNAME, EMP_AREACODE, EMP_SEX...

See similar textbooks

Related questions

Concept explainers

Question

SQL> DESC EMPLOYEES
Name Null? Type
----------------------------------------- -------- ----------------------------
EMPLOYEE_ID NOT NULL NUMBER(6)
FIRST_NAME VARCHAR2(20)
LAST_NAME NOT NULL VARCHAR2(25)
EMAIL NOT NULL VARCHAR2(25)
PHONE_NUMBER VARCHAR2(20)
HIRE_DATE NOT NULL DATE
JOB_ID NOT NULL VARCHAR2(10)
SALARY NUMBER(8,2)
COMMISSION_PCT NUMBER(2,2)
MANAGER_ID NUMBER(6)
DEPARTMENT_ID NUMBER(4)
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.

Expert Solution

Trending now

This is a popular solution!

Step by step

Solved in 2 steps

SEE SOLUTION Check out a sample Q&A here

Knowledge Booster

Learn more about

Need a deep-dive on the concept behind this application? Look no further. Learn more about this topic, computer-science and related others by exploring similar questions and additional content below.