[Solved] DBS301 Lab10 (DDL & DML)

This week reviews and extends the knowledge of Data Definitional Language (Create and Alter)and DML (Data Manipulation Language).Getting StartedYour submission will be a single text-based SQL file with appropriate header and commenting.TasksYou 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. Useonly the equivalent columns from EMPLOYEE as shown below and only for people indepartment 80.Column TypeRepId NUMBER (6)FName VARCHAR2(20)LName VARCHAR2(25)Phone# VARCHAR2(20) ALL these columns’ data types matchSalary NUMBER(8,2) one’s in table EMPLOYEESCommission 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 102 | P a g eThe constraints were left off in the above. The constraints shown below are what wouldnormally be applied as shown. These were applied at the table level. Do not add these at thistime, 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 201502 Black Giant Ottawa B 202503 Mother Goose London B 202701 BLUE SKY LTD Vancouver B 102702 MIKE and SAM Inc. Kingston A 107703 RED PLANET Mississauga C 107717 BLUE SKY LTD Regina D 1023. Create table L09GoodCust by using following columns but only if their rating is A or B.Column TypeCustId NUMBER (6)Name VARCHAR2(30)Location VARCHAR2(20) → ALL these columns’ data types match onesRepId NUMBER(7) in table L09Cust→ You will have exactly 4 rows here.CUSTID NAME LOCATION REPID502 Black Giant Ottawa 202503 Mother Goose London 202504 BLUE SKY LTD Vancouver 202701 MIKE and SAM inc. Kingston 10DBS301 – Database Design II and SQL using Oracle Lab 9 – Week 103 | P a g e4. Now add new column to table L09SalesRep called JobCode that will be of variablecharacter type with max length of 12. Do a DESCRIBE L09SalesRep to ensure itexecuted5. Declare column Salary in table L09SalesRep as mandatory one and ColumnLocation in table L09GoodCust as optional one. You can see location is alreadyoptional.L09GoodCust before looks like the followingAFTER the change it would look as follows:Table Column DataTypeLength Prec. Scale PK Nullable Default CommentSALESREP REPID NUMBER – 6 0 1 – – –FNAME VARCHAR237 – – – – –LNAME VARCHAR225 – – – – – –PHONE# VARCHAR220 – – – – –SALARY NUMBER – 8 2 – – – –COMMISSIONNUMBER – 2 2 – – –JOBCODE VARCHAR212 – – –5. Lengthen FNAME in L09SalesRep to 37. The result of a DESCRIBE should show ithappeningDBS301 – Database Design II and SQL using Oracle Lab 9 – Week 104 | P a g eYou can only decrease the size or length of Name in L09GoodCust to the maximumlength of data already stored. Do it by using SQL and not by looking at each entry andcounting the characters. May take two SQL statements6. Now get rid of the column JobCode in table L09SalesRep in a way that will notaffect daily performance.7. Declare PK constraints in both new tables → RepId and CustId8. Declare UK constraints in both new tables → Phone# and Name9. Restrict amount of Salary column to be in the range [6000, 12000] and Commissionto be not more than 50%.10. Ensure that only valid RepId numbers from table L09SalesRep may be entered inthe table L09GoodCust. Why this statement has failed?11. Firstly write down the values for RepId column in table L09GoodCust and thenmake 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 L09GoodCustand 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 allowSalary amounts from 5000 to 15000.14. Describe both new tables L09SalesRep and L09GoodCust and then show allconstraints for these two tables by running the following query:SELECT constraint_name, constraint_type,search_condition, table_nameFROM user_constraintsWHERE lower(table_name) IN (‘l09salesrep’,’l09goodcust’)ORDER BY table_name, constraint_type;