SESSION: 2023-2024

V.B.S PURVANCHAL UNIVERSITY JAUNPUR

DEPARTMENT OF INFORMATION TECHNOLOGY

D.B.M.S LAB-FILE

Submitted To: Submitted By:

Mr. Dileep Kumar Yadav Vikrant Singh

(Assistant Professor) B.Tech 3rd Year 5th Sem

Mr. Krishna Kumar Yadav Branch – I.T

(Assistant Professor) Roll No. 22001304093

(Dept. C.S.E & IT)

Index
Sr.No. Name of Program Date Remark Sign

1. Write the queries for data Definition

Language.

2. Write the queries for data Manipulation

Language.

3. Write SQL queries using logical operations

(=, <, >).

4. Write SQL queries using SQL operators.

5. Write SQL queries for relational algebra.

6. Write SQL queries for extracting data from

more than one table.

7. To implement the concept of indexes and

views.

8. To implement the concept of joins.

9. To implement the basics of PL/SQL.

10. To implement the concepts for

ROLLBACK, COMMIT and CHECK
POINTS.
PROGRAM NO: 1

AIM: - WRITE THE QUERIES FOR DATA DEFINITION LANGUAGE

DDL COMMANDS:
In this section, We will cover the following DDL commands as
fallow.

1. Create

2. Alter

3. Truncate

4. Drop

5. Rename

Create:
This command is used to creation a table or databases in SQL.

Alter:
This command is used to add, delete & change column in the existing table.

Truncate:
This command is used to remove all rows from the table, but the truncate of the
table still exists.

Drop:
This command is used to remove an existing table along with its structure from the
database.

Rename:
It is possible to change name of table with or without data in it using simple
rename command.

Query:
➢ Create database comp;
✓ Query ok, 1 row affected.

➢ Show databases;

➢ Use comp;

✓ Database changed

➢ Create table emp(emp_id int(5),Name varchar(20),Contact_No varchar(10),

e_id varchar(50) );

✓ Query ok , rows affected

➢ Desc emp;
➢ Alter table emp add comp_address varchar(50);

➢ Truncate table emp;

✓ Table records delete

➢ Rename table emp to employee;

✓ Query ok, row affected

➢ Drop table employee;

✓ Delete the employee table

PROGRAM NO: 2

AIM: - Write the queries for data Manipulation Language

DML COMMANDS:
The DML commands in structured query language change the data
present in the SQL database. We can easily access, store, modify, update and delete the
existing records from the databases using DML commands.

1. Select

2. Insert

3. Update

4. Delete

Insert:
Insert command allows users to insert data in database tables.

Select:
Select the data in table to fetching the records.

Update:
Update query is used to update a record in table.

Delete:
Delete query is used to delete a row in a table.

Query:
➢ Insert into empl(1,”Ram”,”98676545”,”jaunpur”,”tcs”,5000);
➢ Select * from empl;

➢ Update empl set cmp_name=”apple” where e_id=1;

➢ Delete from empl where e_id=4;

➢ Select * from empl where e_id=4;

✓ Empty set
PROGRAM NO: 3

AIM:- Write SQL queries using logical operations (=, <, >)

Logical operation to use a particular fetching the records in table with the table the help of
some operation.

Already table is made and use this table->

Query:
➢ Select * from empl where e_id=2;

➢ Select * from empl where salary>5000;

➢ Select * from empl where salary<10000;

PROGRAM NO:4

AIM:- Write SQL queries using SQL operators.

These are some operators in SQL ……

1. SQL Arithmetic Operators

2. SQL Bitwise Operators

3. SQL Comparison Operators

4. SQL Compound Operators

5. SQL Logical Operators

Query:
➢ Select ProductName from Products where Productid = ALL (Select Productid from
OrderDetails where Quantity = 10);

➢ Select * from Customers where City = "London" AND Country = "UK";

➢ Select * from customers where city not like 's%';

➢ Select * from products where price between 50 and 60;

➢ Select * from products where price > some (Select price from products where price >
20);
➢ Select 30+20;

✓ 50

➢ Select 17%5;

✓ 2

➢ Select 60-40;

✓ 20

➢ Select 20/2;

✓ 10

➢ Select * from products where price <> 18;

➢ Select * from customers where city = "London" OR country = "UK";

PROGRAM NO: 5

AIM: - Write SQL queries for relational algebra

Relational Algebra:
Relational algebra is a procedural query language. It gives a step-by-step process to obtain
the result of the query. It uses operators to perform queries.

Types of Relational operation:

Where:

σ is used for selection prediction

r is used for relation

p is used as a propositional logic formula which may use connectors like: AND OR and
NOT. These relational can use as relational operators like =, ≠, ≥, <, >, ≤.
➢ σ BRANCH_NAME="perryride" (LOAN)

➢ ∏ NAME, CITY (CUSTOMER)

➢ ∏ CUSTOMER_NAME (BORROW) ∪ ∏ CUSTOMER_NAME (DEPOSITOR)

PRACTICAL:06

Aim: -Write SQL queries for extracting data from more than one
table.
Description: -SELECT statement is used to retrieve fields from multiple
tables. To do so, we need to use join query to get data from multiple tables.

Query: -
SELECT vbspu3.GID, vbspu3.PID, vbspu3.Asset, vbspu1.FirstName,

Vbspu2.LastName

FROM vbspu3

LEFT JOIN vbspu1

ON vbspu3.GID=vbspu1.ID

LEFT JOIN vbspu2

ON vbspu3.GID=vbspu.ID

OUTPUT: --
PRACTICAL: -07

Aim: - To implement the concept of indexes and views.

Description: -Indexes are used to retrieve data from the database more quickly than
otherwise. The users cannot see the indexes, they are just used to speed up searches/queries.

In SQL, a view is a virtual table based on the result-set of an SQL statement.

A view contains rows and columns, just like a real table. The fields in a view are fields from
one or more real tables in the database.

Query: -

CREATE VIEW MarksView AS

SELECT StudentDetails.NAME, StudentDetails.ADDRESS, StudentMarks.MARKS
FROM StudentDetails, StudentMarks
WHERE StudentDetails.NAME = StudentMarks.NAME;

OUTPUT: -

Name Address Marks

Harsh Kolkata 90

Pratik Delhi 80

Dhanraj Bihar 95

Ram Rajasthan 85

CREATE INDEX Index

ON StudentDetails(Name,Address,Marks);

OUTPUT: -
Index Name Address Marks

1 Harsh Kolkata 90

2 Pratik Delhi 80

3 Dhanraj Bihar 95

4 Ram Rajasthan 85
PRACTICAL-08

Aim: - To implement the concept of joins.

Description: - A JOIN clause is used to combine rows from two or more tables,
based on a related column between them.

Query: -
SELECT Customers.customer_id, Customers.first_name, Orders.item

FROM Customers

JOIN Orders

ON Customers.customer_id = Orders.customer_id;

OUTPUT: -
PRACTICAL-09

Aim: - To implement the basics of PL/SQL.

Description: -PL/SQL is a block of codes that is used to write the entire

program blocks/ procedure/ function, etc. It is declarative, that defines what
needs to be done, rather than how things need to be done.

Query: -
DECLARE

-- Variable declaration

msg varchar(30):= 'Software Test';

BEGIN

/*

* PL/SQL executable output

*/

dbms_output.put_line(msg);

END;

OUTPUT: -
PRACTICAL-10

Aim: - To implement the concepts for ROLLBACK, COMMIT and CHECK

POINTS.

Description: -If any error occurs with any of the SQL grouped statements, all
changes need to be aborted. The process of reversing changes is
called rollback.

If everything is in order with all statements within a single transaction, all

changes are recorded together in the database is called committed.

A SAVEPOINT is a point in a transaction in which you can roll the transaction

back to a certain point without rolling back the entire transaction.

Ouery:-
DELETE FROM Student WHERE AGE = 20;
COMMIT;

OUTPUT: -

DELETE FROM Student WHERE AGE = 20;

ROLLBACK;

OUTPUT:-
SAVEPOINT SP1;
//Savepoint created.
DELETE FROM Student WHERE AGE = 20;
//deleted
SAVEPOINT SP2;
//Savepoint created.

OUTPUT:-