Friday, October 28, 2016

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

30 INSERT Statements


  • ·         Someone doing “real work” with the computer, using it as a means rather than an end-User
  • ·         Consists of a collection of DML statements that form a logical unit of work.-transaction
  • ·         Fully and clearly expressed; leaving nothing implied-explicit
  • ·         Adds a new row to a table-INSERT INTO


I should execute DESC tablename before doing INSERT to view the data types for each column. VARCHAR2 data-type entries need single quotation marks in the VALUES statement.

1.      Give two examples of why it is important to be able to alter the data in a database.

·         I am on a flight booking site. It shows available flights, say I try to book a ticket and still my transaction is not commenced anywhere, I will be in big trouble.
·         I am trying to create a login on a site, it takes my details and say never store my information in registration request table, I will keep on waiting for approval which will never happen.

2.  DJs on Demand just purchased four new CDs.  Use an explicit INSERT statement to add each CD to the copy_d_cds table. After completing the entries, execute a SELECT * statement to verify your work.

CD_Number
Title
Producer
Year
97
Celebrate the Day
R & B Inc.
2003
98
Holiday Tunes for All Ages
Tunes are Us
2004
99
Party Music
Old Town Records
2004
100
Best of Rock and Roll
Old Town Records
2004




Create copy of main table to play around:
CREATE TABLE copy_d_cds
AS ( SELECT * FROM d_cds);
see if copy worked well:
DESCRIBE copy_d_cds;
DESCRIBE d_cds;
(clone table lost its primary key constraint and cd_number became nullable.)
see how the copied content looks like.
SELECT * FROM copy_d_cds ;
I see that cd_number 98 will be repeated with my insert statements, but it won't give error, since primary key constrain is lost in copy.
The Explicit insert statements without even missing nullable columns:

INSERT INTO copy_d_cds(cd_number,title,producer,year)
VALUES(97,'Celebrate the Day','R & B Inc.','2003');

INSERT INTO copy_d_cds(cd_number,title,producer,year)
VALUES(98,'Holiday Tunes for All Ages','Tunes are Us','2004');

INSERT INTO copy_d_cds(cd_number,title,producer,year)
VALUES(99,'Party Music','Old Town Records','2004');

INSERT INTO copy_d_cds(cd_number,title,producer,year)
VALUES(100,'Best of Rock and Roll','Old Town Records','2004');

SELECT * FROM copy_d_cds ;
3.   DJs on Demand has two new events coming up. One event is a fall football party and the other event is a sixties theme party. The DJs on Demand clients requested the songs shown in the table for their events. Add these songs to the copy_d_songs table using an implicit INSERT statement.

ID
Title
Duration
Type_Code
52
Surfing Summer
Not known
12
53
Victory Victory
5 min
12

Create copy of main table to play around:
CREATE TABLE copy_d_songs
AS ( SELECT * FROM d_songs);
see if copy worked well:
DESCRIBE copy_d_songs;
DESCRIBE d_songs;
(Also, I see that duration & artist is nullable in source table. I could skip these column names in insert into / if I decide to include these columns, I will specify it as NULL in VALUES. I will prefer the later option to be symmetric and avoid missing something by mistake. One more thing the clone table lost its primary key constraint and id became nullable.)
The Explicit insert statements without even missing nullable columns:
INSERT INTO copy_d_songs (id,title,duration,artist,type_code)
VALUES(52,'Surfing Summer',NULL,NULL,12);
INSERT INTO copy_d_songs (id,title,duration,artist,type_code)
VALUES(53,'Victory Victory','5 min',NULL,12);

But, problem specifically says to use implicit insert statement:

INSERT INTO copy_d_songs
VALUES(52,'Surfing Summer',NULL,NULL,12);
INSERT INTO copy_d_songs
VALUES(53,'Victory Victory','5 min',NULL,12);
SELECT * FROM copy_d_songs ;

4.  Add the two new clients to the copy_d_clients table. Use either an implicit or an explicit INSERT.

Client_Number
First_Name
Last_Name
Phone
Email
6655
Ayako
Dahish
3608859030
dahisha@harbor.net
6689
Nick
Neuville
9048953049
nnicky@charter.net

a) Create copy of main table to play around:
CREATE TABLE copy_d_clients
AS ( SELECT * FROM d_clients);
b) see if copy worked well:
DESCRIBE copy_d_clients ;
DESCRIBE d_clients;
(clone table lost its primary key constraint and client_number became nullable.)
SELECT * FROM d_clients ;
SELECT * FROM copy_d_clients ;


c) The Explicit insert statements without even missing nullable columns:

INSERT INTO copy_d_clients(client_number,first_name,last_name,phone,email)
VALUES(6655,'Ayako','Dahish',3608859030,'dahisha@harbor.net');
INSERT INTO copy_d_clients(client_number,first_name,last_name,phone,email)
VALUES(6689,'Nick','Neuville',3608859030,'nnicky@charter.net');

d) verify data:
SELECT * FROM copy_d_clients ;
5.   Add the new client’s events to the copy_d_events table. The cost of each event has not been determined at this date.

ID
Name
Event_ Date
Description
Cost
Venue_ID
Package_ Code
Theme_ Code
Client_ Number
110
Ayako Anniversary
07-Jul-2004
Party for 50, sixties dress, decorations

245
79
240
6655
115
Neuville Sports Banquet
09-Sep-2004
Barbecue at residence, college alumni, 100 people

315
87
340
6689

a) Create copy of main table to play around:
CREATE TABLE copy_d_events
AS ( SELECT * FROM d_events);
b) see if copy worked well:
DESCRIBE copy_d_events ;
DESCRIBE d_events;
(
All the constraints are lost in this copy.e.g.:
·         clone table lost its primary key constraint and id became nullable.
·         245 and 315 venue_id are not there in d_venues, but it insert will still work. [This loss of foreign key constraint is not mentioned by DESCRIBE]

one more thing:
·         cost is not nullable and it needs to be a number. I could either make it nullable, or give some value like 0 to cost. I go with second choice - give value as zero.
)
SELECT * FROM d_events ;
SELECT * FROM copy_d_events ;


c) The Explicit insert statements:

INSERT INTO copy_d_events(id,name,event_date,description,cost,venue_id,package_code,theme_code,client_number)
VALUES(110,'Ayako Anniversary',TO_DATE('07-Jul-2004','dd-Mon-yyyy'),'Party for 50, sixties dress, decorations',0,245,79,240,6655);
INSERT INTO copy_d_events(id,name,event_date,description,cost,venue_id,package_code,theme_code,client_number)
VALUES(115,'Neuville Sports Banquet',TO_DATE('09-Sep-2004','dd-Mon-yyyy'),'Barbecue at residence, college alumni, 100 people',0,315,87,340,6689);

d)verify data:
SELECT * FROM copy_d_events ;

6.  Create a table called rep_email using the following statement:

CREATE TABLE rep_email (
 id NUMBER(2) CONSTRAINT rel_id_pk PRIMARY KEY,
first_name VARCHAR2(10),
 last_name VARCHAR2(10),
email_address VARCHAR2(10))

Populate this table by running a query on the employees table that includes only those employees who are REP’s.

Those employees could be Marketing Representative, or Sales Representative. There JOB_ID ends with '_REP'
DESCRIBE rep_email ;
DESCRIBE employees;
Please note, employee_id has precision 6 and scale 0. But id in problem statement has 2, 0
Similarly other fields also have differences.
Expected to see errors like ORA-01438: value larger than specified precision allowed for this column
Luckily, rest of the mismatches still work because data is ok, but for id, I will have alter it:


ALTER TABLE rep_email DROP column id;
ALTER TABLE rep_email ADD id NUMBER(6,0) CONSTRAINT rel_id_pk PRIMARY KEY;

DESCRIBE rep_email ;

SELECT employee_id, first_name, last_name, email
FROM employees
WHERE job_id LIKE '%\_REP' ESCAPE '\';

INSERT INTO rep_email(id, first_name, last_name, email_address)
SELECT employee_id, first_name, last_name, email
FROM employees
WHERE job_id LIKE '%\_REP' ESCAPE '\';


SELECT * FROM rep_email;