DBMS LAB MANUAL – R2023, Department of Information Technology, SMVEC.

Prepared by Mrs.N.Kalaiselvi, Mr.P.Praveenkumar, Mr.A. Ranjeeth, AP/IT, SMVEC

DEPARTMENT OF INFORMATION TECHNOLGY

Database Management Systems Laboratory - U23CSPC03

Laboratory Manual – R2023

1|Page
DBMS LAB MANUAL – R2023, Department of Information Technology, SMVEC.
Prepared by Mrs.N.Kalaiselvi, Mr.P.Praveenkumar, Mr.A. Ranjeeth, AP/IT, SMVEC

VISION, MISSION AND PROGRAM EDUCATIONAL OBJECTIVES

VISION OF THE INSTITUTION
To be globally recognized for excellence in quality education, innovation and research for
the transformation of lives to serve the society.
MISSION OF THE INSTITUTION
M1: Quality Education: To provide comprehensive academic system that amalgamates the
cutting edge technologies with best practices.
M2: Research and Innovation: To foster value-based research and innovation in
collaboration with industries and institutions globally for creating intellectuals with new
avenues.
M3: Employability and Entrepreneurship: To inculcate the employability and
entrepreneurial skills through value and skill based training.
M4: Ethical Values: To instil deep sense of human values by blending societal righteousness
with academic professionalism for the growth of society.
VISION OF THE DEPARTMENT
To be a pioneer in the field of Information Technology by achieving academic excellence,
involving in research & development and promoting technical & professional expertise.
MISSION OF THE DEPARTMENT
Expertise: To impart quality education and create excellent engineers with strong
analytical, Programming and Problem solving skills to meet the ever changing demands of
IT industry
Eminence: To kindle creative thinking, innovation and foster value-based research in the
field of information technology
Complaisant: To enrich the employability skills, inculcate entrepreneurial ideology and
promote professional expertise
Exemplar: To instill moral values, ethical responsibilities and empowering graduates to be
socially responsible and technically competent
PROGRAM EDUCATIONAL OBJECTIVES
PEO1 Fortify: To prepare the students with fundamental knowledge in programming
languages and in developing applications
PEO2 Equip: To develop skill in understanding the complexity in networking, security,
data mining, web technology and mobile communication so as to develop innovative
applications and projects in these areas for the betterment of society, as well as to enable
them to pursue higher education
PEO3 Endow: To enable the students as full-fledged professionals by providing
opportunities to enhance their analytical, communication skills and problem solving skills
along with organizing abilities
PEO4 Conventional: To familiarize the students with the ethical issues in engineering
profession, issues related to the worldwide economy, nurturing of current job related skills
and emerging technologies

2|Page
DBMS LAB MANUAL – R2023, Department of Information Technology, SMVEC.
Prepared by Mrs.N.Kalaiselvi, Mr.P.Praveenkumar, Mr.A. Ranjeeth, AP/IT, SMVEC

PROGRAM OUTCOMES AND PROGRAM SPECIFIC OUTCOMES

List of Program Specific Outcome’s
PSO 1: Establishment of Mathematical and computer systems concepts: To use mathematical and
system concepts to solve multidisciplinary problems using appropriate mathematical analysis, system
and programming concepts on various computing environments.
PSO 2: Establishment of communication and information concepts: To inculcate good breadth of
knowledge to apply and enhance informatics and communication technologies
PSO 3: Establishment of Business, Technological concepts: The ability to interpret and respond to
business agility with relevant software tools and skills and provide newer ideas and innovations in
information technology research

List of Program Outcomes

PO1 Engineering Knowledge: Apply knowledge of mathematics and science, with fundamentals of
Engineering and Technology to be able to solve complex engineering problems related to IT.
PO2 Problem Analysis: Identify, Formulate, review research literature and analyze complex engineering
problems related to IT and reaching substantiated conclusions using first principles of mathematics, natural
sciences and engineering sciences
PO3 Design/Development of solutions: Design solutions for complex engineering problems related to IT
and design system components or processes that meet the specified needs with appropriate consideration for
the public health and safety and the cultural societal and environmental considerations
PO4 Conduct Investigations of Complex problems: Use research–based knowledge and research
methods including design of experiments, analysis and interpretation of data, and synthesis of the
information to provide valid conclusions.
PO5 Modern Tool Usage: Create, Select and apply appropriate techniques, resources and modern
engineering and IT tools including prediction and modelling to computer science related complex
engineering activities with an understanding of the limitations
PO6 The Engineer and Society: Apply Reasoning informed by the contextual knowledge to assess
societal, health, safety, legal and cultural issues and the consequent responsibilities relevant to the IT
professional engineering practice
PO7 Environment and Sustainability: Understand the impact of the IT professional engineering solutions
in societal and environmental contexts and demonstrate the knowledge of, and need for sustainable
development
PO8 Ethics: Apply Ethical Principles and commit to professional ethics and responsibilities and norms of
the engineering practice
PO9 Individual and Team Work: Function effectively as an individual and as a member or leader in
diverse teams and in multidisciplinary Settings
PO10 Communication: Communicate effectively on complex engineering activities with the engineering
community and with society at large such as able to comprehend and with write effective reports and design
documentation, make effective presentations and give and receive clear instructions.
PO11 Project Management and Finance: Demonstrate knowledge and understanding of the engineering
management principles and apply these to one’s own work, as a member and leader in a team, to manage
projects and in multi-disciplinary environments
PO12 Life-Long Learning: Recognize the need for and have the preparation and ability to engage in
independent and life-long learning the broadest context of technological changes.
3|Page
DBMS LAB MANUAL – R2023, Department of Information Technology, SMVEC.
Prepared by Mrs.N.Kalaiselvi, Mr.P.Praveenkumar, Mr.A. Ranjeeth, AP/IT, SMVEC

SYLLABUS

Department Computer Science and Engineering Programme: B.Tech.

Semester Third Course Category: PC End Semester Exam Type: LE
Periods/Week Credit Maximum Marks
Course Code U23CSPC03
L T P C CAM ESE TM
Database Management Systems
Course Name Laboratory 0 0 2 1 50 50 100
(Common to CSE, IT and CCE)
Prerequisite Data Structures and Algorithms
On completion of the course, the students will be able to BT Mapping
(Highest Level)
CO1 Implement relational database systems using SQL statements. K3
CO2 Use typical data definitions and manipulation commands in various K3
applications.
Course CO3 Demonstrate applications using Nested and Join Queries K3
Outcomes CO4 Execute various advance SQL queries related to Transaction Processing. K3
CO5 Build commercial relational database systems using trigger and cursor K3
concept.
List of Exercises
Structured Query Language:
1. Data Definition Language
2. Data Manipulation Language
3. Data Selection and Projection statements
4. Aggregate Functions
5. Joins
6. Built in Functions
7. Nested Queries
8. Set Operations
9. View
10. Transaction Control Language
11. Data Control Language
PL/SQL:
12. Simple Pl/SQL Programs
13. Trigger
14. Cursor: Implicit Cursor and Explicit Cursor

Lecture Periods: - Tutorial Periods: - Practical Periods:30 Total Periods:30

Reference Books
1. Oracle Developer Handbook
2. SQL/PL/SQL for Oracle by P.S.Deshpande, IITMadras, Dream Tech Press.
nd
3. Alan Beaulieu, Mastering SQL Fundamentals,2 Edition,O‟Reilly,2009
4. Silberschatz, Korth, Sudarshan, Database System Concepts, 7thEdition -McGraw-Hill Higher Education,2019

4|Page
DBMS LAB MANUAL – R2023, Department of Information Technology, SMVEC.
Prepared by Mrs.N.Kalaiselvi, Mr.P.Praveenkumar, Mr.A. Ranjeeth, AP/IT, SMVEC

Web References
1. www.oracle-developer.net
2. www.oracle.com/DBA

COs/POs/PSOs Mapping

Program Specific
Program Outcomes(POs)
COs Outcomes(PSOs)
PO1 PO2 PO3 PO4 PO5 PO6 PO7 PO8 PO9 PO10 PO11 PO12 PSO1 PSO2 PSO3
1 3 3 2 3 2 2 1 - 2 1 - 2 2 3 2

2 3 2 3 3 2 2 1 - 2 1 - - 3 3 3

3 3 3 3 3 2 2 2 - 2 1 - - 3 2 3

4 3 2 3 3 2 2 1 - 2 1 - - 3 3 3

5 3 3 3 3 2 2 2 - 2 1 - - 3 2 3

Correlation Level: 1-Low, 2-Medium, 3-High.

Evaluation Method

Continuous Assessment Marks (CAM)

End Semester
Performance in practical Examination Total
Assessment classes Model
(ESE) Marks Marks
Practical Attendance
Conduction of Record Examination
viva
practical work

Marks 15 5 5 15 10 50 100

5|Page
DBMS LAB MANUAL – R2023, Department of Information Technology, SMVEC.
Prepared by Mrs.N.Kalaiselvi, Mr.P.Praveenkumar, Mr.A. Ranjeeth, AP/IT, SMVEC

Recommended System / Software Requirements

• Intel based desktop PC with minimum of 2.6GHZ or faster processor with at least 256 MB RAM
and 40GB free disk space.
• Operating system: WINDOWS.
• Software
o Oracle Database 10g Express Edition (XE)
Guidelines to Students
• Computers and the equipments in the lab for the use of student community. Students need to
maintain a proper decorum in the computer lab. Students must use the computers with care.
• Students are instructed to come to lab in formal dresses only.
• Students are supposed to occupy the systems allotted to them and are not supposed to talk or make
noise in the lab.
• Students are required to carry their observation book and lab records with completed exercises while
entering the lab.
• Lab records need to be submitted every week.
• Students are not supposed to use pen drives in the lab

Each practical which student is performing in the lab should have the following details:
a) Program Name
b) Aim
c) Table Description or Schema
d) Queries / PL/SQL Code
e) Output
f) Result
g) Viva questions

6|Page
DBMS LAB MANUAL – R2023, Department of Information Technology, SMVEC.
Prepared by Mrs.N.Kalaiselvi, Mr.P.Praveenkumar, Mr.A. Ranjeeth, AP/IT, SMVEC

MARKING SCHEME FOR THE PRACTICAL EXAMS

• Internal Practical Exam

• End Semester Practical Exam

INTERNAL PRACTICAL EXAM

It is taken by the concerned faculty of the batch.
MARKING SCHEME FOR INTENAL PRACTICAL EXAM

Conduction of Practical 15
Record Work 05
Viva Voce 05
Model Practical Examination 15
Attendance 10
Total 50

END SEMESTER PRACTICAL EXAM

It is taken by the concerned faculty of the batch and by an external examiner. In this exam student needs
to perform the experiment allotted at the time of the examination, viva voce conducted and the paper is
evaluated
MARKING SCHEME FOR END SEMESTER PRACTICAL EXAM

Aim & Table Description 10

Queries/PL/SQL Code 20

Execution & Output 10

Viva Voice 10

Total 50

NOTE:
Internal marks (50 marks) + External marks (50 marks) = Total marks given to the students (100 Marks)
Experiments given to perform can be from any section of the lab.
The pattern of Model Practical Examination will be similar to the End Semester practical Examination.

7|Page
DBMS LAB MANUAL – R2023, Department of Information Technology, SMVEC.
Prepared by Mrs.N.Kalaiselvi, Mr.P.Praveenkumar, Mr.A. Ranjeeth, AP/IT, SMVEC

TABLE OF INDEX
EX.NO EXERCISE NAME PAGE NO.
SQL
1. SQL Basic Commands 22
2. Data Definition Language 24
2.a DDL without Constraint 26
2.b DDL with Constraint 29
3. Data Manipulation Language 34
4. Data Query Projection Statements 40
5. Data Query Selection Statements 43
6. Aggregate Functions 47
7. SQL Joins 50
8. Built in Functions 57
9. Set Operations 61
10. Nested Queries 65
11. Views 69
12. Transaction Control Language 73
13. Data Control Language 77
PL/SQL
14. Simple PL/SQL programs 81
15. Cursor 84
16. Triggers 87
Content beyond the Syllabus
17 Student Information System using C#.net and Oracle Database 93
18 NoSQL Database Operations: CRUD (Library) : MongoDB 101

8|Page
DBMS LAB MANUAL – R2023, Department of Information Technology, SMVEC.
Prepared by Mrs.N.Kalaiselvi, Mr.P.Praveenkumar, Mr.A. Ranjeeth, AP/IT, SMVEC

Oracle Database Version

Oracle Initial
Marquee
Database Release
Features
Version Version
AI Vector Search(includes new Vector data type, Vector indexes, and
Vector SQL operators/functions), JSON Relational Duality, JSON
Schema Validation, Transactional Microservices Support, OKafka,
Operational Property Graphs, Support for SQL/PGQ, Schema
Privileges, Developer Role, In-database SQL Firewall, TLS 1.3
Oracle Database
23.4.0 Support, Integration with Azure Active Directory OAuth2, True Cache
23ai
for mid-tier caching, Readable Per-PDB Standby, Globally Distributed
Database with active-active RAFT-based replication, Real-time SQL
Plan Management, Priority Transactions, SQL Syntax Simplification,
Schema Annotations, Data Use Case Domains, Column Value Lock-
free Reservations
Blockchain Tables, Multilingual Engine - JavaScript Execution in the
Database, Binary JSON Data Type, Per-PDB Data Guard Physical
Standby (aka Multitenant Data Guard), Per-PDB GoldenGate Change
Oracle Database
21.1.0 Capture, Self-Managing In-Memory, In-Memory Hybrid Columnar
21c
Scan, In-Memory Vector Joins with SIMD, Sharding Advisor Tool,
Property Graph Visualization Studio, Automatic Materialized Views,
Automatic Zone Maps, SQL Macros, Gradual Password Rollover
Active Data Guard DML Redirection, Automatic Index Creation, Real-
Time Statistics Maintenance, SQL Queries on Object Stores, In-
Oracle Database 19.1.0 // Memory for IoT Data Streams, Hybrid Partitioned Tables, Automatic
19c 12.2.0.3 SQL Plan Management, SQL Quarantine, Zero-Downtime Grid
Infrastructure Patching, Finer-Granularity Supplemental Logging,
Automated PDB Relocation
Polymorphic Table Functions, Active Directory Integration,
Oracle Database 18.1.0 // Transparent Application Continuity, Approximate Top-N Query
18c 12.2.0.2 Processing, PDB Snapshot Carousel, Online Merging of Partitions and
Subpartitions
Oracle Database 12.2.0.1 Native Sharding, Zero Data Loss Recovery Appliance, Exadata Cloud
12c Release 2 March 17 Service, Cloud at Customer
Oracle Database Multitenant architecture, In-Memory Column Store, Native JSON,
12.1.0.1
12c Release 1 SQL Pattern Matching, Database Cloud Service
Edition-Based Redefinition, Data Redaction, Hybrid Columnar
Oracle Database
11.2.0.1 Compression, Cluster File System, Golden Gate Replication, Database
11g Release 2
Appliance
9|Page
DBMS LAB MANUAL – R2023, Department of Information Technology, SMVEC.
Prepared by Mrs.N.Kalaiselvi, Mr.P.Praveenkumar, Mr.A. Ranjeeth, AP/IT, SMVEC

Oracle Database
11.1.0.6 Active Data Guard, Secure Files, Exadata
11g Release 1
Real Application Testing, Database Vault, Online Indexing, Advanced
Oracle Database
10.2.0.1 Compression, Data Guard Fast-Start Failover, Transparent Data
10g Release 2
Encryption
Oracle Database Automated Database Management, Automatic Database Diagnostic
10.1.0.2
10g Release 1 Monitor, Grid infrastructure, Oracle ASM, Flashback Database
Oracle9i Database
9.2.0.1 Advanced Queuing, Data Mining, Streams, Logical Standby
Release 2
Oracle9i Database 9.0.1.0 Oracle Real Application Clusters (RAC), Oracle XML DB
Oracle8i Database 8.1.5.0 Native internet protocols and Java, Virtual Private Database
Oracle8 Database 8.0.3 Recovery Manager, Partitioning. First version available for Linux.
Oracle 7.3 7.3.0 Object-relational database
Oracle 7.2 7.2.0 Shared Server, XA Transactions, Transparent Application Failover
Oracle 7.1 7.1.0 Parallel SQL Execution. First version available for Windows NT.
PL/SQL stored procedures, Triggers, Distributed 2-phase commit,
Oracle7 7.0.12
Shared Cursors, Cost-Based Optimizer
Oracle 6.2 6.2.0 Oracle Parallel Server
Row-level locking, scalability / performance, online backup and
Oracle v6 6.0.17 recovery, B*Tree indexes, PL/SQL executed from compiled programs
(C etc). First version available for Novell Netware 386
5.0.22 Support for client/server computing and distributed database systems.
Oracle v5
(5.1.17) First version available for OS/2. Correlated sub-queries
Oracle v4 4.1.4.0 Multiversion read consistency. First version available for MS-DOS
Concurrency control, data distribution, and scalability. Re-written in C
Oracle v3 3.1.3
for portability to other operating systems, including UNIX
First commercially available SQL RDBMS. Basic SQL queries, simple
join and CONNECT BY joins. Written in assembly language for
Oracle v2 2.3
the PDP-11 to run in 128KB of RAM Ran on PDP-11 and VAX/VMS
in PDP-11 compatibility mode.

10 | P a g e
DBMS LAB MANUAL – R2023, Department of Information Technology, SMVEC.
Prepared by Mrs.N.Kalaiselvi, Mr.P.Praveenkumar, Mr.A. Ranjeeth, AP/IT, SMVEC

SQL Overview
SQL (Structured Query Language) is a standardized language used for managing and manipulating
relational databases. It allows users to perform various operations on the data stored in a database, such as
querying, updating, inserting, and deleting data.
History of SQL
• 1970: Dr. Edgar F. Codd, a computer scientist at IBM, proposed the relational model for databases
in his seminal paper, "A Relational Model of Data for Large Shared Data Banks."
• 1974-1975: SQL was developed at IBM by Donald D. Chamberlin and Raymond F. Boyce. Initially
named SEQUEL (Structured English Query Language), it was designed to manipulate and retrieve
data stored in IBM's original quasi-relational database management system, System R.
• 1979: Relational Software, Inc. (now Oracle Corporation) introduced the first commercially
available implementation of SQL, Oracle V2.
• 1986: The American National Standards Institute (ANSI) adopted SQL as the standard relational
database query language. The International Organization for Standardization (ISO) followed suit in
1987.
• 1992: SQL-92 (or SQL2), an expanded version of the SQL standard, was released.
• 1999 and Beyond: Subsequent versions, such as SQL: 1999 (SQL3), SQL:2003, SQL:2008,
SQL:2011, and SQL:2016, introduced additional features and improvements, including support for
XML, triggers, recursive queries, and more.
Purpose of SQL
1. Data Querying: Retrieve specific data from one or more tables.
2. Data Manipulation: Insert, update, and delete data.
3. Data Definition: Create and modify database structures such as tables, indexes, and views.
4. Data Control: Control access to data and database objects using permissions and roles.
5. Transaction Control: Manage transactions to ensure data integrity.
Applications of SQL
1. Business Intelligence and Analytics:
o Retrieve and analyze large datasets to generate business insights.
o Use aggregate functions to compute metrics like sums, averages, and counts.
2. Web Development:
o Back-end data management for web applications.
o Retrieve and display dynamic content based on user input.
3. Database Administration:
o Create and manage database structures.
o Perform backup and recovery operations.
o Monitor and optimize database performance.
4. Data Warehousing:
o Extract, transform, and load (ETL) processes for data warehousing.
o Manage large-scale data storage and retrieval.
5. Application Development:
o Embed SQL queries in application code to interact with databases.
o Use SQL in stored procedures, triggers, and functions to automate database operations.

11 | P a g e
DBMS LAB MANUAL – R2023, Department of Information Technology, SMVEC.
Prepared by Mrs.N.Kalaiselvi, Mr.P.Praveenkumar, Mr.A. Ranjeeth, AP/IT, SMVEC

SQL IMPORTANT CLAUSES

Sl.
CLAUSE Purpose Syntax Example
No
CREATE TABLE
table_name ( CREATE TABLE departments (
column1 datatype department_id NUMBER PRIMARY
CREATE Creates a new constraints, KEY,
1.
TABLE table. column2 datatype department_name VARCHAR2(50)
constraints, NOT NULL
... );
);
ALTER TABLE
Modifies an
table_name ALTER TABLE employees
2. ALTER TABLE existing table's
ADD ADD email VARCHAR2(100);
structure.
column_namedatatype;
Deletes a table and DROP TABLE
3. DROP TABLE DROP TABLE old_employees;
its data. table_name;
INSERT INTO
INSERT INTO employees
table_name (column1,
Inserts new rows (first_name, last_name,
4. INSERT column2, ...)
into a table. department_id)
VALUES (value1,
VALUES ('John', 'Doe', 10);
value2, ...);
UPDATE table_name
UPDATE employees
Updates existing SET column1 = value1,
5. UPDATE SET department_id = 20
rows in a table. column2 = value2, ...
WHERE employee_id = 1;
WHERE condition;
DELETE FROM
Deletes existing DELETE FROM employees
6. DELETE table_name
rows from a table. WHERE employee_id = 1;
WHERE condition;
The TRUNCATE
statement in
Oracle is a Data
Definition
Language (DDL)
command used to
TRUNCATE TABLE
7. TRUNCATE quickly remove all TRUNCATE TABLE employees;
table_name;
rows from a table
or partition
without logging
individual row
deletions, which
makes it faster
12 | P a g e
DBMS LAB MANUAL – R2023, Department of Information Technology, SMVEC.
Prepared by Mrs.N.Kalaiselvi, Mr.P.Praveenkumar, Mr.A. Ranjeeth, AP/IT, SMVEC

than the DELETE

statement.
Specifies the
columns that you
SELECT column1,
want to retrieve SELECTemployee_id, first_name,
8. SELECT column2, ...
from one or more last_nameFROM employees;
FROMtable_name;
tables or
expressions.
Specifies the table SELECT column1,
SELECT first_name, last_name
9. FROM to retrieve data column2, ...
FROM employees;
from. FROM table_name;
SELECT column1,
Filters records SELECT first_name, last_name
column2, ...
10. WHERE based on specified FROM employees
FROM table_name
conditions. WHERE department_id = 10;
WHERE condition;
Groups rows that
SELECT column1,
have the same SELECT department_id, COUNT(*)
COUNT(*)
11. GROUP BY values in specified FROM employees
FROM table_name
columns into GROUP BY department_id;
GROUP BY column1;
aggregate data.
SELECT column1,
Filters groups COUNT(*) SELECT department_id, COUNT(*)
based on specified FROM table_name FROM employees
12. HAVING
conditions. Used GROUP BY column1 GROUP BY department_id
with GROUP BY. HAVING COUNT(*) > HAVING COUNT(*) > 5;
value;
SELECT column1,
Sorts the result set column2, ... SELECT first_name, last_name
13. ORDER BY in ascending or FROM table_name FROM employees
descending order. ORDER BY column1 ORDER BY last_name ASC;
[ASC|DESC];
SELECT columns SELECT e.first_name, e.last_name,
Combines rows
FROM table1 d.department_name
from two or more
14. JOIN JOIN table2 ON FROM employees e
tables based on
table1.column = JOIN departments d ON
related columns.
table2.column; e.department_id = d.department_id;
Selects unique SELECT DISTINCT
SELECT DISTINCT department_id
15. DISTINCT values from a column1
FROM employees;
column. FROM table_name;
Combines the SELECT column1, SELECT first_name, last_name
UNION and
16. results of two or column2, ... FROM employees_usa
UNION ALL
more SELECT FROM table1 UNION

13 | P a g e
DBMS LAB MANUAL – R2023, Department of Information Technology, SMVEC.
Prepared by Mrs.N.Kalaiselvi, Mr.P.Praveenkumar, Mr.A. Ranjeeth, AP/IT, SMVEC

statements. UNION SELECT first_name, last_name

SELECT column1, FROM employees_uk;
column2, ...
FROM table2;
Creates an index CREATE INDEX
CREATE INDEX
on a table to index_name
17. INDEX idx_employee_last_name
improve query ON table_name
ON employees (last_name);
performance. (column1, column2, ...);
CREATE TABLE
CREATE TABLE employees (
table_name (
employee_id NUMBER PRIMARY
column1 datatype
Defines a primary KEY,
18. PRIMARY KEY PRIMARY KEY,
key for a table. first_name VARCHAR2(50),
column2 datatype,
last_name VARCHAR2(50)
...
);
);
CREATE TABLE
table_name (
CREATE TABLE orders (
column1 datatype,
order_id NUMBER PRIMARY
column2 datatype,
Defines a foreign KEY,
...
key for a table to customer_id NUMBER,
19. FOREIGN KEY FOREIGN KEY
enforce referential FOREIGN KEY (customer_id)
(column_name)
integrity. REFERENCES customers
REFERENCES
(customer_id)
parent_table
);
(column_name)
);
CREATE TABLE
CREATE TABLE employees (
table_name (
Ensures that all employee_id NUMBER PRIMARY
column1 datatype
values in a column KEY,
20. CHECK CHECK (condition),
satisfy a specific salary NUMBER CHECK (salary
column2 datatype,
condition. > 0)
...
);
);
CREATE TABLE
table_name (
CREATE TABLE products (
Sets a default column1 datatype
product_id NUMBER PRIMARY
value for a column DEFAULT
21. DEFAULT KEY,
when no value is default_value,
price NUMBER DEFAULT 0
specified. column2 datatype,
);
...
);
22. VIEW Creates a virtual CREATE VIEW CREATE VIEW employee_salaries

14 | P a g e
DBMS LAB MANUAL – R2023, Department of Information Technology, SMVEC.
Prepared by Mrs.N.Kalaiselvi, Mr.P.Praveenkumar, Mr.A. Ranjeeth, AP/IT, SMVEC

table based on the view_name AS AS

result set of a SELECT column1, SELECT first_name, last_name,
SELECT query. column2, ... salary
FROM table_name FROM employees
WHERE condition; WHERE department_id = 10;
CREATE TRIGGER
trg_update_salary
CREATE TRIGGER BEFORE UPDATE OF salary ON
trigger_name employees
Executes a
BEFORE|AFTER FOR EACH ROW
specified set of
INSERT|UPDATE|DEL BEGIN
SQL statements
23. TRIGGER ETE ON table_name IF :NEW.salary< :OLD.salary
when a specified
FOR EACH ROW THEN
event occurs in the
BEGIN
database.
-- trigger body RAISE_APPLICATION_ERROR(-
END; 20001, 'Salary cannot be decreased');
END IF;
END;
Generates a CREATE SEQUENCE
sequence of sequence_name
CREATE SEQUENCE emp_seq
unique numbers, START WITH
24. SEQUENCE START WITH 1
often used for initial_value
INCREMENT BY 1;
primary key INCREMENT BY
values. increment_value;
DECLARE
CURSOR emp_cursor IS
DECLARE SELECT first_name, last_name
Handles a query CURSOR FROM employees;
result set one row cursor_name IS
25. CURSOR
at a time in SELECT column1, BEGIN
PL/SQL column2, ... OPEN emp_cursor;
FROM table_name; -- Fetch rows and process them
CLOSE emp_cursor;
END;
MERGE INTO MERGE INTO employees e
Performs insert, target_table USING USING new_employees ne
update, or delete source_table ON (e.employee_id =
operations on a ON (condition) ne.employee_id)
26. MERGE target table based WHEN MATCHED WHEN MATCHED THEN
on the results of a THEN UPDATE SET e.salary = ne.salary
join with a source UPDATE SET WHEN NOT MATCHED THEN
table. column1 = value1, INSERT (employee_id,
column2 = value2, ... first_name, last_name, salary)
15 | P a g e
DBMS LAB MANUAL – R2023, Department of Information Technology, SMVEC.
Prepared by Mrs.N.Kalaiselvi, Mr.P.Praveenkumar, Mr.A. Ranjeeth, AP/IT, SMVEC

WHEN NOT VALUES (ne.employee_id,

MATCHED THEN ne.first_name, ne.last_name,
INSERT (column1, ne.salary);
column2, ...) VALUES
(value1, value2, ...);
SELECT column1,
SELECT first_name, last_name,
CASE
CASE
WHEN condition1
WHEN salary > 10000 THEN
Provides if-then- THEN result1
'High'
else logic within a WHEN condition2
27. CASE WHEN salary > 5000 THEN
SELECT THEN result2
'Medium'
statement. ...
ELSE 'Low'
ELSE resultN
END AS salary_level
END AS alias_name
FROM employees;
FROM table_name;
WITH cte_name AS (
Defines a WITH Sales_CTE AS (
SELECT column1,
temporary result SELECT customer_id,
column2, ...
set that can be SUM(amount) AS total_sales
WITH FROM table_name
referenced within FROM sales
(Common Table WHERE condition
28. a SELECT, GROUP BY customer_id
Expressions, )
INSERT, )
CTE) SELECT column1,
UPDATE, or SELECT customer_id, total_sales
column2, ...
DELETE FROM Sales_CTE
FROM cte_name
statement. WHERE total_sales> 500;
WHERE condition;
Assigns a unique SELECT column1,
number to each column2, ... SELECT first_name, last_name
29. ROWNUM row to limit the FROM table_name FROM employees
number of rows WHERE ROWNUM <= WHERE ROWNUM <= 10;
returned. number;
Divides the result SELECT column1,
SELECT department_id,
set into partitions aggregate_function(colu
employee_id, salary,
PARTITION and performs mn2) OVER
30. AVG(salary) OVER (PARTITION
BY aggregate (PARTITION BY
BY department_id) AS avg_salary
functions on each column3)
FROM employees;
partition. FROM table_name;
Performs Common Analytic SELECT first_name, last_name,
ANALYTIC calculations across Functions: salary,
31. FUNCTIONS a set of table rows ROW_NUMBER, RANK() OVER (ORDER BY
related to the RANK, DENSE_RANK, salary DESC) AS salary_rank
current row. LEAD, LAG, etc. FROM employees;
32. SUBQUERY A query nested SELECT column1, SELECT first_name, last_name

16 | P a g e
DBMS LAB MANUAL – R2023, Department of Information Technology, SMVEC.
Prepared by Mrs.N.Kalaiselvi, Mr.P.Praveenkumar, Mr.A. Ranjeeth, AP/IT, SMVEC

within another column2, ... FROM employees

SQL query. FROM table_name WHERE department_id = (SELECT
WHERE column1 = department_id FROM departments
(SELECT column1 WHERE department_name = 'Sales');
FROM another_table
WHERE condition);
SELECT column1,
SELECT first_name, last_name
column2, ...
Tests for the FROM employees e
FROM table_name
existence of any WHERE EXISTS (SELECT 1
33. EXISTS WHERE EXISTS
record in a FROM departments d WHERE
(SELECT 1 FROM
subquery. d.department_id = e.department_id
another_table WHERE
AND d.department_name = 'Sales');
condition);
SELECT column1,
column2, ... SELECT first_name, last_name
Compares a value FROM table_name FROM employees
34. ALL to all values in WHERE column1 > WHERE salary > ALL (SELECT
another set. ALL (SELECT column1 salary FROM employees WHERE
FROM another_table department_id = 30);
WHERE condition);
SELECT column1,
SELECT first_name, last_name
column2, ...
FROM employees
Compares a value FROM table_name
WHERE department_id = ANY
35. ANY to any value in WHERE column1 =
(SELECT department_id FROM
another set. ANY (SELECT column1
departments WHERE location_id =
FROM another_table
1700);
WHERE condition);
SELECT column1, SELECT first_name, last_name
column2, ... FROM employees
Returns distinct
FROM table1 WHERE department_id = 10
rows that are in
36. INTERSECT INTERSECT INTERSECT
both result sets of
SELECT column1, SELECT first_name, last_name
two queries.
column2, ... FROM employees
FROM table2; WHERE salary > 10000;
SELECT column1, SELECT first_name, last_name
Returns distinct column2, ... FROM employees
rows from the first FROM table1 WHERE department_id = 10
37. MINUS query that are not MINUS MINUS
in the second SELECT column1, SELECT first_name, last_name
query. column2, ... FROM employees
FROM table2; WHERE salary > 10000;
38. PL/SQL Oracle's DECLARE DECLARE

17 | P a g e
DBMS LAB MANUAL – R2023, Department of Information Technology, SMVEC.
Prepared by Mrs.N.Kalaiselvi, Mr.P.Praveenkumar, Mr.A. Ranjeeth, AP/IT, SMVEC

procedural variable_namedatatype; v_employee_idemployees.employee_

extension for BEGIN id%TYPE;
SQL, used to write -- PL/SQL block BEGIN
complex scripts -- SQL and PL/SQL SELECT employee_id INTO
and stored statements v_employee_id
procedures. END; FROM employees
WHERE last_name = 'King';

DBMS_OUTPUT.PUT_LINE('Empl
oyee ID: ' || v_employee_id);
END;
LOCK TABLE
Locks a table or
table_name IN LOCK TABLE employees IN
39. LOCK TABLE table partitions in
{SHARE|EXCLUSIVE} EXCLUSIVE MODE;
a specified mode.
MODE;
COMMENT ON
TABLE table_name IS COMMENT ON TABLE employees
'comment'; IS 'Employee details';
Adds comments to
40. COMMENT COMMENT ON COMMENT ON COLUMN
a table or column.
COLUMN employees.salary IS 'Employee
table_name.column_nam salary in USD';
e IS 'comment';
SELECT ROWID, first_name,
Represents the SELECT ROWID,
last_name
41. ROWID unique address of column1, column2
FROM employees
a row in a table. FROM table_name;
WHERE department_id = 10;
CREATE
CREATE MATERIALIZED VIEW
MATERIALIZED
Stores the result of emp_mv AS
VIEW view_name AS
Materialized a query physically SELECT department_id, COUNT(*)
42. SELECT column1,
Views for performance AS emp_count
column2, ...
improvement. FROM employees
FROM table_name
GROUP BY department_id;
WHERE condition;
Partitioning CREATE TABLE CREATE TABLE sales (
Types: table_name ( sale_id NUMBER,
• Range Divides a table column1 datatype, sale_date DATE,
Partitioning into smaller, more column2 datatype, amount NUMBER
43. • List manageable ... ) PARTITION BY RANGE
Partitioning
pieces. ) PARTITION BY (sale_date) (
• Hash
Partitioning RANGE (column) ( PARTITION p1 VALUES LESS
• Composite PARTITION p1 THAN (TO_DATE('2021-01-01',

18 | P a g e
DBMS LAB MANUAL – R2023, Department of Information Technology, SMVEC.
Prepared by Mrs.N.Kalaiselvi, Mr.P.Praveenkumar, Mr.A. Ranjeeth, AP/IT, SMVEC

Partitioning VALUES LESS THAN 'YYYY-MM-DD')),

(value1), PARTITION p2 VALUES LESS
PARTITION p2 THAN (TO_DATE('2022-01-01',
VALUES LESS THAN 'YYYY-MM-DD'))
(value2) );
);
DECLARE
l_random_number NUMBER;
l_random_string VARCHAR2(20);
BEGIN
l_random_number :=
Generates random DBMS_RANDOM.value(1, 100);
DBMS_RAND
44. numbers and l_random_string :=
OM
strings. DBMS_RANDOM.string('x', 20);
DBMS_OUTPUT.put_line('Random
Number: ' || l_random_number);
DBMS_OUTPUT.put_line('Random
String: ' || l_random_string);
END;

19 | P a g e
DBMS LAB MANUAL – R2023, Department of Information Technology, SMVEC.
Prepared by Mrs.N.Kalaiselvi, Mr.P.Praveenkumar, Mr.A. Ranjeeth, AP/IT, SMVEC

SQL DATA TYPES

Sl.No Data Type Description
1. CHAR(n) Fixed-length character string. n specifies the length in bytes, up to 2000 bytes.
2. Variable-length character string. n specifies the maximum length in bytes, up to
VARCHAR2(n)
4000 bytes.
3. NCHAR(n) Fixed-length Unicode character string. n specifies the length in characters.
4. Variable-length Unicode character string. n specifies the maximum length in
NVARCHAR2(n)
characters.
5. CLOB Character Large Object for storing large character data, up to 4 GB.
6. Unicode Character Large Object for storing large Unicode character data, up to 4
NCLOB
GB.
7. Fixed-point or floating-point number. p specifies the precision (total number of
NUMBER(p, s)
digits), and s specifies the scale (number of digits to the right of the decimal p
8. INTEGER Whole number data type.
9. FLOAT(p) Floating-point number. p specifies the precision.
10. REAL Floating-point number with single precision.
11. DOUBLE
Floating-point number with double precision.
PRECISION
12. DATE Stores date and time values (year, month, day, hour, minute, second).
13. TIMESTAMP Stores date and time values with fractional seconds.
14. INTERVAL YEAR
Stores a period of time in years and months.
TO MONTH
15. INTERVAL DAY TO
Stores a period of time in days, hours, minutes, and seconds.
SECOND
16. BLOB Binary Large Object for storing large binary data, up to 4 GB.
17. Variable-length raw binary data. n specifies the maximum length in bytes, up to
RAW(n)
2000 bytes.
18. BINARY_FLOAT Single-precision floating-point number stored in binary format.
19. BINARY_DOUBLE Double-precision floating-point number stored in binary format.
20. BFILE Binary File LOB for storing large binary data outside the database.
21. BOOLEAN Logical Boolean values (TRUE or FALSE).
22. ROWID Binary address of a row in a database table.
23. UROWID Universal row ID, can be stored in any character set.
24. XMLTYPE XML data type for storing XML documents.
25. OBJECT User-defined object type.
26. VARRAY Variable-size array type.
27. Nested Table Collection data type.

20 | P a g e
DBMS LAB MANUAL – R2023, Department of Information Technology, SMVEC.
Prepared by Mrs.N.Kalaiselvi, Mr.P.Praveenkumar, Mr.A. Ranjeeth, AP/IT, SMVEC

STRUCTURED QUERY
LANGUAGE (SQL)

21 | P a g e
DBMS LAB MANUAL – R2023, Department of Information Technology, SMVEC.
Prepared by Mrs.N.Kalaiselvi, Mr.P.Praveenkumar, Mr.A. Ranjeeth, AP/IT, SMVEC

Ex.No: 1
SQL Basic Commands
Create a Employee database and perform the following SQL basic commands.
• Create the emp table with columns for employee number, name, date of birth, salary, and
designation.
• Insert a new employee with employee number 100, name 'Ram', date of birth '1996-04-21',
salary 50000, and designation 'Manager'.
• Update the salary of all employees by adding 1000
• Delete the record of the employee with employee number 100

//Create table
CREATE TABLE emp
(
empno NUMBER,
empname VARCHAR2(255),
DOB DATE,
salary NUMBER,
designation VARCHAR2(20)
);
//Describe Table
SQL> desc emp;
Name Null? Type
----------------------------------------- -------- ----------------------------
EMPNO NUMBER
EMPNAME VARCHAR2(255)
DOB DATE
SALARY NUMBER
DESIGNATION VARCHAR2(20)

// Insert values
INSERT INTO emp(empno,empname, DOB, designation) VALUES(100,'Ram', ‘21-Apr-1996’,
50000,'Manager');
INSERT INTO emp(empno,empname, DOB, designation) VALUES(101,'Meera', ‘20-July-1994’,
25000,'Clerk');

22 | P a g e
DBMS LAB MANUAL – R2023, Department of Information Technology, SMVEC.
Prepared by Mrs.N.Kalaiselvi, Mr.P.Praveenkumar, Mr.A. Ranjeeth, AP/IT, SMVEC

// Display values
SELECT * FROM emp;
EMPNO EMPNAME DOB SALARY DESIGNATION
100 Ram 21-Apr-1996 50000 Manager
101 Meera 20-July-1994 25000 Clerk

SELECT empname,salary FROM emp;

EMPNAME SALARY
Ram 50000
Meera 25000

// Modify values
UPDATE emp SET salary = salary + 1000;

SELECT * FROM emp;

EMPNO EMPNAME DOB SALARY DESIGNATION
100 Ram 21-Apr-1996 51000 Manager
101 Meera 20-July-1994 26000 Clerk

// Delete values
DELETE FROM emp WHERE empno = 100;

SELECT * FROM emp;

EMPNO EMPNAME DOB SALARY DESIGNATION
101 Meera 20-July-1994 26000 Clerk

Result:

Thus all the above basic SQL commands have been executed successfully and the output was
verified.
23 | P a g e
DBMS LAB MANUAL – R2023, Department of Information Technology, SMVEC.
Prepared by Mrs.N.Kalaiselvi, Mr.P.Praveenkumar, Mr.A. Ranjeeth, AP/IT, SMVEC

Ex.No: 2

Data Definition Language (DDL)

DDL (Data Definition Language) consists of statements used to define, manage, and manipulate
database objects such as tables, indexes, views, sequences, and so on. These statements enable
database administrators and developers to create, alter, and drop database objects as needed.

DDL Statements

• CREATE TABLE
• ALTER TABLE
• DROP TABLE

1. CREATE Statement

The CREATE TABLE statement is used to create a relational table

CREATE TABLE table_name

(
column_name1 data_type [constraints],
column_name1 data_type [constraints],
column_name1 data_type [constraints],
……..
);

2. Alter Table

The ALTER TABLE statement is used to add, delete, or modify columns in an existing table

a. To Add a column

ALTER TABLE table_name ADD column_name datatype

b. To delete a column in a table

ALTER TABLE table_name DROP COLUMN column_name

c. To change the data type or size of a column in a table

ALTER TABLE table_name MODIFY column_name datatype

d. To change the name of a column in a table

ALTER TABLE table_name RENAME COLUMN old-col_name TO new-col-name

24 | P a g e
DBMS LAB MANUAL – R2023, Department of Information Technology, SMVEC.
Prepared by Mrs.N.Kalaiselvi, Mr.P.Praveenkumar, Mr.A. Ranjeeth, AP/IT, SMVEC

3. Drop Table

Used to delete the table permanently from the storage

Syntax: DROP TABLE table_name;

4. Truncate the table

The TRUNCATE TABLE statement is used to remove all records from a table, but it does not delete the
table itself.

Syntax: TRUNCATE TABLE table-name;

5. Rename the Table

The RENAME statement is used to change the name of an existing table.

Syntax: RENAME old-table_name TO new-table_name;

25 | P a g e
DBMS LAB MANUAL – R2023, Department of Information Technology, SMVEC.
Prepared by Mrs.N.Kalaiselvi, Mr.P.Praveenkumar, Mr.A. Ranjeeth, AP/IT, SMVEC

Ex. No: 2.a

Data Definition Language (DDL)

(Without constraint)

1. CREATE THE TABLE

CREATE TABLE emp

(
empno NUMBER,
empname VARCHAR2(25),
dob DATE,
salary NUMBER,
designation VARCHAR2(20)
);

- Table Created

// Describe the table emp

SQL> desc emp;

Name Null? Type
----------------------------------------- -------- ----------------------------
EMPNO NUMBER
EMPNAME VARCHAR2(25)
DOB DATE
SALARY NUMBER
DESIGNATION VARCHAR2(20)

2. ALTER THE TABLE

a. ADD

// To alter the table emp by adding new attribute department

ALTER TABLE emp ADD department VARCHAR2 (50);

SQL> desc emp;
Name Null? Type
----------------------------------------- -------- ----------------------------
EMPNO NUMBER
EMPNAME VARCHAR2(255)
DOB DATE
SALARY NUMBER
DESIGNATION VARCHAR2(20)
DEPARTMENT VARCHAR2(50)
26 | P a g e
DBMS LAB MANUAL – R2023, Department of Information Technology, SMVEC.
Prepared by Mrs.N.Kalaiselvi, Mr.P.Praveenkumar, Mr.A. Ranjeeth, AP/IT, SMVEC

b. MODIFY
// To alter the table emp by modifying the size of the attribute department
ALTER TABLE emp MODIFY department VARCHAR2 (100);

SQL> desc emp;

Name Null? Type
----------------------------------------- -------- ----------------------------
EMPNO NUMBER
EMPNAME VARCHAR2(255)
DOB DATE
SALARY NUMBER
DESIGNATION VARCHAR2(20)
DEPARTMENT VARCHAR2(100)

c. DROP
// To alter the table emp by deleting the attribute department
ALTER TABLE emp DROP(department);

SQL> desc emp;

Name Null? Type
----------------------------------------- -------- ----------------------------
EMPNO NUMBER
EMPNAME VARCHAR2(255)
DOB DATE
SALARY NUMBER
DESIGNATION VARCHAR2(20)

d. RENAME
// To alter the table emp by renaming the attribute empno to emplno
ALTER TABLE emp RENAME COLUMN empno TO emplno;

SQL> desc emp;

Name Null? Type
----------------------------------------- -------- ----------------------------
EMPLNO NUMBER
EMPNAME VARCHAR2(255)
DOB DATE
SALARY NUMBER
DESIGNATION VARCHAR2(20)

27 | P a g e
DBMS LAB MANUAL – R2023, Department of Information Technology, SMVEC.
Prepared by Mrs.N.Kalaiselvi, Mr.P.Praveenkumar, Mr.A. Ranjeeth, AP/IT, SMVEC

3. RENAME THE TABLE

// To alter the table name by using rename keyword

RENAME emp TO empnew;

SQL> desc empnew

Name Null? Type
----------------------------------------- -------- ----------------------------
EMPLNO NUMBER
EMPNAME VARCHAR2(255)
DOB DATE
SALARY NUMBER
DESIGNATION VARCHAR2(20)

4. DROP THE TABLE

//To delete the table from the database

DROP TABLE empnew;

Table Dropped

DESC empnew;
Object to be described could not be found.

Result:
Thus all the above DDL SQL commands have been executed successfully and the output was verified.

28 | P a g e
DBMS LAB MANUAL – R2023, Department of Information Technology, SMVEC.
Prepared by Mrs.N.Kalaiselvi, Mr.P.Praveenkumar, Mr.A. Ranjeeth, AP/IT, SMVEC

Ex. No: 2.b

Data Definition Language (DDL)

(With constraint)

Constraints Types

• NOT NULL
• UNIQUE
• PRIMARY KEY
• FOREIGN KEY (REFERENCE KEY)
• CHECK
• DEFAULT
1. CREATE THE TABLE

// To create a table student

CREATE TABLE student

(
studentID NUMBER PRIMARY KEY,
sname VARCHAR2(30) NOT NULL,
department CHAR(5),
sem NUMBER,
dob DATE,
email_id VARCHAR2(20) UNIQUE,
college VARCHAR2(20) DEFAULT 'SMVEC'
);

// Describe the table student

SQL> desc student;

Name Null? Type

----------------------------------------- -------- ----------------------------
STUDENTID NOT NULL NUMBER
SNAME NOT NULL VARCHAR2(30)
DEPARTMENT CHAR(5)
SEM NUMBER
DOB DATE
EMAIL_ID VARCHAR2(20)
COLLEGE VARCHAR2(20)
29 | P a g e
DBMS LAB MANUAL – R2023, Department of Information Technology, SMVEC.
Prepared by Mrs.N.Kalaiselvi, Mr.P.Praveenkumar, Mr.A. Ranjeeth, AP/IT, SMVEC

//To create a table exam

CREATE TABLE exam

(
examID NUMBER ,
studentID NUMBER REFERENCES student(studentID),
department CHAR(5) NOT NULL,
mark1 NUMBER CHECK (mark1<=100 and mark1>=0),
mark2 NUMBER CHECK (mark2<=100 and mark2>=0),
mark3 NUMBER CHECK (mark3<=100 and mark3>=0),
mark4 NUMBER CHECK (mark4<=100 and mark4>=0),
mark5 NUMBER CHECK (mark5<=100 and mark5>=0),
total NUMBER,
average NUMBER,
grade CHAR(1)
);

//Describe the table exam

SQL> desc exam;

Name Null? Type
----------------------------------------- -------- ----------------------------
EXAMID NUMBER
STUDENTID NUMBER
DEPARTMENT NOT NULL CHAR(5)
MARK1 NUMBER
MARK2 NUMBER
MARK3 NUMBER
MARK4 NUMBER
MARK5 NUMBER
TOTAL NUMBER
AVERAGE NUMBER
GRADE CHAR(1)

2. ALTER THE TABLE

A. ADD

//To alter the table student by adding new primary key constraint to the examID attribute

ALTER TABLE exam ADD CONSTRAINT pr PRIMARY KEY (examID);

Table Altered

30 | P a g e
DBMS LAB MANUAL – R2023, Department of Information Technology, SMVEC.
Prepared by Mrs.N.Kalaiselvi, Mr.P.Praveenkumar, Mr.A. Ranjeeth, AP/IT, SMVEC

SQL> desc exam;

Name Null? Type
----------------------------------------- -------- ----------------------------
EXAMID NOT NULL NUMBER
STUDENTID NUMBER
DEPARTMENT NOT NULL CHAR(5)
MARK1 NUMBER
MARK2 NUMBER
MARK3 NUMBER
MARK4 NUMBER
MARK5 NUMBER
TOTAL NUMBER
AVERAGE NUMBER
GRADE CHAR(1)

B. MODIFY

//To alter the table student by modifying not null constraint to studentid column

ALTER TABLE exam MODIFY studentid not null;

Table altered.

SQL> desc exam;

Name Null? Type
----------------------------------------- -------- ----------------------------
EXAMID NOT NULL NUMBER
STUDENTID NOT NULL NUMBER
DEPARTMENT NOT NULL CHAR(5)
MARK1 NUMBER
MARK2 NUMBER
MARK3 NUMBER
MARK4 NUMBER
MARK5 NUMBER
TOTAL NUMBER
AVERAGE NUMBER
GRADE CHAR(1)

//To alter the table student by modifying defualt constraint to Total column as 0

ALTER TABLE exam MODIFY total DEFAULT 0;

Table altered.

31 | P a g e
DBMS LAB MANUAL – R2023, Department of Information Technology, SMVEC.
Prepared by Mrs.N.Kalaiselvi, Mr.P.Praveenkumar, Mr.A. Ranjeeth, AP/IT, SMVEC

SQL> desc exam;

Name Null? Type
----------------------------------------- -------- ----------------------------
EXAMID NOT NULL NUMBER
STUDENTID NOT NULL NUMBER
DEPARTMENT NOT NULL CHAR(5)
MARK1 NUMBER
MARK2 NUMBER
MARK3 NUMBER
MARK4 NUMBER
MARK5 NUMBER
TOTAL NUMBER
AVERAGE NUMBER
GRADE CHAR(1)

C. RENAME
// To alter the table student by renaming constraint for primary key examid column from pr to ps

ALTER TABLE exam RENAME CONSTRAINT pr to ps;

Table altered.

SQL> desc exam;

Name Null? Type
----------------------------------------- -------- ----------------------------
EXAMID NOT NULL NUMBER
STUDENTID NOT NULL NUMBER
DEPARTMENT NOT NULL CHAR(5)
MARK1 NUMBER
MARK2 NUMBER
MARK3 NUMBER
MARK4 NUMBER
MARK5 NUMBER
TOTAL NUMBER
AVERAGE NUMBER
GRADE CHAR(1)

D. DROP

// To delete the primary key constraint for column examid

ALTER TABLE exam DROP CONSTRAINT ps;

32 | P a g e
DBMS LAB MANUAL – R2023, Department of Information Technology, SMVEC.
Prepared by Mrs.N.Kalaiselvi, Mr.P.Praveenkumar, Mr.A. Ranjeeth, AP/IT, SMVEC

Table altered.

SQL> desc exam;

Name Null? Type
----------------------------------------- -------- ----------------------------
EXAMID NUMBER
STUDENTID NOT NULL NUMBER
DEPARTMENT NOT NULL CHAR(5)
MARK1 NUMBER
MARK2 NUMBER
MARK3 NUMBER
MARK4 NUMBER
MARK5 NUMBER
TOTAL NUMBER
AVERAGE NUMBER
GRADE CHAR(1)

ADD UNIQUE CONSTRAINT

//To alter the table student by adding new unique key constraint to the examID attribute

ALTER TABLE exam ADD CONSTRAINT pr UNIQUE (examID);

Table altered.

SQL> desc exam;

Name Null? Type
----------------------------------------- -------- ----------------------------
EXAMID NUMBER
STUDENTID NOT NULL NUMBER
DEPARTMENT NOT NULL CHAR(5)
MARK1 NUMBER
MARK2 NUMBER
MARK3 NUMBER
MARK4 NUMBER
MARK5 NUMBER
TOTAL NUMBER
AVERAGE NUMBER
GRADE CHAR(1)

Result:
Thus all the above DDL SQL commands have been executed successfully and the output was
verified.

33 | P a g e
DBMS LAB MANUAL – R2023, Department of Information Technology, SMVEC.
Prepared by Mrs.N.Kalaiselvi, Mr.P.Praveenkumar, Mr.A. Ranjeeth, AP/IT, SMVEC

Ex.No: 3
DATA MANIPULATION LANGUAGE (DML)

DML (Data Manipulation Language) statements are used to manipulate data within tables. These
statements allow users to insert, update, delete, and retrieve data from database tables.
DML Statements
• Insert into
• Update
• Delete

1. Insert into

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

Different forms of inserting a new record into the table

a. Direct Substitution

INSERT INTO table_name VALUES(value1, value2,value3, ……….);

b. Specific Column Insertion

INSERT INTO table_name (columnane1, columnname2, columnname3, …..)

VALUES (values1, value2,value3, ………) ;

c. Macro Substitution, this query is used to receive values at runtime.

INSERT INTO table_name VALUES(&columnname1,&columnname2, ….);

2. Update

Update new data into an existing table

Syntax
UPDATE table_name SET column1=value, column2=value2,... WHERE some_column=some_value;

3. Delete Query

The DELETE query is used to delete rows in a table.

Syntax

DELETE FROM table_name WHERE column1=somevalue;

34 | P a g e
DBMS LAB MANUAL – R2023, Department of Information Technology, SMVEC.
Prepared by Mrs.N.Kalaiselvi, Mr.P.Praveenkumar, Mr.A. Ranjeeth, AP/IT, SMVEC

1. INSERT

// To insert the values as rows in the table student

Direct substitution

SQL> INSERT INTO student VALUES (101,'RUPESH','IT', 5,'18-apr-1996', '[email protected]',

'SMVEC');

1 row created.

SQL> INSERT INTO student VALUES (102,'BALA','CSE',7,'07-oct-1995','[email protected]','IIT');

1 row created.

SQL> INSERT INTO student VALUES (104,'HEMESH','IT',5,'23-jul-1996','[email protected]','IIT');

1 row created.

SQL> INSERT INTO student VALUES (106,'SAIVAISHNAVI','CSE', 5, '09-jun-1996',

'[email protected]', 'MIT');

1 row created.

SQL> INSERT INTO student (studentid,sname,department,sem,dob,email_id) VALUES (108,'RISHA','IT',

5,'21-apr-1996','[email protected]'); // (For the purpose of default constraint-Specific Column Insertion)

// To display all the records in the table student

SELECT * FROM student;

SQL> SELECT * FROM student;

STUDENTID SNAME DEPAR SEM DOB EMAIL_ID COLLEGE

---------- ------------------------------ ----- ---------- ----------------------------- --------------------
101 RUPESH IT 5 18-APR-96 [email protected] SMVEC
102 BALA CSE 7 07-OCT-95 [email protected] IIT
104 HEMESH IT 5 23-JUL-96 [email protected] IIT
106 SAIVAISHNAVI CSE 5 09-JUN-96 [email protected] MIT
108 RISHA IT 5 21-APR-96 [email protected] SMVEC

35 | P a g e
DBMS LAB MANUAL – R2023, Department of Information Technology, SMVEC.
Prepared by Mrs.N.Kalaiselvi, Mr.P.Praveenkumar, Mr.A. Ranjeeth, AP/IT, SMVEC

// To insert the values as rows in the table exam

Specific Column Insertion

INSERT INTO exam(examid, studentid, department, mark1, mark2, mark3, mark4, mark5)
VALUES (2222,101,'IT',98,87,83,99,87);

INSERT INTO exam(examid, studentid, department, mark1, mark2, mark3, mark4,mark5)

VALUES(3333,104,'IT',99,82,84,89,100);

INSERT INTO exam(examid, studentid, department, mark1, mark2, mark3, mark4,mark5)

VALUES(4444,108,'IT',92,85,83,91,87);

Macro Substitution

INSERT INTO exam VALUES (&examid, &studentid, &department, &mark1, &mark2, mark3, &mark4,
&mark5)
Enter the value for examid: 5555
Enter the value for studentid: 106
Enter the value for department: CSE
Enter the value for mark1: 82
Enter the value for mark2: 85
Enter the value for mark3: 87
Enter the value for mark4: 91
Enter the value for mark5: 85

old 1: (&examid, &studentid, &department, &mark1, &mark2, mark3, &mark4, &mark5)

new 1: (5555,106,’CSE’,82,85,87,91,85)

// To display all the records in the table exam

SELECT * FROM exam;

EXAMID STUDENTID DEPARTMENT MARK1 MARK2 MARK3 MARK4 MARK5 TOTAL

AVERAGE GRADE
2222 101 IT 98 87 83 99 87 - - -
-
3333 104 IT 99 82 84 89 100 - - -
-
4444 108 IT 92 85 83 91 87 - - -
-
5555 106 CSE 82 85 87 91 85 - - -
-

36 | P a g e
DBMS LAB MANUAL – R2023, Department of Information Technology, SMVEC.
Prepared by Mrs.N.Kalaiselvi, Mr.P.Praveenkumar, Mr.A. Ranjeeth, AP/IT, SMVEC

2. UPDATE

// To change the values in the table student

UPDATE student SET college='MEC' WHERE studentid=108;

1 row(s) updated

// To display the updated value in the table student

SELECT * FROM student;

STUDENTID SNAME DEPARTMENT SEM DOB EMAIL_ID COLLEGE

101 RUPESH IT 5 04/18/1996 [email protected] SMVEC
102 BALA CSE 7 10/07/1995 [email protected] IIT
104 HEMESH IT 5 07/23/1996 [email protected] IIT
106 SAI VAISHNAVI CSE 5 06/09/1996 [email protected] MIT
108 RISHA IT 5 04/21/1996 [email protected] MEC

//To set the total in the table exam

UPDATE exam SET total=(mark1+mark2+mark3+mark4+mark5);

4 row(s) updated

//To display the updated value in the table exam

SELECT * FROM exam;

EXAMID STUDENTID DEPARTMENT MARK1 MARK2 MARK3 MARK4 MARK5 TOTAL

AVERAGE GRADE
2222 101 IT 98 87 83 99 87 454 - -
-
3333 104 IT 99 82 84 89 100 454 - -
-
4444 108 IT 92 85 83 91 87 438 - -
-
5555 106 CSE 82 85 87 91 85 430 - -
-

//To set the average in the table exam

UPDATE exam SET average=total/5;

37 | P a g e
DBMS LAB MANUAL – R2023, Department of Information Technology, SMVEC.
Prepared by Mrs.N.Kalaiselvi, Mr.P.Praveenkumar, Mr.A. Ranjeeth, AP/IT, SMVEC

//To display the updated value in the table exam

SELECT * FROM exam;

EXAMID STUDENTID DEPARTMENT MARK1 MARK2 MARK3 MARK4 MARK5 TOTAL AVERAGE
GRADE
2222 101 IT 98 87 83 99 87 454 90.8 -
- -
3333 104 IT 99 82 84 89 100 454 90.8 -
- -
4444 108 IT 92 85 83 91 87 438 87.6 -
- -
5555 106 CSE 82 85 87 91 85 430 86 -
- -
//To set the grade in the table exam

UPDATE exam SET grade='S' WHERE average>95;

UPDATE exam SET grade='A' WHERE average<=95 AND average>90;
UPDATE exam SET grade='B' WHERE average<=90 AND average>85;
UPDATE exam SET grade='C' WHERE average<=85 AND average>80;
UPDATE exam SET grade='D' WHERE average<=80 AND average>75;
UPDATE exam SET grade='F' WHERE average<75;

//To display the updated values in the table exam

SELECT * FROM exam;

EXAMID STUDENTID DEPARTMENT MARK1 MARK2 MARK3 MARK4 MARK5 TOTAL AVERAGE
GRADE
2222 101 IT 98 87 83 99 87 454 90.8
A
3333 104 IT 99 82 84 89 100 454 90.8
A
4444 108 IT 92 85 83 91 87 438 87.6
B
5555 106 CSE 82 85 87 91 85 430 86
B

38 | P a g e
DBMS LAB MANUAL – R2023, Department of Information Technology, SMVEC.
Prepared by Mrs.N.Kalaiselvi, Mr.P.Praveenkumar, Mr.A. Ranjeeth, AP/IT, SMVEC

3. DELETE

//To delete a particular record whose the exam id is 2222

DELETE FROM exam WHERE examid=2222;

//To display the records in table exam after deleted a record

SELECT * FROM exam;

EXAMID STUDENTID DEPARTMENT MARK1 MARK2 MARK3 MARK4 MARK5 TOTAL AVERAGE
GRADE
3333 104 IT 99 82 84 89 100 454 90.8
A
4444 108 IT 92 85 83 91 87 438 87.6
B
5555 106 CSE 82 85 87 91 85 430 86
B

//To inserted the same record in table exam for further use

INSERT INTO exam(examid, studentid, department, mark1, mark2, mark3,

mark4,mark5,total,average,grade) VALUES (2222,101,'IT',98,87,83,99,87, 454,90.8,’A’)

//To display the updated values in the table exam

SELECT * FROM exam;

EXAMID STUDENTID DEPARTMENT MARK1 MARK2 MARK3 MARK4 MARK5 TOTAL AVERAGE
GRADE
2222 101 IT 98 87 83 99 87 454 90.8
A
3333 104 IT 99 82 84 89 100 454 90.8
A
4444 108 IT 92 85 83 91 87 438 87.6
B
5555 106 CSE 82 85 87 91 85 430 86
B

Result:
Thus all the above DML SQL commands have been executed successfully and the output was
verified.

39 | P a g e
DBMS LAB MANUAL – R2023, Department of Information Technology, SMVEC.
Prepared by Mrs.N.Kalaiselvi, Mr.P.Praveenkumar, Mr.A. Ranjeeth, AP/IT, SMVEC

Ex. No: 4
DATA QUERY PROJECTION STATEMENTS

Data query projection refers to the process of selecting specific columns or expressions from tables or
views. Projection statements are primarily achieved using the SELECT statement, which allows you to
specify the columns or expressions you want to retrieve from the database.

//Consider the following table of student

CREATE TABLE student

(
studentID NUMBER PRIMARY KEY,
sname VARCHAR2(30) NOT NULL,
department CHAR(5),
sem NUMBER,
dob DATE,
email_id VARCHAR2(20) UNIQUE,
college VARCHAR2(20) DEFAULT 'SMVEC'
);

// Describe the table student

DESC student;

Column Data Type Length Precision ScalePrimary KeyNullableDefault Comment

STUDENTID NUMBER 22 - - 1 - - -
SNAME VARCHAR2 30 - - - - - -
DEPARTMENT CHAR 5 - - - - - -
SEM NUMBER 22 - - - - - -
DOB DATE 7 - - - - - -
EMAIL_ID VARCHAR2 20 - - - - - -
COLLEGE VARCHAR2 20 - - - - 'SMVEC' -

1. SELECT ALL COLUMNS

SELECT * FROM student;

STUDENTID SNAME DEPARTMENT SEM DOB EMAIL_ID COLLEGE

101 RUPESH IT 5 04-DEC-1996 [email protected] SMVEC
102 BALA CSE 7 10-JAN-1995 [email protected] IIT
104 HEMESH IT 5 07-FEB-1996 [email protected] IIT
106 SAI VAISHNAVI CSE 5 06-OCT-1996 [email protected] MIT
108 RISHA IT 5 04-NOV-1996 [email protected] MEC

40 | P a g e
DBMS LAB MANUAL – R2023, Department of Information Technology, SMVEC.
Prepared by Mrs.N.Kalaiselvi, Mr.P.Praveenkumar, Mr.A. Ranjeeth, AP/IT, SMVEC

2. SELECT MULTICOLUMN

SELECT sname, department, sem FROM student;

SNAME DEPARTMENT SEM

RUPESH IT 5
BALA CSE 7
HEMESH IT 5
SAI VAISHNAVI CSE 5
RISHA IT 5

3. SELECTION WITH ALIAS COLUMN NAME

SELECT sname “Student Name”,department,sem FROM student;

STUDENT NAME DEPARTMENT SEM

RUPESH IT 5
BALA CSE 7
HEMESH IT 5
SAI VAISHNAVI CSE 5
RISHA IT 5

4. SELECTION WITH ARITHMETIC OPERATION

SELECT sname,department,sem+1 “sem” FROM student;

SNAME DEPARTMENT SEM

RUPESH IT 6
BALA CSE 8
HEMESH IT 6
SAI VAISHNAVI CSE 6
RISHA IT 6

5. DISTINCT RECORD SELECTION

// To display the college of the student from the table student by avoiding repeated values.

SELECT DISTINCT college FROM student;

COLLEGE
MEC
MIT
IIT
SMVEC

41 | P a g e
DBMS LAB MANUAL – R2023, Department of Information Technology, SMVEC.
Prepared by Mrs.N.Kalaiselvi, Mr.P.Praveenkumar, Mr.A. Ranjeeth, AP/IT, SMVEC

6. SELECTION WITH CONCATENATION

SELECT sname||studentid FROM student;

SNAME||STUDENTID
RUPESH101
BALA102
HEMESH104
SAIVAISHNAVI106
RISHA108

7. SELECTION WITH WHERE CLAUSE

// To display the records from the table student who belongs to mec college.

SELECT * FROM student WHERE college='MEC';

STUDENTID SNAME DEPARTMENT SEM DOB EMAIL_ID COLLEGE

108 RISHA IT 5 04-NOV-1996 [email protected] MEC

// To display the student id and student name from the table student who belongs to IIT college

SELECT studentid, sname FROM student WHERE college='IIT';

STUDENTID SNAME
102 BALA
104 HEMESH

// To display the student name and department from the table student who belongs to 5th sem.

SELECT sname, department FROM student WHERE sem=5;

SNAME DEPARTMENT
RUPESH IT
HEMESH IT
SAI VAISHNAVI CSE
RISHA IT

Result:
Thus all the above Data Query Projection SQL commands have been executed successfully and the
output was verified.

42 | P a g e
DBMS LAB MANUAL – R2023, Department of Information Technology, SMVEC.
Prepared by Mrs.N.Kalaiselvi, Mr.P.Praveenkumar, Mr.A. Ranjeeth, AP/IT, SMVEC

Ex. No: 5
DATA QUERY SELECTION STATEMENT
Data query selection involves using SQL (Structured Query Language) to retrieve specific data from one or
more tables. The primary statement used for data query selection is the SELECT statement, which allows
you to specify the columns to retrieve, the tables to retrieve them from, and any conditions that filter the
rows returned.

SELECTION COMMANDS

• Between…and
• In
• Not in
• Like
• Relational Operators
• Logical Operators

//Consider the following table of student

CREATE TABLE student

(
studentID NUMBER PRIMARY KEY,
sname VARCHAR2(30) NOT NULL,
department CHAR(5),
sem NUMBER,
dob DATE,
email_id VARCHAR2(20) UNIQUE,
college VARCHAR2(20) DEFAULT 'SMVEC'
);

// Describe the table student

DESC student;

Column Data Type Length Precision ScalePrimary KeyNullableDefault Comment

STUDENTID NUMBER 22 - - 1 - - -
SNAME VARCHAR2 30 - - - - - -
DEPARTMENT CHAR 5 - - - - - -
SEM NUMBER 22 - - - - - -
DOB DATE 7 - - - - - -
EMAIL_ID VARCHAR2 20 - - - - - -
COLLEGE VARCHAR2 20 - - - - 'SMVEC' -

43 | P a g e
DBMS LAB MANUAL – R2023, Department of Information Technology, SMVEC.
Prepared by Mrs.N.Kalaiselvi, Mr.P.Praveenkumar, Mr.A. Ranjeeth, AP/IT, SMVEC

//Select

SELECT * FROM student;

STUDENTID SNAME DEPARTMENT SEM DOB EMAIL_ID COLLEGE

101 RUPESH IT 5 04-DEC-199 [email protected] SMVEC
102 BALA CSE 7 10-JAN-1995 [email protected] IIT
104 HEMESH IT 5 07-FEB-1996 [email protected] IIT
106 SAI VAISHNAVI CSE 5 06-OCT-1996 [email protected] M IT
108 RISHA IT 5 04-NOV-1996 [email protected] MEC

// To display the student id, student name and department of the student whose the semester in
between 5 and 6

SELECT studentid,sname,department FROM student WHERE sem BETWEEN 5 AND 6;

STUDENTID SNAME DEPARTMENT

101 RUPESH IT
104 HEMESH IT
106 SAIVAISHNAVI CSE
108 RISHA IT

// To display the student id, student name and department of the student whose in CSE and IT
department

SELECT studentid,sname,department FROM student WHERE department IN (‘CSE’,’IT’);

STUDENTID SNAME DEPARTMENT

101 RUPESH IT
102 BALA CSE
104 HEMESH IT
106 SAIVAISHNAVI CSE
108 RISHA IT

44 | P a g e
DBMS LAB MANUAL – R2023, Department of Information Technology, SMVEC.
Prepared by Mrs.N.Kalaiselvi, Mr.P.Praveenkumar, Mr.A. Ranjeeth, AP/IT, SMVEC

// To display the student id, student name and department of the student whose not in CSE
department

SELECT studentid,sname,department FROM student WHERE department NOT IN ‘CSE’;

STUDENTID SNAME DEPARTMENT

101 RUPESH IT
104 HEMESH IT
108 RISHA IT

// To display the student id and student name of the student whose name starts letters 'R' from the
table student

SELECT studentid, sname FROM student WHERE sname LIKE 'R%';

STUDENTID SNAME
101 RUPESH
108 RISHA

// To display the student id and student name of the student whose name end with 'H' from the table
student

SELECT studentid, sname FROM student WHERE sname LIKE '%H';

STUDENTID SNAME
101 RUPESH
104 HEMESH

// To display the student id and student name of the student whose name has letters 'sh' from the
table
student

SELECT studentid, sname FROM student WHERE sname LIKE '%SH%';

STUDENTID SNAME
101 RUPESH
104 HEMESH
106 VAISHU
108 RISHA

45 | P a g e
DBMS LAB MANUAL – R2023, Department of Information Technology, SMVEC.
Prepared by Mrs.N.Kalaiselvi, Mr.P.Praveenkumar, Mr.A. Ranjeeth, AP/IT, SMVEC

// To display the student id, student name of the student whose the student id greater than 105

SELECT studentid, sname FROM student WHERE studentid > 105;

STUDENTID SNAME
106 SAIVAISHNAVI
108 RISHA

// To display the student id, student name of the student whose the student id greater than 105 and cse
department

SELECT studentid, sname FROM student WHERE studentid > 105 AND department=’CSE’;

STUDENTID SNAME
106 SAIVAISHNAVI

Result:
Thus all the above Data Query Selection SQL commands have been executed successfully and the
output was verified.

46 | P a g e
DBMS LAB MANUAL – R2023, Department of Information Technology, SMVEC.
Prepared by Mrs.N.Kalaiselvi, Mr.P.Praveenkumar, Mr.A. Ranjeeth, AP/IT, SMVEC

Ex. No: 6 AGGREGATE FUNCTIONS

Aggregate functions are SQL functions that perform a calculation on a set of values and return a single
value. These functions are commonly used with SELECT statements in conjunction with the GROUP BY
clause to summarize data across rows.
➢ Order By
➢ Group By
➢ Aggregate Functions : MAX, MIN, SUM, AVG, COUNT

//Consider the following table of student

CREATE TABLE student

(
studentID NUMBER PRIMARY KEY,
sname VARCHAR2(30) NOT NULL,
department CHAR(5),
sem NUMBER,
dob DATE,
email_id VARCHAR2(20) UNIQUE,
college VARCHAR2(20) DEFAULT 'SMVEC'
);

// Describe the table student

DESC student;

Column Data Type Length Precision ScalePrimary KeyNullableDefault Comment

STUDENTID NUMBER 22 - - 1 - - -
SNAME VARCHAR2 30 - - - - - -
DEPARTMENT CHAR 5 - - - - - -
SEM NUMBER 22 - - - - - -
DOB DATE 7 - - - - - -
EMAIL_ID VARCHAR2 20 - - - - - -
COLLEGE VARCHAR2 20 - - - - 'SMVEC' -

SELECT * FROM student;

STUDENTID SNAME DEPARTMENT SEM DOB EMAIL_ID COLLEGE

101 RUPESH IT 5 04-DEC-1996 [email protected] SMVEC
102 BALA CSE 7 10-JAN-1995 [email protected] IIT
104 HEMESH IT 5 07-FEB-1996 [email protected] IIT
106 SAI VAISHNAVI CSE 5 06-OCT-1996 [email protected] MIT
108 RISHA IT 5 04-NOV-1996 [email protected] MEC
47 | P a g e
DBMS LAB MANUAL – R2023, Department of Information Technology, SMVEC.
Prepared by Mrs.N.Kalaiselvi, Mr.P.Praveenkumar, Mr.A. Ranjeeth, AP/IT, SMVEC

1. ORDER BY

// To display the department, sem and student name from the table student based on department in
ascending order.

SELECT department, sem, sname FROM student ORDER BY department;

DEPARTMENT SEM SNAME

CSE 5 SAI VAISHNAVI
CSE 7 BALA
IT 5 RISHA
IT 5 RUPESH
IT 5 HEMESH

// To display the department, sem and student name from the table student based on department in
descending order.

SELECT department, sem, sname FROM student ORDER BY department DESC, sem DESC, sname
DESC;

DEPARTMENT SEM SNAME

IT 5 RUPESH
IT 5 RISHA
IT 5 HEMESH
CSE 7 BALA
CSE 5 SAI VAISHNAVI

2. GROUP BY

// To displays the total value group by department

SELECT department, SUM(total) AS SUM_DEPARTMENT FROM exam GROUP BY department;

DEPARTMENT SUM_DEPARTMENT
CSE 430
IT 1346

48 | P a g e
DBMS LAB MANUAL – R2023, Department of Information Technology, SMVEC.
Prepared by Mrs.N.Kalaiselvi, Mr.P.Praveenkumar, Mr.A. Ranjeeth, AP/IT, SMVEC

3. AGGREGATE FUNCTIONS

// 1. COUNT - displays total number of rows

SELECT COUNT(examid) AS STUDENTS_REGISTERED FROM exam;

STUDENTS_REGISTERED

// 2. MAX - displays the maximum value

SELECT MAX(average) AS RANK_1 FROM exam;

RANK_1
90.8

// 3. MIN - displays the minimum value

SELECT MIN(average) AS LAST_RANK FROM exam;

LAST_RANK
86

// 4. SUM - displays the total value

SELECT department, SUM(total) AS SUM_DEPARTMENT FROM exam GROUP BY department;

DEPARTMENT SUM_DEPARTMENT
CSE 430
IT 1346

// 5. AVG - displays the average value

SELECT department, AVG(total) AS AVERAGE FROM exam GROUP BY department;

DEPARTMENT AVERAGE
IT 448.66667
CSE 430

Result:
Thus all the above Aggregate function SQL commands has been executed successfully and the
output were verified.

49 | P a g e
DBMS LAB MANUAL – R2023, Department of Information Technology, SMVEC.
Prepared by Mrs.N.Kalaiselvi, Mr.P.Praveenkumar, Mr.A. Ranjeeth, AP/IT, SMVEC

Ex.No: 7
SQL JOINS
Joins are used to combine rows from two or more tables based on a related column between them.
Joins allow you to retrieve data that spans multiple tables, providing a way to link information
together based on common attributes or relationships. Oracle supports different types of joins,
including inner joins, outer joins (left outer join, right outer join, full outer join), cross joins, and self
joins.
Join Types

1. INNER JOIN
2. OUTER JOIN
a. LEFT OUTER JOIN
b. RIGHT OUTER JOIN
c. FULL OUTER JOIN
3. SELF JOIN
4. EQUI JOIN
5. NON EQUI JOIN

// Table Creation - cseitstudent

CREATE TABLE cseitstudent

(
studentID NUMBER PRIMARY KEY,
sname VARCHAR(30),
department CHAR(5),
sem NUMBER
);

// Table Creation - placement

CREATE TABLE placement

(
PlacementID NUMBER PRIMARY KEY,
StudentID NUMBER,
department CHAR(5),
Company VARCHAR2(30),
salary NUMBER
);
50 | P a g e
DBMS LAB MANUAL – R2023, Department of Information Technology, SMVEC.
Prepared by Mrs.N.Kalaiselvi, Mr.P.Praveenkumar, Mr.A. Ranjeeth, AP/IT, SMVEC

// Inserting values into cseitstudent table

INSERT INTO cseitstudent (studentID, sname, department, sem) VALUES(101,'reema', 'IT',5);

INSERT INTO cseitstudent (studentID, sname, department, sem) VALUES(102,'reenu', 'IT',3);

INSERT INTO cseitstudent (studentID, sname, department, sem) VALUES(103,'sheela', 'CSE',3);

INSERT INTO cseitstudent (studentID, sname, department, sem) VALUES(104,'nirmal', 'IT',3);

INSERT INTO cseitstudent (studentID, sname, department, sem) VALUES(105,'eshwar', 'CSE',5);

// Inserting values into placement table

INSERT INTO placement VALUES(1, 104, 'IT', 'infosys', 25000);

INSERT INTO placement VALUES(2, 105, 'CSE', 'Wipro', 22000);

INSERT INTO placement VALUES(3, 204, 'MECH', 'HYUNDAI', 30000);

INSERT INTO placement VALUES(4, 102, 'IT', 'infosys', 25000);

INSERT INTO placement VALUES(5, 103, 'CSE', 'infosys', 25000);

// Display the values in the table as rows

SELECT * FROM cseitstudent;

STUDENTID SNAME DEPARTMENT SEM

101 reema IT 5
102 reenu IT 3
103 sheela CSE 3
104 nirmal IT 3
105 eshwar CSE 5

SELECT * FROM placement;

PLACEMENTID STUDENTID DEPARTMENT COMPANY SALARY

1 104 IT infosys 25000
2 105 CSE Wipro 22000
3 204 MECH HYUNDAI 30000
4 102 IT Infosys 25000
5 103 CSE Infosys 25000
51 | P a g e
DBMS LAB MANUAL – R2023, Department of Information Technology, SMVEC.
Prepared by Mrs.N.Kalaiselvi, Mr.P.Praveenkumar, Mr.A. Ranjeeth, AP/IT, SMVEC

// INNER JOIN

SELECT *
FROM cseitstudent
INNER JOIN Placement
ON cseitstudent.studentID=placement.StudentID;

STUDENTID SNAME DEPARTMENT SEM PLACEMENTID STUDENTID

DEPARTMENT COMPANY SALARY
104 nirmal IT 3 1 104 IT infosys 25000
105 eshwar CSE 5 2 105 CSE Wipro 22000
102 reenu IT 3 4 102 IT infosys 25000
103 sheela CSE 3 5 103 CSE infosys 25000

SELECT cseitstudent.studentID, cseitstudent.sname,placement.company, placement.salary

FROM cseitstudent
INNER JOIN placement
ON cseitstudent.studentID=placement.studentID;

STUDENTID SNAME COMPANY SALARY

104 nirmal infosys 25000
105 eshwar Wipro 22000
102 reenu infosys 25000
103 sheela infosys 25000

//LEFT OUTER JOIN

SELECT *
FROM cseitstudent
LEFT OUTER JOIN placement
ON cseitstudent.studentID=placement.studentID;

STUDENTID SNAME DEPARTMENT SEM PLACEMENTID STUDENTID

DEPARTMENT COMPANY SALARY
104 nirmal IT 3 1 104 IT infosys 25000
105 eshwar CSE 5 2 105 CSE Wipro 22000
102 reenu IT 3 4 102 IT infosys 25000
103 sheela CSE 3 5 103 CSE infosys 25000
101 reema IT 5 - - - - -

52 | P a g e
DBMS LAB MANUAL – R2023, Department of Information Technology, SMVEC.
Prepared by Mrs.N.Kalaiselvi, Mr.P.Praveenkumar, Mr.A. Ranjeeth, AP/IT, SMVEC

SELECT cseitstudent.sname,placement.placementID, placement.company

FROM cseitstudent
LEFT OUTER JOIN placement
ON cseitstudent.studentID=placement.studentID;

SNAME PLACEMENTID COMPANY

nirmal 1 infosys
eshwar 2 Wipro
reenu 4 infosys
sheela 5 infosys
reema - -

//RIGHT OUTER JOIN

SELECT *
FROM cseitstudent
RIGHT OUTER JOIN placement
ON cseitstudent.studentID=placement.studentID;

STUDENTID SNAME DEPARTMENT SEM PLACEMENTID STUDENTID

DEPARTMENT COMPANY SALARY
102 reenu IT 3 4 102 IT infosys 25000
103 sheela CSE 3 5 103 CSE infosys 25000
104 nirmal IT 3 1 104 IT infosys 25000
105 eshwar CSE 5 2 105 CSE Wipro 22000
- - - - 3 204 MECH HYUNDAI 30000

SELECT cseitstudent.sname,placement.placementID, placement.company

FROM cseitstudent
RIGHT OUTER JOIN placement
ON cseitstudent.studentID = placement.studentID;

SNAME PLACEMENTID COMPANY

reenu 4 infosys
sheela 5 infosys
nirmal 1 infosys
eshwar 2 Wipro
3 HYUNDAI

53 | P a g e
DBMS LAB MANUAL – R2023, Department of Information Technology, SMVEC.
Prepared by Mrs.N.Kalaiselvi, Mr.P.Praveenkumar, Mr.A. Ranjeeth, AP/IT, SMVEC

//FULL OUTER JOIN

SELECT *
FROM cseitstudent
FULL OUTER JOIN placement
ON cseitstudent.studentID=placement.studentID;

STUDENTID SNAME DEPARTMENT SEM PLACEMENTID STUDENTID

DEPARTMENT COMPANY SALARY
104 nirmal IT 3 1 104 IT infosys 25000
105 eshwar CSE 5 2 105 CSE Wipro 22000
- - - - 3 204 MECH HYUNDAI 30000
102 reenu IT 3 4 102 IT infosys 25000
103 sheela CSE 3 5 103 CSE infosys 25000
101 reema IT 5 - - - - -

SELECT cseitstudent.sname,placement.placementID, placement.company

FROM cseitstudent
FULL OUTER JOIN placement
ON cseitstudent.studentID=placement.studentID;

SNAME PLACEMENTID COMPANY

nirmal 1 infosys
eshwar 2 Wipro
- 3 HYUNDAI
reenu 4 infosys
sheela 5 infosys
reema - -

//SELF JOIN

Returns rows by comparing the values of the same table.

//TABLE CREATION

CREATE TABLE employee

(
empid NUMBER,
empname VARCHAR2(25),
reportingid NUMBER
);
54 | P a g e
DBMS LAB MANUAL – R2023, Department of Information Technology, SMVEC.
Prepared by Mrs.N.Kalaiselvi, Mr.P.Praveenkumar, Mr.A. Ranjeeth, AP/IT, SMVEC

//INSERTING VALUES IN THE TABLE

INSERT INTO employee VALUES(1, 'Principal', null);

INSERT INTO employee VALUES(2, 'HOD', 1);
INSERT INTO employee VALUES(3, 'PO', 1);
INSERT INTO employee VALUES(4, 'Staff', 2);
INSERT INTO employee VALUES(5, 'Non Teaching Staff', 2);

//DISPLAYS VALUES IN THE TABLE

SELECT * FROM employee;

EMPID EMPNAME REPORTINGID

1 Principal -
2 HOD 1
3 PO 1
4 Staff 2
5 Non Teaching Staff 2

SELECT e1.empid, e1.empname, e2.empname AS HeadName

FROM employee e1, employee e2
WHERE e1.reportingid=e2.empid;

EMPID EMPNAME HEADNAME

2 HOD Principal
3 PO Principal
4 Staff HOD
5 Non Teaching Staff HOD

//EQUI JOIN

SELECT * FROM cseitstudent, placement WHERE cseitstudent.studentID=placement.studentID;

STUDENTID SNAME DEPARTMENT SEM PLACEMENTID STUDENTID DEPARTMENT

COMPANY SALARY
104 nirmal IT 3 1 104 IT infosys 25000
105 eshwar CSE 5 2 105 CSE Wipro 22000
102 reenu IT 3 4 102 IT infosys 25000
103 sheela CSE 3 5 103 CSE infosys 25000

55 | P a g e
DBMS LAB MANUAL – R2023, Department of Information Technology, SMVEC.
Prepared by Mrs.N.Kalaiselvi, Mr.P.Praveenkumar, Mr.A. Ranjeeth, AP/IT, SMVEC

// NON EQUI JOIN

SELECT cseit.studentID, cseit.sname FROM cseitstudent cseit, placement placed

WHERE cseit.studentID>placed.studentID;

STUDENTID SNAME
105 eshwar
105 eshwar
105 eshwar
104 nirmal
104 nirmal
103 sheela

Result:

Thus all the above Joins SQL commands have been executed successfully and the output was
verified.

56 | P a g e
DBMS LAB MANUAL – R2023, Department of Information Technology, SMVEC.
Prepared by Mrs.N.Kalaiselvi, Mr.P.Praveenkumar, Mr.A. Ranjeeth, AP/IT, SMVEC

Ex.No: 8
BUILT IN FUNCTIONS

Built-in functions provide a wide range of capabilities to manipulate and operate on data within SQL
queries. These functions can be categorized into several types based on their purposes, including string
functions, numeric functions, date functions, conversion functions, and aggregate functions.

DUAL TABLE:
The DUAL table is a special one-row, one-column table present by default. It serves a variety of purposes,
primarily acting as a placeholder for performing calculations or querying system-level functions without
needing to specify an actual table. Here’s a detailed explanation of the DUAL table and its usage:

Purpose of the DUAL Table

1. Evaluating Expressions: It allows you to evaluate expressions or functions that do not depend on
any table. For example, you can use it to perform simple arithmetic or string operations.
2. Querying Pseudo columns: Certain Oracle SQL functions and pseudo columns, such as
SYSDATE, USER, and VERSION, do not require a table to be queried. You can query them
directly from DUAL.
3. Testing SQL Syntax: It can be used to test the syntax of SQL statements without affecting any
actual data in tables.

Structure of the DUAL Table

The DUAL table has the following structure:
• Column Name: DUMMY
• Data Type: VARCHAR2(1)

1. String Functions: String functions in Oracle are used to manipulate character strings, such as extracting
substrings, concatenating strings, converting case, and trimming spaces.

SUBSTR: Extracts a substring from a string.

SELECT SUBSTR('Hello World', 1, 5) AS result FROM dual; -- Outputs 'Hello'

CONCAT or ||: Concatenates two strings.

SELECT 'Hello' || ' ' || 'World' AS result FROM dual; -- Outputs 'Hello World'

UPPER: Converts a string to uppercase.

SELECT UPPER('hello') AS result FROM dual; -- Outputs 'HELLO'

LOWER: Converts a string to lowercase.

SELECT LOWER('HELLO') AS result FROM dual; -- Outputs 'hello'

TRIM: Removes leading or trailing characters (or spaces by default) from a string.
SELECT TRIM(' hello ') AS result FROM dual; -- Outputs 'hello'
57 | P a g e
DBMS LAB MANUAL – R2023, Department of Information Technology, SMVEC.
Prepared by Mrs.N.Kalaiselvi, Mr.P.Praveenkumar, Mr.A. Ranjeeth, AP/IT, SMVEC

LENGTH: The LENGTH function returns the number of characters in a string.

SELECT LENGTH('Hello') AS result FROM dual; -- Outputs 5

INSTR: The INSTR function searches for a substring within a string and returns its starting position.
SELECT INSTR('Hello World', 'World') AS result FROM dual; -- Outputs 7

REPLACE: The REPLACE function replaces occurrences of a substring within a string with another
substring.
SELECT REPLACE('Hello World', 'World', 'Universe') AS result FROM dual; -- Outputs 'Hello Universe'

INITCAP: The INITCAP function capitalizes the first letter of each word in a string and converts all
other letters to lowercase.
SELECT INITCAP('hello world') AS result FROM dual; -- Outputs 'Hello World'

LPAD and RPAD: These functions pad a string with a specified character (or space by default) to
reach a desired length.
SELECT LPAD('Hello', 10, '*') AS result FROM dual; -- Outputs '*****Hello'
SELECT RPAD('Hello', 10, '*') AS result FROM dual; -- Outputs 'Hello*****'
LTRIM and RTRIM: These functions remove leading (left) or trailing (right) characters (or spaces
by default) from a string.
SELECT LTRIM(' Hello ') AS result FROM dual; -- Outputs 'Hello '
SELECT RTRIM(' Hello ') AS result FROM dual; -- Outputs ' Hello'

ASCII and CHR: ASCII: Returns the ASCII value of the first character in a string.
SELECT ASCII('A') AS result FROM dual; -- Outputs 65

CHR: Returns the character corresponding to an ASCII code.

SELECT CHR(65) AS result FROM dual; -- Outputs 'A'

TRANSLATE: The TRANSLATE function replaces characters in a string with specified characters.
SELECT TRANSLATE('Hello', 'elo', 'abc') AS result FROM dual; -- Outputs 'Habbc'

2. Numeric Functions: Numeric functions in Oracle are used to perform operations on numeric data types,
such as rounding numbers, calculating absolute values, and generating random numbers.

ROUND: Rounds a number to a specified number of decimal places.

SELECT ROUND(3.14159, 2) AS result FROM dual; -- Outputs 3.14

ABS: Returns the absolute value of a number.

SELECT ABS(-10) AS result FROM dual; -- Outputs 10

MOD: Returns the remainder of a division operation.

SELECT MOD(10, 3) AS result FROM dual; -- Outputs 1 (remainder of 10 divided by 3)

58 | P a g e
DBMS LAB MANUAL – R2023, Department of Information Technology, SMVEC.
Prepared by Mrs.N.Kalaiselvi, Mr.P.Praveenkumar, Mr.A. Ranjeeth, AP/IT, SMVEC

CEIL: The CEIL function returns the smallest integer greater than or equal to a specified number.
SELECT CEIL(3.14) AS result FROM dual; -- Outputs 4

FLOOR: The FLOOR function returns the largest integer less than or equal to a specified number.
SELECT FLOOR(3.14) AS result FROM dual; -- Outputs 3

POWER: The POWER function calculates the power of a number.

SELECT POWER(2, 3) AS result FROM dual; -- Outputs 8 (2 raised to the power of 3)

SQRT: The SQRT function calculates the square root of a number.

SELECT SQRT(16) AS result FROM dual; -- Outputs 4 (square root of 16)

TRUNC: The TRUNC function truncates a number to a specified number of decimal places.
SELECT TRUNC(3.14159, 2) AS result FROM dual; -- Outputs 3.14 (truncates to 2 decimal places)

SIGN: The SIGN function returns the sign of a number as -1 for negative, 0 for zero, and 1 for
positive.
SELECT SIGN(-10) AS result FROM dual; -- Outputs -1

ROUND:The ROUND function rounds a number to a specified number of decimal places.

SELECT ROUND(3.14159, 2) AS result FROM dual; -- Outputs 3.14 (rounds to 2 decimal places)

GREATEST: The GREATEST function returns the greatest value from a list of expressions.
SELECT GREATEST(10, 20, 15, 5) AS result FROM dual; -- Outputs 20 (returns the largest value)

LEAST: The LEAST function returns the smallest value from a list of expressions.
SELECT LEAST(10, 20, 15, 5) AS result FROM dual; -- Outputs 5 (returns the smallest value)

EXP: The EXP function returns e raised to the power of a specified number.
SELECT EXP(1) AS result FROM dual; -- Outputs approximately 2.71828 (e raised to the power of 1)

3. Date Functions: Date functions in Oracle are used for manipulating date and time values, such as
extracting parts of a date, calculating differences between dates, and formatting dates.

SYSDATE: Returns the current date and time.

SELECT SYSDATE FROM dual; -- Outputs current date and time

TO_CHAR: Converts a date to a string with a specified format.

SELECT TO_CHAR(SYSDATE, 'DD-MON-YYYY') AS result FROM dual; -- Outputs current date in
'DD-MON-YYYY' format

MONTHS_BETWEEN: Returns the number of months between two dates.

SELECT MONTHS_BETWEEN('01-JAN-2024', '01-JAN-2023') AS result FROM dual; -- Outputs 12
(months between Jan 2024 and Jan 2023)
59 | P a g e
DBMS LAB MANUAL – R2023, Department of Information Technology, SMVEC.
Prepared by Mrs.N.Kalaiselvi, Mr.P.Praveenkumar, Mr.A. Ranjeeth, AP/IT, SMVEC

ADD_MONTHS: The ADD_MONTHS function adds a specified number of months to a date.

SELECT ADD_MONTHS(SYSDATE, 3) AS result FROM dual; -- Adds 3 months to current date

LAST_DAY: The LAST_DAY function returns the last day of the month for a given date.
SELECT LAST_DAY(SYSDATE) AS result FROM dual; -- Returns the last day of the current month

NEXT_DAY: The NEXT_DAY function finds the next specified day of the week after a given date.
SELECT NEXT_DAY(SYSDATE, 'FRIDAY') AS result FROM dual; -- Finds the next Friday after the
current date

EXTRACT: The EXTRACT function retrieves a specific part (year, month, day, hour, minute, etc.)
from a date value.
SELECT EXTRACT(YEAR FROM hire_date) AS hire_year FROM employees; -- Extracts the year from
hire_date column

TRUNC: The TRUNC function truncates a date to a specified unit (day, month, year, etc.), effectively
removing the time portion.
SELECT TRUNC(SYSDATE, 'MM') AS start_of_month FROM dual; -- Truncates current date to start of
the month
SELECT TRUNC(SYSDATE, 'YYYY') AS start_of_year FROM dual; -- Truncates current date to start of
the year

Result:

Thus all the above Built in commands in SQL have been executed successfully and the output was
verified.
60 | P a g e
DBMS LAB MANUAL – R2023, Department of Information Technology, SMVEC.
Prepared by Mrs.N.Kalaiselvi, Mr.P.Praveenkumar, Mr.A. Ranjeeth, AP/IT, SMVEC

Ex.No: 9
SET OPERATIONS

Set operations allow you to combine the results of two or more queries into a single result set. Oracle
supports several set operations: UNION, UNION ALL, INTERSECT, and MINUS. These operations
are useful for combining and comparing datasets from multiple queries.
• UNION: The UNION operator combines the results of two or more queries and removes duplicate
rows from the result set.
• UNION ALL: The UNION ALL operator also combines the results of two or more queries,
including all rows (including duplicates) from each query.
• INTERSECT: The INTERSECT operator returns rows that are common between the results of two
queries. It effectively performs an intersection of two result sets.
• MINUS: The MINUS operator returns rows from the first query that are not present in the result of
the second query. It effectively performs a set difference operation.
// Consider the tables Student

CREATE TABLE student

(
studentID NUMBER PRIMARY KEY,
sname VARCHAR2(30) NOT NULL,
department CHAR(5),
sem NUMBER,
dob DATE,
email_id VARCHAR2(20) UNIQUE,
college VARCHAR2(20) DEFAULT 'SMVEC'
);

// Describe the table student

DESC student;

Column Data Type Length Precision ScalePrimary KeyNullableDefault Comment

STUDENTID NUMBER 22 - - 1 - - -
SNAME VARCHAR2 30 - - - - - -
DEPARTMENT CHAR 5 - - - - - -
SEM NUMBER 22 - - - - - -
DOB DATE 7 - - - - - -
EMAIL_ID VARCHAR2 20 - - - - - -
COLLEGE VARCHAR2 20 - - - - 'SMVEC' -

61 | P a g e
DBMS LAB MANUAL – R2023, Department of Information Technology, SMVEC.
Prepared by Mrs.N.Kalaiselvi, Mr.P.Praveenkumar, Mr.A. Ranjeeth, AP/IT, SMVEC

SELECT * FROM student;

STUDENTID SNAME DEPARTMENT SEM DOB EMAIL_ID COLLEGE

101 RUPESH IT 5 04-DEC-1996 [email protected] SMVEC
102 BALA CSE 7 10-JAN-1995 [email protected] IIT
104 HEMESH IT 5 07-FEB-1996 [email protected] IIT
106 SAI VAISHNAVI CSE 5 06-OCT-1996 [email protected] MIT
108 RISHA IT 5 04-NOV-1996 [email protected] MEC
// Consider the tables Student

CREATE TABLE exam

(
examID NUMBER ,
studentID NUMBER REFERENCES student(studentID),
department CHAR(5) NOT NULL,
mark1 NUMBER CHECK (mark1<=100 and mark1>=0),
mark2 NUMBER CHECK (mark2<=100 and mark2>=0),
mark3 NUMBER CHECK (mark3<=100 and mark3>=0),
mark4 NUMBER CHECK (mark4<=100 and mark4>=0),
mark5 NUMBER CHECK (mark5<=100 and mark5>=0),
total NUMBER,
average NUMBER,
grade CHAR(1)
);

//Describe the table exam

DESC exam;

Column Data Type Length Precision Scale Primary Key Nullable Default Comment
EXAMID NUMBER 22 - - - - - -
STUDENTID NUMBER 22 - - - - - -
DEPARTMENT CHAR 5 - - - - - -
MARK1 NUMBER 22 - - - - - -
MARK2 NUMBER 22 - - - - - -
MARK3 NUMBER 22 - - - - - -
MARK4 NUMBER 22 - - - - - -
MARK5 NUMBER 22 - - - - - -
TOTAL NUMBER 22 - - - - - -
AVERAGE NUMBER 22 - - - - - -
GRADE CHAR 1 - - - - - -

62 | P a g e
DBMS LAB MANUAL – R2023, Department of Information Technology, SMVEC.
Prepared by Mrs.N.Kalaiselvi, Mr.P.Praveenkumar, Mr.A. Ranjeeth, AP/IT, SMVEC

// To display student id using union - displays all values without duplicates from student and exam
SELECT studentid FROM student
UNION
SELECT studentid FROM exam;

STUDENTID
101
102
104
106
108
// To display student id using union all - displays all values with duplicates from student and exam

SELECT studentid FROM student

UNION ALL
SELECT studentid FROM exam;

STUDENTID
101
102
104
106
108
108
104
101
106

// To display student id using intersect - displays common values in both the tables without
duplicates from student and exam

SELECT studentid FROM student

INTERSECT
SELECT studentid FROM exam;

STUDENTID
101
104
106
108

63 | P a g e
DBMS LAB MANUAL – R2023, Department of Information Technology, SMVEC.
Prepared by Mrs.N.Kalaiselvi, Mr.P.Praveenkumar, Mr.A. Ranjeeth, AP/IT, SMVEC

// To display student id using minus - displays the values that minus the student id of student from
exam

SELECT studentid FROM student

MINUS
SELECT studentid FROM exam;

STUDENTID
102

Result:
Thus all the above SQL Set Operation queries has been executed successfully and the output was
verified.

64 | P a g e
DBMS LAB MANUAL – R2023, Department of Information Technology, SMVEC.
Prepared by Mrs.N.Kalaiselvi, Mr.P.Praveenkumar, Mr.A. Ranjeeth, AP/IT, SMVEC

Ex.No: 10

NESTED QUERIES

Nested queries (also known as subqueries) are queries that are embedded within another SQL statement,
allowing you to retrieve data based on the results of another query. Nested queries can be used in SELECT,
INSERT, UPDATE, or DELETE statements.

//Create a table employee1

CREATE TABLE employee1
(
empno NUMBER,
empname VARCHAR2(255),
salary NUMBER,
designation VARCHAR2(20)
);
Q1. Select the employee details whose designation is equivalent to employee RUPESH designation.

SELECT * FROM employee1 WHERE designation =

(SELECT designation FROM employee1 WHERE empname='RUPESH');

EMPNO EMPNAME SALARY DESIGNATION

103 RUPESH 20000 Clerk

Q2:Select the employee details whose salary is less than employee LINGAM salary.

SELECT * FROM employee1 WHERE salary <

(SELECT salary FROM employee1 WHERE empname='LINGAM');

EMPNO EMPNAME SALARY DESIGNATION

103 RUPESH 20000 Clerk
104 BALA 10000 Typist
105 PRAVEEN 15000 Cashier

Q3:Select the studentname and ID who are all placed.

SELECT studentID, sname FROM cseitstudent WHERE studentID IN

(SELECT studentID FROM placement);

STUDENTID SNAME
102 reenu
103 sheela
104 nirmal
105 eshwar
65 | P a g e
DBMS LAB MANUAL – R2023, Department of Information Technology, SMVEC.
Prepared by Mrs.N.Kalaiselvi, Mr.P.Praveenkumar, Mr.A. Ranjeeth, AP/IT, SMVEC

Q4:Selectthe studentname and ID who are not placed.

SELECT studentID, sname FROM cseitstudent WHERE studentID NOT IN

(SELECT studentID FROM placement);

STUDENTID SNAME
101 reema

Q5:Select student name and department who are all placed in the company INFOSYS.

SELECT sname, department FROM cseitstudent WHERE studentID in

(SELECT studentID FROM placement WHERE company='infosys');

SNAME DEPARTMENT
reenu IT
sheela CSE
nirmal IT

Q6:Select student name and department who are not placed in the company INFOSYS.

SELECT sname, department FROM cseitstudent WHERE studentID NOT IN

(SELECT studentID FROM placement WHERE company='infosys');

SNAME DEPARTMENT
eshwar CSE
reema IT

Q7:Select studentID, company, salary whose salary is less than either 23,000 or 28,000.
SELECT studentID, company, salary FROM placement WHERE SALARY < SOME (23000, 28000);

STUDENTID COMPANY SALARY

104 infosys 25000
105 Wipro 22000
102 infosys 25000
103 infosys 25000

Q8:Select studentID, company, salary whose salary is less than both 23,000 and 28,000.

SELECT studentID, company, salary FROM placement WHERE salary < ALL(23000,28000);

STUDENTID COMPANY SALARY

105 Wipro 22000

66 | P a g e
DBMS LAB MANUAL – R2023, Department of Information Technology, SMVEC.
Prepared by Mrs.N.Kalaiselvi, Mr.P.Praveenkumar, Mr.A. Ranjeeth, AP/IT, SMVEC

// EXISTS

Q9:Select the student placement details where any one of them salary is greater than 20,000.

SELECT * FROM placement WHERE EXISTS (

SELECT * FROM placement WHERE salary>20000);

PLACEMENTID STUDENTID DEPAR COMPANY SALARY

1 104 IT infosys 25000
2 105 CSE Wipro 22000
3 204 MECH HYUNDAI 30000
4 102 IT infosys 25000
5 103 CSE infosys 25000

SELECT * FROM employee1 WHERE EXISTS

(SELECT * FROM employee1 WHERE salary>20000 );

EMPNO EMPNAME SALARY DESIGNATION

101 RAM 45000 Manager
102 LINGAM 35000 Asst. Manager
103 RUPESH 20000 Clerk
104 BALA 10000 Typist
105 PRAVEEN 15000 Cashier

// NOT EXISTS

Q10:Select the employee details where any one of them salary is not greater than 20,000.

SELECT * FROM employee1 WHERE

NOT EXISTS (SELECT * FROM employee1 WHERE salary>20000 );

no data found

USING WHERE, HAVING, GROUP BY, ORDER BY

In Oracle SQL, when using nested queries (subqueries) within a SELECT statement that includes HAVING,
WHERE, GROUP BY, and ORDER BY clauses, the order of execution is generally as follows:

1. FROM clause: The data is first retrieved from the tables specified in the FROM clause, including any
joins or subqueries that appear there.
2. WHERE clause: Rows that meet the conditions specified in the WHERE clause are filtered from the
result set obtained from the FROM clause. This reduces the number of rows to be processed further.

67 | P a g e
DBMS LAB MANUAL – R2023, Department of Information Technology, SMVEC.
Prepared by Mrs.N.Kalaiselvi, Mr.P.Praveenkumar, Mr.A. Ranjeeth, AP/IT, SMVEC

3. GROUP BY clause: If a GROUP BY clause is present, the rows are then grouped based on the
columns specified in this clause. Aggregate functions (like SUM, AVG, COUNT, etc.) can be applied to
these groups to calculate summary values.
4. HAVING clause: The HAVING clause filters groups of rows based on the conditions specified,
similar to how the WHERE clause filters individual rows. It operates on the grouped data after the
GROUP BY clause has been applied.
5. SELECT clause: Finally, the SELECT clause specifies which columns or expressions are to be
included in the final result set. This clause operates on the grouped and filtered data obtained from
the previous steps.
6. ORDER BY clause: If an ORDER BY clause is present, the final result set is then sorted according to
the specified criteria.

Example: Retrieve the department IDs and their average salaries from the employees table where the salary
exceeds 50,000. Group the results by department ID and display only those departments where the average
salary is greater than 70,000. Arrange the output in descending order of department IDs.

SELECT department_id, AVG(salary) AS avg_salary

FROM employees
WHERE salary > 50000
GROUP BY department_id
HAVING AVG(salary) > 70000
ORDER BY department_id DESC;

Result:

Thus all the above SQL nested queries has been executed successfully and the output was verified.

68 | P a g e
DBMS LAB MANUAL – R2023, Department of Information Technology, SMVEC.
Prepared by Mrs.N.Kalaiselvi, Mr.P.Praveenkumar, Mr.A. Ranjeeth, AP/IT, SMVEC

Ex.No: 11

VIEWS

In Oracle Database, a view is a virtual table that presents data from one or more underlying tables (or
views) to users. Views are defined by SQL queries and can simplify complex queries, provide data security,
and enhance data integrity by abstracting the underlying structure of tables.
Creating a View
To create a view in Oracle, you use the CREATE VIEW statement followed by a query that defines the
view's structure and data:
CREATE VIEW view_name AS
SELECT column1, column2, ...
FROM table_name
WHERE conditions;

//Consider the placement table

CREATE TABLE placement

(
PlacementID NUMBER PRIMARY KEY,
StudentID NUMBER,
department CHAR(5),
Company VARCHAR2(30),
salary NUMBER
);

SELECT * FROM placement;

PLACEMENTID STUDENTID DEPARTMENT COMPANY SALARY

1 104 IT infosys 25000
2 105 CSE Wipro 22000
3 204 MECH Hyundai 30000
4 102 IT infosys 25000
5 103 CSE infosys 25000

// Simple Example for View

// View creation

CREATE VIEW studview

AS(SELECT studentid,department,company FROM placement
WHERE salary >25000 );

69 | P a g e
DBMS LAB MANUAL – R2023, Department of Information Technology, SMVEC.
Prepared by Mrs.N.Kalaiselvi, Mr.P.Praveenkumar, Mr.A. Ranjeeth, AP/IT, SMVEC

//To displat the records in studview

SELECT * FROM studview;

STUDENTID DEPAR COMPANY

204 MECH HYUNDAI

// To create a view by using inner join of cseitstudent and placement

CREATE VIEW studetails

AS(SELECT cseitstudent.studentID,cseitstudent.sname,cseitstudent.department,
cseitstudent.sem,placement.company,placement.salary
FROM cseitstudent
INNER JOIN placement
ON cseitstudent.studentID=placement.studentID);

// To display the records in studetails view

SELECT * FROM studetails;

STUDENTID SNAME DEPARTMENT SEM COMPANY SALARY

104 nirmal IT 3 infosys 25000
105 eshwar CSE 5 Wipro 22000
102 reenu IT 3 infosys 25000
103 sheela CSE 3 infosys 25000

// To insert a new record in base table of cseitstudent table

INSERT INTO cseitstudent(studentID,sname,department,sem)VALUES(107,'kannan','CSE',5);

// To insert a new record in base table of placement table

INSERT INTO placement(placementID,studentID,department,company,salary)

VALUES(6,107,'CSE','infosys',25000);

// To display the records in cseitstudent table (After inserted a new record)

SELECT * FROM cseitstudent;

STUDENTID SNAME DEPARTMENT SEM

101 reema IT 5
102 reenu IT 3
103 sheela CSE 3
104 nirmal IT 3
105 eshwar CSE 5
106 kannan CSE 5
70 | P a g e
DBMS LAB MANUAL – R2023, Department of Information Technology, SMVEC.
Prepared by Mrs.N.Kalaiselvi, Mr.P.Praveenkumar, Mr.A. Ranjeeth, AP/IT, SMVEC

// To display the records in placement table (After inserted a new record)

SELECT * FROM placement;

PLACEMENTID STUDENTID DEPARTMENT COMPANY SALARY

1 104 IT infosys 25000
2 105 CSE Wipro 22000
3 204 MECH Hyundai 30000
4 102 IT infosys 25000
5 103 CSE infosys 25000
6 106 CSE infosys 25000

// To display the records in placement table (After inserted a new record in base tables)

SELECT * FROM studetails;

STUDENTID SNAME DEPARTMENT SEM COMPANY SALARY

104 nirmal IT 3 infosys 25000
105 eshwar CSE 5 Wipro 22000
102 reenu IT 3 infosys 25000
103 sheela CSE 3 infosys 25000
106 kannan CSE 5 infosys 25000

// To delete a particular record in studetails View

DELETE FROM studetails WHERE studentid=106;

// To display the records in studetails view (After deleted a new record in view)

SELECT * FROM studetails;

STUDENTID SNAME DEPARTMENT SEM COMPANY SALARY

104 nirmal IT 3 infosys 25000
105 eshwar CSE 5 Wipro 22000
102 reenu IT 3 infosys 25000
103 sheela CSE 3 infosys 25000

71 | P a g e
DBMS LAB MANUAL – R2023, Department of Information Technology, SMVEC.
Prepared by Mrs.N.Kalaiselvi, Mr.P.Praveenkumar, Mr.A. Ranjeeth, AP/IT, SMVEC

// To display the records in placement table (After deleted a new record in view)
SELECT * FROM placement;

PLACEMENTID STUDENTID DEPARTMENT COMPANY SALARY

1 104 IT infosys 25000
2 105 CSE Wipro 22000
3 204 MECH Hyundai 30000
4 102 IT infosys 25000
5 103 CSE infosys 25000
//To update a particular record in studetails view

UPDATE studetails SET salary=27500 WHERE studentid=102;

// To display the records in studetails view (After updated a new record in view)

SELECT * FROM studetails;

STUDENTID SNAME DEPARTMENT SEM COMPANY SALARY

104 nirmal IT 3 infosys 25000
105 eshwar CSE 5 Wipro 22000
102 reenu IT 3 infosys 27500
103 sheela CSE 3 infosys 25000

// To display the records in placement table (After deleted a new record in view)

SELECT * FROM placement;

PLACEMENTID STUDENTID DEPARTMENT COMPANY SALARY

1 104 IT infosys 25000
2 105 CSE Wipro 22000
3 204 MECH Hyundai 30000
4 102 IT infosys 27500
5 103 CSE infosys 25000

SQL> DROP VIEW studetails;

View dropped.
SQL> SELECT * FROM studetails;
ERROR at line 1:
ORA-00942: table or view does not exist

Result:
Thus all the above SQL View queries has been executed successfully and the output was verified.
72 | P a g e
DBMS LAB MANUAL – R2023, Department of Information Technology, SMVEC.
Prepared by Mrs.N.Kalaiselvi, Mr.P.Praveenkumar, Mr.A. Ranjeeth, AP/IT, SMVEC

Ex.No. 12
Transaction Control Language (TCL)

TCL commands

• COMMIT
• ROLLBACK
• SAVE POINT

Commit

It tells the DBMS to make permanent changes made to temporary copies of the data updating the
permanent database tables to updated temporary copies.

Syntax:

SQL> COMMIT;

Rollback

It tells the DBMS to undo any changes made to the DBMS after the most recent commit.

Syntax:

SQL> ROLLBACK;

Save point:

Save point are like markers to divide a very lengthy transaction to smaller ones. They are used to
identify a point n transaction to which we can later ROLLBACK. Thus save point is used in conjunction
with ROLLBACK to ROLLBACK portions of the current transaction.

Syntax:

SQL> SAVEPOINT<name>;

//Table creation empp:

CREATE TABLE empp

(
emp_ID NUMBER ,
emp_name VARCHAR2(30) ,
salary NUMBER,
73 | P a g e
DBMS LAB MANUAL – R2023, Department of Information Technology, SMVEC.
Prepared by Mrs.N.Kalaiselvi, Mr.P.Praveenkumar, Mr.A. Ranjeeth, AP/IT, SMVEC

city VARCHAR2(30)
designation VARCHAR2(30),
);

Commit
//Select
SELECT * FROM empp;
EMP_ID EMP_NAME CITY DESIGNATION SALARY
100 James Bangalore Modleader 20000
101 Ganesh Chennai Engineer 21000
102 Priya Calculate Manager 50000
103 Kamal Coimbatore Projlead 40000
104 Vishnu Bombay Ceo 15000
105 Kirthika Chennai Accountant 15000
COMMIT;

- COMMIT complete.

Rollback

//Insert

INSERT INTO EMPP VALUES (106,’John’,’Puducherry’,’manager’,25000);

//Select
SELECT * FROM empp;
EMP_ID EMP_NAME CITY DESIGNATION SALARY
100 James Bangalore Modleader 20000
101 Ganesh Chennai Engineer 21000
102 Priya Calculate Manager 50000
103 Kamal Coimbatore Projlead 40000
104 Vishnu Bombay Ceo 15000
105 Kirthika Chennai Accountant 15000
106 John Puducherry manager 25000

ROLLBACK;

- ROLLBACK complete.

74 | P a g e
DBMS LAB MANUAL – R2023, Department of Information Technology, SMVEC.
Prepared by Mrs.N.Kalaiselvi, Mr.P.Praveenkumar, Mr.A. Ranjeeth, AP/IT, SMVEC

//Select

SELECT * FROM empp;

EMP_ID EMP_NAME CITY DESIGNATION SALARY

100 James Bangalore Modleader 20000
101 Ganesh Chennai Engineer 21000
102 Priya Calculate Manager 50000
103 Kamal Coimbatore Projlead 40000
104 Vishnu Bombay Ceo 15000
105 Kirthika Chennai Accountant 15000

//Commit:

DELETE FROM empp WHERE emp_id = 105;

COMMIT

COMMIT COMPLETE.

//Select

SELECT * FROM empp;

EMP_ID EMP_NAME CITY DESIGNATION SALARY
100 James Bangalore Modleader 20000
101 Ganesh Chennai Engineer 21000
102 Priya Calculate Manager 50000
103 Kamal Coimbatore Projlead 40000
104 Vishnu Bombay Ceo 15000
//Rollback

ROLLBACK

ROLLBACK COMPLETE.

//Select
SELECT * FROM empp;
EMP_ID EMP_NAME CITY DESIGNATION SALARY
100 James Bangalore Modleader 20000
101 Ganesh Chennai Engineer 21000
102 Priya Calculate Manager 50000
103 Kamal Coimbatore Projlead 40000
104 Vishnu Bombay Ceo 15000

75 | P a g e
DBMS LAB MANUAL – R2023, Department of Information Technology, SMVEC.
Prepared by Mrs.N.Kalaiselvi, Mr.P.Praveenkumar, Mr.A. Ranjeeth, AP/IT, SMVEC

Save point:

SAVEPOINT s1;

SAVEPOINT created.

UPDATE empp SET salary = 50000 WHERE emp_id = 104

SAVEPOINT s2;

SAVEPOINT created.

UPDATE empp SET salary = 45000 WHERE emp_id = 103

//Select

SELECT * FROM empp;

EMP_ID EMP_NAME CITY DESIGNATION SALARY
100 James Bangalore Modleader 20000
101 Ganesh Chennai Engineer 21000
102 Priya Calculate Manager 50000
103 Kamal Coimbatore Projlead 45000
104 Vishnu Bombay Ceo 50000

//Rollback

ROLLBACK to s2;

SELECT * FROM empp;

EMP_ID EMP_NAME CITY DESIGNATION SALARY
100 James Bangalore Modleader 20000
101 Ganesh Chennai Engineer 21000
102 Priya Calculate Manager 50000
103 Kamal Coimbatore Projlead 40000
104 Vishnu Bombay Ceo 50000

Result:
Thus all the above TCL commands has been executed successfully and the output was verified.

76 | P a g e
DBMS LAB MANUAL – R2023, Department of Information Technology, SMVEC.
Prepared by Mrs.N.Kalaiselvi, Mr.P.Praveenkumar, Mr.A. Ranjeeth, AP/IT, SMVEC

Ex.No. 13
Data Control Language (DCL)

DCL (Data Control Language) consists of statements that control access to data within the database. DCL
statements primarily deal with privileges, roles, and permissions, allowing database administrators (DBAs)
to manage security and access control effectively. The main DCL statements in Oracle include GRANT,
REVOKE.

DCL Commands

• GRANT: The GRANT statement in Oracle is used to give specific privileges to users or roles.
Privileges can include the ability to perform SELECT, INSERT, UPDATE, DELETE, and other
operations on tables and views, as well as execute procedures and sequences.
• REVOKE: The REVOKE statement in Oracle is used to revoke previously granted privileges from
users or roles.

//Creation of new user student

CREATE USER student IDENTIFIED BY stsmvec;
-user created
//Grant
GRANT DBA TO USER student;
-grant succeeded

//Creation of new user staff

CREATE USER staff IDENTIFIED BY staffsmvec;
-user created
//Grant
GRANT DBA TO USER staff;
-grant succeeded

77 | P a g e
DBMS LAB MANUAL – R2023, Department of Information Technology, SMVEC.
Prepared by Mrs.N.Kalaiselvi, Mr.P.Praveenkumar, Mr.A. Ranjeeth, AP/IT, SMVEC

//Connecting to new user student

CONNECT student
Enter password: stsmvec
//Create table
CREATE TABLE emp
(
empno NUMBER,
empname VARCHAR2(255),
DOB DATE,
salary NUMBER,
designation VARCHAR2(20)
);
// Insert values
INSERT INTO emp(empno,empname, DOB, designation) VALUES(100,'John', ‘21-Apr-1996’,
50000,'Manager');
INSERT INTO emp(empno,empname, DOB, designation) VALUES(101,'Greg', ‘20-July-1994’,
2500,'Clerk');

// Display values
SELECT * FROM emp;
EMPNO EMPNAME DOB SALARY DESIGNATION
100 John 21-Apr-1996 50000 Manager
101 Greg 20-July-1994 2500 Clerk

//Grant the SELECT access of table emp to another user staff

GRANT SELECT ON student.emp TO staff;
GRANT succeeded
//Grant the UPDATE, DELETE access of table emp to another user staff
GRANT UPDATE, DELETE ON student.emp TO staff;
GRANT succeeded

78 | P a g e
DBMS LAB MANUAL – R2023, Department of Information Technology, SMVEC.
Prepared by Mrs.N.Kalaiselvi, Mr.P.Praveenkumar, Mr.A. Ranjeeth, AP/IT, SMVEC

//Grant the ALL access of table emp to another user staff

GRANT ALL ON student.emp TO staff;
GRANT succeeded

//Revoke the ALL access of table emp from the user staff
REVOKE ALL ON student.emp FROM staff;
REVOKE succeeded

Result:
Thus all the above DCL commands has been executed successfully and the output was verified.

79 | P a g e
DBMS LAB MANUAL – R2023, Department of Information Technology, SMVEC.
Prepared by Mrs.N.Kalaiselvi, Mr.P.Praveenkumar, Mr.A. Ranjeeth, AP/IT, SMVEC

PL/SQL (Programming
Language / SQL)

80 | P a g e
DBMS LAB MANUAL – R2023, Department of Information Technology, SMVEC.
Prepared by Mrs.N.Kalaiselvi, Mr.P.Praveenkumar, Mr.A. Ranjeeth, AP/IT, SMVEC

Ex. No: 14
SIMPLE PROGRAMS

PL Block Structure

//Syntax

DECLARE
BEGIN
-----
-----
END;

//To print message

SET SERVEROUTPUT ON
DECLARE
BEGIN
dbms_output.put_line(‘BASIC PLSQL PROGRAM’);
END;
/

//OUTPUT:

BASIC PLSQL PROGRAM

-PL/SQL procedure successfully completed.

//For Loop

DECLARE
n NUMBER
BEGIN
dbms_output.put_line(‘FOR LOOP’);
n:=&n;
for i in 1..n
loop
dbms_output.put_line(i);
end loop;
END;/

81 | P a g e
DBMS LAB MANUAL – R2023, Department of Information Technology, SMVEC.
Prepared by Mrs.N.Kalaiselvi, Mr.P.Praveenkumar, Mr.A. Ranjeeth, AP/IT, SMVEC

//OUTPUT:

Enter value for n: 6

old 5: n:=&n;
new 5: n:=6;
FOR LOOP
1
2
3
4
5
6
PL/SQL procedure successfully completed.

//WHILE LOOP:

DECLARE
n NUMBER;
i NUMBER:=0;
BEGIN
dbms_output.put_line(‘WHILE LOOP’);
n:=&n;
while i<=n
loop
dbms_output.put_line(i);
i:=i+2;
end loop;
END;
/

//OUTPUT:

Enter value for n: 7

old 6: n:=&n;
new 6: n:=7;
WHILE LOOP
0
2
4
6
PL/SQL procedure successfully completed.

82 | P a g e
DBMS LAB MANUAL – R2023, Department of Information Technology, SMVEC.
Prepared by Mrs.N.Kalaiselvi, Mr.P.Praveenkumar, Mr.A. Ranjeeth, AP/IT, SMVEC

//SQL Query results to PL/SQL variables

//Table creation:
CREATE TABLE employee
(
eid INT NOT NULL,
ename VARCHAR(10) NOT NULL,
age INT NOT NULL,
sal NUMBER
);

//Select:

DECLARE
name VARCHAR(10);
salary NUMBER;
BEGIN
SELECT ename, sal INTO name,salary FROM employee
WHERE eid = 102;
dbms_output.put_line(‘Customer : ’ ||name);
dbms_output.put_line(‘ Salary : ’ ||salary);
END;
/

//OUTPUT:
Customer: Raja
Salary : 27000
PL/SQL procedure successfully completed.

Result:
Thus all the above PL/SQL Programs has been executed successfully and the output was verified.

83 | P a g e
DBMS LAB MANUAL – R2023, Department of Information Technology, SMVEC.
Prepared by Mrs.N.Kalaiselvi, Mr.P.Praveenkumar, Mr.A. Ranjeeth, AP/IT, SMVEC

Ex:No: 15
CURSOR

Cursors are used to retrieve and process rows returned by a SQL query. There are two types of cursors
based on their declaration and usage: implicit cursors and explicit cursors.

Implicit Cursor
An implicit cursor is automatically created by Oracle whenever a SQL statement is executed, and there is
no explicit declaration of a cursor by the programmer. Implicit cursors are used for single-row queries,
typically when fetching a single value or row into variables.
Key Points:
• Automatically Managed: Oracle manages implicit cursors automatically behind the scenes.
• Limited Functionality: Suitable for simple queries that retrieve a single row or execute DML
statements.
• Example: Fetching a single employee's salary based on employee ID using SELECT INTO
statement.

Example:
DECLARE
v_salary NUMBER;
BEGIN
SELECT salary INTO v_salary
FROM employees
WHERE employee_id = 101;

DBMS_OUTPUT.PUT_LINE('Employee 101 Salary: ' || v_salary);

END;
/

Explicit Cursor
An explicit cursor is explicitly declared and defined by the programmer to manage multiple rows returned
by a query. Explicit cursors provide more control over the result set and are used when handling queries
that return multiple rows.
Key Points:
• Declared by Programmer: Explicitly declared using DECLARE CURSOR statement before opening
and fetching rows.
• Manual Control: Requires explicit commands (OPEN, FETCH, CLOSE) to manage the cursor's
lifecycle.
• Example: Iterating through all employees' salaries to calculate the total.

84 | P a g e
DBMS LAB MANUAL – R2023, Department of Information Technology, SMVEC.
Prepared by Mrs.N.Kalaiselvi, Mr.P.Praveenkumar, Mr.A. Ranjeeth, AP/IT, SMVEC

//Syntax

DECLARE
CURSOR cursor_name IS SELECT * FROM table_name;
BEGIN
OPEN CURSOR_NAME;
LOOP
-------
-------
END LOOP;
END;

//Table creation:

CREATE TABLE student

(
sid NUMBER,
sname VARCHAR2(10),
dept VARCHAR2(10),
m1 NUMBER,
m2 NUMBER,
m3 NUMBER,
total NUMBER,
cgpa NUMBER,
result VARCHAR2(5)
);

//Select:

85 | P a g e
DBMS LAB MANUAL – R2023, Department of Information Technology, SMVEC.
Prepared by Mrs.N.Kalaiselvi, Mr.P.Praveenkumar, Mr.A. Ranjeeth, AP/IT, SMVEC

//Cursor creation:

DECLARE
CURSOR c IS SELECT * FROM student;
m student%rowtype;
tot NUMBER;
res CHAR(5);
average NUMBER;
BEGIN
OPEN c;
LOOP
FETCH c INTO m;
EXIT WHEN c%NOTFOUND;
tot:=m.m1+m.m2+m.m3;
average:=tot/3;
UPDATE student SET total=tot WHERE sid=m.sid;
UPDATE student SET cgpa=average WHERE sid=m.sid;
IF (m.m1>49 AND m.m2>49 AND m.m3>49 ) THEN
res:=’PASS’;
ELSE
res:=’FAIL’;
END IF;
UPDATE student SET result=res WHERE sid=m.sid;
END LOOP;
END;
/

//OUTPUT:

Result:
Thus all the above PL/SQL Programs has been executed successfully and the output was verified.

86 | P a g e
DBMS LAB MANUAL – R2023, Department of Information Technology, SMVEC.
Prepared by Mrs.N.Kalaiselvi, Mr.P.Praveenkumar, Mr.A. Ranjeeth, AP/IT, SMVEC

Ex:No: 16
TRIGGERS

A trigger is a PL/SQL block that is automatically executed (or fired) when certain events occur on a
particular table or view. Triggers are used to enforce business rules, automate actions, and maintain data
integrity. Here are the key aspects and types of triggers in Oracle:

Key Aspects of Triggers

• Event: Triggers are associated with specific events that occur on a table or view, such as INSERT,
UPDATE, DELETE, or DROP.
• Timing: Triggers can be defined to execute either BEFORE the triggering event (BEFORE
INSERT, BEFORE UPDATE, BEFORE DELETE) or AFTER the event (AFTER INSERT, AFTER
UPDATE, AFTER DELETE).
• Scope: Triggers can be defined at the statement level (executed once per triggering statement) or the
row level (executed once for each affected row).
• Access to Data: Triggers have access to OLD and NEW values of the data being modified:
o OLD: Represents the original values of the data before the event.
o NEW: Represents the new values of the data after the event.

//Syntax - Trigger

CREATE [OR REPLACE ] TRIGGER trigger_name

{BEFORE | AFTER | INSTEAD OF }
{INSERT [OR] | UPDATE [OR] | DELETE}
[OF col_name]
ON table_name
[REFERENCING OLD AS o NEW AS n]
[FOR EACH ROW]
WHEN (condition)
BEGIN

--- sql statements

END;

87 | P a g e
DBMS LAB MANUAL – R2023, Department of Information Technology, SMVEC.
Prepared by Mrs.N.Kalaiselvi, Mr.P.Praveenkumar, Mr.A. Ranjeeth, AP/IT, SMVEC

Example-1: Employee Salary Log

//Table creation - employ:

CREATE TABLE employ

(
eid NUMBER,
ename VARCHAR2(10),
dept VARCHAR2(10),
salary NUMBER,
expe NUMBER
);

//Select:

//Table creation - emplog

CREATE TABLE emplog

eid NUMBER,
ename VARCHAR2(10),
idate DATE,
nsal NUMBER,
);

//Trigger Creation-logsal:

CREATE OR REPLACE TRIGGER logsal

AFTER UPDATE OF salary ON employ
FOR EACH ROW
BEGIN
INSERT INTO emplog(eid,ename,idate,nsal)
VALUES(:NEW.eid,:NEW.ename,SYSDATE,:NEW.salary);
END;
/
Trigger created.
88 | P a g e
DBMS LAB MANUAL – R2023, Department of Information Technology, SMVEC.
Prepared by Mrs.N.Kalaiselvi, Mr.P.Praveenkumar, Mr.A. Ranjeeth, AP/IT, SMVEC

//Update employ table:

UPDATE employ SET salary = salary+2500.0 WHERE expe>3 AND expe<7;

3 rows updated.

// Select-employ:

//Select –emplog:

Example 2: Inventory System

//Table creation:

CREATE TABLE inventory

(
pno NUMBER,
pname VARCHAR2(10),
quan NUMBER,
price NUMBER
);

89 | P a g e
DBMS LAB MANUAL – R2023, Department of Information Technology, SMVEC.
Prepared by Mrs.N.Kalaiselvi, Mr.P.Praveenkumar, Mr.A. Ranjeeth, AP/IT, SMVEC

// Add Records into the Inventory Table

//Select:

//Table creation:

CREATE TABLE purlog

(
pno NUMBER,
pdate DATE,
quan NUMBER
);

//Trigger Creation –purchase:

CREATE OR REPLACE TRIGGER purchase AFTER INSERT ON purlog
FOR EACH ROW
DECLARE
oldquan NUMBER;
quan NUMBER;
newquan number;
BEGIN
SELECT quan INTO oldquan FROM inventory WHERE pno=:NEW.pno;
quan:=:NEW.quan;
newquan:=oldquan+quan;
UPDATE inventory SET quan=newquan WHERE pno=:NEW.pno;
END;
/
Trigger created.

90 | P a g e
DBMS LAB MANUAL – R2023, Department of Information Technology, SMVEC.
Prepared by Mrs.N.Kalaiselvi, Mr.P.Praveenkumar, Mr.A. Ranjeeth, AP/IT, SMVEC

//Select:

Result:
Thus all the above PL/SQL Programs has been executed successfully and the output was verified.

91 | P a g e
DBMS LAB MANUAL – R2023, Department of Information Technology, SMVEC.
Prepared by Mrs.N.Kalaiselvi, Mr.P.Praveenkumar, Mr.A. Ranjeeth, AP/IT, SMVEC

Content beyond the Syllabus

92 | P a g e
DBMS LAB MANUAL – R2023, Department of Information Technology, SMVEC.
Prepared by Mrs.N.Kalaiselvi, Mr.P.Praveenkumar, Mr.A. Ranjeeth, AP/IT, SMVEC

Ex No: 17

STUDENT INFORMATION SYSTEM USING C#.NET AND ORACLE

//Table creation

CREATE TABLE exam

(
id NUMBER,
name VARCHAR2(15),
gen VARCHAR2(6),
dep VARCHAR2(10),
yr NUMBER,
sems VARCHAR2(10),
sub1 NUMBER,
sub2 NUMBER,
sub3 NUMBER,
tot NUMBER,
cg NUMBER,
rest VARCHAR2(10)
);

//Form design

93 | P a g e
DBMS LAB MANUAL – R2023, Department of Information Technology, SMVEC.
Prepared by Mrs.N.Kalaiselvi, Mr.P.Praveenkumar, Mr.A. Ranjeeth, AP/IT, SMVEC

//Code

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using Oracle.DataAccess.Client;
namespace exam
{
public partial class Form1 : Form
{
OracleConnection conn = new OracleConnection("Data Source=XE;User
Id=system;Password=password;");
OracleCommand cmd;

public Form1()
{
InitializeComponent();
}

private void Form1_Load(object sender, EventArgs e)

{
dept.Items.Add("IT");
dept.Items.Add("CSE");
dept.Items.Add("ECE");
dept.Items.Add("EEE");
dept.Items.Add("MECH");
sem.Items.Add("I");
sem.Items.Add("II");
sem.Items.Add("III");
sem.Items.Add("IV");
sem.Items.Add("V");
sem.Items.Add("VI");
sem.Items.Add("VII");
sem.Items.Add("VIII");
year.Items.Add("1");
year.Items.Add("2");
year.Items.Add("3");
year.Items.Add("4");
}
private void save_Click(object sender, EventArgs e)
94 | P a g e
DBMS LAB MANUAL – R2023, Department of Information Technology, SMVEC.
Prepared by Mrs.N.Kalaiselvi, Mr.P.Praveenkumar, Mr.A. Ranjeeth, AP/IT, SMVEC

{
try
{
String g;
if (male.Checked == true)
g = "male";
else
g="female";
conn.Open();
cmd = new OracleCommand("Insert into

exam(id,name,gen,dep,yr,sems,sub1,sub2,sub3,tot,cg,rest) values('" + regno.Text +

"','" + name.Text + "','"+dept.Text+"','"+g+"','"+year.Text+"',
'"+sem.Text+"','"+m1.Text+"','"+m2.Text+"','"+m3.Text+"',
'"+total.Text+"','"+cgpa.Text+"','"+res.Text+"')", conn);
MessageBox.Show("Records Inserted Successfully");
cmd.ExecuteNonQuery();
conn.Close();
}

catch (Exception ex)

{
MessageBox.Show(ex.Message);
}
}

private void select_Click(object sender, EventArgs e)

{
conn.Open();
cmd = new OracleCommand("select * from exam", conn);
OracleDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
if (dr["id"].ToString() == regno.Text)
{
string sname= dr["name"].ToString();
if (dr["gen"].ToString() == "male")
Convert.ToBoolean(male.Checked = true);
else
Convert.ToBoolean(female.Checked = true);
string sdept= dr["dep"].ToString();
string syear= dr["yr"].ToString();
string ssem = dr["sems"].ToString();
string sm1 = dr["sub1"].ToString();
95 | P a g e
DBMS LAB MANUAL – R2023, Department of Information Technology, SMVEC.
Prepared by Mrs.N.Kalaiselvi, Mr.P.Praveenkumar, Mr.A. Ranjeeth, AP/IT, SMVEC

string sm2= dr["sub2"].ToString();

string sm3 = dr["sub3"].ToString();
string stot= dr["tot"].ToString();
string scgpa = dr["cg"].ToString();
string sres= dr["rest"].ToString();
name.Text = sname;
dept.Text=sdept;
year.Text=syear;
sem.Text=ssem;
m1.Text=sm1;
m2.Text=sm2;
m3.Text=sm3;
total.Text=stot;
cgpa.Text=scgpa;
res.Text = sres;
}
}
dr.Close();
cmd.Dispose();
conn.Dispose();
}
{
MessageBox.Show(ex.Message);
}
}

private void exit_Click(object sender, EventArgs e)

{
this.Close();
}

private void calc_Click(object sender, EventArgs e)

{
String rest;
int mar1, mar2, mar3, t;
float c;
mar1 = int.Parse(m1.Text);
mar2 = int.Parse(m2.Text);
mar3 = int.Parse(m3.Text);
t = mar1 +mar2 + mar3;
c = (t)/29 ;
if (mar1>=50 && mar2>=50 && mar3 >= 50)
rest = "Pass";
else
96 | P a g e
DBMS LAB MANUAL – R2023, Department of Information Technology, SMVEC.
Prepared by Mrs.N.Kalaiselvi, Mr.P.Praveenkumar, Mr.A. Ranjeeth, AP/IT, SMVEC

rest = "Fail";
total.Text = t.ToString();
cgpa.Text = c.ToString();
res.Text = rest;
}

private void report_Click(object sender, EventArgs e)

{
Form2 f=new Form2();
f.Show();
}
}
}

private void update_Click(object sender, EventArgs e)

{
try
{
conn.Open();
cmd = new OracleCommand(" UPDATE exam set name='" + name.Text + "'
WHERE id = '" + regno.Text + "'", conn);
MessageBox.Show("Records updated Successfully");
cmd.ExecuteNonQuery();
conn.Close();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}

private void delete_Click(object sender, EventArgs e)

{
try
{
conn.Open();
cmd = new OracleCommand(" DELETE from exam WHERE id = '" +
regno.Text + "'", conn);
MessageBox.Show("Records deleted Successfully");
cmd.ExecuteNonQuery();
conn.Close() }

97 | P a g e
DBMS LAB MANUAL – R2023, Department of Information Technology, SMVEC.
Prepared by Mrs.N.Kalaiselvi, Mr.P.Praveenkumar, Mr.A. Ranjeeth, AP/IT, SMVEC

//Output

//Select

//Insert

98 | P a g e
DBMS LAB MANUAL – R2023, Department of Information Technology, SMVEC.
Prepared by Mrs.N.Kalaiselvi, Mr.P.Praveenkumar, Mr.A. Ranjeeth, AP/IT, SMVEC

//Select

//Delete

99 | P a g e
DBMS LAB MANUAL – R2023, Department of Information Technology, SMVEC.
Prepared by Mrs.N.Kalaiselvi, Mr.P.Praveenkumar, Mr.A. Ranjeeth, AP/IT, SMVEC

//Select

//Report

Result:
Thus the above Student Information System Application has been created successfully using SQL
queries and the output was verified.

100 | P a g e
DBMS LAB MANUAL – R2023, Department of Information Technology, SMVEC.
Prepared by Mrs.N.Kalaiselvi, Mr.P.Praveenkumar, Mr.A. Ranjeeth, AP/IT, SMVEC

Ex No: 18

NoSQL Database Operations: CRUD (Library) : MongoDB

MongoDB is a popular NoSQL database that uses a document-oriented data model. It stores data in flexible,
JSON-like documents, meaning fields can vary from document to document, and data structure can be
changed over time.

CRUD Operations in MongoDB

CRUD stands for Create, Read, Update, and Delete. These operations are fundamental in database
management and are used to manipulate data stored in databases. Here’s an explanation of each CRUD
operation:

1. Create (C)

Purpose: The Create operation is used to insert new data records (or documents, in the case of NoSQL
databases like MongoDB) into a database.

Key Points:

Insertion: Create involves adding new records to a database table or collection.

Data Integrity: Ensures that new records adhere to any constraints or rules defined by the database schema.

Primary Keys: Generates or assigns unique identifiers (e.g., primary keys) for new records.

Examples: Inserting a new user profile, adding a new product to an inventory, or creating a new document
in a NoSQL collection.

2. Read (R)

Purpose: The Read operation retrieves data from a database, allowing users or applications to view and
access existing records.

Key Points:

Querying: Read involves executing queries to fetch specific records or sets of records from a database.

Data Retrieval: Retrieves data based on various criteria, such as filtering, sorting, or aggregating.

Efficiency: Ensures efficient retrieval of data using indexes and query optimization techniques.

Examples: Fetching user details by username, retrieving product information based on category, or reading
all documents from a collection.

101 | P a g e
DBMS LAB MANUAL – R2023, Department of Information Technology, SMVEC.
Prepared by Mrs.N.Kalaiselvi, Mr.P.Praveenkumar, Mr.A. Ranjeeth, AP/IT, SMVEC

3. Update (U)

Purpose: The Update operation modifies existing data records within a database.

Key Points:

Modification: Update involves changing the values of one or more fields in existing records.

Atomicity: Ensures that updates are either applied completely or not at all (atomic operations).

Concurrency Control: Manages simultaneous updates to prevent data inconsistencies.

Examples: Changing a user's email address, updating the price of a product, or modifying fields in a
document.

4. Delete (D)

Purpose: The Delete operation removes existing data records from a database.

Key Points:

Removal: Delete involves permanently removing records from a database table or collection.

Data Integrity: Maintains referential integrity by handling cascading deletes or enforcing constraints.

Recovery: Often soft deletes (marking records as inactive) are used to maintain audit trails.

Examples: Deleting a customer account, removing an expired product listing, or purging outdated
documents.

Application of CRUD Operations

Data Management: CRUD operations form the backbone of data management systems, enabling
applications to create, retrieve, update, and delete data efficiently.

User Interfaces: Most applications (web, mobile, desktop) use CRUD operations to interact with backend
databases, allowing users to perform actions on data.

APIs: RESTful APIs commonly map CRUD operations to HTTP methods: Create (POST), Read (GET),
Update (PUT/PATCH), and Delete (DELETE).

102 | P a g e
DBMS LAB MANUAL – R2023, Department of Information Technology, SMVEC.
Prepared by Mrs.N.Kalaiselvi, Mr.P.Praveenkumar, Mr.A. Ranjeeth, AP/IT, SMVEC

Example: Perform CRUD operations on a MongoDB collection named books.

Step 1: Download MongoDB

• Go to the https://www.mongodb.com/try/download/community
• Under the "Community Server" tab, ensure the correct version for your Windows system is selected.
• Choose "MSI" as the package.
• Click "Download" to download the installer.
Step 2: Run the Installer
• Once the download is complete, run the downloaded .msi installer.
• Follow the installation prompts:
• Click "Next" on the initial screen.
• Accept the End-User License Agreement and click "Next".
• Choose the "Complete" setup type and click "Next".
Step 3: Configure the Installation
• On the "Service Configuration" screen, you can choose to run MongoDB as a service. If you choose
this option, MongoDB will start automatically after installation.
• You can specify the data directory and the log directory. By default, they are set to:
• Data directory: C:\Program Files\MongoDB\Server\<version>\data
• Log directory: C:\Program Files\MongoDB\Server\<version>\log
• Click "Next" to proceed.
Step 4: Complete the Installation
• Complete the installation process by clicking "Install".
• After the installation is complete, click "Finish".
Step 5: Set Up Environment Variables (Optional)
• Add the MongoDB binaries to your system's PATH environment variable to run MongoDB
commands from any command prompt.
• To do this, open the Control Panel, go to System and Security, then System, and click on "Advanced
system settings".
• Click on the "Environment Variables" button.
• In the "System variables" section, find the Path variable, select it, and click "Edit".
• Click "New" and add the path to the MongoDB bin directory (e.g., C:\Program
Files\MongoDB\Server\<version>\bin).
Step 6: Verify the Installation
• Open a command prompt.
• Type mongod --version to verify that the MongoDB server is installed and working.
• Type mongo --version to verify that the MongoDB shell is installed and working.
Step 7: Start MongoDB
• If you chose to run MongoDB as a service during installation, it should be running automatically.

103 | P a g e
DBMS LAB MANUAL – R2023, Department of Information Technology, SMVEC.
Prepared by Mrs.N.Kalaiselvi, Mr.P.Praveenkumar, Mr.A. Ranjeeth, AP/IT, SMVEC

• If not, you can start it manually by opening a command prompt and running mongod.
Step 8: Verify the MongoDB Server
• Open a command prompt.
• Type mongo to start the MongoDB shell and connect to the running MongoDB instance.
• You should see the MongoDB shell prompt (>), indicating that you are connected to the MongoDB
server.

CODE
// Switch to the library database
use library

// Create and insert documents into the books collection

db.books.insertMany([
{ title: "The Great Gatsby", author: "F. Scott Fitzgerald", year: 1925, genre: "Novel" },
{ title: "To Kill a Mockingbird", author: "Harper Lee", year: 1960, genre: "Novel" },
{ title: "1984", author: "George Orwell", year: 1949, genre: "Dystopian" }
])

// Read documents from the books collection

db.books.find().pretty()

// Update a document in the books collection

db.books.updateOne(
{ title: "1984" },
{ $set: { year: 1950 } }
)

// Delete a document from the books collection

db.books.deleteOne({ title: "The Great Gatsby" })

Result:
Thus the above MongoDB Application has been created successfully using NoSQL queries and
the output was verified.

104 | P a g e
DBMS LAB MANUAL – R2023, Department of Information Technology, SMVEC.
Prepared by Mrs.N.Kalaiselvi, Mr.P.Praveenkumar, Mr.A. Ranjeeth, AP/IT, SMVEC

PRACTICE QUESTIONS

105 | P a g e
DBMS LAB MANUAL – R2023, Department of Information Technology, SMVEC.
Prepared by Mrs.N.Kalaiselvi, Mr.P.Praveenkumar, Mr.A. Ranjeeth, AP/IT, SMVEC

APPLICATIONS

PRACTICE QUESTIONS
1. Insurance Database
Consider the insurance database given below. The primary keys are made bold and the data types
are specified.

PERSON( driver_id:string , name:string , address:string )

CAR( regno:string , model:string , year:int )
ACCIDENT( report_number:int , accd_date:date , location:string )
OWNS( driver_id:string , regno:string )
PARTICIPATED( driver_id:string , regno:string , report_number:int , damage_amount:int)

1) Create the above tables by properly specifying the primary keys and foreign keys.
2) Enter at least five tuples for each relation.
3) Demonstrate how you
a.Update the damage amount for the car with specific regno in the accident with report number 12 to 25000.
b.Add a new accident to the database.
4) Find the total number of people who owned cars that were involved in accidents in the year 2008.
5) Find the number of accidents in which cars belonging to a specific model were involved.

2. Consider the following relations for a order processing database application in a company.
CUSTOMER( custno:int , cname:string , city:string )
ORDER( orderno:int , odate:date , custno:int , ord_amt:int )
ORDER_ITEM( orderno:int , itemno:int , quantity:int )
ITEM( itemno:int , unitprice:int )
SHIPMENT( orderno:int , warehouseno:int , ship_date:date )
WAREHOUSE( warehouseno:int , city:string )
1)Create the above tables by properly specifying the primary keys and foreign keys.
2) Enter at least five tuples for each relation.
3) Produce a listing: custname ,No_of_orders , Avg_order_amount , where the middle column is the total
number of orders by the customer and the last column is the average order amount for that customer.
4) List the orderno for orders that were shipped from all the warehouses that the company has in a specific
city.
5) Demonstrate the deletion of an item from the ITEM table and demonstrate a method of handling the rows
in the ORDER_ITEM table that contains this particular item.

106 | P a g e
DBMS LAB MANUAL – R2023, Department of Information Technology, SMVEC.
Prepared by Mrs.N.Kalaiselvi, Mr.P.Praveenkumar, Mr.A. Ranjeeth, AP/IT, SMVEC

3. Consider the following database of student enrollment in courses and books adopted for that
course.
STUDENT( regno:string , name:string , major:string , bdate:date )
COURSE( courseno:int , cname:string , dept:string )
ENROLL( regno:string , courseno:int , sem:int , marks:int )
BOOK_ADOPTION( courseno:int , sem:int , book_isbn:int )
TEXT( book_isbn:int , book_title:string , publisher:string , author:string )
1)Create the above tables by properly specifying the primary keys and foreign keys.
2) Enter atleast five tuples for each relation.
3) Demonstrate how you add a new text book to the database and make this book to be adopted by some
department.
4) Produce a list of text books (includes courseno ,book_isbn , book_title ) in the alphabetical order for
courses offered by the 'CS' department that use more than two books.
5) List any department that has all its books published by a specific publisher.

4. The following are maintained by a book dealer.

AUTHOR( author_id:int , name:string , city:string , country:string )
PUBLISHER( publisher_id:int , name:string , city:string , country:string )
CATALOG( book_id:int , title:string , author_id:int , publisher_id:int , category_id:int , year:int , price:int)
CATEGORY( category_id:int , description:string )
ORDER_DETAILS( order_no:int , book_id:int , quantity:int )
1)Create the above tables by properly specifying the primary keys and foreign keys.
2) Enter at least five tuples for each relation.
3) Give the details of the authors who have 2 or more books in the catalog and the price of the books is
greater than the average price of the books in the catalog and the year of publication is after 2000.
4) Find the author of the book that has maximum sales.
5) Demonstrate how you increase the price of books published by a specific publisher by 10%.

5. Consider the following database for a banking enterprise.

BRANCH( branch_name:string , branch_city:string , assets:real )
ACCOUNT( accno:int , branch_name:string , balance:real )
DEPOSITOR( customer_name:string , accno:int )
CUSTOMER( customer_name:string , customer_street:string , customer_city:string )
LOAN( loan_number:int , branch_name:string , amount:real )
BORROWER( customer_name:string , loan_number:int )
1)Create the above tables by properly specifying the primary keys and foreign keys.
2) Enter at least five tuples for each relation.
3) Find all the customers who have at least two accounts at the main branch.

107 | P a g e
DBMS LAB MANUAL – R2023, Department of Information Technology, SMVEC.
Prepared by Mrs.N.Kalaiselvi, Mr.P.Praveenkumar, Mr.A. Ranjeeth, AP/IT, SMVEC

4) Find all the customers who have an account at all the branches located in a specific city.
5) Demonstrate how you delete all account tuples at every branch located in a specific city.

6. Sales Information System

A database is being constructed for storing sales information system. A product can be described with a
unique product number, product name, selling price, manufacturer name.

The product can sale to a particular client and each client have it own unique client number, client
name, client addresses, city, pin code, state and total balance to be required to paid.

Each client order to buy product from the salesman. In the order, it has unique sales order number, sales
order date, client number, salesman number (unique), billed whole payment by the party or not and its
delivery date.

The salesman have the salesman_no, name, addresses, city, pin code, state, salary of the sales man,
delivery date, total quantity ordered, and product rate.

Write the SQL queries for the following –

a. Retrieve the list of names and the cities of all the clients.
b. List the various products available.
c. Find the names of all clients having ‘a’ as the second letter in their names.
d. Find the products whose selling price is greater than 2000 and less than or equal to 5000
e. Add a new column NEW_PRICE into the product_master table.
f. Rename the column product_price of product relation to new_product_rate.
g. Display the order number and date on which the clients placed their order.
h. Delete all the records having delivery date before 14thOctober 2021.
i. Find the sum of products based on manufacturer wise.
j. List of all orders that were canceled.

7. EMPLOYEE TABLE with CHECK CONSTRAINT for CITY:

employee (empid, empname, st, city, phone, pin)
WORKTABLE with DEFAULT CONSTRAINT for COMPANYNAME:
worktable (empid, companyname, salary)
COMPANY TABLE: - company(companyname, city)
MANAGER TABLE: - manager(empid, managername)
USE REFERENCE INTEGRITY for WORKTABLE and EMPLOYEE tables
Write the SQL Queries for the following
a. Find name, city, phone, pin of the resident of all employees who work for TCS
b. Find all employee names who don’t work for TCS
c. Find all employees who lives in ‘Bangalore’ city.
d. Find the employee who got highest salary company wise.
e. Find the employee id whose salary is greater than 1 lakh.

108 | P a g e
DBMS LAB MANUAL – R2023, Department of Information Technology, SMVEC.
Prepared by Mrs.N.Kalaiselvi, Mr.P.Praveenkumar, Mr.A. Ranjeeth, AP/IT, SMVEC

8. Patient registration form should include Registration number, Patient name, Address, Gender, Bed
number, date of registration, refer doctor id etc.
Doctor information should include Doctorcode, Doctor Name, Specialization etc.
Lab test information should include Test name, test number, test date, results, patient Registration number
and referred doctor’s code.

Queries:
a. Display the details of patients admitted on date ’14-Oct-2021’
b. Change the name of the patient to ‘Ram’ as name is ‘rajesh’.
c. Display the details of the patients and lab test results performed on ‘20-jul-21’.
d. Display the number of patients taking treatment under doctor =’ABC’.
e. Retrieve the name of doctor who is taking care of maximum number of patients.

9. Payroll system
Create necessary schema with proper attributes for payroll system and Write the queries for the following
1. List all the employees of CSE department.
2. Retrieve all the employees who have the gross salary greater than or equal to Rs-21,000.
3. Find the DA, TA, HRA of the employee name ‘Ramesh’.
4. Find the max salary of an employee department wise.
5. List the employees who are joined between ’10-APR-21’ to ’2-OCT-21’.

10. The following relations keep track of airline flight information:

Flights(flno: integer, fname:string, from: string, to: string, distance: integer, departs:time, arrives:
time, fare: integer,sdate:date)
Passenger(pid: integer, pname: string, age: integer, address:string,flno:integer) with referential to
flight schema
Write each of the following queries in SQL.
• List the flight detail that schedule on ’12-Sep-2021’.
• List the passenger detail for a flight name=”Airlines 125”
• Find the flight detail which has same source and destination
• Add new column fare_class to flights relation.
• List the ordered class details along with fare.

109 | P a g e
DBMS LAB MANUAL – R2023, Department of Information Technology, SMVEC.
Prepared by Mrs.N.Kalaiselvi, Mr.P.Praveenkumar, Mr.A. Ranjeeth, AP/IT, SMVEC

VIVA QUESTIONS

110 | P a g e
DBMS LAB MANUAL – R2023, Department of Information Technology, SMVEC.
Prepared by Mrs.N.Kalaiselvi, Mr.P.Praveenkumar, Mr.A. Ranjeeth, AP/IT, SMVEC

VIVA QUESTIONS
Basic Concepts:
1. What is Oracle Database?
o Oracle Database is a relational database management system developed by Oracle
Corporation.
2. Explain the architecture of Oracle Database.
o Oracle Database architecture includes the instance and the database. The instance consists of
memory structures and background processes, while the database includes physical data files
and control files.
3. Differentiate between SQL and PL/SQL.
o SQL (Structured Query Language) is used for querying and manipulating data in Oracle
Database. PL/SQL (Procedural Language/Structured Query Language) is Oracle's procedural
extension to SQL, used for writing code blocks, procedures, and functions.
4. What are the types of Oracle Database users?
o Types include database administrators (DBAs), application developers, and end-users.
5. Explain the concept of a tablespace.
o A tablespace is a logical storage unit in Oracle Database where schema objects are stored.
6. What is a schema in Oracle Database?
o A schema is a collection of database objects (tables, views, etc.) owned by a particular
database user.
SQL Basics:
7. Write a query to retrieve all columns from a table named employees.
o SELECT * FROM employees;
8. Explain the GROUP BY clause in SQL.
o GROUP BY is used with aggregate functions to group the result set based on one or more
columns.
9. What is a subquery? Provide an example.
o A subquery is a query nested within another query. Example: SELECT * FROM employees
WHERE department_id IN (SELECT department_id FROM departments WHERE
location_id = 1700);
PL/SQL:
10. What is a stored procedure?
o A stored procedure is a named PL/SQL block stored in the database that performs one or
more tasks.
11. How do you declare a variable in PL/SQL?
o DECLARE variable_name datatype;
12. Explain the CURSOR in PL/SQL.
o A CURSOR is a pointer used to fetch rows from a result set one at a time.

111 | P a g e
DBMS LAB MANUAL – R2023, Department of Information Technology, SMVEC.
Prepared by Mrs.N.Kalaiselvi, Mr.P.Praveenkumar, Mr.A. Ranjeeth, AP/IT, SMVEC

Advanced Topics:
13. What are indexes in Oracle Database?
o Indexes are database objects used to speed up the retrieval of rows by providing fast access
to rows in a table.
14. Explain the concept of data concurrency.
o Data concurrency refers to the ability of multiple users to access and modify data
simultaneously without interference.
15. What are triggers in Oracle Database?
o Triggers are PL/SQL blocks executed automatically in response to events such as INSERT,
UPDATE, or DELETE operations on a table.
Performance Tuning:
16. What are the different types of joins in SQL?
o Types include INNER JOIN, LEFT JOIN (or LEFT OUTER JOIN), RIGHT JOIN (or
RIGHT OUTER JOIN), and FULL JOIN (or FULL OUTER JOIN).
17. How do you optimize SQL queries in Oracle Database?
o Optimization techniques include using indexes, rewriting complex queries, and analyzing
execution plans.
Data Management:
18. Explain the process of backing up and recovering Oracle Database.
o Backups can be done using tools like RMAN (Recovery Manager) and Data Pump.
Recovery involves restoring data files and applying redo logs.
19. What is data integrity in Oracle Database?
o Data integrity ensures the accuracy, validity, and consistency of data stored in Oracle
Database through constraints (e.g., NOT NULL, UNIQUE, FOREIGN KEY).
Security:
20. How do you grant privileges in Oracle Database?
o GRANT privilege_name TO user_or_role;
21. Explain roles in Oracle Database.
o Roles are named groups of privileges that can be granted to users.
Miscellaneous:
22. What are the different types of triggers in Oracle Database?
o Types include BEFORE triggers, AFTER triggers, and INSTEAD OF triggers.
23. How do you manage transactions in Oracle Database?
o Transactions are managed using COMMIT to save changes and ROLLBACK to undo
changes.
24. What is the purpose of the EXPLAIN PLAN statement?
o EXPLAIN PLAN displays the execution plan of a SQL statement without executing it,
showing how Oracle Database will execute the statement.

112 | P a g e
DBMS LAB MANUAL – R2023, Department of Information Technology, SMVEC.
Prepared by Mrs.N.Kalaiselvi, Mr.P.Praveenkumar, Mr.A. Ranjeeth, AP/IT, SMVEC

Advanced SQL:
25. What are analytic functions in Oracle?
o Analytic functions compute aggregate values based on a group of rows and return a single
result for each row.
26. Explain the MERGE statement in Oracle.
o MERGE statement performs insert, update, or delete operations on a target table based on
the results of a join with a source table.
27. What is the purpose of the WITH clause (common table expression) in Oracle SQL?
o The WITH clause allows you to define temporary result sets within a SQL statement,
making complex queries easier to read and maintain.
PL/SQL Procedures and Functions:
28. What is a stored function in PL/SQL?
o A stored function is a PL/SQL block that returns a single value. It can be called in SQL
queries or other PL/SQL blocks.
29. Differentiate between PROCEDURE and FUNCTION in PL/SQL.
o A PROCEDURE is a PL/SQL block that performs a specific task, whereas a FUNCTION
returns a single value and can be used in SQL queries.
30. How do you handle exceptions in PL/SQL?
o Exceptions in PL/SQL are handled using EXCEPTION blocks, which specify actions to take
when errors occur during execution.
Database Administration:
31. What are the different types of backups available in Oracle Database?
o Types include full backup, incremental backup, and archive log backup.
32. Explain the roles of DBA_USERS and DBA_TABLES views in Oracle Database.
o DBA_USERS view provides information about all users in the database, while
DBA_TABLES view lists all tables accessible to the DBA.
Indexes and Performance Tuning:
33. What factors should you consider when creating indexes in Oracle Database?
o Considerations include the columns frequently used in queries, cardinality (uniqueness of
data), and storage implications.
34. How does the optimizer determine the execution plan for a SQL statement in Oracle
Database?
o The optimizer analyzes statistics, indexes, and constraints to generate an execution plan
based on cost estimates.
Data Integrity and Constraints:
35. Explain the purpose of referential integrity constraints in Oracle Database.
o Referential integrity constraints (foreign keys) ensure that relationships between tables are
enforced, maintaining data consistency.

113 | P a g e
DBMS LAB MANUAL – R2023, Department of Information Technology, SMVEC.
Prepared by Mrs.N.Kalaiselvi, Mr.P.Praveenkumar, Mr.A. Ranjeeth, AP/IT, SMVEC

36. What is the difference between PRIMARY KEY and UNIQUE constraints?
o PRIMARY KEY constraint uniquely identifies each record in a table, while UNIQUE
constraint ensures that all values in a column are distinct.
Views and Materialized Views:
37. What are views in Oracle Database?
o Views are virtual tables derived from one or more tables or other views. They provide a way
to present data in a customized manner.
38. Explain the difference between views and materialized views in Oracle.
o Views are virtual and do not store data, while materialized views store the results of a query
as a physical table, refreshing periodically.
Security and Authorization:
39. How do you revoke privileges from a user in Oracle Database?
o REVOKE privilege_name FROM user_or_role;
40. Explain the concept of database auditing in Oracle.
o Database auditing involves monitoring and recording database activities to ensure
compliance and track unauthorized access.
Transaction Management:
41. What is a savepoint in Oracle Database?
o A savepoint marks a point in a transaction to which you can roll back if necessary, without
rolling back the entire transaction.
Backup and Recovery:
42. Describe the steps to recover a database using RMAN (Recovery Manager).
o Steps include restoring data files and applying archived redo logs to bring the database to a
consistent state.
Data Pump and Import/Export:
43. What is Oracle Data Pump?
o Oracle Data Pump is a high-speed data movement utility for importing, exporting, and
maintaining large databases.
Data Dictionary Views:
44. Explain the purpose of V$SESSION and V$SQL views in Oracle Database.
o V$SESSION view provides information about active sessions in the database, while V$SQL
view displays information about SQL statements executed.
Performance Monitoring:
45. How do you monitor performance using Oracle Enterprise Manager (OEM)?
o OEM provides graphical tools for monitoring database performance metrics such as CPU
usage, memory usage, and SQL execution times.

114 | P a g e
DBMS LAB MANUAL – R2023, Department of Information Technology, SMVEC.
Prepared by Mrs.N.Kalaiselvi, Mr.P.Praveenkumar, Mr.A. Ranjeeth, AP/IT, SMVEC

Data Encryption and Security:

46. What are Transparent Data Encryption (TDE) and how do you implement it in Oracle
Database?
o TDE encrypts data stored in the database at the storage level, ensuring data security against
unauthorized access.
Troubleshooting:
47. How do you diagnose performance issues in Oracle Database?
o Diagnose using tools like SQL Tuning Advisor, AWR (Automatic Workload Repository)
reports, and SQL trace.
High Availability and Disaster Recovery:
48. Explain the concept of Oracle Data Guard.
o Oracle Data Guard provides high availability and disaster recovery solutions by maintaining
standby databases that can quickly take over in case of primary database failure.
Data Warehousing:
49. What is Oracle OLAP (Online Analytical Processing)?
o Oracle OLAP enables users to analyze multidimensional data stored in data warehouses,
facilitating complex analytical queries.
Cloud Integration:
50. How does Oracle Database integrate with Oracle Cloud services?
o Oracle Database can be deployed on Oracle Cloud Infrastructure (OCI) and integrates with
Oracle Cloud services for backup, storage, and scalability.

115 | P a g e