EXPERIMENT NO.

:- 02

Name of Experiment :- Introduction to SQL, DDL, DDL, DCL queries and constraints.
Populating and manipulating database tables using DML statements.

Introduction to SQL :-
A Brief History of SQL -

The history of SQL begins in an IBM laboratory in San Jose, California, where SQL was
developed in the late 1970s. The initials stand for Structured Query Language, and the language
itself is often referred to as "sequel." It was originally developed for IBM's DB2 product (a
relational database management system, or RDBMS, that can still be bought today for various
platforms and environments). In fact, SQL makes an RDBMS possible. SQL is a nonprocedural
language, in contrast to the procedural or third- generation languages (3GLs) such as COBOL
and C that had been created up to that time.
The characteristic that differentiates a DBMS from an RDBMS is that the RDBMS provides a
set-oriented database language. For most RDBMSs, this set-oriented database language is
SQL. Set oriented means that SQL processes sets of data in groups.
An Overview of SQL
SQL is the de facto standard language used to manipulate and retrieve data from these
relational databases. SQL enables a programmer or database administrator to do the following:

• Modify a database's structure

• Change system security settings
• Add user permissions on databases or tables
• Query a database for information
• Update the contents of a database
SQL is basically divided into four parts;

• DDL = Data Definition Language

• DML = Data Manipulation Language
• DCL = Data Control Language
• TCL = Transaction Control Language
DDL stands for data definition language which is to define any table as well as
creation of tables. It includes four basic commands create, drop, alter, truncate.
DML stands for data manipulation language which is used to perform any
manipulation, updating the existing data. We are using four basic commands that
are insert, update, select, delete.

TCL stands for transaction control language which is used to control the
transactions just performed? In this we are basically using two commands, ie.
Commit and rollback. Commit is used to save the transactions and rollback is
used to unsave the transaction and it backs to its original position.

DCL stands for data control language. It is used to control the data access by the
user. If u don’t want to give permission to anybody to access any data in that we
can. It includes basically two commands that are grant and revoke.

Components of SQL

Data Definition Language (DDL) Commands

What is DDL?

DDL, which stands for Data Definition Language, is a subset of SQL (Structured Query
Language) commands used to define and modify the database structure. These commands are
used to create, alter, and delete database objects like tables, indexes, and schemas.

The primary DDL commands in SQL include:

1. CREATE: This command is used to create a new database object. For example, creating
a new table, a view, or a database.
 Syntax for creating a table: CREATE TABLE table_name (column1 datatype,
column2 datatype, ...);
2. ALTER: This command is used to modify an existing database object, such as adding,
deleting, or modifying columns in an existing table.
 Syntax for adding a column in a table: ALTER TABLE table_name ADD
column_name datatype;
  Syntax for modifying a column in a table: ALTER TABLE table_name MODIFY
COLUMN column_name datatype;
3. DROP: This command is used to delete an existing database object like a table, a view,
or other objects.
 Syntax for dropping a table: DROP TABLE table_name;
4. TRUNCATE: This command is used to delete all data from a table, but the structure of
the table remains. It’s a fast way to clear large data from a table.
 Syntax: TRUNCATE TABLE table_name;
5. COMMENT: Used to add comments to the data dictionary.
 Syntax: COMMENT ON TABLE table_name IS 'This is a comment.';
6. RENAME: Used to rename an existing database object.
 Syntax: RENAME TABLE old_table_name TO new_table_name;
DDL commands play a crucial role in defining the database schema.

Data Manipulation Language (DML) Commands in SQL

What is DML Commands in SQL?

Data Manipulation Language (DML) is a subset of SQL commands used for adding (inserting),
deleting, and modifying (updating) data in a database. DML commands are crucial for managing
the data within the tables of a database.

The primary DML commands in SQL include:

1. INSERT: This command is used to add new rows (records) to a table.

 Syntax: INSERT INTO table_name (column1, column2, column3, ...) VALUES
(value1, value2, value3, ...);
2. UPDATE: This command is used to modify the existing records in a table.
 Syntax: UPDATE table_name SET column1 = value1, column2 = value2, ... WHERE
condition;
 The WHERE clause specifies which records should be updated. Without it, all
records in the table will be updated.
 3. DELETE: This command is used to remove one or more rows from a table.
 Syntax: DELETE FROM table_name WHERE condition;
 Like with UPDATE, the WHERE clause specifies which rows should be deleted.
Omitting the WHERE clause will result in all rows being deleted.
4. SELECT: Although often categorized separately, the SELECT command is sometimes
considered part of DML as it is used to retrieve data from the database.
 Syntax: SELECT column1, column2, ... FROM table_name WHERE condition;
 The SELECT statement is used to query and extract data from a table, which can then
be used for various purposes.
Data Control Language (DCL) Commands in SQL

What is DCL commands in SQL?

Data Control Language (DCL) is a subset of SQL commands used to control access to data in a
database. DCL is crucial for ensuring security and proper data management, especially in multi-
user database environments.

The primary DCL commands in SQL include:

1. GRANT: This command is used to give users access privileges to the database. These
privileges can include the ability to select, insert, update, delete, and so on, over database
objects like tables and views.
 Syntax: GRANT privilege_name ON object_name TO user_name;
 For example, GRANT SELECT ON employees TO user123; gives user123 the
permission to read data from the employees table.
2. REVOKE: This command is used to remove previously granted access privileges from a
user.
 Syntax: REVOKE privilege_name ON object_name FROM user_name;
 For example, REVOKE SELECT ON employees FROM user123; would
remove user123‘s permission to read data from the employees table.
  Database administrators typically use DCL commands. When using these commands, it’s
important to carefully manage who has access to what data, especially in environments
where data sensitivity and user roles vary significantly.
 In some systems, DCL functionality also encompasses commands like DENY (specific to
certain database systems like Microsoft SQL Server), which explicitly denies specific
permissions to a user, even if those permissions are granted through another role or user
group.
 Remember, the application and syntax of DCL commands can vary slightly between
different SQL database systems, so it’s always good to refer to specific.

Constraints :-

o Integrity constraints are a set of rules. It is used to maintain the quality of information.

o Integrity constraints ensure that the data insertion, updating, and other processes have to
be performed in such a way that data integrity is not affected.

o Thus, integrity constraint is used to guard against accidental damage to the database.

Types of Integrity Constraint

1. Domain constraints
o Domain constraints can be defined as the definition of a valid set of values for an
attribute.

o The data type of domain includes string, character, integer, time, date, currency, etc. The
value of the attribute must be available in the corresponding domain.

Example:
2. Entity integrity constraints
o The entity integrity constraint states that primary key value can't be null.

o This is because the primary key value is used to identify individual rows in relation and if
the primary key has a null value, then we can't identify those rows.

o A table can contain a null value other than the primary key field.

Example:

3. Referential Integrity Constraints

o A referential integrity constraint is specified between two tables.

 o In the Referential integrity constraints, if a foreign key in Table 1 refers to the Primary
Key of Table 2, then every value of the Foreign Key in Table 1 must be null or be
available in Table 2.

Example:

4. Key constraints
o Keys are the entity set that is used to identify an entity within its entity set uniquely.

o An entity set can have multiple keys, but out of which one key will be the primary key. A
primary key can contain a unique and null value in the relational table.

Example:
Modifying the structure of tables – DDL Commands
Renaming tables,
Dropping table structure – Alter Table,
Rename, Drop, Truncate Commands

The structure of a table can be modified by using the ALTER TABLE it is possible to add or
delete columns, create or destroy indexes, change the data of existing columns orrename
columns or the table itself.

Adding New Columns:

This command is used to add anew column at the end of the structure of an existingtable.

Syntax: ALTER TABLE <TableName>ADD(<NewColumnName><Datatype>(<size>),

<NewColumnName><Datatype>(<size>),…….);

Ex: ALTER TABLE STUDENT ADD(AGE NUMBER(2));

Output:

Dropping a Column from the Table

This is used for removing a column of any existing table.

Syntax: ALTER TABLE <TableName> DROP COLUMN <ColumnName>;

Ex: ALTER TABLE STUDENT DROP COLUMN AGE;

Output:

Modifying Existing Columns

This is used to modify/change the size of any column in a table.

Syntax: ALTER TABLE <TableName>MODIFY (<ColumnName><NewDataType>

(<NewSize>));

Ex: ALTER TABLE STUDENT MODIFY(ROLL_NO VARCHAR2(15);

Output:

RENAMING TABLES
RENAME command is used to change the old name of any table to a new one.
Syntax: RENAME <OldTableName> TO <NewTableName>;

Ex: RENAME STUDENT TO STU;

Output:

DESTROYING TABLES
DROP TABLE command is used to delete/discard any table.

Syntax: DROP <TableName>;

Ex: CREATE TABLE X(N NUMBER(2));

DROP TABLE X;

Output:

TRUNCATING TABLES
TRUNCATE TABLE command deletes all the rows of any table.

Syntax: TRUNCATE TABLE <STU>;

Difference between DELETE and TRUNCATE command –

It is similar to a DELETE statement for deleting all rows but there are some differencesalso:
Truncate operations drop & re-create the table.

Deleted rows cannot be recovered i.e. rows are deleted permanently.

Updating The Contents Of A Table – DML Commands

The UPDATE command is used to change or modify data values in a table.

Update All Rows

UPDATE <TableName> SET <ColumnName1> = <Expression1>,

<ColumnName2>=<Expression2>;

Ex: UPDATE STUDENT SET BRANCH=‘CS’;

Output:

Update Selected Rows

UPDATE <TableName> SET <ColumnName1> = <Expression1>,

<ColumnName2>=<Expression2> WHERE <Condition>

Ex: UPDATE STUDENT SET PERCENT = 81.25 WHERE

ROLL_NO=‘CS/05/119’;
 EXPERIMENT NO. :- 03

Name of Experiment :- Selecting data from tables : SELECT statement, where clause,
having clause, group by, order by, selecting NULL values, use of IN and DISTINCT
keywords.

SELECT Statement - Viewing data from the

tablesAll Rows & Columns of a Table-

SELECT * FROM <Table Name>;

Ex: SELECT * FROM STUDENT;

Output:

student_id name age grade

1 John Doe 18 A
2 Jane Smith 17 B
3 AAA 18 C

Selected Columns & All Rows –

SELECT <Column Name1>, <Column Name2>FROM <Table Name>;

Ex: SELECT NAME, ROLL_NO FROM STUDENT;

name roll_no
John Doe 101
Jane Smith 102
AAA 103

(A) WHERE CLAUSE

This clause is used to specify any condition within a SQL statement.

Ex: WHERE clause can be used with CREATE, SELECT, DELETE & UPDATE
commands.
Output:

Selected Rows & All Columns –

SELECT * FROM <TableName> WHERE <Condition>;

Ex: SELECT * FROM STUDENT WHERE NAME=‘AAA’;

Output:

name age grade

AAA 18 C

(B) GROUP BY CLAUSE

This clause is used to filter data. This clause creates a data set , containing
several setsof records grouped together based on a condition.

Syntax:
SELECT <ColumnName1>, <ColumnName2>,
<ColumnName1>, Aggregate_Function
(<Expression>) GROUP BY <ColumnName1>, <ColumnName2>,
<ColumnNameN>;

Ex: SELECT PERCENT, COUNT (ROLL_NO) “No. of Students”

FROM STUDENT GROUP BY PERCENT;

Output:

PERCENT | No. of Students

------------------------
80 | 5
85 | 8
90 | 12

(C) HAVING CLAUSE

This can be used in conjunction with the GROUP BY clause. Having imposes
(puts) acondition on the GROUP BY clause, which further filters the groups
created by the Group By clause.

Syntax:
SELECT <ColumnName1>, <ColumnName2>,
<ColumnName1>,Aggregate_Function (<Expression>) GROUP BY
<ColumnName1>, <ColumnName2>,<ColumnNameN> HAVING <Condition>;

Ex: SELECT PERCENT, COUNT (ROLL_NO) “No.of Students”FROM

STUDENT GROUP BY PERCENT HAVING ROLL_NO> 15;

Output: 25 Student

This clause is used to filter data. This clause creates a data set , containing
several setsof records grouped together based on a condition.

Syntax:
SELECT
<ColumnName1>,<ColumnName2>,<ColumnName1>,Aggregate_Function
(<Expression>) GROUP BY <ColumnName1>, <ColumnName2>,
<ColumnNameN>;

Ex: SELECT PERCENT, COUNT (ROLL_NO) “No.of Students”FROM

STUDENT GROUP BY PERCENT;

(D) HAVING CLAUSE

This can be used in conjunction with the GROUP BY clause. Having imposes
(puts) acondition on the GROUP BY clause, which further filters the groups
created by the Group By clause.

Syntax:
SELECT <ColumnName1>, <ColumnName2>,
<ColumnName1>,Aggregate_Function (<Expression>) GROUP BY
<ColumnName1>, <ColumnName2>, <ColumnNameN> HAVING <Condition>;

Ex: SELECT PERCENT, COUNT (ROLL_NO) “No.of Students”FROM

STUDENTGROUP BY PERCENT HAVING ROLL_NO> 15;
 EXPERIMENT NO. :- 04

Name of Experiment :- Study the use of SQL string, date, arithmetic and
aggregate functions with examples.

String Functions:

String functions in SQL allow manipulation and modification of string data. Some commonly
used string functions include:

 CONCAT: Concatenates two or more strings together.

 UPPER/LOWER: Converts a string to uppercase or lowercase, respectively.
 SUBSTRING: Extracts a substring from a string.
 LENGTH/LEN: Returns the length of a string.

Example:

SELECT CONCAT('Hello ', 'World') AS concatenated_string;

SELECT UPPER('hello') AS uppercase_string;
SELECT SUBSTRING('abcdef', 1, 3) AS substring_result;
SELECT LENGTH('Hello') AS string_length;

2. Date Functions:

Date functions in SQL are used for manipulating date and time values. Some commonly used
date functions include:

 CURRENT_DATE: Returns the current date.

 DATE_ADD/DATE_SUB: Adds or subtracts a specified time interval from a date.
 DATE_FORMAT: Formats a date as a string according to a specified format.
 DATEDIFF: Calculates the difference between two dates.

Example:

SELECT CURRENT_DATE AS current_date;

SELECT DATE_ADD('2024-01-01', INTERVAL 1 MONTH) AS new_date;
SELECT DATE_FORMAT('2024-05-25', '%Y-%m-%d') AS formatted_date;
SELECT DATEDIFF('2024-05-25', '2024-01-01') AS date_difference;

3. Arithmetic Operations:
Arithmetic operations in SQL are used for mathematical calculations. Common arithmetic
operators include:

 + (Addition)
 Subtraction)
 (Multiplication)
 / (Division)

Example:

SELECT 5 + 3 AS addition_result;
SELECT 10 - 4 AS subtraction_result;
SELECT 6 * 2 AS multiplication_result;
SELECT 20 / 4 AS division_result;

4. Aggregate Functions:

Aggregate functions in SQL operate on a set of values and return a single value. Commonly used
aggregate functions include:

 COUNT: Counts the number of rows in a result set.

 SUM: Calculates the sum of values in a column.
 AVG: Calculates the average of values in a column.
 MAX/MIN: Returns the maximum/minimum value in a column.

Example:

SELECT COUNT(*) AS total_records FROM employees;

SELECT SUM(salary) AS total_salary FROM employees;
SELECT AVG(salary) AS average_salary FROM employees;
SELECT MAX(salary) AS highest_salary FROM employees;

Example :-

-- Create a table for demonstration

CREATE TABLE employees (
emp_id INT,
emp_name VARCHAR(100),
hire_date DATE,
salary DECIMAL(10, 2)
);

-- Insert some sample data

INSERT INTO employees (emp_id, emp_name, hire_date, salary) VALUES
(1, 'John Doe', '2020-01-15', 50000.00),
(2, 'Jane Smith', '2019-05-20', 60000.00),
(3, 'Michael Johnson', '2021-03-10', 55000.00),
(4, 'Emily Davis', '2022-07-05', 52000.00),
(5, 'David Wilson', '2023-02-28', 48000.00);

-- Concatenation using string functions

SELECT emp_name || ' is hired since ' || hire_date AS emp_info
FROM employees;

-- Extracting year from date using date functions

SELECT emp_name, EXTRACT(YEAR FROM hire_date) AS hire_year
FROM employees;

-- Adding bonus of 10% to salaries using arithmetic operations

SELECT emp_name, salary, salary * 1.1 AS new_salary
FROM employees;

-- Finding average salary using aggregate functions

SELECT AVG(salary) AS avg_salary
FROM employees;

This SQL file creates a table of employees with columns for employee ID, name, hire date, and
salary. It then inserts some sample data.

The experiments in the file demonstrate:

 Concatenating strings using the || operator.

 Extracting the year from the hire date using the EXTRACT function.
 Adding a bonus of 10% to salaries using arithmetic operations.
 Finding the average salary using the AVG aggregate function.

Output :-

1. Concatenation using string functions:

emp_info
------------------------------
John Doe is hired since 2020-01-15
Jane Smith is hired since 2019-05-20
Michael Johnson is hired since 2021-03-10
Emily Davis is hired since 2022-07-05
David Wilson is hired since 2023-02-28
2. Extracting year from date using date functions:

emp_name hire_year
John Doe 2020
Jane Smith 2019
Michael Johnson 2021
Emily Davis 2022
David Wilson 2023

3. Adding bonus of 10% to salaries using arithmetic operations:

No. emp_name salary new_salary

1 John Doe 50000.00 55000.00
2 Jane Smith 60000.00 66000.00
3 Michael 55000.00 60500.00
Johnson
4 Emily Davis 52000.00 57200.00
5 David Wilson 48000.00 52800.00

4. Finding average salary using aggregate functions:

avg_salary
----------
53000.00
 EXPERIMENT NO. :- 05

Name of Experiment :- JOINS : study the use of joining tables using natural, inner, outer
joins.
Inner Join:
An inner join returns only the rows from both tables that satisfy the join condition. It combines
rows from two tables based on a related column (or columns) by comparing the values and
includes only the rows where the condition is true.
Syntax:
SELECT column_list
FROM table1
INNER JOIN table2 ON table1.column = table2.column;
Example :-
Inner join returns rows when there is at least one match in both tables. Consider two tables,
"Employees" and "Departments":
Employees Table:
EmployeeID Name DepartmentID
1 John 1
2 Alice 2
3 Bob 1
4 Mary NULL
Departments Table:
DepartmentID DepartmentName
1 HR
2 IT
3 Finance

Example SQL Query for Inner Join:

SELECT Employees.Name, Departments.DepartmentName
FROM Employees
INNER JOIN Departments ON Employees.DepartmentID = Departments.DepartmentID;
Output:
Name Department Name
John HR
Alice IT
Bob HR

Outer Join:
 Outer joins return all the rows from at least one of the tables specified in the FROM
clause, as long as the join condition is met.
 There are three types of outer joins: LEFT OUTER JOIN, RIGHT OUTER JOIN, and
FULL OUTER JOIN.
Syntax:
SELECT column_list FROM table1 LEFT OUTER JOIN table2 ON table1.column =
table2.column;
SELECT column_list FROM table1 RIGHT OUTER JOIN table2 ON table1.column =
table2.column;
SELECT column_list FROM table1 FULL OUTER JOIN table2 ON table1.column =
table2.column;
Example :-

Outer joins return all rows from one table and matching rows from the other table. If there is no
match, NULL values are returned. There are three types: LEFT, RIGHT, and FULL.
Example SQL Query for Left Outer Join:

SELECT Employees.Name, Departments.DepartmentName

FROM Employees
LEFT OUTER JOIN Departments ON Employees.DepartmentID = Departments.DepartmentID;
Output:

Name DepartmentName
John HR
Alice IT
Bob HR
Mary NULL

Natural Join:
A natural join is a type of join that automatically matches columns with the same name in both
tables. It eliminates the need to explicitly specify the columns to join on, but it can be risky if the
column names are not unique across tables.
Syntax:
SELECT column_list FROM table1 NATURAL JOIN table2;
Example :-
Natural join automatically matches columns with the same name in both tables. Consider the
same tables as before.
Example SQL Query for Natural Join:
SELECT Employees.Name, Employees.DepartmentID, Departments.DepartmentName
FROM Employees
NATURAL JOIN Departments;
Output:
Name DepartmentID DepartmentName
John 1 HR
Alice 2 IT
Bob 1 HR
Example of SQL Query – Inner Join , Natural Join , Outer Join
-- Create the Employee table
CREATE TABLE Employee (
EmployeeID INT PRIMARY KEY,
Name VARCHAR(100),
Department_id VARCHAR(100) );
-- Insert sample data into the Employee table
INSERT INTO Employee (EmployeeID, Name, Department_id)
VALUES
(1, 'piyush', 'cs-1'),
(2, 'bonika', 'cs-2'),
(3, 'pradeep', 'civil-1'),
(4, 'prakash', 'ee-5');
-- Create the Department table
CREATE TABLE Department (
Department_id VARCHAR(100) PRIMARY KEY,
Name VARCHAR(100) );
-- Insert sample data into the Department table
INSERT INTO Department (Department_id, Name)
VALUES
('cs-1', 'Computer Science'),
('cs-2', 'Computer Science'),
('civil-1', 'Civil Engineering'),
('ee-5', 'Electrical Engineering');
-- Perform inner join
SELECT Employee.Name AS EmployeeName, Department.Name AS DepartmentName
FROM Employee
INNER JOIN Department ON Employee.Department_id = Department.Department_id;
-- Perform left join
SELECT Employee.EmployeeID, Employee.Name AS EmployeeName, Department.Name AS
DepartmentName
FROM Employee
LEFT JOIN Department ON Employee.Department_id = Department.Department_id;
-- Perform natural join (not recommended, but for demonstration)
SELECT Employee.Name AS EmployeeName, Employee.Department_id, Department.Name
AS DepartmentName
FROM Employee
NATURAL JOIN Department;
Output :-
Inner join

Outer join - Left join

Natural Join :-
 EXPERIMENT NO. :- 06

Name of Experiment :- Subqueries and set operations : Study the use of nested queries and
how to apply them in database.

Subqueries :

A subquery, also known as a nested query or inner query, is a query nested within another query.
It allows you to break down complex problems into smaller, more manageable parts. Subqueries
can be used in various clauses like SELECT, FROM, WHERE, etc.

Here's an example of a subquery in the WHERE clause:

SELECT name
FROM employees
WHERE department_id = (SELECT department_id FROM departments WHERE name =
'Sales');

In this example, the inner query (SELECT department_id FROM departments WHERE name =
'Sales') retrieves the department ID for the department named 'Sales'. This result is then used in
the outer query to find the names of employees who belong to the 'Sales' department.

Set Operations :
Set operations in SQL (Structured Query Language) are used to combine the results of two or
more SELECT queries. There are three main set operations: UNION, INTERSECT, and
EXCEPT (or MINUS, depending on the SQL dialect).

1. UNION: It combines the results of two or more SELECT statements into a single result set.
It removes duplicate rows by default.

SELECT column1 FROM table1

UNION
SELECT column1 FROM table2;

2. INTERSECT: It returns only the rows that appear in both result sets of two SELECT
statements.

SELECT column1 FROM table1

INTERSECT
SELECT column1 FROM table2;
 3. EXCEPT (or MINUS): It returns only the rows that appear in the first result set but not
in the second.

SELECT column1 FROM table1

EXCEPT
SELECT column1 FROM table2;

Example:

Let's say we have two tables, employees and managers. We want to find the names of all
employees who are not managers. We can use the EXCEPT operator for this.

SELECT name FROM employees

EXCEPT
SELECT name FROM managers;

This will give us the names of all employees who are not managers by subtracting the set of
manager names from the set of all employee names.

Example :-
-- Create employees table
CREATE TABLE employees (
employee_id INT PRIMARY KEY,
name VARCHAR(50),
department_id INT,
salary DECIMAL(10, 2)
);

-- Insert sample data into employees table

INSERT INTO employees (employee_id, name, department_id, salary)
VALUES
(1, 'John Doe', 1, 50000.00),
(2, 'Jane Smith', 2, 60000.00),
(3, 'Alice Johnson', 1, 55000.00),
(4, 'Bob Williams', 2, 62000.00),
(5, 'Charlie Brown', 3, 52000.00);
-- Create managers table
CREATE TABLE managers (
manager_id INT PRIMARY KEY,
name VARCHAR(50),
department_id INT,
salary DECIMAL(10, 2)
);

-- Insert sample data into managers table

INSERT INTO managers (manager_id, name, department_id, salary)
VALUES
(101, 'Michael Scott', 1, 75000.00),
(102, 'Leslie Knope', 2, 78000.00),
(103, 'Ron Swanson', 3, 80000.00);
--1. Subqueries:
--a. Basic Subquery: Find the names of employees whose salaries are greater than the average
salary.
SELECT name
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);
--b. Correlated Subquery: Find the names of employees who earn more than the average salary of
their department.
SELECT name
FROM employees e
WHERE salary > (
SELECT AVG(salary)
FROM employees
WHERE department_id = e.department_id
);
--2. Set Operations:
--a. UNION: Combine the names of employees and managers into a single list.
SELECT name FROM employees
UNION
SELECT name FROM managers;
--b. INTERSECT: Find the names of employees who are also managers.
SELECT name FROM employees
INTERSECT
SELECT name FROM managers;
--c. EXCEPT: Find the names of employees who are not managers.
SELECT name FROM employees
EXCEPT
SELECT name FROM managers;

Output :-

1. Subqueries:

a. Basic Subquery:

b. Correlated Subquery:

2. Set Operations:
a. UNION:

b. INTERSECT:
name
John Doe
Jane Smith

c. EXCEPT:
 EXPERIMENT NO. :- 07

Name of Experiment :- Views : Study the use of inline and external views.

VIEWS :-

A view is often referred to as a virtual table. Views are created by using the
CREATE VIEW statement. After the view has been created, you can use the
following SQL commands to refer to that view:

 SELECT
 INSERT
 INPUT
 UPDATE
 DELETE
Views can be created using CREATE
VIEW.
The syntax for the CREATE VIEW
statement is
Syntax:
CREATE VIEW <view_name> AS
SELECT columnname, columnname
FROM <table_name>
Where columnname = expression list;
Group By grouping criteria
Having predicate

Note: The Order By clause cannot be used while creating a view.

Selecting a data set from the view:

Once a view has been created, it can be queried exactly like a base table.

Syntax:
Select columnname, columnname
From viewname;
 Updating views :

Views can also be used for data manipulation(i.e. the user can perform the Insert,
Update and Delete operations).
Views on which data manipulation can be done are called Updatable Views.
When you give an updatable view name in the Update, Insert or Delete SQL
statement, modifications to data will be passed to the underlying table.

Views defined from single table

 If the user want to Insert records with the help of a view, then the Primary
Key column/s and all the NOT
NULL columns must be included in the view.
 The user can Update, Delete records with the help of a view even if
the Primary Key column and NOT
NULL column/s are excluded from the view definition.

Views defined from multiple tables(Which have no Referencing clause)

 In this case even though the Primary Key column/s as well as NOT NULL
columns are included in the View definition the view’s behavior will be as
follows:

 The Insert, Update or Delete operation is not allowed. If attempted the

Oracle displays an error message.

Views defined from multiple tables(Which have been created with a

Referencing clause)

In this case even though the Primary Key and NOT NULL columns are included in
the View definition the view’s behavior will be as follows:
• An Insert operation is not allowed.
• The Delete or Modify do not affect the Master Table.
• The view can be used to Modify the columns of the detail table included in the
view.
• If a Delete operation is executed on the view, the corresponding records from
the detail table will be deleted.

Destroying a view:

The drop view command is used to remove a view from the

database. Syntax:

Drop View viewname;

Example :-

CREATE TABLE Employees (

EmployeeID INT PRIMARY KEY,

Name VARCHAR(50),

Department VARCHAR(50),

Salary DECIMAL(10, 2)

);

INSERT INTO Employees (EmployeeID, Name, Department, Salary)

VALUES (1, 'piyush', 'IT', 50000.00),

(2, 'bonika', 'HR', 45000.00),

(3, 'prakash', 'Finance', 60000.00);

CREATE TABLE Departments (

DepartmentID INT PRIMARY KEY,

DepartmentName VARCHAR(50) );

INSERT INTO Departments (DepartmentID, DepartmentName)

VALUES (1, 'IT'),

(2, 'HR'),

(3, 'Finance');

SELECT Name, Department

FROM (

SELECT Name, Department

FROM Employees

WHERE Salary > 45000 ) AS HighEarners;

CREATE VIEW HighSalaryEmployees AS

SELECT Name, Department

FROM Employees

WHERE Salary > 45000;

SELECT * FROM HighSalaryEmployees;

Output :-
 EXPERIMENT NO 8

Name of Experiment - Introduction to PL/ SQL : Pl/ SQL block, PL/ SQL statements, if else
statements, looping statements.

A procedure has a header and a body. The header consists of the name of the procedure
and the parameters or variables passed to the procedure. The body consists or
declaration section, execution section and exception section similar to a general PL/SQL
Block. A procedure is similar to an anonymous PL/SQL Block but it is named for
repeated usage.
We can pass parameters to procedures in three ways.
1) IN-parameters
2) OUT-parameters
3) IN OUT-parameters
A procedure may or may not return any value.
General Syntax to create a procedure is:
CREATE [OR REPLACE] PROCEDURE [schema.]procedure name
(argument {IN, OUT, IN OUT} data type,…){Is, AS}
variable declaration;
constant declaration;
BEGIN
PL/SQL subprogram body;
EXCEPTION
Exception PL/SQL block;
END;
Keywords and Parameters:
REPLACE – recreates the procedure if it already exists.
schema – is the schema to contain the procedure. The Oracle engine takes the default
schema to be the current schema, if it is omitted.
argument – is the name of an argument to the procedure. Parantheses can be omitted if
no arguments are present.
IN – specifies that a value for the argument must be specified when calling the
procedure.
OUT – specifies that the procedure passes a value for this argument back to its calling
environment after execution.
IN OUT – specifies tha a value for the argument must be specified when calling the
procedure and that the procedure passes a value for this argument back to its calling
environment after execution. By default it takes IN.
Data type – is the data type of an argument.
PL/SQL subprogram body is the definition of procedure consisting of PL/SQL
statements.
IS - marks the beginning of the body of the procedure and is similar to DECLARE in
anonymous PL/SQL Blocks. The code between IS and BEGIN forms the Declaration
section.
The syntax within the brackets [ ] indicate they are optional. By using CREATE OR
REPLACE together the procedure is created if no other procedure with the same name
exists or the existing procedure is replaced with the current code.
The below example creates a procedure ‘employer_details’ which gives the details of
the employee.
1> CREATE OR REPLACE PROCEDURE employer_details
2> IS
3>CURSORemp_cur IS
4>SELECTfirst_name, last_name, salary FROM emp_tbl;
5>emp_recemp_cur%rowtype;
6> BEGIN
7>FORemp_rec in sales_cur
8> LOOP
9>dbms_Output.put_line(emp_cur.first_name || ' ' ||emp_cur.last_name
10> || ' ' ||emp_cur.salary);
11> END LOOP;
12>END;
13> /

Output:

(a) To generate all prime numbers below 100

SET SERVEROUTPUT ON
DECLARE
i NUMBER;
counter NUMBER;
n NUMBER;
k NUMBER;
BEGIN
i := 2;
counter := 0;
n := 50;
k := n/2;
FOR i IN 1..k LOOP
IF (n%i := 0 ) THEN
counter := 1;
END IF;
IF (counter := 0) THEN
DBMS_OUTPUT.PUT_LINE(n||' is prime number');
END IF;
END LOOP;
END;

(b) Write a PL/SQL Program to display the number in Reverse Order

declare
a number;
rev number;
d number;
begin
a:=&a;
rev:=0;
while a>0
loop
d:=mod(a,10);
rev:=(rev*10)+d;
a:=trunc(a/10);
end loop;
dbms_output.put_line('no is'|| rev);
end;
/
OUTPUT:
SQL> @ REVERSE2.sql
Enter value for a: 536
old 6: a:=&a;
new 6: a:=536;
no is 635

PL/SQL procedure successfully completed.

pl/sql program to generate reverse for given number.

Procedure:

declare
n number(4):=&n;
s number(4):=0;
r number(4);
begin
while n>0
loop
r:=mod(n,10);
s:=(s*10)+r;
n:=trunc(n/10);
end loop;
dbms_output.put_line(‘the reverse number is:’);
dbms_output.put_line(s);
end;
/
Execution:
SQL> @e:\plsql\rev.sql
Enter value for n: 457
old 2: n number(4):=&n;
new 2: n number(4):=457;

the reverse number is:

754

PL/SQL procedure successfully completed.

Conclusion:
apl/sql program is successfully executed to generate reverse number for given number.

(c) Create a procedure to demonstrate IN, OUT and INOUT parameters

1. IN

mysql> CREATE PROCEDURE in_2(IN value INT )BEGIN SELECT value; SET
value =100;SE
 LECT value;END//
Query OK, 0 rows affected (0.00 sec)

mysql> SET @s =9//

Query OK, 0 rows affected (0.00 sec)

mysql> CALL in_2(@s)//

+ +
| value |
+ +
| 9|
+ +
1 row in set (0.00 sec)

+ +
| value |
+ +
| 100 |
+ +
1 row in set (0.00 sec)

mysql> SELECT @s;

-> //
+------+
| @s |
+------+
| 9|
+ +
1 row in set (0.00 sec)
2.OUT

mysql> CREATE PROCEDURE in_3(OUT value INT)

-> SET value=100//
Query OK, 0 rows affected (0.00 sec)

mysql> SET @x=56//

Query OK, 0 rows affected (0.00 sec)

mysql> CALL in_3(@x)//

Query OK, 0 rows affected (0.00 sec)
mysql> SELECT @x//
+ +
| @x |
+------+
| 100 |
+------+
1 row in set (0.00 sec)
 EXPERIMENT NO 9

Name of Experiment - Cursor : Study the use of cursor and exceptions.

What are Cursors?

A cursor is a temporary work area created in the system memory when a SQL statement
is executed. A cursor contains information on a select statement and the rows of data
accessed by it. This temporary work area is used to store the data retrieved from the
database, and manipulate this data. A cursor can hold more than one row, but can
process only one row at a time. The set of rows the cursor holds is called the active set.
There are two types of cursors in SQL:

Implicit cursors:
These are created by default when DML statements like, INSERT, UPDATE, and
DELETE statements are executed. They are also created when a SELECT statement that
returns just one row is executed.

Explicit cursors:
They must be created when you are executing a SELECT statement that returns more
than one row. Even though the cursor stores multiple records, only one record can be
processed at a time, which is called as current row. When you fetch a row the current
row position moves to next row.
Both implicit and explicit cursors have the same functionality, but they differ in the way
they are accessed.

General Syntax for using FOR LOOP:

FOR record_name IN cusror_name
LOOP
process the row...
END LOOP;
Let’s use the above example to learn how to use for loops in cursors.
1> DECLARE
2>CURSORemp_cur IS
3>SELECTfirst_name, last_name, salary FROM emp_tbl;
4>emp_recemp_cur%rowtype;
5> BEGIN
6>FORemp_rec in sales_cur
7> LOOP
8>dbms_Output.put_line(emp_cur.first_name || ' ' ||emp_cur.last_name
9> || ' ' ||emp_cur.salary);
10> END LOOP;
11>END;
12> /
(a) Create a cursor, which displays all employee numbers and names from the EMP
table.
CREATE TABLE employees (
employee_id INTEGER PRIMARY KEY,
first_name VARCHAR(25),
last_name VARCHAR(25),
email VARCHAR(25),
phone_number VARCHAR(15),
salary INTEGER
);

INSERT INTO employees (employee_id, first_name, last_name, email, phone_number, salary)

VALUES
(1, 'John', 'Doe', 'john.doe@example.com', '555-1234', 50000),
(2, 'Jane', 'Smith', 'jane.smith@example.com', '555-5678', 60000),
(3, 'Alice', 'Johnson', 'alice.johnson@example.com', '555-9012', 70000);

DECLARE @id INT

DECLARE @first_name VARCHAR(25)
DECLARE @last_name VARCHAR(25)
DECLARE cursor_name CURSOR FOR
SELECT employee_id, first_name + ' ' + last_name AS name FROM employees
OPEN cursor_name
FETCH NEXT FROM cursor_name INTO @id, @name
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT 'ID: ' + CAST(@id AS VARCHAR) + ', Name: ' + @name
FETCH NEXT FROM cursor_name INTO @id, @name
END
CLOSE cursor_name
DEALLOCATE cursor_name
Output:
 (b) Create a cursor, which update the salaries of all employees as per the given data.
SET serveroutput ON;
DECLARE
CURSOR cur_emp IS
SELECT * FROM employees WHERE department_id = 40 OR department_id = 70;
rec_emp cur_emp%rowtype;
BEGIN
OPEN cur_emp;
LOOP
FETCH cur_emp INTO rec_emp;
EXIT WHEN cur_emp%NOTFOUND;
DBMS_OUTPUT.PUT_LINE('Employee ID: ' || rec_emp.employee_id || ', Name: ' ||
rec_emp.first_name || ' ' || rec_emp.last_name);
END LOOP;
CLOSE cur_emp;
END
/

Output :-
 EXPERIMENT NO – 10

Name of Experiment - Functions and procedures : Study the use of functions and procedures in
PL/ SQL programs.

Stored Procedures
What is a Stored Procedure?
A stored procedure or in simple a proc is a named PL/SQL block which performs one
or more specific task.

How to execute a Stored Procedure?

There are two ways to execute a procedure.
1) From the SQL prompt.
EXECUTE [or EXEC] procedure_name;
2) Within another procedure – simply use the procedure name.
procedure_name;
NOTE: In the examples given above, we are using backward slash ‘/’ at the end of the
program. This indicates the oracle engine that the PL/SQL program has ended and it can
begin processing the statements.
(a) Find the greatest among three numbers.

Declare
a number;
b number;
c number;
Begin
dbms_output.put_line('Enter a:');
a:=&a;
dbms_output.put_line('Enter b:');
b:=&b;
dbms_output.put_line('Enter c:');
c:=&C;
if (a>b) and (a>c)
then
dbms_output.put_line('A is GREATEST'||A);
elsif (b>a) and (b>c)
then
dbms_output.put_line('B is GREATEST'||B);
else
dbms_output.put_line('C is GREATEST'||C);
end if;
End;

(b) To check given number is Armstrong number or not.

DECLARE
n number(3);
s number(3):=0;
t number(3);
BEGIN
n:=&n;
t:=n;
while t>0 loop
s:=s+power((t mod 10),3);
t:=trunc(t/10);
end loop;

if(s=n) then
dbms_output.put_line('The Given Number ' || n || ' is an Armstrong Number');
else
dbms_output.put_line('The Given Number ' || n || ' is Not an Armstrong Number');
end if;
END;

OUTPUT:

NOTE: We used SQL Developer and Oracle XE to Execute this Program.

The Given Number 153 is an Armstrong Number.

 EXPERIMENT NO - 11

Name of Experiment - Trigger : Study the use of triggers to enforce constraints.

Triggers

Triggers are simply stored procedures that are ran automatically by the database
whenever some event (usually a table update) happens.

Triggers are basically PL/SQL procedures that are associated with tables, and are called
whenever a certain modification (event) occurs. The modification statements may
include INSERT, UPDATE, and DELETE.

The general structure of triggers is:

CREATE [OR REPLACE] TRIGGER [schema] trigger_name

{BEFORE AFTER}
{ DELETE, INSERT OR UPDATE [OF COLUMN, ..] }
ON [schema.] tablename
[REFRENCING {OLD AS old, NEW AS new}]
[FOR EACH ROW [WHEN (condition)]]
DECLARE
variable declarations;
constantdecalarations;
BEGIN
PL/SQL subprogram body;
EXCEPTION
exception PL/SQL block;

END;

where:

Example:
Sample Table to be Triggered
Before we begin playing with triggers, let’s create a simple table with which we can
experiment:

CREATE TABLE PERSON (

ID INT,
NAME VARCHAR(30),
DOB DATE,
PRIMARY KEY(ID)
);

The above creates a PERSON table with an ID, a NAME and a DOB columns (fields).
Whatever triggers we define in these notes will relate to this table.

Viewing Triggers

You can see all your user defined triggers by doing a select statement on
USER_TRIGGERS.

For example:
SELECT TRIGGER_NAME FROM USER_TRIGGERS;

Which produces the names of all triggers. You can also select more columns to get more
detailed trigger information.

Dropping Triggers

You can DROP triggers just like anything. The general format would be something like:

DROP TRIGGER trigger_name;

(a) Create a trigger before/after update on employee table for each row/statement

SET serveroutput ON
DECLARE

CURSOR cur_emp
IS\

SELECT * FROM employees WHERE department_id = 40 OR department_id = 70;

rec_empcur_emp%rowtype;
BEGIN
OPEN cur_emp;
LOOP
FETCH cur_emp INTO rec_emp;
IF rec_emp.department_id = 40 THEN
UPDATE employees
SET salary = salary + (salary * 0.1)
WHERE employee_id = rec_emp.employee_id;
elsifrec_emp.department_id = 70 THEN
UPDATE employees
SET salary = salary + (salary * 0.15)
WHERE employee_id = rec_emp.employee_id;
END IF;
EXIT
WHEN cur_emp%notfound;
END LOOP;
CLOSE cur_emp;
END;
/
Output:

(b) Create a trigger before/after delete on employee table for each row/statement

creates a DML trigger that uses conditional predicates to determine which of its four
possible triggering statements fired it.

Example - Trigger that Uses Conditional Predicates to Detect Triggering Statement

CREATE OR REPLACE TRIGGER t

BEFORE
INSERT OR
UPDATE OF salary OR UPDATE
OF department_id OR DELETE
ON employees
BEGIN
CASE
WHEN INSERTING THEN
DBMS_OUTPUT.PUT_LINE('Inserting');
WHEN UPDATING('salary') THEN
DBMS_OUTPUT.PUT_LINE('Updating salary');
WHEN UPDATING('department_id') THEN
 DBMS_OUTPUT.PUT_LINE('Updating department ID');
WHEN DELETING THEN
DBMS_OUTPUT.PUT_LINE('Deleting');
END CASE;
END;
/
Output:

(c) Create a trigger before/after insert on employee table for each row/statement.

CREATE TRIGGER <Trigger name>

{BEFORE | AFTER} <trigger event> ON <Table name>
[ REFERENCING<old or new values alias list> ]
<triggered action>

<trigger event> ::=

INSERT |
DELETE |
UPDATE [ OF<trigger Column list> ]

<trigger Column list> ::= <Column name> [ {,<Column name>} ... ]

<old or new values alias list> ::=

<old or new values alias>...

<old or new values alias> ::=

OLD [ ROW ] [ AS ] old values <Correlation name> |
NEW [ ROW ] [ AS ] new values <Correlation name> |
OLD TABLE [ AS ] <old values Table alias> |
NEW TABLE [ AS ] <new values Table alias>

<old values Table alias> ::= <identifier>

<new values Table alias> ::= <identifier>

<triggered action> ::=

[ FOR EACH {ROW | STATEMENT} ] [ WHEN (search condition) ]
<triggered SQL statement>

<triggered SQL statement> ::=

SQL statement |
 BEGIN ATOMIC {SQL statement;}... END

creates a log table and a trigger that inserts a row in the log table after any UPDATE
statement affects the SALARY column of the EMPLOYEES table, and then updates
EMPLOYEES.SALARY and shows the log table.

CREATE TABLE employees (

employee_id INTEGER PRIMARY KEY,
first_name VARCHAR(25),
last_name VARCHAR(25)
);

CREATE TABLE salaries (

salary_id INTEGER PRIMARY KEY,
employee_id INTEGER,
salary_amount INTEGER,
CONSTRAINT fk_employee FOREIGN KEY (employee_id) REFERENCES
employees(employee_id)
);

CREATE OR REPLACE TRIGGER check_employee_exists

BEFORE INSERT ON salaries
FOR EACH ROW
DECLARE
v_count INTEGER;
BEGIN
SELECT COUNT(*) INTO v_count
FROM employees
WHERE employee_id = :NEW.employee_id;

IF v_count = 0 THEN
RAISE_APPLICATION_ERROR(-20001, 'Employee does not exist');
END IF;
END;
/
BEGIN
INSERT INTO salaries (salary_id, employee_id, salary_amount) VALUES (1, 1001,
50000);
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Error: ' || SQLERRM);
END;
/
Output :-