Practice 1 (Basics)

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

2. 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.
DESCRIBE employees

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

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

5. 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 (Functions & Operators)
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.sql. Run your query.

2. Create a query to display the employee last name and department number for employee
number 176.

3. Display the last name and salary for all employees whose salary is not in the range of
5,000 and 12,000.

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. 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 first name, 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.

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’ anywhere in their last
name.

12. Display the last name, job, and salary for all employees whose job is sales
representative (SA_REP) or stock clerk (ST_CLERK) and whose salary is not
equal to $2,500, $3,500, or $7,000.

13. 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 number, last_name, salary, and salary
increased by 15% and expressed as a whole number. Label the column New Salary.

3. display the employee number, last_name, salary, and salary increased by 15% and expressed
as a whole number modify your query to add a column that subtracts the old salary from
the new salary. Label the column Increment.

4. 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 name 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.
5. 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.

6. Write a query that produces the following statement as a output for each employee:
<Employee first name> earns <salary> monthly but wants <3 times salary>. Label the
column Dream Salaries.
[Example:- smith earns 1000 monthly but wants 3000 (“Dream Salary’)]

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

8. Display the last name; hire date, and day of the week on which the employees get
hired. Label the column DAY.

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

10. 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
None of the above 0

11. Rewrite the statement in the preceding question using the CASE syntax.
Practice 4 (Aggregate)
1. 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.

2. Display the minimum, maximum, sum, and average salary for each job type.

3. Write a query to count the number of people with the same job.

4. Without listing them. Label the column Number of Managers.

5. Write a query that displays the difference between the highest and lowest salaries.
Label the column DIFFERENCE.
Practice 5 (Constraints)
1. Add a 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

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

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. 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.
 Practice 6 (Joins)
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 80. Include the
location of the department 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 anywhere
(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. Modify lab4_6.sql to display all employees including King, who has no manager.
Place your SQL statement in a text file named lab4_7.sql. Run the query in lab4_7.sql

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

8. 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.
Practice 7 (SubQuery)
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. Display the last name, department number, and job ID of all employees whose department
location ID is 1700.

4. Display the department number, last name, and job ID for every employee in the
Executive department.

5. Display the employee numbers, last names, and salaries of all employees who earn more
than the average salary.
Practice 8(view,index,sequence)
1. Create a view called EMPLOYEES_VU based on the employee numbers,
employee names, and department numbers from the EMPLOYEES table.
Change the heading for the employee name to EMPLOYEE.

2. Display the contents of the EMPLOYEES_VU view.

3. Using your EMPLOYEES_VU view, enter a query to display all employee

4. Create a sequence to be used with the primary key column of the DEPT
table. The sequence should start at 200 and have a maximum value of
1000. Have your sequence increment by ten numbers. Name the
sequence DEPT_ID_SEQ.

5. Create a unique index on the foreign key column (DEPT_ID) in the EMP table.