0

Practical File
Of
Database Management System

MATA GUJRI COLLEGE

FATEHGARH SAHIB
AN AUTONOMOUS COLLEGE

Session-2024-25

SUBMITTED TO: ​ ​ ​ SUBMITTED BY:

Dr.KAMALJEET KAUR SARAO​ ​ ​ ​ NAVJOT SINGH
P.G. Dept. of Comp. Sc. ​ ​ ​ ​ B.Sc.(H)AIDS-II

​ ​ ​​ ROLLNO:
 1

INDEX
SR.NO PROGRAM TITLE PAGE.NO

1 INTRODUCTION 3-6

2 The SQL CREATE TABLE Statement. 7

3 The SQL INSERT INTO Statement. 8

4 ●​ Select ALL columns 9-12

●​ The SQL SELECT Statement

●​ THE SQL AND Operator
●​ The SQL OR Operator

5 The SQL SELECT DISTINCT Statement 13

6 The SQL COUNT() Function 14

7 The SQL DELETE Statement 15

8 The SQL UPDATE Statement 16

9 The SQL ORDER BY 17

10 The SQL MIN( ) and MAX( ) Functions 18-19

11 The SQL AVG( ) Function 20

12 The SQL SUM( ) Function 21

 2

13 The SQL LIKE Function 22-23

14 The SQL IN Function 24

15 The SQL BETWEEN Operator 25

16 The NOT Operator 26

17 SQL ALTER TABLE Statement- 27-28

18 SQL CREATE STATEMENT 29-30

A.)CREATE VIEW STATEMENT
B.)DROP VIEW STATEMENT

19 SQL NOT NULL Constraints 31

20 INDEXS 32

21 Database security and privileges: Grant and 33-35

Revoke

22 COMMIT AND ROLLBACK Commands: 36

23 PL/SQL Code: 37

24 Writing PL/SQL Code: 38

25 Stored Procedure: 39-40

26 CURSORS 41-42
 3

1. Introduction:
Database Management system (DBMS)

Database Management Systems (DBMS) are software systems used to

store, retrieve, and run queries on data. A DBMS serves as an interface
between an end-user and a database, allowing users to create, read,
update, and delete data in the database.
DBMS manages the data, the database engine, and the database schema,
allowing for data to be manipulated or extracted by users and other
programs. This helps provide data security, data integrity, concurrency, and
uniform data administration procedures

Database Table
A table is a collection of related data entries, and it consists of columns
and rows. A column holds specific information about every record in the
table. A record (or row) is each individual entry that exists in a table.

Introduction to SQL
SQL is a standard language for accessing and manipulating databases.
What is SQL
● SQL stands for Structured Query Language
● SQL lets you access and manipulate databases
● SQL became a standard of the American National Standards Institute (ANSI) in
1986, and of the International Organization for Standardization (ISO) in 1987
 4

What are features of SQL

Data Querying: SQL allows for complex queries to retrieve specific data from one
or more tables using the SELECT statement. You can filter, sort, and aggregate
data to extract meaningful information.
Data Manipulation: SQL provides commands for modifying data:

● INSERT: Adds new records.

● UPDATE: Alters existing records.
● DELETE: Removes records

SQL Rules:-

● Structure query language is not case sensitive. Generally, keywords of SQL are
written in uppercase.
● Statements of SQL are dependent on text lines. We can use a single SQL
statement on one or multiple text lines.
● Using the SQL statements, you can perform most of the actions in a database.
● SQL depends on tuple relational calculus and relational algebra.

Table :-
The data in an RDBMS is stored in database objects which are called tables. This
table is basically a collection of related data entries and it consists of numerous
columns and rows. A table is the most common and simplest form of data storage
in a relational database.
 5

Row or Record:-
In relational databases, a row is a data record within a table. Each row, which
represents a complete record of specific item data, holds different data within the
same structure. A row is occasionally referred to as a tuple.

Column:-
In relational databases, a column is a set of data values of a particular type, one
value for each row of the database.

Data definition language (DDL)

● CREATE: Creates a new database or object, such as a table, index or column
● ALTER: Changes the structure of the database or object
● DROP: Deletes the database or existing objects
● RENAME: Renames the database or existing objects

Data manipulation language (DML)

● INSERT: Adds new data to the existing database table
● UPDATE: Changes or updates values in the table
● DELETE: Removes records or rows from the table
● SELECT: Retrieves data from the table or multiple tables

Data control language (DCL)

● GRANT: Gives a user access to the database
● REVOKE: Removes a user's access to the database
 6

2. Create table statement:

The CREATE TABLE statement is used to create a new table in a database.

Syntax:
CREATE TABLE table_name (
column1 datatype,
column2 datatype,
column3 datatype,
....
);
2. The SQL CREATE TABLE Statement
The CREATE TABLE statement is used to create a new table in a database.

Syntax
CREATE TABLE table_name(
column1 data type,
column2 data type,
column3 data type,
…
):
EXAMPLE :-
 7

3.The SQL INSERT INTO Statement

The INSERT INTO statement is used to insert new records in a table

INSERT INTO Syntax

INSERT INTO table_name

VALUES (value1, value2, value3, ...);

EXAMPLE:-
 8

4. Select
ALL
columns
If you want to return all columns, without specifying every column name, you can
use the SELECT * syntax:

Example:-
 9

● The SQL SELECT Statement

The SELECT statement is used to select data from a database.

Syntax
 10

SELECT column1, column2, ...

FROM table_name;

● The SQL AND Operator

The WHERE clause can contain one or many AND operators.The AND operator
is , used to filter records based on more than one condition
 11

● The SQL OR Operator

The WHERE clause can contain one or more OR operators
The OR operator is used to filter records based on more than one condition

Syntax
SELECT column1, column2, …
FROM table_name
WHERE condition1 OR condition2 OR condition3 ...;
 12

5. The SQL SELECT DISTINCT Statement

The SELECT DISTINCT statement is used to return only distinct (different)
values.
Syntax
SELECT DISTINCT column1, column2, ...
FROM table_name;
 13

6. The SQL COUNT() Function

The COUNT() function returns the number of rows that matches a specified
criterion.
Syntax
SELECT COUNT(column_name)
FROM table_name
WHERE condition;
 14

7. The SQL DELETE Statement

The DELETE statement is used to delete existing records in a table.
DELETE Syntax
DELETE FROM table_name WHERE condition;
 15

8. The SQL UPDATE Statement

The UPDATE statement is used to modify the existing records in a table.
UPDATE Syntax
UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition;
 16

9. The SQL ORDER BY

The ORDER BY keyword is used to sort the result-set in ascending or descending
order.
Syntax
SELECT column1, column2, ... FROM table_name ORDER BY column1,
column2, ... ASC|DESC;
 17

10. The SQL MIN() and MAX() Functions

The MIN() function returns the smallest value of the selected column.
The MAX() function returns the largest value of the selected column.
Syntax
SELECT MIN(column_name)
FROM table_name
WHERE condition;

MIN()
 18

MAX()
Syntax
SELECT Max(column_name)
FROM table_name
WHERE condition;
 19

11. The SQL AVG() Function

The AVG() function returns the average value of a numeric column.
Syntax
SELECT AVG(column_name)
FROM table_name
WHERE condition;
 20

12. The SQL SUM() Function

The SUM() function returns the total sum of a numeric column.
Syntax
SELECT SUM(column_name)
FROM table_name
WHERE condition;
 21

13. The SQL LIKE Operator

The LIKE operator is used in a WHERE clause to search for a specified
pattern in a column.
There are two wildcards often used in conjunction with the LIKE operator:
● The percent sign % represents zero, one, or multiple characters
● The underscore sign _ represents one, single character
Syntax
SELECT column1, column2, …
FROM table_name
WHERE columnN LIKE pattern;
1.
 22

2.

3.
 23

14. The SQL IN Operator

The IN operator allows you to specify multiple values in a WHERE clause
The IN operator is a shorthand for multiple OR conditions. Ju in
Syntax
SELECT column_name(s)
FROM table_name
WHERE column_name IN (value1, value2, ...);

15. The SQL BETWEEN Operator

The BETWEEN operator selects values within a given range. The values
can be numbers, text, or dates
 24

The BETWEEN operator is inclusive: begin and end values are included.
Syntax
SELECT column_name(s)
FROM table_name
WHERE column_name BETWEEN value1 AND value2;

16. The NOT Operator

The NOT operator is used in combination with other operators to give the
opposite result, also called the negative result.
Syntax
 25

SELECT column1, column2, ...

FROM table_name
WHERE NOT condition;

17. SQL ALTER TABLE Statement

The ALTER TABLE statement is used to add, delete, or modify columns in
an existing table
The ALTER TABLE statement is also used to add and drop various
constraints on an existing table.
 26

Syntax
RENAME COLUMN
select old_name as new_name from table_name ;

2.
 27

18. SQL CREATE STATEMENT

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.
You can add SQL statements and functions to a view and present the data as if the
data were coming from one single table.
A view is created with the CREATE VIEW statement.

A.) CREATE VIEW Syntax

CREATE VIEW view_nameAS
SELECT column1, column2,.. .
FROM table_name
WHERE condition;
 28

B.) DROP VIEW

The drop view command deletes a view.
VIEW Syntax
DROP VIEW VIEW_NAME;
 29

19. SQL NOT NULL CONSTRAINTS

BY default, a column can hold NULL value.

The NOT NULL constraint enforces a column to NOT accept NULL value.

This enforces a field to always contain a value, which means that you
cannot insert a new record, or update a record without adding a value to
this field.
 30

20. INDEX
The CREATE INDEX statement is used to create indexes in tables.

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

SYNTAX
CREATE INDEX index_name
ON table_name(column1,column2, ..);

CREATE INDEX:
EXAMPLE:

DROP INDEX:
EXAMPLE:

21. Database security and Privileges: Grant and Revoke

a. Grant :
SQL Grant command is specifically used to provide privileges to database
objects for a user. This command also allows users to grant permissions to
other users too.
Syntax:
grant privilege_name on object_name
 31

to {user_name | public | role_name}

EXAMPLE:
 32

Other similar commands grant privileges to the users.

b. Revoke:
Revoke command withdraw user privileges on database objects if any
granted. It does operations opposite to the Grant command. When a
privilege is revoked from a particular user U, then the privileges granted to
all other users by user U will be revoked.
 33

Syntax:

revoke privilege_name on object_name

from {user_name | public | role_name}

EXAMPLE:

22. COMMIT and ROLLBACK Commands:

COMMIT in SQL is a transaction control language that is used to
permanently save the changes done in the transaction in tables/databases.
The database cannot regain its previous state after its execution of a
commit.

EXAMPLE :
 34

ROLLBACK in SQL is a transactional control language that is used to undo

the transactions that have not been saved in the database. The command
has only been used to undo changes since the last COMMIT.

EXAMPLE :

23. PL/SQL Architecture:

The PL/SQL compilation and run-time system is an engine that compiles

and executes PL/SQL blocks and subprograms. The engine can be
installed in an Oracle server or in an application development tool such as
Oracle Forms.
In either environment, the PL/SQL engine accepts as input any valid
PL/SQL block or subprogram. The PL/SQL engine processes an
 35

anonymous block. The PL/SQL engine executes procedural statements but

sends SQL statements to the SQL engine in the Oracle database.

24. Writing PL/SQL Code:

Every PL/SQL statement ends with a semicolon (;). PL/SQL blocks can be
nested within other PL/SQL blocks using BEGIN and END. Following is the
basic structure of a PL/SQL block −

Syntax:
DECLARE
< declarations section>
 36

BEGIN
<executable command(s)>
EXCEPTION
<exception handling>
END;

25. Stored Procedure:

A stored procedure is a prepared SQL code that you can save, so the code
can be reused over and over again.
So if you have an SQL query that you write over and over again, save it as
a stored procedure, and then just call it to execute it.
You can also pass parameters to a stored procedure, so that the stored
procedure can act based on the parameter value(s) that is passed.

Syntax:
CREATE PROCEDURE procedure_name
AS
sql_statement
GO;
 37

EXAMPLE :
 38

26. CURSORS:
Cursor is a Temporary Memory or Temporary Work Station. It is Allocated
by Database Server at the Time of Performing DML(Data Manipulation
Language) operations on Table by User. Cursors are used to store
Database Tables. There are 2 types of Cursors: Implicit Cursors, and
Explicit Cursors.

Syntax:
DECLARE cursor_name CURSOR FOR SELECT column(s)_name…
table_name

EXAMPLE:
39