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