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_id, AVG(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(+);
