III Sem DBMS - PART A

1. Execute a single line query and group functions.

AIM:

a) Using single line query:

• Find length of the string.

• Display lowercase string.
• Display Uppercase string.
• Concatenate two strings.
• Initialize the string with uppercase.
• Display date.

b) Perform group functions.

Query:

a) Using single line query:

SQL> select length('Mahith') length from dual;

LENGTH

SQL> select lower('MAHITH') lowercase from dual;

LOWERC

mahith

SQL> select upper('mahith') uppercase from dual;

UPPERC

------

MAHITH

SQL> select ('Mahith')||('Sasiklala') AS Name from dual;

NAME

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

MahithSasiklala

SQL> select initcap('smahith') AS initcaptital from dual;

INITCAP

-------

Smahith

SQL> select sysdate "today's Date" from dual;

today's D

---------

09-FEB-23

b) Perform Group Functions

SQL> create table customer(custid number(10),cname varchar2(10),address varchar2(30), salary number(7));

Table created.

SQL> insert into customer values(101, 'sasikala' , 'Delhi' , '50000');

1 row created.

SQL> insert into customer values(102, 'sachin' , 'Mumbai' , '500000');

1 row created.

SQL> insert into customer values(102, 'Rohit' , 'Kolkata' , '150000');

1 row created.

SQL> insert into customer values(104, 'Dinesh' , 'Pune' , '75000');

1 row created.

SQL> select * from customer;

CUSTID CNAME ADDRESS SALARY

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

101 sasikala Delhi 50000

102 sachin Mumbai 500000

102 Rohit Kolkata 150000

104 Dinesh Pune 75000

SQL> select cname, sum(salary) from customer group by cname;

CNAME SUM(SALARY)

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

Rohit 150000

sasikala 50000

sachin 500000

Dinesh 75000

SQL> select address,count(*) from customer GROUP BY address;

ADDRESS COUNT(*)

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

Delhi 1
 Mumbai 1

Kolkata 1

Pune 1

SQL> select cname,address ,count(*) from customer GROUP BY cname,address;

CNAME ADDRESS COUNT(*)

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

Dinesh Pune 1

sachin Mumbai 1

sasikala Delhi 1

Rohit Kolkata 1

SQL> select cname,address,sum(salary) from customer GROUP BY cname,address;

CNAME ADDRESS SUM(SALARY)

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

Dinesh Pune 75000

sachin Mumbai 500000

sasikala Delhi 50000

Rohit Kolkata 150000

SQL> select cname,sum(salary) from customer GROUP BY cname;

CNAME SUM(SALARY)

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

Rohit 150000

sasikala 50000

sachin 500000

Dinesh 75000

2. Execute DML Commands.

AIM:

• Create table student with attribute rno, name, age, gender

• Insert records into the table
• Perform all the DML(select,insert,update ,delete) commands on the table

Query:

Create table command

SQL> create table student(rno number(10),name varchar2(10),age number(2), gender varchar2(7));

Table created.

Insert table Command

SQL>insert into student values(101, ‘sachin’ , 35 , ‘male’);

1 row created.

SQL>insert into student values(102, ‘aarthi ’ , 26 , ‘female’);

1 row created.

SQL>insert into student values(103, ‘antony’ , 34 , ‘male’);

1 row created.

SQL>insert into student values(104, ‘yuvaraj’ , 18 , ‘male’);

1 row created.

SQL>insert into student values(105, ‘vandhana’ , 24 , ‘female’);

1 row created.

SQL> insert into student values(106, 'kaviya', 28, 'female');

1 row created.

Select command

SQL> select * from student;

RNO NAME AGE GENDER

---------- ---------- ---------- -------
101 sachin 35 male
102 aarthi 26 female
103 antony 34 male
104 yuvaraj 18 male
105 vandhana 24 male
106 Kaviya 28 female

6 rows selected.

SQL> select rno,name from student;

RNO NAME
---------- ----------
101 sachin
102 aarthi
103 antony
104 yuvaraj
105 vandhana
106 Kaviya

6 rows selected.
SQL> select * from student where age=18;

RNO NAME AGE GENDER

---------- ---------- ---------- -------
104 yuvaraj 18 male

SQL> select distinct age from student;

AGE
----------
35
26
34
18
24
28

Update command

SQL> alter table student add(contactno number(10));

Table altered.
SQL> update student set contactno=9444430881 where rno=101;
1 row updated.
SQL> update student set contactno=9444530881 where rno=102;
1 row updated.
SQL> update student set contactno=9050230881 where rno=103;
1 row updated.
SQL> update student set contactno=9538830881 where rno=104;
1 row updated.
SQL> update student set contactno=9538888301 where rno=105;
1 row updated.
SQL> update student set contactno=9538858301 where rno=106;
1 row updated.

SQL> select * from student;

RNO NAME AGE GENDER CONTACTNO

---------- ---------- ---------- ------- --------------
101 sachin 35 male 9444430881
102 aarthi 26 female 9444530881
103 antony 34 male 9050230881
104 yuvaraj 18 male 9538830881
105 vandhana 24 male 9538888301
106 Kaviya 28 female 9538858301

6 rows selected.

Delete command
SQL> delete from student where rno=106;
1 row deleted.

SQL> select * from student;

RNO NAME AGE GENDER CONTACTNO
---------- ---------- ---------- ------- --------------
101 sachin 35 male 9444430881
102 aarthi 26 female 9444530881
103 antony 34 male 9050230881
104 yuvaraj 18 male 9538830881
105 vandhana 24 male 9538888301

3. Execute DDL Commands.

AIM:
• Perform all the DDL(Alter, Rename, Drop, Truncate) commands on the table

Query:

Alter table Command

SQL> alter table student add (city varchar2(10));

Table altered.

RENAME command

SQL> rename student to student_details;

Table renamed.

SQL> desc student_details;

Name Null? Type

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

RNO NUMBER(10)

NAME VARCHAR2(10)

AGE NUMBER(2)

GENDER VARCHAR2(7)

CONTACTNO NUMBER(10)

CITY VARCHAR2(10)

Truncate Command

SQL> truncate table student_details;

Table truncated.

SQL> select * from student_details;

no rows selected
4. Execute DCL commands
AIM:
• Create table student1 with attribute stu_name,roll_no,course_enrolled in sys user
• Insert the tuples values
• Perform DCL commands(Grant, Revoke) command

Query:
SQL> create table student1(stud_name varchar2(20),roll_no varchar2(10),course_enrolled varchar2(20));
Table created.

SQL> insert into student1 values('mahith','SCS394303','BCA');

1 row created.
SQL> insert into student1 values('kaviya','SCS394708', 'BSC');
1 row created.

SQL> select * from student1;

STUD_NAME ROLL_NO COURSE_ENROLLED

-------------------- ---------- --------------------
mahith SCS394303 BCA
kaviya SCS394708 BSC

grant command:
a) To grant SELECT permission to a single user To grant the SELECT permission to the sys(user name) of the
college
SQL> grant select on student1 to sys;
Grant succeeded.

b) To grant SELECT permission to public We write the following command to grant the SELECT permission to all
database users.
SQL> grant select on student1 to PUBLIC;
Grant succeeded.

c) The WITH GRANT OPTION clause: (This clause is used to permit the user who already has some permissions
on a particular table so that they can give those access to other users)
SQL> grant select on student1 to sys with grant OPTION;
Grant succeeded.

d) To grant all permissions to a particular user:

SQL> grant select,insert,update,delete on student1 to sys;
Grant succeeded.
SQL> grant all on student1 to sys;
Grant succeeded.

revoke command
SQL> revoke all on student1 from sys;
Revoke succeeded.
5. Execute TCL(commit,rollback,savepoint ) Commands
AIM:
• Perform TCL (commit,savepoint,rollback) command operation

Query:
SQL> select * from student1;
STUD_NAME ROLL_NO COURSE_ENROLLED
-------------------- ---------- --------------------
mahith SCS394303 BCA
kaviya SCS394708 BSC

commit command
SQL> commit;
Commit complete.

SQL> insert into student1 values('yuvaraj','U19UU0196',' BCA ');

1 row created.

SQL> select * from student1;

STUD_NAME ROLL_NO COURSE_ENROLLED
-------------------- ---------- --------------------
mahith SCS394303 BCA
kaviya SCS394708 BSC
yuvaraj U19UU0196 BCA

SQL> rollback;
Rollback complete.

SQL> select * from student1;

STUD_NAME ROLL_NO COURSE_ENROLLED
-------------------- ---------- --------------------
mahith SCS394303 BCA
kaviya SCS394708 BSC

savepoint command

SQL> savepoint one;

Savepoint created.

SQL> insert into student1 values('monisha','U19UU0001','BBA');

1 row created.
SQL> savepoint two;
Savepoint created.
SQL> insert into student1 values('gomathi','U19UU0011','BCA');
1 row created.

SQL> select * from student11;

STUD_NAME ROLL_NO COURSE_ENROLLED
-------------------- ---------- --------------------
mahith SCS394303 BCA
kaviya SCS394708 BSC
monisha U19UU0001 BBA
gomathi U19UU0011 BCA

rollback command
SQL> rollback to one;
Rollback complete.
SQL> select *from student1;
STUD_NAME ROLL_NO COURSE_ENROLLED
-------------------- ---------- --------------------
mahith SCS394303 BCA
kaviya SCS394708 BSC

SQL> rollback to two;

Rollback complete.

SQL> select * from student11;

STUD_NAME ROLL_NO COURSE_ENROLLED
-------------------- ---------- --------------------
mahith SCS394303 BCA
kaviya SCS394708 BSC
monisha U19UU0001 BCA

6. Implement the Nested Queries

AIM:
• Create a student table and studentnew table
• Insert the tuple values from student table
• Subquery with SELECT statement
• Subquery with INSERT statement
• Subquery with UPDATE statement
• Subquery with DELETE statement

Query:
SQL> create table student(rno number(10),name varchar2(10),age number(2), gender varchar2(7), contactno
number(10), fees number(8));

Table created.

SQL> insert into student values(101, 'sachin' , 35 , 'male', 9444430881, 20000);

1 row created.

SQL> insert into student values(102, 'aarthi' , 26 , 'female', 9444530881, 60000);

1 row created.
SQL> insert into student values(103, 'antony' , 34 , 'male', 9050230881, 42000);

1 row created.

SQL> insert into student values(104, 'yuvaraj' , 18 , 'male', 9538830881, 85000);

1 row created.

SQL> insert into student values(105, 'vandhana' , 24 , 'female', 9538888301, 85000);

1 row created.

SQL> select * from student;

RNO NAME AGE GENDER CONTACTNO FEES

---------- ---------- ---------- ------- ---------- ----------
101 sachin 35 male 9444430881 20000
102 aarthi 26 female 9444530881 60000
103 antony 34 male 9050230881 42000
104 yuvaraj 18 male 9538830881 85000
105 vandhana 24 female 9538888301 85000

Subquery SELECT command

SQL> select *from student where rno IN(select rno from student where fees>20000);

RNO NAME AGE GENDER CONTACTNO FEES

---------- ---------- ---------- ------- ---------- ----------
102 aarthi 26 female 9444530881 60000
103 antony 34 male 9050230881 42000
104 yuvaraj 18 male 9538830881 85000
105 vandhana 24 female 9538888301 85000

SQL> select *from student where gender='male' AND fees>ANY (select fees from student where
gender='male');

RNO NAME AGE GENDER CONTACTNO FEES

---------- ---------- ---------- ------- ---------- ----------
103 antony 34 male 9050230881 42000
104 yuvaraj 18 male 9538830881 85000

SQL> create table studentnew(rno number(10),name varchar2(20), age number(5),gender

varchar2(20),contactno number(10), fees number(10));

Table created.

Subquery INSERT command

SQL> insert into studentnew select *from student where rno IN(select rno from student);

5 rows created.

RNO NAME AGE GENDER CONTACTNO FEES

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

101 sachin 35 male 9444430881 20000

102 aarthi 26 female 9444530881 60000

103 antony 34 male 9050230881 42000

104 yuvaraj 18 male 9538830881 85000

Subquery UPDATE command

SQL> update student set fees =fees*0.25 where age IN (select age from studentnew where age>=21);
4 rows updated.

SQL> select *from studentnew;

RNO NAME AGE GENDER CONTACTNO FEES

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

101 sachin 35 male 9444430881 20000

102 aarthi 26 female 9444530881 60000

103 antony 34 male 9050230881 42000

104 yuvaraj 18 male 9538830881 85000

105 vandhana 24 female 9538888301 85000

Subquery DELETE command

SQL> delete from student where gender NOT IN(Select gender from studentnew where gender='female');
3 rows deleted.

SQL> select * from student;

RNO NAME AGE GENDER CONTACTNO FEES

---------- ---------- ---------- ------- ---------- ----------
102 aarthi 26 female 9444530881 15000
105 vandhana 24 female 9538888301 21250

SQL> select *from studentnew;

 RNO NAME AGE GENDER CONTACTNO FEES
---------- -------------------- ---------- -------------------- ---------- ----------

101 sachin 35 male 9444430881 20000

102 aarthi 26 female 9444530881 60000

103 antony 34 male 9050230881 42000

104 yuvaraj 18 male 9538830881 85000

105 vandhana 24 female 9538888301 85000

7. Implement Join operations in SQL

AIM:
• Create two tables stud1 and stud2
• Insert the tuple values
• Implement the different join operation
a) INNER JOIN
b) CROSS JOIN
c) LEFT OUTER JOIN
d) RIGHT OUTER JOIN
e) FULL OUTER JOIN

Query:

SQL> create table stud1(regno number(10),name varchar2(20), age number(5),address

varchar2(20),contactno number(10), salary number(10));

Table created.

SQL> insert into stud1 values(101, 'DILIP' , 25 , 'Malur', 9876543210, 35000);

1 row created.

SQL> insert into stud1 values(102, 'KUMAR' , 26 , 'Bangalore', 9874563210, 25000);

1 row created.

SQL> insert into stud1 values(103, 'SASI' , 24 , 'Hosur', 9873210456, 20000);

1 row created.

SQL> select * from stud1;

REGNO NAME AGE ADDRESS CONTACTNO SALARY

------------- -------------------- ----------- -------------------- ---------- ----------
 101 DILIP 25 Malur 9876543210 35000

102 KUMAR 26 Bangalore 9874563210 25000

103 SASI 24 Hosur 9873210456 20000

SQL> create table stud2(regno number(10),name varchar2(20), Marks number(5));

Table created.

SQL> insert into stud2 values(101, 'DILIP' , 2400);

1 row created.

SQL> insert into stud2 values(102, 'KUMAR' , 2200);

1 row created.

SQL> insert into stud2 values(104, 'KANNAN' , 2200);

1 row created.

SQL> select * from stud2;

REGNO NAME MARKS

---------- -------------------- ----------
101 DILIP 2400
102 KUMAR 2200
104 KANNAN 2200

INNER JOIN command

SQL> select stud1.regno, stud2.name from stud1 INNER JOIN stud2 ON stud1.regno=stud2.regno;

REGNO NAME
---------- --------------------
101 DILIP
102 KUMAR

CROSS JOIN command

SQL> select * from stud1 CROSS JOIN stud2;

REGNO NAME AGE ADDRESS CONTACTNO SALARY REGNO NAME MARKS

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

101 DILIP 25 Malur 9876543210 35000 101 DILIP 2400

 101 DILIP 25 Malur 9876543210 35000 102 KUMAR 2200

101 DILIP 25 Malur 9876543210 35000 104 KANNAN 2200

102 KUMAR 26 Bangalore 9874563210 25000 101 DILIP 2400

102 KUMAR 26 Bangalore 9874563210 25000 102 KUMAR 2200

102 KUMAR 26 Bangalore 9874563210 25000 104 KANNAN 2200

103 SASI 24 Hosur 9873210456 20000 101 DILIP 2400

103 SASI 24 Hosur 9873210456 20000 102 KUMAR 2200

103 SASI 24 Hosur 9873210456 20000 104 KANNAN 2200

9 rows selected.

LEFT OUTER JOIN command

SQL> select *from stud1 LEFT OUTER JOIN stud2 ON stud1.regno=stud2.regno(+);

REGNO NAME AGE ADDRESS CONTACTNO SALARY REGNO NAME MARKS

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

101 DILIP 25 Malur 9876543210 35000 101 DILIP 2400

102 KUMAR 26 Bangalore 9874563210 25000 102 KUMAR 2200

103 SASI 24 Hosur 9873210456 20000

RIGHT OUTER JOIN command

SQL> select *from stud1 RIGHT OUTER JOIN stud2 ON stud1.regno(+)=stud2.regno;
REGNO NAME AGE ADDRESS CONTACTNO SALARY REGNO NAME MARKS

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

101 DILIP 25 Malur 9876543210 35000 101 DILIP 2400

102 KUMAR 26 Bangalore 9874563210 25000 102 KUMAR 2200

104 KANNAN 2200

FULL OUTER JOIN command

SQL> select *from stud1 FULL OUTER JOIN stud2 ON stud1.regno=stud2.regno;
REGNO NAME AGE ADDRESS CONTACTNO SALARY REGNO NAME MARKS

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

101 DILIP 25 Malur 9876543210 35000 101 DILIP 2400

102 KUMAR 26 Bangalore 9874563210 25000 102 KUMAR 2200

104 KANNAN 2200

103 SASI 24 Hosur 9873210456 20000

8. Create views for a particular table

AIM:
• Create a table student and student_marks
• Insert the tuple value
• Implement the view

a) Creating a view(single and multiple table)

b) Deleting view
Query:

SQL> create table student_marks (rno number(10),name varchar2(20), age number(5),marks number(10));
Table created.
SQL> insert into student_marks values(101,'sasikala',40,97);
1 row created.
SQL> insert into student_marks values(102,'aarthi',24,86);
1 row created.
SQL> insert into student_marks values(103,'antim',21,74);
1 row created.
SQL> insert into student_marks values(104,'yubaraj',21,90);
1 row created.
SQL> select *from student_marks;

RNO NAME AGE MARKS

---------- -------------------- ---------- ----------
101 sasikala 40 97
102 aarthi 24 86
103 antim 21 74
104 yubaraj 21 90

Create view single table command

SQL> create view V1 AS select name,gender from student where age>21;
View created.

SQL> select *from V1;

NAME GENDER
---------- -------
aarthi female
vandhana female

Create view multiple table command

SQL> create view V2 AS select student.rno,student.name,student.gender,student_marks.marks from
student,student_marks where student.rno=student_marks.rno;
View created.

RNO NAME GENDER MARKS

---------- ---------- ------- ----------
102 aarthi female 86

Delete view table command

SQL> drop view v2;
View dropped.

SQL> select * from v2;

select * from v2
*
ERROR at line 1:
ORA-00942: table or view does not exist

9. Write pl/sql procedure for an application using exception handling.

SQL> alter session set plsql_warnings='enable:all';

Session altered.

SQL> set serveroutput on;

SQL> select * from student;

RNO NAME AGE GENDER CONTACTNO FEES

---------- ---------- ---------- ------- ---------- ----------
102 aarthi 26 female 9444530881 15000
105 vandhana 24 female 9538888301 21250

DECLARE
s_rno student.rno%type := 10;
s_name student.name%type;
s_age student.age%type;
s_gender student.gender%type;
BEGIN
SELECT rno, name, age, gender INTO s_rno,s_name,s_age,s_gender FROM student WHERE rno = s_rno;
dbms_output.put_line(s_rno || ' ' || s_name || ' ' || s_age||' '||s_gender);
EXCEPTION
WHEN no_data_found THEN
dbms_output.put_line('No such student!');
WHEN others THEN
dbms_output.put_line('Error!');
END;
/

No such student!

PL/SQL procedure successfully completed.

10 . WRITE PL/SQL PROCEDURE FOR AN APPLICATION USING CURSORS

SQL> alter session set plsql_warnings='enable:all';
Session altered.
SQL> set serveroutput on;
SQL> select *from student;

RNO NAME AGE GENDER CONTACTNO FEES

---------- ---------- ---------- ------- ---------- ----------
102 aarthi 26 female 9444530881 15000
105 vandhana 24 female 9538888301 21250

DECLARE
s_rno student.rno%type := 10;
s_name student.name%type;
s_age student.age%type;
s_gender student.gender%type;
CURSOR cur_student is
SELECT rno, name, age, gender from student;
BEGIN
OPEN cur_student;
LOOP
FETCH cur_student INTO s_rno,s_name,s_age,s_gender;
EXIT WHEN cur_student%notfound;
dbms_output.put_line(s_rno ||''||s_name||''||s_age||''||s_gender);
END LOOP;
CLOSE cur_student;
END;
/

102aarthi26female
105vandhana24female

PL/SQL procedure successfully completed.

11. Write a pl/sql procedure for an application using function.

SQL> alter session set plsql_warnings='enable:all';

Session altered.
SQL> set serveroutput on;

declare
a number;
b number;
c number;
function findmax(x in number, y in number)
return number
is
z number;
begin
if x>y then
z:=x;
else
z:=y;
end if;
return z;
end;
begin
a:=23;
b:=45;
c:=findmax(a,b);
dbms_output.put_line('Maximum of (23,45):' || c);
end;
/

Maximum of (23,45):45

PL/SQL procedure successfully completed.

12. Write a pl/sql procedure for an application using package

SQL> select * from customer;

CUSTID CNAME ADDRESS SALARY

---------- ---------- ------------------------------ ----------
101 sasikala Delhi 50000
102 sachin Mumbai 500000
102 Rohit Kolkata 150000
104 Dinesh Pune 75000

SQL> create package cust_sal as

2 procedure find_sal(c_custid customer.custId%type);
3 END cust_sal;
4 /

Package created.
SQL> create or replace package body cust_sal as
2
3 procedure find_sal(c_custid customer.custid%type) is
4 c_sal customer.salary%type;
5 begin
6 select salary into c_sal
7 from customer
8 where custid=c_custid;
9 dbms_output.put_line('salary:'||c_sal);
10 end find_sal;
11 end cust_sal;
12 /

Package body created.

Enter value for cc_custid: 101

old 2: code customer.custid%type := &cc_custid;
new 2: code customer.custid%type := 101;

PL/SQL procedure successfully completed.