Tuesday, November 1, 2016

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

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

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');
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 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)
SET  tgt.title = src.title, tgt.producer = src.producer, tgt.year = src.year
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.
WHEN salary > 20000 THEN
INTO special_sal
VALUES(employee_id, salary)
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;
SELECT COUNT(*) FROM sal_history;
SELECT COUNT(*) FROM mgr_history;


No comments:

Post a Comment