41 Managing Views

• ·         Asks for the N largest or smallest values in a column-TOP-N Analysis
• ·         Removes a view-DROP VIEW view_name
• ·         Subquery with an alias that can be used within a SQL statement-INLINE VIEW

1.  Create a view from the copy_d_songs table called view_copy_d_songs that includes only the title and artist. Execute a SELECT * statement to verify that the view exists.

CREATE OR REPLACE VIEW view_copy_d_songs  AS

SELECT title, artist

FROM copy_d_songs;

SELECT * FROM view_copy_d_songs;

2.  Issue a DROP view_copy_d_songs. Execute a SELECT * statement to verify that the view has been deleted.

DROP VIEW view_copy_d_songs;

SELECT * FROM view_copy_d_songs;

ORA-00942: table or view does not exist

3.  Create a query that selects the last name and salary from the Oracle database. Rank the salaries from highest to lowest for the top three employees.

SELECT * FROM

(SELECT last_name, salary FROM employees ORDER BY salary  DESC)

WHERE ROWNUM <= 3;

4.  Construct an inline view from the Oracle database that lists the last name, salary, department ID, and maximum salary for each department. Hint: One query will need to calculate maximum salary by department ID.

There may be some employees without department mentioned since it is nullable. I want to miss such records in my calculations. This is achieved in dptmx in-line view itself. Also a department without an employee is also taken in.

SELECT empm.last_name, empm.salary, dptmx.department_id

FROM

(SELECT dpt.department_id, MAX(NVL(emp.salary,0)) max_dpt_sal

FROM departments dpt LEFT OUTER JOIN employees emp ON dpt.department_id = emp.department_id

GROUP BY dpt.department_id) dptmx LEFT OUTER JOIN employees empm ON dptmx.department_id = empm.department_id

WHERE NVL(empm.salary,0) = dptmx.max_dpt_sal;

5.  Create a query that will return the staff members of Global Fast Foods ranked by salary from lowest to highest.

So, it means lowest has rank one:

SELECT ROWNUM,last_name, salary

FROM

(SELECT * FROM f_staffs ORDER BY SALARY);

Extras:

1.  Create a new table called my_departments and add all columns and all rows to it using a subquery from the Oracle departments table. Do a SELECT * from my_departments to confirm that you have all the columns and rows.

CREATE TABLE my_departments

AS ( SELECT * FROM departments);

DESCRIBE my_departments;

DESCRIBE departments;

SELECT * FROM departments;

SELECT * FROM my_departments;

2.  To view any constraints that may affect the my_departments table, DESCRIBE my_departments to check if any constraints were carried over from the departments table. If there are constraints on my_departments, use an ALTER TABLE command to DISABLE all constraints on my_departments.

Foreign key, check and primary key don’t go with copy but not NULL check constraint goes in copy:

DESCRIBE my_departments;

I need to look into constraint_name column in results here:

SELECT * FROM user_constraints WHERE table_name = UPPER('my_departments');

It suggests that I need:

ALTER TABLE my_departments

DROP CONSTRAINT SYS_C00868380;

3.  Create a view called view_my_departments that includes: department_id and department_name.

CREATE OR REPLACE VIEW view_my_departments  AS

SELECT department_id , department_name

FROM my_departments;

SELECT * FROM view_my_departments ;

4.  Add the following data to the my_departments table using view_my_departments.

department_id	department_name
105	Advertising
120	Custodial
130	Planning

I verified that I can't include here: manager_id, location_id: ORA-00904: column_name: invalid identifier

INSERT INTO view_my_departments ( department_id, department_name)

VALUES(105, 'Advertising');

INSERT INTO view_my_departments ( department_id, department_name)

VALUES(120, 'Custodial');

INSERT INTO view_my_departments ( department_id, department_name)

VALUES(130, 'Planning');

SELECT * FROM my_departments;

5.  Create or enable the department_id column as the primary key.

This must be done on table itself (not the view) or I will get ORA-00942: table or view does not exist

ALTER TABLE my_departments

ADD CONSTRAINT my_departments_id_pk  PRIMARY KEY (department_id);

DESCRIBE my_departments;

6.  Enter a new department named Human Resources into the my_departments table using view_my_departments. Do not add a new department ID.

INSERT INTO view_my_departments ( department_id, department_name)

VALUES(NULL, 'Human Resources');

ORA-01400: cannot insert NULL into ("HKUMAR"."MY_DEPARTMENTS"."DEPARTMENT_ID")

INSERT INTO view_my_departments ( department_id, department_name)

VALUES(105, 'Human Resources');

ORA-00001: unique constraint (HKUMAR.MY_DEPARTMENTS_ID_PK) violated

7.  Add the Human Resources department, department ID 220, to my_departments using view_my_departments.

INSERT INTO view_my_departments ( department_id, department_name)

VALUES(220, 'Human Resources');

1 row(s) inserted.

8.  Verify that the new additions to my_departments were added using view_my_departments.

See chart below

DEPARTMENT_ID	DEPARTMENT_NAME	MANAGER_ID	LOCATION_ID
10	Administration	200	1700
20	Marketing	201	1800
50	Shipping	124	1500
60	IT	103	1400
80	Sales	149	2500
90	Executive	100	1700
110	Accounting	205	1700
190	Contracting	(null)	1700
105	Advertising	(null)	(null)
120	Custodial	(null)	(null)
220	Human Resources	(null)	(null)
130	Planning	(null)	(null)

SELECT * FROM my_departments;

9.  Modify view_my_departments to include location ID. Do a SELECT * command to show what columns are present and a DESCRIBE command to view the columns and associated constraints.

CREATE OR REPLACE VIEW view_my_departments  AS

SELECT department_id , department_name, location_id

FROM my_departments;

SELECT * FROM view_my_departments ;

SELECT department_id , department_name, location_id

FROM my_departments;

DESCRIBE view_my_departments;

DESCRIBE my_departments;

So DESCRIBE take details from underlying table.

SELECT * FROM user_constraints WHERE table_name = UPPER('view_my_departments');

SELECT * FROM user_constraints WHERE table_name = UPPER('my_departments');

But in user_constraints view there are constraints for only parent table. No entry for the view 'view_my_departments’ is present in user_constraints view as predicted.

10. Make location_id a NOT NULL column in the my_departments table.

ALTER TABLE my_departments

MODIFY ( location_id NUMBER(4,0) CONSTRAINT my_departments_loc_id_nn NOT NULL); says:

ORA-02296: cannot enable (HKUMAR.MY_DEPARTMENTS_LOC_ID_NN) - null values found

ALTER TABLE my_departments

ADD CONSTRAINT my_departments_loc_id_nn CHECK("LOCATION_ID" IS NOT NULL);  says:

ORA-02293: cannot validate (HKUMAR.MY_DEPARTMENTS_LOC_ID_NN) - check constraint violated

Now fix the error source:

UPDATE  my_departments

SET location_id = 1800

WHERE location_id IS NULL;

4 row(s) updated.

I verified again above mentioned two methods of forcing NOT NULL. Now above mentioned both ways will work. Even though practically both solve the purpose, I will prefer highlighted in green, due to results of describe mentioned below.

Some statements used to decide highlighted is better with results:

DESCRIBE my_departments;

SELECT * FROM user_constraints WHERE table_name = UPPER('my_departments') AND constraint_type = 'C';

ALTER TABLE my_departments

DROP CONSTRAINT MY_DEPARTMENTS_LOC_ID_NN;
SELECT column_name, nullable FROM user_tab_columns
 WHERE table_name = UPPER('my_departments');

11.  Using the Oracle database, create a complex view between locations and departments with only the following columns: department_name, street_address, city, and state. Include only U.S. cities. Verify that the view was created using a SELECT * statement.

See chart below

Results of select statement from view:

location_id is nullable in departments:

CREATE OR REPLACE VIEW view_dpt_loc  AS

SELECT dpt.department_name, loc.street_address, loc.city, loc.state_province

FROM

 departments dpt

LEFT OUTER JOIN locations loc ON dpt.location_id = loc.location_id

left OUTER JOIN countries con ON loc.country_id = con.country_id

WHERE con.country_name = 'United States of America';

View created.

0.01 seconds

SELECT * FROM view_dpt_loc;