Sunday, November 20, 2016

49 Ensuring Quality Query Results– Advanced Technique


1. Create additional tables used in this section by executing the following statements:

CREATE TABLE emp AS SELECT * FROM employees;
CREATE TABLE dept AS SELECT * FROM departments;
CREATE TABLE emp
AS ( SELECT * FROM employees);

CREATE TABLE dept
AS ( SELECT * FROM departments);

DESCRIBE table_name;






SELECT chld.table_name "Subject", chldcols.column_name "Subject Column Name", chld.constraint_name "constraint_name in Subject",  chld.constraint_type  "constraint_type in Subject",  prnt.table_name "Parent of FK",  prntcols.column_name "Parent's Column Name", prnt.constraint_name "Parent PK"
FROM user_constraints chld LEFT OUTER JOIN user_constraints prnt ON  chld.r_constraint_name = prnt.constraint_name
LEFT OUTER JOIN user_cons_columns chldcols ON chld.constraint_name = chldcols.constraint_name
LEFT OUTER JOIN user_cons_columns prntcols ON prnt.constraint_name = prntcols.constraint_name
WHERE chld.table_name = UPPER('table_name');




So, only not null constraints travel through with copy.

SELECT * FROM table_name;
Data is copied well though.
2.  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.
I have to show rows related to all constraints including CHECK constraints, except the one which are NOT NULL constraints.
Difference between NOT NULL constraint and check constraint is going to be tricky here. I could very well create a CHECK constraint with search condition e.g. "EMPLOYEE_ID" IS NOT NULL to achieve my business goal. But, problem says specifically, exclude not null constraint, but include check/pk/fk constraint.
 Given that search_condition of user_constraints is a long in oracle as of today.  Some errors I got under obvious implementations (e.g. using WITH clause, instead of  my_temp_table  use subquery, trying to convert long to desired formats for comparisons etc.) :
ORA-00932: inconsistent datatypes: expected - got LONG
ORA-00932: inconsistent datatypes: expected CHAR got LONG
ORA-00997: illegal use of LONG datatype
Etc. Finally: Execute all 3 SQL statements

CREATE TABLE my_temp_table AS
(SELECT cons.constraint_name, cons.constraint_type,
cols.column_name, cols.position,
TO_LOB(cons.search_condition)  search_condition
FROM  user_constraints cons
INNER JOIN user_cons_columns cols
ON cons.constraint_name = cols.constraint_name
WHERE cons.table_name = 'JOB_HISTORY' );

SELECT * FROM my_temp_table
WHERE NOT (
constraint_type = 'C'
AND  column_name in
(SELECT column_name FROM user_tab_columns
 WHERE table_name = UPPER('job_history')
 AND nullable = 'N')
AND DBMS_LOB.COMPARE(search_condition,
 CONCAT('"', CONCAT(column_name, '" IS NOT NULL'))) = 0);

All other constraints except JHIST_EMPLOYEE_NN, JHIST_START_DATE_NN, JHIST_END_DATE_NN and JHIST_JOB_NN which are the NOT NULL constraints are returned by above statement:

If I want to display only NULL constraints here, I will remove NOT in above SELECT statement.
If I want to display all constraints including NOT NULL then I will remove WHERE clause.
Please note, a constraint which uses two columns, is coming twice here, since problem also mentioned to display position
DROP TABLE my_temp_table PURGE;

Edit:  There is another way to target this Problem 2 which doesn’t need creating and dropping a temp table:
a)      Create a function which gives search_condition as varchar2(4000):
CREATE OR REPLACE FUNCTION getsearchcondition( p_constraint_name in varchar2)
return varchar2 as l_cursor integer default dbms_sql.open_cursor;
l_n number;
l_long_val varchar2(4000);
l_long_len number;
l_buflen number := 4000;
l_curpos number := 0;
begin
dbms_sql.parse( l_cursor, 'SELECT search_condition FROM user_constraints WHERE constraint_name = :x', dbms_sql.native );
dbms_sql.bind_variable( l_cursor, ':x', p_constraint_name );
dbms_sql.define_column_long(l_cursor, 1);
l_n := dbms_sql.execute(l_cursor);
if (dbms_sql.fetch_rows(l_cursor)>0)
then
dbms_sql.column_value_long(l_cursor, 1, l_buflen, l_curpos ,l_long_val, l_long_len );
end if;
dbms_sql.close_cursor(l_cursor);
return l_long_val;
end getsearchcondition;





b)
WITH
subquery1 AS
(SELECT cons.constraint_name, cons.constraint_type,
cols.column_name, cols.position,
getsearchcondition(cons.constraint_name)  search_condition
FROM  user_constraints cons
INNER JOIN user_cons_columns cols
ON cons.constraint_name = cols.constraint_name
WHERE cons.table_name = 'JOB_HISTORY' ),
subquery2 AS
(
SELECT column_name FROM user_tab_columns
 WHERE table_name = UPPER('job_history')
 AND nullable = 'N'
)
SELECT * FROM (SELECT * FROM subquery1 )
WHERE NOT (
constraint_type = 'C'
AND  column_name in
(SELECT * FROM subquery2)
AND search_condition = CONCAT('"', CONCAT(column_name, '" IS NOT NULL')) );

This second method [includes two steps a) and b) mentioned above] seems to be cleaner approach with same valid results.

3.  Create a primary key constraint on the emp table’s employee_id column
ALTER TABLE emp
ADD  CONSTRAINT emp_employee_id_pk PRIMARY KEY (employee_id);
SELECT constraint_name, constraint_type, table_name, status, index_name  FROM user_constraints  WHERE table_name = UPPER('emp') AND constraint_type = 'P';

4.  Create a primary key on the dept table’s department_id column
ALTER TABLE dept
ADD  CONSTRAINT dept_department_id_pk PRIMARY KEY (department_id);
SELECT constraint_name, constraint_type, table_name, status, index_name  FROM user_constraints  WHERE table_name = UPPER('dept') AND constraint_type = 'P';

5.  Add a foreign constraint between DEPT and EMP so that only valid departments can be entered in the EMP table. Make sure you can delete any row from the DEPT table, and that referenced rows in the EMP table are deleted.
ALTER TABLE  emp ADD CONSTRAINT emp_dept_department_id_fk FOREIGN KEY (department_id)
REFERENCES  dept (department_id) ON DELETE CASCADE;

SELECT chld.table_name "Subject", chldcols.column_name "Subject Column Name", chld.constraint_name "constraint_name in Subject",  chld.constraint_type  "constraint_type in Subject",  chld.delete_rule  "delete_rule in Subject", prnt.table_name "Parent Table",  prntcols.column_name "Parent table Column Name", prnt.constraint_name "Parent PK"
FROM user_constraints chld LEFT OUTER JOIN user_constraints prnt ON  chld.r_constraint_name = prnt.constraint_name
LEFT OUTER JOIN user_cons_columns chldcols ON chld.constraint_name = chldcols.constraint_name
LEFT OUTER JOIN user_cons_columns prntcols ON prnt.constraint_name = prntcols.constraint_name
WHERE chld.table_name = UPPER('emp') AND chld.constraint_type = 'R' AND chldcols.column_name = UPPER('department_id') ;


Verification:
SELECT * FROM dept WHERE department_id = 20;

SELECT * FROM emp WHERE department_id = 20;



If same action done on original departments table:


But in dept where ON DELETE CASCADE is used:
DELETE FROM dept WHERE department_id = 20;



6.  Test the foreign key constraint you just created:
Count the number of rows in the EMP table.
SELECT COUNT(*) FROM emp;

Remove department 10 from the dept table.
DELETE FROM dept WHERE department_id = 10;

Now count emps again. There should be fewer employees.

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

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.

I target first the phrase "average salary of all the departments":
·         I assume that I have to consider employees having no department; I assume department_id as -1 where, there is no department_id mentioned.
·         Also, AVG skips null salaries; it's very unfair and will like to include nulls as zero.
SELECT NVL(department_id, -1) dpt_idAVG(NVL(salary,0)) avg_sal FROM employees
GROUP BY NVL(department_id, -1);

Now I target "where salary is greater than the average salary"
It seems to me, I have to print the employees in a department, if the salary is greater than average of their department.

WITH avg_sal_by_dept AS
(SELECT NVL(department_id, -1) dpt_id,  AVG(NVL(salary,0)) avg_sal FROM employees
GROUP BY NVL(department_id, -1))

SELECT emp.last_name "last name", TO_CHAR(ROUND(emp.salary,2),'$999999.99') "salary",  CASE WHEN avgqry.dpt_id = -1 THEN NULL ELSE avgqry.dpt_id END  "department number", TO_CHAR(ROUND(avgqry.avg_sal,2),'$999999.99')  "average salary"
FROM employees emp INNER JOIN (SELECT * FROM avg_sal_by_dept) avgqry ON NVL(emp.department_id, -1) = avgqry.dpt_id
WHERE emp.salary > avgqry.avg_sal;



8.  Create a view named V2 that returns the highest salary, lowest salary, average salary and department name.
This problem is same as Creating Views > Problem 6

Included null salaries in avg/max calculations.
Included a department name even if there is no employee for this.
Excluded the employee with null department_id.

CREATE OR REPLACE VIEW v2 ("highest salary", "lowest salary", "average salary", "Department Name") AS
SELECT 
TO_CHAR(ROUND(MAX(NVL(emp.salary,0)),2),'$999999.99'),
TO_CHAR(ROUND(MIN(NVL(emp.salary,0)),2),'$999999.99'),
TO_CHAR(ROUND(AVG(NVL(emp.salary,0)),2),'$999999.99'),  dpt.department_name
FROM departments dpt LEFT OUTER JOIN employees emp ON dpt.department_id = emp.department_id
GROUP BY (dpt.department_id, dpt.department_name);

SELECT * FROM v2;

.

9.  Create a view named Dept_Managers_view that returns a listing of department names long 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.

Ideally manager of an employee should have been in the same department as the employee. And I should have been using Hierarchical Queries to get the top most employees in a department to be termed as super manager.
SELECT emp.employee_id employeeid, mgr.employee_id managerid
FROM employees emp LEFT OUTER JOIN employees mgr ON emp.manager_id = mgr.employee_id
WHERE emp.department_id != mgr.department_id;
Suggests, this is not true.
Let’s assume definition of managers as given in Correlated Subqueries > Problem 3
"employees who have at least one person reporting to them"
So this problem has just one more addition on the top of Correlated Subqueries > Problem 3, join with departments for department name.
Let’s do it with join instead of subquery (Correlated Subqueries > Problem 3)
manager initial means first character of firt name + first character of last name. First name is nullable
CREATE OR REPLACE VIEW dept_managers_view  AS
SELECT DISTINCT SUBSTR(NVL(mgr.first_name, '_'),1, 1) || SUBSTR(mgr.last_name,1, 1) initials,   mgr.last_name surname, dpt.department_name
FROM
employees mgr INNER JOIN employees emp ON mgr.employee_id = emp.manager_id
LEFT OUTER JOIN departments dpt ON mgr.department_id = dpt.department_id;
I used left outer join here to include managers with null department also.
SELECT * FROM Dept_Managers_view ;

As per "DML Operations and Views”, since view has DISTINCT keyword, DML operations can't be done on it.

UPDATE dept_managers_view 
SET surname = 'Kumar' WHERE department_name = ‘Sales';

ORA-01732: data manipulation operation not legal on this view



10.  Create a sequence named ct_seq using all the default values.
CREATE SEQUENCE ct_seq ;

SELECT * FROM user_sequences WHERE sequence_name = UPPER('ct_seq');


11.  Examine the following insert statement and fix the errors.
INSERT INTO emp
(employee_id, first_name, last_name, email, phone_number, hire_date,
job_id, salary, commission_pct, manager_id, department_id)
VALUES
(ct_seq.nextvalue, "Kaare", 'Hansen', 'KHANSEN', '44965 832123',
sysdate, 'SA_REP', $6500, null, 100, 20);

INSERT INTO dept (department_id, department_name, manager_id, location_id)
VALUES (20, 'Marketing', 201, 1800);
INSERT INTO emp
(employee_id, first_name, last_name, email, phone_number, hire_date,
job_id, salary, commission_pct, manager_id, department_id)
VALUES
(ct_seq.NEXTVAL, 'Kaare', 'Hansen', 'KHANSEN', '44965 832123',
sysdate, 'SA_REP', 6500, null, 100, 20);

ORA-00911: invalid character
ORA-00984: column not allowed here
ORA-00984: column not allowed here
ORA-02291: integrity constraint (HKUMAR.EMP_DEPT_DEPARTMENT_ID_FK) violated - parent key not found

12.  Write the SQL statement to list all the user tables which contains the name PRIV.
DESCRIBE  user_tables;
SELECT * FROM user_tables WHERE table_NAME like '%PRIV%';
SELECT * FROM user_tables WHERE  REGEXP_LIKE(table_NAME, '(PRIV)');
SELECT * FROM all_tables WHERE REGEXP_LIKE(table_name, '(PRIV)');
first attempt:
ORA-07455: estimated execution time (86 secs), exceeds limit (60 secs)
Second attempt:


13.  Give select access to public on the EMP table, and verify the grant by running this query.
SELECT *
FROM user_tab_privs
WHERE table_name = 'EMP';

GRANT SELECT ON emp to PUBLIC;

14.  Replace the ?? in the following query using regular expressions to return only the numbers from the following string: 'Oracle Academy9547d6905%&^ db apex'.
SELECT REGEXP_REPLACE('Oracle Academy9547d6905%&^ db apex',??,'') regexpreplace
FROM DUAL;

SELECT REGEXP_REPLACE('Oracle Academy9547d6905%&^ db apex','[^[:digit:]]','') regexpreplace
FROM DUAL;
95476905
SELECT REGEXP_REPLACE('Oracle Academy9547d6905%&^ db apex','[^0-9]','') regexpreplace
FROM DUAL;
95476905

15.  Amend the previous query using regular expressions to return the number of digits from the following string: 'Oracle Academy9547d6905%&^ db’
SELECT LENGTH(REGEXP_REPLACE('Oracle Academy9547d6905%&^ db apex','??','')) regexpreplace
FROM DUAL;
SELECT LENGTH(REGEXP_REPLACE('Oracle Academy9547d6905%&^ db apex','[^[:digit:]]','')) regexpreplace
FROM DUAL;
8
16.  Amend the query again to return only the non-numeric characters.
SELECT REGEXP_REPLACE('Oracle Academy9547d6905%&^ db apex','??','') regexpreplace
FROM DUAL;
SELECT REGEXP_REPLACE('Oracle Academy9547d6905%&^ db apex','[[:digit:]]','') regexpreplace
FROM DUAL;
Oracle Academyd%&^ db apex
SELECT REGEXP_REPLACE('Oracle Academy9547d6905%&^ db apex','[0-9]','') regexpreplace
FROM DUAL;
Oracle Academyd%&^ db apex
17.  Using Oracle proprietary joins, construct a statement that returns all the employee_ids joined to all the department_names.
Seems to be this problem wants Cartesian Product: listing all the possible matches
SELECT em.employee_id, dp.department_name
FROM employees em, departments dp;

18.  Still using Oracle Joins, correct the previous statement so that it returns only the name of the department that the employee actually works in.
Seems to be problem wants oracle proprietary equi join equivalent to INNER JOIN
SELECT em.employee_id, dp.department_name
FROM employees em, departments dp
WHERE em.department_id = dp.department_id;

19.  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.
After reading next problem, seems to be problem just wants to stick to equi join equivalent to INNER JOIN for employees and departments, seems to be part of normal problems flow. But for other table combinations I will follow common sense:
For departments location_id is nullable
For locations country_id is nullable
So left outer join in both cases

SELECT em.last_name "last name", dp.department_name "department name",em.salary, con.country_name "country name"
FROM employees em, departments dp, locations loc, countries con
WHERE em.department_id = dp.department_id
AND
dp.location_id = loc.location_id(+)
AND
loc.country_id = con.country_id(+)





20.  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’.
So just need to do long awaited left outer join for employees, departments combination too ( department_id is nullable in employees table, so this make sense too)
SELECT em.last_name "last name", dp.department_name "department name",em.salary, con.country_name "country name"
FROM employees em, departments dp, locations loc, countries con
WHERE em.department_id = dp.department_id(+)
AND
dp.location_id = loc.location_id(+)
AND


loc.country_id = con.country_id(+);

No comments:

Post a Comment