RDBMS LAB PROGRAMS

Ex.No :01

Date:


Query Processing: 01
AIM:
To Create a table student master with the following fields name, regno, department and year with
suitable data types. Use select command to do the following

a) Select the students’s name column

b) Eliminate the duplicate entry in table

c) Sort the table in alphabetical order

d) Select all the students of a particular department

Procedure:
SQL> create table student_master(regno number(20) primary key,name varchar(20),dept
varchar(20),year number(4));

Table created.

SQL> select * from student_master;

no rows selected

SQL> insert into student_master values(101,'Gulam','Bsc.Cs',2011);

1 row created.

SQL> insert into student_master values(102,'Faisal','Bsc.IT',2012);

1 row created.

SQL> insert into student_master values(103,'Umar','B.com',2013);

1 row created.

SQL> insert into student_master values(104,'Yasar','B.A Eng',2014);

1 row created.

SQL> insert into student_master values(105,'Afridi','Bsc.che',2015);

1 row created.



1

,a) Select the students’s name column
SQL> select name from student_master;

NAME

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

Gulam

Faisal

Umar

Yasar

Afridi

b) Eliminate the duplicate entry in table
Create the primary key during the time of table creation

c) Sort the table in alphabetical order
SQL> select * from student_master order by name;

REGNO NAME DEPT YEAR

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

105 Afridi Bsc.che 2015

102 Faisal Bsc.IT 2012

101 Gulam Bsc.Cs 2011

103 Umar B.com 2013

104 Yasar B.A Eng 2014

d) Select all the students of a particular department
SQL> select * from student_master where dept='Bsc.Cs';

REGNO NAME DEPT YEAR

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

101 Gulam Bsc.Cs 2011




2

,Ex.No :02

Date:


Query Processing: 02
AIM:
To Create a table sales_order with sorderno,productno as primary key set other

field to clientno,delivery_data,order status.




a) add a new column for starting a salesman number using alter command.

b) set sorderno as foreign key as columnconstrains.

c) set the sorderno as foreign key as tableconstrains.



Procedure:
SQL> create table sales(sorderno number(20),prodno number(20)primary key);

Table created.




SQL> create table delivery( deliveryno number(20), delivery_add varchar(20),

delivery_data date);

Table created.




a) add a new column for starting a salesman number using alter command.

3

, SQL> alter table sales add(salesmanno number(20));

Table altered.



SQL> desc sales;



Name Null? Type

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

SORDERNO NUMBER(20)

PRODNO NOT NULL NUMBER(20)

SALESMANNO NUMBER(20)



b) set sorderno as foreign key as column constrains.



SQL> alter table delivery add(delno number(20)primary key);

Table altered.



SQL> desc delivery;

Name Null? Type

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

DELIVERYNO NUMBER(20)

DELIVERY_ADD VARCHAR2(20)

DELIVERY_DATA DATE

DELNO NOT NULL NUMBER(20)




c) set the sorderno as foreign key as table constrains.


4