Program 1

_____________________________________________
____
Aim:
A. Introduction to SQL & Types of SQL Statements (DDL, DML, TCL, DCL)
B. Introduction to DBMS, RDBMS.
C. Introduction to various RDBMS software
Theory:
SQL:
 SQL stands for Structured Query Language.(Also called “Sequel”)
 SQL is a standard language for accessing databases
 SQL has been an international standard (ISO) since 1987,
Characteristics of SQL:
o SQL is easy to learn.

o SQL is used to access data from relational database management systems.

o SQL can execute queries against the database.

o SQL is used to describe the data.

o SQL is used to define the data in the database and manipulate it when needed.

o SQL is used to create and drop the database and table.

o SQL is used to create a view, stored procedure, function in a database.

o SQL allows users to set permissions on tables, procedures, and views.

Types of SQL:
 DDL (Data Definition Language)
 DQL (Data Query Language)
 DML (Data Manipulation Language)
 DCL (Data Control Language)
 TCL (Transaction Control Language)
DDL (Data Definition Language) :
DDL or Data Definition Language actually consists of the SQL commands that can be used to
define the database schema. DDL is a set of SQL commands used to create, modify, and
delete database structures but not data.

Command Description

Create database or its objects (table, index, function,

CREATE
views, store procedure, and triggers)

DROP Delete objects from the database

ALTER Alter the structure of the database

Remove all records from a table, including all spaces

TRUNCATE
allocated for the records are removed

COMMENT Add comments to the data dictionary

DML (Data Manipulation Language):

The SQL commands that deal with the manipulation of data present in the database belong to
DML or Data Manipulation Language and this includes most of the SQL statements.

Command Description

INSERT Insert data into a table

Update existing data within a

UPDATE
table

Delete records from a database

DELETE
table

DCL (Data Control Language)

DCL includes commands such as GRANT and REVOKE which mainly deal with the rights,
permissions, and other controls of the database system.

Command Description

Assigns new privileges to a user account, allowing access

GRANT
to specific database objects, actions, or functions.

Removes previously granted privileges from a user account,

REVOKE taking away their access to certain database objects or
actions.

TCL (Transaction Control Language)

Transactions group a set of tasks into a single execution unit. Each transaction begins with a
specific task and ends when all the tasks in the group are successfully completed. If any of
the tasks fail, the transaction fails.

command Description

BEGIN TRANSACTION Starts a new transaction

COMMIT Saves all changes made during the transaction

ROLLBACK Undoes all changes made during the transaction

SAVEPOINT Creates a save point within the current transaction

DQL (Data Query Language)

DQL statements are used for performing queries on the data within schema objects. The
purpose of the DQL Command is to get some schema relation based on the query passed to it.
Command Description

SELECT It is used to retrieve data from the database

Data Types of SQL:

Data Type Description

The maximum length of 8000 characters.(Fixed-Length non-Unicode

char
Characters)

The maximum length of 8000 characters.(Variable-Length non-Unicode

varchar
Characters)

In SQL Server, for storing integer or numeric values in a table column the
int
INT data type is used.

DATE A data type is used to store the data of date in a record

TIME A data type is used to store the data of time in a record

DBMS:
Database management system is a software which is used to manage the database. For
example: MySQL, Oracle, etc are a very popular commercial database which is used in
different applications.
DBMS provides an interface to perform various operations like database creation, storing
data in it, updating data, creating a table in the database and a lot more.
It provides protection and security to the database. In the case of multiple users, it also
maintains data consistency.

RDBMS:
RDBMS stands for Relational Database Management System.
All modern database management systems like SQL, MS SQL Server, IBM DB2, ORACLE,
My-SQL, and Microsoft Access are based on RDBMS.
It is called Relational Database Management System (RDBMS) because it is based on the
relational model introduced by E.F. Codd.

Difference between DBMS and RDBMS

DBMS RDBMS

DBMS stores data as file. RDBMS stores data in tabular form.

Data elements need to access Multiple data elements can be accessed at the same
individually. time.

Data is stored in the form of tables which are related to

No relationship between data.
each other.

Normalization is not present. Normalization is present.

DBMS does not support

RDBMS supports distributed database.
distributed database.

It stores data in either a It uses a tabular structure where the headers are the
navigational or hierarchical column names, and the rows contain corresponding
form. values.

Security is less More security measures provided.

RBDMS software:
Some popular RDBMS software options:
1. MySQL: An open-source RDBMS that's widely used for web applications. It's known
for its reliability and performance.
 2. PostgreSQL: Another open-source RDBMS, known for its advanced features and
standards compliance. It supports complex queries and large databases.
3. Oracle Database: A commercial RDBMS known for its high performance, scalability,
and robustness. It's widely used in enterprise environments.
4. Microsoft SQL Server: A commercial RDBMS from Microsoft, known for its
integration with other Microsoft products and its strong security features.

Program: 2
___________________________________________________________________________
___

Aim: Creating Entity-Relationship Diagram for given case:

a. Car-insurance company whose customer own or more car each car has
associated with it zero or any number of recorded accidents.
b. Online bookstore application.
Theory:
Entity-Relationship Diagram:
An Entity-Relationship (ER) diagram is a visual representation of the relationships between
entities in a database. It helps in designing a database by illustrating how entities (like tables)
relate to each other.

Components:

1. Entities:
 Represent real-world objects or
concepts.  Visualized as rectangles.
2. Attributes:
 Characteristics or properties of entities. 
Visualized as ovals connected to their corresponding
entity.
3. Relationships:
 Describe how entities interact with each
other.  Visualized as diamonds or lines
connecting entities.
4. Cardinality:
• Indicates the number of instances of one entity that can or must be associated
with instances of another entity.
• Types include:
 One-to-One (1:1)
 One-to-Many (1)  Many-to-Many
(M)
5. Primary Keys:
  Unique identifiers for each entity instance, usually underlined in the diagram.
6. Foreign Keys:  Attributes that create a link between two entities, indicating a
relationship.
7. Weak Entities:
 Entities that cannot be uniquely identified by their own attributes alone. They
depend on a "strong" entity for their identification, typically represented with
a double rectangle.
8. Generalization/Specialization:
• Generalization: Combining common characteristics from multiple entities into
a higher-level entity.
• Specialization: Creating sub-entities from a more general entity, adding
specific attributes.
9. Multi-valued Attributes:
 Attributes that can hold multiple values for a single entity instance (e.g., a
Student may have multiple. Visualized as double ovals.

10. Derived Attributes:

• Attributes that can be calculated from other. Typically represented by dashed ovals.
Benefits of Using ER Diagrams:

• Clarity: Provides a clear visualization of the database structure.

• Communication: Facilitates discussion and understanding among stakeholders,
including developers and non-technical team members.
• Planning: Assists in the planning and organization of data before implementation.
• Normalization: Helps in identifying redundancies and ensuring data integrity.

For case a: Car insurance Company

1. Entity Set: Person, Car and Accident
2. Attributes:
• Person- Name, Address and Driver_id
• Car- License_no., Year and Model
• Accident- Date, Recipt_no. and
3. No. of tables:

For case b: Online bookstore

1. Entity Set:
2. Attributes:
3. No. of tables:
Program: 3
___________________________________________________________________________
______

Aim: DDL Commands: CREATE, ALTER, DROP, TRUNCATE, CREATING A TABLE

from a Table Using SELECT Command.
Theory:
DDL Commands:

DDL or Data Definition Language actually consists of the SQL commands that can be
used to define the database schema. It simply deals with descriptions of the database
schema and is used to create and modify the structure of database objects in the
database

 CREATE:
Create database or its objects (table, index, function, views, store procedure, and
triggers)
Syntax: CREATE TABLE table_name (column1 data_type, column2
data_type, ...); Code:
CREATE TABLE STUDENT(
S_ID NUMBER(10) PRIMARY KEY,
FIRST_NAME VARCHAR2(25) NOT NULL,
SECOND_NAME VARCHAR2(25) NOT NULL,
CITY VARCHAR2(15) NOT NULL,
DOB DATE NULL
);

ALTER:

Alter the structure of the database.

 Syntax: ALTER TABLE table_name MODIFY column_name data_type;
Code:
ALTER TABLE STUDENT MODIFY CITY VARCHAR(30);
ALTER TABLE STUDENT RENAME COLUMN
SECOND_NAME TO LAST_NAME;

• DROP: Delete objects from the database.

Syntax:
1. For deleting entire table content: DROP TABLE table_name;
2. For deleting column: ALTER TABLE table_name DROP COLUMN column_name;
CODE:
ALTER TABLE STUDENT DROP COLUMN DOB;
 Fig 2.4 Deleting Dob column using drop

Fig 2.5 Deleting Table using drop

• TRUNCATE:
Remove all records from a table, including all spaces allocated for the records are
removed.
Syntax: TRUNCATE TABLE table_name;
CODE:

TRUNCATE TABLE STUDENT;

Fig 2.6: Using truncate command in student table.

• CREATING A TABLE From a Table Using SELECT Command:

A copy of an existing table can also be created using CREATE TABLE. The
new table gets the same column definitions. All columns or specific columns
can be selected. If you create a new table using an existing table, the new table
will be filled with the existing values from the old table.
Syntax:
CREATE TABLE new_table_name AS
SELECT column1,column2,...
FROM
existing_table_name
WHERE ....; Code:
 Program: 4
_____________________________________________
____
Aim: DML Commands: Managing data using INSERT, DELETE AND UPDATE
command.
Theory:
DML commands:
The commands that deal with the manipulation of data present in the database belong to
DML or Data Manipulation Language and this includes most of the SQL statements.
It is the component of the SQL statement that controls access to data and to the database.
Basically, DCL statements are grouped with DML statements.

• INSERT:
Insert data into a table in a database.
Syntax: INSERT INTO table_name (column1, column2, ...) VALUES (value1,
value2,
...);
Code:
INSERT INTO STUDENTS (STD_ID, F_NAME, L_NAME, UMAR)
VALUES (1, ’ARYAN’, ‘BAKSHI’, 20);
INSERT INTO STUDENTS (STD_ID, F_NAME, L_NAME, UMAR)
VALUES (2, ’ABHISHEK’, ‘GUPTA’, 20);
SELECT* FROM STUDENTS;

Fig 3.1: Insert data of students in table

• DELETE:
Delete records from a database table.
Syntax: DELETE FROM table_name WHERE condition;
Code:
DELETE FROM STUDENT WHERE STD_ID= 2;
SELECT* FROM STUDENTS;

Fig 3.2: Delete data of student having STD_ID as 2

• UPDATE:
Update existing data within a table.
Syntax: UPDATE table_name SET column1 = value1, column2 = value2
WHERE condition; Code:
UPDATE STUDENT SET L_NAME= ‘KUMAR’ WHERE STD_ID= 2
SELECT * FROM STUDENT;

Fig 3.3: Update data of student having STD_ID as 2

Program-5

_____________________________________________
___
Aim: Implementing data Constraints at Column Level and Table
level: PRIMARY Key, FOREIGN Key, UNIQUE, NOT NULL, CHECK,
DEFAULT value concept.
Theory:
Data Constraints:
• SQL constraints are used to specify rules for data in a table.
• Constraints can be specified when the table is created with
the CREATE
TABLE statement, or after the table is created with the
ALTER TABLE statement.

PRIMARY Key:
 The PRIMARY KEY constraint uniquely identifies each record
in a table.
 Primary keys must contain UNIQUE values, and cannot
contain NULL values.
 A table can have only ONE primary key; and in the table,
this primary key can consist of single or multiple columns
(fields)
NOT NULL –
 By default, a column can hold NULL values.
 The NOT NULL constraint enforces a column to NOT accept
NULL values.
 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
UNIQUE –
 The UNIQUE constraint ensures that all values in a column
are different.
 Both the UNIQUE and PRIMARY KEY constraints provide a
guarantee for uniqueness for a column or set of columns.
 A PRIMARY KEY constraint automatically has a UNIQUE
constraint.
  However, you can have many UNIQUE constraints per table,
but only one PRIMARY KEY constraint per table.
FOREIGN KEY –
 The FOREIGN KEY constraint is used to prevent actions that
would destroy links between tables.
 A FOREIGN KEY is a field (or collection of fields) in one table,
that refers to the PRIMARY KEY in another table.
 The table with the foreign key is called the child table, and
the table with the primary key is called the referenced or
parent table.
CHECK –
 The CHECK constraint is used to limit the value range that
can be placed in a column.
 If you define a CHECK constraint on a column it will allow
only certain values for this column.
 If you define a CHECK constraint on a table it can limit the
values in certain columns based on values in other columns
in the row.
DEFAULT –
 The DEFAULT constraint is used to set a default value for a
column.
 The default value will be added to all new records, if no
other value is specified.

Code:
CREATE TABLE EMPLOYEE (
E_ID INT PRIMARY KEY,
F_NAME VARCHAR(50) NOT NULL,
L_NAME VARCHAR(50) NOT NULL,
AGE INT CHECK (AGE>=18),
SALARY DECIMAL(10,2) DEFAULT 30000,
D_ID INT, FOREIGN KEY (D_ID) REFERENCES DEPARTMENT
(D_ID)
);
CREATE TABLE DEPARTMENT (

D_ID INT PRIMARY KEY,

D_NAME VARCHAR(50) NOT NULL

);

DESCRIBE EMPLOYEE;
DESCRIBE DEPARTMENT;
Fig 5.1: Applying various constraints on table while creating the
table and description of table employee

Fig 5.2 Description of table Department

 Program-6
_____________________________________________
___
Aim: DML Commands: Retrieving database using SELECT
command using various logical operator.
Theory:
DML commands:
DML is an abbreviation for Data Manipulation Language.
Represents a collection of programming languages explicitly used
to make changes to the database, such as: CRUD operations to
create, read, update and delete data.

Select command: It is used to retrieve data from

the database WHERE clause:

• The WHERE clause is used to filter records.

• It is used to extract only those records that fulfill a
specified condition. Syntax: SELECT column1,
column2, ...FROM table_name WHERE condition Operators
(Logical):
SQL logical operators are used to test for the truth of the
condition. A logical operator like the Comparison operator returns
a boolean value of TRUE, FALSE, or UNKNOWN. In this article, we
will discuss different types of Logical Operators.

Logical operators are used to combine or manipulate the

conditions given in a query to retrieve or manipulate data .there
are some logical operators in SQL like IN, BETWEEN etc.

• Like
• In
• Between Like Operator:
In SQL, the LIKE operator is used in the WHERE clause to search
for a specified pattern in a column.

• % – It is used for zero or more than one character.

• _ – It is used for only one character means fixed
length.
Syntax:
SELECT * FROM table_name WHERE column_name LIKE '%xyz% ';

SELECT * FROM table_name WHERE column_name LIKE “abc_def”;

CODE: SELECT * FROM EMPLOYEES;

Fig 6.1: Data in Employee Table

CODE: SELECT * FROM EMPLOYEES;

Fig 6.2: Data in Department Table

CODE:
SELECT * FROM EMPLOYEE

WHERE F_NAME LIKE “A%”;

 Fig 6.3 Retrieving data of employees where names starting with
‘A’ using %

CODE:
SELECT * FROM EMPLOYEE

WHERE F_NAME LIKE “A_ICE”;

Fig 6.4 Retrieving data of employees names having a letter

between ‘A’ and ”ice” using _;

CODE:
SELECT * FROM EMPLOYEE

WHERE F_NAME LIKE “%INCE”;

Fig 6.5 Retrieving data of employees having name ending

with “ince” using %; CODE:
SELECT * FROM EMPLOYEE
WHERE F_NAME LIKE“%h%”;

F Retrieving data of employees having name ending with

“ince” using %; IN Operator:

It is used to remove the multiple OR conditions in SELECT, INSERT,

UPDATE, or DELETE and we can also use NOT IN to minimize the
rows in your list and any kind of duplicate entry will be retained.

Syntax: SELECT column_name(s) FROM table_name WHERE

column_name IN

(value1,value2,..);

CODE:
SELECT * FROM EMPLOYEE

WHERE D_ID IN (1, 3);

Fig 6.6 Retrieving data of employees having D_ID 1 or 3 ending

using in operator;

BETWEEN Operator:
The BETWEEN operator selects values within a given range. The
values can be numbers, text, or dates. 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; CODE:

SELECT * FROM EMPLOYEE

WHERE AGE BETWEEN 25 AND 35;

Retrieving data of employees having AGE between 25 and 35

using between; Combination of IN, LIKE, BETWEEN operators:
Syntax:
SELECT column_name(s) FROM table_name WHERE column_1
BETWEEN value1 AND value2 AND Column_2 IN (value1, value2,
value3,…) AND COLUMN_3 LIKE “%xyz”; CODE:

SELECT * FROM EMPLOYEE

WHERE F_NAME LIKE “A%”

AND D_ID IN (1, 2)

AND AGE BETWEEN 25 AND 40;

Program-8
__________________________________________________________________________

Aim: Implement Aggregate function in SQL: SUM, COUNT, AVG,

MIN and MAX.
Theory:
Aggregate function:
An aggregate function is a function that performs a calculation on
a set of values, and returns a single value.
Aggregate functions are often used with the GROUP BY clause of
the SELECT statement. The GROUP BY clause splits the result-set
into groups of values and the aggregate function can be used to
return a single value for each group.
SUM():
The SUM() function returns the total sum of a numeric column.
SYNTAX:
SELECT SUM(column_name)

FROM table_name

WHERE condition;
CODE: SELECT * FROM EMPLOYEE;

Fig 7.1: Data values of table Employees

CODE:
SELECT SUM(SALARY) AS TOTAL_SALARY FROM EMPLOYEE;
 Fig 7.2: Finding total salary of
Employee AVG():

The AVG() function returns the average value of a numeric

column.
SYNTAX:
SELECT AVG(column_name)

FROM
table_name
WHERE
condition;
CODE:
SELECT AVG(SALARY) FROM EMPLOYEE;

Fig 7.3: Finding average salary of employee

COUNT():
The COUNT() function returns the number of rows that matches a
specified criterion.
SYNTAX:
SELECT COUNT(column_name)

FROM
table_name
WHERE
condition;
CODE:
SELECT COUNT(NAME) AS TOTAL_EMPLOYEE FROM EMPLOYEE;

Fig 7.4: Finding number of

employees MIN():

The MIN() function returns the smallest value of the selected

column.
SYNTAX:
SELECT MIN(column_name)

FROM
table_name
WHERE
condition;
CODE:
SELECT MIN(SALARY) FROM EMPLOYEE;

Fig 7.4: Finding minimum salary of employees

MAX():
The MAX() function returns the largest value of the selected
column.
SYNTAX:
SELECT MAX(column_name)

FROM
table_name
WHERE
condition;
CODE:

SELECT MAX(SALARY) AS MAXIMUM_SALARY FROM EMPLOYEE;

Fig 7.6: Finding maximum salary of employee

Experiment -7
___________________________________________________________________________
______

Aim of Experiment – SQL Functions: Date functions, Conversion

functions.

1. SQL Date Functions: SQL provides various built-in functions to

manipulate and format date and time data. These functions are useful
for extracting parts of a date, performing calculations with dates, and
formatting dates.

Common SQL Date Functions:

1. NOW():

• Returns the current date and time.

2. CURDATE():

• Returns the current date (without time).

3. CURTIME():

• Returns the current time.

4. DATE():

• Extracts the date part from a DATETIME or TIMESTAMP

value.

5. YEAR(), MONTH(), DAY():

• Extracts the year, month, or day from a date.

2. SQL Conversion Functions: Conversion functions are used to convert
data from one data type to another. This is especially useful when dealing
with different data formats or when manipulating strings, dates, and
numeric values.

Common SQL Conversion Functions:

1. CAST():

• Converts a value from one data type to another.

2. CONVERT():

• Similar to CAST(), it converts a value to a specified data type.

Syntax varies slightly in different RDBMS.

3. TO_CHAR():

• Converts a date or number to a string in a specific format

(commonly used in Oracle SQL).

4. TO_DATE():

• Converts a string to a DATE data type (commonly used in

Oracle SQL).

5. CONCAT():

• Converts multiple values into a single string.

Experiment -9
___________________________________________________________________________
______

Retrieving information from database using Group by and Having

clause.
Apparatus Used: Oracle11g

Literature / Theory / Formula:

Creating Groups of Data:
GROUP BY Clause Syntax
SELECT column, group_function(column)
FROM table
[WHERE condition]
[GROUP BY group_by_expression]
[ORDER BY column];
We can divide rows in a table into smaller

Aim of Experiment – groups by using the GROUP BY clause.

Using the GROUP BY Clause

All columns in the SELECT list that are not in group functions must be in
the GROUP BY clause.

Question: Display department number and average salary

department wise. SQL Statement: SELECT deptno, AVG(sal) FROM
emp GROUP BY deptno ;
The GROUP BY column does not have to be in the SELECT list.

Question: Display average salary department wise.

SQL Statement: SELECT AVG(sal) FROM emp GROUP BY deptno ;

Output:

Grouping by More Than One Column

• Using the GROUP BY Clause on Multiple Columns

Question: Display average salary department wise.

SQL Statement: SELECT deptno, job, SUM(salary)
FROM emp

GROUP BY deptno, job ;

Output:
Illegal Queries Using Group Functions: Any column or expression in the
SELECT list that is not an aggregate function must be in the GROUP BY
clause:
SELECT department_id, COUNT(last_name) FROM employees;

Output:

Illegal Queries Using Group Functions

• We cannot use the WHERE clause to restrict groups.
• We use the HAVING clause to restrict groups.
• We cannot use group functions in the WHERE clause.
SELECT department_id, AVG(salary) FROM employees WHERE AVG(salary)
> 8000

GROUP BY

department_id;
Output:
Restricting Group Results with the HAVING Clause
When we use the HAVING clause, the Oracle server restricts groups as
follows:
1. Rows are grouped.
2. The group function is applied.
3. Groups matching the HAVING clause are displayed.

Syntax:
SELECT column, group_function
FROM table
[WHERE condition]
[GROUP BY group_by_expression]
[HAVING group_condition]
[ORDER BY column];
Using the HAVING Clause
Experiment -9
___________________________________________________________________________
_____

Retrieving information from database using Group by and Having

clause.
Apparatus Used: Oracle11g
Literature / Theory / Formula:
Creating Groups of Data:
GROUP BY Clause Syntax
SELECT column, group_function(column)
FROM table
[WHERE condition]
[GROUP BY group_by_expression]
[ORDER BY column];
We can divide rows in a table into smaller
Aim of Experiment – groups by using the GROUP BY clause.
Using the GROUP BY Clause
All columns in the SELECT list that are not in group functions must be in
the GROUP BY clause.
Question: Display department number and average salary
department wise. SQL Statement: SELECT deptno, AVG(sal) FROM
emp GROUP BY deptno ;

The GROUP BY column does not have to be in the SELECT list.

Question: Display average salary department wise.

SQL Statement: SELECT AVG(sal) FROM emp GROUP BY deptno ;
Output:

Grouping by More Than One Column

• Using the GROUP BY Clause on Multiple Columns

Question: Display average salary department wise.

SQL Statement: SELECT deptno, job, SUM(salary)
FROM emp

GROUP BY deptno, job ;

Output:
Illegal Queries Using Group Functions: Any column or expression in the
SELECT list that is not an aggregate function must be in the GROUP BY
clause:
SELECT department_id, COUNT(last_name) FROM employees;

Output:

Illegal Queries Using Group Functions

• We cannot use the WHERE clause to restrict groups.
• We use the HAVING clause to restrict groups.
• We cannot use group functions in the WHERE clause.
SELECT department_id, AVG(salary) FROM employees WHERE AVG(salary)
> 8000

GROUP BY

department_id;
Output:

Restricting Group Results with the HAVING Clause

When we use the HAVING clause, the Oracle server restricts groups as
follows:
1. Rows are grouped.
2. The group function is applied.
3. Groups matching the HAVING clause are displayed.

Syntax:
SELECT column, group_function
FROM table
[WHERE condition]
[GROUP BY group_by_expression]
[HAVING group_condition]
[ORDER BY column];
Using the HAVING Clause

Question: Display department id and maximum salary of those

employees where maximum salary more than 2000 department
wise.
SQL Statement: SELECT dept, MAX(sal)
FROM emp

GROUP BY deptno

HAVING
MAX(sal)>2000 ;

Output: