33 Creating Tables

• ·         Created and maintained by the Oracle Server and contains information about the database- Data dictionary
• ·         A collection of objects that are the logical structures that directly refer to the data in the database- Schema
• ·         Specifies a preset value if a value is omitted in the INSERT statement- DEFAULT
• ·         Stores data; basic unit of storage composed of rows and columns- TABLE
• ·         Command use to make a new table- CREATE TABLE

1.  Complete the GRADUATE CANDIDATE table instance chart. Credits is a foreign-key column referencing the requirements table.

Column Name	student_id	last_name	first_name	credits	graduation_date
Key Type	Primary key			foreign key
Nulls/Unique	NO/YES	NO/NO	NO/NO	NO/NO	YES/NO
FK Column				credits
Datatype	NUMBER	VARCHAR2	VARCHAR2	NUMBER	DATE
Length	6	75	75	6

For credits and student_id it could have been precision and scale rather mentioned here. I assume that when 6 is written for student_id it means NUMBER(6,0) and for credits NUMBER(5, 2)

2.  Write the syntax to create the grad_candidates table.

CREATE TABLE  graduate_candidates

   ( student_id NUMBER(6,0),

    last_name VARCHAR2(75) CONSTRAINT gcs_last_name_nn NOT NULL ENABLE,

 first_name VARCHAR2(75) CONSTRAINT gcs_first_name_nn NOT NULL ENABLE,

 credits NUMBER(5,2) CONSTRAINT gcs_credits_nn NOT NULL ENABLE,

graduation_date DATE,

 CONSTRAINT gcs_std_id_pk PRIMARY KEY (student_id),

 CONSTRAINT gcs_req_fk FOREIGN KEY(credits) REFERENCES requirements(credits) ENABLE     

   

  );

Note: Since there is no requirements table already existing for foreign key above stamen will fail. I may create this table if choose not to include this constraint.

3.  Confirm creation of the table using DESCRIBE.

DESCRIBE graduate_candidates;

4.  Create a new table using a subquery. Name the new table your last name -- e.g., smith_table. Using a subquery, copy grad_candidates into smith_table.

Even if the foreign key world have been created in problem 2, it would have been lost here in copy.

CREATE TABLE kumar_table

AS ( SELECT * FROM graduate_candidates);

see if copy worked well:

DESCRIBE kumar_table;

student_id is nullable and not a primary key here in kumar_table.

5.  Insert your personal data into the table created in question 4.

INSERT INTO kumar_table (student_id, last_name, first_name, credits, graduation_date)

Values(10,'kumar','he',999.99,NULL);

SELECT LENGTH(credits) FROM kumar_table WHERE student_id = 10;

6.  Query the data dictionary for each of the following:

• USER_TABLES

• USER_OBJECTS

• USER_CATALOG or USER_CAT

In separate sentences, summarize what each query will return.

--USER_TABLES describes the relational tables owned by the current user. Its columns (except for OWNER) are the same as those in ALL_TABLES.

SELECT * FROM user_tables;

48 rows returned in 0.64 seconds

--USER_CATALOG lists indexes, tables, views, clusters, synonyms, and sequences owned by the current user. Its columns are the same as those in "ALL_CATALOG".

SELECT * FROM user_catalog;

407 rows returned in 0.05 seconds

SELECT DISTINCT(table_type) FROM user_catalog;

3 rows returned in 0.02 seconds        

--USER_OBJECTS describes all objects owned by the current user. Its columns are the same as those in "ALL_OBJECTS".

SELECT * FROM user_objects;

117 rows returned in 0.19 seconds

SELECT DISTINCT(object_type) FROM user_objects;

6 rows returned in 0.17 seconds        

SELECT * FROM user_cat;

ORA-00942: table or view does not exist

32 DEFAULT Values, MERGE, and Multi-Table Inserts

1.  When would you want a DEFAULT value?

-If no value is given while row creation and I want the field to take some predefined value. For example there may be a created on column, and I want that when a row is created, it gets filled up with current time.

2.  Currently, the Global Foods F_PROMOTIONAL_MENUS table START_DATE column does not have SYSDATE set as DEFAULT. Your manager has decided she would like to be able to set the starting date of promotions to the current day for some entries. This will require three steps:

a.  In your schema, Make a copy of the Global Foods F_PROMOTIONAL_MENUS table using the following SQL statement:

CREATE TABLE copy_f_promotional_menus

AS (SELECT * FROM f_promotional_menus)

b.  Alter the current START_DATE column attributes using:

ALTER TABLE copy_f_promotional_menus

MODIFY(start_date DATE DEFAULT SYSDATE)

c.  INSERT the new information and check to verify the results.

INSERT a new row into the copy_f_promotional_menus table for the manager’s new promotion. The promotion code is 120. The name of the promotion is ‘New Customer.’ Enter DEFAULT for the start date and '01-Jun-2005' for the ending date. The giveaway is a 10% discount coupon. What was the correct syntax used?

As a standard rule, we are doing all operations on copy tables rather than screwing original tables.

a. So first I will create a copy table:

CREATE TABLE copy_f_promotional_menus

AS ( SELECT * FROM f_promotional_menus);

DESCRIBE f_promotional_menus;

DESCRIBE copy_f_promotional_menus;

SELECT * FROM f_promotional_menus;

SELECT * FROM copy_f_promotional_menus;

SELECT TO_CHAR(TRUNC(start_date), 'dd-mm-yyyy-hh24:mi:ss'),TO_CHAR(start_date, 'dd-mm-yyyy-hh24:mi:ss')  FROM copy_f_promotional_menus;

SELECT TO_CHAR(TRUNC(sysdate), 'dd-mm-yyyy-hh24:mi:ss') FROM dual;

b. Give default vale to start_date:

ALTER TABLE copy_f_promotional_menus

MODIFY(start_date DATE DEFAULT TRUNC(SYSDATE));

c.1 Out of existing rows, say he wants to set start_date to current date for the rows where start_date is 10-Feb-2004

Set to starting of today:

UPDATE  copy_f_promotional_menus

SET start_date = TRUNC(SYSDATE)

WHERE   TRUNC(start_date) =    TO_DATE('10-Feb-2004','dd-Mon-yyyy');

c.2 For new rows, simply skip giving any value to this field or say DEFAULT:

INSERT INTO copy_f_promotional_menus(code,name,start_date,end_date,give_away)

VALUES('115','Back to School part 2',DEFAULT,NULL,'ballpen and highlighter again');

or

INSERT INTO copy_f_promotional_menus(code,name,end_date,give_away)

VALUES('116','Back to School part 3',NULL,'ballpen and highlighter again2');

INSERT INTO copy_f_promotional_menus(code,name,start_date,end_date,give_away)

VALUES('120','New Customer',DEFAULT,TO_DATE('01-Jun-2005','dd-Mon-yyyy'),' 10% discount coupon');

3.  Allison Plumb, the event planning manager for DJs on Demand, has just given you the following list of CDs she acquired from a company going out of business. She wants a new updated list of CDs in inventory in an hour, but she doesn’t want the original D_CDS table changed. Prepare an updated inventory list just for her.

a.  Assign new cd_numbers to each new CD acquired.

It seems to be, this cd_number assignment is being done manually, I need not create a sequence for this. If the sequence had to be created, this point would have come after point b below, original table don’ have a sequence on this column.

b.  Create a copy of the D_CDS table called manager_copy_d_cds. What was the correct syntax used?

CREATE TABLE manager_copy_d_cds

AS ( SELECT * FROM d_cds);

DESCRIBE d_cds;

DESCRIBE manager_copy_d_cds;

SELECT * FROM d_cds;

SELECT * FROM manager_copy_d_cds;

c.  INSERT into the manager_copy_d_cds table each new CD title using an INSERT statement. Make up one example or use this data:

20, 'Hello World Here I Am', 'Middle Earth Records', '1998'

What was the correct syntax used?

INSERT INTO manager_copy_d_cds(cd_number,title,producer,year)

VALUES(20,'Hello World Here I Am','Middle Earth Records','1998');

INSERT INTO manager_copy_d_cds(cd_number,title,producer,year)

VALUES(97,'Celebrate the Day','R & B Inc.','2003');

INSERT INTO manager_copy_d_cds(cd_number,title,producer,year)

VALUES(99,'Party Music','Old Town Records','2004');

INSERT INTO manager_copy_d_cds(cd_number,title,producer,year)

VALUES(100,'Best of Rock and Roll','Old Town Records','2004');

SELECT * FROM manager_copy_d_cds ;

d.  Use a merge statement to add to the manager_copy_d_cds table, the CDs from the original table. If there is a match, update the title and year. If not, insert the data from the original table. What was the correct syntax used?

To verify merge, first

i) I need to edit some record in manager_copy_d_cds, this should get updated from d_cds to original value.

UPDATE manager_copy_d_cds

SET title = 'hkumar'

WHERE cd_number = 90;

ii) I should delete some record in manager_copy_d_cds which is present in d_cds. This should be recreated after merge.

DELETE FROM manager_copy_d_cds

WHERE cd_number = 91;

SELECT * FROM manager_copy_d_cds ;

MERGE INTO manager_copy_d_cds tgt USING d_cds src

ON (src.cd_number = tgt.cd_number)

WHEN MATCHED THEN UPDATE

SET  tgt.title = src.title, tgt.producer = src.producer, tgt.year = src.year

WHEN NOT MATCHED THEN INSERT

VALUES (src.cd_number, src.title, src.producer, src.year);

If () is missing I will get: ORA-00969: missing ON keyword

SELECT * FROM manager_copy_d_cds ;

4.  Run the following 3 statements to create 3 new tables for use in a Multi-table insert statement. All 3 tables should be empty on creation, hence the WHERE 1=2 condition in the WHERE clause.

CREATE TABLE sal_history (employee_id, hire_date, salary)

AS SELECT employee_id, hire_date, salary

FROM employees

WHERE 1=2;

CREATE TABLE mgr_history (employee_id, manager_id, salary)

AS SELECT employee_id, manager_id, salary

FROM employees

WHERE 1=2;

CREATE TABLE special_sal (employee_id, salary)

AS SELECT employee_id, salary

FROM employees

WHERE 1=2;

Once the tables exist in your account, write a Multi-Table insert statement to first select the employee_id, hire_date, salary, and manager_id of all employees. If the salary is more than 20000 insert the employee_id and salary into the special_sal table. Insert the details of employee_id, hire_date, and salary into the sal_history table. Insert the employee_id, manager_id, and salary into the mgr_history table.

You should get a message back saying 39 rows were inserted. Verify you get this message and verify you have the following number of rows in each table:

Sal_history: 19 rows

Mgr_history: 19 rows

Special_sal: 1

If I use FISRT / ALL no difference, since there is only one ‘WHEN’ I am using. In else there are 2 inserts.

INSERT FIRST

WHEN salary > 20000 THEN

INTO special_sal

VALUES(employee_id, salary)

ELSE

INTO sal_history

VALUES(employee_id, hire_date, salary)

INTO mgr_history

VALUES(employee_id, manager_id, salary)

SELECT employee_id, salary, hire_date, manager_id

FROM employees;

39 row(s) inserted.

SELECT COUNT(*) FROM special_sal;

1

SELECT COUNT(*) FROM sal_history;

19

SELECT COUNT(*) FROM mgr_history;

19

31 Updating Column Values and Deleting Rows

• ·         Modifies existing rows in a table-UPDATE
• ·         retrieves information from one table & uses the information to update another table-correlated subquery update
• ·         Ensures that the data adheres to a predefined set of rules-Integrity Constraints
• ·         deletes information on a linked table based on what was deleted on the other table- correlated subquery delete
• ·         Removes existing rows from a table-DELETE

NOTE: Copy tables in this section do not exist

If any change is not possible, give an explanation as to why it is not possible.

1.   Monique Tuttle, the manager of Global Fast Foods, sent a memo requesting an immediate change in prices. The price for a strawberry shake will be raised from $3.59 to $3.75, and the price for fries will increase to $1.20. Make these changes to the copy_f_food_items table.

CREATE TABLE copy_f_food_items

AS ( SELECT * FROM f_food_items);

DESCRIBE f_food_items;

DESCRIBE copy_f_food_items;

SELECT * FROM f_food_items;

SELECT * FROM copy_f_food_items;

UPDATE copy_f_food_items  SET price = 3.75

WHERE LOWER(description) = 'strawberry shake';

UPDATE copy_f_food_items  SET price = 1.20

WHERE LOWER(description) = 'fries';

SELECT * FROM copy_f_food_items;

2.   Bob Miller and Sue Doe have been outstanding employees at Global Fast Foods. Management has decided to reward them by increasing their overtime pay. Bob Miller will receive an additional $0.75 per hour and Sue Doe will receive an additional $0.85 per hour. Update the copy_f_staffs table to show these new values. (Note: Bob Miller currently doesn’t get overtime pay. What function do you need to use to convert a null value to 0?)

CREATE TABLE copy_f_staffs

AS ( SELECT * FROM f_staffs);

DESCRIBE f_staffs;

DESCRIBE copy_f_staffs;

SELECT * FROM f_staffs;

SELECT * FROM copy_f_staffs;

UPDATE copy_f_staffs  SET overtime_rate = NVL(overtime_rate, 0) + 0.75

WHERE LOWER(first_name || ' ' || last_name) = 'bob miller';

UPDATE copy_f_staffs  SET overtime_rate = NVL(overtime_rate, 0) + 0.85

WHERE LOWER(first_name || ' ' || last_name) = 'sue doe';

SELECT * FROM copy_f_staffs;

3.   Add the orders shown to the Global Fast Foods copy_f_orders table:

ORDER_NUMBER	ORDER_DATE	ORDER_TOTAL	CUST_ID	STAFF_ID
5680	June 12, 2004	159.78	145	9
5691	09-23-2004	145.98	225	12
5701	July 4, 2004	229.31	230	12

If this action would have been done for f_orders, highlighted values would have failed the update due to foreign key constraint.

CREATE TABLE copy_f_orders

AS ( SELECT * FROM f_orders);

DESCRIBE f_orders;

DESCRIBE copy_f_orders;

SELECT * FROM f_orders;

SELECT * FROM copy_f_orders;

INSERT INTO copy_f_orders(order_number,order_date,order_total,cust_id,staff_id)

VALUES(5680,TO_DATE('June 12, 2004','fmMonth dd, yyyy'),159.78,145,9);

INSERT INTO copy_f_orders(order_number,order_date,order_total,cust_id,staff_id)

VALUES(5691,TO_DATE('09-23-2004','mm-dd-yyyy'),145.98,225,12);

INSERT INTO copy_f_orders(order_number,order_date,order_total,cust_id,staff_id)

VALUES(5701,TO_DATE('July 4, 2004','fmMonth dd, yyyy'),229.31,230,12);

SELECT * FROM copy_f_orders;

4.   Add the new customers shown below to the copy_f_customers table. You may already have added Katie Hernandez. Will you be able to add all these records successfully?

ID	FIRST_NAME	LAST_NAME	ADDRESS	CITY	STATE	ZIP	PHONE_NUMBER
145	Katie	Hernandez	92 Chico Way	Los Angeles	CA	98008	8586667641
225	Daniel	Spode	1923 Silverado	Denver	CO	80219	7193343523
230	Adam	Zurn	5 Admiral Way	Seattle	WA		4258879009

Yes I will be able to add row, even if it has existing id, since in cloning table as mentioned below, primary key constraint is lost.

CREATE TABLE copy_f_customers

AS ( SELECT * FROM f_customers);

DESCRIBE f_customers;

DESCRIBE copy_f_customers;

In copy table, zip is not nullable same as in master table, so the last row will give error while insert.

SELECT * FROM f_customers;

SELECT * FROM copy_f_customers;

INSERT INTO copy_f_customers(id,first_name,last_name,address,city,state,zip,phone_number)

VALUES(145,'Katie','Hernandez','92 Chico Way','Los Angeles','CA',98008,'8586667641');

INSERT INTO copy_f_customers(id,first_name,last_name,address,city,state,zip,phone_number)

VALUES(225,'Daniel','Spode','1923 Silverado','Denver','CO',80219,'7193343523');

INSERT INTO copy_f_customers(id,first_name,last_name,address,city,state,zip,phone_number)

VALUES(230,'Adam','Zurn','5 Admiral Way','Seattle','WA',NULL,'4258879009');

ORA-01400: cannot insert NULL into ("HKUMAR"."COPY_F_CUSTOMERS"."ZIP")

SELECT * FROM copy_f_customers;

5.   Sue Doe has been an outstanding Global Foods staff member and has been given a salary raise. She will now be paid the same as Bob Miller. Update her record in copy_f_staffs.

UPDATE copy_f_staffs  SET salary = (SELECT salary FROM copy_f_staffs WHERE LOWER(first_name || ' ' || last_name) = 'bob miller')

WHERE LOWER(first_name || ' ' || last_name) = 'sue doe';

SELECT * from copy_f_staffs;

6.   Global Fast Foods is expanding their staff. The manager, Monique Tuttle, has hired Kai Kim. Not all information is available at this time, but add the information shown here.

ID	FIRST_NAME	LAST_NAME	BIRTHDATE	SALARY	STAFF_TYPE
25	Kai	Kim	3-Nov-1988	6.75	Order Taker

It should work since all the mandatory columns have values.

INSERT INTO copy_f_staffs(id,first_name,last_name,birthdate,salary,overtime_rate,training,staff_type,manager_id,manager_budget,manager_target)

VALUES(25,'Kai','Kim',TO_DATE('03-Nov-1988','fmdd-Mon-yyyy'),6.75,NULL,NULL,'Order Taker',NULL,NULL,NULL);

SELECT * FROM copy_f_staffs;

7.  Now that all the information is available for Kai Kim, update his Global Fast Foods record to include the following: Kai will have the same manager as Sue Doe. He does not qualify for overtime. Leave the values for training, manager budget, and manager target as null.

UPDATE copy_f_staffs  SET manager_id = (SELECT manager_id  FROM copy_f_staffs WHERE LOWER(first_name || ' ' || last_name) = 'sue doe')

WHERE LOWER(first_name || ' ' || last_name) = 'kai kim';

SELECT * FROM copy_f_staffs;

8.   Execute the following SQL statement. Record your results.

DELETE from departments

WHERE department_id = 60;

ORA-02292: integrity constraint (HKUMAR.EMP_DEPT_FK) violated - child record found

9.   Kim Kai has decided to go back to college and does not have the time to work and go to school. Delete him from the Global Fast Foods staff. Verify that the change was made.

SELECT * FROM copy_f_staffs;

DELETE FROM copy_f_staffs

WHERE LOWER(first_name || ' ' || last_name) = 'kai kim';

SELECT * FROM copy_f_staffs;

10.  Create a copy of the employees table and call it lesson7_emp;

Once this table exists, write a correlated delete statement that will delete any employees from the lesson7_employees table that also exist in the emp_history table.

CREATE TABLE lesson7_emp

AS ( SELECT * FROM employees);

DESCRIBE employees;

DESCRIBE lesson7_emp;

SELECT * FROM employees;

SELECT * FROM lesson7_emp;

SELECT DISTINCT employee_id FROM job_history;

7 rows returned in 0.00 seconds

DELETE FROM lesson7_emp

WHERE employee_id IN ( SELECT DISTINCT employee_id FROM job_history) ;

5 row(s) deleted.

0.01 seconds