SESSION: SUMMER 2017 LAB 1B – SQL * Plus - CREATING TABLES and CONSTRAINTS FOLLO
ID: 3888224 • Letter: S
Question
SESSION: SUMMER 2017
LAB 1B – SQL * Plus - CREATING TABLES and CONSTRAINTS
FOLLOW INSTRUCTIONS: DO NOT SKIP A SINGLE STEP
SECTION I:
NOTE: Do not specify any constraints with the create statement
SERVICE REQUEST-1:
CREATE the following 3 tables
TABLE NAME: CATEGORY_DETAILS
COLUMNS DATATYPE SIZE or BYTES
category_id number 2
category_name varchar2 30
TABLE NAME: SUB_CATEGORY_DETAILS
COLUMNS: DATATYPE SIZE or BYTES
sub_category_id number 2
category_id number 2
sub_category_name varchar2 30
TABLE NAME: PRODUCT_DETAILS
COLUMNS: DATATYPE SIZE or BYTES
product_id number 6
category_id number 2
sub_category_id number 2
product_name varchar2 30
SERVICE REQUEST-2:
Use the ALTER TABLE command to add a primary key constraint (without a constraint name) on column category_id on table CATEGORY_DETAILS
Run the query below to see the constraint:
SQL> select constraint_name, constraint_type, table_name
from user_constraints where table_name= 'CATEGORY_DETAILS';
Use the ALTER TABLE command to add a primary key constraint with a constraint name on column sub_category_id of table SUB_CATEGORY_DETAILS
Run the query below to see the constraint:
SQL> select constraint_name, constraint_type, table_name
from user_constraints where table_name= 'SUB_CATEGORY_DETAILS';
Use the ALTER TABLE command to add a foreign key constraint with a constraint name on column category_id on the SUB_CATEGORY_DETAILS table referencing category_id of the CATEGORY_DETAILStable.
Run the query below to see the constraint:
SQL> select constraint_name, constraint_type, table_name
from user_constraints where table_name= 'SUB_CATEGORY_DETAILS';
Use the ALTER TABLE command to add a new column called price with datatype number of 2 bytes to the PRODUCT_DETAILS table
Use the ALTER TABLE command to modify the data type of the price column to NUMBER(6,2)
Use the ALTER TABLE command to drop the price column
NOTE:
Dropping columns that are part of primary key or unique constraints and are referenced by some other table, you will need to also add the CASCADE CONSTRAINT to the ALTER TABLE command. If you drop a column that belongs to the primary key, oracle will drop both the column and the associated foreign key constraints.
Use the ALTER TABLE command to add a new column called BRANDNAME with VARCHAR2(20) NOT NULL to the PRODUCT_DETAILS table
Rename the table CATEGORY_DETAILS to CATDT
Write SQL DDL command to create a table called SP10STUDENT with the following fields and data types. Note the NOT NULL and NULL
Column name
Data Type/Bytes
Constraints
Null?
SNUM
NUMBER(5)
NOT NULL
SNAME
VARCHAR2(25)
NOT NULL
MAJOR
VARCHAR2(25)
NULL
SLEVEL
VARCHAR2(25)
NOT NULL
AGE
VARCHAR2(25)
NOT NULL
Use the INSERT command to ingest the sample records below into the SP10STUDENT
SAMPLE DATA in the SP10STUDENT table
SNUM
SNAME
MAJOR
SLEVEL
AGE
0418
S.Jack
Math
SO
18
0671
A.Smith
FR
20
1234
T.Banks
ME
SERVICE REQUEST
19
3726
M.Lee
SO
20
4829
J.Bale
Chemistry
JR
22
5765
L.Lim
SERVICE REQUEST
19
After inserting all the records, commit the them in to the database as:
SQL> commit;
Using the NVL function, write a query (SELECT) to retrieve records from the SNAME and MAJOR columns of the SP10STUDENT table substituting 'NOT DECLARED' for the MAJOR column when there is no value found.
HINT: LESSON 7 on how to use the (NVL) Null Value function
Run select * from SP10STUDENT to display all records in the table.
Run delete from SP10STUDENT to delete all rows from the SP10STUDENT table.
Run select * from SP10STUDENT to display all records in the table
Run rollback to undo the delete
Run select * from SP10STUDENT to display all records in the table.
SECTION II: CREATING VIEWS and INDEXES
TABLE NAME: STUDENTS_TAB
Column name
Data Type/Bytes
Constraints
Null?
NAME
VARCHAR2(10)
NOT NULL
REGISTER_DATE
DATE
NOT NULL
DOB
DATE
NOT NULL
Column name
Data Type/Bytes
Constraints
Null?
COURSE_NUMBER
NUMBER(10)
NOT NULL
Column name
Data Type/Bytes
Constraints
Null?
NAME
VARCHAR2(25)
NOT NULL
5. Study the create table and insert into SQL commands, copy and run the DDLs and DMLs for the EMPLOY table
Copy and paste the insert DML below to populate the EMPLOY table.
Use the SELECT keyword to retrieve all the records as follows:
Results are displayed as follows:
2. Use the SELECT command to retrieve all records in the view.
SECTION III
FOLLOW INSTRUCTIONS: DO NOT SKIP ANY STEP
1. Use the CREATE TABLE command to create the DBA_LOANS table below.
TBALE NAME: DBA_LOANS
Column Name Data Type Precision
LNO NUMBER 3
EMPNO NUMBER 5
TYPE CHAR 1
AMNT NUMBER 15, 3
2. Use the insert command to ingest records into the DBA_LOANS table.
EXAMPLE for the first Record:
insert into dba_loans values (23, 7499, 'M ', 30000);
LNO
EMPNO
TYPE
AMNT
23
7499
M
30000
42
7499
C
20000
65
7844
M
3564.20
3. Write a SQL * Plus query to retrieve all the records you inserted into the DBA_LOANS table above.
4. Write a SQL command to alter the DBA_LOANS table to add a new column named OUTST_BAL with datatype NUMBER, scale 15, precision 3 (15, 3).
5. Write an update statement (HINT: update DBA_LOANS set) command that will add 10% interest to all loans that have value M in the TYPE column. Your final result should look like
LNO
EMPNO
TYPE
AMNT
OUTST_BAL
23
7499
M
33,000.00
65
7844
M
3,920.62
6. Write a SQL command to delete all loans less than 30,000 from the DBA_LOANS table. HINT: you must use the where clause to filter or you loose all records.
Write another SQL DDL to rename the table DBA_LOANS to DBA_ACCOUNTS
HINT: SQL> rename X to Y;
Column name
Data Type/Bytes
Constraints
Null?
SNUM
NUMBER(5)
NOT NULL
SNAME
VARCHAR2(25)
NOT NULL
MAJOR
VARCHAR2(25)
NULL
SLEVEL
VARCHAR2(25)
NOT NULL
AGE
VARCHAR2(25)
NOT NULL
Explanation / Answer
SECTION I:
SERVICE REQUEST-1:
Create command is used to create the tables. The below are the create table commands for the given three table structures.
1. create table CATEGORY_DETAILS (category_id number(2),category_name varchar2(30));
2. create table SUB_CATEGORY_DETAILS (sub_category_id number(2),category_id number(2),
sub_category_name varchar2(30));
3. create table PRODUCT_DETAILS (product_id number(6),category_id number (2), sub_category_id number(2), product_name varchar2(30));
SERVICE REQUEST-2:
1. alter table CATEGORY_DETAILS add primary key(category_id);
2. alter table SUB_CATEGORY_DETAILS add constraint pk_scid primary key(sub_category_id);
3. alter table SUB_CATEGORY_DETAILS add constraint fk_catid foreign key(category_id) references CATEGORY_DETAILS(category_id);
4. alter table PRODUCT_DETAILS add price number(2);
5. alter table PRODUCT_DETAILS modify column price number(6,2);
6. alter table PRODUCT_DETAILS drop column price;
7. alter table PRODUCT_DETAILS add BRANDNAME varchar2(20) NOT NULL ;
8. rename table CATEGORY_DETAILS to CATDT;
This rename table command will give errors because it is having foreign key constraint, If there is any foreign key constraints then rename query will give errors.
9. create table SP10STUDENT (SNUM NUMBER(5) NOT NULL,SNAME VARCHAR2(25) NOT NULL,MAJOR VARCHAR2(25) NULL,SLEVEL VARCHAR2(25) NOT NULL,AGE VARCHAR2(25) NOT NULL);
10. insert into SP10STUDENT values (0418,'s.jack','MATH','SO','18');
insert into SP10STUDENT values (0671,'A.smith', ,'FR','20');
insert into SP10STUDENT values (1234,'T.banks','ME','SERVICE REQUEST','19');
insert into SP10STUDENT values (3726,'M.LEE', , 'so','20');
insert into SP10STUDENT values (4829,'J.Bale','Chemistry','JR','22');
insert into SP10STUDENT values (5765, 'L.Lim', , 'Service request','19');
11. COMMIT;
12. select SNAME,NVL(MAJOR,'NOT DECLARED') as Major from SP10STUDENT;
13. select * from SP10STUDENT;
14. delete from SP10STUDENT where 1=1;
15. select * from SP10STUDENT;
16. rollback;
17. select * from SP10STUDENT;
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.