DBS301 Lab 9 – Week 10 (DDL & DML)

$30.00

Category: You will Instantly receive a download link for .zip solution file upon Payment

Description

5/5 - (7 votes)

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’
DBS301 – Database Design II and SQL using Oracle Lab 9 – Week 10
2 | P a g e
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
DBS301 – Database Design II and SQL using Oracle Lab 9 – Week 10
3 | P a g e
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
executed
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 VARCHA
R2
37 – – – – –
LNAME VARCHA
R2
25 – – – – – –
PHONE# VARCHA
R2
20 – – – – –
SALARY NUMBER – 8 2 – – – –
COMMISS
ION
NUMBER – 2 2 – – –
JOBCODE VARCHA
R2
12 – – –
5. Lengthen FNAME in L09SalesRep to 37. The result of a DESCRIBE should show it
happening
DBS301 – Database Design II and SQL using Oracle Lab 9 – Week 10
4 | P a g e
You can only decrease the size or length of Name in L09GoodCust to the maximum
length of data already stored. Do it by using SQL and not by looking at each entry and
counting the characters. May take two SQL statements
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%.
10. Ensure that only valid RepId numbers from table L09SalesRep may be entered in
the table L09GoodCust. Why this statement has failed?
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;