Practice 1

1. Initiate an iSQL*Plus session using the user ID and password provided by the
instructor.

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

3. The following SELECT statement executes successfully: SELECT last_name,

job_id, salary AS Sal FROM employees; True/False

4. The following SELECT statement executes successfully: SELECT * FROM

job_grades; True/False

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

6. Show the structure of the DEPARTMENTS table. Select all data from the
table.

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. Save your SQL statement to a file named
lab1_7.sql.

8. Run your query in the file lab1_7.sql.

9. Create a query to display unique job codes from the EMPLOYEES table.

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. Run
your query again.

11. Display the last name concatenated with the job ID, separated by a comma
and space, and name the column Employee and Title.

12. Create a query to display all the data from the EMPLOYEES table. Separate
each column by a comma. Name the column THE_OUTPUT.
Practice 2
1. Create a query to display the last name and salary of employees earning more
than $12,000. Place your SQL statement in a text file named lab2_1.sql. Run
your query.

2. Create a query to display the employee last name and department number for
employee number 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. Place your SQL statement in a
text file named lab2_3.sql.

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.

5. Display the last name and department number of all employees in

departments 20 and 50 in alphabetical order by 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

7. Display the last name and hire date of every employee who was hired in 1994.

8. Display the last name and job title of all employees who do not have a
manager.

9. Display the last name, salary, and commission for all employees who earn
commissions. Sort data in descending order of salary and commissions.

If you have time, complete the following exercises: 10. Display the last names of
all employees where the third letter of the name is an a.

11. Display the last name of all employees who have an a and an e in their last
name.

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.

13. Modify lab2_6.sql to display the last name, salary, and commission for all
employees whose commission amount is 20%.
Practice 3
1. Write a query to display the current date. Label the column Date.

2. For each employee, display the employee ID number, last_name, salary, and
salary increased by 15% and expressed as a whole number. Label the column
New Salary. Place your SQL statement in a text file named lab3_2.sql. 3

4. Modify your query lab3_2.sql to add a column that subtracts the old salary
from the new salary. Label the column Increase. Save the contents of the file as
lab3_4.sql. Run the revised query.

5. Write a query that displays the employee’ last names with the f irst letter
capitalized and all other s 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.

6. For each employee, display the employee’ last name, and calculate the
number of months between s 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. Note:
Your results will differ.

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.

8. Create a query to display the last name and salary for all employees. Format
the salary to be 15 characters long, left-padded with $. Label the column
SALARY.

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

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.

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.
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 doll ars. Sort the data in descending order of salary. Label the column
EMPLOYEES_AND_THEIR_SALARIES.

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 AD_PRES ST_MAN IT_PROG SA_REP ST_CLERK None of the above
Grade A B C D E 0

14. Rewrite the statement in the preceding question using the CASE syntax.
Practice 4,

1. Write a query to display the last name, department number, and department
name for all employees.

2. Create a unique listing of all jobs that are in department 30. Include the
location of department 90 in the output.

3. Write a query to display the employee last name, department name, location
ID, and city of all employees who earn a commission.

4. Display the employee last name and department name for all employees who
have an a (lowercase) in their last names. Place your SQL statement in a text file
named lab4_4.sql.

5. Write a query to display the last name, job, department number, and
department name for all employees who work in Toronto.

6. Display the employee last name and employee number along with their
manager’ last name and s manager number. Label the columns Employee,
Emp#, Manager, and Mgr#, respectively. Place your SQL statement in a text file
named lab4_6.sql.

7. Modify lab4_6.sql to display all employees including King, who has no

manager. Order the results by the employee number. Place your SQL statement
in a text file named lab4_7.sql. Run the query in lab4_7.sql.

8. Create a query that displays employee last names, department numbers, and
all the employees who work in the same department as a given employee. Give
each column an appropriate label.

9. Show the structure of the JOB_GRADES table. Create a query that displays
the name, job, department name, salary, and grade for all employees.

10. Create a query to display the name and hire date of any employee hired after
employee Davies.

11. Display the names and hire dates for all employees who were hired before
their managers, along with their manager’ names and hire dates. Label the
columns Employee, Emp s Hired, Manager, and Mgr Hired, respectively.
Practice 5
1. Group functions work across many rows to produce one result per group.
True/False

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

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

True/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. Place your SQL statement in a text file
named lab5_6.sql.

5. Modify the query in lab5_4.sql to display the minimum, maximum, sum, and
average salary for each job type. Resave lab5_4.sql to lab5_5.sql. Run the
statement in lab5_5.sql.

6. Write a query to display the number of people with the same job.

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.

8. Write a query that displays the difference between the highest a nd lowest
salaries. Label the column DIFFERENCE.

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 less than $6,000. Sort the output in
descending order of salary.

10. Write a query to display each department’ name, location, number of

employees, and the s 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.

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.

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.
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.

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.

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. Place your SQL statement in a text file named lab6_3.sql. Run your
query.

4. Display the last name, department number, and job ID of all employees whose
department location ID is 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.

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. Resave
lab6_3.sql to lab6_7.sql. Run the statement in lab6_7.sql.
Practice 7

Determine whether the following two statements are true or false :

1. The following statement is valid: DEFINE & p_val = 100 True/False

2. The DEFINE command is a SQL command. True/False

3. Write a script file to display the last names, job IDs, and hire dates for all
employees who started within a given range of dates. Concatenate the name and
job together, separated by a space and comma, and label the column
Employees. Use the DEFINE command to provide the two ranges. Use the
format MM/DD/YYYY. Save the script file as lab7_3.sql. DEFINE low_date =
01/01/1998 DEFINE high_date = 01/01/1999

4. Write a script to display the last names, job IDs, and department names for
every employee in a given location. The search condition should allow for case-
insensitive searches of the department location. Save the script file as
lab7_4.sql.

5. Modify the code in lab7_4.sql to create a report containing the department

name, employee last name, hire date, salary, and annual salary for each
employee in a given location. Label the columns DEPARTMENT NAME,
EMPLOYEE NAME, START DATE, SALARY, and ANNUAL SALARY, placing
the labels on multiple lines. Resave the script as lab7_5.sql, and execute the
commands in the script.
Practice 8

Insert data into the MY_EMPLOYEE table.

1. Run the statement in the lab8_1.sql script to build the MY_EMPLOYEE table
to be used for the lab.

2. Describe the structure of the MY_EMPLOYEE table to identify the column

names.

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.

ID 1 2 3 4 5

LAST_NAME Patel Dancs Biri Newman Ropeburn

FIRST_NAME Ralph Betty Ben Chad Audrey

USERID rpatel bdancs bbiri cnewman aropebur

SALARY 895 860 1100 750 1550

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.

5. Confirm your addition to the table.

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.

7. Populate the table with the next two rows of sample data by running the
INSERT statement in the script that you created.

8. Confirm your additions to the table.

9. Make the data additions permanent. Update and delete data in the
MY_EMPLOYEE table.

10. Change the last name of employee 3 to Drexler.

11. Change the salary to 1000 for all employees with a salary less than 900.

12. Verify your changes to the table.

13. Delete Betty Dancs from the MY_EMPLOYEE table.

14. Confirm your changes to the table.

15. Commit all pending changes. Control data transaction to the

MY_EMPLOYEE table.

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.

17. Confirm your addition to the table.

18. Mark an intermediate point in the processing of the transaction.

19. Empty the entire table.

20. Confirm that the table is empty.

21. Discard the most recent DELETE operation without discarding the earlier
INSERT operation.

22. Confirm that the new row is still intact.

23. Make the data addition permanent.

Practice 9

1. Create the DEPT table based on the following table instance chart. Place the
syntax in a script called lab9_1.sql, then execute the statement in the script to
create the table. Confirm that the table is created.

Column Name Key Type Nulls/Unique FK Table FK Column Data type Length

ID NUMBER 7
NAME VARCHAR2 25

2. Populate the DEPT table with data from the DEPARTMENTS table. Include
only columns that you need.

3. Create the EMP table based on the following table instance chart. Place the
syntax in a script called lab9_3.sql, and then execute the statement in the script
to create the table. Confirm that the table is created.

Column Name Key Type Nulls/Unique FK Table FK Column Data type Length

ID LAST_NAME FIRST_NAME DEPT_ID

NUMBER 7 VARCHAR2 25 VARCHAR2 25 NUMBER 7

4. Modify the EMP table to allow for longer employee last names. Confirm your
modification.

5. Confirm that both the DEPT and EMP tables are stored in the data dictionary.
(Hint: 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.
7. Drop the EMP table.
8. Rename the EMPLOYEES2 table as EMP.
9. Add a comment to the DEPT and EMP table definitions describing the tables.
Confirm your additions in the data dictionary.
10. Drop the FIRST_NAME column from the EMP table. Confirm your
modification by checking the description of the table.
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.
12. Drop all the UNUSED columns from the EMP table. Confirm your
modification by checking the description of the table.
Practice 10
1. Add a table-level PRIMARY KEY constraint to the EMP table on the ID
column. The constraint should be named at creation. Name the constraint
my_emp_id_pk. Hint: The constraint is enabled as soon as the ALTER TABLE
command executes successfully.

2. Create a PRIMARY KEY constraint to the DEPT table using the ID column.
The constraint should be named at creation. Name the constraint my_deptid_pk.
Hint: The constraint is enabled as soon as the ALTER TABLE command
executes successfully.

3. Add a column DEPT_ID to the EMP table. Add a foreign key reference on the
EMP table that ensures that the employee is not assigned to a nonexistent
department. Name the constraint my_emp_dept_id_fk.

4. Confirm that the constraints were added by querying the

USER_CONSTRAINTS view. Note the types and names of the constraints. Save
your statement text in a file called lab10_4.sql.

5. Display the object names and types from the USER_OBJECTS data dictionary
view for the EMP and DEPT tables. Notice that the new tables and a new index
were created.

6. Modify the EMP table. Add a COMMISSION column of NUMBER data type,
precision 2, scale 2. Add a constraint to the commission column that ensures that
a commission value is greater than zero.