Scribd
Upload
100 views4 pages

DBMS Lab File: CS502 Exercises

This document is a lab file for a database management systems course. It contains 11 problems related to SQL commands for creating tables, inserting and deleting data, updating records, using queries with filters and sorting. Sample schemas and data are provided for problems 5-11 involving the "dept" and "emp" tables, with emp having at least 20 records across the 4 dept categories. The file is submitted by a student for the course.

Uploaded by

latina
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
100 views4 pages

DBMS Lab File: CS502 Exercises

This document is a lab file for a database management systems course. It contains 11 problems related to SQL commands for creating tables, inserting and deleting data, updating records, using queries with filters and sorting. Sample schemas and data are provided for problems 5-11 involving the "dept" and "emp" tables, with emp having at least 20 records across the 4 dept categories. The file is submitted by a student for the course.

Uploaded by

latina
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
You are on page 1/ 4

RUSTAMJI INSTITUTE OF TECHNOLOGY

BSF ACADEMY, TEKANPUR

Lab File for


CS502 (Database Management System)

Submitted by
Name of Student (Enrolment No.)
B.Tech. Computer Science & Engineering 5th Semester
(2017-2021 batch)

Subject Teacher File Checked by


Dr. Jagdish Makhijani Mr. Yashwant Pathak
TABLE OF CONTENTS
S. No. Practical Description Page Nos.
1 Introduction to DDL Commands with example
2 Introduction to DDL Commands with example
3 Use of where, like, in, between, not in select command with
example
4 Use of select subquery in SELECT, INSERT, DELETE
command
5 Write Commands for:
i. Create table given in sample schema (next page)
ii. Insert data as given on next page.
iii. Delete duplicate row from the table.
iv. Display the alternate row from table.
v. Delete alternate row from table.
vi. Update multiple rows in using single update
statement.
vii. Find the third highest paid and third lowest paid
salary.
viii. Display the 3rd, 4th, 9th rows from table.
ix. Display the ename, which is start with j, k, l or m.
x. Show all employees who were hired the first half of
the month.
xi. Display the three record in the first row and two
records in the second row and one record in the third
row in a single sql statements.
6 Write a pl/sql for select, insert, update and delete
statements.
7 Write a pl/sql block to delete a record. If delete operation is
successful return 1 else return 0.
8 Display name, hire date of all employees using cursors.
9 Display details of first 5 highly paid employees using cursors.
10 Write a database trigger which fires if you try to insert,
update, or delete after 7’o’ clock.
11 Write a data base trigger, which acts just like primary key
and does not allow duplicate values.
Sample Schema for above problems (Q. No. 5 to Q. No. 11):
Table Name: dept
deptno number(2,0),
dname varchar2(14),
loc varchar2(13),
constraint pk_dept primary key (deptno)

Table Name: emp


empno number(4,0),
ename varchar2(10),
job varchar2(9),
mgr number(4,0),
hiredate date,
sal number(7,2),
comm number(7,2),
deptno number(2,0),
constraint pk_emp primary key (empno),
constraint fk_deptno foreign key (deptno) references dept (deptno)

Sample Date for above problems (Q. No. 5 to Q. No. 11) (only for dept table):
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON

Insert atleast 20 records in emp table with atleast 3 records in each department.
Practical No.: 1
Problem Description:

Solution:

Output:

File Submitted by: Name of student (Enrolment No.)


1

You might also like