Ensuring Quality Query Results – Advanced Techniques

Objectives

This lesson covers the following objectives:

• Create an advanced query to produce specified data
• Modify an advanced query to produce specified data

Purpose

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

Create These Tables

CREATE TABLE emp
AS select * FROM employees;

CREATE TABLE dept
AS select * FROM departments;

Write the Query

• Problem:
  • Produce a report that lists the constraint name, type, column name, and column position of all the constraints on the JOB_HISTORY table, apart from the not null constraints.
• Tables Used:
  • user_constraints, user_cons_columns

Create the Statement

• Create a primary key constraint on the emp table’s employee_id column.

Table altered.

• Create a primary key on the dept table’s department_id column.

Table altered.

Advanced Techniques

Fix the Code

• Problem:
  • Add a foreign constraint between DEPT and EMP so that only valid departments can be entered in the EMP table, but make sure you can delete any row from the DEPT table.
• Statement:

ALTER TABLE emp
CREATE CONSTRAINT FOREIGN KEY (dept_id) REFS dept(deptid)
on del cascade

Table altered.

Create the Code

• Test the foreign key constraint you just created by following the examples on this slide.

SELECT COUNT(*) AS "Num emps"
FROM emp;

• Examine the number of rows in the EMP table. Remove the details of department 10 from the dept table.

DELETE dept
WHERE department_id = 10;

1 row(s) deleted.

• Now count emps again and check if there are fewer employees as well.

• Problem:
  • Produce a report that returns the last name, salary, department number, and average salary of all the departments where salary is greater than the average salary.
• Tables Used:
  • Employees, Departments

• Problem:
  • Create a view named V2 that returns the highest salary, lowest salary, average salary, and department name.
• Tables Used:
  • emp, dept

SELECT * FROM v2;

• Problem:
  • Create a view named Dept_Managers_view that returns a listing of department names along with the manager initial and surname for that department.
  • Test the view by returning all the rows from it.
  • Make sure no rows can be updated through the view.
  • Try to run an UPDATE statement against the view.
• Tables Used:
  • Employees, departments

• Problem:
  • The following statement contains errors.
  • Fix them and run the code to get the displayed result.
• Code:

DROP V3 views;

View dropped.

Create a Sequence and Fix the Code

• Problem:
  • Create a sequence named ct_seq with all the default values. Run the statements and fix the error.
  • Correct the statement to return the subsequent number.
• Code:

CREATE SEQUENCE ct_seq;

Sequence created.

SELECT ct_seq.currval
FROM dual;

ORA-08002: sequence CT-SEQ CURRVAL is not yet defined in this session

Fix the Code

• Problem:
  • Look at the insert statement and fix the error.
• Code:

INSERT emp
(employee_id, first_name, last_name, email, phone_number, hire_date,
job_id, salary, commission_pct, manager_id, department_id)
VALUES
(currval.ct_seq, 'Kaare', 'Hansen', 'KHANSEN', '4496583212', sysdate,
'Manager', 6500, null, 100, 10)

ORA-00984: column not allowed here

Fix the Code

• Problem:
  • Fix the error in the SQL statement to create the index as shown in the screenshot.
• Code:

CREATE INX emp indx FOR TABLE emp(employee_id DESC,
UPPR(SUBST(firstname,1.1 ||" "||astname)

• Problem:
  • Write the SQL statement to list all the user tables which contain the name PRIV.
• Tables Used:
  • dictionary

• Problem:
  • Give select access to public on the EMP table, and verify the grant by running this query. The query contains errors that you must fix before you can run the select statement.
• Code:

GRANT SELECT ON emp TO PUBLIC

Statement processed.

SELECT *
FROM usr_tab_privs
WHERE tablename = "emp"

• Problem:
  • Using Oracle proprietary joins, construct a statement that returns all the employee_id’s joined to all the department_names.
• Tables Used:
  • Employees, departments

• Problem:
  • Still using Oracle Joins, correct the previous statement so that it returns only the name of the department that the employee actually works in.
• Tables Used:
  • Employees, departments

• Problem:
  • Still using Oracle Joins, construct a query that lists the employees last name, the department name, the salary and the country name of all employees.
• Tables Used:
  • Employees, departments, locations and countries

• Problem:
  • Still using Oracle join syntax, alter the previous query so that it also includes the employee record of the employee with no department_id, ‘Grant’.
• Tables Used:
  • Employees, departments, locations and countries

Summary

In this lesson, you should have learned how to:

• Create an advanced query to produce specified data
• Modify an advanced query to produce specified data