Assignment #6
DML, DDL, and Views
1) Create an empty table called SECTION2 with the same structure as the SECTION table. Create a
view on the SECTION2 table that will be used for updating the table and restricts updates to sections
with capacities that are less than 25. Write two INSERT statements – one that succeeds and one
that fails and that uses the view to insert into the SECTION2 table.
CREATE TABLE section2 AS
SELECT *
FROM section
WHERE 1=2
2) Create a SQL script file that will
a) Create a view that will display the names of courses taught by Anita Morris
b) Display that data from the view.
You should be able to run the script from the SQL*Plus command line with the @ symbol or the “run”
command.
CREATE OR REPLACE VIEW v_morris AS
SELECT course_no, description
3) Write a table creation script for a table called OWNER. Include the following:
• Column for the owner id; primary key
• Column for the owner username (i.e., the logon name); unique and not null
• Column for the owner’s first name; can be null
• Column for the owner’s last name; can be null
a) Add a row to OWNER using the following values:
• Owner id = 20
• Owner username = Cartman_E
• Owner first name = Eric
• Owner last name = Cartman
INSERT INTO owner
VALUES (20,’Cartman_E’,’Eric’,’Cartman’)
COMMIT
b) Create a sequence called SEQ_OWNER. Write a SELECT statement to find out the highest
value currently in the OWNER table. Start the sequence at that value plus one when you create
it.
SELECT MAX(owner_id)
c) Add to OWNERS table all the entries in the STUDENT table using an INSERT INTO…SELECT.
Use the sequence number to generate the owner id. Construct the owner username using string
functions and appending the student id to insure uniqueness (use the format
MORRISON_A_122).
INSERT INTO owner
SELECT seq_owner.NEXTVAL,
last_name||’_’||SUBSTR(first_name,1,1)||TO_CHAR(student_id),
first_name, last_name