SQL Practice Questions
SQL interview Questions also include in
this questions.
This Question seems to be simple in the
beginning but toughness increase gradually, please try all the levels. Solutions for questions are at the end or Click Here!
Moreover, to practice this questions use
Oracle Database 11g with Sample Schemes which consists of tables like
EMPLOYEES, DEPARTMENT,JOB_GRADES etc., and will facing a interview use Oracle
Apps Tables like AP_INVOICES_ALL,PO_HEADERS_ALL,AP_CHECKS_ALL etc.,instead of
this tables for experienced candidates.
Tools Used : SQL*Plus
More Interviews questions will updated
regularly, so visit for updates.
Practice 1 : Questions on SELECT statement
1. The following SELECT statement executes
successfully or not?
SELECT last_name, job_id, salary AS Sal
FROM employees;
2. The following SELECT statement executes
successfully or not?
SELECT *
FROM job_grades;
3. There are four coding errors in this
statement. Can you identify them?
SELECT employee_id, last_name
sal x 12 ANNUAL SALARY
FROM employees;
4. Show the structure of the DEPARTMENTS
table. Select all data from the DEPARTMENTS table.
5. 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. Save your SQL statement to a file
named lab1_7.sql.
6. Create a query to display unique job
codes from the EMPLOYEES table.
If you have time, complete the following
exercises:
7. 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.
8. Display the last name concatenated with
the job ID, separated by a comma and space, and name
the column Employee and Title.
If you want an extra challenge, complete
the following exercise:
9. Create a query to display all the data
from the EMPLOYEES table. Separate each column by a
* * * * * * * * * * * *
Practice 2 : Restricting
and Sorting Data
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. Resave lab2_3.sql as
lab2_6.sql. Run the statement in
lab2_6.sql.
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.
If you want an extra challenge, complete
the following exercises:
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%. Resave
lab2_6.sql as lab2_13.sql. Rerun the
statement in lab2_13.sql.
Solutions for questions Click Here!
* * * * * * * * * * * *
Solutions for questions Click Here!
* * * * * * * * * * * *
Practice 3 : Single-Row Functions
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. Place your SQL statement in
a text file named lab3_2.sql.
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. Save the contents
of the file as lab3_4.sql.
Run the revised query.
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 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.
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.
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.
If you have time, complete the following exercises:
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’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.”
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.
If you want an extra challenge, complete the following
exercises:
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 dollars. 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 GRADE
AD_PRES A
ST_MAN B
IT_PROG C
SA_REP D
ST_CLERK E
None of the above 0
14. Rewrite the statement in the preceding question using
the CASE syntax.
* * * *
* * * * * *
* * * *
Practice 4 : Displaying Data
from Multiple Tables
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
(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’s last name and
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.
Place your SQL statement in a text file named lab4_7.sql.
Run the query in lab4_7.sql
If you have time, complete the following exercises.
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.
If you want an extra challenge, complete the following
exercises:
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’s names and hire dates. Label the columns
Employee, Emp
Hired, Manager, and Mgr Hired, respectively.
Solutions for questions Click Here!
Practice 5 : Aggregating Data
Using Group Functions
Determine the validity of the following three statements.
Circle either True or False.
1. Group functions work across many rows to produce one
result.
2. Group functions include nulls in calculations.
3. The WHERE clause restricts rows prior to inclusion in a
group calculation.
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_6.sql to lab5_4.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 and lowest salaries. Label the
column DIFFERENCE.
If you have time, complete the following exercises.
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.
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.
If you want an extra challenge, complete the following
exercises:
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.