CREATE TABLE called supplier with the following fields. supplier_id numeric(10) -> Primary key with constraint name...

 

 

Transcribed Image Text:

CREATE TABLE called supplier with the following fields. supplier_id numeric(10) -> Primary key with constraint name supplier_name varchar2(50)->unique name contact_name varchar2(50) city varchar2(10) Region should accept only ('N', 'NW', 'NE', 'S', 'SE', 'SW', 'W', 'E') 1. Insert 8 records (Insert records based on the below questions). 2. Display the details of the supplier who comes from Toronto and their supplier id 500 (note: you need to use make believe data). 3. Add phone number in the supplier table using DDL command 4. Write a sql command to make city as unused column 5. Delete the unused column in the supplier table 6. Rename table SUPPLIER -> SUPPLIER_CONTACT 7. Write a sql command to delete all the records in the supplier table. 8. Write a sql command to Retrieve the deleted table 9. Write a sql command to permanently delete the table, so that it is not moved to recyclebin Question 2 (5 marks) Create 2 tables based on the ER model shown below. Include all the constraints in the CREATE TABLE statements. You should have only two create Table statements and no ALTER TABLE statements. Name all constraints except NOT NULLs COMP122W21_002_P_40.EMPLOYEE P ENO ENAME * JOB MANAGER * JDATE GENDER CHAR (4 BYTE) VARCHAR2 (30 BYTE) VARCHAR2 (30 BYTE) CHAR (4 BYTE) TIMESTAMP CHAR (1 BYTE) SALARY NUMBER (8.2) COMISSION NUMBER (8,2) F DEPTNO NUMBER (0) EMPLOYEE_PK (ENO) SYS_CO0853807 (DEPTNO) COMP122W21_002_P_40.DEPARTMENT NUMBER (0) VARCHAR2 (30 BYTE) PDNO U DNAME U DLOCATION VARCHAR2 (30 BYTE) DEPARTMENT_PK (DNO) DEPARTMENT_DNAME_UN (DNAME) DEPARTMENT_DLOCATION_UN (DLOCATION) Use the column datatypes and sizes as indicated in the ER model ENO and DNO are used to uniquely identify rows in these tables. Each Employee must be assigned a department, and no duplicate department names are allowed. Assign commission default value 0. The Salary entered should be greater than zero. If the data is given in Gender column, only 'M' or 'F' are acceptable values Each table should have 8 data records in them Create and execute the SQL statements needed to enforce the data relationships among these tables. CREATE TABLE called supplier with the following fields. supplier_id numeric(10) -> Primary key with constraint name supplier_name varchar2(50)->unique name contact_name varchar2(50) city varchar2(10) Region should accept only ('N', 'NW', 'NE', 'S', 'SE', 'SW', 'W', 'E') 1. Insert 8 records (Insert records based on the below questions). 2. Display the details of the supplier who comes from Toronto and their supplier id 500 (note: you need to use make believe data). 3. Add phone number in the supplier table using DDL command 4. Write a sql command to make city as unused column 5. Delete the unused column in the supplier table 6. Rename table SUPPLIER -> SUPPLIER_CONTACT 7. Write a sql command to delete all the records in the supplier table. 8. Write a sql command to Retrieve the deleted table 9. Write a sql command to permanently delete the table, so that it is not moved to recyclebin Question 2 (5 marks) Create 2 tables based on the ER model shown below. Include all the constraints in the CREATE TABLE statements. You should have only two create Table statements and no ALTER TABLE statements. Name all constraints except NOT NULLs COMP122W21_002_P_40.EMPLOYEE P ENO ENAME * JOB MANAGER * JDATE GENDER CHAR (4 BYTE) VARCHAR2 (30 BYTE) VARCHAR2 (30 BYTE) CHAR (4 BYTE) TIMESTAMP CHAR (1 BYTE) SALARY NUMBER (8.2) COMISSION NUMBER (8,2) F DEPTNO NUMBER (0) EMPLOYEE_PK (ENO) SYS_CO0853807 (DEPTNO) COMP122W21_002_P_40.DEPARTMENT NUMBER (0) VARCHAR2 (30 BYTE) PDNO U DNAME U DLOCATION VARCHAR2 (30 BYTE) DEPARTMENT_PK (DNO) DEPARTMENT_DNAME_UN (DNAME) DEPARTMENT_DLOCATION_UN (DLOCATION) Use the column datatypes and sizes as indicated in the ER model ENO and DNO are used to uniquely identify rows in these tables. Each Employee must be assigned a department, and no duplicate department names are allowed. Assign commission default value 0. The Salary entered should be greater than zero. If the data is given in Gender column, only 'M' or 'F' are acceptable values Each table should have 8 data records in them Create and execute the SQL statements needed to enforce the data relationships among these tables.