GOVERNMENT FIRST

GRADE COLLEGE,
BANGARPET
DEPARTMENT OF
COMPUTER SCIENCE

DBMS LAB MANUAL -NEP

2022-2023
DBMS LAB PROGRAMS -NEP

PART A

1. EXECUTE A SINGLE LINE QUERY AND GROUP FUNCTIONS.

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) Create a table employee with the attributes empid, ename and salary.
c) Enter at least 5 tuples.
d) Perform group functions.

a) Using single line query:

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

LENGTH
----------
6

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

LOWERC
------
pushpa

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

UPPERC
------
PUSHPA

GOVERNMENT FIRST GRADE COLLEGE, BANGARPET

1
DBMS LAB PROGRAMS -NEP

SQL> select ('Pushpa')||('Raj') as Name from dual;

NAME
--------- PushpaRaj

SQL> select initcap('pushpa') as initcaptital from dual;

INITCA
------ Pushpa

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

today's D ---------
13-JAN-23

b) Create a table employee with the attributes empid, ename and salary.

SQL> create table employee(empid number(5)primary key, ename varchar2(20), salary

decimal(10,5));

Table created.
c) Enter at least 5 tuples.

SQL> insert into employee values(102,'shekar',36000.78);

1 row created.
SQL> insert into employee values(103,'arun',84000.00);
1 row created.
SQL> insert into employee values(104,'preethi',64000);
1 row created.
SQL> insert into employee values(105,'kavan',34000);
1 row created.

d) Perform group functions.

SQL> select count(empid) as Totalemployees from employee;

TOTALEMPLOYEES
--------------
5

GOVERNMENT FIRST GRADE COLLEGE, BANGARPET

2
DBMS LAB PROGRAMS -NEP

SQL> select avg(salary) as Total_average_salary from employee;

TOTAL_AVERAGE_SALARY
--------------------
50400.29

SQL> select max(salary) as Maximum_salary from employee;

MAXIMUM_SALARY
--------------
84000

SQL> select min(salary) as Minimum_salary from employee;

MINIMUM_SALARY
--------------
34000

SQL> select sum(salary) as total_sum_of_salary from employee;

TOTAL_SUM_OF_SALARY
-------------------
252001.45

2.EXECUTE DDL COMMANDS.

• Crate table student1 with attribute Regno, student_name, phonenumber, dob,

percentage
• Insert records into the table
• Perform all the DDL(Alter, rename, drop, truncate) commands on the table

a) CREATE Command

SQL> create table student1(Regno varchar(20), student_name varchar(20), phonenumber

number(10), dob date, percentage number(4,2));
Table created.

GOVERNMENT FIRST GRADE COLLEGE, BANGARPET

3
 DBMS LAB PROGRAMS -NEP

SQL> desc student1

Name Null? Type
----------------------------------------- -------- ----------------------------
REGNO VARCHAR2(20)
STUDENT_NAME VARCHAR2(20)
PHONENUMBER NUMBER(10)
DOB DATE
PERCENTAGE NUMBER(4,2)

b) ALTER COMMAND
SQL> alter table student1 add(grade char(2)); Table
altered.
SQL> desc student1
Name Null? Type
----------------------------------------- -------- ----------------------------
REGNO VARCHAR2(20)
STUDENT_NAME VARCHAR2(20)
PHONENUMBER NUMBER(10)
DOB DATE
PERCENTAGE NUMBER(4,2)
GRADE CHAR(2)

SQL> alter table student1 modify(Regno number(20)); Table

altered.
SQL> desc student1
Name Null? Type
----------------------------------------- -------- ----------------------------
REGNO NUMBER(20)
STUDENT_NAME VARCHAR2(20)
PHONENUMBER NUMBER(10)
DOB DATE
PERCENTAGE NUMBER(4,2)
GRADE CHAR(2)

GOVERNMENT FIRST GRADE COLLEGE, BANGARPET

4
 DBMS LAB PROGRAMS -NEP

RENAME command
SQL> rename student1 to student_details;
Table renamed. SQL> desc student1 ERROR:
ORA-04043: object student1 does not exist

SQL> desc student_details

Name Null? Type
--------------------------------- -------- -------- ----------------------------
REGNO NUMBER(20)
STUDENT_NAME VARCHAR2(20)
PHONENUMBER NUMBER(10)
DOB DATE
PERCENTAGE NUMBER(4,2)
GRADE CHAR(2)

DROP Command
SQL> drop table student_details ; Table
dropped.
SQL> desc student_details ERROR:
ORA-04043: object student_details does not exist
TRUNCATE table
SQL> truncate table student_details; Table
truncated.

3.EXECUTE DML COMMANDS

 Create table kdc_student with attribute Regno, Name , Gender, Dob, Course
 Perform all the DML(Insert,update ,delete,select) commands on the table
Create table command
SQL> CREATE TABLE kdc_student(Regno NUMBER(3), Name VARCHAR(20),
Gender CHAR, Dob DATE,Course CHAR(5));
Table created.
Insert table Command
SQL> INSERT INTO kdc_student VALUES(102,’Mohith G Kalyan’,’M’,’20-Aug-
1980’,’BBM’);
1 row created.

GOVERNMENT FIRST GRADE COLLEGE, BANGARPET

5
DBMS LAB PROGRAMS -NEP

SQL> INSERT INTO kdc_student VALUES(106,’Nisarga’,’F’,’15-Jul-1983’,’BCom’);

1 row created.
SQL> INSERT INTO kdc_student VALUES(105,’Eenchara’,’F’,’04-Dec-
1985’,’BCA’); 1 row created.
SQL> INSERT INTO kdc_student VALUES(103,’Ravi K’,’M’,’29-Mar-
1989’,’BCom’); 1 row created.
SQL> INSERT INTO kdc_student VALUES(104,’Roopa’,’F’,’17-Jan-1984’,’BBM’);
1 row created.

Select command
SQL> SELECT * FROM kdc_student;

REGNO NAME G DOB COURSE

102 Mohith G Kalyan M 20-AUG-80 BBM
106 Nisarga F 15-JUL-83 BCom
105 Eenchara F 04-DEC-85 BCA
103 Ravi K M 29-MAR-89 BCom
104 Roopa F 17-JAN-84 BBM

SQL> SELECT Regno, Name, Course FROM kdc_student;

REGNO NAME COURSE

---------- - ------------------- -----
102 Mohith G Kalyan BBM
106 Nisarga BCom
105 Eenchara BCA
103 Ravi K BCom
104 Roopa BBM

SQL> SELECT * FROM kdc_student WHERE Course=’BCA’;

REGNO NAME G DOB COURSE

---------- -------------------- --------- -----
105 Eenchara F 04-DEC-85 BCA

GOVERNMENT FIRST GRADE COLLEGE, BANGARPET

6
DBMS LAB PROGRAMS -NEP

SQL> SELECT DISTINCT Course from kdc_student;

COURSE
-----
BBM
BCom
BCA
Delete command
SQL> DELETE FROM kdc_student WHERE Regno=103; 1
row deleted.
SQL> select * from kdc_student;
REGNO NAME G DOB COURS
---------- -------------------- - --------- ----- 102 Mohith G Kalyan M
20-AUG-80 BBM
106 Nisarga F 15-JUL-83 BCom
105 Eenchara F 04-DEC-85 BCA
104 Roopa F 17-JAN-84 BBM

Update command
SQL> UPDATE kdc_student SET course=’BCA’; 3
rows updated.

SQL> select * from kdc_student;

REGNO NAME G DOB COURS

---------- -------------------- - --------- -----
102 Mohith G Kalyan M 20-AUG-80 BCA
106 Nisarga F 15-JUL-83 BCA
105 Eenchara F 04-DEC-85 BCA
104 Roopa F 17-JAN-84 BCA

SQL> UPDATE kdc_student SET Course=’BBM’ WHERE Regno=102; 1

row updated.

SQL> select * from kdc_student;

GOVERNMENT FIRST GRADE COLLEGE, BANGARPET

7
 DBMS LAB PROGRAMS -NEP

REGNO NAME G DOB COURS

---------- -------------------- - --------- -----
102 Mohith G Kalyan M 20-AUG-80 BBM
106 Nisarga F 15-JUL-83 BCA
105 Eenchara F 04-DEC-85 BCA
104 Roopa F 17-JAN-84 BCA

4.EXECUTE DCL COMMANDS

• Create table student with attribute ROLL_NO NAME ADDRES PHONE AGE in sys
user
• Insert the tuples into the relation
• Perform DCL commands ( Grant, Revoke)command

SQL> select * from student;

ROLL_NO NAME ADDRESS PHONE AGE

---------- ---------- -------------------- ---------- ----------
1 varun tumkur 1234567890 1
2 tharun bangalore 2234567890 23
3 murthy mangalore 3334567890 40
4 rajit bangalore 2234567890 23
5 tavanesh mangalore 3334567890 40
6 supriya bangalore 2234567890 23

6 rows selected.

Create user user2

Create user user2 in sys

SQL> alter session set "_oracle_script"=true; Session altered.

SQL> create user user2 identified by user2; User created.

SQL> grant connect,resource to user2;

a) Grant select permission on the table student to user2
SQL> show user;
USER is "SYS"
SQL> grant select on sys.student to user2;
Grant succeeded.

GOVERNMENT FIRST GRADE COLLEGE, BANGARPET

8
DBMS LAB PROGRAMS -NEP

Connect to user2

C:\Users\Varu>sqlplus

Enter user-name: user2 Enter

password:
Last Successful login time: Wed Jan 18 2023 06:16:42 +05:30

SQL> show user;

USER is "USER2"
SQL> select * from sys.student;

ROLL_NO NAME ADDRESS PHONE AGE

---------- ---------- -------------------- ---------- ---------- 1 varun tumkur
1234567890 12
2 tharun bangalore 2234567890 23
3 murthy mangalore 3334567890 40
4 rajit bangalore 2234567890 23
5 tavanesh mangalore 3334567890 40
6 supriya bangalore 2234567890 23

6 rows selected.

Revoke the select privilege from user2 from sys SQL> revoke
select on sys.student from user2; Revoke succeeded.

SQL> select * from sys.student;

select * from sys.student
*
ERROR at line 1:
ORA-01031: insufficient privileges

5.EXECUTE TCL(COMMIT,ROLLBACK.SAVEPOINT ) COMMANDS

• Create table studentdemo with attribute id,name
• Insert records into the table
• Perform TCL operation

SQL> create table studentDemo(id integer,name varchar2(10)); Table created.

SQL> insert into studentdemo values(100 ,'varun'); 1 row created.
SQL> insert into studentdemo values(101 ,'murthy'); 1 row created.

GOVERNMENT FIRST GRADE COLLEGE, BANGARPET

9
 DBMS LAB PROGRAMS -NEP

SQL> select * from studentdemo;

ID NAME
---------- ----------
100 varun
101 murthy

SQL> commit; Commit

complete.

SQL> insert into studentdemo values(102 ,'tharun'); 1 row created.

SQL> select * from studentdemo;

ID NAME
---------- ----------
100 varun
101 murthy
102 tharun

SQL> rollback; Rollback

complete.
SQL> select * from studentdemo;

ID NAME
---------- ----------
100 varun
101 murthy
SQL> savepoint one;
Savepoint created.
SQL> insert into studentdemo values(103 ,'ramesh'); 1 row created.
SQL> savepoint two; Savepoint created.
SQL> insert into studentdemo values(104 ,'ravi'); 1 row created.
SQL> select * from studentdemo;
ID NAME
---------- ----------
100 varun
101 murthy
103 ramesh
104 ravi
SQL> rollback to two; Rollback
complete.

GOVERNMENT FIRST GRADE COLLEGE, BANGARPET

10
 DBMS LAB PROGRAMS -NEP

SQL> select * from studentdemo;

ID NAME
---------- ----------
100 varun
101 murthy
103 ramesh

6.IMPLEMENT THE NESTED QUERIES

• Create table customers1 with attribute ID ,name ,age, address SALARY

• Insert at least 4 tuples into the table
• Write Nested Queries to updates SALARY by 0.25 times in the CUSTOMERS table
for all the customers whose AGE is greater than or equal to 27.

SQL> CREATE TABLE CUSTOMERS1(ID INT NOT NULL,NAME VARCHAR(20) NOT

NULL,AGE INT NOT NULL,ADDRESS CHAR (25) ,SALARY DECIMAL (18,
2),PRIMARY
KEY (ID));
Table created.

SQL> INSERT INTO CUSTOMERS1 VALUES (1, 'Ramesh', 32, 'Ahmedabad', 2000.00 ); 1
row created.
SQL> INSERT INTO CUSTOMERS1 VALUES(2, 'varun', 25, 'Delhi', 1500.00 );
1 row created.
SQL> INSERT INTO CUSTOMERS1 VALUES(3, 'kaushik', 23, 'bopal', 2000.00 );
1 row created.
SQL> INSERT INTO CUSTOMERS1 VALUES(4, 'narasimha', 25, 'Mumbai', 6500.00 ); 1
row created.
SQL> select * from customers1;

ID NAME AGE ADDRESS SALARY

---------- -------------------- ---------- ------------------------- ----------
1 Ramesh 32 Ahmedabad 2000
2 varun 25 Delhi 1500
3 kaushik 23 bopal 2000
4 narasimha 25 Mumbai 6500

GOVERNMENT FIRST GRADE COLLEGE, BANGARPET

11
 DBMS LAB PROGRAMS -NEP

SQL> UPDATE CUSTOMERS1

2 SET SALARY = SALARY * 0.25
3 WHERE AGE IN (SELECT AGE FROM CUSTOMERS1
4 WHERE AGE >= 27 );

1 row updated.
SQL> select * from customers1;

ID NAME AGE ADDRESS SALARY

---------- -------------------- ---------- ------------------------- ----------
1 Ramesh 32 Ahmedabad 500
2 varun 25 Delhi 1500
3 kaushik 23 bopal 2000
4 narasimha 25 Mumbai 6500

7.IMPLEMENT JOIN OPERATIONS IN SQL

• Create table R1 with attribute C_id, Course

• Crate table R2 with attribute C_id , Faculty
• Insert Records into R1 and R2
• Perform inner join, left outerjoin, Rightouter join and Full outer join on R1 and R2

SQL> create table R1(C_id integer,Course char(20)); Table created.

SQL> create table R2(C_id integer,Faculty char(30)); Table created.
SQL> insert into R1 values(100,'DBMS'); 1 row
created.
SQL> insert into R1 values(101,'C#'); 1 row
created.
SQL> insert into R1 values(102,'CN'); 1 row
created.

SQL> insert into R2 values(100,'Murthy'); 1 row

created.
SQL> insert into R2 values(101,'Shilpa'); 1 row
created.
SQL> insert into R2 values(104,'Varun'); 1 row
created.

GOVERNMENT FIRST GRADE COLLEGE, BANGARPET

12
 DBMS LAB PROGRAMS -NEP

SQL> select * from R1;

C_ID COURSE
---------- --------------------
100 DBMS
101 C#
102 CN

SQL> select * from R2;

C_ID FACULTY
---------- ------------------------------
100 Murthy
101 Shilpa
104 Varun

SQL> SELECT R1.C_ID, R1.Course, R2.Faculty FROM R1

INNER JOIN R2
ON R1.C_id= R2.C_id;

C_ID COURSE FACULTY

---------- -------------------- ------------------------------
100 DBMS Murthy
101 C# Shilpa

SQL> SELECT R1.Course,R2.Faculty from R1

left JOIN R2 ON R1.C_id=
R2.C_id;

COURSE FACULTY
-------------------- ------------------------------ DBMS
Murthy
C# Shilpa
CN

SQL> SELECT R1.Course,R2.Faculty FROM R1

right JOIN R2 ON
R1.C_id= R2.C_id;

GOVERNMENT FIRST GRADE COLLEGE, BANGARPET

13
 DBMS LAB PROGRAMS -NEP

COURSE FACULTY
-------------------- ------------------------------
DBMS Murthy
C# Shilpa
CN Varun

SQL> SELECT R1.Course,R2.Faculty FROM R1

full JOIN R2 ON R1.C_id= R2.C_id;

COURSE FACULTY
------------ ----------
DBMS Murthy
C# Shilpa
Varun
CN

8.CREATE VIEWS FOR A PARTICULAR TABLE

• Create table customers with attribute id,name,age,address,salary

• Insert records into the table
• Create view customers_view and perform Update operation

SQL> select * from customers;

ID NAME AGE ADDRESS SALARY
---------- -------------------- ---------- ------------------------- ----------
1 Ramesh 32 Ahmedabad 500
2 varun 25 Delhi 1500
3 kaushik 23 bopal 2000
4 narasimha 25 Mumbai 6500

SQL> CREATE VIEW CUSTOMERS_VIEW AS

SELECT name, age FROM
CUSTOMERS;
View created.

GOVERNMENT FIRST GRADE COLLEGE, BANGARPET

14
 DBMS LAB PROGRAMS -NEP

SQL> SELECT * FROM CUSTOMERS_VIEW;

NAME AGE
-------------------- ----------
Ramesh 32 varun
kaushik 23
narasimha 25

SQL> UPDATE CUSTOMERS_VIEW

2 SET AGE = 35
3 WHERE name = 'Ramesh'; 1 row updated.

SQL> SELECT * FROM CUSTOMERS_VIEW;

NAME AGE
-------------------- ----------
Ramesh 35 varun
25 kaushik 23
narasimha 25

9.WRITE PL/SQL PROCEDURE FOR AN APPLICATION USING

EXCEPTION HANDLING.

SQL> CREATE TABLE CUSTOMERS(ID INT NOT NULL,NAME VARCHAR(20) NOT

NULL,AGE INT NOT NULL,ADDRESS CHAR (25) ,SALARY DECIMAL (18,
2),PRIMARY
KEY (ID));
Table created.

SQL> INSERT INTO CUSTOMERS VALUES (1, 'Ramesh', 32, 'Ahmedabad', 2000.00 ); 1
row created.
SQL> INSERT INTO CUSTOMERS VALUES(2, 'varun', 25, 'Delhi', 1500.00 );
1 row created.
SQL> INSERT INTO CUSTOMERS VALUES(3, 'kaushik', 23, 'bopal', 2000.00 );
1 row created.
SQL> INSERT INTO CUSTOMERS VALUES(4, 'narasimha', 25, 'Mumbai', 6500.00 ); 1
row created.
SQL> select * from customers;

GOVERNMENT FIRST GRADE COLLEGE, BANGARPET

15
DBMS LAB PROGRAMS -NEP

ID NAME AGE ADDRESS SALARY

---------- -------------------- ---------- ------------------------- ----------
1 Ramesh 32 Ahmedabad 2000
2 varun 25 Delhi 1500
3 kaushik 23 bopal 2000
4 narasimha 25 Mumbai 6500
SQL> Set serveroutput on
SQL> edit exceptiondemo
DECLARE c_id
customers.id%type := 8;
c_name customers.name%type;
c_addr customers.address%type;
BEGIN
SELECT name, address INTO c_name, c_addr
FROM customers
WHERE id = c_id;
DBMS_OUTPUT.PUT_LINE ('Name: '|| c_name);
DBMS_OUTPUT.PUT_LINE ('Address: ' || c_addr);
EXCEPTION
WHEN no_data_found THEN
dbms_output.put_line('No such customer!'); WHEN
others THEN dbms_output.put_line('Error!');
END;
/
SQL> @exceptiondemo No
such customer!
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

GOVERNMENT FIRST GRADE COLLEGE, BANGARPET

16
 DBMS LAB PROGRAMS -NEP

SQL> select *from student;

RNO NAME AGE GENDER

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

101 sasikala 40 female

102 aarthi 24 female

103 antim 21 female

104 yubaraj 21 male

102 aarthi 24 female

GOVERNMENT FIRST GRADE COLLEGE, BANGARPET

17
DBMS LAB PROGRAMS -NEP

103 antim 21 female

104 yubaraj 21 male

105 varun 23 male

PL/SQL procedure successfully completed.

11. WRITE A PL/SQL PROCEDURE FOR AN APPLICATION USING

FUNCTION

SQL> connect

Enter user-name: System

Enter password:

Connected.

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

Session altered.

GOVERNMENT FIRST GRADE COLLEGE, BANGARPET

18
DBMS LAB PROGRAMS -NEP

GOVERNMENT FIRST GRADE COLLEGE, BANGARPET

19
 DBMS LAB PROGRAMS -NEP

12. WRITE A PL/SQL PROCEDURE FOR AN APPLICATION USING

PACKAGE

SQL> select * from customer;

CUSTID CNAME ADRESS CONTACT SALARY

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

101 sasi chennai 9444430881 22000

102 yubarj bangalore 9443113836 21000

Package created.

SQL> CREATE OR REPLACE PACKAGE BODY cust_sal AS

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 /

GOVERNMENT FIRST GRADE COLLEGE, BANGARPET

20
DBMS LAB PROGRAMS -NEP

Package body created.

SQL> DECLARE
2 code customer.custid%type := &cc_custid;
3 BEGIN
4 cust_sal.find_sal(code);

5 END;
6 /
Enter value for cc_custid: 101 old 2: code

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

code customer.custid%type := 101;

Salary: 22000

PL/SQL procedure successfully completed.

GOVERNMENT FIRST GRADE COLLEGE, BANGARPET

21
DBMS LAB PROGRAMS -NEP

PART B
1. WRITE A SQL QUERY TO DISPLAY THE DATE&TIME DATA TYPES
AND TIMESTAMP FUNCTIONS

SQL> SELECT SYSDATE FROM DUAL;

SYSDATE
---------
09-FEB-23
SQL> SELECT TO_CHAR(CURRENT_DATE, 'DD-MM-YYYY HH:MI:SS')
FROM DUAL;

TO_CHAR(CURRENT_DAT
-------------------
09-02-2023 10:08:37

SQL> SELECT ADD_MONTHS(SYSDATE, 5) FROM DUAL;

ADD_MONTH
---------
09-JUL-23

SQL> SELECT LOCALTIMESTAMP FROM DUAL;

LOCALTIMESTAMP
---------------------------------------------------------------------------
09-FEB-23 10.10.10.356000 AM

GOVERNMENT FIRST GRADE COLLEGE, BANGARPET

22
DBMS LAB PROGRAMS -NEP

2. WRITE A SQL CODE IMPLEMENTATION WITH AGGREGATION

FUNCTION

SQL> select *from customer;

CUSTID CNAME ADRESS CONTACT SALARY

---------- ---------- ---------- ---------- ----------
101 sasi chennai 9444430881 22000
102 yubarj bangalore 9443113836 21000
103 ramesh delhi 9551724212 45000
104 rajesh mumbai 9441724212 55000
105 suresh chennai 7446113222 10000
106 dhanesh chennai 7446113222 11000

6 rows selected.

SQL> insert into customer values (107,'aarthi','nagercoil',7885212582,null);

1 row created.

SQL> select * from customer;

CUSTID CNAME ADRESS CONTACT SALARY

---------- ---------- ---------- ---------- ----------
101 sasi chennai 9444430881 22000
102 yubarj bangalore 9443113836 21000
103 ramesh delhi 9551724212 45000
104 rajesh mumbai 9441724212 55000
105 suresh chennai 7446113222 10000
106 dhanesh chennai 7446113222 11000
107 aarthi nagercoil 7885212582

7 rows selected.

SQL> select count(*) from customer;

COUNT(*)
----------
7

GOVERNMENT FIRST GRADE COLLEGE, BANGARPET

23
DBMS LAB PROGRAMS -NEP

SQL> select count(distinct salary) from customer;

COUNT(DISTINCTSALARY)
---------------------
6

SQL> select count(salary) from customer;

COUNT(SALARY)
-------------
6

SQL> select sum(salary) from customer;

SUM(SALARY)
-----------
164000

SQL> select avg(salary) from customer;

AVG(SALARY)
-----------
27333.3333

SQL> select min(salary) from customer;

MIN(SALARY)
-----------
10000 SQL>
select
max(salary)
from
customer;

MAX(SALARY)
-----------
55000

GOVERNMENT FIRST GRADE COLLEGE, BANGARPET

24
DBMS LAB PROGRAMS -NEP

3. WRITE A QUERY TO SORT THE RECORDS IN THE ASCENDING

/DESCENDING ORDER OF THE CUSTOMER NAMES STORED IN THE
CUSTOMERS TABLE.

SQL> select *from customer;

CUSTID CNAME ADDRESS CONTACT SALARY

---------- ---------- ---------- ---------- ----------
101 sasi chennai 9444430881 22000
102 yubarj bangalore 9443113836 21000
103 ramesh delhi 9551724212 45000
104 rajesh mumbai 9441724212 55000
105 suresh chennai 7446113222 10000
106 dhanesh chennai 7446113222 11000
107 aarthi nagercoil 7885212582 22000

7 rows selected.

SQL> select * from customer ORDER BY cname ASC;

CUSTID CNAME ADDRESS CONTACT SALARY

---------- ---------- ---------- ---------- ----------
107 aarthi nagercoil 7885212582 22000
106 dhanesh chennai 7446113222 11000
104 rajesh mumbai 9441724212 55000
103 ramesh delhi 9551724212 45000
101 sasi chennai 9444430881 22000
105 suresh chennai 7446113222 10000
102 yubarj bangalore 9443113836 21000

7 rows selected.

SQL> select * from customer ORDER BY address;

CUSTID CNAME ADDRESS CONTACT SALARY

---------- ---------- ---------- ---------- ----------
102 yubarj bangalore 9443113836 21000
101 sasi chennai 9444430881 22000
106 dhanesh chennai 7446113222 11000

GOVERNMENT FIRST GRADE COLLEGE, BANGARPET

25
DBMS LAB PROGRAMS -NEP

105 suresh chennai 7446113222 10000

103 ramesh delhi 9551724212 45000
104 rajesh mumbai 9441724212 55000
107 aarthi nagercoil 7885212582 22000

7 rows selected.

SQL> select * from customer ORDER BY cname DESC;

CUSTID CNAME ADDRESS CONTACT SALARY

---------- ---------- ---------- ---------- ----------
102 yubarj bangalore 9443113836 21000
105 suresh chennai 7446113222 10000
101 sasi chennai 9444430881 22000
103 ramesh delhi 9551724212 45000
104 rajesh mumbai 9441724212 55000
106 dhanesh chennai 7446113222 11000
107 aarthi nagercoil 7885212582 22000

7 rows selected.

4. WRITE A SQL QUERY USING AND,OR,NOT OPERATOR

SQL> select custid,cname from customer;

CUSTID CNAME
---------- ----------
101 sasi
102 yubarj
103 ramesh
104 rajesh
105 suresh
106 dhanesh
107 aarthi

7 rows selected.

GOVERNMENT FIRST GRADE COLLEGE, BANGARPET

26
DBMS LAB PROGRAMS -NEP

SQL> select *from customer where adress='chennai' and cname='dhanesh';

CUSTID CNAME ADRESS CONTACT SALARY

---------- ---------- ---------- ---------- ----------
106 dhanesh chennai 7446113222 11000

SQL> select *from customer where adress='chennai' or cname='dhanesh';

CUSTID CNAME ADRESS CONTACT SALARY

---------- ---------- ---------- ---------- ----------
101 sasi chennai 9444430881 22000
105 suresh chennai 7446113222 10000
106 dhanesh chennai 7446113222 11000

SQL> select * from customer where not adress='chennai';

CUSTID CNAME ADRESS CONTACT SALARY

---------- ---------- ---------- ---------- ----------
102 yubarj bangalore 9443113836 21000
103 ramesh delhi 9551724212 45000
104 rajesh mumbai 9441724212 55000
107 aarthi nagercoil 7885212582 22000

GOVERNMENT FIRST GRADE COLLEGE, BANGARPET

27
DBMS LAB PROGRAMS -NEP

5. WRITE A SQL QUERY USING LIKE OPERATOR:

SQL> select * from customer where cname like 'a%';

CUSTID CNAME ADRESS CONTACT SALARY

---------- ---------- ---------- ---------- ----------
107 aarthi nagercoil 7885212582 22000

SQL> select * from customer where cname like '%a';

no rows selected

SQL> select * from customer where cname like '%h';

CUSTID CNAME ADRESS CONTACT SALARY

---------- ---------- ---------- ---------- ----------
103 ramesh delhi 9551724212 45000
104 rajesh mumbai 9441724212 55000
105 suresh chennai 7446113222 10000
106 dhanesh chennai 7446113222 11000

SQL> select *from customer where cname like '%or%';

no rows selected

SQL> select *from customer where adress like '%nn%';

CUSTID CNAME ADRESS CONTACT SALARY

---------- ---------- ---------- ---------- ----------
101 sasi chennai 9444430881 22000
105 suresh chennai 7446113222 10000
106 anesh chennai 7446113222 11000

GOVERNMENT FIRST GRADE COLLEGE, BANGARPET

28
DBMS LAB PROGRAMS -NEP

6. WRITE A PL/SQL FUNCTION TO CALCULATE THE

FACTORIAL OF A GIVEN NUMBER

7.WRITE PL/SQL PROCEDURE FOR GIVEN YEAR IS LEAP YEAR

OR NOT.

GOVERNMENT FIRST GRADE COLLEGE, BANGARPET

29
DBMS LAB PROGRAMS -NEP

8.WRITE PL/SQL PROCEDURE FOR GIVEN NUMBER IS PRIME

OR NOT.

GOVERNMENT FIRST GRADE COLLEGE, BANGARPET

30