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

1: Use the CREATE table command to create the table below: 

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

2. Using the ALTER table command add the following column STUDENTS_TAB table 

Column name	Data Type/Bytes	Constraints	Null?
COURSE_NUMBER	NUMBER(10)		NOT NULL

3. Using the ALTER table command modify the NAME column and increase the size of the column to 25 bytes 

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

SQL> create table EMPLOY ( 

 ID VARCHAR2(4 BYTE) NOT NULL, 

 First_Name VARCHAR2(10 BYTE), 

 Last_Name VARCHAR2(10 BYTE), 

 Start_Date DATE, 

 End_Date DATE, 

 Salary Number(8,2), 

 City VARCHAR2(10 BYTE), 

 Description VARCHAR2(15 BYTE)); 

SQL> desc employ; 

Copy and paste the insert DML below to populate the EMPLOY table.

insert into Employ (ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description) values ('01','Jason', 'Martin', to_date('19960725','YYYYMMDD'),to_date('20060725','YYYYMMDD'), 1234.56,'Toronto', 'Programmer'); 

insert into Employ (ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)values('02','Alison', 'Mathews',to_date('19760321','YYYYMMDD'),to_date('19860221','YYYYMMDD'), 6661.78, 'Vancouver','Tester'); 

insert into Employ (ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)values('03','James', 'Smith', to_date('19781212','YYYYMMDD'), to_date('19900315','YYYYMMDD'), 6544.78, 'Vancouver','Tester'); 

Use the SELECT keyword to retrieve all the records as follows:

SQL> set lines 120; 

SQL> select * from EMPLOY; 

Results are displayed as follows:

ID FIRST_NAME LAST_NAME START_DAT END_DATE SALARY CITY DESCRIPTION 

---- ---------- ---------- --------- --------- ---------- ---------- --------------- 

01 Jason Martin 25-JUL-96 25-JUL-06 1234.56 Toronto Programmer 

02 Alison Mathews 21-MAR-76 21-FEB-86 6661.78 Vancouver Tester 

03 James Smith 12-DEC-78 15-MAR-90 6544.78 Vancouver Tester 

SERVICE REQUEST-3: 

1. Use the CREATE VIEW command to create a view called EmployView with the following columns: First_Name, Last_Name from the EMPLOY table 

2. Use the SELECT command to retrieve all records in the view.

3. Use the create index command to create an index named idx_employ on the ID field of the EMPLOY table. 

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;