Practice: SECTION 12
Section 12.1
1. Give two examples of why it is important to be able to alter the data in a database.
- 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.
- 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.
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.
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.
4. Add the two new clients to the copy_d_clients table. Use either an implicit or an
explicit INSERT.
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.
6. Create a table called rep_email using the following statement:
CREATE TABLE rep_email (
id NUMBER(6,0) 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.
Section 12.2
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.
2. Bob Miller and Sue Doe have been outstanding employees at Global Fast Foods.
Man-agement has decided to reward them by increasing their overtime pay. Bob
Miller will re-ceive 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 cur-rently doesn’t get overtime pay. What function do you need to
use to convert a null value to 0?)
3. Add the orders shown to the Global Fast Foods copy_f_orders table:
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?
5. Sue Doe has been an outstanding Global Foods staff member and has been given a
sal-ary raise. She will now be paid the same as Bob Miller. Update her record in
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.
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.
8. Execute the following SQL statement. Record your results.
DELETE from departments
WHERE department_id = 60;
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.
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 job_history table.
Section 12.3
1. When would you want a DEFAULT value?
In the event that a new row is inserted and no value for the column is assigned, the
default value will be assigned instead of a null value. Using default values allows you
to control where and when the default value should be applied.
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?
3. Allison Plumb, the event planning manager for DJs on Demand, has just given you
the fol-lowing 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.
b. Create a copy of the D_CDS table called manager_copy_d_cds. What was the
correct syntax used?
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?
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?
4. Run the following 3 statements to create 3 new tables for use in a Multi-table insert
state-ment. 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 em-ployee_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
mes-sage and verify you have the following number of rows in each table:
Sal_history: 19 rows
Mgr_history: 19 rows
Special_sal: 1
