1|Page DATABASE MANAGEMENT SYSTEM 16103092

LAB-1
# INTRODUCTION TO DBMS
A database management system (DBMS) refers to the technology for creating and managing databases.
Basically, DBMS is a software tool to organize (create, retrieve, update and manage) data in a database.
We use queries for restriction/constraints.

ADVANTAGES OF DBMS OVER FILE SYSTEM

✓ Increased security
✓ Concurrency (multiple users at same time)
✓ Data sharing
✓ Data consistency
✓ Data integrity
✓ Lesser redundancy

BASIC OPERATIONS PERFORMED ON DATA

✓ Insert
✓ Delete
✓ Retrieve(select)
✓ Update(modify)

VARIOUS LANGUAGES
❖ DDL (Data Definition language)
➢ Create table
➢ Create view
➢ Create trigger
❖ DML (Data Manipulation Language)
➢ Insert
➢ Update
➢ Add
➢ Modify
➢ Delete
➢ Truncate
➢ Select

VARIOUS OPERATORS
❖ Arithmetic Operators
➢ +,-,/,%,*
❖ Comparison Operators
➢ =, < , > , <= , >= , IN , BETWEEN , NOTIN , ALL , ANY , EXISTS.
❖ Logical Operators
➢ AND , OR , NOT
❖ Set Operators
➢ UNION, INTERSECT, UNION ALL, MINUS
❖ Aggregate Operators
➢ Avg, sum, min, max, count.
2|Page DATABASE MANAGEMENT SYSTEM 16103092
SIMPLE SQL QUERY

#SELECT
1. Select * from all_tables
->display all existing tables
2. Select * from emp
->displays the table emp
3. Select empname from emp
->displays the name of all employees

Query 1:

Result:
3|Page DATABASE MANAGEMENT SYSTEM 16103092

Query 2:

Query 3:
4|Page DATABASE MANAGEMENT SYSTEM 16103092

LAB-2
AIM: To practice DDL and DML commands on database.

Various Queries:

1. Create table
Syntax: create table<table name> (table attributes and their data types)

2. Alter
• It has 3 operations
o ADD
▪ SYNTAX:
Alter table <table name>
Add (attribute and data type)

o MODIFY
▪ SYNTAX:
Alter table <table name>
Modify table attribute and datatype
5|Page DATABASE MANAGEMENT SYSTEM 16103092
o DROP
▪ SYNTAX:
Alter table <table name>
Drop column <name>

3. RENAME
SYNTAX: rename <old name> to <new name>

4. DESCRIBE
SYNTAX: desc <table name>

5. INSERT
SYNTAX: insert into <table name> (attribute and datatypes)
Values (values to be filled in )
6|Page DATABASE MANAGEMENT SYSTEM 16103092

6. UPDATE
SYNTAX: update <table name>
Set condition

7. DELETE
->Used to delete rows
SYNTAX: delete from<table name>
Condition

8. TRUNCATE
->To delete permanently
SYNTAX: truncate table <tale name>
7|Page DATABASE MANAGEMENT SYSTEM 16103092

LAB-3
AIM: To practice constraints using queries

Theory:

1. NOT NULL
Null means missing unknown values or inapplicable.
Query:

2. UNIQUE
It is used for non-repeating values.
Query:

3. CHECK
It specifies a requirement that must be met by all rows.
Query:
8|Page DATABASE MANAGEMENT SYSTEM 16103092

4. Primary key
It is a combination of not null and unique constraints.
Query:

5. Foreign key
Primary key of one table acts as the foreign key for other table.
Query:
9|Page DATABASE MANAGEMENT SYSTEM 16103092

LAB-4
Aim: To create table Student having columns stu_id, sname, class, dob, phone no.
Use following DDL statements on it.
a. Set the stu_id as primary key
b. Input records to it
c. Delete any record
d. Update the phone no. Of any student
e. Set the not null constraint for Dob column

Solution:
a.

b.

c.
10 | P a g e DATABASE MANAGEMENT SYSTEM 16103092

d.

e.
11 | P a g e DATABASE MANAGEMENT SYSTEM 16103092

LAB-5
# VARIOUS OPERATORS IN SQL

1. SQL Arithmetic Operators

Assume 'variable a' holds 10 and 'variable b' holds 20, then −

Show Examples

Operator Description Example

+ (Addition) Adds values on either side of the operator. a + b will give 30

- (Subtraction) Subtracts right hand operand from left hand operand. a - b will give -10

* Multiplies values on either side of the operator. a * b will give

(Multiplication) 200

/ (Division) Divides left hand operand by right hand operand. b / a will give 2

Divides left hand operand by right hand operand and returns

% (Modulus) b % a will give 0
remainder.

2. SQL Comparison Operators

Assume 'variable a' holds 10 and 'variable b' holds 20, then −

Show Examples

Operator Description Example

Checks if the values of two operands are equal or not, if yes then condition (a = b) is not
=
becomes true. true.

Checks if the values of two operands are equal or not, if values are not equal
!= (a != b) is true.
then condition becomes true.

Checks if the values of two operands are equal or not, if values are not equal (a <> b) is
<>
then condition becomes true. true.
12 | P a g e DATABASE MANAGEMENT SYSTEM 16103092

Checks if the value of left operand is greater than the value of right operand, (a > b) is not
>
if yes then condition becomes true. true.

Checks if the value of left operand is less than the value of right operand, if
< (a < b) is true.
yes then condition becomes true.

Checks if the value of left operand is greater than or equal to the value of (a >= b) is not
>=
right operand, if yes then condition becomes true. true.

Checks if the value of left operand is less than or equal to the value of right (a <= b) is
<=
operand, if yes then condition becomes true. true.

Checks if the value of left operand is not less than the value of right operand, (a !< b) is
!<
if yes then condition becomes true. false.

Checks if the value of left operand is not greater than the value of right
!> (a !> b) is true.
operand, if yes then condition becomes true.

3. SQL Logical Operators

Here is a list of all the logical operators available in SQL.

Show Examples

Sr.No. Operator & Description

ALL
1
The ALL operator is used to compare a value to all values in another value set.

AND
2 The AND operator allows the existence of multiple conditions in an SQL statement's WHERE
clause.

ANY
3 The ANY operator is used to compare a value to any applicable value in the list as per the
condition.

BETWEEN
4
The BETWEEN operator is used to search for values that are within a set of values, given the
13 | P a g e DATABASE MANAGEMENT SYSTEM 16103092

minimum value and the maximum value.

EXISTS
5 The EXISTS operator is used to search for the presence of a row in a specified table that meets a
certain criterion.

IN
6
The IN operator is used to compare a value to a list of literal values that have been specified.

LIKE
7
The LIKE operator is used to compare a value to similar values using wildcard operators.

NOT
8 The NOT operator reverses the meaning of the logical operator with which it is used. Eg: NOT
EXISTS, NOT BETWEEN, NOT IN, etc. This is a negate operator.

OR
9
The OR operator is used to combine multiple conditions in an SQL statement's WHERE clause.

IS NULL
10
The NULL operator is used to compare a value with a NULL value.

UNIQUE
11
The UNIQUE operator searches every row of a specified table for uniqueness (no duplicates).
14 | P a g e DATABASE MANAGEMENT SYSTEM 16103092

LAB-6
AIM: To create table student with attributes student id, name and address and perform following queries
on it.

1. Insert 5 rows
2. Create table student1 with same structure as atudent without explicitly mentioning column
names(include data also)
3. Same as 2 but does not contain data
4. Show structure of student
5. Rename name to student name
6. Change data type of id
7. Change it back to it’s original form
8. Add phone no, city as attribute
9. Drop column city
10. Truncate student1

Queries:
15 | P a g e DATABASE MANAGEMENT SYSTEM 16103092
1.

2.

3.
16 | P a g e DATABASE MANAGEMENT SYSTEM 16103092

4.

Describing student

Describing student1
17 | P a g e DATABASE MANAGEMENT SYSTEM 16103092
Table :

5.

6.
18 | P a g e DATABASE MANAGEMENT SYSTEM 16103092
7.

8.

9.

10.
19 | P a g e DATABASE MANAGEMENT SYSTEM 16103092

LAB-7
Aim: To learn and practice aggregate functions.

Theory:

# aggregate functions

1. Count

->Total number of rows

2. Sum

->calculates the sum and ignores the null values

3. avg

->calculates the average and ignores the null values

4. minimum/maximum

->displays the minimum or maximum value

5. variance

->calculates variance of given column

6. group by

7. having

Practice question

1. List ename of employee where salary is greater than Rs. 2000

2. List ename, job, salary of emp where department number is 10 and salary greater than 2000
3. List all columns of emp table where dept no. is other than 10
4. List all employee whose ename starts with ‘a’ and ends with ‘n’
5. List all employee where job post ‘clerk’ or ‘manager’
6. List all employee where salary lies between 1250 and 3000
7. List all employee who is getting a commission
8. List all employee according to the date of joining
9. List all last 3 character of ename of each employee
10. List hire dates of each employee in format ”dd/mm/yyyy”
11. List maximum, minimum and average of salary
12. List sum of sal and comm. Of each employee
13. Count number of job titles in emp table
14. Calculate total number of employee in each department except 30
15. Find all dept number where total no. of employee greater than 2
16. List employee who are not clerk
17. List all employee who do not have manager
18. List all employee who are not working in any department
20 | P a g e DATABASE MANAGEMENT SYSTEM 16103092

THE EMPLOYEE TABLE:

1.
21 | P a g e DATABASE MANAGEMENT SYSTEM 16103092
2.

3.

4.
22 | P a g e DATABASE MANAGEMENT SYSTEM 16103092
5.

6.

7.
23 | P a g e DATABASE MANAGEMENT SYSTEM 16103092
8.

9.
24 | P a g e DATABASE MANAGEMENT SYSTEM 16103092
10.

11.

12.
25 | P a g e DATABASE MANAGEMENT SYSTEM 16103092
13.

14.

15.

16.
26 | P a g e DATABASE MANAGEMENT SYSTEM 16103092
17.

18.
27 | P a g e DATABASE MANAGEMENT SYSTEM 16103092

LAB-8
DATA CONTROL LANGUAGE
It is used to provide authorization

1. Grant - is to give access

2. Revoke - is to take back access

Example queries:
1. GRANT select, update, insert, delete ON emp to user1
2. GRANT all on emp to user
3. GRANT select on EMP to public
4. REVOKE delete on emp from user
5. REVOKE all on emp from user

Sub queries:

Ques:
1. Retrieve details of emp where salary is greater than salary of emp with eID=103
2. Display details of emp who work in paris
3. Retrieve details of emp working in deptno. 10 whose salary is greater than the min salary of
employees working in dept no. 30
4. Retrieve details of the emp who wok in dept no. 10 and who earn atleast as much as any
employee working in dept no. 30
5. Details of employee who work in paris

Solutions:
Employee table:

The department table:

28 | P a g e DATABASE MANAGEMENT SYSTEM 16103092

1.

2.

3.
29 | P a g e DATABASE MANAGEMENT SYSTEM 16103092

4.

5.
30 | P a g e DATABASE MANAGEMENT SYSTEM 16103092
Ques:

Create a table with attributes – name , order_no and price_no and insert some tuples into it.
CREATE TABLE XYZ(
name varchar(20),order_number number(6),price number(7)
);
INSERT INTO XYZ
VALUES('ram',14562,20000);
INSERT INTO XYZ
VALUES('sonu',25330,20013);
INSERT INTO XYZ
VALUES('bunny',12542,60021);

1. Display the total number of orders by bunny

SELECT COUNT(ORDER_NUMBER) FROM XYZ WHERE name='bunny';

2. Display the minimum price in the table

SELECT MIN(PRICE) FROM XYZ;
31 | P a g e DATABASE MANAGEMENT SYSTEM 16103092

LAB -9
Aim: Practice harder problems

Ques:

1. Find names and id of dept where least salary is greater than the highest salary in dept 10
2. Find all employee who have their manager and dept matching with emp having emp id 121 or 200
3. Find 3rd maximum salary from employee table
4. Select * from emp where
Sal<all<(select salary from employee where dept=10)
Find equivalent query by using function inside 1st query
Solutions:

1.

2.
32 | P a g e DATABASE MANAGEMENT SYSTEM 16103092

3.

4.
33 | P a g e DATABASE MANAGEMENT SYSTEM 16103092

LAB -10
AIM- To implement join operations.

Theory:
Joins are basically a cartesian product applied on two tables with a same attribute to list tuples which
correspond to same values in two tables.

Two tables named – XYZ and ABC are created to apply various join operations .
34 | P a g e DATABASE MANAGEMENT SYSTEM 16103092

1. Apply inner join

select * from abc inner join xyz on abc.firstname='anu';

2. Apply outer join

Select * from xyz outer join abc on abc.id=12;

3. Apply left outer join

Select * from xyz left outer join abc on abc.id=12;
35 | P a g e DATABASE MANAGEMENT SYSTEM 16103092

LAB-11
AIM: To implement index and view

View is a means to give a new name to table returned from a query

Example
Create a view table vtable of employee having only employee name, number and salary

CREATE VIEW vtable AS SELECT empno,ename,sal FROM emp;

SELECT * FROM vtable;

INDEXING :
Theory:

Indexing is a data structure technique to efficiently retrieve records from the database files based on
some attributes on which the indexing has been done. Indexing in database systems is similar to what
we see in books.

Indexing is defined based on its indexing attributes. Indexing can be of the following types −

• Primary Index − Primary index is defined on an ordered data file. The data file is ordered on
a key field. The key field is generally the primary key of the relation.
36 | P a g e DATABASE MANAGEMENT SYSTEM 16103092

• Secondary Index − Secondary index may be generated from a field which is a candidate key
and has a unique value in every record, or a non-key with duplicate values.

• Clustering Index − Clustering index is defined on an ordered data file. The data file is ordered
on a non-key field.

Ordered Indexing is of two types −

• Dense Index
• Sparse Index

• Dense Index
In dense index, there is an index record for every search key value in the database. This makes searching
faster but requires more space to store index records itself. Index records contain search key value and a
pointer to the actual record on the disk.

• Sparse Index
In sparse index, index records are not created for every search key. An index record here contains a
search key and an actual pointer to the data on the disk. To search a record, we first proceed by index
record and reach at the actual location of the data. If the data we are looking for is not where we directly
reach by following the index, then the system starts sequential search until the desired data is found.
37 | P a g e DATABASE MANAGEMENT SYSTEM 16103092