Ritesh kumar (21SCSE1260009) Page |1

Experiment -1
Aim- Design ER diagrams for various scenarios or based on given projects.
Theory-

What is ER Diagram?
ER Diagram stands for Entity Relationship Diagram, also known as ERD is a diagram that displays the
relationship of entity sets stored in a database. In other words, ER diagrams help to explain the logical structure
of databases. ER diagrams are created based on three basic concepts: entities, attributes and relationships.

ER Diagrams contain different symbols that use rectangles to represent entities, ovals to define attributes and
diamond shapes to represent relationships.

Main components and its symbols in ER Diagrams:

 Rectangles: This Entity Relationship Diagram symbol represents entity types

 Ellipses : Symbol represent attributes
 Diamonds: This symbol represents relationship types
 Lines: It links attributes to entity types and entity types with other relationship types
 Primary key: attributes are underlined
 Double Ellipses: Represent multi-valued attributes

This model is based on three basic concepts:

 Entities

A real-world thing either living or non-living that is easily recognizable and

nonrecognizable. It is anything in the enterprise that is to be represented in our database.

 Attributes

It is a single-valued property of either an entity-type or a relationship-type.

For example, a lecture might have attributes: time, date, duration, place, etc.
 Ritesh kumar (21SCSE1260009) Page |2

 Relationships

Relationship is nothing but an association among two or more entities. E.g., Tom works in
the Chemistry department.
 Ritesh kumar (21SCSE1260009) Page |3

Experiment -2
Aim- Implement DDL Statements and DML statements.
Theory-

The SQL Data Definition Language allows specification of not only a set of relations but also information
about each relation, including-
 Schema for each relation
 The domain of values associated with each attribute.
 The integrity constraints.
 The set of indices to be maintained for each relation.
 The security and authorization information for each relation.
The physical storage structure of each relation on disk.
Data Manipulation Language (DML) statements are used for managing data in database. DML commands are
not auto-committed. It means changes made by DML command are not permanent to database, it can be rolled
back.
DML statements are used for managing data within schema objects. Some examples:
 SELECT - retrieve data from the a database
 INSERT - insert data into a table
 UPDATE - updates existing data within a table
 DELETE - deletes all records from a table, the space for the records remain.
Data Definition Language
 CREATE TABLE
Emp1 (EID int, EName Char, Edept char, EDOB date,Salary int)
Create Table Emp1(EID int, EName varchar(20), Edept varchar(10), EDOB Date, Salary int);
CREATE TABLE TABLENAME (COLUMN_NAME1 DATA_TYPE1(SIZE1),……. COLUMN_NAMEN
DATA_TYPEN(SIZEN));

 ALTER TABLE
ALTER TABLE table_name ADD column_name datatype;
ALTER TABLE table_name MODIFY column_name datatype;
ALTER TABLE table_name DROP COLUMN column_name;
ALTER TABLE table_name RENAME COLUMN old_column_name TO new_column_name;

 DROP TABLE
DROP TABLE table_name;

 RENAME TABLE
 Ritesh kumar (21SCSE1260009) Page |4

RENAME old_table_name TO new_table_name;

 TRUNCATE
TRUNCATE TABLE table_name;
Data Manipulation Language
 SELECT column1, column2, ...FROM table_name;
 INSERT INTO table_name (column1, column2, column3, ...) VALUES
(value1, value2, value3, ...);
 UPDATE table_name SET column1 = value1, column2 = value2, ... WHERE
condition;
 DELETE FROM table_name WHERE condition; Delete from
Emp where EID=2;
 Ritesh kumar (21SCSE1260009) Page |5

Experiment -3
Aim- Execute the SELECT command with different clauses.
Theory-
SQL SELECT Statement
The most used SQL command is SELECT statement.
SQL SELECT statement is used to query or retrieve data from a table in the database.
A query may retrieve information from specified columns or from all of the columns in the table.
To create a simple SQL SELECT Statement, you must specify the column(s) name and the table name.
The whole query is called SQL SELECT Statement.

Syntax of SQL SELECT Statement:

SELECT column_list FROM table-name [WHERE Clause]

[GROUP BY clause] [HAVING clause] [ORDER BY clause];
 Ritesh kumar (21SCSE1260009) Page |6

Experiment -4
Aim- Execute various types of Integrity Constraints on database.
Theory-
SQL Constraints

SQL constraints are used to specify rules for the data in a table.
If there is any violation between the constraint and the data action, the action is aborted by the constraint.
Constraints can be specified when the table is created (inside the CREATE TABLE statement) or after
the table is created (inside the ALTER TABLE statement).
In SQL, we have the following constraints:
 NOT NULL - Indicates that a column cannot store NULL value
 UNIQUE - Ensures that each row for a column must have a unique value
 PRIMARY KEY - A combination of a NOT NULL and UNIQUE. Ensures that a column (or
combination of two or more columns) have a unique identity which helps to find a particular
record in a table more easily and quickly
 FOREIGN KEY - Ensure the referential integrity of the data in one table to match values in
another table
 CHECK - Ensures that the value in a column meets a specific condition
 DEFAULT - Specifies a default value for a column SQL PRIMARY

KEY Constraint

 The PRIMARY KEY constraint uniquely identifies each record in a database table. Primary keys
must contain UNIQUE values.
A primary key column cannot contain NULL values.
Most tables should have a primary key, and each table can have only ONE primary key.

 SQLFOREIGN KEY Constraint

A FOREIGN KEY in one table points to a PRIMARY KEY in another table.

Syntax
SQL CREATE TABLE + CONSTRAINT Syntax
CREATE TABLE table_name( column_name1 data_type(size) constraint_name, column_name2
data_type(size) constraint_name, column_name3 data_type(size) constraint_name,);
 Ritesh kumar (21SCSE1260009) Page |7

CREATE TABLE PersonsNotNull (

P_Id int NOT NULL,LastName varchar(255) NOT NULL,FirstName varchar(255), Address
varchar(255), City varchar(255)
)

CREATE TABLE Persons (

P_Id int NOT NULL,
LastName varchar(255) NOT NULL, FirstName varchar(255),
Address varchar(255), City varchar(255), PRIMARY KEY (P_Id)
)

CREATE TABLE Orders (

O_Id int NOT NULL, OrderNo int NOT NULL, P_Id int,
PRIMARY KEY (O_Id),
FOREIGN KEY (P_Id) REFERENCES Persons(P_Id)
)
 Ritesh kumar (21SCSE1260009) Page |8

Experiment -5
Aim- Implement SINGLE ROW functions (Character, Numeric, Date functions) and
GROUP functions (avg, count, max, min, sum).
Theory-
SINGLE ROW functions
Oracle SQL supplies a rich library of in-built functions which can be employed for various tasks.
The essential capabilities of functions can be the case conversion of strings, in-string or substring operations,
mathematical computations on numeric data, and date operations on date type values.
SQL Functions optionally take arguments from the user and mandatorily return a value.

GROUP functions

Aggregate functions perform a variety of actions such as counting all the rows in a table, averaging a
column's data, and summing numeric data.
Aggregates can also search a table to find the highest "MAX" or lowest "MIN" values in a column.
As with other types of queries, you can restrict, or filter out the rows these functions act on with the WHERE
clause.

For example, if a manager needs to know how many employees work in an organization, the aggregate
function named COUNT(*) can be used to produce this information.The COUNT(*) function shown in the
below
SELECT statement counts all rows in a table.

The SELECT query below demonstrates the use of NVL function.

SELECT first_name, last_name, salary, NVL (commission_pct,0) FROM employees

WHERE rownum < 5;

FIRST_NAME LAST_NAME SALARY NVL(COMMISSION_PCT,0)

Steven King 24000 0

Neena Kochhar 17000 0
Lex De Haan 17000 0
Alexander Hunold 9000 0

Some of the commonly used aggregate functions are as below –

 SUM( [ALL | DISTINCT] expression )
 AVG( [ALL | DISTINCT] expression ) COUNT( [ALL |
 Ritesh kumar (21SCSE1260009) Page |9

DISTINCT] expression ) COUNT(*)

 MAX(expression)
 MIN(expression)
 Ritesh kumar (21SCSE1260009) P a g e | 10

Experiment -6
Aim- Implement the concept of grouping of Data and Sub-queries.
Theory-
Subqueries:- A subquery is a form of an SQL statement that appears inside another SQL statement.
It also termed as nested query.
The statement containing a subquery called a parent statement.
The rows returned by the subquery are use by the following statement.

Example:-
Customers

Customer_Id Name Age

1 Sun 91
2 Moon 90
3 Star 95

Select * From Customers Where Age=(Select MIN(Age) From Customers);

Customer

Customer_Id Name Age

2 Moon 90
 Ritesh kumar (21SCSE1260009) P a g e | 11

Experiment -7
Aim- Implement Simple and Complex View.
Theory-

CREATE VIEW 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 functions, WHERE, and JOIN statements to a view and present the data as if the data were
coming from one single table.
Note: A view always shows up-to-date data! The database engine recreates the data, using the view's SQL
statement, every time a user queries a view.
SQL CREATE VIEW Syntax
CREATE VIEW view_name AS SELECT column_name(s) FROM table_name
WHERE condition
Renaming the columns of a view:-

Syntax:-
CREATE VIEW viewname AS SELECT newcolumnname…. FROM tablename
WHERE columnname=expression_list;
Selecting a data set from a view- Syntax:-
SELECT columnname, columnname FROM viewname
WHERE search condition;

Destroying a view-
Syntax:-

DROP VIEW viewname;

 Ritesh kumar (21SCSE1260009) P a g e | 12

Experiment -8
Aim- Execute the concept of Data Control Language (DCL).
Theory-

Data Control Language (DCL)

Data Control Language (or DCL) consists of statements that control security and concurrent access to table
data.
 COMMIT

Instructs the XDB Server to make permanent all data changes resulting from DML statements executed
by a transaction.
 CONNECT

Connects the application process (or user) to a designated XDB Server or DB2 location. This location
becomes the current location for the application process or user.
 GRANT (Database Privileges)

Assigns access privileges to XDB Server users or applications.

 GRANT (Sequence Privileges)

Grants privileges on a user-defined sequence.

 LOCK TABLE

Extends XDB Server's automatic record and table level locking functions (in a multi-user system) by
acquiring explicit locks on a particular table.
 REVOKE (Database Privileges)

Cancels access privileges for XDB Server users or applications.

 REVOKE (Sequence Privileges)

Revokes the privileges on a user-defined sequence.

 ROLLBACK
 Ritesh kumar (21SCSE1260009) P a g e | 13

Instructs the XDB Server to reverse the effect of any DML commands executed on a database by a
transaction. Information recorded in a backward log is used to restore the database to a state existing
before the transaction.
 UNLOCK TABLE

Removes table locks (in a multi-user system) acquired with the LOCK TABLE command.

Commands that come under DCL:

 Grant
 Revoke

Grant:
This command is use to give user access privileges to a database.

Syntax:

GRANT SELECT, UPDATE ON MY_TABLE TO SOME_USER, ANOTHER_USER;

For example:

GRANT SELECT ON Users TO 'Tom' @ 'localhost;

Revoke:
It is useful to back permissions from the user.

Syntax:

REVOKE privilege_nameON object_name

FROM {user_name |PUBLIC |role_name}

For example:

REVOKE SELECT, UPDATE ON student

FROM BCA, MCA;
 Ritesh kumar (21SCSE1260009) P a g e | 14

Experiment -9
Aim- Implement Transaction Control Language (TCL).
Theory-
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 successfully complete. If any of the tasks fail, the
transaction fails.
Therefore, a transaction has only two results: success or failure. You can explore more about
transactions here.
Hence, the following TCL commands are used to control the execution of a transaction:

 COMMIT: Commits a Transaction.

 ROLLBACK: Rollbacks a transaction in case of any error occurs.
 SAVEPOINT: Sets a save point within a transaction.
 SET TRANSACTION: Specifies characteristics for the transaction.

Transaction control language or TCL commands deal with the transaction within the database.
Commit
This command is used to save all the transactions to the database.

Syntax:

Commit;

For example:

DELETE FROM Students

WHERE RollNo =25;
 Ritesh kumar (21SCSE1260009) P a g e | 15

COMMIT;
Rollback
Rollback command allows you to undo transactions that have not already been saved to the database.

Syntax:

ROLLBACK;

Example:

DELETE FROM Students

WHERE RollNo =25;
SAVEPOINT
This command helps you to sets a savepoint within a transaction.

Syntax:

SAVEPOINT SAVEPOINT_NAME;
Example:

SAVEPOINT RollNo;
 Ritesh kumar (21SCSE1260009) P a g e | 16

Experiment -10
Aim- Write a PL/SQL block for greatest of three numbers using IF AND ELSEIF.
Algorithm-
STEP 1: Start
STEP 2: Initialize the necessary variables.
STEP 3: invoke the if else if condition.
STEP 4: Execute the statements.
STEP 5: Stop

Syntax-
SQL>set server output on
SQL> declare
2 a number;
3 b number;
4 c number;
5 begin
6 a:=&a;
7 b:=&b;
8 c:=&c;
9 if(a>b)and(a>c) then
10 dbms_output.put_line('A is maximum');
11 else if(b>a)and(b>c)then
12 dbms_output.put_line('B is maximum');
13 else
14 dbms_output.put_line('C is maximum');
15 end if;
16 end;
 Ritesh kumar (21SCSE1260009) P a g e | 17

17 / INPUT Enter value for a: 21

old 7: a:=&a;
new 7: a:=21;
Enter value for b: 12
old 8: b:=&b;
new 8: b:=12;
Enter value for b: 45
old 9: c:=&b;
new 9: c:=45;

OUTPUT C is maximum PL/SQL procedure successfully completed.