Name of Student

Roll No DOP DOS Signature Remark

EXPERIMENT 3

Aim - Create a database using Data Definition Language (DDL)

Theory-
DBMS is a database management system is the software system that allows users to define,
create and maintain a database and provides controlled access to the data.
A Database Management System (DBMS) is basically a collection of programs that enables
users to store, modify, and extract information from a database as per the requirements.
DBMS is an intermediate layer between programs and the data. Programs access the DBMS,
which then accesses the data. There are different types of DBMS ranging from small systems
that run on personal computers to huge systems that run on mainframes.

To create a database and to retrieve the information from the database using SQL
queries.
SQL statements are divided into two major categories: data definition language (DDL) and data
manipulation language (DML).

Data Definition Language (DDL) statements are used to define the database structure or
schema. Some examples:

DDL COMMANDS –
1. CREATE TABLE-
The create table statement is used to create a new table. The CREATE TABLE statement
defines a table. The definition must include its name and the names and attributes of its
columns. The definition can include other attributes of the table, such as its primary key or
check constraints. This statement comes under the DDL statement.
Syntax:
SQL> create table tablename
( Column1 datatype [default exp],
Column2 datatype [default exp] ,
 Column3 datatype [default exp] ,
);
To create a new table, enter the keywords create table followed by the table name, followed
by an open parenthesis, followed by the first column name, followed by the data type for that
column, followed by any optional constraints, and followed by a closing parenthesis. It is
important to make sure you use an open parenthesis before the beginning table and a closing
parenthesis after the end of the last column definition. Make sure you separate each column
definition with a comma. All SQL statements should end with a";".
Example:
SQL> create table employee
(Name varchar2 (15),
Eid number (6),
Age number (3),
City varchar2 (30),
State varchar2 (20)
);
Table created.
If you want to see the structure of the table then use the following command on SQL prompt.
SQL > DESC employee;
As you can see that this command displays the entire attribute names with their data type and
if constraints are imposed then their detail also.
2. SQL ALTER
After the table( s) are created and begin to be used, requirements are likely to occur which
requires modifications in the structure of the table, such as adding new columns to the table,
modifying and existing column's definition etc. So these modifications can be performed
using the ALTER table DDL statement. Tills statement changes the structure of the table and
not its contents. There are many types of modifications that can be made to the structure of
the tables. Using ALTER TABLE statement, you can make modifications such as
Add or drop one or more columns to i from existing table.
Increase or decrease the width of the existing column.
Change an existing column from mandatory to optional or vice versa.
● ALTER Table- Add Column
The syntax is ALTER TABLE <tablename>
ADD (column_specification I , ....
column_specification );
 Where tablename corresponds to the name of the table, column_specification corresponds to
the valid specification for a column (columnname and datatype)
To add address column into the INSTRUCTOR table with column width 30 and data type
varchar2 is shown below.
SQL> ALTER TABLE instructor
ADD (address VARCHAR2(30));
Table altered.
Instead of adding columns one by one we can add multiple columns in the table. To add
address and salary columns into the INSTRUCTOR table, the statement is
SQL> ALTER TABLE instructor
ADD (address VARCHAR2
(30),
Salary NUMBER (10, 2)); Table
altered.
● ALTER Table- Add Primary Key
To Add primary key constraint to existing attribute
Syntax:
ALTER TABLE tablename
Add primary key (attribute_name);
Example-
ALTER TABLE student
Add primary key (roll_no);
● ALTER table- drop column
To delete column from an existing column.
Syntax-
ALTER TABLE table_name
DROP column name(s); Example-
ALTER TABLE student
DROP column age;
● ALTER TABLE- MODIFY column
To change data type of any column or to modify its size.
Syntax-
ALTER table table_name
MODIFY column_name datatype;
 Example-
ALTER TABLE student
MODIFY name varchar(50);
● ALTER table- RENAME column
To rename existing column in a
table Syntax-
ALTER TABLE table_name
RENAME old_column name to new_column_name;
Example-
ALTER TABLE student
RENAME name to stud_name;

3. DROP TABLE
The SQL DROP command is used to remove an object from the database. If you drop a table,
all the rows in the table is deleted and the table structure is removed from the database. Once
a table is dropped we cannot get it back, When a table is dropped all the references to the
table will not be valid.
Syntax:
Drop Table Tablename;
Example:
DROP TABLE Persons;

4. DDL- TRUNCATE command

The SQL TRUNCATE Command is used to remove all rows from the table. If you truncate a
table, all the rows in the table are deleted but the table structure remains in the database
Syntax:
TRUNCATE TABLE table_name

5. RENAME TABLE
The SQL RENAME Command used to Change the name of a table.
Syntax:
RENAME TABLE old_table_name to new_table_name
Example:
 RENAME TABLE Stud to Student

Exercise-
Consider following schema and perform DDL commands on the given schema
Create table SUPPLIER with following structure
● S_id int
● S_name varchar2(20)
● item_supplied varchar2(30)
● item_code int
● item_price number(6)
Write SQL queries
1. Add new column pincode and city.

2. Change the size of S_name to 100

3. Rename the table as Supplierinfo

 4. rename column item_code to i_code

5. delete column pincode

6. drop table

Conclusion: Thus the creation of database and the SQL queries to retrieve information from
the database has been implemented and the output was verified using create, alter, drop
commands are used as DDL commands.
 Name of Student Aaditya Pandhare

Roll No DOP DOS Signature Remark

49

EXPERIMENT 4

Aim: Create a database using Data Definition Language (DDL) and apply
integrity constraints Theory:
Constraints are used to limit the type of data that can go into a [Link] can be
specified when a table is created (with the CREATE TABLE statement) or after the table is
created (with the ALTER TABLE statement).
We will focus on the following constraints:
● NOT NULL
● UNIQUE
● PRIMARY KEY
● FOREIGN KEY
● CHECK ● DEFAULT

1. NOT NULL:
The NOT NULL constraint enforces a column to NOT accept NULL [Link] NOT NULL
constraint enforces a field to always contain a value. This means that you cannot insert a new
record, or update a record without adding a value to this [Link] following SQL enforces the
"P_Id" column and the "LastName" column to not accept NULL values:

CREATE TABLE Persons

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

SQL NOT NULL Constraint in ALTER TABLE

To create a NOT NULL constraint on the "City" column when the table is already created, use
the following SQL:

ALTER TABLE
Persons ADD City NOT
NULL;
2. UNIQUE:

The UNIQUE constraint uniquely identifies each record in a database [Link] UNIQUE and
PRIMARY KEY constraints both provide a guarantee for uniqueness for a column or set of
columns.A PRIMARY KEY constraint automatically has a UNIQUE constraint defined on
[Link] that you can have many UNIQUE constraints per table, but only one PRIMARY KEY
constraint per [Link] following SQL creates a UNIQUE constraint on the "P_Id" column
when the "Persons" table is created:

CREATE TABLE Persons

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

SQL UNIQUE Constraint in ALTER TABLE

To create a UNIQUE constraint on the "FirstName" column when the table is already created,
use the following SQL:

ALTER TABLE Persons

ADD FirstName UNIQUE;

3. PRIMARY KEY:

The PRIMARY KEY constraint uniquely identifies each record in a database [Link] keys must
contain unique values.A primary key column cannot contain NULL [Link] table should have a
primary key, and each table can have only ONE primary [Link] following SQL creates a PRIMARY
KEY on the "P_Id" column when the "Persons" table is created:

CREATE TABLE
Persons (
P_Id int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address
varchar(255), City
varchar(255),
);

SQL PRIMARY KEY Constraint in ALTER TABLE

To create a PRIMARY KEY constraint on the "P_Id" column when the table is already created,
use the following SQL:
ALTER TABLE Persons
ADD PRIMARY KEY(P_Id);

4. FOREIGN KEY:

A FOREIGN KEY in one table points to a PRIMARY KEY in another [Link]'s illustrate
the foreign key with an example. Look at the following two tables:

The "Persons" table:

P_Id LastName FirstName Address City

1 Hansen Ola Timoteivn 10 Sandnes

2 Svendson Tove Borgvn 23 Sandnes

3 Pettersen Kari Storgt 20 Stavanger

The "Orders" table:

O_Id OrderNo P_Id

1 77895 3

2 44678 3

3 22456 2

4 24562 1

Note that the "P_Id" column in the "Orders" table points to the "P_Id" column in the
"Persons" table. The "P_Id" column in the "Persons" table is the PRIMARY KEY in the
"Persons" table. The "P_Id" column in the "Orders" table is a FOREIGN KEY in the "Orders"
table. The FOREIGN KEY constraint is used to prevent actions that would destroy links
between tables. The FOREIGN KEY constraint also prevents that invalid data form being
inserted into the foreign key column, because it has to be one of the values contained in the
table it points to.

The following SQL creates a FOREIGN KEY on the "P_Id" column when the "Orders" table
is created:

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)
);
5. CHECK:

The CHECK constraint is used to limit the value range that can be placed in a [Link] you
define a CHECK constraint on a single column it allows only certain values for this [Link]
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.

SQL CHECK Constraint on CREATE TABLE

The following SQL creates a CHECK constraint on the "P_Id" column when the "Persons"
table is created. The CHECK constraint specifies that the column "P_Id" must only include
integers greater than 0.

CREATE TABLE
Persons (
P_Id int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address
varchar(255), City
varchar(255),
CHECK (P_Id>0)
);

SQL CHECK Constraint in ALTER TABLE

To create a CHECK constraint on the "P_Id" column when the table is already created, use the
following SQL:

ALTER TABLE
Persons ADD CHECK
(P_Id>0);

6. DEFAULT:

The DEFAULT constraint is used to insert a default value into a [Link] default value will
be added to all new records, if no other value is specified.

SQL DEFAULT Constraint on CREATE TABLE

The following SQL creates a DEFAULT constraint on the "City" column when the “Persons"
table is created:

CREATE TABLE
Persons (
P_Id int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255) DEFAULT 'Sandnes' );
SQL DEFAULT Constraint on ALTER TABLE

To create a DEFAULT constraint on the "City" column when the table is already reated, use the
following SQL:
ALTER TABLE Persons
ALTER City SET DEFAULT 'SANDNES';

To DROP a DEFAULT Constraint

To drop a DEFAULT constraint, use the following SQL:

ALTER TABLE Persons

ALTER City DROP DEFAULT;

● NOT NULL
● UNIQUE
● PRIMARY KEY
● FOREIGN KEY
● CHECK
● DEFAULT

EXERCISE
1. Consider following schema and perform DDL commands on the given schema
Create table supplier with following structure
● S_id int
● S_name varchar(20)
● itemsupplied varchar(30)
● itemcode int
● itemprice number(6)
● pincode number(6)
● city varchar(30)
Write SQL queries
1. Make S_id as PRIMARY KEY

2. Add CHECKconstraint to itemprice column (itemprice>2000)

3. Drop CHECKconstraint of itemprice

4. Add NOT NULL constraint to itemcode

5. Add DEFAULT city ‘India’

6. Add UNIQUE constraint to S_name.

7. DROP primary key

2. Write SQL DDL commands to create following

schema Employee(e_name, doj, city, street)
Company(c_name, city)
Works(e_name, c_name, salary)
Conclusion: Thus NOT NULL, UNIQUE, PRIMARY KEY, FOREIGN KEY, CHECK,
DEFAULT are SQL constraints are implemented and the output was verified.
Name of Student

Roll No DOP DOS Signature Remark

20

EXPERIMENT 5

Aim- Implementation of DML commands.

Theory-
SQL (Structured Query Language) is born as a result of the mathematical work of Codd, who
founded the work of relational databases, three types of manipulations on the database:
1 The maintenance of tables: create, delete, and modify the table structure.
2 The manipulation of databases: Selecting, modifying, deleting records.
3 The management of access rights to tables: Data control: access rights, commit the changes.

Data Manipulation Language (DML) statements are used for managing data within schema
objects. Some examples:

1. SELECT-
A select statement is used to select information from one or more tables. SQL statements
can be on one or more lines. SQL is not case sensitive. SELECT is the same as select.
SQL SELECT Syntax
SELECT * FROM table_name
If all columns of a table should be selected, the asterisk symbol * can be used to denote all
attributes. The query retrieves all tuples with all columns from the table STUD. To
illustrate the usage of the SELECT command we are going to use the STUD table:

2. SQL INSERT INTO -

The INSERT statement is used to add new row to a table. To insert new row(s) into a table,
the table must be in your own schema or you must have INSERT privilege on the table.
Only one row is inserted at a time with this syntax.
The Syntax is
 INSERT INTO <table_name> values
(col1, col2,... coln), (col1, col2,...
coln),
:
(col1, col2,... coln);
In order to insert data, let us first create an STUD table usingCREATE TABLE command.
Example:
CREATION OF TABLE
create table STUD (sname
varchar(30), sid varchar2(10),
sage int, sarea varchar(20),
sdept varchar(20)
);

Table created.
INSERTION OF VALUES INTO THE TABLE insert
into stud values
('ashwin',101,19,'anna nagar', 'aeronautical'),
('bhavesh',102,18,'nungambakkam','marine'),
('pruthvik',103,20,'anna nagar','aerospace'),
('charith',104,20,'kilpauk','mechanical');
select * from stud;

SNAME SID SAGE SAREA SDEPT

ashwin 101 19 anna nagar aeronautical

bhavesh 102 18 nungambakkam marine
pruthvik 103 20 anna nagar aerospace
charith 104 20 kilpauk mechanical

3. UPDATE-
Quite often it is required to make changes or modifications in the records of the table, so
in order to make these changes, the UPDATE statement is used. With this statement, the
user can modify the existing data stored in the table. It can update zero or more rows in a
table. To update rows in table, it must be in your own schema or you must have update
privilege on the table.

The syntax is
 UPDATE <tablename>
SET <columnnamel> = <expression> [, <column narne2>= <expression> ]
. [,<columnnameN>=<expression> ]
[WHERE condition];
Suppose that we insert a column SALARY (Number (8, 2)) into the INSTRUCTOR table
and we want to set the salary of each instructor to be 10000. Then the update statement
will be
Update INSTRUCTOR SET
SALARY = 10000;
5 ROW UPDATED
On execution this will modify the salary of each instructor to 10000. Now suppose that we
want to increase the salary of each INSTRUCTOR with a post'READER' by 5000. The
statement will be
Update INSTRUCTOR
SALARY = SALARY + 5000;
WHERE POST = ‘READER’;
Row Updated

4. SQL DELETE
The DELETE statement is used to delete rows in a table. To do so, we can use the
DELETE FROM command. An SQL DELETE statement removes one or more records
from a table. A subset may be defined for deletion using a condition, otherwise all records
are removed.
Delete from <table> [where <condition>];
If the where clause is omitted, all tuples are deleted from the table. An alternative
command for deleting all tuples from a table is the truncate table <table> command.
However, in this case, the deletions cannot be undone.
SQL > DELETE FROM stu
WHERE ROLL=2214;
1 row deleted.
• Now we will delete all the records from the stu table.
SQL > DELETE FROM stu;
OR
SQL > DELETE stu;
 RENAMING THE TABLE ‘STUD’
SQL> rename stud to studs; Table renamed.

Logical Operators
There are three Logical Operators namely, AND, OR, and NOT. These operators compare two
conditions at a time to determine whether a row can be selected for the output.
When retrieving data using a SELECT statement, you can use logical operators in the WHERE
clause, which allows you to combine more than one condition.

1. OR Logical Operator:

If you want to select rows that satisfy at least one of the given conditions, you can use the
logical operator, OR.
For example: if you want to find the names of students who are studying either Maths or
Science, the query would be like

SELECT first_name, last_name, subject

FROM student_details
WHERE subject = 'Maths' OR subject = 'Science';

2. AND Logical Operator:

If you want to select rows that must satisfy all the given conditions, you can use the logical
operator, AND.

For Example: To find the names of the students between the age 10 to 15 years, the query
would be like:

SELECT first_name, last_name, age

FROM student_details
WHERE age >= 10 AND age <= 15;

3. NOT Logical Operator:

If you want to find rows that do not satisfy a condition, you can use the logical operator,
NOT. NOT results in the reverse of a condition. That is, if a condition is satisfied, then the
row is not returned.

For example: If you want to find out the names of the students who do not play football,
the query would be like:
 SELECT first_name, last_name, games
FROM student_details
WHERE NOT games = ‘Football’;
SQL Special operators
1. BETWEEN- It selects values within a given range.
Syntax:
SELECT colname(s) from tablename
WHERE column BETWEEN Value1 AND Value2;

For Example:
SELECT * from student
WHERE age BETWEEN 15 AND 25
2. IN- To specify multiple values in a WHERE Clause
Syntax:
SELECT colname(s) from tablename WHERE column
IN(Value1, Value2, …Valuen)
For Example:
SELECT * from student
WHERE city IN(‘Mumbai’, ‘Pune’, ‘Hydrabad’);
3. Like -
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
For Example-
1. SELECT * FROM Student WHERE Name like ‘%hit%;

2. SELECT * FROM Student

WHERE Name like ‘_r%;
Q.1 Create table student with following structure
● S_id int
● S_name varchar(20)
● S_city varchar(30)
● S_dept varchar(8)
● S_age int
● S_percentage numeric(2,2)
● S_phone varchar(10)
1. Insert 8 records in table

2. Display S_id, S_name, S_address from table Student.

3. Update the city to ‘Mumbai’ whose S_id is 4

4. Delete the record of student whose S_id is 6

5. Display the records whose age greater than 18 and less than 25
6. Increase the percentage of all students by 5% whose city is “Mumbai”

7. Display the records of all students having ‘a’ as second letter in their name.

8. Display the details of students whose age is between 18 to 25.

Conclusion: Thus the creation of the database and the SQL queries to retrieve information from
the database has been implemented and the output was verified using SELECT, INSERT,
UPDATE, DELETE commands are used as DML commands. Similarly AND, OR, NOT are the
logical operators in SQL are implemented and the output was verified.
Name of Student

Roll No DOP DOS Signature Remark

20

Experiment No: 06
Aim: Implementation of Aggregate Functions, GROUP BY and HAVING Clause
Theory:
SQL has many built-in functions for performing calculations on data. SQL aggregate functions
return a single value, calculated from values in a column.
Useful aggregate functions:
● AVG() - Returns the average value
● COUNT() - Returns the number of rows
● FIRST() - Returns the first value
● LAST() - Returns the last value
● MAX() - Returns the largest value
● MIN() - Returns the smallest value ● SUM() - Returns the sum

1. AVG() –
The AVG() function returns the average value of a numeric column.
Syntax:
 Now we want to find the average value of the "OrderPrice" [Link] use the following
SQL statement:

SELECT AVG(OrderPrice) AS Order Average FROM Orders;

2. COUNT() –
The COUNT() function returns the number of rows that matches a specified [Link]
COUNT(column_name) function returns the number of values (NULL values will not be
counted) of the specified column:

Syntax:
SELECT COUNT(*) FROM table_name;

Example
The COUNT(*) function returns the number of records in a table:

SELECT COUNT(*) FROM table_name;

Now we want to count the number of orders from "Customer Nilsen".We use the following
SQL statement:
SELECT COUNT(Customer) AS CustomerNilsen FROM Orders
WHERE Customer='Nilsen';

3. FIRST() –
The FIRST() function returns the first value of the selected column.
Syntax:
SELECT FIRST (column_name) FROM table_name;
 Now we want to find the first value of the "OrderPrice" [Link] use the following SQL
statement:

SELECT FIRST(OrderPrice) AS FirstOrderPrice FROM Orders;

4. LAST() –
The LAST() function returns the last value of the selected column.
Syntax:
SELECT LAST(column_name) FROM table_name; Example:

O_Id OrderDate Customer

2008/11/12 1000 Hansen
2008/10/23 1600 Nilsen
2008/09/02 700 Hansen

2008/09/03 300 Hansen

2008/08/30 2000 Jensen

2008/10/04 100 Nilsen

1
2
3
 4
5
6

Now we want to find the last value of the "OrderPrice" [Link] use the following SQL
statement:

SELECT LAST(OrderPrice) AS LastOrderPrice FROM Orders;

MAX() –
The MAX() function returns the largest value of the selected column.

Syntax:
SELECT MAX(column_name) FROM table_name;

Example:
We have the following "Orders" table:
Now we want to find the largest value of the "OrderPrice" column.
We use the following SQL statement:

O_Id OrderDate OrderPrice Customer

1 2008/11/12 1000 Hansen

2 2008/10/23 1600 Nilsen

3 2008/09/02 700 Hansen

4 2008/09/03 300 Hansen

5 2008/08/30 2000 Jensen

6 2008/10/04 100 Nilsen

SELECT MAX(OrderPrice) AS LargestOrderPrice FROM Orders;

5. MIN() –

The MIN() function returns the smallest value of the selected column.
Syntax:
SELECT MIN(column_name) FROM table_name; Example:

O_Id OrderDate OrderPrice Customer

2008/11/12 Hansen
2008/10/23 1600 Nilsen

2008/09/02 700 Hansen

2008/09/03 300 Hansen

2008/08/30 2000 Jensen

2008/10/04 100 Nilsen

6
Now we want to find the smallest value of the "OrderPrice" [Link] use the following
SQL statement:
SELECT MIN(OrderPrice) AS SmallestOrderPrice FROM Orders; 6.
SUM() –

The SUM() function returns the total sum of a numeric column.

Syntax:
SELECT SUM(column_name) FROM table_name; Example:
 O_Id OrderDate Customer

2008/11/12 000 Hansen

2008/10/23 600 Nilsen
2008/09/02 00 Hansen
2008/09/03 00 Hansen
2008/08/30 000 Jensen
2008/10/04 00 Nilsen

2
3
4
5
6
Now we want to find the sum of all "OrderPrice" fields".We use the following SQL
statement:
SELECT SUM(OrderPrice) AS OrderTotal FROM Orders;

● ORDER BY, GROUP BY and HAVING Clauses

● ORDER BY- Specifies an order in which to return the row

ORDER BY clause allows to specify order for output. It is used with the SELECT statement.
By default data is retrieved in Ascending order.
Syntax:
SELECT column1,column2…….
FROM table_name
ORDER BY column(s);

Example
Select * from Emp
Order by name;
● GROUP BY-groups rows that share a property so that the aggregate function can be applied to
each group. Group by statement is used for organizing similar data into groups. It is often used
with aggregate functions (count, max, min, sum, avg)

Syntax:
SELECT Col_name(s), aggregate function (col_name)
From table_name
Group by col_name(s)
Order by col_name;
Example select city, count(*)
from
Emp
group by city;

● HAVING- It selects among the groups defined by GROUP BY Clause by specifying conditions.
This clause is used with the GROUP BY clause.
Syntax:
SELECT col_name(s), aggregate_function(col_name)
FROM table_name
GROUP BY col_name
HAVING condition;

Example
SELECT city, sum(salary)
FROM Emp
GROUP BY city
HAVING sum(salary)>5000

Output:
Exercise:

1. Write SQL query to prepare the following table.

Q.2 Write SQL query to find the sum of fees paid by students from each department

Q.3 Write SQL query to find the max fees paid by students from each department.

Q.4 Write SQL query to find the department for which total fees paid >100000.
 Conclusion:
Thus we have implemented that AVG(), COUNT(), FIRST(), LAST(), MAX(), MIN(),SUM() are
aggregate functions of SQL and order by group by and having clauses and the output was verified.
 Name of Student

Roll No DOP DOS Signature Remark

EXPERIMENT 7

Aim: Implementation of Complex and Nested queries.

Theory:
Sub query or Inner query or Nested query is a query in a query. A subquery is usually added
in the WHERE Clause of the sql statement. Most of the time, a subquery is used when you
know how to search for a value using a SELECT statement, but do not know the exact value.

Sub queries are an alternate way of returning data from multiple [Link] queries can
be used with the following sql statements along with the comparison operators like: =,
<, >, >=, <= etc.

The following are the rules to use subqueries:

○ Subqueries should always be used in parentheses.
○ If the main query does not have multiple columns for subquery, then a
subquery can have only one column in the SELECT command.
○ We can use various comparison operators with the subquery, such as >, <, =,
IN, ANY, SOME, and ALL. A multiple-row operator is very useful when the
subquery returns more than one row.
○ We cannot use the ORDER BY clause in a subquery, although it can be used
inside the main query.

Syntax:
SELECT column_list (s) FROM table_name
WHERE column_name OPERATOR
(SELECT column_list (s) FROM table_name WHERE condition )

Example:

1) Usually, a subquery should return only one record, but sometimes it can also return
multiple records when used with operators like IN, NOT IN in the where clause. The
query would be like,

SELECT first_name, last_name, subject

FROM student_details
WHERE games NOT IN ('Cricket', 'Football');
The output would be similar to:

First_name last_name subject

------------- ------------- ----------
Shekar Gowda Badminton
Priya Chandra Chess
2) Let's consider the student details table which we have used earlier. If you know
the name of the students who are studying science subject, you can get their ids by using
this query below,

SELECT id, first_name

FROM student_details
WHERE first_name IN ('Rahul', 'Stephen');

but, if you do not know their names, then to get their id's you need to write the query in this
manner,

SELECT id, first_name

FROM student_details
WHERE first_name IN (SELECT first_name
FROM student_details
WHERE subject= 'Science');
Output:
id
first_name
-------- -------------
100 Rahul
102 Stephen
In the above sql statement, first the inner query is processed first and then the outer query is
Processed.

3) A sub query can be used in the SELECT statement as follows. Let’s use the product
and order_items table defined in the sql_joins section.

select p.product_name, p.supplier_name, (select order_id from order_items where

product_id = 101) as order_id from product p where p.product_id = 101;

product_name supplier_name order_id

Television Onida 5103

Correlated Sub query:

A query is called correlated sub query when both the inner query and the outer query are
interdependent. For every row processed by the inner query, the outer query is processed as
well. The inner query depends on the outer query before it can be processed.
SELECT p.product_name FROM product p
WHERE p.product_id = (SELECT o.product_id FROM order_items o
WHERE o.product_id = p.product_id);

Questions

1 . Consider the following schemas

Worker(Wroker_id, first_name, last_name, salary, Date of Joining,Department)

 Bonus(Wroker_id,bonus_date,bonus)
Designation(Worker_id,designation)

1. Write SQL Query to Fetch Worker Names With Salaries >= 50000 And <= 100000.

2. Write SQL Query to Show the Second Highest Salary From A Table.
3. Write SQL Query to Fetch the Departments that have less than five people In It.

4. Write SQL Query to Print the name of employees having the highest salary in
each department.

5. Write An SQL Query To Fetch the Names of Workers who earn the highest salary.

2 . Consider the following schemas

employee(employee-name, street, city)

works(employee-name, company-name, salary)
company(company-name, city) manages(employee-
name, manager-name)
1. Write SQL QueryFind the names, name, city of all employees who work for ‘ABC’
and earn more than Rs. 20000.

2. Write SQL Query to give all employees of ABC company a rise of 20%.
3. Write SQL Query to find the name of the company that has the smallest payroll

4. Write SQL Query to delete the employee ‘Akash’ belonging to ABC Company.

Conclusion: Thus we have seen Nested queries, and complex queries, and we are
concluded that we can nest as many queries as we want but it is recommended not to nest
more than 16 sub queries in oracle.
 Name of Student Mohit Subhash Joshi

Roll No DOP DOS Signature Remark

20

EXPERIMENT 8

Aim: Implementation of SQL Joins.

Theory:
SQL joins are used to query data from two or more tables, based on a relationship between
certain columns in these tables.
The JOIN keyword is used in an SQL statement to query data from two or more tables, based
on a relationship between certain columns in these [Link] in a database are often
related to each other with keys.
A primary key is a column (or a combination of columns) with a unique value for each row.
Each primary key value must be unique within the table. The purpose is to bind data together,
across tables, without repeating all of the data in every table. Look at the "Persons" table:

The “Persons” table:

P_Id LastName FirstName Address City

Hansen Ola Timoteivn 10 Sandnes

1
Svendson Tove Borgvn 23 Sandnes
2
Pettersen Kari Storgt 20 Stavanger
3
Note that the "P_Id" column is the primary key in the "Persons" table. This means that no
two rows can have the same P_Id. The P_Id distinguishes two persons even if they have the
same name.

Next, we have the "Orders" table:

O_Id OrderNo P_Id

77895 3
1
44678 3
2
22456 1
3
24562 1
4
 3476 15
5 4
Note that the "O_Id" column is the primary key in the "Orders" table and that the "P_Id"
column refers to the persons in the "Persons" table without using their [Link] that the
relationship between the two tables above is the "P_Id" column.

Different SQL JOINs:

Before we continue with examples, we will list the types of JOIN you can use, and
thedifferences between them.

JOIN: Return rows when there is at least one match in both tables.

LEFT JOIN: Return all rows from the left table, even if there are no matches in the right
table

RIGHT JOIN: Return all rows from the right table, even if there are no matches in the left
table

FULL JOIN: Return rows when there is a match in one of the tables.

1 . SQL INNER JOIN:

The INNER JOIN keyword return rows when there is at least one match in both tables.

SQL INNER JOIN Syntax:

SELECT column_name(s)
FROM table_name1 INNER JOIN table_name2
ON table_name1.column_name=table_name2.column_name;

SQL INNER JOIN Example:

The “Persons” table:

P_Id LastName FirstName Address City

Hansen Ola Timoteivn 10 Sandnes

1
Svendson Tove Borgvn 23 Sandnes
2
3 Pettersen Kari Storgt 20 Stavanger

The "Orders" table:

O_Id OrderNo P_Id

7789
1 5 3
 44678
2 3
22456
3 1
24562
4 1
34764
5 15
Now we want to list all the persons with any orders.
We use the following SELECT statement:

SELECT [Link], [Link], [Link]

FROM Persons INNER JOIN Orders
ON Persons.P_Id=Orders.P_Id
ORDER BY [Link];
The INNER JOIN keyword return rows when there is at least one match in both tables. If
there are rows in "Persons" that do not have matches in "Orders", those rows will NOT be
listed.

2 . SQL LEFT JOIN:

The LEFT JOIN keyword returns all rows from the left table (table_name1), even if there are
no matches in the right table (table_name2).

SQL LEFT JOIN Syntax:

SELECT column_name(s)
FROM table_name1 LEFT JOIN table_name2
ON table_name1.column_name=table_name2.column_name;

SQL LEFT JOIN Example:

The “Persons” table:

P_Id LastName FirstName Address City

Hansen Ola Timoteivn 10 Sandnes

1
Svendson Tove Borgvn 23 Sandnes
2
Pettersen Kari Storgt 20 Stavanger
3
The "Orders" table:
O_Id OrderNo P_Id

1 77895 3

2 44678 3

3 22456 1
 4 24562 1

5 34764 15

Now we want to list all the persons and their orders - if any, from the tables [Link] use
the following SELECT statement:

SELECT [Link], [Link], [Link]

FROM Persons LEFT JOIN Orders
ON Persons.P_Id=Orders.P_Id
ORDER BY [Link];

The LEFT JOIN keyword returns all the rows from the left table (Persons), even if there are
no matches in the right table (Orders).

3 . SQL RIGHT JOIN:

The RIGHT JOIN keyword returns all the rows from the right table (table_name2), even if
there are no matches in the left table (table_name1).

SQL RIGHT JOIN Syntax:

SELECT column_name(s)
FROM table_name1 RIGHT JOIN table_name2
ON table_name1.column_name=table_name2.column_name;

SQL RIGHT JOIN Example:

The “Persons” table:

P_Id LastName FirstName Address City

1 Hansen Ola Timoteivn 10 Sandnes

2 Svendson Tove Borgvn 23 Sandnes

3 Pettersen Kari Storgt 20 Stavanger

The "Orders" table:
O_Id OrderNo P_Id

77895
1 3
44678
2 3
22456
3 1
24562
4 1
 3476
5 4 15
Now we want to list all the orders with containing persons - if any, from the tables above.
We use the following SELECT statement:

SELECT [Link], [Link], [Link]

FROM Persons RIGHT JOIN Orders ON Persons.P_Id=Orders.P_Id
ORDER BY [Link];

The RIGHT JOIN keyword returns all the rows from the right table (Orders), even if there
are no matches in the left table (Persons).

[Link] FULL JOIN:

The FULL JOIN keyword return rows when there is a match in one of the tables.

SQL FULL JOIN Syntax:

SELECT column_name(s)
FROM table_name1 FULL JOIN table_name2
ON table_name1.column_name=table_name2.column_name;
SQL FULL JOIN Example:

The “Persons” table:

P_Id LastName FirstName Address City

1 Hansen Ola Timoteivn 10 Sandnes

2 Svendson Tove Borgvn 23 Sandnes

3 Pettersen Kari Storgt 20 Stavanger

The "Orders" table:
O_Id OrderNo P_Id

77895
1 3
44678
2 3
22456
3 1
24562
4 1
34764
5 15
Now we want to list all the persons and their orders, and all the orders with their persons.
We use the following SELECT statement:
SELECT [Link], [Link], [Link]
FROM Persons FULL JOIN Orders
ON Persons.P_Id=Orders.P_Id
ORDER BY [Link];
LastName FirstName OrderNo

Hansen Ola 22456

Hansen Ola 24562

Svendson Tove NULL

Pettersen Kari 77895

Pettersen Kari 44678

NULL NULL 34764

The FULL JOIN keyword returns all the rows from the left table (Persons), and all the rows
from the right table (Orders). If there are rows in "Persons" that do not have matches in
"Orders", or if there are rows in "Orders" that do not have matches in "Persons", those rows
will be listed as well.

Questions:
Q.1.

Worker(Wroker_id, first_name, last_name, salary, Dateof Joining,Deaprtment)

Bonus(Wroker_id,bonus_date,bonus)
Designation(Worker_id,designation)

1 . Write SQL query to print all worker details from the worker table Order By
FIRST_NAME ascending and DEPARTMENT Descending.

2. Write SQL query to print details of the workers who are also Managers
3. Write SQL query to fetch the list of Employees with The Same Salary

4. Write SQL query To fetch the Departments that have Less than five people in it.

5. Write SQL query To Print the Name of employees having the lowest salary in each
department
Q.2
Employee(employee-name, street, city)
Works(employee-name, company-name, salary)
Company(company-name, city)
Manages(employee-name, manager-name)

1. Find the names, street address, and cities of residence for all employees who work for
ABC and earn more than 60,000.

2 . Find the names of all employees in the database who live in the same cities as the
companies for which they work.
3 . Find the names of all employees in the database who do not work for 'ABC'.
Assume that all people work for exactly one company

4. Find the names of all employees in the database who earn more than every employee
of 'ABC'. Assume that all people work for at most one company

Q3
Airport(airport_no,city,street_name)
Booking(Passenger_id,Passenger_name,age,nationality,source,destination,flight_no)
Flight(flight_no,departure,arrival,airline_name,airport_no)

1. Retrieve the flight number with departure and arrival airports of all British Airways flights.

2. Retrieve the name of every passenger together with their flight number and the associated
flight company.
3. Retrieve the ticket numbers and names of all passengers departing from London

Conclusion: Thus we have implemented Different SQL joins in SQL and the output was
verified.
 Name of Student Mohit Subhash Joshi

Roll No DOP DOS Signature Remark

20

EXPERIMENT 9

Aim: Implementation of SQL Views and triggers.

Theory:

VIEW
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.

● SQL CREATE VIEW Syntax:

CREATE VIEW view_name
AS SELECT column_name(s)
FROM table_name
WHERE condition;

SQL CREATE VIEW Examples:

If you have the Northwind database you can see that it has several views installed by
[Link] view "Current Product List" lists all active products (products that are
not discontinued) from the "Products" table. The view is created with the following
SQL: CREATE VIEW [Current Product List] AS
SELECT ProductID,ProductName
FROM Products WHERE
Discontinued=No;

● SQL Updating a View:

You can update a view by using the following syntax
SQL CREATE OR REPLACE VIEW Syntax:
CREATE OR REPLACE VIEW view_name AS
SELECT column_name(s)
FROM table_name
WHERE condition;

Now we want to add the "Category" column to the "Current Product List" view. We
will update the view with the following SQL: CREATE VIEW [Current Product List]
AS
SELECT ProductID,ProductName,Category
FROM Products
WHERE Discontinued=No;
● SQL Dropping a View:
You can delete a view with the DROP VIEW command.

SQL DROP VIEW Syntax:

DROP VIEW view_name;

Trigger

A MySQL trigger is a stored program (with queries) which is executed automatically to

respond to a specific event such as insertion, updation or deletion occurring in a table. There
are 6 different types of triggers in MySQL:
1. Before Insert: It is activated before the insertion of data into the table.
2. After Insert: It is activated after the insertion of data into the table.
3. Before Update: It is activated before the update of data in the table.
4. After Update: It is activated after the update of the data in the table.
5. Before Delete: It is activated before the data is removed from the table.
6. After Delete: It is activated after the deletion of data from the table.
Syntax
CREATE TRIGGER trigger_name
(AFTER | BEFORE) (INSERT | UPDATE | DELETE)
ON table_name FOR EACH ROW
BEGIN
--variable declarations
--trigger code
END;

The trigger body can access the column's values, which are affected by the DML statement.
The NEW and OLD modifiers are used to distinguish the column values BEFORE and
AFTER the execution of the DML statement.

Questions:
Write SQL Queries for following

Student_Details
STU_ID NAME ADDRESS

Rohan Delhi
1
Payal Noida
2
Raj Ghaziabad
3
Tanuja Gurugram
4
Student_Marks
 STU_ID SUBJECT MARKS AGE

DBMS 97 19
1
DS 86 21
2
DBMS 74 18
3
TCS 90 20
4
DS 96 18
3
Q.1 Creating View from a single table

1. Create a view stud, to display name and address of student having ID <4

2. In a stud view, update the city of Raj to Delhi

3. Insert a new record in a stud view

Q.2 Creating View from a Two tables

1 . Create a view A1 to display details of students having maximum marks in each subject

2 Create a view A2 to display Student ID, name and Age of student having age<20

Q.3 Create a new table employee with eid, ename, salary. Insert a few records in
employee tables.
1. Create a trigger to generate the log of deleted records from the above table.

2. Increase the salary of all employees by 10%. Write a trigger to generate the log of
Old salary and updated salary of the employees.
Q.4 Create a table Student with Sid, Sname, Sub1, Sub2, Total, Percentage. Create a
trigger to calculate the Total and percentage while inserting a record into the table.
Conclusion: Thus we have implemented SQL view, Updating Views, dropping of views,
Creating triggers and dropping triggers.
 Name of Student Mohit Subhash Joshi

Roll No DOP DOS Signature Remark

20

EXPERIMENT 10

Aim: To Study DCL and TCL

Commands Theory-
DCL Commands

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.

DCL commands are typically used by database administrators. 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.

Transaction Control Language (TCL) Commands in SQL

Transaction Control Language (TCL) is a subset of SQL commands used to manage

transactions in a database. Transactions are important for maintaining the integrity and
consistency of data. They allow multiple database operations to be executed as a single unit
of work, which either entirely succeeds or fails. The primary TCL commands in SQL
include:

1. COMMIT: This command is used to permanently save all changes made in the
current transaction.

● Syntax: COMMIT;
● When you issue a COMMIT command, the database system will ensure that
all changes made during the current transaction are saved to the database.

2. ROLLBACK: This command is used to undo changes that have been made in the
current transaction.
● Syntax: ROLLBACK;
● If you issue a ROLLBACK command, all changes made in the current transaction
are discarded, and the state of the data reverts to what it was at the beginning of
the transaction.

3. SAVEPOINT: This command creates points within a transaction to which you can
later roll back. It allows for partial rollbacks and more complex transaction control.

● Syntax: SAVEPOINT savepoint_name;

● You can roll back to a savepoint using ROLLBACK TO savepoint_name;

Conclusion: In conclusion, DCL commands manage user access and permissions, while
TCL commands ensure data integrity by controlling transactions, allowing for commits,
rollbacks, and savepoints.