Skip to content Skip to main navigation Skip to footer

Ensuring Quality Query Results in SQL

Objectives

This lesson covers the following objectives:

  • Create a query to produce specified data
  • SQL Query Optimization: Modify a query to produce specified data

Purpose of SQL Query Optimization

  • You’ve learned the syntax rules for generating a SQL query but are you sure you are producing the desired data?
  • Looking at the desired output and then figuring out the query to generate that output helps you to gain confidence that your query results are what you expect.

Write the Query

  • Problem:
    • Create a list of all tables whose first two characters in the name of the table is JO.
    • The tables must be owned by the current Oracle User.
  • Tables Used:
    • User_tables
SQL Query Optimization
  • Problem:
    • Create a list that includes the first initial of every employee’s first name, a space, and the last name of the employee.
  • Tables Used:
    • Employees
Ensuring Quality Query Results
  • Problem:
    • Create a list of every employee’s first name concatenated to a space and the employee’s last name, and the email of all employees where the email address contains the string ‘IN’.
  • Tables Used:
    • Employees
Write the Query
  • Problem:
    • Create a list of ‘smallest’ last name and the ‘highest’ last name from the employees table.
  • Tables Used:
    • Employees
Query Result:
  • Problem:
    • Create a list of weekly salaries from the employees table where the weekly salary is between 700 and 3000.
    • The salaries should be formatted to include a $-sign and have two decimal points like: $9999.99.
  • Tables Used:
    • Employees
Write the Query
  • Problem:
    • Create a list of every employee and his related job title sorted by job_title.
  • Tables Used:
    • Employees, Jobs
Write the Query
  • Problem:
    • Create a list of every employee’s job, the salary ranges within the job, and the employee’s salary.
    • List the lowest and highest salary range within each job with a dash to separate the salaries like this: 100 – 200.
  • Tables Used:
    • Employees, Jobs
Create a list of every employee's job, the salary ranges within the job, and the employee's salary.
  • Problem:
    • Using an ANSII join method, create a list of every employee’s first initial and last name, and department name.
    • Make sure the tables are joined on all of the foreign keys declared between the two tables.
  • Tables Used:
    • Employees, Departments
Using an ANSII join method, create a list of every employee's first initial and last name, and department name.
  • Problem:
    • Change the previous listing to join only on the department_id column.
  • Tables Used:
    • Employees, Departments
  • Problem:
    • Create a list of every employee’s last name, and the word nobody or somebody depending on whether or not the employee has a manager.
    • Use the Oracle DECODE function to create the list.
  • Tables Used:
    • Employees
Write the Query
  • Problem:
    • Create a list of every employee’s first initial and last name, salary, and a yes or no to show whether or not an employee makes a commission.
    • Fix this query to produce the result.
  • QUERY:
SELECT SUBSTR(first_name,1 1)||' '|last_name,
"Employee Name", salary "Salary",
DEC(commission_pct NULL, 'No', 'Yes')'Commission'
FROM employees;
Create a list of every employee's first initial and last name, salary, and a yes or no to show whether or not an employee makes a commission.
  • Problem:
    • Create a list of every employee’s last name, department name, city, and state_province.
    • Include departments without employees.
    • An outer join is required.
  • Tables Used:
    • Employees, Departments, Locations
Create a list of every employee's last name, department name, city, and state_province.
  • Problem:
    • Create a list of every employee’s first and last names, and the first occurrence of: commission_pct, manager_id, or -1.
    • If an employee gets commission, display the commission_pct column; if no commission, then display his manager_id; if he has neither commission nor manager, then the number -1.
  • Tables Used:
    • Employees
Create a list of every employee's first and last names, and the first occurrence of
  • Problem:
    • Create a list of every employee’s last name, salary, and job_grade for all employees working in departments with a department_id greater than 50.
  • Tables Used:
    • Employees, job_grades
Create a list of every employee's last name, salary, and job_grade for all employees working in departments with a department_id greater than 50.
  • Problem:
    • Produce a list of every employee’s last name and department name.
    • Include both employees without departments, and departments without employees.
  • Tables Used:
    • Employees, Departments
Produce a list of every employee's last name and department name.
  • Problem:
    • Create a treewalking list of every employee’s last name, his manager’s last name, and his position in the company.
    • The top level manager has position 1, this manager’s subordinates position 2, their subordinates position 3, and so on.
    • Start the listing with employee number 100.
  • Tables Used:
    • Employees
  • Problem:
    • Produce a list of the earliest hire date, the latest hire date, and the number of employees from the employees table.
  • Tables Used:
    • Employees
Produce a list of the earliest hire date, the latest hire date, and the number of employees from the employees table.
  • Problem:
    • Create a list of department names and the departmental costs (salaries added up).
    • Include only departments whose salary costs are between 15000 and 31000, and sort the listing by the cost.
  • Tables Used:
    • Employees, Departments
Create a list of department names and the departmental costs (salaries added up).
  • Problem:
    • Create a list of department names, the manager id, manager name (employee last name) of that department, and the average salary in each department.
  • Tables Used:
    • Employees, Departments
Create a list of department names, the manager id, manager name (employee last name) of that department, and the average salary in each department.
  • Problem:
    • Show the highest average salary for the departments in the employees table.
    • Round the result to the nearest whole number.
  • Tables Used:
    • Employees
Show the highest average salary for the departments in the employees table.
  • Problem:
    • Create a list of department names and their monthly costs (salaries added up).
  • Tables Used:
    • Employees, Departments
Create a list of department names and their monthly costs (salaries added up).
  • Problem:
    • Create a list of department names, and job_ids.
    • Calculate the monthly salary cost for each job_id within a department, for each department, and for all departments added together.
  • Tables Used:
    • Employees, Departments
Create a list of department names, and job_ids.
  • Problem:
    • Expand the previous list to also show if the department_id or job_id was used to create the subtotals shown in the output. (Hint: Cube, Grouping)
  • Tables Used:
    • Employees, Departments
Expand the previous list to also show if the department_id or job_id was used to create the subtotals shown in the output. (Hint: Cube, Grouping)
  • Problem:
    • Create a list that includes the monthly salary costs for each job title within a department.
    • In the same list, display the monthly salary cost per city. (Hint: Grouping Sets)
  • Tables Used:
    • Employees, Departments, Locations
Create a list that includes the monthly salary costs for each job title within a department.
  • Problem:
    • Create a list of employee names as shown and department ids.
    • In the same report, list the department ids and department names. And finally, list the cities.
    • The rows should not be joined, just listed in the same report. (Hint: Union)
  • Tables Used:
    • Employees, Departments, Locations
Create a list of employee names as shown and department ids.
  • Problem:
    • Create a list of each employee’s first initial and last name, salary, and department name for each employee earning more than the average for his department.
  • Tables Used:
    • Departments, Employees
Create a list of each employee's first initial and last name, salary, and department name for each employee earning more than the average for his department.

Summary

In this lesson, you should have learned how to:

  • Create a query to produce specified data
  • Modify a query to produce specified data

Was This Article Helpful?

0
0 Comments

There are no comments yet

Leave a comment

Your email address will not be published. Required fields are marked *