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;

40 DML Operations and Views

• ·         A pseudocolumn which assigns a sequential value starting with 1 to each of the rows returned from the subquery- ROWNUM
• ·         Specifies that INSERTS and UPDATES performed through the view can’t create rows which the view cannot select- WITH CHECK OPTION    ( also the delete)
• ·         Ensures that no DML operations can be performed on this view- WITH READ ONLY

Use the DESCRIBE statement to verify that you have tables named copy_d_songs, copy_d_events, copy_d_cds, and copy_d_clients in your schema. If you don't, write a query to create a copy of each.

CREATE TABLE copy_d_songs

AS ( SELECT * FROM d_songs);

DESCRIBE copy_d_songs;

DESCRIBE d_songs;

SELECT * FROM d_songs;

SELECT * FROM copy_d_songs;

CREATE TABLE copy_d_events

AS ( SELECT * FROM d_events);

DESCRIBE copy_d_events ;

DESCRIBE d_events;

SELECT * FROM d_events ;

SELECT * FROM copy_d_events ;

CREATE TABLE copy_d_cds

AS ( SELECT * FROM d_cds);

DESCRIBE copy_d_cds;

DESCRIBE d_cds;

SELECT * FROM d_cds;

SELECT * FROM copy_d_cds ;

CREATE TABLE copy_d_clients

AS ( SELECT * FROM d_clients);

DESCRIBE copy_d_clients ;

DESCRIBE d_clients;

SELECT * FROM d_clients ;

SELECT * FROM copy_d_clients ;

1. Query the data dictionary USER_UPDATABLE_COLUMNS to make sure the columns in the base tables will allow UPDATE, INSERT, or DELETE. Use a SELECT statement or the Browse Data Dictionary feature in HTML DB. All table names in the data dictionary are stored in uppercase.

USER_UPDATABLE_COLUMNS describes columns in a join view that can be updated by the current user, subject to appropriate privileges.

SELECT owner, table_name, column_name, updatable,insertable, deletable

FROM user_updatable_columns WHERE LOWER(table_name) = 'copy_d_songs';

SELECT owner, table_name, column_name, updatable,insertable, deletable

FROM user_updatable_columns WHERE LOWER(table_name) = 'copy_d_events';

SELECT owner, table_name, column_name, updatable,insertable, deletable

FROM user_updatable_columns WHERE LOWER(table_name) = 'copy_d_cds';

SELECT owner, table_name, column_name, updatable,insertable, deletable

FROM user_updatable_columns WHERE LOWER(table_name) = 'copy_d_clients';

2.  Use the CREATE or REPLACE option to create a view of all the columns in the copy_d_songs table called view_copy_d_songs.

CREATE OR REPLACE VIEW view_copy_d_songs  AS

SELECT *

FROM copy_d_songs;

SELECT * FROM view_copy_d_songs;

3.  Use view_copy_d_songs to INSERT the following data into the underlying copy_d_songs table. Execute a SELECT * from copy_d_songs to verify your DML command. See the graphic.

ID	TITLE	DURATION	ARTIST	TYPE_CODE
88	Mello Jello	2	The What	4

INSERT INTO view_copy_d_songs(id,title,duration,artist,type_code)

VALUES(88,'Mello Jello','2 min','The What',4);

4.  Create a view based on the DJs on Demand COPY_D_CDS table. Name the view read_copy_d_cds. Select all columns to be included in the view. Add a WHERE clause to restrict the year to 2000. Add the WITH READ ONLY option.

CREATE OR REPLACE VIEW read_copy_d_cds  AS

SELECT *

FROM copy_d_cds

WHERE year = '2000'

WITH READ ONLY ;

SELECT * FROM read_copy_d_cds;

5.  Using the read_copy_d_cds view, execute a DELETE FROM read_copy_d_cds WHERE cd_number = 90;

ORA-42399: cannot perform a DML operation on a read-only view

6.  Use REPLACE to modify read_copy_d_cds. Replace the READ ONLY option with WITH CHECK OPTION CONSTRAINT ck_read_copy_d_cds. Execute a SELECT * statement to verify that the view exists.

CREATE OR REPLACE VIEW read_copy_d_cds  AS

SELECT *

FROM copy_d_cds

WHERE year = '2000'

WITH CHECK OPTION CONSTRAINT ck_read_copy_d_cds;

7.  Use the read_copy_d_cds view to delete any CD of year 2000 from the underlying copy_d_cds.

DELETE FROM read_copy_d_cds

WHERE year = '2000';

8.  Use the read_copy_d_cds view to delete cd_number 90 from the underlying copy_d_cds table.

DELETE FROM read_copy_d_cds

WHERE cd_number = 90;

9.  Use the read_copy_d_cds view to delete year 2001 records.

DELETE FROM read_copy_d_cds

WHERE year = '2001';

10. Execute a SELECT * statement for the base table copy_d_cds. What rows were deleted?

Only the one in problem 7 above, not the one in 8 and 9

11. What are the restrictions on modifying data through a view?

For simple views, all DML’s are OK, but for complex views:

Delete restricted if it contains:

Group functions

GROUP BY CLAUSE

DISTINCT

pseudocolumn ROWNUM  Keyword

Modify restricted if it contains:

Group functions

GROUP BY CLAUSE

DISTINCT

pseudocolumn ROWNUM Keyword

Column defined by expressions

INSERT restricted if it contains:

Group functions

GROUP BY CLAUSE

DISTINCT

pseudocolumn ROWNUM Keyword

Column defined by expressions

Does not include NOT NULL columns in the base table.

12. What is Moore’s Law? Do you consider that it will continue to apply indefinitely? Support your opinion with research from the internet.

 It roughly predicted that computing power nearly doubles every year. But Moore also said in 2005 that as per nature of exponential functions, this trend may not continue forever.

13. What is the “singularity” in terms of computing?

Is the hypothesis that the invention of artificial superintelligence will abruptly trigger runaway technological growth, resulting in unfathomable changes to human civilization.

3 Reasons To Believe The Singularity Is Near as per Greg Satell on Forbes:

·         We’re Going Beyond Moore’s Law

·         Robots Are Doing Human Jobs

·         We’re Editing Genes

39 Creating Views

• ·         A subset of data from one or more tables that is generated from a query and stored as a virtual table-VIEW
• ·         Name of view-VIEW_NAME
• ·         Creates a view regardless of whether or not the base tables exist- FORCE
• ·         Derives data from a table, no functions or groups, performs DML operations through the view-SIMPLE VIEW
• ·         Creates the view only if the base table exists-NOFORCE
• ·         Statement used to create a new view- CREATE VIEW statement
• ·         Specifies a name for each expression selected by the view’s query-Alias
• ·         A complete SELECT statement-subquery
• ·         Derives data from more than one table, contains functions or groups of data, and does not always allow DML operations through the view-Complex View
• ·         Re-creates the view if it already exists-REPLACE

1.  What are three uses for a view from a DBA’s perspective?

·         Restrict access and display selective columns

·         Reduce complexity of queries from other internal systems. So, providing a way to view same data in a different manner.

·         Let the app code rely on views and allow the internal implementation of tables to be modified later.

2.  Create a simple view called view_d_songs that contains the ID, title, and artist from the DJs on Demand table for each “New Age” type code. In the subquery, use the alias “Song Title” for the title column.

The subquery:

SELECT d_songs.id, d_songs.title "Song Title", d_songs.artist

from d_songs INNER JOIN d_types ON d_songs.type_code = d_types.code

where d_types.description = 'New Age';

Now the view:

CREATE VIEW view_d_songs AS

SELECT d_songs.id, d_songs.title "Song Title", d_songs.artist

from d_songs INNER JOIN d_types ON d_songs.type_code = d_types.code

where d_types.description = 'New Age';

But second execution of this SQL statement will say:

ORA-00955: name is already used by an existing object

If I want to recreate the view/ create it first time

CREATE OR REPLACE VIEW view_d_songs AS

SELECT d_songs.id, d_songs.title "Song Title", d_songs.artist

from d_songs INNER JOIN d_types ON d_songs.type_code = d_types.code

where d_types.description = 'New Age';

Verify results again:

SELECT * FROM view_d_songs ;

3.  SELECT *

FROM view_d_songs.

What was returned?

The result is same as that of

SELECT d_songs.id, d_songs.title "Song Title", d_songs.artist

from d_songs INNER JOIN d_types ON d_songs.type_code = d_types.code

where d_types.description = 'New Age';

SELECT * FROM view_d_songs ;

4.  REPLACE view_d_songs. Add type_code to the column list. Use aliases for all columns.

CREATE OR REPLACE VIEW view_d_songs AS

SELECT d_songs.id, d_songs.title "Song Title", d_songs.artist, d_songs.type_code

from d_songs INNER JOIN d_types ON d_songs.type_code = d_types.code

where d_types.description = 'New Age';

5.  Jason Tsang, the disk jockey for DJs on Demand, needs a list of the past events and those planned for the coming months so he can make arrangements for each event’s equipment setup. As the company manager, you do not want him to have access to the price that clients paid for their events. Create a view for Jason to use that displays the name of the event, the event date, and the theme description. Use aliases for each column name.

CREATE OR REPLACE VIEW view_d_events_pkgs AS

SELECT evt.name "Name of Event", TO_CHAR(evt.event_date, 'dd-Month-yyyy')  "Event date", thm.description "Theme description"

FROM  d_events  evt INNER JOIN d_themes  thm  ON evt.theme_code = thm.code

WHERE evt.event_date <= ADD_MONTHS(SYSDATE,1);

SELECT * FROM view_d_events_pkgs ;

6.  It is company policy that only upper-level management be allowed access to individual employee salaries. The department managers, however, need to know the minimum, maximum, and average salaries, grouped by department. Use the Oracle database to prepare a view that displays the needed information for department managers.

DESCRIBE employees;

suggests:

Salary is a nullable field, I don't want to miss nulls in average/min/max calculation.

There may be some employees without department mentioned since it is nullable. I want to miss such records in my calculations.

SELECT department_id FROM departments WHERE department_id NOT IN ( SELECT NVL(department_id,0) FROM employees);

Suggests:

There may be a department for which there is no record in employees table.

CREATE OR REPLACE VIEW view_min_max_avg_dpt_salary ("Department Id", "Department Name", "Max Salary", "Min Salary", "Average Salary") AS

SELECT dpt.department_id, dpt.department_name, MAX(NVL(emp.salary,0)), MIN(NVL(emp.salary,0)), ROUND(AVG(NVL(emp.salary,0)),2)

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 view_min_max_avg_dpt_salary;