DR.K.N.

MODI INSTITUTE OF
ENGINEERING & TECHNOLOGY
N.H-58, MODINAGAR,GHAZIABAD, UTTAR PRADESH,201204
Affiliated to Dr. APJ Abdul Kalam Technical University, Lucknow

DATABASE MANAGEMENT SYSTEMS LAB

(BCS 551)
Department of Computer Science and Engineering/IT
Session: 2024-25

SUBMMITED TO: SUBMITTED BY:

Dr. Neha Gupta NAME: NEELKARAN BIND
COURSE: B.TECH [I.T.]
(Associate Professor)
ROLL-NO.: 2200770130008
INDEX

Expt. Title of experiment

DATE REMARK
No. DATE
Write the queries for Data Manipulation and Data
1.
Definition Language.
Write SQL queries using logical operations and operators.
2.

Write SQL query using group by function.

3.
Write SQL queries for group functions.
4.
Write SQL queries for sub queries, nested queries.
5.
Write programme by the use of PL/SQL.
6.
Write SQL queries to create views.
7.
Write an SQL query to implement JOINS.
8.
Write a query for extracting data from more than one table.
9.
Write a query to understand the concepts for ROLL BACK,
10.
COMMIT & CHECK POINTS.
INTRODUCTION

A database is an organized collection of data, generally stored and accessed electronically from a
computer system. Where databases are more complex they are often developed using formal design
and modelling techniques.

The database management system (DBMS) is the software that interacts with end users, applications,
and the database itself to capture and analyse the data. The DBMS software additionally encompasses
the core facilities provided to administer the database. The sum total of the database, the DBMS and the
associated applications can be referred to as a "database system". Often the term "database" is also used
to loosely refer to any of the DBMS, the database system or an application associated with the database.

Computer scientists may classify database-management systems according to the database models that
they support. Relational databases became dominant in the 1980s. These model data
as rows and columns in a series of tables, and the vast majority use SQL for writing and querying data.
In the 2000s, non-relational databases became popular, referred to as NoSQL because they use
different query languages.

A database has broad searching functionality. For example, a sales department could quickly search
for and find all sales personnel who had achieved a certain amount of sales over a particular time period.

A database can update records in bulk – even millions or more records. This would be useful, for
example, if you wanted to add new columns or apply a data patch of some sort.

If the database is relational, which most databases are, it can cross-reference records in different tables.
This means that you can create relationships between tables. For instance, if you linked a Customers
table with an Orders table, you could find all purchase orders from the Orders table that a single customer
from the Customers table ever processed, or further refine it to return only those orders processed in a
particular time period – or almost any type of combination you could imagine.

A database can perform complex aggregate calculations across multiple tables. For example, you could
list expenses across multiple retail outlets, including all possible sub-totals, and then a final total.

A database can enforce consistency and data integrity, which means that it can avoid duplication and
ensure data accuracy through its design and a series of constraints.

Structure Query Language(SQL) is a database query language used for storing and managing data in
Relational DBMS. SQL was the first commercial language introduced for E.F Codd's Relational model
of database. Today almost all RDBMS(MySql, Oracle, Infomix, Sybase, MS Access) use SQL as the
standard database query language. SQL is used to perform all types of data operations in RDBMS.On
the surface, a database might seem much like a spread sheet; it has data arranged in columns and rows.
But that is where the similarity ends because a database is far more powerful.
Experiment 1

AIM: Write the queries for Data Manipulation and Data definition Language.

Description :-

DML: A data manipulation language (DML) is a family of syntax elements similar to a computer
programming language used for selecting, inserting, deleting and updating data in a database. Performing
read-only queries of data is sometimes also considered a component of DML.

Commands in DML are:

a. INSERT
b. UPDATE
c. DELETE
d. SELECT

DML COMMANDS:

SYNTAX:

INSERT Statement:

Single Row into a Table: INSERT INTO table – name [column- identifier-comma-list)] VALUES (column-
valuecomma-list);

Multiple Row into a Table: insert into <table name> values (&col1, &col2, ….);

UPDATE Statement: UPDATE table-name SET update- column-list [WHERE search-condition];

DELETE Statement: DELETE FROM table-name [WHERE search- condition];

DDL: A data definition language or data description language (DDL) is syntax similar to a computer
programming language for defining data structures, especially database schemas.-
Commands in DDL are:

a. CREATE
b. DROP
c. TRUNCATE
d. RENAME
e. ALTER

DDL COMMANDS:

SYNTAX:

CREATE Statement: Create table tablename (column_name1 data_ type constraints,

column_name2 data_ type constraints);

DROP:DROP TABLE table_name;

TRUNCATE: TRUNCATE TABLE table_name;

RENAME: RENAME TABLE {tbl_name} TO {new_tbl_name};

ALTER:

Add column to Table: ALTER TABLE table_name ADD column_name column-definition;

Modify column in Table: ALTER TABLE table_name MODIFY column_namecolumn_type; Drop

column in Table:ALTER TABLE table_name DROP COLUMN column_name
DDL QUERIES:

Q1. Write a query to create a table employee with empno, ename, designation, and salary.

SQL>CREATE TABLE EMP (EMP_ID NUMBER (5),

EMP_NAME VARCHAR2 (10), DESIGNATIN

VARCHAR2 (10),

SALARY NUMBER (7));

Table created.

Q2. Write a query for create a table from an existing table with all the fields.

SQL> CREATE TABLE EMP1 AS SELECT * FROM EMP;

Table created.

SQL> DESC EMP1

Name Null? Type

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

EMP_ID NUMBER (4)

EMP_NAME VARCHAR2 (10)

EMP_DESIGNATIN VARCHAR2 (10)

EMP_SALARY NUMBER (7,0)

Q3. Write a Query to Alter the column EMP_ID NUMBER(1001) TO EMPNO NUMBER(1009).

SQL>ALTER TABLE EMP MODIFY EMP_ID NUMBER

(6); Table altered.

Q4. Write a query to add a new column in to employee.

SQL> ALTER TABLE EMP ADD

EMP_EMAILVARCHAR2(12); Table altered.
Q5. Write a query to drop a column from an existing table employee.

SQL> ALTER TABLE EMP DROP COLUMN

EMP_SALARY; Table altered.
Q6. Write a query to drop an existing table employee.

SQL> DROP table employee;

Table deleted.

DML QUERIES:

Q1. Write a query to insert the records in to employee.

SQL>INSERT INTO EMP_D VALUES(1001,'NEELKARAN','MANAGER',50000); 1 row

created.
Q2. Write a query to display the records from employee.

SQL> SELECT * FROM EMP_D;

EMP_ID EMP_NAME EMP_DESIGNATION EMP_SALARY EMP_MOB

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

1001 NEELKARAN MANAGER 50000 7524856515

Q3. Write a query to insert the records in to employee using substitution method.

SQL> INSERT INTO EMP

VALUES(&EMPNO,'&ENAME','&DESIGNATIN','&SALARY');

Enter value for empno: 102

Enter value for ename: DHAJVEER

Enter value for designatin: ASST_PROF

Enter value for salary: 35000

old 1: INSERT INTO EMP

VALUES(&EMPNO,'&ENAME','&DESIGNATIN','&SALARY')
new 1: INSERT INTO EMP VALUES(102,'DHAJVEER','ASST_PROF','35000')

1 row created.

SQL> /

Enter value for empno: 101

Enter value for ename: ABHILASHA

Enter value for designatin: ASST_PROF

Enter value for salary: 40000

old 1: INSERT INTO EMP VALUES(&EMPNO,'&ENAME','&DESIGNATIN','&SALARY')

new 1: INSERT INTO EMP VALUES(101,'ABHILASHA','ASST_PROF','40000') 1 row created.

Q4. Write a query to update the records from employee.

SQL> UPDATE EMP_D SET EMP_SALARY=55000

WHERE EMP_ID=1001;

1 row updated.

SQL> SELECT * FROM EMP;

EMP_ID EMP_NAME EMP_DESIGNATION EMP_SALARY EMP_MOB

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

1001 NEELKARAN MANAGER 55000 7524856515