- · 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
No comments:
Post a Comment