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