Chapter 1

Practice 1
1. Initiate an iSQL*Plus session using the user ID and password provided by the instructor.
  User_id   :  scott
  Password: tiger

2. iSQL*Plus commands access the database.
True/False
True

3. The following select statement executes successfully:
select last_name, job_id, salary AS Sal FROM employees;
True/False
True
 
4. The following select statement executes successfully:
select *
FROM job_grades;
True/False
False

5. There are four coding errors in this statement. Can you identify them?
select employee_id, last_name
sal x 12 ANNUAL SALARY
FROM employees;
False

6. Show the structure of the DEPARTMENTS table. select all data from the table.
1.desc   departments;
2.  select  * 
    FROM      departments;


7. Show the structure of the EMPLOYEES table. create 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.
desc employees;


8. Run your query in the file lab1_7.sql.
select employee_id, last_name, job_id, hire_date  "Start Date" 
FROM    employees;

9. create a query to display unique job codes from the EMPLOYEES table.
select   job_id 
FROM      employees;


10. Copy the statement from lab1_7.sql into the iSQL*Plus Edit window. Name the column headings Emp #, Employee, Job, and Hire Date, respectively.
select  employee_id  "emp#", last_name  "Employee" ,job_id "Job",
                  hire_date "hire date" 
FROM      employees;

11. Display the last name concatenated with the job ID, separated by a comma and space, and name the column Employee and Title.
select  last_name || ',' || job_id "employee and title" 
FROM     employees;

12. create a query to display all the data from the EMPLOYEES table. Separate each column by a comma. Name the column THE_OUTPUT
select    EMPLOYEE_ID||','
                   ||FIRST_NAME||','||LAST_NAME||','||EMAIL||','
                   ||PHONE_NUMBER||','||HIRE_DATE||','||JOB_ID||','
                   ||SALARY||','||COMMISSION_PCT||  ','
                   ||MANAGER_ID||','||DEPARTMENT_ID   "THE _OUTPUT" 
FROM       employees;





Chapter 2

Practice 2
1. create a query to display the last name and salary of employees earning more than $12,000.
select     last_name ,salary  
FROM         employees 
WHERE      salary >12000;

2. create a query to display the employee last name and department number for employee number
176
select  last_name,department_id
FROM      employees
WHERE   employee_id=176;


3. Modify lab2_1.sql to display the last name and salary for all employees whose salary is not in the range of $5,000 and $12,000. 
select    last_name,salary 
FROM        employees 
WHERE     salary not between 12000 and 55000;

4. Display the employee last name, job ID, and start date of employees hired between February 20, 1998, AND May 1, 1998. Order the query in ascending order by start date.
select  last_name,job_id ,hire_date
FROM      employees 
WHERE   hire_date between '20-feb-98' AND '01-may-98';


5. Display the last name and department number of all employees in departments 20 AND 50 in alphabetical order by name.
select         last_name ,department_id
FROM            employees
WHERE         department_id in(20,50)
ORDER  BY   last_name;

6. Modify lab2_3.sql to list the last name and salary of employees who earn between $5,000 and $12,000, and are in department 20 or 50. Label the columns Employee and Monthly Salary, respectively.
select      last_name "employee",salary "monthly salary" 
FROM         employees 
WHERE      salary between 12000 and 5000
AND            department_id in(20,50);


7. Display the last name and hire date of every employee who was hired in 1994.
select     last_name , hire_date 
FROM         employees
WHERE      hire_date like'%94';

8. Display the last name and job title of all employees who do not have a manager.
select  last_name ,job_id 
FROM      employees 
WHERE   manager_id is null;

9. Display the last name, salary, and commission for all employees who earn commissions. Sort 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;


10. Display the last names of all employees where the third letter of the name is an a.
select     last_name 
FROM        employees 
WHERE     last_name like'__a%';





11. Display the last name of all employees who have an a and an e in their last name.
select   last_name 
FROM      employees 
WHERE   last_name like'%a%'
OR            last_name like '%e%';

12. Display the last name, job, and salary for all employees whose job is sales representative or stock 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 like'ST_CLERK'
OR               job_id like 'SA_REP'
AND            salary not in(2005,3500,7000);


13. Modify lab2_6.sql to display the last name, salary, and commission for all employees whose commission amount is 20%. 
select  last_name "Employee",salary "Monthly Salary",commission_pct
FROM      employees 
WHERE   commission_pct =.2



















Chapter 3

Practice 3
1.Write a query to display the current date. Label the column Date.
select  sysdate “data”
FROM      employees;

2. FOR each employee, display the employee number, last_name, salary, AND salary increased by 15% and expressed as a whole number. Label the column New Salary. 
select   EMPLOYEE_ID  , LAST_NAME   , salary,salary*1.15  
                   "NEW _SALARY"
FROM        employees;

3. Run your query in the file lab3_2.sql.

4. Modify your query lab3_2.sql to add a column that subtracts the old salary from  the new salary. Label the column Increase. 
select  EMPLOYEE_ID,LAST_NAME, salary,
        salary*1.15 "NEW _  SALARY",salary*1.15- salary "Increase"
FROM      employees; 

5. Write a query that displays the employee’s last names with the first letter capitalized and all other letters lowercase, and the length of the names, for all employees whose name starts with J, A, or M. Give each column an appropriate label. Sort the results by the employees’ last_names. 
select       initcap (last_name) "name",length(last_name) "length"
FROM           employees  
WHERE        last_name like 'A%'
OR                 last_name like 'J%'
OR                 last_name like 'M%'
ORDER BY  last_name;





6. for each employee, display the employee’s last name, and calculate the number of months between today and the date the employee was hired. Label the column MONTHS_WORKED.
order your results by the number of months employed. Round the number of months up to the closest whole number.
select        last_name ,
             round (months_between(sysdate,
             hire_date),0)"MONTHS_WORKE"
FROM           employees
ORDER BY   round (months_between( sysdate ,hire_date),0);

 
7. Write a query that produces the following for each employee:
<employee last name> earns <salary> monthly but wants <3 times
salary>. Label the column Dream Salaries.
select  last_name ||'earns' || to_char(salary,'$99,999.00') ||'monthly but
                  wents'||to_char(3*   salary,'$99,999.00') "dream salaries"
FROM     employees; 

8. create a query to display the last name and salary for all employees. format the salary to be characters long, left-padded with $. Label the column SALARY. 
select  last_name, lpad (salary,15,'$') "salary"
FROM     employees;

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 "the" fmDdspth"of" month yyyy') "review"
FROM         employees;

10. Display the last name, hire date, and day of the week on which the employee started. Label the column DAY. order the results by the day of the week starting with Monday.
select        last_name ,hire_date,to_char(hire_date,'day') "day"
FROM           employees
ORDER BY   to_char(hire_date,'day')  ; 
11. create a query that displays the employees’ last names and commission amounts. If an employee does not earn commission, put “No Commission.” Label the column COMM.
select  last_name ,nvl (to_char(commission_pct),'NO COMMISSION') 
FROM      employees; 

 
12. create a query that displays the employees’ last names and indicates the amounts of  their annual 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  rpad(last_name,trunc(salary/1000,0)+length(last_name),'*')
                  EMPLOYEES_AND_THEIR_SALARIES
FROM     employees;;


13. Using the DECODE function, write a query that displays the grade of all employees based on the value of the column JOB_ID, as per the following data:
Job Grade
AD_PRES A
ST_MAN B
IT_PROG C
SA_REP D
ST_CLERK E
select  job_id ,
Decode     (job_id,     'AD_PRES','A',
             	               'ST_MAN','B',
             	               'IT_PROG','C', 
                                    'SA_REP','D', 
         		               'ST_CLERK','E',
                                                            '0')         
FROM   employees;
    





Chapter 4

Practice 4
1. 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;

2. create a unique listing of all jobs that are in department 80. Include the location of the department in the output.
select   e.job_id , d.location_id  
FROM      employees e, departments d 
WHERE   d.department_id= 80  
AND          e.department_id = d.department_id;


3. Write a query to display the employee last name, department name, location ID, and city of all employees who earn a commission.
select   e.last_name,d.department_name,l.location_id,l.city  
FROM       employees e, departments d ,locations l
WHERE    e.department_id = d.department_id
AND          d.location_id=l.location_id
AND          commission_pct is not null ;

4. Display the employee last name and department name for all employees who have an a (lowercase) in their last names.
select    e.last_name,d.department_name
FROM       employees e, departments d 
WHERE    e.department_id = d.department_id
AND           last_name like '%a%' ;

 
  5. Write a query to 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    l.city like 'Toronto'
AND          e.department_id=d.department_id;
Chapter 5

Practice 5
Determine the validity of the following three statements. Circle either True or False.
1. Group functions work across many rows to produce one result per group.
True/False
True

2. Group functions include nulls in calculations.
True/False
False

3. The WHERE clause restricts rows prior to inclusion in a group calculation.
True/False
False

4. Display 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     max(salary)"Maximum",min(salary)"Minimum",
                    sum (salary)"Sum", avg(salary)"Average"
FROM        employees;

5. Modify the query in lab5_4.sql to display the minimum, maximum, sum, and average salary for each job type. 
select        job_id,max(salary)"Maximum",min(salary)"Minimum",
                        sum (salary)"Sum", avg(salary)"Average"
FROM            employees
GROUP BY   job_id;

6. Write a query to display the number of people with the same job.
select        job_id,count(employee_id)
FROM           employees
GROUP BY   job_id;;


7. Determine the number of managers without listing them. Label the column Number of Managers. Hint: Use the MANAGER_ID column to determine the number of managers.
select   count (DISTINCT(manager_id))  " Number of Manager"
FROM       employees


8. Write a query that displays the difference between the highest and 
lowest salaries. Label the column DIFFERENCE.
select    max(salary)-min(salary) difference
FROM       employees;;


9. Display the manager number and the salary of the lowest paid employee for that manager. Exclude anyone whose manager is not known. Exclude any groups WHERE the minimum salary is $6,000 or less. Sort the output in descending Order of salary.
select        manager_id ,min(salary)
FROM           employees
HAVING       min(salary)>=6000
AND               manager_id is not null
GROUP BY   manager_id
ORDER BY   min(salary) DESC;


10. Write a query to display each department’s name, location, number of employees, and the average salary for all employees in that department. Label the columns Name, Location, Number of People, and Salary, respectively. Round the average salary to two decimal places.
select        d.department_name "Name",d.location_id "Location",
                       count(e.employee_id) "Nember of People",avg(e.salary) 
                        "Salary"
FROM           departments d,employees e
WHERE        d.department_id= e.department_id
GROUP BY  d.department_name,location_id ;


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(employee_id) "Total", 
                   sum(decode(to_char(hire_date,'yyyy'),1995,1,0))"1995",
                   sum(decode(to_char(hire_date,'yyyy'),1996,1,0))"1996",
                   sum(decode(to_char(hire_date,'yyyy'),1997,1,0))"1997",
                   sum(decode(to_char(hire_date,'yyyy'),1998,1,0))"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 "Job", 
                       sum(decode (department_id,20,salary,0))"dept 20",
                       sum(decode (department_id,50,salary,0))"dept 50",
                       sum(decode (department_id,80,salary,0))"dept 80",
                       sum(decode (department_id,90,salary,0))"dept 90",
                       sum(salary)
FROM            employees 
GROUP BY  job_id;

Chapter 6

Practice 6
1. Write a query to display the last name and hire date of any employee in the same department as Zlotkey. Exclude Zlotkey.
select  last_name hire_date,department_id
FROM     employees 
WHERE  department_id =(select  department_id
                                            FROM      employees
                                            WHERE   last_name like 'Zlotkey')
AND         last_name not like 'Zlotkey';


2. create a query to display the employee numbers and last names of all employees who earn more than the average salary. Sort the results in ascending Order of salary.
select   last_name ,employee_id,salary
FROM      employees
WHERE   salary > (select  avg(salary)
                                   FROM    employees)

3. Write a query that displays the employee numbers and last names of all employees who work in a department with any employee whose last name contains a u.  
select    employee_id,last_name
FROM        employees
WHERE     department_id in(select  department_id 
                                                 FROM      employees
                                                 WHERE   last_name like'%u%');


4. Display the last name, department number, and job ID of all employees whose department location ID is 1700
select    last_name,department_id,job_id
FROM       employees
WHERE    department_id in(select   department_id 
                                                FROM      departments
                                                WHERE    location_id =1700);

5. Display the last name and salary of every employee who reports to King.



6. Display the department number, last name, and job ID for every employee in the Executive department.
select  department_ id , last _ name ,job_id
FROM      employees
WHERE   job_id  like 'AD_%';

7. Modify the query in lab6_3.sql to display the employee numbers, last names, and salaries of all employees who earn more than the average salary and who work in a department with any employee with a u in their name.
select   employee _ id, last_ name , salary , department _id
FROM      employees
WHERE   salary >(select   avg(salary)
                                 FROM      employees)
AND         department _ id in (select    department _ id
                                                  FROM       employees
                                                 WHERE     last_name like '%u%');




Chapter 8

Practice 8
1. Run the statement in the lab8_1.sql script to build the MY_EMPLOYEE table to be used for the lab.
create table my_employees 
                (id number(4),last_name varchar2(25),
                first_name varchar2(25),userid varchar2(8),
                salary number(9,2));


2. Describe the structure of the MY_EMPLOYEE table to identify the column names.
DESC  my_employees

3. Add the first row of data to the MY_EMPLOYEE table from the following sample data. Do not list the columns in the insert clause.
insert  INTO my_employees 
VALUES         (1,'Patel','Ralph', 'rpatel' ,895);

4. Populate the MY_EMPLOYEE table with the second row of sample data from the preceding list.
This time, list the columns explicitly in the insert clause.
insert    INTO my_employees(id,last_name ,first_name,userid,salary) 
VALUES (2,' Dancs', 'Betty',' bdancs', 860);

5. Confirm your addition to the table.
select  *
FROM     my_employees;

6. Write an insert statement in a text file named loademp.sql to load rows into the MY_EMPLOYEE table. Concatenate the first letter of the first name and the first seven characters of the last name to produce the user ID.
insert    INTO  my_employees
VALUES              (&id,' &frist_mame',
                                '&last_name','lower(substr(&frist_name,1,1))|| 
                               'lower( substr(&last_name,1,7)) ', &salary);



7. Populate the table with the next two rows of sample data by running the insert statement in the
script that you created.
insert    INTO  my_employees
VALUES              (&id,' &frist_mame',
                                '&last_name','lower(substr(&frist_name,1,1))|| 
                               'lower( substr(&last_name,1,7)) ', &salary);


8. Confirm your additions to the table.
select  * 
FROM     my_employees;

9. Make the data additions permanent.
COMMIT;

10. Change the last name of employee 3 to Drexler.
update   my_employees 
SET            last_name = 'Drexler'
WHERE     id=3;

11. Change the salary to 1000 for all employees with a salary less than 900.
update   my_employees 
SET            salary= 1000
WHERE    salary <900;

12. Verify your changes to the table.
select * 
FROM     my_employees;

13. delete Betty Dancs from the MY_EMPLOYEE table.
delete FROM  my_employees 
WHERE                 first_name like 'Betty';

14. Confirm your changes to the table
select * 
FROM     my_employees;



15. Commit all pending changes.
COMMIT;

16. Populate the table with the last row of sample data by modifying the statements in the script that you created in step 6.
 Run the statements in the script.
insert    INTO  my_employees
VALUES              (&id,' &frist_mame',
                                '&last_name','lower(substr(&frist_name,1,1))|| 
                               'lower( substr(&last_name,1,7)) ', &salary);

17. Confirm your addition to the table.
select * 
FROM     my_employees;

18. Mark an intermediate point in the processing of the transaction.
SAVEPOINT  b;

19. Empty the entire table.
delete FROM  my_employees

20. Confirm that the table is empty.
select * 
FROM     my_employees;

21. Discard the most recent delete operation without discarding the earlier insert operation.
ROLLBACK  TO b;

22. Confirm that the new row is still intact.
select * 
FROM     my_employees;

23. Make the data addition permanent.
COMMIT;




Chapter 9

Practice 9
1. create the DEPT table based on the following table instance chart. 
Confirm that the table is created.
create  TABLE   dept
                      (ID number(7),NAME varchar2(25));
DESC          dept;

2. Populate the DEPT table with data from the DEPARTMENTS table. Include only columns that you need.
create   TABLE  dept 
AS                  select department_id,department_name
                       FROM     departments;

3. create the EMP table based on the following table instance chart.
Confirm that the table is created
create  TABLE  emp (id number(7),last_name varchar2(25),
                   first_name varchar2(25),dept_id number(7)); 
DESC emp;

4. Modify the EMP table to allow for longer employee last names. Confirm your modification.
ALTER  TABLE  emp 
MODIFY         (last_name varchar2(50)); 
DESC   emp;

5. Confirm that both the DEPT and EMP tables are stored in the data dictionary. (Hint: USER_TABLES)
select  table_name
FROM     user_tables;







6. create the EMPLOYEES2 table based on the structure of the EMPLOYEES table. Include only the EMPLOYEE_ID, FIRST_NAME, LAST_NAME, SALARY, and DEPARTMENT_ID columns.
 Name the columns in your new table ID, FIRST_NAME, LAST_NAME, SALARY , and DEPT_ID, respectively.


create  TABLE employees2 
AS       select employee_id "ID",first_name "FIRST_NAME" ,
            last_name "LAST_NAME", 
            salary "SALARY" ,department_id "DEPT_ID" 
FROM   employees;
DESC    employees2

7. drop the EMP table.
drop  TABLE  emp;

8. Rename the EMPLOYEES2 table as EMP.
RENAME employees TO emp;

9. Add a comment to the DEPT and EMP table definitions describing the tables. Confirm your additions in the data dictionary.
COMMENT  ON  TABLE emp 
IS     'employees information';
COMMENT  ON TABLE dept 
IS     'department information';
select * 
FROM     user_tab_comments;

10. drop the FIRST_NAME column from the EMP table. Confirm your modification by checking the description of the table.
ALTER  TABLE    emp
drop  COLUMN  first_name;
DESC  emp



11. In the EMP table, mark the DEPT_ID column in the EMP table as UNUSED. Confirm your modification by checking the description of the table.
ALTER  TABLE emp
SET    UNUSED (dept_id);
DESC emp;

12. drop all the UNUSED columns from the EMP table. Confirm your modification by checking the description of the table.
ALTER  TABLE emp
drop  UNUSED columns;
DESC emp