Description

This week reviews and extends the knowledge of Data Definitional Language (Create and Alter) and DML (Data Manipulation Language).
Getting Started
Your submission will be a single text-based SQL file with appropriate header and commenting. Tasks
You will:
• create tables first,
• add / modify /remove some columns and finally • add / modify / remove some constraints in this lab.

1. Create table L09SalesRep and load it with data from table EMPLOYEES table. Use only the equivalent columns from EMPLOYEE as shown below and only for people in department 80.
Column Type
RepId NUMBER (6) FName VARCHAR2(20)
LName VARCHAR2(25)
Phone# VARCHAR2(20) ALL these columns’ data types match
Salary NUMBER(8,2) one’s in table EMPLOYEES
Commission NUMBER(2,2) You will have exactly 3 rows here.
2. Create L09Cust table.
CREATE TABLE L09Cust (
CUST# NUMBER(6),
CUSTNAME VARCHAR2(30), CITY VARCHAR2(20),
RATING CHAR(1),
COMMENTS VARCHAR2(200), SALESREP# NUMBER(7) );
NOTE: Caution that copying from WORD will create errors if WORD is using quotes that look like ‘this’ – SQL needs straight quotes like ‘this’

The constraints were left off in the above. The constraints shown below are what would normally be applied as shown. These were applied at the table level. Do not add these at this time, you will do so through the following questions.

Load the table with these values in the chart.
CUST# CUSTNAME CITY RAT SALESREP#
501 ABC LTD. Montreal C 201
502 Black Giant Ottawa B 202
503 Mother Goose London B 202
701 BLUE SKY LTD Vancouver B 102
702 MIKE and SAM Inc. Kingston A 107
703 RED PLANET Mississauga C 107
717 BLUE SKY LTD Regina D 102
3. Create table L09GoodCust by using following columns but only if their rating is A or B. Column Type
CustId NUMBER (6)
Name VARCHAR2(30)
Location VARCHAR2(20)  ALL these columns’ data types match ones
RepId NUMBER(7) in table L09Cust
 You will have exactly 4 rows here.
CUSTID NAME LOCATION REPID
502 Black Giant Ottawa 202
503 Mother Goose London 202
504 BLUE SKY LTD Vancouver 202
701 MIKE and SAM inc. Kingston 10

4. Now add new column to table L09SalesRep called JobCode that will be of variable character type with max length of 12. Do a DESCRIBE L09SalesRep to ensure it

5. Declare column Salary in table L09SalesRep as mandatory one and Column Location in table L09GoodCust as optional one. You can see location is already optional.
L09GoodCust before looks like the following

AFTER the change it would look as follows:
Table Column Data Type Length Prec. Scale PK Nullable Default Comment
SALESREP REPID NUMBER – 6 0 1 – – –
FNAME VARCHAR2 37 – – – – –
LNAME VARCHAR2 25 – – – – – –
PHONE# VARCHAR2 20 – – – – –
SALARY NUMBER – 8 2 – – – –
COMMISSION NUMBER – 2 2 – – –
JOBCODE VARCHAR2 12 – – –

5. Lengthen FNAME in L09SalesRep to 37. The result of a DESCRIBE should show it happening

6. Now get rid of the column JobCode in table L09SalesRep in a way that will not affect daily performance.
7. Declare PK constraints in both new tables  RepId and CustId
8. Declare UK constraints in both new tables  Phone# and Name
9. Restrict amount of Salary column to be in the range [6000, 12000] and Commission to be not more than 50%.

11. Firstly write down the values for RepId column in table L09GoodCust and then make all these values blank. Now redo the question 10. Was it successful?
12. Disable this FK constraint now and enter old values for RepId in table L09GoodCust and save them. Then try to enable your FK constraint. What happened?
13. Get rid of this FK constraint. Then modify your CK constraint from question 9 to allow Salary amounts from 5000 to 15000.

14. Describe both new tables L09SalesRep and L09GoodCust and then show all constraints for these two tables by running the following query:
SELECT constraint_name, constraint_type, search_condition, table_name
FROM user_constraints
WHERE lower(table_name) IN (‘l09salesrep’,’l09goodcust’)
ORDER BY table_name, constraint_type;