Data base Management Systems Lab Dept. of CSE.

Geethanjali College of Engineering and Technology

Cheeryal (V), Keesara (M), Medchal District – 501 301, Telangana

(AUTONOMOUS)
II year II Sem (AR18)
Database Management Systems
Laboratory Work Book

(18CS22L3)

DEPARTMENT OF
COMPUTER SCIENCE & ENGINEERING
(2020-2021)

Lab In- Charge HOD-CSE

Prof & Dr. A Sreelakshmi

Geethanjali College of Engineering Page i

 Database Management Systems Lab Dept. of CSE.

Geethanjali College of Engineering Page ii

 Database Management Systems Lab Dept. of CSE.

DEPARTMENT OF COMPUTER SCIENCE ENGINEERING

Geethanjali College of Engineering and Technology

Cheeryal (V), Keesara (M), Medchal District – 501 301 (T S)

CERTIFICATE
This is to certify that Mr./ Miss
has satisfactorily completed number of programs in the Database
Management Systems Laboratory.

RollNo: Branch: CSE Section: A / B / C / D/ E

Year: II Academic Year: 2020 - 21

Faculty In-charge
HoD-CSE

Internal Examiner External Examiner

Geethanjali College of Engineering Page iii

 Database Management Systems Lab Dept. of CSE.

List of Programs
S.No. Name of the Program Page no
1 E-R Model: Analyze the problem with the entities which identify data 8
persisted in the database which contains entities, attributes.
2 Concept design with E-R Model: Apply cardinalities for each 13
relationship; identify strong entities and weak entities for relationships
like generalization, aggregation, specialization.
3 Relation Model: Represent attributes as columns in tables and different 18
types of attributes like Composite, Multi-valued, and Derived. Apply
Normalization.
4 Installation of Mysql and Queries using DATA DEFINITION 24
LANGUAGE (DDL) Commands: Create, Alter, Drop, Truncate
5 Data Manipulation Language (DML) COMMANDS: SELECT, 36
INSERT, UPDATE, DELETE
6 Data Control Language (DCL): GRANT, REVOKE 41
Transaction Control Language (TCL) COMMANDS : COMMIT,
ROLL BACK SAVE POINT
7 In Built Functions: - DATE FUNCTION, NUMERICAL FUNCTIONS, 46
CHARACTER FUNCTIONS, CONVERSION FUNCTION
8 Querying Queries using ANY 60
9 Querying: Using aggregate functions COUNT, SUM using 65
GROUPBY and HAVING
a. Using aggregate functions AVERAGE
b. using GROUPBY and HAVING
10 Querying: NESTED QUERIES AND JOIN QUERIES: Nested Queries , 77
Correlated sub queries , Simple Join, a) Equi-join b) Non Equi-join , Self
join , Outer Join
11 Set Operators: Union 91
12 Views: Creating and dropping view 99
13 Triggers: Creation of INSERT TRIGGER, DELETE TRIGGER, 103
UPDATE TRIGGER
14 Procedures: Creation, Execution and Modification of stored Procedure 118
15 Additional Queries 125

16 Database Design and Implementation: MINI DATABASE PROJECT 136

Geethanjali College of Engineering Page iv

 Database Management Systems Lab Dept. of CSE.

Vision of the Institute

Geethanjali visualizes dissemination of knowledge and skills to students, who eventually
contribute to well being of the people of the nation and global community.

Mission of the Institute

• To impact adequate fundamental knowledge in all basic science and engineering technical and
Inter-Personals skills so students.
• To bring out creativity in students that would promote innovation, research and
entrepreneurship
• To Preserve and promote cultural heritage, humanistic and spiritual values promoting peace
and harmony in society

Vision of the Department

To produce globally competent and socially responsible computer science engineers,
contributing to the advancement of engineering and technology which involves creativity and
innovation by providing excellent learning environment with world class facilities.

Mission of the Department

1. To be a center of excellence in instruction, innovation in research and scholarship, and
service to the stake holders, the profession, and the public.
2. To prepare graduates to enter a rapidly changing field as a competent computer science
engineer.
3. To prepare graduate capable in all phases of software development, possess a firm
understanding of hardware technologies, have the strong mathematical background necessary
for scientific computing, and be sufficiently well versed in general theory to allow growth
within the discipline as it advances.
4. To prepare graduates to assume leadership roles by possessing good communication skills,
the ability to work effectively as team members, and an appreciation for their social and
ethical responsibility in a global setting.

Geethanjali College of Engineering Page 1

 Database Management Systems Lab Dept. of CSE.

PEOs and POs

PROGRAM EDUCATIONAL OBJECTIVES
1. To provide graduates with a good foundation in mathematics, sciences and engineering
fundamentals required to solve engineering problems that will facilitate them to find
employment in industry and / or to pursue postgraduate studies with an appreciation for
lifelong learning.
2. To provide graduates with analytical and problem solving skills to design algorithms, other
hardware / software systems, and inculcate professional ethics, inter-personal skills to work
in a multi-cultural team.
3. To facilitate graduates to get familiarized with the art software / hardware tools, imbibing
creativity and innovation that would enable them to develop cutting-edge technologies of
multi-disciplinary nature for societal development.

PROGRAM OUTCOMES
1. Engineering knowledge: Apply the knowledge of mathematics, science, engineering
fundamentals, and an engineering specialization to the solution of complex engineering
problems.
2. Problem analysis: Identify, formulate, review research literature, and analyze complex
engineering problems reaching substantiated conclusions using first principles of
mathematics, natural sciences, and engineering sciences.
3. Design/development of solutions : Design solutions for complex engineering problems
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.
4. 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.
5. Modern tool usage: Create, select, and apply appropriate techniques, resources, and
modern engineering and IT tools including prediction and modelling to complex engineering
activities with an understanding of the limitations.
6. 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 professional engineering practice.
7. Environment and sustainability: Understand the impact of the professional engineering
solutions in societal and environmental contexts, and demonstrate the knowledge of,
and need for sustainable development.

Geethanjali College of Engineering Page 2

 Database Management Systems Lab Dept. of CSE.

8. Ethics: Apply ethical principles and commit to professional ethics and responsibilities and
norms of the engineering practice.
9. Individual and team work: Function effectively as an individual, and as a member or
leader in diverse teams, and in multidisciplinary settings.
10. Communication: Communicate effectively on complex engineering activities with the engineering
community and with society at large, such as, being able to comprehend and write effective reports
and design documentation, make effective presentations, and give and receive clear instructions.
11. Project management and finance: Demonstrate knowledge and understanding of the engineering
and management principles and apply these to one’s own work, as a member and leader in a
team, to manage projects and in multidisciplinary environments.
12. Life-long learning : Recognize the need for, and have the preparation and ability to engage in
independent and life-long learning in the broadest context of technological change.

PSO (Program Specific Outcome):

PSO 1: To identify and define the computing requirements for its solution under given
constraints.
PSO 2: To follow the best practices namely SEI-CMM levels and six sigma which varies from time to
time for software development project using open ended programming environment to produce
software deliverables as per customer needs.

Course Objectives
Develop ability to
1. Learn and practice data modeling using entity-relationship and develop database design.
2. Understand the features of database management systems and Relational database.
3. Understand Structured Query Language (SQL) and learn SQL syntax.
4. Understand normalization process of a logical data model and correct any anomalies.
5. Understand needs of database processing and learn techniques for controlling the
consequences of concurrent data access.

Course Outcomes
After completion of the course, student would be able to
18CS22L3.1. Design and describe data models and schemas in DBMS.
18CS22L3.2. Use SQL- the standard language of relational databases, for database processing.
18CS22L3.3. Resolve redundant and functional dependencies, design a normalized database.
18CS22L3.4. Implement Transaction and Query processing techniques for data storage and retrieval.
18CS22L3.5. Choose appropriate file and page organizations, implement indexing methods including
B-tree, and hashing.

Geethanjali College of Engineering Page 3

 Database Management Systems Lab Dept. of CSE.

Course Mapping with POs

Mapping of Course to PEOs and POs

DBMS PEO1, PEO2 PO1,PO2, PO3, PO4, PO5, PO12, PSO1, PSO2

Mapping of Course outcomes with Programme outcomes:

POs 1 2 3 4 5 6 7 8 9 10 11 12 PSO1 PSO2
DBMS
CO 1: Design and describe 2 1 3 3 1 - - - - - - 2 2 1
data models and
schemas in DBMS.
CO 2: Use SQL- the 1 3 2 3 1 - - - - - - 2 2 1
standard language
of relational
databases, for
database
processing.
CO 3: Resolve redundant 1 3 3 3 1 - - - - - - 2 2 1
and functional
dependencies,
design a normalized
database.
CO 4: Implement 1 3 2 3 3 - - - - - - 2 2 1
Transaction and
Query processing
techniques for
data storage and
retrieval.
CO 5: Choose appropriate 1 3 2 3 3 - - - - - - 2 2 1
file and page
organizations,
implement
indexing methods
including B -tree,
and hashing.

Geethanjali College of Engineering Page 4

 Database Management Systems Lab Dept. of CSE.

Prerequisites:
18CS1201 – ADVANCED DATA STRUCTURES

INSTRUCTIONS TO THE STUDENTS:

1. Students are required to attend all labs.
2. Students will work individually in computer laboratories.
3. While coming to the lab bring the observation book and Work book etc.
4. Before coming to the lab, prepare the pre lab questions. Read through the lab
experiment to familiarize you.
5. Utilize 3 hours time properly to perform the experiment and noting down the outputs.
6. If the experiment is not completed in the prescribed time, the pending work has to be
done in the leisure hour or extended hours.
7. You will be expected to submit the completed work book according to the deadlines
set up by your instructor.

INSTRUCTIONS TO LABORATORY TEACHERS:

1. Observation book and lab records submitted for the lab work are to be checked and signed
before the next lab session.
2. Students should be instructed to switch ON the power supply after the connections are
checked by the lab assistant / teacher.
3. The promptness of submission should be strictly insisted by awarding the marks accordingly.
4. Ask viva questions at the end of the experiment.
5. Do not allow students who come late to the lab class.
6. Encourage the students to do the experiments innovatively.
7. Fill continuous Evaluation sheet, on continuous basis.
8. Ensure that the students are dressed in a formal way.

Geethanjali College of Engineering Page 5

 Database Management Systems Lab Dept. of CSE.

Scheme of Lab Exam Evaluation:

Evaluation of Internal Marks:
a) 15 Marks are awarded for day to day work
1) Record and Observation book --------- 5Marks
2) Attendance and behavior of student --------- 5 Marks
3) Viva and performance ----------------5 Marks
b) 15 Marks are awarded for conducting laboratory test as follows:
1) Write up and program--------5 Marks
2) Execution of Program ---------5 Marks
3) Viva and performance ----------------5 Marks

Evaluation of External Marks:

70 Marks are awarded for conducting laboratory test as follows:
1) Algorithm .................... 25
2) Write up of program--------- 15 Marks
3) Execution of Program --------- 15 Marks
4) Viva ---------------------- 15 Marks

Geethanjali College of Engineering Page 6

 Database Management Systems Lab Dept. of CSE.

INDEX
S. Name of the Program Date of Date of Marks Signature Remarks
No Ex. Submission

Geethanjali College of Engineering Page 7

 Database Management Systems Lab Dept. of CSE.

WEEK 1 Date:
E-R Model
Analyze the problem with the entities which identify data persisted in the database which
contains entities and attributes.

Objectives:
Student will be able to learn the Entity-Relationship(ER) modeling to develop a conceptual
model of data.

Outcomes:
Student gains the ability to
• Know the business rules, notations and constructs.
• To Construct E-R diagrams including: Entities , Attributes , Relationships

E-R Model:
• Entity: Real-world objects distinguishable from other objects. An entity is described using
a set of attributes.
• An attribute is a characteristic. In a database management system (DBMS),
an attribute refers to a database component, such a table. It also may refer to
a database field. Attributes describe the instances in the row of a database.
• Entity Set: A collection of similar entities. E.g., all employees.
– All entities in an entity set have the same set of attributes.
– Each entity set has a key.
– Each attribute has a domain.
• Relationship: Association among two or more
entities. E.g., Anil works in Pharmacy department.
• Relationship Set: Collection of similar relationships.
– An n-ary relationship set R relates n entity sets E1 ... En; each relationship in R involves
entities e1 < E1, ..., en < En
– Same entity set could participate in different relationship sets, or in different “roles” in
same set.

Geethanjali College of Engineering Page 8

 Database Management Systems Lab Dept. of CSE.

1) Library Management Systems: Library Management System is to manage accounts and

various details of particular student, staff and employees working under library along with
the records of book. (keeping student records, book records, account details)

Identify the Entities and their attributes for Library Management Systems:

S.NO Entities Attributes

1 Book authno, isbn number, title, edition, category, price

2 Reader UserId, Email, address, phone no, name

3 Publisher PublisherId, Year of publication, name

4 Authentication System LoginId and password

5 Reports UserId, Reg_no, Book_no, Issue/Return date

6 Staff name and staff_id

Geethanjali College of Engineering Page 9

 Database Management Systems Lab Dept. of CSE.

2) Hospital Management Systems:

A Hospital Management system is an element of health informatics that focuses mainly on the
administrational needs of hospitals such as medical, administrative, financial, and legal issues
and the corresponding processing of services. Hospital Management Systems provide a common
source of information about a patient’s health history.

Identify the Entities and their attributes for Hospital Management Systems:

S.NO Entities Attributes

1 Doctor Doc_id, doc_name, qualification

2 Patient Pid, pname, address, date_discharge, date_admitted

3 Records Record_no, appointment, patient bill

4 Ward Ward_id, ward_type

5 Accountant Acc_id, description

6 Tests Test_no, test_type

7 Assistants Batch_no, ward_no

Geethanjali College of Engineering Page 10

 Database Management Systems Lab Dept. of CSE.

3) University Management System:

University Management Software is used to manage the University’s student database.
University database management system is required to manage the records of the students.
Managing student information, generating of enrollment number, courses provided are some of
the modules of the University Management System.

.
Identify the Entities and their attributes for University Management Systems:

S.NO Entities Attributes

1 Professor ProfNum, ProfName

2 Student StudentNum, StudentName, GPA

3 Course CourseNum, CourseName

4 Section Section Num, Term

Geethanjali College of Engineering Page 11

 Database Management Systems Lab Dept. of CSE.

VIVA QUESTIONS

1. What is Data base?

A database is an organized collection of structured information, or data, typically stored
electronically in a computer system.

2. Explain entity, relation and attributes.

An entity can be place, person, object, event or a concept, which stores data in the database
(DBMS).
Relationship is nothing but an association among two or more entities.
An attribute is a property that describes an entity.

3. What are the different types of attributes?

There are many types of attributes which are as follows:
• Simple Attribute & Composite Attribute.
• Single Valued Attribute & Multi-valued Attribute.
• Stored Attribute & Derived Attribute.
• Key Attribute & Non-key Attribute.

4. What is the difference between entity and entity set?

An entity is an object that exists and is distinguishable from other objects. ...
An entity may be concrete (a person or a book, for example) or abstract (like a holiday or a
concept).
An entity set is a set of entities of the same type (e.g., all persons having an account at a
bank).

Work space:

Geethanjali College of Engineering Page 12

 Database Management Systems Lab Dept. of CSE.

WEEK 2 : Date:
Concept design with E-R Model. Apply cardinalities for each relationship. Identify strong
entities and weak entities for relationships like generalization, aggregation, specialization.

Objectives:
Student will be able to learn data structures in terms of entity types, relationship types and
attributes or classes, associations and attributes.

Outcomes:
Student gains the ability to describe the data requirements for a new information system in a
direct and easy to understand graphical notation.

The overall logical structure of a database can be expressed graphically by an E-R diagram,
which is built up from the following components.
• Rectangles, which represent entity sets
• Ellipse, which represent attributes
• Diamonds, which represent relationship sets
• Lines, which link attributes to entity sets and entity sets to relationship sets
• Double ellipses, which represent multi-valued attributes
• Double lines, which indicate total participation of an entity in a relationship

set For example in Fig 2.1. An employee Entity set with three attributes

Geethanjali College of Engineering Page 13

 Database Management Systems Lab Dept. of CSE.

1) Draw the E-R diagram for Library Management Systems.

E_R diagram:

Geethanjali College of Engineering Page 14

 Database Management Systems Lab Dept. of CSE.

2) Draw the E R diagram for Hospital Management Systems

E_R diagram:

Patients are treated in a single ward by the doctors assigned to them. Each patient will be
treated by a single doctor. Healthcare assistants also attend to the patients, a number of
these are associated with each ward. Patient undergoes various tests. Accounts
department manages patient treatment bill and staff payment. Some staff is paid part time
and doctors and care assistants work varying amounts of overtime at varying rates

Geethanjali College of Engineering Page 15

 Database Management Systems Lab Dept. of CSE.

3) Draw the E R diagram for University Management System.

E_R diagram:

Geethanjali College of Engineering Page 16

 Database Management Systems Lab Dept. of CSE.

VIVA QUESTIONS

1. Explain many - to - many and many- to -one relationship.

Many to one – When entities in one entity set can take part only once in the relationship
set and entities in other entity set can take part more than once in the relationship
set, cardinality is many to one.
Many to many – When entities in all entity sets can take part more than once in the
relationship cardinality is many to many.

2. What is the difference between weak entity and strong entity?

An entity type has a key attribute which uniquely identifies each entity in the entity set is
called strong entity.
Some entity types for which key attribute can’t be defined is called Weak Entity type. A
weak entity type is represented by a double rectangle.

3. Explain difference between multi - valued and single valued attributes

An attribute consisting more than one value for a given entity. For example, Phone_No (can
be more than one for a given student).
An attribute consisting only one value for a given entity. For example, Roll_No (can be only
one for a given student).

Work space:

Geethanjali College of Engineering Page 17

 Database Management Systems Lab Dept. of CSE.

WEEK 3 Date:
Relation Model represents attributes as columns in tables and different types of attributes
like composite, Multi-valued and Derived.

Objectives:
Student will be able to learn the structural components of the relational data model.
Student will be able to learn to map ER models into relational models.

Outcomes:
Student gains the ability
• To describe the Model Structure.
• To define Properties of Relations.
• To define Domains.
• To implement Notation to Describe the Relational Schema
• To Represent an ER Model as a Relational Model.

1) Represent all the entities in tabular fashion. Represent attributes as columns in tables.
Library Management systems:

Entities

Book
authno isbn number title edition category price

Reader
UserId Email address Phone_no name

Publisher
PublisherId Year of publication name

Authentication System
LoginId password
Reports
UserId Reg_no Book_no Issue/Return date

Staff
staff_id name

Relationship Set

Reserve/Return
Reserve date Due date Return date

Geethanjali College of Engineering Page 18

 Database Management Systems Lab Dept. of CSE.

2) Hospital Management systems: (Represent all the entities in tabular fashion)

Relational Model:
Entities

Relationships

Geethanjali College of Engineering Page 19

 Database Management Systems Lab Dept. of CSE.

3) University Management System: (Represent all the entities in tabular fashion)

Geethanjali College of Engineering Page 20

 Database Management Systems Lab Dept. of CSE.

VIVA QUESTIONS

1. What is relational model and its importance?

The relational model (RM) for database management is an approach to managing data using
a structure and language consistent with first-order predicate logic, first described in 1969 by
English computer scientist Edgar F. Codd, where all data is represented in terms of tuples,
grouped into relations.

2. Explain the difference between candidate key and primary key.

Primary Key is a unique and non-null key which identify a record uniquely in table. A table
can have only one primary key.
Candidate key is also a unique key to identify a record uniquely in a table but a table can
have multiple candidate keys. Candidate key signifies as which key can be used as Primary
Key.

3. Explain the need of foreign key.

A foreign key is a column or set of columns that allow us to establish a referential link between
the data in two tables. This referential link helps to match the foreign key column data with the
data of the referenced table data.

4. What is a super key?

Super key is a single key or a group of multiple keys that can uniquely identify tuples in a
table. Super keys can contain redundant attributes that might not be important for identifying
tuples. Candidate keys are a subset of Super keys.

5. Differentiate among all types of keys with example.

• Super Key - A super key is a group of single or multiple keys which identifies rows in a table.
• Primary Key - is a column or group of columns in a table that uniquely identify every row in
that table.
• Candidate Key - is a set of attributes that uniquely identify tuples in a table. Candidate Key is
a super key with no repeated attributes.
• Alternate Key - is a column or group of columns in a table that uniquely identify every row in
that table.
• Foreign Key - is a column that creates a relationship between two tables. The purpose of
Foreign keys is to maintain data integrity and allow navigation between two different instances
of an entity.
• Compound Key - has two or more attributes that allow you to uniquely recognize a specific
record. It is possible that each column may not be unique by itself within the database.
• Composite Key - is a combination of two or more columns that uniquely identify rows in a
table. The combination of columns guarantees uniqueness, though individual uniqueness is not
guaranteed.
• Surrogate Key - An artificial key which aims to uniquely identify each record is called a
surrogate key. These kind of keys are unique because they are created when you don't have any
natural primary key.
• <STUDENT>
Geethanjali College of Engineering Page 21
 Database Management Systems Lab Dept. of CSE.

Student_Number Student_Name Student_Phone Subject_Number

1 Andrew 6615927284 10

2 Sara 6583654865 20

3 Harry 4647567463 10

• <SUBJECT>

Subject_Number Subject_Name Subject_Instructor

10 DBMS Korth

20 Algorithms Cormen

30 Algorithms Leiserson

• <ENROLL>

Student_Number Subject_Number

1 10

2 20

3 10

• The Super Keys in <Student> table are −

• {Student_Number}
• {Student_Phone}
• {Student_Number,Student_Name}
Geethanjali College of Engineering Page 22
 Database Management Systems Lab Dept. of CSE.

• {Student_Number,Student_Phone}
• {Student_Number,Subject_Number}
• {Student_Phone,Student_Name}
• {Student_Phone,Subject_Number}
• {Student_Number,Student_Name,Student_Phone}
• {Student_Number,Student_Phone,Subject_Number}
• {Student_Number,Student_Name,Subject_Number}
• {Student_Phone,Student_Name,Subject_Number}

• The Super Keys in <Subject> table are −

• {Subject_Number}
• {Subject_Number,Subject_Name}
• {Subject_Number,Subject_Instructor}
• {Subject_Number,Subject_Name,Subject_Instructor}
• {Subject_Name,Subject_Instructor}

• The Super Key in <Enroll> table is −

• {Student_Number,Subject_Number}

• The Candidate Key in <Student> table is {Student_Number} or {Student_Phone}

• The Candidate Key in <Subject> table is {Subject_Number} or
{Subject_Name,Subject_Instructor}
• The Candidate Key in <Student> table is {Student_Number, Subject_Number}
• The Primary Key in <Student> table is {Student_Number}
• The Primary Key in <Subject> table is {Subject_Number}
• The Primary Key in <Enroll> table is {Student_Number, Subject_Number}
• The Composite Key in <Enroll> table is {Student_Number, Subject_Number}
• The Secondary Key in <Student> table is {Student_Phone}
• The Secondary Key in <Subject> table is {Subject_Name,Subject_Instructor}
• {Subject_Number} is the Foreign Key of <Student> table and Primary key of <Subject> table.

Geethanjali College of Engineering Page 23

 Database Management Systems Lab Dept. of CSE.

WEEK 4 Date:
INSTALLATION OF MYSQL and Practicing DDL commands

Objectives:
Student will be able to learn DDL Statements to Create and Manage Tables.

Outcomes:
Student gains the ability to
• Categorize the main database objects
• Review the table structure
• List the data types that are available for columns
• Create a simple table
• Describe how constraints are created at the time of table creation
• Describe how schema objects work.

Installation of My SQL:

Download MySQL 5.7.

The latest and stable version of MySQL package is 5.7. It brings many security fixes and
performance enhancements. And we recommend to use it for your deployments.

So the first step is to get the MySQL community installer for the Windows platform. The latest
version of the MySQL community package is 5.7.17 which comprises the following
components.

1. MySQL Connectors – A set of programmable modules for C, C++, C# APIs, and ODBC. It
helps developing applications using MySQL database.
2. MySQL Workbench – A visual tool for DBA, Software Architects, and Web Developers. It
features data modeling, SQL builder, server/user management, and database backup.
3. MySQL Notifier – A service that resides in the system’s tray which lets you watch over the
status of any local or remote MySQL instance.
4. MySQL for Excel – An Excel Add-on to help users explore MySQL schemas, tables, views,
and stored procedures.
5. MySQL Server – It is the relational database management system (RDBMS) which uses
SQL for adding, accessing, and managing content in a database.
Other notable features which you’ll find in the MySQL package are as follows:

• Pluggable storage engine support,

• Database replication/partitioning services,
• Stored procedures, triggers, views, and
• Performance schema to monitor resource consumption.
Geethanjali College of Engineering Page 24
 Database Management Systems Lab Dept. of CSE.

Install MySQL 5.7.

After downloading the MySQL community edition, double-click the installer file to begin the
installation process. It’ll launch the installer window.

Subsequently, follow the below steps. Take help from the snapshots attached. Make sure to
edit or select the option, as shown in the picture.

Step-1.

MySQL – Choose a Setup Type.

Geethanjali College of Engineering Page 25

 Database Management Systems Lab Dept. of CSE.

Step-2.

Check Requirements.
Step-3.

Begin Installation.
Geethanjali College of Engineering Page 26
 Database Management Systems Lab Dept. of CSE.

Step-4.

Installation Status.
Step-5.

Open Configuration Screen.

Geethanjali College of Engineering Page 27

 Database Management Systems Lab Dept. of CSE.

Step-6.

Edit Configuration.
Step-7.

Set Root Password.

Geethanjali College of Engineering Page 28

 Database Management Systems Lab Dept. of CSE.

Step-8.

Set As Windows Service.

Step-9.

Apply Server Configuration.

What to do After MySQL installation?

You’ve completed the MySQL installation on your Windows system. Now, you can perform
the following tasks.
Geethanjali College of Engineering Page 29
 Database Management Systems Lab Dept. of CSE.

Using MySQL CLI.

You can access the MySQL CLI from the <All Programs> section inside the Windows start
menu. See the attached screenshot and open the 2nd CLI option as shown on the screen.

MySQL CLI – Open MySQL Commandline

The CLI tool will ask for the root database password.

MySQL CLI – Enter Password

Entering the correct password will open up the MySQL command prompt. Here, you can run
simple SQL commands, e.g., show databases.

MySQL CLI – Show Databases

Geethanjali College of Engineering Page 30

 Database Management Systems Lab Dept. of CSE.

DDL COMMANDS:
1) CREATE
2) ALTER
3) DROP
4) TRUNCATE
5) RENAME

Write all the DDL commands on Student database.

DDL- DATA DEFINITION LANGUAGE, It performs auto commit action.

CREATE :-
--------:- It is used to create the database objects into the database.

Syntax:-
-------:-
create table table_name(col_name1 datatype(size),col_name2 datatype(size),col_name3
datatype(size));

ex:-
----:-

mysql> create table student(st_id int, st_name varchar(10),st_dob date, st_addr varchar(10));
Query OK, 0 rows affected (0.74 sec)

desc:-
-----:- It is for description and it is used to view the structure of the existing table.

Syntax:-
-------:- desc table_name;

ex:-
---:-

mysql> desc student;

+---------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| st_id | int | YES | | NULL | |
| st_name | varchar(10) | YES | | NULL | |
| st_dob | date | YES | | NULL | |
| st_addr | varchar(10) | YES | | NULL | |
+---------+-------------+------+-----+---------+-------+
4 rows in set (0.19 sec)

Geethanjali College of Engineering Page 31

 Database Management Systems Lab Dept. of CSE.

ALTER:-
-------:- It is used to modify the structure of the existing tables.

* How to add the new column names in tables.

Syntax:-
------:-
alter table table_name add col_name datatype(size);

ex:-
----:-
mysql> alter table student add st_mobile_number int(10);
Query OK, 0 rows affected, 1 warning (0.59 sec)
Records: 0 Duplicates: 0 Warnings: 1

mysql> desc student;

+------------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------------+-------------+------+-----+---------+-------+
| st_id | int | YES | | NULL | |
| st_name | varchar(10) | YES | | NULL | |
| st_dob | date | YES | | NULL | |
| st_addr | varchar(10) | YES | | NULL | |
| st_mobile_number | int | YES | | NULL | |
+------------------+------------- +------+-----+---------+-------+
5 rows in set (0.07 sec)

* How to modify the datatype sizes of existing tables.

Syntax:-
-------:-
alter table table_name modify col_name datatype(size);

mysql> alter table student modify column st_addr varchar(20);

Query OK, 0 rows affected (1.13 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> desc student;

+------------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------------+-------------+------+-----+---------+-------+
| st_id | int | YES | | NULL | |
| st_name | varchar(10) | YES | | NULL | |
| st_dob | date | YES | | NULL | |
| st_addr | varchar(20) | YES | | NULL | |
| st_mobile_number | int | YES | | NULL | |
+------------------+------------- +------+-----+---------+-------+
Geethanjali College of Engineering Page 32
 Database Management Systems Lab Dept. of CSE.

5 rows in set (0.07 sec)

* How to rename column names in tables.

Syntax:-
--------:-
alter table table_name rename column old_col_name to new_col_name;

ex:-
---:-
mysql> alter table student rename column st_mobile_number to st_mobile;
Query OK, 0 rows affected (0.14 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> desc student;

+------------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------------+-------------+------+-----+---------+-------+
| st_id | int | YES | | NULL | |
| st_name | varchar(10) | YES | | NULL | |
| st_dob | date | YES | | NULL | |
| st_addr | varchar(10) | YES | | NULL | |
| st_mobile | int | YES | | NULL | |
+------------------+------------- +------+-----+---------+-------+
5 rows in set (0.10 sec)

* How to drop column in table.

Syntax:-
-------:-
alter table table_name drop column_name;

ex:-
----:-
mysql> alter table student drop st_mobile;
Query OK, 0 rows affected (2.10 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> desc student;

+------------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------------+-------------+------+-----+---------+-------+
| st_id | int | YES | | NULL | |
| st_name | varchar(10) | YES | | NULL | |
| st_dob | date | YES | | NULL | |
| st_addr | varchar(10) | YES | | NULL | |
+------------------+------------- +------+-----+---------+-------+
4 rows in set (0.01 sec)
Geethanjali College of Engineering Page 33
 Database Management Systems Lab Dept. of CSE.

DROP:-
-----:- It is used to remove the database objects in database.

Syntax:-
-------:-
drop table table_name;

ex:-
---:-
mysql> drop table medicine;
Query OK, 0 rows affected (0.70 sec)

mysql> desc medicine;

ERROR 1146 (42S02): Table 'my_db.medicine' doesn't exist

TRUNCATE:-
-----------:-
It is used to remove the data permanently from the table.

Syntax:-
------:-
truncate table table_name;
ex:-
---:-
mysql> truncate table student;
Query OK, 0 rows affected (1.63 sec)

RENAME:-
-------:- It is used to change the table name from old table name to new table name.
Syntax:-
-------:-
rename table old_table_name to new_table_name;
ex:-
---:-
mysql> rename table student to student_1;
Query OK, 0 rows affected (0.72 sec)

mysql> desc student_1;

| Field | Type | Null | Key | Default | Extra |
+------------------+-------------+------+-----+---------+-------+
| st_id | int | YES | | NULL | |
| st_name | varchar(10) | YES | | NULL | |
| st_dob | date | YES | | NULL | |
| st_addr | varchar(10) | YES | | NULL | |
+------------------+------------- +------+-----+---------+-------+
4 rows in set (0.01 sec)
Geethanjali College of Engineering Page 34
 Database Management Systems Lab Dept. of CSE.

VIVA QUESTIONS
1. Write the syntax for all DDL commands?
Create :-
Syntax:- create table table_name(col_name1 datatype(size),col_name2
datatype(size),col_name3 datatype(size));
Alter:-
Syntax:- alter table table_name add col_name datatype(size);
Drop:-
Syntax:- drop table table_name;
Truncate:-
Syntax:- truncate table table_name;
Rename:-
Syntax:-rename table old_table_name to new_table_name;

2. What is the difference between drop and truncate command?

Objects deleted using DROP are permanently lost and it cannot be rolled back.
Unlike TRUNCATE which only deletes the data of the tables, the DROP command deletes the
data of the table as well as removes the entire schema/structure of the table from the database.

3. What is DDL interpreter?

DDL Interpreter interprets the DDL statements and records the generated statements in the
table containing metadata.

4. What is the command to delete columns from table?

The DROP COLUMN command is used to delete a column in an existing table.

Work space:

Geethanjali College of Engineering Page 35

 Database Management Systems Lab Dept. of CSE.

WEEK 5 Date:
Practicing DML commands

Objectives:
Student will be able to learn commands that make changes in relational database and transaction
management.

Outcomes:
Student gains the knowledge to perform transactions like updating, deleting, inserting and
selecting data from a data base.

Data Manipulation Language commands:

SELECT
INSERT
UPDATE
DELETE

Write all the DML commands on Student database.

INSERT:-
---------:- It is used to insert the data into the tables.

Syntax:-
-------:-
insert into table_name(col_name1,col_name2,col_name3) values(val1,val2,val3);

Ex:-
----:-

mysql> insert into student(st_id,st_name,st_dob,st_addr) values(1,'AAA','2021-01-01','HYD');

Query OK, 1 row affected (0.10 sec)

mysql> insert into student(st_id,st_name,st_dob,st_addr) values(2,'BBB','2021-02-01','SEC');

Query OK, 1 row affected (0.14 sec)

mysql> insert into student(st_id,st_name,st_dob,st_addr) values(3,'CCC','2021-03-01','HYT');

Query OK, 1 row affected (0.16 sec)

mysql> select * from student;

+-------+---------+------------+---------+
| st_id | st_name | st_dob | st_addr |
+-------+---------+------------+---------+
| 1 | AAA | 2021-01-01 | HYD |
Geethanjali College of Engineering Page 36
 Database Management Systems Lab Dept. of CSE.

| 2 | BBB | 2021-02-01 | SEC |

| 3 | CCC | 2021-03-01 | HYT |
+-------+---------+------------+---------+
3 rows in set (0.00 sec)

UPDATE:-
-------:-
It is used to modify the data into existing table.

Syntax:-
--------:-
update table_name set col_name=value where <condition>;

Ex:-
---:-

mysql> select * from student;

+-------+---------+------------+---------+
| st_id | st_name | st_dob | st_addr |
+-------+---------+------------+---------+
| 1 | AAA | 2021-01-01 | HYD |
| 2 | BBB | 2021-02-01 | SEC |
| 3 | CCC | 2021-03-01 | HYT |
+-------+---------+------------+---------+
3 rows in set (0.00 sec)

mysql> update student set st_name='DDD' where st_id=3;

Query OK, 1 row affected (0.11 sec)
Rows matched: 1 Changed: 1 Warnings: 0

mysql> select * from student;

+-------+---------+------------+---------+
| st_id | st_name | st_dob | st_addr |
+-------+---------+------------+---------+
| 1 | AAA | 2021-01-01 | HYD |
| 2 | BBB | 2021-02-01 | SEC |
| 3 | DDD | 2021-03-01 | HYT |
+-------+---------+------------+---------+
3 rows in set (0.00 sec)

DELETE:-
-------:-
It is used to remove the data temporarily from the table.
Syntax:-
--------:-
delete from table_name where <condition>;
Geethanjali College of Engineering Page 37
 Database Management Systems Lab Dept. of CSE.

ex:-
---:-
mysql> select * from student;
+-------+---------+------------+---------+
| st_id | st_name | st_dob | st_addr |
+-------+---------+------------+---------+
| 1 | AAA | 2021-01-01 | HYD |
| 2 | BBB | 2021-02-01 | SEC |
| 3 | DDD | 2021-03-01 | HYT |
+-------+---------+------------+---------+
3 rows in set (0.00 sec)

mysql> delete from student where st_id=3;

Query OK, 1 row affected (0.29 sec)

mysql> select * from student;

+-------+---------+------------+---------+
| st_id | st_name | st_dob | st_addr |
+-------+---------+------------+---------+
| 1 | AAA | 2021-01-01 | HYD |
| 2 | BBB | 2021-02-01 | SEC |
+-------+---------+------------+---------+
2 rows in set (0.00 sec)

DRL:-
----:- Data Retrieve Language:-
The DRL command is "select" statement.

SELECT:-
-------:- It is used to retrieve or access the data from the table.

Syntax:-
-------:-
select */col_list from table_name where <condition>;

ex:-
---:-
mysql> select * from emp;
+-------+--------+-----------+------+------------+---------+---------+--------+
| empno | ename | job | mgr | hiredate | sal | comm | deptno |
+-------+--------+-----------+------+------------+---------+---------+--------+
| 7369 | SMITH | CLERK | 7902 | 1980-12-17 | 800.00 | NULL | 20 |
| 7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 | 1600.00 | 300.00 | 30 |
| 7521 | WARD | SALESMAN | 7698 | 1981-02-22 | 1250.00 | 500.00 | 30 |
| 7566 | JONES | MANAGER | 7839 | 1981-04-02 | 2975.00 | NULL | 20 |
| 7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 | 1250.00 | 1400.00 | 30 |
Geethanjali College of Engineering Page 38
 Database Management Systems Lab Dept. of CSE.

| 7698 | BLAKE | MANAGER | 7839 | 1981-05-01 | 2850.00 | NULL | 30 |

| 7782 | CLARK | MANAGER | 7839 | 1981-06-09 | 2450.00 | NULL | 10 |
| 7788 | SCOTT | ANALYST | 7566 | 1982-12-09 | 3000.00 | NULL | 20 |
| 7839 | KING | PRESIDENT | NULL | 1981-11-17 | 5000.00 | NULL | 10 |
| 7844 | TURNER | SALESMAN | 7698 | 1981-09-08 | 1500.00 | 0.00 | 30 |
| 7876 | ADAMS | CLERK | 7788 | 1983-01-12 | 1100.00 | NULL | 20 |
| 7900 | JAMES | CLERK | 7698 | 1981-12-03 | 950.00 | NULL | 30 |
| 7902 | FORD | ANALYST | 7566 | 1981-12-03 | 3000.00 | NULL | 20 |
| 7934 | MILLER | CLERK | 7782 | 1982-01-23 | 1300.00 | NULL | 10 |
+-------+--------+-----------+------+------------+---------+---------+--------+
14 rows in set (0.04 sec)

mysql> select ename,sal,deptno from emp;

+--------+---------+--------+
| ename | sal | deptno |
+--------+---------+--------+
| SMITH | 800.00 | 20 |
| ALLEN | 1600.00 | 30 |
| WARD | 1250.00 | 30 |
| JONES | 2975.00 | 20 |
| MARTIN | 1250.00 | 30 |
| BLAKE | 2850.00 | 30 |
| CLARK | 2450.00 | 10 |
| SCOTT | 3000.00 | 20 |
| KING | 5000.00 | 10 |
| TURNER | 1500.00 | 30 |
| ADAMS | 1100.00 | 20 |
| JAMES | 950.00 | 30 |
| FORD | 3000.00 | 20 |
| MILLER | 1300.00 | 10 |
+--------+---------+--------+
14 rows in set (0.00 sec)

mysql> select * from emp where deptno=10;

+-------+--------+-----------+------+------------+---------+------+--------+
| empno | ename | job | mgr | hiredate | sal | comm | deptno |
+-------+--------+-----------+------+------------+---------+------+--------+
| 7782 | CLARK | MANAGER | 7839 | 1981-06-09 | 2450.00 | NULL | 10 |
| 7839 | KING | PRESIDENT | NULL | 1981-11-17 | 5000.00 | NULL | 10 |
| 7934 | MILLER | CLERK | 7782 | 1982-01-23 | 1300.00 | NULL | 10 |
+-------+--------+-----------+------+------------+---------+------+--------+
3 rows in set (0.04 sec)

Geethanjali College of Engineering Page 39

 Database Management Systems Lab Dept. of CSE.

VIVA QUESTIONS

1. What is the syntax for insert command?

INSERT INTO TABLE_NAME (column1, column2, column3,... columnN)] VALUES
(value1, value2, value3,... valueN);

2. Define Key constraint?

The constraint in MySQL is used to specify the rule that allows or restricts what
values/data will be stored in the table. They provide a suitable method to ensure data
accuracy and integrity inside the table. It also helps to limit the type of data that will be
inserted inside the table.

3. What is the difference between NULL Values and NOT NULL Values?
NULL means that database columns can hold NULL values. NOT NULL means a column
does not accept NULL values.

4. What is the command to display table?

The SELECT statement is used to select data from a database. The data returned is stored
in a result table, called the result-set.

Work space:

Geethanjali College of Engineering Page 40

 Database Management Systems Lab Dept. of CSE.

WEEK 6 : Date:
The DCL language is used for controlling the access to the table and hence securing the
database. DCL is used to provide certain privileges to a particular user. Privileges are rights to
be allocated. The privilege commands are namely, Grant and Revoke. The various privileges
that can be granted or revoked are, Select Insert Delete Update References Execute All.

GRANT COMMAND: It is used to create users and grant access to the database. It requires
database administrator (DBA) privilege, except that a user can change their password. A user
can grant access to their database objects to other users.

REVOKE COMMAND: Using this command, the DBA can revoke the granted database
privileges from the user.

TCL COMMAND
COMMIT: command is used to save the Records.

ROLL BACK: command is used to undo the Records.

SAVE POINT command is used to undo the Records in a particular transaction.

Queries:
Tables Used: Consider the following tables namely “DEPARTMENTS” and “EMPLOYEES”

Their schemas are as follows, Departments (dept _no , dept_ name , dept_location); Employees
(emp_id , emp_name , emp_salary);

Q1: Develop a query to grant all privileges of employees table into departments table
Query:

mysql> GRANT ALL ON emp TO 'root'@'localhost';

Query OK, 0 rows affected (0.17 sec)

mysql> GRANT ALL ON dept TO 'root'@'localhost';

Query OK, 0 rows affected (0.16 sec)

Q2: Develop a query to grant some privileges of employees table into departments table

Query:

mysql> GRANT SELECT, INSERT ON emp TO 'root'@'localhost';

Query OK, 0 rows affected (0.16 sec)
Geethanjali College of Engineering Page 41
 Database Management Systems Lab Dept. of CSE.

mysql> GRANT SELECT, INSERT ON dept TO 'root'@'localhost';

Query OK, 0 rows affected (0.11 sec)

Q3: Develop a query to revoke all privileges of employees table from departments table

Query:
mysql> REVOKE ALL ON emp FROM 'root'@'localhost';
Query OK, 0 rows affected (0.10 sec)

mysql> REVOKE ALL ON dept FROM 'root'@'localhost';

Query OK, 0 rows affected (0.11 sec)

Q4: Develop a query to revoke some privileges of employees table from departments table

Query:

mysql> REVOKE SELECT, INSERT ON emp FROM 'root'@'localhost';

Query OK, 0 rows affected (0.18 sec)

mysql> REVOKE SELECT, INSERT ON dept FROM 'root'@'localhost';

Query OK, 0 rows affected (0.11 sec)

Q5: Write a query to implement the save point

Query:

mysql> CREATE TABLE t_test (id INT PRIMARY KEY);

Query OK, 0 rows affected (2.57 sec)

mysql> START TRANSACTION;

Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO t_test VALUES (1);

Query OK, 1 row affected (0.07 sec)

mysql> SELECT * FROM t_test;

+----+
| id |
+----+
| 1 |
+----+
1 row in set (0.00 sec)

mysql> SAVEPOINT tran2;

Geethanjali College of Engineering Page 42

 Database Management Systems Lab Dept. of CSE.

Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO t_test VALUES (2);

Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM t_test;

+----+
| id |
+----+
| 1 |
| 2 |
+----+
2 rows in set (0.00 sec)

mysql> ROLLBACK TO tran2;

Query OK, 0 rows affected (0.00 sec)

mysql> SELECT * FROM t_test;

+----+
| id |
+----+
| 1 |
+----+
1 row in set (0.00 sec)

Q6: Write a query to implement the rollback

Query:

mysql> select * from student;

+-------+---------+------------+
| st_id | st_name | st_dob |
+-------+---------+------------+
| 1 | james | 2021-01-01 |
| 2 | adams | 2020-01-01 |
| 3 | scott | 2011-06-06 |
+-------+---------+------------+
3 rows in set (0.00 sec)

mysql> set autocommit=0;

Query OK, 0 rows affected (0.03 sec)

mysql> insert into student values(4,'raju','2021-02-02');

Query OK, 1 row affected (0.08 sec)
mysql> commit;
Geethanjali College of Engineering Page 43
 Database Management Systems Lab Dept. of CSE.

Query OK, 0 rows affected (0.09 sec)

mysql> select * from student;

+-------+---------+------------+
| st_id | st_name | st_dob |
+-------+---------+------------+
| 1 | james | 2021-01-01 |
| 2 | adams | 2020-01-01 |
| 3 | scott | 2011-06-06 |
| 4 | raju | 2021-02-02 |
+-------+---------+------------+
4 rows in set (0.00 sec)

Q7: Write a query to implement the commit

Query:

mysql> select * from student;

+-------+---------+------------+
| st_id | st_name | st_dob |
+-------+---------+------------+
| 1 | james | 2021-01-01 |
| 2 | adams | 2020-01-01 |
| 3 | scott | 2011-06-06 |
| 4 | raju | 2021-02-02 |
+-------+---------+------------+
4 rows in set (0.00 sec)

mysql> set autocommit=0;

Query OK, 0 rows affected (0.00 sec)

mysql> insert into student values(5,'ramu','2021-09-09');

Query OK, 1 row affected (0.05 sec)

mysql> select * from student;

+-------+---------+------------+
| st_id | st_name | st_dob |
+-------+---------+------------+
| 1 | james | 2021-01-01 |
| 2 | adams | 2020-01-01 |
| 3 | scott | 2011-06-06 |
| 4 | raju | 2021-02-02 |
| 5 | ramu | 2021-09-09 |
+-------+---------+------------+
5 rows in set (0.01 sec)
mysql> rollback;
Geethanjali College of Engineering Page 44
 Database Management Systems Lab Dept. of CSE.

Query OK, 0 rows affected (0.03 sec)

mysql> select * from student;

+-------+---------+------------+
| st_id | st_name | st_dob |
+-------+---------+------------+
| 1 | james | 2021-01-01 |
| 2 | adams | 2020-01-01 |
| 3 | scott | 2011-06-06 |
| 4 | raju | 2021-02-02 |
+-------+---------+------------+
4 rows in set (0.00 sec)

VIVA QUESTIONS

1. A transaction that completes its execution is said to be_________?

A transaction that completes its execution is said to be Committed.

2. What is the use of DCL Commands?

DCL commands are used for access control and permission management for users in the
database. With them we can easily allow or deny some actions for users on the tables or
records (row level security).

3. What is the difference between Commit and Rollback commands?

Commit permanently saves the changes made by current transaction.
Rollback undo the changes made by current transaction.

4. List out all DCL and TCL Commands.

DCL: Grant, Revoke
TCL Start Transaction, Commit, Savepoint, Rollback

Work space:

Geethanjali College of Engineering Page 45

 Database Management Systems Lab Dept. of CSE.

WEEK 7: Date:
In Built Functions: - DATE FUNCTION, NUMERICAL FUNCTIONS, CHARACTER
FUNCTIONS, CONVERSION FUNCTION
SQL Functions:-
----------------:-
Function:-
----------:- It accepts one or more arguments and it return single value.
The sql functions are,
* single row functions
* multiple row functions or aggregate group functions
* general functions

Single row functions:-

-----------------------:- The single row functions are,
* Character functions
* Number functions
* Date and time functions
* Conversion functions

CHARACTER/STRING FUNCTIONS:

The character functions are,

* lower()
* upper()
* Initial capital letter
* concat()
* length()
* substr()
* instr()
* Lpad()
* Rpad()
* trim()
* replace()

lower():-
----------:- It has one expression and it returns small characters.
ex:-
---:-
mysql> select lower('MYSQL CORPORATION');
+----------------------------+
| lower('MYSQL CORPORATION') |
+----------------------------+
| mysql corporation |
+----------------------------+
1 row in set (0.04 sec)
Geethanjali College of Engineering Page 46
 Database Management Systems Lab Dept. of CSE.

upper():-
--------:- It has one expression and it returns capital characters.
ex:-
---:-

mysql> select upper('mysql corporation');

+----------------------------+
| upper('mysql corporation') |
+----------------------------+
| MYSQL CORPORATION |
+----------------------------+
1 row in set (0.00 sec)

initial capital letter:-

------------------------:-First character is capital and remaining all are small characters.
ex:-
-----:-

mysql> select concat(upper(substr('mysqlcorp',1,1)),lower(substr('mysqlcorp',2)))as result from

dual;
+-----------+
| result |
+-----------+
| Mysqlcorp |
+-----------+
1 row in set (0.00 sec)

concat():-
----------:- It has two expressions and it is used to concatenate the two strings.
ex:-
----:-

mysql> select concat('mysql','corp');

+------------------------+
| concat('mysql','corp') |
+------------------------+
| mysqlcorp |
+------------------------+
1 row in set (0.43 sec)

length():-
---------:-It has one expression and it is used to count the individual characters and it returns
number.
ex:-
-----:-

Geethanjali College of Engineering Page 47

 Database Management Systems Lab Dept. of CSE.

mysql> select length('mysqlcorp');

+---------------------+
| length('mysqlcorp') |
+---------------------+
| 9|
+---------------------+
1 row in set (0.00 sec)

substr():-
---------:-It returns substr from the main strings and it returns characters.
ex:-
------:-

mysql> select substr('mysqlcorp',1,5);

+-------------------------+
| substr('mysqlcorp',1,5) |
+-------------------------+
| mysql |
+-------------------------+
1 row in set (0.00 sec)

ex:-
-----:-

mysql> select substr('mysqlcorp',3,3);

+-------------------------+
| substr('mysqlcorp',3,3) |
+-------------------------+
| sql |
+-------------------------+
1 row in set (0.00 sec)

instr():-
---------:-It is used to find the position of a substring.
ex:-
----:-
mysql> select instr('mysql is a powerful database','powerful');
+--------------------------------------------------+
| instr('mysql is a powerful database','powerful') |
+--------------------------------------------------+
| 12 |
+--------------------------------------------------+
1 row in set (0.06 sec)

mysql> select instr('mysql is a powerful database','database');

Geethanjali College of Engineering Page 48

 Database Management Systems Lab Dept. of CSE.

+--------------------------------------------------+
| instr('mysql is a powerful database','database') |
+--------------------------------------------------+
| 21 |
+--------------------------------------------------+
1 row in set (0.00 sec)

Lpad():-
-------:-
It has three expressions and it returns characters.
Lpad() in mysql is used to pad or add a string to the left side of the original string.
ex:-
----:-

mysql> select sal,lpad(sal,10,'X')as result from emp;

+---------+------------+
| sal | result |
+---------+------------+
| 800.00 | XXXX800.00 |
| 1600.00 | XXX1600.00 |
| 1250.00 | XXX1250.00 |
| 2975.00 | XXX2975.00 |
| 1250.00 | XXX1250.00 |
| 2850.00 | XXX2850.00 |
| 2450.00 | XXX2450.00 |
| 3000.00 | XXX3000.00 |
| 5000.00 | XXX5000.00 |
| 1500.00 | XXX1500.00 |
| 1100.00 | XXX1100.00 |
| 950.00 | XXXX950.00 |
| 3000.00 | XXX3000.00 |
| 1300.00 | XXX1300.00 |
+---------+------------+
14 rows in set (0.04 sec)

Rpad():-
---------:- It has three expressions and it returns characters.
Rpad() in mysql is used to pad or add a string to the right side of the original string.

ex:-
----:-

mysql> select sal,rpad(sal,10,'X')as result from emp;

+---------+------------+
| sal | result |

Geethanjali College of Engineering Page 49

 Database Management Systems Lab Dept. of CSE.

+---------+------------+
| 800.00 | 800.00XXXX |
| 1600.00 | 1600.00XXX |
| 1250.00 | 1250.00XXX |
| 2975.00 | 2975.00XXX |
| 1250.00 | 1250.00XXX |
| 2850.00 | 2850.00XXX |
| 2450.00 | 2450.00XXX |
| 3000.00 | 3000.00XXX |
| 5000.00 | 5000.00XXX |
| 1500.00 | 1500.00XXX |
| 1100.00 | 1100.00XXX |
| 950.00 | 950.00XXXX |
| 3000.00 | 3000.00XXX |
| 1300.00 | 1300.00XXX |
+---------+------------+
14 rows in set (0.00 sec)

trim():-
--------:- It returns a string that has unwanted characters removed.
ex:-
----:-

mysql> select length(' mysql '),length(trim(' mysql '));

+-------------------+-------------------------+
| length(' mysql ') | length(trim(' mysql ')) |
+-------------------+-------------------------+
| 7| 5|
+-------------------+-------------------------+
1 row in set (0.00 sec)

replace():-
----------:- It has three expressions and it replaces the string by string.
ex:-
-----:-
mysql> select replace('mysqlcorp','corp','xyz');
+-----------------------------------+
| replace('mysqlcorp','corp','xyz') |
+-----------------------------------+
| mysqlxyz |
+-----------------------------------+
1 row in set (0.00 sec)

mysql> select replace('mysqlcorp','mys','xyz');

+----------------------------------+

Geethanjali College of Engineering Page 50

 Database Management Systems Lab Dept. of CSE.

| replace('mysqlcorp','mys','xyz') |
+----------------------------------+
| xyzqlcorp |
+----------------------------------+
1 row in set (0.00 sec)

NUMBER FUNCTIONS:-
-----------------:- The number functions are,
* power()
* abs()
* mod()
* sqrt()
* round()
* greatest()
* least()
* ceil()
* floor()

power():-
--------:-
power() in mysql is used to find the value of a number raised to the power of another number.
ex:-
---:-
mysql> select power(10,3);
+-------------+
| power(10,3) |
+-------------+
| 1000 |
+-------------+
1 row in set (0.01 sec)

abs():-
-------:-It means absolute function, it has one expression and it always return positive value.
ex:-
------:-
mysql> select abs(10);
+---------+
| abs(10) |
+---------+
| 10 |
+---------+
1 row in set (0.00 sec)

mysql> select abs(-10);

+----------+

Geethanjali College of Engineering Page 51

 Database Management Systems Lab Dept. of CSE.

| abs(-10) |
+----------+
| 10 |
+----------+
1 row in set (0.00 sec)

mod():-
------:-It has two expressions and it returns remainder value.
ex:-
-----:-
mysql> select mod(10,2);
+-----------+
| mod(10,2) |
+-----------+
| 0|
+-----------+
1 row in set (0.00 sec)

mysql> select mod(9,2);

+----------+
| mod(9,2) |
+----------+
| 1|
+----------+
1 row in set (0.00 sec)

sqrt():-
--------:- It has one expression and it returns sqrt value.
ex:-
-----:-
mysql> select sqrt(15);
+-------------------+
| sqrt(15) |
+-------------------+
| 3.872983346207417 |
+-------------------+
1 row in set (0.00 sec)

round():-
-----------:-It returns roundfig value.
ex:-
---:-

mysql> select round(sqrt(15));

+-----------------+

Geethanjali College of Engineering Page 52

 Database Management Systems Lab Dept. of CSE.

| round(sqrt(15)) |
+-----------------+
| 4|
+-----------------+
1 row in set (0.04 sec)

greatest():-
-----------:- it has multiple expressions and it returns greatest value.
ex:-
----:-

mysql> select greatest(10,20,30,-50,-70);

+----------------------------+
| greatest(10,20,30,-50,-70) |
+----------------------------+
| 30 |
+----------------------------+
1 row in set (0.00 sec)

least():-
----------:- It has multiple expressions and it returns least value.
ex:-
-----:-
mysql> select least(10,20,30,-50,-70);
+-------------------------+
| least(10,20,30,-50,-70) |
+-------------------------+
| -70 |
+-------------------------+
1 row in set (0.00 sec)

ceil():-
--------:- the ceil() function returns the smallest integer value that is bigger than or equal to a
number.
ex:-
---:-

mysql> select ceil(25);

+----------+
| ceil(25) |
+----------+
| 25 |
+----------+
1 row in set (0.04 sec)

Geethanjali College of Engineering Page 53

 Database Management Systems Lab Dept. of CSE.

mysql> select ceil(25.75);

+-------------+
| ceil(25.75) |
+-------------+
| 26 |
+-------------+
1 row in set (0.00 sec)

floor():-
----------:- the floor() function returns the largest integer value that is smaller than or equal to a
number.
ex:-
----:-
mysql> select floor(25);
+-----------+
| floor(25) |
+-----------+
| 25 |
+-----------+
1 row in set (0.00 sec)

mysql> select floor(25.75);

+--------------+
| floor(25.75) |
+--------------+
| 25 |
+--------------+
1 row in set (0.00 sec)

DATE AND TIME FUNCTIONS:-

--------------------------:-
* adddate()
* addtime()
* curdate()
* curtime()
* sysdate() or now()
* dayname()
* last_day()
* monthname()
* hour()
* minute()
* second()

adddate():-
--------------:-It adds dates.

Geethanjali College of Engineering Page 54

 Database Management Systems Lab Dept. of CSE.

ex:-
---:-
mysql> select adddate('2021-01-01',20);
+--------------------------+
| adddate('2021-01-01',20) |
+--------------------------+
| 2021-01-21 |
+--------------------------+
1 row in set (0.05 sec)

addtime():-
---------:-It adds time.
ex:-
------:-
mysql> select addtime('2021-01-01 01:01:01','01:01:01');
+-------------------------------------------+
| addtime('2021-01-01 01:01:01','01:01:01') |
+-------------------------------------------+
| 2021-01-01 02:02:02 |
+-------------------------------------------+
1 row in set (0.04 sec)

curdate():-
------------:- It returns current date.
ex:-
----:-
mysql> select curdate();
+------------+
| curdate() |
+------------+
| 2021-06-02 |
+------------+
1 row in set (0.00 sec)

curtime():-
---------:- It returns current time.
ex:-
----:-

mysql> select curtime();

+-----------+
| curtime() |
+-----------+
| 15:07:30 |
+-----------+

Geethanjali College of Engineering Page 55

 Database Management Systems Lab Dept. of CSE.

1 row in set (0.00 sec)

sysdate() or now():-
--------------------:- Both functions are used to returns system dates with time.
ex:-
----:-
mysql> select sysdate();
+---------------------+
| sysdate() |
+---------------------+
| 2021-06-02 15:09:02 |
+---------------------+
1 row in set (0.00 sec)

mysql> select now();

+---------------------+
| now() |
+---------------------+
| 2021-06-02 15:09:06 |
+---------------------+
1 row in set (0.00 sec)

dayname():-
-----------:- It returns the name of the weekday for date.
ex:-
-----:-

mysql> select dayname('2021-06-01');

+-----------------------+
| dayname('2021-06-01') |
+-----------------------+
| Tuesday |
+-----------------------+
1 row in set (0.04 sec)

last_day():-
-------------:- It has one expression and it returns last day of the month.
ex:-
----:-

mysql> select last_day('2021-02-02');

+------------------------+
| last_day('2021-02-02') |
+------------------------+
| 2021-02-28 |

Geethanjali College of Engineering Page 56

 Database Management Systems Lab Dept. of CSE.

+------------------------+
1 row in set (0.01 sec)

mysql> select last_day('2020-02-02');

+------------------------+
| last_day('2020-02-02') |
+------------------------+
| 2020-02-29 |
+------------------------+
1 row in set (0.00 sec)

monthname():-
--------------:- It returns the full name of the month for date.
ex:-
-----:-

mysql> select monthname('2021-05-05');

+-------------------------+
| monthname('2021-05-05') |
+-------------------------+
| May |
+-------------------------+
1 row in set (0.00 sec)

mysql> select monthname('2021-06-05');

+-------------------------+
| monthname('2021-06-05') |
+-------------------------+
| June |
+-------------------------+
1 row in set (0.00 sec)

hour():-
------:- It returns hour for time.
ex:-
------:-

mysql> select hour('24:25:26');

+------------------+
| hour('24:25:26') |
+------------------+
| 24 |
+------------------+
1 row in set (0.00 sec)

Geethanjali College of Engineering Page 57

 Database Management Systems Lab Dept. of CSE.

minute():-
------------:- It returns minute for time.
ex:-
-----:-

mysql> select minute('24:25:26');

+--------------------+
| minute('24:25:26') |
+--------------------+
| 25 |
+--------------------+
1 row in set (0.00 sec)

second():-
---------:- It returns second for time.
ex:-
----:-

mysql> select second('24:25:26');

+--------------------+
| second('24:25:26') |
+--------------------+
| 26 |
+--------------------+
1 row in set (0.00 sec)

CONVERT FUNCTION:-
----------------------:- Here we are using convert() function.
The convert() function is used for converting a value from one datatype to a different datatype.

ex:-
----:-
mysql> select convert(150,char);
+-------------------+
| convert(150,char) |
+-------------------+
| 150 |
+-------------------+
1 row in set (0.00 sec)

mysql> select convert("14:06:10",time);

+--------------------------+
| convert("14:06:10",time) |
+--------------------------+
| 14:06:10 |

Geethanjali College of Engineering Page 58

 Database Management Systems Lab Dept. of CSE.

+--------------------------+
1 row in set (0.00 sec)

mysql> select convert("2021-01-02",datetime);

+--------------------------------+
| convert("2021-01-02",datetime) |
+--------------------------------+
| 2021-01-02 00:00:00 |
+--------------------------------+
1 row in set (0.00 sec)

VIVA QUESTIONS
1. Differentiate between Ceil() and floor ()?
Ceil() is a function that returns the smallest integer value that is bigger than or equal to a
number while Floor() is a function that returns the largest integer value that is smaller than or
equal to a number.

2. What is the use of DATE Function?

DATE(): Extracts the date part of a date or date/time expression. Example:
For the below table named ‘Test’
Id Name BirthTime

4120 Pratik 1996-09-26 16:44:15.581

SELECT Name, DATE(BirthTime) AS BirthDate FROM Test;

Output:
Name BirthDate

Pratik 1996-09-26

3. What is the difference between CHARACTER AND STRING Functions?

A character function is a function that takes one or more character values as parameters and
returns either a character value or a number value.
A string function is a function that takes a string value as an input regardless of the data type
of the returned value.

4. List out all Numeric Functions.

The number functions are,
* power(), abs(), mod(), sqrt(), round(), greatest(), least(), ceil(), floor()

Geethanjali College of Engineering Page 59

 Database Management Systems Lab Dept. of CSE.

WEEK 8: QUERYING using ANY Date:

Objectives:
Student will able to learn to operate on multiple result sets to return a single result set.
Student will able to learn to perform nested Queries.

Outcomes:
Student gains the knowledge to implement queries using ANY, ALL, IN, NOT IN.

IN:-
----:-
The in operator allows you to specify multiple values in a WHERE clause.

ex:-
---:-

mysql> select * from emp where sal in(800,3000,5000);

+-------+-------+-----------+------+------------+---------+------+--------+
| empno | ename | job | mgr | hiredate | sal | comm. | deptno |
+-------+-------+-----------+------+------------+---------+------+--------+
| 7369 | SMITH | CLERK | 7902 | 1980-12-17 | 800.00 | NULL | 20 |
| 7788 | SCOTT | ANALYST | 7566 | 1982-12-09 | 3000.00 | NULL | 20 |
| 7839 | KING | PRESIDENT | NULL | 1981-11-17 | 5000.00 | NULL | 10 |
| 7902 | FORD | ANALYST | 7566 | 1981-12-03 | 3000.00 | NULL | 20 |
+-------+-------+-----------+----- -+------------+---------+------+--------+
4 rows in set (0.00 sec)

mysql> select max(sal),deptno from emp group by deptno;

+----------+--------+
| max(sal) | deptno |
+----------+--------+
| 5000.00 | 10 |
| 3000.00 | 20 |
| 2850.00 | 30 |
+----------+--------+
3 rows in set (0.00 sec)

mysql> select * from emp where sal in(select max(sal) from emp group by deptno);
+-------+-------+-----------+------+------------+---------+------+--------+
| empno | ename | job | mgr | hiredate | sal | comm | deptno |
+-------+-------+-----------+------+------------+---------+------+--------+
| 7698 | BLAKE | MANAGER | 7839 | 1981-05-01 | 2850.00 | NULL | 30 |
| 7788 | SCOTT | ANALYST | 7566 | 1982-12-09 | 3000.00 | NULL | 20 |
| 7839 | KING | PRESIDENT | NULL | 1981-11-17 | 5000.00 | NULL | 10 |

Geethanjali College of Engineering Page 60

 Database Management Systems Lab Dept. of CSE.

| 7902 | FORD | ANALYST | 7566 | 1981-12-03 | 3000.00 | NULL | 20 |

+-------+-------+-----------+------+------------+---------+------+--------+
4 rows in set (0.06 sec)

NOT IN:-
-------:-
The not in operator allows you to specify multiple values in a WHERE clause.

mysql> select * from emp where sal not in(800,3000,5000);

+-------+--------+----------+------+------------+---------+---------+--------+
| empno | ename | job | mgr | hiredate | sal | comm | deptno |
+-------+--------+----------+------+------------+---------+---------+--------+
| 7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 | 1600.00 | 300.00 | 30 |
| 7521 | WARD | SALESMAN | 7698 | 1981-02-22 | 1250.00 | 500.00 | 30 |
| 7566 | JONES | MANAGER | 7839 | 1981-04-02 | 2975.00 | NULL | 20 |
| 7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 | 1250.00 | 1400.00 | 30 |
| 7698 | BLAKE | MANAGER | 7839 | 1981-05-01 | 2850.00 | NULL | 30 |
| 7782 | CLARK | MANAGER | 7839 | 1981-06-09 | 2450.00 | NULL | 10 |
| 7844 | TURNER | SALESMAN | 7698 | 1981-09-08 | 1500.00 | 0.00 | 30 |
| 7876 | ADAMS | CLERK | 7788 | 1983-01-12 | 1100.00 | NULL | 20 |
| 7900 | JAMES | CLERK | 7698 | 1981-12-03 | 950.00 | NULL | 30 |
| 7934 | MILLER | CLERK | 7782 | 1982-01-23 | 1300.00 | NULL | 10 |
+-------+--------+----------+------+------------+---------+---------+--------+
10 rows in set (0.00 sec)

mysql> select * from emp where sal not in(select max(sal) from emp group by deptno);
+-------+--------+----------+------+------------+---------+---------+--------+
| empno | ename | job | mgr | hiredate | sal | comm | deptno |
+-------+--------+----------+------+------------+---------+---------+--------+
| 7369 | SMITH | CLERK | 7902 | 1980-12-17 | 800.00 | NULL | 20 |
| 7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 | 1600.00 | 300.00 | 30 |
| 7521 | WARD | SALESMAN | 7698 | 1981-02-22 | 1250.00 | 500.00 | 30 |
| 7566 | JONES | MANAGER | 7839 | 1981-04-02 | 2975.00 | NULL | 20 |
| 7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 | 1250.00 | 1400.00 | 30 |
| 7782 | CLARK | MANAGER | 7839 | 1981-06-09 | 2450.00 | NULL | 10 |
| 7844 | TURNER | SALESMAN | 7698 | 1981-09-08 | 1500.00 | 0.00 | 30 |
| 7876 | ADAMS | CLERK | 7788 | 1983-01-12 | 1100.00 | NULL | 20 |
| 7900 | JAMES | CLERK | 7698 | 1981-12-03 | 950.00 | NULL | 30 |
| 7934 | MILLER | CLERK | 7782 | 1982-01-23 | 1300.00 | NULL | 10 |
+-------+--------+----------+------+------------+---------+---------+--------+
10 rows in set (0.00 sec)

ANY:-
----:-
ANY means that the condition will be true if the operation is true for any of the values in the
Geethanjali College of Engineering Page 61
 Database Management Systems Lab Dept. of CSE.

range.

ex:-
---:-

mysql> select min(sal) from emp group by deptno;

+----------+
| min(sal) |
+----------+
| 1300.00 |
| 800.00 |
| 950.00 |
+----------+
3 rows in set (0.00 sec)

mysql> select * from emp where sal>any(select min(sal) from emp group by deptno);
+-------+--------+-----------+------+------------+---------+---------+--------+
| empno | ename | job | mgr | hiredate | sal | comm | deptno |
+-------+--------+-----------+------+------------+---------+---------+--------+
| 7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 | 1600.00 | 300.00 | 30 |
| 7521 | WARD | SALESMAN | 7698 | 1981-02-22 | 1250.00 | 500.00 | 30 |
| 7566 | JONES | MANAGER | 7839 | 1981-04-02 | 2975.00 | NULL | 20 |
| 7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 | 1250.00 | 1400.00 | 30 |
| 7698 | BLAKE | MANAGER | 7839 | 1981-05-01 | 2850.00 | NULL | 30 |
| 7782 | CLARK | MANAGER | 7839 | 1981-06-09 | 2450.00 | NULL | 10 |
| 7788 | SCOTT | ANALYST | 7566 | 1982-12-09 | 3000.00 | NULL | 20 |
| 7839 | KING | PRESIDENT | NULL | 1981-11-17 | 5000.00 | NULL | 10 |
| 7844 | TURNER | SALESMAN | 7698 | 1981-09-08 | 1500.00 | 0.00 | 30 |
| 7876 | ADAMS | CLERK | 7788 | 1983-01-12 | 1100.00 | NULL | 20 |
| 7900 | JAMES | CLERK | 7698 | 1981-12-03 | 950.00 | NULL | 30 |
| 7902 | FORD | ANALYST | 7566 | 1981-12-03 | 3000.00 | NULL | 20 |
| 7934 | MILLER | CLERK | 7782 | 1982-01-23 | 1300.00 | NULL | 10 |
+-------+--------+-----------+------+------------+---------+---------+--------+
13 rows in set (0.00 sec)

mysql> select * from emp where sal<any(select min(sal) from emp group by deptno);
+-------+--------+----------+------+------------+---------+---------+--------+
| empno | ename | job | mgr | hiredate | sal | comm | deptno |
+-------+--------+----------+------+------------+---------+---------+--------+
| 7369 | SMITH | CLERK | 7902 | 1980-12-17 | 800.00 | NULL | 20 |
| 7521 | WARD | SALESMAN | 7698 | 1981-02-22 | 1250.00 | 500.00 | 30 |
| 7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 | 1250.00 | 1400.00 | 30 |
| 7876 | ADAMS | CLERK | 7788 | 1983-01-12 | 1100.00 | NULL | 20 |
| 7900 | JAMES | CLERK | 7698 | 1981-12-03 | 950.00 | NULL | 30 |
+-------+--------+----------+------+------------+---------+---------+--------+
5 rows in set (0.00 sec)
Geethanjali College of Engineering Page 62
 Database Management Systems Lab Dept. of CSE.

ALL:-
----
ALL means that the condition will be true only if the operation is true for all values in the range.

ex:-
----:-

mysql> select min(sal) from emp group by deptno;

+----------+
| min(sal) |
+----------+
| 1300.00 |
| 800.00 |
| 950.00 |
+----------+
3 rows in set (0.00 sec)

mysql> select min(sal) from emp group by deptno;

+----------+
| min(sal) |
+----------+
| 1300.00 |
| 800.00 |
| 950.00 |
+----------+
3 rows in set (0.00 sec)

mysql> select * from emp where sal>all(select min(sal) from emp group by deptno);
+-------+--------+-----------+------+------------+---------+--------+--------+
| empno | ename | job | mgr | hiredate | sal | comm | deptno |
+-------+--------+-----------+------+------------+---------+--------+--------+
| 7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 | 1600.00 | 300.00 | 30 |
| 7566 | JONES | MANAGER | 7839 | 1981-04-02 | 2975.00 | NULL | 20 |
| 7698 | BLAKE | MANAGER | 7839 | 1981-05-01 | 2850.00 | NULL | 30 |
| 7782 | CLARK | MANAGER | 7839 | 1981-06-09 | 2450.00 | NULL | 10 |
| 7788 | SCOTT | ANALYST | 7566 | 1982-12-09 | 3000.00 | NULL | 20 |
| 7839 | KING | PRESIDENT | NULL | 1981-11-17 | 5000.00 | NULL | 10 |
| 7844 | TURNER | SALESMAN | 7698 | 1981-09-08 | 1500.00 | 0.00 | 30 |
| 7902 | FORD | ANALYST | 7566 | 1981-12-03 | 3000.00 | NULL | 20 |
+-------+--------+-----------+------+------------+---------+--------+--------+
8 rows in set (0.00 sec)

mysql> select * from emp where sal<all(select min(sal) from emp group by deptno);
Empty set (0.00 sec)

Geethanjali College of Engineering Page 63

 Database Management Systems Lab Dept. of CSE.

VIVA QUESTIONS

1. What is the syntax for create command?

CREATE TABLE table_name (column1 data_type(size), column2 data_type(size),
column3 data_type(size), .... );

2. What is the difference between primary key and foreign key?

Primary key uniquely identifies a record in the table. Foreign key is a field in the table
that is primary key in another table. Foreign key can accept multiple null value. By
default, Primary key is clustered index and data in the database table is physically
organized in the sequence of clustered index.

3. What is the command to display data from a table?

An SQL SELECT statement retrieves records from a database table according to clauses
(for example, FROM and WHERE ) that specify criteria. The syntax is: SELECT column1,
column2 FROM table1, table2 WHERE column2='value';

4. What are the types of clause used in MySQL ?

There are generally five kinds of SQL Clauses in MySQL Server. They are listed as
follows:

• WHERE Clause
• ORDER BY clause
• HAVING Clause
• TOP Clause
• GROUP BY Clause

Work space:

Geethanjali College of Engineering Page 64

 Database Management Systems Lab Dept. of CSE.

WEEK 9 : Date:
Querying Using Aggregate functions:

Objectives:
Student will able to learn to perform mathematical operations that return a single value,
calculated from values in a column.

Outcomes:
Student gains the knowledge to perform aggregate operations on the database appropriately.

Aggregate operators: In addition to simply retrieving data, we often want to perform some
computation or summarization. SQL allows the use of arithmetic expressions. We now consider
a powerful class of constructs for computing aggregate values such as MIN and SUM.
1. Count: COUNT following by a column name returns the count of tuple in that column. If
DISTINCT keyword is used then it will return only the count of unique tuple in the column.
Otherwise, it will return count of all the tuples (including duplicates) count (*) indicates all the
tuples of the column.
Syntax: COUNT (Column name)
Example: SELECT COUNT (Sal) FROM emp;
2. SUM: SUM followed by a column name returns the sum of all the values in that column.
Syntax: SUM (Column name)
Example: SELECT SUM (Sal) From emp;
3. AVG: AVG followed by a column name returns the average value of that column values.
Syntax: AVG (n1,n2..)
Example: Select AVG(10, 15, 30) FROM DUAL;
4. MAX: MAX followed by a column name returns the maximum value of that column.
Syntax: MAX (Column name)
Example: SELECT MAX (Sal) FROM emp;
5. MIN: MIN followed by column name returns the minimum value of that column.
Syntax: MIN (Column name)
Example: SELECT MIN (Sal) FROM emp;

Geethanjali College of Engineering Page 65

 Database Management Systems Lab Dept. of CSE.

Multiple row functions:-

Its another name is aggregate group functions.
The aggregate group functions are,
min() - It returns minimum value.
max() - It returns maximum value.
sum() - It returns total value.
avg() - It returns avg value.
count()- It is used to find the total no of records.
distinct() - It is used to restrict the duplicate data.

mysql> select min(sal) from emp;

+----------+
| min(sal) |
+----------+
| 800.00 |
+----------+
1 row in set (0.05 sec)

mysql> select max(sal) from emp;

+----------+
| max(sal) |
+----------+
| 5000.00 |
+----------+
1 row in set (0.00 sec)

mysql> select sum(sal) from emp;

+----------+
| sum(sal) |
+----------+
| 29025.00 |
+----------+
1 row in set (0.00 sec)

mysql> select avg(sal) from emp;

+-------------+
| avg(sal) |
Geethanjali College of Engineering Page 66
 Database Management Systems Lab Dept. of CSE.

+-------------+
| 2073.214286 |
+-------------+
1 row in set (0.00 sec)

mysql> select count(sal) from emp;

+------------+
| count(sal) |
+------------+
| 14 |
+------------+
1 row in set (0.00 sec)

mysql> select distinct(deptno) from emp;

+--------+
| deptno |
+--------+
| 10 |
| 20 |
| 30 |
+--------+
3 rows in set (0.00 sec)

CLAUSES IN SQL FUNCTIONS:-

-------------------------:-
Group by:-
---------:- It is one of the clause and its purpose is grouping the records.
Group by clause is used to group rows based on one or more columns/expressions to calculate
aggregates like min, max, sum, avg, count for each group.

mysql> select min(sal), deptno from emp group by deptno;

+----------+--------+
| min(sal) | deptno |
+----------+--------+
| 1300.00 | 10 |
| 800.00 | 20 |
| 950.00 | 30 |
Geethanjali College of Engineering Page 67
 Database Management Systems Lab Dept. of CSE.

+----------+--------+
3 rows in set (0.00 sec)

mysql> select max(sal),deptno from emp group by deptno;

+----------+--------+
| max(sal) | deptno |
+----------+--------+
| 5000.00 | 10 |
| 3000.00 | 20 |
| 2850.00 | 30 |
+----------+--------+
3 rows in set (0.00 sec)

mysql> select avg(sal),deptno from emp group by deptno;

+-------------+--------+
| avg(sal) | deptno |
+-------------+--------+
| 2916.666667 | 10 |
| 2175.000000 | 20 |
| 1566.666667 | 30 |
+-------------+--------+
3 rows in set (0.00 sec)

mysql> select sum(sal),deptno from emp group by deptno;

+----------+--------+
| sum(sal) | deptno |
+----------+--------+
| 8750.00 | 10 |
| 10875.00 | 20 |
| 9400.00 | 30 |
+----------+--------+
3 rows in set (0.00 sec)

mysql> select count(sal),deptno from emp group by deptno;

+------------+--------+
| count(sal) | deptno |
+------------+--------+
Geethanjali College of Engineering Page 68
 Database Management Systems Lab Dept. of CSE.

| 3 | 10 |
| 5 | 20 |
| 6 | 30 |
+------------+--------+
3 rows in set (0.00 sec)

Order by():-
---------:- It is one of the clause and it is used to sorting the data either asc order or desc order.
This order by clause we can use last of the query.
For ascending order, we can use "asc" keyword
For descending order, we can use "desc" keyword

mysql> select * from emp order by deptno asc;

+-------+--------+-----------+------+------------+---------+---------+--------+
| empno | ename | job | mgr | hiredate | sal | comm | deptno |
+-------+--------+-----------+------+------------+---------+---------+--------+
| 7782 | CLARK | MANAGER | 7839 | 1981-06-09 | 2450.00 | NULL | 10 |
| 7839 | KING | PRESIDENT | NULL | 1981-11-17 | 5000.00 | NULL | 10 |
| 7934 | MILLER | CLERK | 7782 | 1982-01-23 | 1300.00 | NULL | 10 |
| 7369 | SMITH | CLERK | 7902 | 1980-12-17 | 800.00 | NULL | 20 |
| 7566 | JONES | MANAGER | 7839 | 1981-04-02 | 2975.00 | NULL | 20 |
| 7788 | SCOTT | ANALYST | 7566 | 1982-12-09 | 3000.00 | NULL | 20 |
| 7876 | ADAMS | CLERK | 7788 | 1983-01-12 | 1100.00 | NULL | 20 |
| 7902 | FORD | ANALYST | 7566 | 1981-12-03 | 3000.00 | NULL | 20 |
| 7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 | 1600.00 | 300.00 | 30 |
| 7521 | WARD | SALESMAN | 7698 | 1981-02-22 | 1250.00 | 500.00 | 30 |
| 7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 | 1250.00 | 1400.00 | 30 |
| 7698 | BLAKE | MANAGER | 7839 | 1981-05-01 | 2850.00 | NULL | 30 |
| 7844 | TURNER | SALESMAN | 7698 | 1981-09-08 | 1500.00 | 0.00 | 30 |
| 7900 | JAMES | CLERK | 7698 | 1981-12-03 | 950.00 | NULL | 30 |
+-------+--------+-----------+------+------------+---------+---------+--------+
14 rows in set (0.00 sec)

mysql> select * from emp order by deptno desc;

+-------+--------+-----------+------+------------+---------+---------+--------+
| empno | ename | job | mgr | hiredate | sal | comm | deptno |
+-------+--------+-----------+------+------------+---------+---------+--------+
Geethanjali College of Engineering Page 69
 Database Management Systems Lab Dept. of CSE.

| 7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 | 1600.00 | 300.00 | 30 |

| 7521 | WARD | SALESMAN | 7698 | 1981-02-22 | 1250.00 | 500.00 | 30 |
| 7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 | 1250.00 | 1400.00 | 30 |
| 7698 | BLAKE | MANAGER | 7839 | 1981-05-01 | 2850.00 | NULL | 30 |
| 7844 | TURNER | SALESMAN | 7698 | 1981-09-08 | 1500.00 | 0.00 | 30 |
| 7900 | JAMES | CLERK | 7698 | 1981-12-03 | 950.00 | NULL | 30 |
| 7369 | SMITH | CLERK | 7902 | 1980-12-17 | 800.00 | NULL | 20 |
| 7566 | JONES | MANAGER | 7839 | 1981-04-02 | 2975.00 | NULL | 20 |
| 7788 | SCOTT | ANALYST | 7566 | 1982-12-09 | 3000.00 | NULL | 20 |
| 7876 | ADAMS | CLERK | 7788 | 1983-01-12 | 1100.00 | NULL | 20 |
| 7902 | FORD | ANALYST | 7566 | 1981-12-03 | 3000.00 | NULL | 20 |
| 7782 | CLARK | MANAGER | 7839 | 1981-06-09 | 2450.00 | NULL | 10 |
| 7839 | KING | PRESIDENT | NULL | 1981-11-17 | 5000.00 | NULL | 10 |
| 7934 | MILLER | CLERK | 7782 | 1982-01-23 | 1300.00 | NULL | 10 |
+-------+--------+-----------+------+------------+---------+---------+--------+
14 rows in set (0.00 sec)

mysql> select min(sal),deptno from emp group by deptno;

+----------+--------+
| min(sal) | deptno |
+----------+--------+
| 1300.00 | 10 |
| 800.00 | 20 |
| 950.00 | 30 |
+----------+--------+
3 rows in set (0.00 sec)

mysql> select min(sal),deptno from emp group by deptno order by deptno desc;
+----------+--------+
| min(sal) | deptno |
+----------+--------+
| 950.00 | 30 |
| 800.00 | 20 |
| 1300.00 | 10 |
+----------+--------+
3 rows in set (0.00 sec)

Geethanjali College of Engineering Page 70

 Database Management Systems Lab Dept. of CSE.

having():-
--------:-
It is one of the clause and it is used for conditions purpose in aggregate group functions.

mysql> select max(sal),deptno from emp group by deptno having max(sal)>3000;

+----------+--------+
| max(sal) | deptno |
+----------+--------+
| 5000.00 | 10 |
+----------+--------+
1 row in set (0.03 sec)

mysql> select count(*),deptno from emp group by deptno;

+----------+--------+
| count(*) | deptno |
+----------+--------+
| 3 | 10 |
| 5 | 20 |
| 6 | 30 |
+----------+--------+
3 rows in set (0.01 sec)

mysql> select count(*),deptno from emp group by deptno having count(*)>=5;

+----------+--------+
| count(*) | deptno |
+----------+--------+
| 5 | 20 |
| 6 | 30 |
+----------+--------+
2 rows in set (0.00 sec)

mysql> select count(*),deptno from emp group by deptno having count(*)>=5 order by deptno
desc
-> ;
+----------+--------+
| count(*) | deptno |
+----------+--------+
Geethanjali College of Engineering Page 71
 Database Management Systems Lab Dept. of CSE.

| 6 | 30 |
| 5 | 20 |
+----------+--------+
2 rows in set (0.00 sec)

mysql> select max(sal),deptno from emp

-> where sal>1000
-> group by deptno
-> having max(sal)>3000
-> order by deptno desc;
+----------+--------+
| max(sal) | deptno |
+----------+--------+
| 5000.00 | 10 |
+----------+--------+
1 row in set (0.00 sec)

General functions:-
------------------:-
* ifnull()
* case()
* nullif()
* isnull()
* version()
* database()

ifnull():-
-----------:-It has two expressions, if first expression is null it returns second expression or if first
expression is not null it returns first expression.

mysql> select comm,ifnull(comm,1) from emp;

+---------+----------------+
| comm | ifnull(comm,1) |
+---------+----------------+
| NULL | 1.00 |
| 300.00 | 300.00 |
| 500.00 | 500.00 |
Geethanjali College of Engineering Page 72
 Database Management Systems Lab Dept. of CSE.

| NULL | 1.00 |
| 1400.00 | 1400.00 |
| NULL | 1.00 |
| NULL | 1.00 |
| NULL | 1.00 |
| NULL | 1.00 |
| 0.00 | 0.00 |
| NULL | 1.00 |
| NULL | 1.00 |
| NULL | 1.00 |
| NULL | 1.00 |
+---------+----------------+
14 rows in set (0.00 sec)

case():-
-------:-
It is used for elsif ladder purpose in mysql.

Syntax:-
--------:-
case col_name when <condition> then result when <condition> then result else result end;

ex:-
----:-
mysql> select job,sal,case job when upper('manager') then sal+1000
-> when upper('clerk') then sal-1000
-> else
-> sal+500
-> end as result from emp;
+-----------+---------+---------+
| job | sal | result |
+-----------+---------+---------+
| CLERK | 800.00 | -200.00 |
| SALESMAN | 1600.00 | 2100.00 |
| SALESMAN | 1250.00 | 1750.00 |
| MANAGER | 2975.00 | 3975.00 |
| SALESMAN | 1250.00 | 1750.00 |
Geethanjali College of Engineering Page 73
 Database Management Systems Lab Dept. of CSE.

| MANAGER | 2850.00 | 3850.00 |

| MANAGER | 2450.00 | 3450.00 |
| ANALYST | 3000.00 | 3500.00 |
| PRESIDENT | 5000.00 | 5500.00 |
| SALESMAN | 1500.00 | 2000.00 |
| CLERK | 1100.00 | 100.00 |
| CLERK | 950.00 | -50.00 |
| ANALYST | 3000.00 | 3500.00 |
| CLERK | 1300.00 | 300.00 |
+-----------+---------+---------+
14 rows in set (0.11 sec)

nullif():-
---------:- It has two parameters, if first parameter is equal to second parameter it returns null
value.If first parameter is not equal to second parameter it returns first parameter.

mysql> select nullif(100,100);

+-----------------+
| nullif(100,100) |
+-----------------+
| NULL |
+-----------------+
1 row in set (0.04 sec)

mysql> select nullif(10,100);

+----------------+
| nullif(10,100) |
+----------------+
| 10 |
+----------------+
1 row in set (0.00 sec)

mysql> select nullif(100,10);

+----------------+
| nullif(100,10) |
+----------------+
| 100 |
Geethanjali College of Engineering Page 74
 Database Management Systems Lab Dept. of CSE.

+----------------+
1 row in set (0.00 sec)

mysql> select nullif(100,10,200);

ERROR 1582 (42000): Incorrect parameter count in the call to native function 'nullif'

isnull():-
----------:- The isnull() function returns 1 or 0 depending on whether an expression is null. if
expression is null, this function returns 1 otherwise it returns 0.

ex:-
----:-
mysql> select isnull(null) ;
+--------------+
| isnull(null) |
+--------------+
| 1|
+--------------+
1 row in set (0.00 sec)

mysql> select isnull(100);

+-------------+
| isnull(100) |
+-------------+
| 0|
+-------------+
1 row in set (0.00 sec)

version():-
----------:-
It returns currrent version of the mysql database.

mysql> select version();

+-----------+
| version() |
+-----------+
| 8.0.24 |
Geethanjali College of Engineering Page 75
 Database Management Systems Lab Dept. of CSE.

+-----------+
1 row in set (0.00 sec)

database():-
----------:-
It returns which database we are connected in mysql.

mysql> select database();

+------------+
| database() |
+------------+
| my_db |
+------------+
1 row in set (0.00 sec)

Geethanjali College of Engineering Page 76

 Database Management Systems Lab Dept. of CSE.

WEEK 10 : Querying: NESTED QUERIES AND JOIN QUERIES

NESTED QUERIES:
------------:- A query within the query is called subquery or nested query.
* In this subquery first executes inner query and next executes outer query.

The subqueries are 1) Single row subquery 2) multiple row subquery

Single row subquery:-

--------------------:-
In this single row subquery, inner query returns single value,here we can able to use single row
operators.
The single row operators are( >, >=, <, <=, =, <>).

ex:-
-----:-

mysql> select * from emp where sal=(select min(sal) from emp);

+-------+-------+-------+------+------------+--------+------+--------+
| empno | ename | job | mgr | hiredate | sal | comm. | deptno |
+-------+-------+-------+------+------------+--------+------+--------+
| 7369 | SMITH | CLERK | 7902 | 1980-12-17 | 800.00 | NULL | 20 |
+-------+-------+-------+------+------------+--------+------+--------+
1 row in set (0.07 sec)

mysql> select * from emp where sal<>(select min(sal) from emp);

+-------+--------+-----------+------+------------+---------+---------+--------+
| empno | ename | job | mgr | hiredate | sal | comm | deptno |
+-------+--------+-----------+------+------------+---------+---------+--------+
| 7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 | 1600.00 | 300.00 | 30 |
| 7521 | WARD | SALESMAN | 7698 | 1981-02-22 | 1250.00 | 500.00 | 30 |
| 7566 | JONES | MANAGER | 7839 | 1981-04-02 | 2975.00 | NULL | 20 |
| 7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 | 1250.00 | 1400.00 | 30 |
| 7698 | BLAKE | MANAGER | 7839 | 1981-05-01 | 2850.00 | NULL | 30 |
| 7782 | CLARK | MANAGER | 7839 | 1981-06-09 | 2450.00 | NULL | 10 |
| 7788 | SCOTT | ANALYST | 7566 | 1982-12-09 | 3000.00 | NULL | 20 |
| 7839 | KING | PRESIDENT | NULL | 1981-11-17 | 5000.00 | NULL | 10 |
| 7844 | TURNER | SALESMAN | 7698 | 1981-09-08 | 1500.00 | 0.00 | 30 |
| 7876 | ADAMS | CLERK | 7788 | 1983-01-12 | 1100.00 | NULL | 20 |
| 7900 | JAMES | CLERK | 7698 | 1981-12-03 | 950.00 | NULL | 30 |
| 7902 | FORD | ANALYST | 7566 | 1981-12-03 | 3000.00 | NULL | 20 |
| 7934 | MILLER | CLERK | 7782 | 1982-01-23 | 1300.00 | NULL | 10 |
+-------+--------+-----------+------+------------+---------+---------+--------+
13 rows in set (0.00 sec)
Geethanjali College of Engineering Page 77
 Database Management Systems Lab Dept. of CSE.

mysql> select * from emp where sal>=(select max(sal) from emp);

+-------+-------+-----------+------+------------+---------+------+--------+
| empno | ename | job | mgr | hiredate | sal | comm | deptno |
+-------+-------+-----------+------+------------+---------+------+--------+
| 7839 | KING | PRESIDENT | NULL | 1981-11-17 | 5000.00 | NULL | 10 |
+-------+-------+-----------+------+------------+---------+------+--------+
1 row in set (0.00 sec)

mysql> select * from emp where sal<(select max(sal) from emp);

+-------+--------+----------+------+------------+---------+---------+--------+
| empno | ename | job | mgr | hiredate | sal | comm | deptno |
+-------+--------+----------+------+------------+---------+---------+--------+
| 7369 | SMITH | CLERK | 7902 | 1980-12-17 | 800.00 | NULL | 20 |
| 7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 | 1600.00 | 300.00 | 30 |
| 7521 | WARD | SALESMAN | 7698 | 1981-02-22 | 1250.00 | 500.00 | 30 |
| 7566 | JONES | MANAGER | 7839 | 1981-04-02 | 2975.00 | NULL | 20 |
| 7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 | 1250.00 | 1400.00 | 30 |
| 7698 | BLAKE | MANAGER | 7839 | 1981-05-01 | 2850.00 | NULL | 30 |
| 7782 | CLARK | MANAGER | 7839 | 1981-06-09 | 2450.00 | NULL | 10 |
| 7788 | SCOTT | ANALYST | 7566 | 1982-12-09 | 3000.00 | NULL | 20 |
| 7844 | TURNER | SALESMAN | 7698 | 1981-09-08 | 1500.00 | 0.00 | 30 |
| 7876 | ADAMS | CLERK | 7788 | 1983-01-12 | 1100.00 | NULL | 20 |
| 7900 | JAMES | CLERK | 7698 | 1981-12-03 | 950.00 | NULL | 30 |
| 7902 | FORD | ANALYST | 7566 | 1981-12-03 | 3000.00 | NULL | 20 |
| 7934 | MILLER | CLERK | 7782 | 1982-01-23 | 1300.00 | NULL | 10 |
+-------+--------+----------+------+------------+---------+---------+--------+
13 rows in set (0.00 sec)

Multiple row subquery:-

-------------------------:-
In this multiple row subquery, inner query returns multiple values and here we can able to use
multiple row operators.
The multiple row operators are, (in, not in, any, all).
ex:-
--------:-

mysql> select * from emp where sal in(select min(sal) from emp group by deptno);
+-------+--------+-------+------+------------+---------+------+--------+
| empno | ename | job | mgr | hiredate | sal | comm | deptno |
+-------+--------+-------+------+------------+---------+------+--------+
| 7369 | SMITH | CLERK | 7902 | 1980-12-17 | 800.00 | NULL | 20 |
| 7900 | JAMES | CLERK | 7698 | 1981-12-03 | 950.00 | NULL | 30 |
| 7934 | MILLER | CLERK | 7782 | 1982-01-23 | 1300.00 | NULL | 10 |
+-------+--------+-------+------+------------+---------+------+--------+
3 rows in set (0.00 sec)
Geethanjali College of Engineering Page 78
 Database Management Systems Lab Dept. of CSE.

mysql> select min(sal) from emp group by deptno;

+----------+
| min(sal) |
+----------+
| 1300.00 |
| 800.00 |
| 950.00 |
+----------+
3 rows in set (0.00 sec)

mysql> select * from emp where sal not in(select min(sal) from emp group by deptno);
+-------+--------+-----------+------+------------+---------+---------+--------+
| empno | ename | job | mgr | hiredate | sal | comm | deptno |
+-------+--------+-----------+------+------------+---------+---------+--------+
| 7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 | 1600.00 | 300.00 | 30 |
| 7521 | WARD | SALESMAN | 7698 | 1981-02-22 | 1250.00 | 500.00 | 30 |
| 7566 | JONES | MANAGER | 7839 | 1981-04-02 | 2975.00 | NULL | 20 |
| 7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 | 1250.00 | 1400.00 | 30 |
| 7698 | BLAKE | MANAGER | 7839 | 1981-05-01 | 2850.00 | NULL | 30 |
| 7782 | CLARK | MANAGER | 7839 | 1981-06-09 | 2450.00 | NULL | 10 |
| 7788 | SCOTT | ANALYST | 7566 | 1982-12-09 | 3000.00 | NULL | 20 |
| 7839 | KING | PRESIDENT | NULL | 1981-11-17 | 5000.00 | NULL | 10 |
| 7844 | TURNER | SALESMAN | 7698 | 1981-09-08 | 1500.00 | 0.00 | 30 |
| 7876 | ADAMS | CLERK | 7788 | 1983-01-12 | 1100.00 | NULL | 20 |
| 7902 | FORD | ANALYST | 7566 | 1981-12-03 | 3000.00 | NULL | 20 |
+-------+--------+-----------+------+------------+---------+---------+--------+
11 rows in set (0.04 sec)

mysql> select * from emp where sal>any(select min(sal) from emp group by deptno);
+-------+--------+-----------+------+------------+---------+---------+--------+
| empno | ename | job | mgr | hiredate | sal | comm | deptno |
+-------+--------+-----------+------+------------+---------+---------+--------+
| 7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 | 1600.00 | 300.00 | 30 |
| 7521 | WARD | SALESMAN | 7698 | 1981-02-22 | 1250.00 | 500.00 | 30 |
| 7566 | JONES | MANAGER | 7839 | 1981-04-02 | 2975.00 | NULL | 20 |
| 7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 | 1250.00 | 1400.00 | 30 |
| 7698 | BLAKE | MANAGER | 7839 | 1981-05-01 | 2850.00 | NULL | 30 |
| 7782 | CLARK | MANAGER | 7839 | 1981-06-09 | 2450.00 | NULL | 10 |
| 7788 | SCOTT | ANALYST | 7566 | 1982-12-09 | 3000.00 | NULL | 20 |
| 7839 | KING | PRESIDENT | NULL | 1981-11-17 | 5000.00 | NULL | 10 |
| 7844 | TURNER | SALESMAN | 7698 | 1981-09-08 | 1500.00 | 0.00 | 30 |
| 7876 | ADAMS | CLERK | 7788 | 1983-01-12 | 1100.00 | NULL | 20 |
| 7900 | JAMES | CLERK | 7698 | 1981-12-03 | 950.00 | NULL | 30 |
| 7902 | FORD | ANALYST | 7566 | 1981-12-03 | 3000.00 | NULL | 20 |
| 7934 | MILLER | CLERK | 7782 | 1982-01-23 | 1300.00 | NULL | 10 |
Geethanjali College of Engineering Page 79
 Database Management Systems Lab Dept. of CSE.

+-------+--------+-----------+------+------------+---------+---------+--------+
13 rows in set (0.00 sec)

mysql> select * from emp where sal<any(select min(sal) from emp group by deptno);
+-------+--------+----------+------+------------+---------+---------+--------+
| empno | ename | job | mgr | hiredate | sal | comm | deptno |
+-------+--------+----------+------+------------+---------+---------+--------+
| 7369 | SMITH | CLERK | 7902 | 1980-12-17 | 800.00 | NULL | 20 |
| 7521 | WARD | SALESMAN | 7698 | 1981-02-22 | 1250.00 | 500.00 | 30 |
| 7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 | 1250.00 | 1400.00 | 30 |
| 7876 | ADAMS | CLERK | 7788 | 1983-01-12 | 1100.00 | NULL | 20 |
| 7900 | JAMES | CLERK | 7698 | 1981-12-03 | 950.00 | NULL | 30 |
+-------+--------+- ---------+------+------------+---------+---------+--------+
5 rows in set (0.00 sec)

mysql> select * from emp where sal<all(select min(sal) from emp group by deptno);
Empty set (0.00 sec)

mysql> select * from emp where sal>all(select min(sal) from emp group by deptno);
+-------+--------+-----------+------+------------+---------+--------+--------+
| empno | ename | job | mgr | hiredate | sal | comm | deptno |
+-------+--------+-----------+------+------------+---------+--------+--------+
| 7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 | 1600.00 | 300.00 | 30 |
| 7566 | JONES | MANAGER | 7839 | 1981-04-02 | 2975.00 | NULL | 20 |
| 7698 | BLAKE | MANAGER | 7839 | 1981-05-01 | 2850.00 | NULL | 30 |
| 7782 | CLARK | MANAGER | 7839 | 1981-06-09 | 2450.00 | NULL | 10 |
| 7788 | SCOTT | ANALYST | 7566 | 1982-12-09 | 3000.00 | NULL | 20 |
| 7839 | KING | PRESIDENT | NULL | 1981-11-17 | 5000.00 | NULL | 10 |
| 7844 | TURNER | SALESMAN | 7698 | 1981-09-08 | 1500.00 | 0.00 | 30 |
| 7902 | FORD | ANALYST | 7566 | 1981-12-03 | 3000.00 | NULL | 20 |
+-------+--------+-----------+------+------------+---------+--------+--------+
8 rows in set (0.00 sec)

Correlated subquery:-
----------------------:-
In this corelated subquery, first executes outer query and next executes inner query.

ex:-
---:-

mysql> select * from emp e where exists(select * from dept where deptno=e.deptno);
+-------+--------+-----------+------+------------+---------+---------+--------+
| empno | ename | job | mgr | hiredate | sal | comm | deptno |
+-------+--------+-----------+------+------------+---------+---------+--------+
| 7782 | CLARK | MANAGER | 7839 | 1981-06-09 | 2450.00 | NULL | 10 |
Geethanjali College of Engineering Page 80
 Database Management Systems Lab Dept. of CSE.

| 7839 | KING | PRESIDENT | NULL | 1981-11-17 | 5000.00 | NULL | 10 |

| 7934 | MILLER | CLERK | 7782 | 1982-01-23 | 1300.00 | NULL | 10 |
| 7369 | SMITH | CLERK | 7902 | 1980-12-17 | 800.00 | NULL | 20 |
| 7566 | JONES | MANAGER | 7839 | 1981-04-02 | 2975.00 | NULL | 20 |
| 7788 | SCOTT | ANALYST | 7566 | 1982-12-09 | 3000.00 | NULL | 20 |
| 7876 | ADAMS | CLERK | 7788 | 1983-01-12 | 1100.00 | NULL | 20 |
| 7902 | FORD | ANALYST | 7566 | 1981-12-03 | 3000.00 | NULL | 20 |
| 7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 | 1600.00 | 300.00 | 30 |
| 7521 | WARD | SALESMAN | 7698 | 1981-02-22 | 1250.00 | 500.00 | 30 |
| 7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 | 1250.00 | 1400.00 | 30 |
| 7698 | BLAKE | MANAGER | 7839 | 1981-05-01 | 2850.00 | NULL | 30 |
| 7844 | TURNER | SALESMAN | 7698 | 1981-09-08 | 1500.00 | 0.00 | 30 |
| 7900 | JAMES | CLERK | 7698 | 1981-12-03 | 950.00 | NULL | 30 |
+-------+--------+-----------+------+------------+---------+---------+--------+
14 rows in set (0.00 sec)

mysql> select * from emp e where not exists(select * from dept where deptno=e.deptno);
Empty set (0.00 sec)

mysql> select * from dept d where not exists(select * from emp where deptno=d.deptno);
+--------+------------+--------+
| deptno | dname | loc |
+--------+------------+--------+
| 40 | OPERATIONS | BOSTON |
+--------+------------+--------+
1 row in set (0.00 sec)

mysql> select * from dept d where exists(select * from emp where deptno=d.deptno);
+--------+------------+----------+
| deptno | dname | loc |
+--------+------------+----------+
| 10 | ACCOUNTING | NEW YORK |
| 20 | RESEARCH | DALLAS |
| 30 | SALES | CHICAGO |
+--------+------------+----------+
3 rows in set (0.00 sec)

JOINS:-
---------:-Join is a query and it is used to retrieve data from multiple tables.
The joins are,

* Inner joins
* Outer joins
* Self join
Inner joins:-
------------:- It is used to retrieve data from multiple tables and it returns matching rows.
Geethanjali College of Engineering Page 81
 Database Management Systems Lab Dept. of CSE.

The inner joins are,

* equi join
* non equi join
* cross join
* natural join

equi join:-
----------:-
It is used to retrieve data from multiple tables and it returns matching rows, here we can use '='
symbol.
this symbol we can use after where clause.

ex:-
----:-

mysql> select e.ename,e.sal,d.dname,d.loc from emp e,dept d where e.deptno=d.deptno;

+--------+---------+------------+----------+
| ename | sal | dname | loc |
+--------+---------+------------+----------+
| CLARK | 2450.00 | ACCOUNTING | NEW YORK |
| KING | 5000.00 | ACCOUNTING | NEW YORK |
| MILLER | 1300.00 | ACCOUNTING | NEW YORK |
| SMITH | 800.00 | RESEARCH | DALLAS |
| JONES | 2975.00 | RESEARCH | DALLAS |
| SCOTT | 3000.00 | RESEARCH | DALLAS |
| ADAMS | 1100.00 | RESEARCH | DALLAS |
| FORD | 3000.00 | RESEARCH | DALLAS |
| ALLEN | 1600.00 | SALES | CHICAGO |
| WARD | 1250.00 | SALES | CHICAGO |
| MARTIN | 1250.00 | SALES | CHICAGO |
| BLAKE | 2850.00 | SALES | CHICAGO |
| TURNER | 1500.00 | SALES | CHICAGO |
| JAMES | 950.00 | SALES | CHICAGO |
+--------+---------+------------+----------+
14 rows in set (0.00 sec)

non equi join:-

-----------------:-
It is used to retrieve data from multiple tables and it returns matching rows, here we won't use '='
symbol.

ex:-
-----:-
mysql> select e.ename,e.sal,s.losal,s.hisal from emp e,salgrade s where e.sal between s.losal and
s.hisal;
Geethanjali College of Engineering Page 82
 Database Management Systems Lab Dept. of CSE.

+--------+---------+-------+-------+
| ename | sal | losal | hisal |
+--------+---------+-------+-------+
| SMITH | 800.00 | 700 | 1200 |
| ALLEN | 1600.00 | 1401 | 2000 |
| WARD | 1250.00 | 1201 | 1400 |
| JONES | 2975.00 | 2001 | 3000 |
| MARTIN | 1250.00 | 1201 | 1400 |
| BLAKE | 2850.00 | 2001 | 3000 |
| CLARK | 2450.00 | 2001 | 3000 |
| SCOTT | 3000.00 | 2001 | 3000 |
| KING | 5000.00 | 3001 | 9999 |
| TURNER | 1500.00 | 1401 | 2000 |
| ADAMS | 1100.00 | 700 | 1200 |
| JAMES | 950.00 | 700 | 1200 |
| FORD | 3000.00 | 2001 | 3000 |
| MILLER | 1300.00 | 1201 | 1400 |
+--------+---------+-------+-------+
14 rows in set (0.00 sec)

cross join:-
----------:- It is used to retrieve data from multiple tables and it returns cross product result.
here we won't use where clause.

ex:-
----:-

mysql> select e.ename,d.dname from emp e,dept d;

+--------+------------+
| ename | dname |
+--------+------------+
| SMITH | OPERATIONS |
| SMITH | SALES |
| SMITH | RESEARCH |
| SMITH | ACCOUNTING |
| ALLEN | OPERATIONS |
| ALLEN | SALES |
| ALLEN | RESEARCH |
| ALLEN | ACCOUNTING |
| WARD | OPERATIONS |
| WARD | SALES |
| WARD | RESEARCH |
| WARD | ACCOUNTING |
| JONES | OPERATIONS |
| JONES | SALES |
| JONES | RESEARCH |
Geethanjali College of Engineering Page 83
 Database Management Systems Lab Dept. of CSE.

| JONES | ACCOUNTING |
| MARTIN | OPERATIONS |
| MARTIN | SALES |
| MARTIN | RESEARCH |
| MARTIN | ACCOUNTING |
| BLAKE | OPERATIONS |
| BLAKE | SALES |
| BLAKE | RESEARCH |
| BLAKE | ACCOUNTING |
| CLARK | OPERATIONS |
| CLARK | SALES |
| CLARK | RESEARCH |
| CLARK | ACCOUNTING |
| SCOTT | OPERATIONS |
| SCOTT | SALES |
| SCOTT | RESEARCH |
| SCOTT | ACCOUNTING |
| KING | OPERATIONS |
| KING | SALES |
| KING | RESEARCH |
| KING | ACCOUNTING |
| TURNER | OPERATIONS |
| TURNER | SALES |
| TURNER | RESEARCH |
| TURNER | ACCOUNTING |
| ADAMS | OPERATIONS |
| ADAMS | SALES |
| ADAMS | RESEARCH |
| ADAMS | ACCOUNTING |
| JAMES | OPERATIONS |
| JAMES | SALES |
| JAMES | RESEARCH |
| JAMES | ACCOUNTING |
| FORD | OPERATIONS |
| FORD | SALES |
| FORD | RESEARCH |
| FORD | ACCOUNTING |
| MILLER | OPERATIONS |
| MILLER | SALES |
| MILLER | RESEARCH |
| MILLER | ACCOUNTING |
+--------+------------+
56 rows in set (0.00 sec)

mysql> select d.dname,s.losal from dept d,salgrade s;

+------------+-------+
Geethanjali College of Engineering Page 84
 Database Management Systems Lab Dept. of CSE.

| dname | losal |
+------------+-------+
| OPERATIONS | 700 |
| SALES | 700 |
| RESEARCH | 700 |
| ACCOUNTING | 700 |
| OPERATIONS | 1201 |
| SALES | 1201 |
| RESEARCH | 1201 |
| ACCOUNTING | 1201 |
| OPERATIONS | 1401 |
| SALES | 1401 |
| RESEARCH | 1401 |
| ACCOUNTING | 1401 |
| OPERATIONS | 2001 |
| SALES | 2001 |
| RESEARCH | 2001 |
| ACCOUNTING | 2001 |
| OPERATIONS | 3001 |
| SALES | 3001 |
| RESEARCH | 3001 |
| ACCOUNTING | 3001 |
+------------+-------+
20 rows in set (0.00 sec)

Natural join:-
-------------:-

mysql> select e.ename,e.sal,d.dname,d.loc from emp e natural join dept d;

+--------+---------+------------+----------+
| ename | sal | dname | loc |
+--------+---------+------------+----------+
| CLARK | 2450.00 | ACCOUNTING | NEW YORK |
| KING | 5000.00 | ACCOUNTING | NEW YORK |
| MILLER | 1300.00 | ACCOUNTING | NEW YORK |
| SMITH | 800.00 | RESEARCH | DALLAS |
| JONES | 2975.00 | RESEARCH | DALLAS |
| SCOTT | 3000.00 | RESEARCH | DALLAS |
| ADAMS | 1100.00 | RESEARCH | DALLAS |
| FORD | 3000.00 | RESEARCH | DALLAS |
| ALLEN | 1600.00 | SALES | CHICAGO |
| WARD | 1250.00 | SALES | CHICAGO |
| MARTIN | 1250.00 | SALES | CHICAGO |
| BLAKE | 2850.00 | SALES | CHICAGO |
| TURNER | 1500.00 | SALES | CHICAGO |
| JAMES | 950.00 | SALES | CHICAGO |
Geethanjali College of Engineering Page 85
 Database Management Systems Lab Dept. of CSE.

+--------+---------+------------+----------+
14 rows in set (0.00 sec)

mysql> select d.dname,d.loc,s.losal,s.hisal from dept d natural join salgrade s;

+------------+----------+-------+-------+
| dname | loc | losal | hisal |
+------------+----------+-------+-------+
| OPERATIONS | BOSTON | 700 | 1200 |
| SALES | CHICAGO | 700 | 1200 |
| RESEARCH | DALLAS | 700 | 1200 |
| ACCOUNTING | NEW YORK | 700 | 1200 |
| OPERATIONS | BOSTON | 1201 | 1400 |
| SALES | CHICAGO | 1201 | 1400 |
| RESEARCH | DALLAS | 1201 | 1400 |
| ACCOUNTING | NEW YORK | 1201 | 1400 |
| OPERATIONS | BOSTON | 1401 | 2000 |
| SALES | CHICAGO | 1401 | 2000 |
| RESEARCH | DALLAS | 1401 | 2000 |
| ACCOUNTING | NEW YORK | 1401 | 2000 |
| OPERATIONS | BOSTON | 2001 | 3000 |
| SALES | CHICAGO | 2001 | 3000 |
| RESEARCH | DALLAS | 2001 | 3000 |
| ACCOUNTING | NEW YORK | 2001 | 3000 |
| OPERATIONS | BOSTON | 3001 | 9999 |
| SALES | CHICAGO | 3001 | 9999 |
| RESEARCH | DALLAS | 3001 | 9999 |
| ACCOUNTING | NEW YORK | 3001 | 9999 |
+------------+----------+-------+-------+
20 rows in set (0.00 sec)

Outer joins:-
--------------:-It is used to retrieve data from multiple tables and it returns matching rows and non
matching rows.
The outer joins are,

* left outer join

* right outer join
* full outer join

left outer join:-

------------------:-
It is used to retrieve data from multiple tables and it returns matching rows and left side non
matching rows.
ex:-
-----:-
Geethanjali College of Engineering Page 86
 Database Management Systems Lab Dept. of CSE.

mysql> select e.ename,e.sal,d.dname,d.loc from ad_emp e left outer join ad_dept d on

e.deptno=d.deptno;
+--------+---------+------------+----------+
| ename | sal | dname | loc |
+--------+---------+------------+----------+
| SMITH | 800.00 | RESEARCH | DALLAS |
| ALLEN | 1600.00 | SALES | CHICAGO |
| WARD | 1250.00 | SALES | CHICAGO |
| JONES | 2975.00 | RESEARCH | DALLAS |
| MARTIN | 1250.00 | SALES | CHICAGO |
| BLAKE | 2850.00 | SALES | CHICAGO |
| CLARK | 2450.00 | ACCOUNTING | NEW YORK |
| SCOTT | 3000.00 | RESEARCH | DALLAS |
| KING | 5000.00 | ACCOUNTING | NEW YORK |
| TURNER | 1500.00 | SALES | CHICAGO |
| ADAMS | 1100.00 | RESEARCH | DALLAS |
| JAMES | 950.00 | SALES | CHICAGO |
| FORD | 3000.00 | RESEARCH | DALLAS |
| MILLER | 1300.00 | ACCOUNTING | NEW YORK |
| AAA | NULL | NULL | NULL |
+--------+---------+------------+----------+
15 rows in set (0.00 sec)

Right outer join:-

------------------:-
It is used to retrieve data from both tables and it returns matching rows and non matching rows
from right side table.

ex:-
----:-
mysql> select e.ename,e.sal,d.dname,d.loc from ad_emp e right outer join ad_dept d on
e.deptno=d.deptno;
+--------+---------+------------+----------+
| ename | sal | dname | loc |
+--------+---------+------------+----------+
| MILLER | 1300.00 | ACCOUNTING | NEW YORK |
| KING | 5000.00 | ACCOUNTING | NEW YORK |
| CLARK | 2450.00 | ACCOUNTING | NEW YORK |
| FORD | 3000.00 | RESEARCH | DALLAS |
| ADAMS | 1100.00 | RESEARCH | DALLAS |
| SCOTT | 3000.00 | RESEARCH | DALLAS |
| JONES | 2975.00 | RESEARCH | DALLAS |
| SMITH | 800.00 | RESEARCH | DALLAS |
| JAMES | 950.00 | SALES | CHICAGO |
| TURNER | 1500.00 | SALES | CHICAGO |
Geethanjali College of Engineering Page 87
 Database Management Systems Lab Dept. of CSE.

| BLAKE | 2850.00 | SALES | CHICAGO |

| MARTIN | 1250.00 | SALES | CHICAGO |
| WARD | 1250.00 | SALES | CHICAGO |
| ALLEN | 1600.00 | SALES | CHICAGO |
| NULL | NULL | OPERATIONS | BOSTON |
+--------+---------+------------+----------+
15 rows in set (0.00 sec)

Full outer join:-

-----------------:-
It is used to retrieve data from multiple tables and it returns matching rows and non matching
rows, whether that non matching rows is either left side or right side.

ex:-
-----:-
mysql> select e.ename,e.sal,d.dname,d.loc from ad_emp e left outer join ad_dept d on
e.deptno=d.deptno
-> union
-> select e.ename,e.sal,d.dname,d.loc from ad_emp e right outer join ad_dept d on
e.deptno=d.deptno;
+--------+---------+------------+----------+
| ename | sal | dname | loc |
+--------+---------+---- --------+----------+
| SMITH | 800.00 | RESEARCH | DALLAS |
| ALLEN | 1600.00 | SALES | CHICAGO |
| WARD | 1250.00 | SALES | CHICAGO |
| JONES | 2975.00 | RESEARCH | DALLAS |
| MARTIN | 1250.00 | SALES | CHICAGO |
| BLAKE | 2850.00 | SALES | CHICAGO |
| CLARK | 2450.00 | ACCOUNTING | NEW YORK |
| SCOTT | 3000.00 | RESEARCH | DALLAS |
| KING | 5000.00 | ACCOUNTING | NEW YORK |
| TURNER | 1500.00 | SALES | CHICAGO |
| ADAMS | 1100.00 | RESEARCH | DALLAS |
| JAMES | 950.00 | SALES | CHICAGO |
| FORD | 3000.00 | RESEARCH | DALLAS |
| MILLER | 1300.00 | ACCOUNTING | NEW YORK |
| AAA | NULL | NULL | NULL |
| NULL | NULL | OPERATIONS | BOSTON |
+--------+---------+------------+----------+
16 rows in set (0.04 sec)

Self join:-
----------:-
self join is a join and it is used to itself.
ex:-
Geethanjali College of Engineering Page 88
 Database Management Systems Lab Dept. of CSE.

-------:-
mysql> select e.ename,e.sal,e1.ename,e1.job from emp e,emp e1 where e.empno=e1.mgr;
+-------+---------+--------+----------+
| ename | sal | ename | job |
+-------+---------+--------+----------+
| FORD | 3000.00 | SMITH | CLERK |
| BLAKE | 2850.00 | ALLEN | SALESMAN |
| BLAKE | 2850.00 | WARD | SALESMAN |
| KING | 5000.00 | JONES | MANAGER |
| BLAKE | 2850.00 | MARTIN | SALESMAN |
| KING | 5000.00 | BLAKE | MANAGER |
| KING | 5000.00 | CLARK | MANAGER |
| JONES | 2975.00 | SCOTT | ANALYST |
| BLAKE | 2850.00 | TURNER | SALESMAN |
| SCOTT | 3000.00 | ADAMS | CLERK |
| BLAKE | 2850.00 | JAMES | CLERK |
| JONES | 2975.00 | FORD | ANALYST |
| CLARK | 2450.00 | MILLER | CLERK |
+-------+---------+--------+----------+
13 rows in set (0.00 sec)

mysql> select e.ename,e.sal,e1.ename,e1.job from emp e,emp e1 where e.empno=e1.mgr and

e.ename=upper('blake');
+-------+---------+--------+----------+
| ename | sal | ename | job |
+-------+---------+--------+----------+
| BLAKE | 2850.00 | ALLEN | SALESMAN |
| BLAKE | 2850.00 | WARD | SALESMAN |
| BLAKE | 2850.00 | MARTIN | SALESMAN |
| BLAKE | 2850.00 | TURNER | SALESMAN |
| BLAKE | 2850.00 | JAMES | CLERK |
+-------+---------+--------+----------+
5 rows in set (0.02 sec)

Geethanjali College of Engineering Page 89

 Database Management Systems Lab Dept. of CSE.

VIVA QUESTIONS

1. What is Equijoin?
An equijoin is a join with a join condition containing an equality operator.
An equijoin returns only the rows that have equivalent values for the specified columns.

2. What is Outer Join?

An outer join retrieves all rows from the left-hand table, or the right-hand table, or both;
wherever there is no matching data in the table on the other side of the join, the
corresponding columns in the result set are set to NULL .

3. Explain Nested Queries.

A Subquery or Inner query or a Nested query is a query within another SQL query and
embedded within the WHERE clause. A subquery is used to return data that will be used in
the main query as a condition to further restrict the data to be retrieved.

4. Explain difference between Equijoin and Non Equi Join.

An equijoin is a join with a join condition containing an equality operator. An equijoin
returns only the rows that have equivalent values for the specified columns.

An Non Equi Join /inner join is a join of two or more tables that returns only those rows
(compared using a comparison operator) that satisfy the join condition.

Work space:

Geethanjali College of Engineering Page 90

 Database Management Systems Lab Dept. of CSE.

WEEK 11: SET OPERATORS Date:

QUERIES:
1: Display all the dept numbers available with the dept and emp tables avoiding duplicates.
Solution:
1. Use select from clause.

2. Use union select clause to get the result.

Query:

SET OPERATORS:-
----------------:-
The SQL Set operators is used to combine the two or more SQL SELECT statements.

Types of Set operators in mysql are,

* Union
* Union All
* Intersect (MySQL does not provide support for the "intersect" operator, alternatively we will
do)
* Minus (MySQL does not provide support for the "minus" operator, alternatively we will do)

UNION:-
------:-
* The SQL Union operation is used to combine the result of two or more SQL SELECT queries.
* In the union operation, all the number of datatype and columns must be same in both the tables
on which UNION operation is being applied.
* The union operation eliminates the duplicate rows from its result set.

Syntax:-
----------:-
select */col_list from table_name1
union
select */col_list from table_name2;

ex:-
----:-

mysql> select * from tab1;

+------+
| id |
+------+
|1 |

Geethanjali College of Engineering Page 91

 Database Management Systems Lab Dept. of CSE.

|2 |
|A |
|B |
|Y |
+------+
5 rows in set (0.06 sec)

mysql> select * from tab2;

+------+
| id |
+------+
|1 |
|3 |
|B |
|X |
|Y |
+------+
5 rows in set (0.00 sec)

mysql> select * from tab1 union select * from tab2;

+------+
| id |
+------+
|1 |
|2 |
|A |
|B |
|Y |
|3 |
|X |
+------+
7 rows in set (0.00 sec)

mysql> select * from emp;

+-------+--------+-----------+------+------------+---------+---------+--------+
| empno | ename | job | mgr | hiredate | sal | comm | deptno |
+-------+--------+-----------+------+------------+---------+---------+--------+
| 7369 | SMITH | CLERK | 7902 | 1980-12-17 | 800.00 | NULL | 20 |
| 7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 | 1600.00 | 300.00 | 30 |
| 7521 | WARD | SALESMAN | 7698 | 1981-02-22 | 1250.00 | 500.00 | 30 |
| 7566 | JONES | MANAGER | 7839 | 1981-04-02 | 2975.00 | NULL | 20 |
| 7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 | 1250.00 | 1400.00 | 30 |
| 7698 | BLAKE | MANAGER | 7839 | 1981-05-01 | 2850.00 | NULL | 30 |
| 7782 | CLARK | MANAGER | 7839 | 1981-06-09 | 2450.00 | NULL | 10 |
| 7788 | SCOTT | ANALYST | 7566 | 1982-12-09 | 3000.00 | NULL | 20 |
| 7839 | KING | PRESIDENT | NULL | 1981-11-17 | 5000.00 | NULL | 10 |
Geethanjali College of Engineering Page 92
 Database Management Systems Lab Dept. of CSE.

| 7844 | TURNER | SALESMAN | 7698 | 1981-09-08 | 1500.00 | 0.00 | 30 |

| 7876 | ADAMS | CLERK | 7788 | 1983-01-12 | 1100.00 | NULL | 20 |
| 7900 | JAMES | CLERK | 7698 | 1981-12-03 | 950.00 | NULL | 30 |
| 7902 | FORD | ANALYST | 7566 | 1981-12-03 | 3000.00 | NULL | 20 |
| 7934 | MILLER | CLERK | 7782 | 1982-01-23 | 1300.00 | NULL | 10 |
+-------+--------+-----------+------+------------+---------+---------+--------+
14 rows in set (0.00 sec)

mysql> select * from dept;

+--------+------------+----------+
| deptno | dname | loc |
+--------+------------+----------+
| 10 | ACCOUNTING | NEW YORK |
| 20 | RESEARCH | DALLAS |
| 30 | SALES | CHICAGO |
| 40 | OPERATIONS | BOSTON |
+--------+------------+----------+
4 rows in set (0.05 sec)

mysql> select deptno from emp union select deptno from dept;
+--------+
| deptno |
+--------+
| 10 |
| 20 |
| 30 |
| 40 |
+--------+
4 rows in set (0.00 sec)

2: Display all the dept numbers available with the dept and emp tables.

Solution:
1. Use select from clause.
2. Use union all in select clause to get the result.

Query:

* UNION ALL:
==============:-
-> Union All operation is equal to the Union operation. It returns the set without removing
duplication and sorting the data.

Syntax:-

Geethanjali College of Engineering Page 93

 Database Management Systems Lab Dept. of CSE.

---------:-
select */col_list from table_name1
union
select */col_list from table_name2;

ex:-
------:-

mysql> select * from tab1;

+------+
| id |
+------+
|1 |
|2 |
|A |
|B |
|Y |
+------+
5 rows in set (0.06 sec)

mysql> select * from tab2;

+------+
| id |
+------+
|1 |
|3 |
|B |
|X |
|Y |
+------+
5 rows in set (0.00 sec)

mysql> select * from tab1 union all select * from tab2;

+------+
| id |
+------+
|1 |
|2 |
|A |
|B |
|Y |
|1 |
|3 |
|B |
|X |
Geethanjali College of Engineering Page 94
 Database Management Systems Lab Dept. of CSE.

|Y |
+------+
10 rows in set (0.00 sec)

mysql> select deptno from emp union all select deptno from dept;
+--------+
| deptno |
+--------+
| 10 |
| 10 |
| 10 |
| 20 |
| 20 |
| 20 |
| 20 |
| 20 |
| 30 |
| 30 |
| 30 |
| 30 |
| 30 |
| 30 |
| 10 |
| 20 |
| 30 |
| 40 |
+--------+
18 rows in set (0.00 sec)

INTERSECT:-
===========
* It is used to combine two SELECT statements. The Intersect operation returns the common
rows from both the SELECT statements.
* In the Intersect operation, the number of datatype and columns must be the same.
* It has no duplicates and it arranges the data in ascending order by default.

=> In Mysql database there is no directly intersect operator but that resultant output we will do
alternatively. i,e.

Note:-
-----:- MySQL does not provide support for the INTERSECT operator. The below one shows us
how to emulate the INTERSECT query in MySQL using the JOIN clause.

ex:-
----:-
Geethanjali College of Engineering Page 95
 Database Management Systems Lab Dept. of CSE.

mysql> select * from tab1;

+------+
| id |
+------+
|1 |
|2 |
|A |
|B |
|Y |
+------+
5 rows in set (0.06 sec)

mysql> select * from tab2;

+------+
| id |
+------+
|1 |
|3 |
|B |
|X |
|Y |
+------+
5 rows in set (0.00 sec)

mysql> select distinct id from tab1 inner join tab2 using(id);

+------+
| id |
+------+
|1 |
|B |
|Y |
+------+
3 rows in set (0.00 sec)

MINUS:-
=========

* It combines the result of two SELECT statements. Minus operator is used to display the rows
which are present in the first query but absent in the second query.
* It has no duplicates and data arranged in ascending order by default.

NOTE:
------:- MySQL does not provide support for the MINUS operator. The below one shows you how
to simulate the MINUS query in MySQL using the JOIN clause.

Geethanjali College of Engineering Page 96

 Database Management Systems Lab Dept. of CSE.

ex:-
----:-

mysql> select * from tab1;

+------+
| id |
+------+
|1 |
|2 |
|A |
|B |
|Y |
+------+
5 rows in set (0.00 sec)

mysql> select * from tab2;

+------+
| id |
+------+
|1 |
|3 |
|B |
|X |
|Y |
+------+
5 rows in set (0.00 sec)

mysql> select id from tab1 left join tab2 using (id) where tab2.id is null;
+------+
| id |
+------+
|2 |
|A |
+------+
2 rows in set (0.00 sec)

mysql> select id from tab2 left join tab1 using (id) where tab1.id is null;
+------+
| id |
+------+
|3 |
|X |
+------+
2 rows in set (0.00 sec)

Geethanjali College of Engineering Page 97

 Database Management Systems Lab Dept. of CSE.

VIVA QUESTIONS

1. Explain the difference between Union and Union All.

UNION ALL command is equal to UNION command, except that UNION
ALL selects all the values. The difference between Union and Union all is that Union
all will not eliminate duplicate rows, instead it just pulls all the rows from all the tables
fitting your query specifics and combines them into a table.

2. List out the Set Operators.

Types of SQL Set Operators with Examples
• Union Set Operator. The UNION set operator is used to combine the results obtained
from two or more SELECT statements.
• Union All Set Operator.
• Intersect Set Operator.
• Minus Set Operator.

Work space:

Geethanjali College of Engineering Page 98

 Database Management Systems Lab Dept. of CSE.

WEEK 12 : VIEWS Date:

VIEW: In SQL, a view is a virtual table based on the result-set of an SQL statement.
A view contains rows and columns, just like a real table. The fields in a view are fields from one
or more real tables in the database. You can add SQL functions, WHERE, and JOIN statements
to a view and present the data as if the data were coming from one single table.
A view is a virtual table, which consists of a set of columns from one or more tables. It is similar
to a table but it does not store in the database. View is a query stored as an object.

Syntax: CREATE VIEW view name AS SELECT set of fields FROM relation name
WHERE (Condition)

SIMPLE VIEW:-
-------------:- It contains one base table and it retrieves data from that base table.It supports dml
commands.

ex:-
---:-

mysql> select * from student;

+-------+---------+------------+
| st_id | st_name | st_dob |
+-------+---------+------------+
| 1 | james | 2021-01-01 |
| 2 | adams | 2020-01-01 |
| 3 | scott | 2011-06-06 |
| 4 | raju | 2021-02-02 |
+-------+---------+------------+
4 rows in set (0.06 sec)

mysql> create view s_view as select * from student;

Query OK, 0 rows affected (1.27 sec)

mysql> select * from s_view;

+-------+---------+------------+
| st_id | st_name | st_dob |
+-------+---------+------------+
| 1 | james | 2021-01-01 |
| 2 | adams | 2020-01-01 |
| 3 | scott | 2011-06-06 |
| 4 | raju | 2021-02-02 |
+-------+---------+------------+
4 rows in set (0.07 sec)

Geethanjali College of Engineering Page 99

 Database Management Systems Lab Dept. of CSE.

mysql> insert into s_view(st_id,st_name) values(5,'AAA');

Query OK, 1 row affected (0.23 sec)

mysql> select * from s_view;

+-------+---------+------------+
| st_id | st_name | st_dob |
+-------+---------+------------+
| 1 | james | 2021-01-01 |
| 2 | adams | 2020-01-01 |
| 3 | scott | 2011-06-06 |
| 4 | raju | 2021-02-02 |
| 5 | AAA | NULL |
+-------+---------+------------+
5 rows in set (0.00 sec)

mysql> delete from student where st_id=5;

Query OK, 1 row affected (0.30 sec)

mysql> select * from s_view;

+-------+---------+------------+
| st_id | st_name | st_dob |
+-------+---------+------------+
| 1 | james | 2021-01-01 |
| 2 | adams | 2020-01-01 |
| 3 | scott | 2011-06-06 |
| 4 | raju | 2021-02-02 |
+-------+---------+------------+
4 rows in set (0.00 sec)

COMPLEX VIEW:-
-------------:-
It contains multiple base tables and it retrieves data from that base tables.It won't supports dml
commands.

mysql> create view c_view as select e.ename,e.sal,d.dname,d.loc from emp e,dept d where
e.deptno=d.deptno;
Query OK, 0 rows affected (0.80 sec)

mysql> select * from c_view;

+--------+---------+------------+----------+
| ename | sal | dname | loc |
+--------+---------+------------+----------+
| CLARK | 2450.00 | ACCOUNTING | NEW YORK |
| KING | 5000.00 | ACCOUNTING | NEW YORK |
| MILLER | 1300.00 | ACCOUNTING | NEW YORK |
| SMITH | 800.00 | RESEARCH | DALLAS |
Geethanjali College of Engineering Page 100
 Database Management Systems Lab Dept. of CSE.

| JONES | 2975.00 | RESEARCH | DALLAS |

| SCOTT | 3000.00 | RESEARCH | DALLAS |
| ADAMS | 1100.00 | RESEARCH | DALLAS |
| FORD | 3000.00 | RESEARCH | DALLAS |
| ALLEN | 1600.00 | SALES | CHICAGO |
| WARD | 1250.00 | SALES | CHICAGO |
| MARTIN | 1250.00 | SALES | CHICAGO |
| BLAKE | 2850.00 | SALES | CHICAGO |
| TURNER | 1500.00 | SALES | CHICAGO |
| JAMES | 950.00 | SALES | CHICAGO |
+--------+---------+------------+----------+
14 rows in set (1.15 sec)

MODIFYING THE VIEW:-

-------------------:-
mysql> create or replace view c_view as select * from dept;
Query OK, 0 rows affected (0.37 sec)

mysql> select * from c_view;

+--------+------------+----------+
| deptno | dname | loc |
+--------+------------+----------+
| 10 | ACCOUNTING | NEW YORK |
| 20 | RESEARCH | DALLAS |
| 30 | SALES | CHICAGO |
| 40 | OPERATIONS | BOSTON |
+--------+------------+----------+
4 rows in set (0.00 sec)

DROPPING THE VIEW:-

------------------:-
It is used to dropping the view from the database.

general syntax:-
---------------:-
drop view view_name;

ex:-
----:-

mysql> drop view s_view;

Query OK, 0 rows affected (0.35 sec)

mysql> desc s_view;

ERROR 1146 (42S02): Table 'my_db.s_view' doesn't exist

Geethanjali College of Engineering Page 101

 Database Management Systems Lab Dept. of CSE.

VIVA QUESTIONS

1. What are aggregate functions?

SQL Aggregate Functions. SQL aggregation function is used to perform the calculations
on multiple rows of a single column of a table. It returns a single value. It is also used to
summarize the data.
• AVG – calculates the average of a set of values.
• COUNT – counts rows in a specified table or view.
• MIN – gets the minimum value in a set of values.
• MAX – gets the maximum value in a set of values.
• SUM – calculates the sum of values.

2. Define View?
A view is a subset of a database that is generated from a query and stored as a permanent
object. Although the definition of a view is permanent, the data contained therein is
dynamic depending on the point in time at which the view is accessed. Views represent a
subset of the data contained in a table.

3. What is the difference between group by and order by clause?

In group by clause, the tuples are grouped based on the similarity between the attribute
values of tuples. Whereas in order by clause, the result-set is sorted based on ascending
or descending order.

Work space:

Geethanjali College of Engineering Page 102

 Database Management Systems Lab Dept. of CSE.

WEEK 13: Date:

Triggers: Creation of INSERT TRIGGER, DELETE TRIGGER, UPDATE TRIGGER.

Objectives:
Student will able to learn to monitor a database and take initiate action when a condition occurs.

Outcomes:
Student gains the ability to change database manager from a passive system to an active one.

MySQL Trigger:-
-----------------:-

A trigger in MySQL is a set of SQL statements that reside in a system catalog. It is a special type
of stored procedure that is invoked automatically in response to an event. Each trigger is associated
with a table, which is activated on any DML statement such as INSERT, UPDATE, or DELETE.

A trigger is called a special procedure because it cannot be called directly like a stored procedure.
The main difference between the trigger and procedure is that a trigger is called automatically
when a data modification event is made against a table. In contrast, a stored procedure must be
called explicitly.

Generally, triggers are of two types according to the SQL standard: row-level triggers and
statement-level triggers.

Row-Level Trigger: It is a trigger, which is activated for each row by a triggering statement such
as insert, update, or delete. For example, if a table has inserted, updated, or deleted multiple rows,
the row trigger is fired automatically for each row affected by the insert, update, or delete
statement.

Statement-Level Trigger: It is a trigger, which is fired once for each event that occurs on a table
regardless of how many rows are inserted, updated, or deleted.

Types of Triggers in MySQL?

----------------------------:-
We can define the maximum six types of actions or events in the form of triggers:

Before Insert : It is activated before the insertion of data into the table.
After Insert : It is activated after the insertion of data into the table.
Before Update : It is activated before the update of data in the table.
After Update : It is activated after the update of the data in the table.
Before Delete : It is activated before the data is removed from the table.
After Delete : It is activated after the deletion of data from the table.

Geethanjali College of Engineering Page 103

 Database Management Systems Lab Dept. of CSE.

How to create triggers in MySQL?

---------------------------------:-

We can use the CREATE TRIGGER statement for creating a new trigger in MySQL. Below is the
syntax of creating a trigger in MySQL:

CREATE TRIGGER trigger_name

(AFTER | BEFORE) (INSERT | UPDATE | DELETE)
ON table_name FOR EACH ROW
BEGIN
--variable declarations
--trigger code
END;

ex:-
----:-
mysql> CREATE TABLE employee(
name varchar(45) NOT NULL,
occupation varchar(35) NOT NULL,
working_date date,
working_hours varchar(10)
);
Query OK, 0 rows affected (7.45 sec)

mysql> INSERT INTO employee VALUES

('Robin', 'Scientist', '2020-10-04', 12),
('Warner', 'Engineer', '2020-10-04', 10),
('Peter', 'Actor', '2020-10-04', 13),
('Marco', 'Doctor', '2020-10-04', 14),
('Brayden', 'Teacher', '2020-10-04', 12),
('Antonio', 'Business', '2020-10-04', 11);
Query OK, 6 rows affected (0.67 sec)
Records: 6 Duplicates: 0 Warnings: 0

mysql> select * from employee;

+---------+------------+--------------+---------------+
| name | occupation | working_date | working_hours |
+---------+------------+--------------+---------------+
| Robin | Scientist | 2020-10-04 | 12 |
| Warner | Engineer | 2020-10-04 | 10 |
| Peter | Actor | 2020-10-04 | 13 |
| Marco | Doctor | 2020-10-04 | 14 |
| Brayden | Teacher | 2020-10-04 | 12 |
| Antonio | Business | 2020-10-04 | 11 |
+---------+------------+--------------+---------------+
6 rows in set (0.00 sec)
Geethanjali College of Engineering Page 104
 Database Management Systems Lab Dept. of CSE.

Next, we will create a BEFORE INSERT trigger. This trigger is invoked automatically insert the
working_hours = 0 if someone tries to insert working_hours < 0.

ex:-
----:-

DELIMITER //
Create Trigger before_insert_empworkinghours
BEFORE INSERT ON employee FOR EACH ROW
BEGIN
IF NEW.working_hours < 0 THEN SET NEW.working_hours = 0;
END IF;
END //
Query OK, 0 rows affected (0.83 sec)

Now, we can use the following statements to invoke this trigger:

mysql> insert into employee values('Markus', 'Former', '2020-10-08', 14);

Query OK, 1 row affected (0.61 sec)

mysql> insert into employee values('Alexander', 'Actor', '2020-10-012', -13);

Query OK, 1 row affected (0.73 sec)

mysql> select * from employee;

+-----------+------------+--------------+---------------+
| name | occupation | working_date | working_hours |
+-----------+------------+--------------+---------------+
| Robin | Scientist | 2020-10-04 | 12 |
| Warner | Engineer | 2020-10-04 | 10 |
| Peter | Actor | 2020-10-04 | 13 |
| Marco | Doctor | 2020-10-04 | 14 |
| Brayden | Teacher | 2020-10-04 | 12 |
| Antonio | Business | 2020-10-04 | 11 |
| Markus | Former | 2020-10-08 | 14 |
| Alexander | Actor | 2020-10-12 | 0 |
+-----------+------------+--------------+---------------+
8 rows in set (0.00 sec)

In this output, we can see that on inserting the negative values into the working_hours column of
the table will automatically fill the zero value by a trigger.

mysql> SHOW TRIGGERS;

+-------------------------------+--------+----------+---------------------------------------------------------------
-------------------+--------+------------------------+--------------------------------------------+----------------
+----------------------+----------------------+--------------------+
| Trigger | Event | Table | Statement |
Geethanjali College of Engineering Page 105
 Database Management Systems Lab Dept. of CSE.

Timing | Created | sql_mode | Definer | character_set_client |

collation_connection | Database Collation |
+-------------------------------+--------+----------+---------------------------------------------------------------
-------------------+--------+------------------------+--------------------------------------------+----------------
+----------------------+----------------------+--------------------+
| before_insert_empworkinghours | INSERT | employee | BEGIN
IF NEW.working_hours < 0 THEN SET NEW.working_hours = 0;
END IF;
END | BEFORE | 2021-06-22 08:42:24.87 |
STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION | root@localhost | cp850 |
cp850_general_ci | utf8mb4_0900_ai_ci |
+-------------------------------+--------+----------+---------------------------------------------------------------
-------------------+--------+------------------------+--------------------------------------------+----------------
+----------------------+----------------------+--------------------+
1 row in set (0.33 sec)

mysql> drop trigger before_insert_empworkinghours;

Query OK, 0 rows affected (0.28 sec)

mysql> SHOW TRIGGERS;

Empty set (0.00 sec)

MySQL AFTER INSERT Trigger

-------------------------------:-

After Insert Trigger in MySQL is invoked automatically whenever an insert event occurs on the
table. In this article, we are going to learn how to create an after insert trigger with its syntax and
example.

Syntax:-
---------:-
The following is the syntax to create an AFTER INSERT trigger in MySQL:

CREATE TRIGGER trigger_name

AFTER INSERT
ON table_name FOR EACH ROW
trigger_body ;

The AFTER INSERT trigger syntax parameter can be explained as below:

-----------------------------------------------------------------------

* First, we will specify the name of the trigger that we want to create. It should be unique within
the schema.
* Second, we will specify the trigger action time, which should be AFTER INSERT clause to
invoke the trigger.
Geethanjali College of Engineering Page 106
 Database Management Systems Lab Dept. of CSE.

* Third, we will specify the name of a table to which the trigger is associated. It must be written
after the ON keyword. If we did not specify the table name, a trigger would not exist.
* Finally, we will specify the trigger body that contains one or more statements for execution when
the trigger is activated.

If we want to execute multiple statements, we will use the BEGIN END block that contains a set of
SQL queries to define the logic for the trigger. See the below syntax:

DELIMITER $$
CREATE TRIGGER trigger_name AFTER INSERT
ON table_name FOR EACH ROW
BEGIN
variable declarations
trigger code
END$$
DELIMITER ;

AFTER INSERT Trigger Example:-

----------------------------:-
Let us understand how to create an AFTER INSERT trigger using the CREATE TRIGGER
statement in MySQL with an example.

Suppose we have created a table named "student_info" as follows:

mysql> CREATE TABLE student_info (

stud_id int NOT NULL,
stud_code varchar(15) DEFAULT NULL,
stud_name varchar(35) DEFAULT NULL,
subject varchar(25) DEFAULT NULL,
marks int DEFAULT NULL,
phone varchar(15) DEFAULT NULL,
PRIMARY KEY (stud_id) ) ;
Query OK, 0 rows affected (2.13 sec)

mysql> insert into student_info values(1,101,'Mark','English',68,3456789234);

Query OK, 1 row affected (0.08 sec)

mysql> insert into student_info values(2,201,'Joseph','Physics',69,3956789234);

Query OK, 1 row affected (0.14 sec)

mysql> insert into student_info values(3,301,'Adams','Maths',60,2956789234);

Query OK, 1 row affected (0.10 sec)

mysql> select * from student_info;

+---------+-----------+-----------+---------+-------+------------+
| stud_id | stud_code | stud_name | subject | marks | phone |
Geethanjali College of Engineering Page 107
 Database Management Systems Lab Dept. of CSE.

+---------+-----------+-----------+---------+-------+------------+
| 1 | 101 | Mark | English | 68 | 3456789234 |
| 2 | 201 | Joseph | Physics | 69 | 3956789234 |
| 3 | 301 | Adams | Maths | 60 | 2956789234 |
+---------+-----------+-----------+---------+-------+------------+
3 rows in set (0.00 sec)

Again, we will create a new table named "student_detail" as follows:

mysql> CREATE TABLE student_detail (

stud_id int NOT NULL,
stud_code varchar(15) DEFAULT NULL,
stud_name varchar(35) DEFAULT NULL,
subject varchar(25) DEFAULT NULL,
marks int DEFAULT NULL,
phone varchar(15) DEFAULT NULL,
Lasinserted Time,
PRIMARY KEY (stud_id) );
Query OK, 0 rows affected (1.78 sec)

Next, we will use a CREATE TRIGGER statement to create an after_insert_details trigger on the
student_info table. This trigger will be fired after an insert operation is performed on the table.

DELIMITER //
Create Trigger after_insert_details
AFTER INSERT ON student_info FOR EACH ROW
BEGIN
INSERT INTO student_detail VALUES (new.stud_id, new.stud_code,
new.stud_name, new.subject, new.marks, new.phone, CURTIME());
END //
Query OK, 0 rows affected (0.25 sec)

How to call the AFTER INSERT trigger?

We can use the following statements to invoke the above-created trigger:

mysql> INSERT INTO student_info VALUES (10, 110, 'Alexandar', 'Biology', 67, '2347346438');
Query OK, 1 row affected (0.30 sec)

The table that has been modified after the update query executes is student_detail. We can verify it
by using the SELECT statement as follows:

mysql> SELECT * FROM student_detail;

+---------+-----------+-----------+---------+-------+------------+-------------+
| stud_id | stud_code | stud_name | subject | marks | phone | Lasinserted |
Geethanjali College of Engineering Page 108
 Database Management Systems Lab Dept. of CSE.

+---------+-----------+-----------+---------+-------+------------+-------------+
| 10 | 110 | Alexandar | Biology | 67 | 2347346438 | 09:18:33 |
+---------+-----------+-----------+---------+-------+------------+-------------+
1 row in set (0.00 sec)

In this output, we can see that on inserting values into the student_info table, the student_detail
table will automatically fill the records by invoking a trigger.

mysql> show triggers;

+----------------------+--------+--------------+--------------------------------------------------------------------
---------------------------------------------------------------------------+--------+------------------------+-------
-------------------------------------+----------------+----------------------+----------------------+---------------
-----+
| Trigger | Event | Table | Statement
| Timing | Created | sql_mode | Definer | character_set_client |
collation_connection | Database Collation |
+----------------------+--------+--------------+--------------------------------------------------------------------
---------------------------------------------------------------------------+--------+------------------------+-------
-------------------------------------+----------------+----------------------+----------------------+---------------
-----+
| after_insert_details | INSERT | student_info | BEGIN
INSERT INTO student_detail VALUES (new.stud_id, new.stud_code,
new.stud_name, new.subject, new.marks, new.phone, CURTIME());
END | AFTER | 2021-06-22 09:16:51.33 |
STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION | root@localhost | cp850 |
cp850_general_ci | utf8mb4_0900_ai_ci |
+----------------------+--------+--------------+--------------------------------------------------------------------
---------------------------------------------------------------------------+--------+------------------------+-------
-------------------------------------+----------------+----------------------+----------------------+---------------
-----+
1 row in set (0.02 sec)

mysql> create table medicine(mid int,medname varchar(10),price float,quantity int);

Query OK, 0 rows affected (3.06 sec)

mysql> insert into medicine values(1,'crocin',500.60,25), values(2,'calpol',255.90,30);

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds
to your MySQL server version for the right syntax to use near 'values(2,'calpol',255.90,30)' at line
2
mysql> insert into medicine values(1,'crocin',500.60,25),
-> (2,'calpol',355.50,30);
Query OK, 2 rows affected (2.18 sec)
Records: 2 Duplicates: 0 Warnings: 0

mysql> select * from medicine;

+------+---------+-------+----------+
Geethanjali College of Engineering Page 109
 Database Management Systems Lab Dept. of CSE.

| mid | medname | price | quantity |

+------+---------+-------+----------+
| 1 | crocin | 500.6 | 25 |
| 2 | calpol | 355.5 | 30 |
+------+---------+-------+----------+
2 rows in set (0.06 sec)

mysql> create table neworder(mid int,odate date,newqty int);

Query OK, 0 rows affected (0.71 sec)

mysql> desc neworder;

+--------+------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+------+------+-----+---------+-------+
| mid | int | YES | | NULL | |
| odate | date | YES | | NULL | |
| newqty | int | YES | | NULL | |
+--------+------+------+-----+---------+-------+
3 rows in set (0.39 sec)

mysql> deliniter $$
-> ;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds
to your MySQL server version for the right syntax to use near 'deliniter $$' at line 1
delimiter $$
create trigger neworderformedicine
before update on medicine
for each row
begin
if new.quantity<20 then
insert into neworder values(new.mid,sysdate(),200);
end if;
end;
$$
Query OK, 0 rows affected (0.24 sec)

mysql> select * from neworder $$

Empty set (0.08 sec)

mysql> update medicine set quantity=15 where mid=1;

-> $$
Query OK, 1 row affected (0.41 sec)
Rows matched: 1 Changed: 1 Warnings: 0

mysql> select * from medicine $$

+------+---------+-------+----------+
Geethanjali College of Engineering Page 110
 Database Management Systems Lab Dept. of CSE.

| mid | medname | price | quantity |

+------+---------+-------+----------+
| 1 | crocin | 500.6 | 15 |
| 2 | calpol | 355.5 | 30 |
+------+---------+-------+----------+
2 rows in set (0.00 sec)

mysql> select * from neworder $$

+------+------------+--------+
| mid | odate | newqty |
+------+------------+--------+
| 1 | 2021-06-24 | 200 |
+------+------------+--------+
1 row in set (0.00 sec)

After Update:-
=============:-
We will be creating two tables, patient_food_allergies,patient_allergy_modification_history and
inserting some rows into patient_food_allergies.

#create the table patient_food_allergies

CREATE TABLE patient_food_allergies (
patient_id INT,
patient_food_allergy VARCHAR(255)
);

#inserting rows into patient_food_allergies

INSERT INTO patient_food_allergies (patient_id, patient_food_allergy)
VALUES(1,"Milk"),
(2,"Nuts"),
(3,"Wheat"),
(4,"Nuts"),
(5,"Nuts"),
(6,"Pumpkin");

#create the table patient_allergy_modification_history

CREATE TABLE patient_allergy_modification_history (
patient_id INT,
patient_food_allergy VARCHAR(255),
patient_food_allergy_changed VARCHAR(255),
date_of_change DATE
);

SELECT * FROM patient_food_allergies;

mysql> SELECT * FROM patient_food_allergies //

Geethanjali College of Engineering Page 111
 Database Management Systems Lab Dept. of CSE.

+------------+----------------------+
| patient_id | patient_food_allergy |
+------------+----------------------+
| 1 | Milk |
| 2 | Nuts |
| 3 | Wheat |
| 4 | Nuts |
| 5 | Nuts |
| 6 | Pumpkin |
+------------+----------------------+
6 rows in set (0.00 sec)

Syntax of AFTER UPDATE trigger:-

=================================

CREATE TRIGGER name_of_your_trigger

AFTER UPDATE
ON name_of_your_table FOR EACH ROW
trigger_body;

Example of AFTER UPDATE trigger

--------------------------------:-
Create AFTER UPDATE trigger- whenever patient_food_allergy gets updated on the table
patient_food_allergies, the trigger should save the old and new values of patient_food_allergy and
the date of modification in the patient_allergy_modification_history table.

DELIMITER //
CREATE TRIGGER after_update_patient_food_allergies
AFTER UPDATE
ON patient_food_allergies FOR EACH ROW
BEGIN
IF (OLD.patient_food_allergy <> new.patient_food_allergy)
THEN
INSERT INTO patient_allergy_modification_history(patient_id,
patient_food_allergy, patient_food_allergy_changed, date_of_change)
VALUES(old.patient_id, old.patient_food_allergy, new.patient_food_allergy, sysdate());
END IF;
END//

mysql> select * from patient_food_allergies //

+------------+----------------------+
| patient_id | patient_food_allergy |
+------------+----------------------+
| 1 | Milk |
| 2 | Nuts |
Geethanjali College of Engineering Page 112
 Database Management Systems Lab Dept. of CSE.

| 3 | Wheat |
| 4 | Nuts |
| 5 | Nuts |
| 6 | Pumpkin |
+------------+----------------------+
6 rows in set (0.12 sec)

mysql> select * from patient_allergy_modification_history //

Empty set (0.04 sec)

mysql> UPDATE patient_food_allergies SET patient_food_allergy = 'Peanuts' WHERE patient_id

= 4 //
Query OK, 1 row affected (0.18 sec)
Rows matched: 1 Changed: 1 Warnings: 0

mysql> select * from patient_allergy_modification_history //

+------------+----------------------+------------------------------+----------------+
| patient_id | patient_food_allergy | patient_food_allergy_changed | date_of_change |
+------------+----------------------+------------------------------+----------------+
| 4 | Nuts | Peanuts | 2021-07-02 |
+------------+----------------------+------------------------------+----------------+
1 row in set (0.00 sec)

mysql> select * from patient_food_allergies //

+------------+----------------------+
| patient_id | patient_food_allergy |
+------------+----------------------+
| 1 | Milk |
| 2 | Nuts |
| 3 | Wheat |
| 4 | Peanuts |
| 5 | Nuts |
| 6 | Pumpkin |
+------------+----------------------+
6 rows in set (0.00 sec)

Before Delete:-
--------------:-
* create a trigger which will work before deleteion in employee table and create a copy of the
record in another table called emp-backup two tables.(employee,emp-backup)

employee(empid,ename,job,doj,salary)
emp_backup(empid,ename,job,doj,salary)

Geethanjali College of Engineering Page 113

 Database Management Systems Lab Dept. of CSE.

mysql> create table emp1(emp_id int,ename varchar(10),job varchar(10),doj date,salary float) //

Query OK, 0 rows affected (0.70 sec)

mysql> create table emp_backup(emp_id int,ename varchar(10),job varchar(10),doj date,salary

float) //
Query OK, 0 rows affected (1.94 sec)

mysql> insert into emp1 values(111,'AAA','CLERK','2021-01-01',1000.50) //

Query OK, 1 row affected (0.15 sec)

mysql> insert into emp1 values(222,'BBB','MANAGER','2021-02-01',2000.50) //

Query OK, 1 row affected (0.11 sec)

mysql> insert into emp1 values(333,'CCC','ANALYST','2021-03-01',1500.50) //

Query OK, 1 row affected (0.10 sec)

mysql> SELECT * FROM EMP1 //

+--------+-------+---------+------------+--------+
| emp_id | ename | job | doj | salary |
+--------+-------+---------+------------+--------+
| 111 | AAA | CLERK | 2021-01-01 | 1000.5 |
| 222 | BBB | MANAGER | 2021-02-01 | 2000.5 |
| 333 | CCC | ANALYST | 2021-03-01 | 1500.5 |
+--------+-------+---------+------------+--------+
3 rows in set (0.00 sec)

mysql> SELECT * FROM EMP_BACKUP //

Empty set (0.15 sec)

EX:-
----:-

delimiter //
create trigger bef_delete_emp before delete on employee for each row
begin
insert into emp_backup values(old.empid,old.ename,old.job,old.doj,old.salary);
end //
Query OK, 0 rows affected (0.19 sec)

mysql> select * from employe //

+--------+-------+---------+------------+--------+
| emp_id | ename | job | doj | salary |
+--------+-------+---------+------------+--------+
| 111 | AAA | CLERK | 2021-01-01 | 1000.5 |
| 222 | BBB | MANAGER | 2021-02-01 | 2000.5 |
| 333 | CCC | ANALYST | 2021-03-01 | 1500.5 |
Geethanjali College of Engineering Page 114
 Database Management Systems Lab Dept. of CSE.

+--------+-------+---------+------------+--------+
3 rows in set (0.03 sec)

mysql> select * from emp_backup //

Empty set (0.05 sec)

mysql> delete from employe where emp_id=333 //

Query OK, 1 row affected (0.07 sec)

mysql> select * from emp_backup //

+--------+-------+---------+------------+--------+
| emp_id | ename | job | doj | salary |
+--------+-------+---------+------------+--------+
| 333 | CCC | ANALYST | 2021-03-01 | 1500.5 |
+--------+-------+---------+------------+--------+
1 row in set (0.00 sec)

After Delete:-
=============:-
It is activated after the deletion of data from the table.
Here we are taking two tables i,e. employe and salarybudget.

mysql> SELECT * FROM EMPLOYE //

+--------+-------+---------+------------+--------+
| emp_id | ename | job | doj | salary |
+--------+-------+---------+------------+--------+
| 111 | AAA | CLERK | 2021-01-01 | 1000.5 |
| 222 | BBB | MANAGER | 2021-02-01 | 2000.5 |
| 333 | CCC | ANALYST | 2021-03-01 | 3000.5 |
| 444 | DDD | ANALYST | 2021-04-01 | 4000.5 |
| 555 | EEE | ANALYST | 2021-05-01 | 5000.5 |
+--------+-------+---------+------------+--------+
5 rows in set (0.00 sec)

mysql> CREATE TABLE SALARYBUDGET(TOTAL_SALARY FLOAT) //

Query OK, 0 rows affected (2.06 sec)

mysql> insert into salarybudget select sum(salary) from employe //

Query OK, 1 row affected (0.30 sec)
Records: 1 Duplicates: 0 Warnings: 0

mysql> select * from employe //

+--------+-------+---------+------------+--------+
| emp_id | ename | job | doj | salary |
Geethanjali College of Engineering Page 115
 Database Management Systems Lab Dept. of CSE.

+--------+-------+---------+------------+--------+
| 111 | AAA | CLERK | 2021-01-01 | 1000.5 |
| 222 | BBB | MANAGER | 2021-02-01 | 2000.5 |
| 333 | CCC | ANALYST | 2021-03-01 | 3000.5 |
| 444 | DDD | ANALYST | 2021-04-01 | 4000.5 |
| 555 | EEE | ANALYST | 2021-05-01 | 5000.5 |
+--------+-------+---------+------------+--------+
5 rows in set (0.00 sec)

mysql> select * from salarybudget //

+--------------+
| TOTAL_SALARY |
+--------------+
| 15002.5 |
+--------------+
1 row in set (0.00 sec)

DELIMITER //
create trigger after_del after delete on employee for each row
begin
update salarybudget set total_salary=total_salary-old.salary;
end //

mysql> select * from salarybudget //

+--------------+
| TOTAL_SALARY |
+--------------+
| 10002 |
+--------------+
1 row in set (0.00 sec)

mysql> select * from employe //

+--------+-------+---------+------------+--------+
| emp_id | ename | job | doj | salary |
+--------+-------+---------+------------+--------+
| 111 | AAA | CLERK | 2021-01-01 | 1000.5 |
| 222 | BBB | MANAGER | 2021-02-01 | 2000.5 |
| 333 | CCC | ANALYST | 2021-03-01 | 3000.5 |
| 444 | DDD | ANALYST | 2021-04-01 | 4000.5 |
+--------+-------+---------+------------+--------+
4 rows in set (0.00 sec)

Geethanjali College of Engineering Page 116

 Database Management Systems Lab Dept. of CSE.

VIVA QUESTIONS

1. What is Trigger?
A trigger is a stored procedure in database which automatically invokes whenever a
special event in the database occurs. For example, a trigger can be invoked when a row is
inserted into a specified table or when certain table columns are being updated.

2. What is Nested Trigger?

Both DML and DDL triggers are nested when a trigger performs an action that initiates
another trigger. These actions can initiate other triggers, and so on. DML and
DDL triggers can be nested up to 32 levels. You can control whether AFTER triggers can
be nested through the nested triggers server configuration option.

3. What are the types on Triggers?

In SQL Server we can create four types of triggers Data Definition Language
(DDL) triggers, Data Manipulation Language (DML) triggers, CLR triggers, and
Logon triggers.

4. What is the difference between Before Trigger and After trigger?

Before triggers execute before the data has been committed into the database. After
triggers execute after the data has been inserted or updated in the database. Usually, after
triggers are used because you need access to a formula field or the Id in the case of an
insert.

Work space:

Geethanjali College of Engineering Page 117

 Database Management Systems Lab Dept. of CSE.

WEEK 14: Date:

Procedures: Creation of stored procedures, Execution of procedure and modification of
procedures.
Objectives:
Student will be able to learn the features like reusability, maintainability and modularity.
Student will be able to learn to develop procedures and function for various operation.

Outcomes:
Student gains the knowledge to implement procedures and function for various operations.

Stored procedures:-
----------------------:- procedure is a database object, procedures may or maynot return a value.
The procedure parameters are, in, out, in out.

Structure of the Stored Procedure:-

---------------------------------:-
DELIMITER &&
CREATE PROCEDURE procedure_name [[IN | OUT | INOUT] parameter_name datatype [,
parameter datatype]) ]
BEGIN
Declaration_section
Executable_section
END &&
DELIMITER ;

How to call a stored procedure?:-

---------------------------------:-
CALL procedure_name ( parameter(s))

ex:-
----:-

mysql> DELIMITER $$
mysql> CREATE PROCEDURE get_merit_student ()
BEGIN
SELECT * FROM student_info WHERE marks > 60;
SELECT COUNT(stud_code) AS Total_Student FROM student_info;
END $$
Query OK, 0 rows affected (0.25 sec)

mysql> call get_merit_student() $$

+---------+-----------+-----------+---------+-------+------------+
| stud_id | stud_code | stud_name | subject | marks | phone |
Geethanjali College of Engineering Page 118
 Database Management Systems Lab Dept. of CSE.

+---------+-----------+-----------+---------+-------+------------+
| 1 | 101 | Mark | English | 68 | 3456789234 |
| 2 | 201 | Joseph | Physics | 69 | 3956789234 |
| 10 | 110 | Alexandar | Biology | 67 | 2347346438 |
+---------+-----------+-----------+---------+-------+------------+
3 rows in set (0.09 sec)

+---------------+
| Total_Student |
+---------------+
| 4|
+---------------+
1 row in set (0.13 sec)

Query OK, 0 rows affected (0.17 sec)

How to show procedure in database..?

------------------------------------:-
SHOW PROCEDURE STATUS WHERE db = 'my_db' $$

How to drop the procedure:-

--------------------------

mysql> drop procedure get_merit_student $$

Query OK, 0 rows affected (0.21 sec)

Procedures with IN Parameter:-

------------------------------:-

mysql> DELIMITER $$
mysql> CREATE PROCEDURE get_student (IN var1 INT)
-> BEGIN
-> SELECT * FROM student_info LIMIT var1;
-> SELECT COUNT(stud_code) AS Total_Student FROM student_info;
-> END $$;
Query OK, 0 rows affected (0.36 sec)

-> $$
ERROR 1065 (42000): Query was empty
mysql> select * from student_info;
-> $$
+---------+-----------+-----------+---------+-------+------------+
| stud_id | stud_code | stud_name | subject | marks | phone |
+---------+-----------+-----------+---------+-------+------------+
| 1 | 101 | Mark | English | 68 | 3456789234 |
| 2 | 201 | Joseph | Physics | 69 | 3956789234 |
Geethanjali College of Engineering Page 119
 Database Management Systems Lab Dept. of CSE.

| 3 | 301 | Adams | Maths | 60 | 2956789234 |

| 10 | 110 | Alexandar | Biology | 67 | 2347346438 |
+---------+-----------+-----------+---------+-------+------------+
4 rows in set (0.01 sec)

mysql> call get_student(4)$$

+---------+-----------+-----------+---------+-------+------------+
| stud_id | stud_code | stud_name | subject | marks | phone |
+---------+-----------+-----------+---------+-------+------------+
| 1 | 101 | Mark | English | 68 | 3456789234 |
| 2 | 201 | Joseph | Physics | 69 | 3956789234 |
| 3 | 301 | Adams | Maths | 60 | 2956789234 |
| 10 | 110 | Alexandar | Biology | 67 | 2347346438 |
+---------+-----------+-----------+---------+-------+------------+
4 rows in set (0.01 sec)

+---------------+
| Total_Student |
+---------------+
| 4 |
+---------------+
1 row in set (0.03 sec)

Query OK, 0 rows affected (0.03 sec)

mysql> call get_student(3)$$

+---------+-----------+-----------+---------+-------+------------+
| stud_id | stud_code | stud_name | subject | marks | phone |
+---------+-----------+-----------+---------+-------+------------+
| 1 | 101 | Mark | English | 68 | 3456789234 |
| 2 | 201 | Joseph | Physics | 69 | 3956789234 |
| 3 | 301 | Adams | Maths | 60 | 2956789234 |
+---------+-----------+-----------+---------+-------+------------+
3 rows in set (0.00 sec)

+---------------+
| Total_Student |
+---------------+
| 4|
+---------------+
1 row in set (0.03 sec)

Query OK, 0 rows affected (0.05 sec)

Geethanjali College of Engineering Page 120

 Database Management Systems Lab Dept. of CSE.

Procedures with OUT Parameter:-

-----------------------------:-

mysql> DELIMITER $$
mysql> CREATE PROCEDURE display_max_mark (OUT highestmark INT)
-> BEGIN
-> SELECT MAX(marks) INTO highestmark FROM student_info;
-> END $$;
Query OK, 0 rows affected (0.27 sec)

->
-> $$
ERROR 1065 (42000): Query was empty
mysql> call display_max_mark $$
ERROR 1318 (42000): Incorrect number of arguments for PROCEDURE
my_db.display_max_mark; expected 1, got 0
mysql> call display_max_mark(@a) $$
Query OK, 1 row affected (0.02 sec)

mysql> select @a;

-> $$
+------+
| @a |
+------+
| 69 |
+------+
1 row in set (0.00 sec)

mysql> select * from student_info $$

+---------+-----------+-----------+---------+-------+------------+
| stud_id | stud_code | stud_name | subject | marks | phone |
+---------+-----------+-----------+---------+-------+------------+
| 1 | 101 | Mark | English | 68 | 3456789234 |
| 2 | 201 | Joseph | Physics | 69 | 3956789234 |
| 3 | 301 | Adams | Maths | 60 | 2956789234 |
| 10 | 110 | Alexandar | Biology | 67 | 2347346438 |
+---------+-----------+-----------+---------+-------+------------+
4 rows in set (0.00 sec)

Procedures with INOUT Parameter:-

----------------------------------:-

mysql> DELIMITER $$
mysql> CREATE PROCEDURE display_marks (INOUT var1 INT)
-> BEGIN
Geethanjali College of Engineering Page 121
 Database Management Systems Lab Dept. of CSE.

-> SELECT marks INTO var1 FROM student_info WHERE stud_id = var1;
-> END $$
Query OK, 0 rows affected (0.23 sec)

mysql> select * from student_info $$

+---------+-----------+-----------+---------+-------+------------+
| stud_id | stud_code | stud_name | subject | marks | phone |
+---------+-----------+-----------+---------+-------+------------+
| 1 | 101 | Mark | English | 68 | 3456789234 |
| 2 | 201 | Joseph | Physics | 69 | 3956789234 |
| 3 | 301 | Adams | Maths | 60 | 2956789234 |
| 10 | 110 | Alexandar | Biology | 67 | 2347346438 |
+---------+-----------+-----------+---------+-------+------------+
4 rows in set (0.00 sec)

mysql> set @a='3' $$

Query OK, 0 rows affected (0.02 sec)

mysql> call display_marks(@a) $$

Query OK, 1 row affected (0.02 sec)

mysql> select @a $$
+------+
| @a |
+------+
| 60 |
+------+
1 row in set (0.00 sec)

How to Alter procedure:-

-----------------------------:-

mysql> alter procedure get_merit_student

-> comment 'it displays all records' $$
Query OK, 0 rows affected (0.16 sec)

mysql> SHOW CREATE PROCEDURE get_merit_student $$

+-------------------+--------------------------------------------+---------------------------------------------------
-----------------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------+----------------------+---------------------
-+--------------------+
| Procedure | sql_mode | Create Procedure
| character_set_client | collation_connection | Database Collation |
+-------------------+--------------------------------------------+---------------------------------------------------
-----------------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------+----------------------+---------------------
Geethanjali College of Engineering Page 122
 Database Management Systems Lab Dept. of CSE.

-+--------------------+
| get_merit_student | STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION | CREATE
DEFINER=`root`@`localhost` PROCEDURE `get_merit_student`()
COMMENT 'it displays all records'
BEGIN
SELECT * FROM student_info WHERE marks > 60;
SELECT COUNT(stud_code) AS Total_Student FROM student_info;
END | cp850 | cp850_general_ci | utf8mb4_0900_ai_ci |
+-------------------+--------------------------------------------+---------------------------------------------------
-----------------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------+----------------------+---------------------
-+--------------------+
1 row in set (0.00 sec)

drop procedure:-
===============:- It is used to remove the procedures from the database.

syntax:-
--------:- drop procedure procedure_name;

ex:-
---:-

mysql> drop procedure get_student $$

Query OK, 0 rows affected (0.27 sec)

VIVA QUESTIONS

1. What is difference between Function and Stored Procedure?

Stored Procedures can call functions. Functions can be called from a select
statement. Procedures can't be called from Select/Where/Having and so on statements.
Execute/Exec statement can be used to call/execute Stored Procedure. A UDF can be used
in join clause as a result set.

2. What is Stored Procedure?

Stored Procedures are created to perform one or more DML operations on Database. It is
nothing but the group of SQL statements that accepts some input in the form of parameters
and performs some task and may or may not returns a value. Parameters are used to pass
values to the Procedure.

3. What is PL/SQL?
PL/SQL is a block of codes that used to write the entire program blocks/ procedure/
function, etc. It is declarative, that defines what needs to be done, rather than how things
need to be done. PL/SQL is procedural that defines how the things needs to be done. It is

Geethanjali College of Engineering Page 123

 Database Management Systems Lab Dept. of CSE.

an extension of SQL, so it can contain SQL inside it.

4. Show how functions and procedures are called in a PL/SQL block?

Procedures and functions defined within a package are known as packaged
subprograms. Procedures and functions nested inside other subprograms or within
a PL/SQL block are known as local subprograms, which cannot be referenced by other
applications and exist only inside of the enclosing block. You can call stored procedures or
functions from a database trigger, another stored subprogram, or interactively from SQL
Command Line (SQL*Plus). You can also configure a Web server so that the HTML for a
Web page is generated by a stored subprogram, making it simple to provide a Web interface
for data entry and report generation.

Work space:

Geethanjali College of Engineering Page 124

 Database Management Systems Lab Dept. of CSE.

15. ADDITIONAL QUERIES:

Sailors (sid: integer, sname: string, rating: integer, age: real);
Boats (bid: integer, bname: string, color: string);
Reserves (sid: integer, bid: integer, day: date).
mysql> CREATE TABLE sailors ( sid integer not null,
-> sname varchar(32),
-> rating integer,
-> age real,
-> CONSTRAINT PK_sailors PRIMARY KEY (sid) );
Query OK, 0 rows affected (0.55 sec)
mysql> desc sailors;
+--------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| sid | int | NO | PRI | NULL | |
| sname | varchar(32) | YES | | NULL | |
| rating | int | YES | | NULL | |
| age | double | YES | | NULL | |
+------ --+-------------+------+-----+---------+-------+
4 rows in set (0.09 sec)
mysql> insert into sailors values(22,'Dustin',7,45),
-> (29,'Brutus',1,33),
-> (31,'Lubber',8,55.5),
-> (32,'Andy',8,25.5),
-> (58,'Rusty',10,35),
-> (64,'Horatio',7,35),
-> (71,'Zorba',10,16),
-> (74,'Horatio',9,40),
-> (85,'Art',3,25.5),
-> (95,'Bob',3,63.5);
Query OK, 10 rows affected (0.19 sec)
Records: 10 Duplicates: 0 Warnings: 0
mysql> select * from sailors;
+-----+---------+--------+------+
| sid | sname | rating | age |
+-----+---------+--------+------+
| 22 | Dustin | 7 | 45 |
| 29 | Brutus | 1 | 33 |
Geethanjali College of Engineering Page 125
 Database Management Systems Lab Dept. of CSE.

| 31 | Lubber | 8 | 55.5 |
| 32 | Andy | 8 | 25.5 |
| 58 | Rusty | 10 | 35 |
| 64 | Horatio | 7 | 35 |
| 71 | Zorba | 10 | 16 |
| 74 | Horatio | 9 | 40 |
| 85 | Art | 3 | 25.5 |
| 95 | Bob | 3 | 63.5 |
+-----+---------+--------+------+
10 rows in set (0.00 sec)
mysql> create table boats(bid int primary key,
-> bname varchar(10),
-> color varchar(10));
Query OK, 0 rows affected (0.33 sec)
mysql> desc boats;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| bid | int | NO | PRI | NULL | |
| bname | varchar(10) | YES | | NULL | |
| color | varchar(10) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.04 sec)
mysql> insert into boats values(101,'Interlake','blue'),
-> (102,'Interlake','red'),
-> (103,'Clipper','green'),
-> (104,'Marine','red');
Query OK, 4 rows affected (0.31 sec)
Records: 4 Duplicates: 0 Warnings: 0

mysql> select * from boats;

+-----+-----------+-------+
| bid | bname | color |
+-----+-----------+-------+
| 101 | Interlake | blue |
| 102 | Interlake | red |
| 103 | Clipper | green |
| 104 | Marine | red |
+-----+-----------+-------+
4 rows in set (0.00 sec)mysql> insert into reserves values(22,101,'1998-10-10'),
Geethanjali College of Engineering Page 126
 Database Management Systems Lab Dept. of CSE.

-> (22,102,'1998-10-10'),
-> (22,103,'1998-10-8'),
-> (22,104,'1998-10-7'),
-> (31,102,'1998-11-10'),
-> (31,103,'1998-11-6'),
-> (31,104,'1998-11-12'),
-> (64,101,'1998-9-5'),
-> (64,102,'1998-9-8'),
-> (74,103,'1998-9-8');
Query OK, 10 rows affected (0.34 sec)
Records: 10 Duplicates: 0 Warnings: 0
mysql> select * from reserves;
+-----+-----+---------------------+
| sid | bid | day |
+-----+-----+---------------------+
| 22 | 101 | 1998-10-10 00:00:00 |
| 64 | 101 | 1998-09-05 00:00:00 |
| 22 | 102 | 1998-10-10 00:00:00 |
| 31 | 102 | 1998-11-10 00:00:00 |
| 64 | 102 | 1998-09-08 00:00:00 |
| 22 | 103 | 1998-10-08 00:00:00 |
| 31 | 103 | 1998-11-06 00:00:00 |
| 74 | 103 | 1998-09-08 00:00:00 |
| 22 | 104 | 1998-10-07 00:00:00 |
| 31 | 104 | 1998-11-12 00:00:00 |
+-----+-----+---------------------+
10 rows in set (0.01 sec)

Ex1: Display the name and age of sailors table.

mysql> select sname,age from sailors;
+---------+------+
| sname | age |
+---------+------+
| Dustin | 45 |
| Brutus | 33 |
| Lubber | 55.5 |
| Andy | 25.5 |
| Rusty | 35 |
| Horatio | 35 |
| Zorba | 16 |
Geethanjali College of Engineering Page 127
 Database Management Systems Lab Dept. of CSE.

| Horatio | 40 |
| Art | 25.5 |
| Bob | 63.5 |
+---------+------+
10 rows in set (0.00 sec)
(or)
mysql> select s.sname,s.age from sailors s;
+---------+------+
| sname | age |
+---------+------+
| Dustin | 45 |
| Brutus | 33 |
| Lubber | 55.5 |
| Andy | 25.5 |
| Rusty | 35 |
| Horatio | 35 |
| Zorba | 16 |
| Horatio| 40 |
| Art | 25.5 |
| Bob | 63.5 |
+---------+------+
10 rows in set (0.00 sec)

Ex2: Using DISTINCT

mysql> SELECT DISTINCT S.sname, S.age
-> FROM sailors AS S;
+---------+------+
| sname | age |
+---------+------+
| Dustin | 45 |
| Brutus | 33 |
| Lubber | 55.5 |
| Andy | 25.5 |
| Rusty | 35 |
| Horatio | 35 |
| Zorba | 16 |
| Horatio | 40 |
| Art | 25.5 |
| Bob | 63.5 |
+---------+------+
Geethanjali College of Engineering Page 128
 Database Management Systems Lab Dept. of CSE.

10 rows in set (0.00 sec)

Ex3:-Find all information of sailors who have reserved boat number 101.
mysql> SELECT S.*
-> FROM Sailors S, Reserves R
-> where s.sid=r.sid
-> and r.bid=101;
+-----+---------+--------+------+
| sid | sname | rating | age |
+-----+---------+--------+------+
| 22 | Dustin | 7 | 45 |
| 64 | Horatio | 7 | 35 |
+-----+---------+--------+------+
2 rows in set (0.01 sec)

Ex4:-
Find the names of sailors who have reserved a red boat, and list in the order of age.
mysql> SELECT S.sname, S.age
-> FROM Sailors S, Reserves R, Boats B
-> WHERE S.sid = R.sid AND R.bid = B.bid AND B.color ='red'
-> order by s.age;
+---------+------+
| sname | age |
+---------+------+
| Horatio | 35 |
| Dustin | 45 |
| Dustin | 45 |
| Lubber | 55.5 |
| Lubber | 55.5 |
+---------+------+
5 rows in set (0.05 sec)

Ex5:-
Find the names of sailors who have reserved at least one boat.
mysql> SELECT sname
-> FROM Sailors S, Reserves R
-> WHERE S.sid = R.sid;
+---------+
| sname |
+---------+
| Dustin |
Geethanjali College of Engineering Page 129
 Database Management Systems Lab Dept. of CSE.

| Dustin |
| Dustin |
| Dustin |
| Lubber |
| Lubber |
| Lubber |
| Horatio |
| Horatio |
| Horatio |
+---------+
10 rows in set (0.00 sec)

Ex6:-
Find the ids and names of sailors who have reserved two different boats on the same day
mysql> SELECT DISTINCT S.sid, S.sname
-> FROM Sailors S, Reserves R1, Reserves R2
-> WHERE S.sid = R1.sid AND S.sid = R2.sid
-> AND R1.day = R2.day AND R1.bid <> R2.bid;
+-----+--------+
| sid | sname |
+-----+--------+
| 22 | Dustin |
+-----+--------+
1 row in set (0.00 sec)

Ex7:-
Find the ids of sailors who have reserved a red boat or a green boat
mysql> select r.sid from boats b, reserves r
-> where r.bid=b.bid and b.color='red'
-> union
-> select r2.sid
-> from boats b2,reserves r2
-> where r2.bid=b2.bid and b2.color='green';
+-----+
| sid |
+-----+
| 22 |
| 31 |
| 64 |
| 74 |
Geethanjali College of Engineering Page 130
 Database Management Systems Lab Dept. of CSE.

+-----+
4 rows in set (0.00 sec)

Ex8:-
Find the All ids of sailors who have reserved a red boat or a green boat
mysql> select r.sid from boats b, reserves r
-> where r.bid=b.bid and b.color='red'
-> union all
-> select r2.sid
-> from boats b2,reserves r2
-> where r2.bid=b2.bid and b2.color='green';
+-----+
| sid |
+-----+
| 22 |
| 31 |
| 64 |
| 22 |
| 31 |
| 22 |
| 31 |
| 74 |
+-----+
8 rows in set (0.00 sec)

Ex9:-
Find the names of sailors who have reserved boat 103.
mysql> SELECT S.sname
-> FROM Sailors S
-> WHERE S.sid IN ( SELECT R.sid
-> FROM Reserves R
-> WHERE R.bid = 103 );
+---------+
| sname |
+---------+
| Dustin |
| Lubber |
| Horatio |
+---------+
3 rows in set (0.02 sec)
Geethanjali College of Engineering Page 131
 Database Management Systems Lab Dept. of CSE.

Ex10:-
Find the name and the age of the youngest sailor.
mysql> SELECT S.sname, S.age
-> FROM Sailors S
-> WHERE S.age <= ALL ( SELECT age
-> FROM Sailors );
+-------+------+
| sname | age |
+-------+------+
| Zorba | 16 |
+-------+------+
1 row in set (0.01 sec)

Ex11:-
Find the names and ratings of sailor whose rating is better than some sailor called Horatio.
mysql> select s.sname,s.rating
-> from sailors s
-> where s.rating>any(select s2.rating from sailors s2 where s2.sname='Horatio');
+---------+--------+
| sname | rating |
+---------+--------+
| Lubber | 8 |
| Andy | 8 |
| Rusty | 10 |
| Zorba | 10 |
| Horatio | 9 |
+---------+--------+
5 rows in set (0.01 sec)

Ex12:-
Count the number of different sailor names
mysql> SELECT COUNT( DISTINCT S.sname )
-> FROM Sailors S;
+---------------------------+
| COUNT( DISTINCT S.sname ) |
+---------------------------+
| 9|
+---------------------------+
1 row in set (0.02 sec)
Geethanjali College of Engineering Page 132
 Database Management Systems Lab Dept. of CSE.

Ex13:-
Calculate the average age of all sailors
mysql> SELECT AVG(s.age)
-> FROM Sailors S;
+------------+
| AVG(s.age) |
+------------+
| 37.4 |
+------------+
1 row in set (0.00 sec)

Ex14:-
Find the name and the age of the youngest sailor
mysql> SELECT S.sname, S.age
-> FROM Sailors S
-> WHERE S.age = (SELECT MIN(S2.age)
-> FROM Sailors S2 );
+-------+------+
| sname | age |
+-------+------+
| Zorba | 16 |
+-------+------+
1 row in set (0.00 sec)

Ex15:-
Find the average age of sailors for each rating level.
mysql> SELECT S.rating, AVG(S.age) AS avg_age
-> FROM Sailors S
-> GROUP BY S.rating;
+--------+---------+
| rating | avg_age |
+--------+---------+
| 7 | 40 |
| 1 | 33 |
| 8 | 40.5 |
| 10 | 25.5 |
| 9 | 40 |
| 3 | 44.5 |
+--------+---------+
Geethanjali College of Engineering Page 133
 Database Management Systems Lab Dept. of CSE.

6 rows in set (0.01 sec)

Ex16:-
Find the average age of sailors for each rating level that has at least two sailors.
mysql> SELECT S.rating, AVG(S.age) AS avg_age
-> FROM Sailors S
-> GROUP BY S.rating
-> HAVING COUNT(*) > 1;
+--------+---------+
| rating | avg_age |
+--------+---------+
| 7 | 40 |
| 8 | 40.5 |
| 10 | 25.5 |
| 3 | 44.5 |
+--------+---------+
4 rows in set (0.01 sec)

Ex17:-
An example shows difference between WHERE and HAVING:
mysql> SELECT S.rating, AVG(S.age) as avg_age
-> FROM Sailors S
-> WHERE S.age >=40
-> GROUP BY S.rating;
+--------+---------+
| rating | avg_age |
+--------+---------+
| 7 | 45 |
| 8 | 55.5 |
| 9 | 40 |
| 3 | 63.5 |
+--------+---------+
4 rows in set (0.01 sec)
mysql> SELECT S.rating, AVG(S.age) as avg_age
-> FROM Sailors S
-> GROUP BY S.rating
-> HAVING AVG(S.age) >= 40;
+--------+---------+
| rating | avg_age |
+--------+---------+
Geethanjali College of Engineering Page 134
 Database Management Systems Lab Dept. of CSE.

| 7 | 40 |
| 8 | 40.5 |
| 9 | 40 |
| 3 | 44.5 |
+--------+---------+
4 rows in set (0.00 sec)

Ex18:-
Find the names of sailors who have reserved all boats
mysql> SELECT S.sname
-> FROM Sailors S
-> WHERE NOT EXISTS ( SELECT B.bid
-> FROM Boats B
-> WHERE NOT EXISTS ( SELECT R.bid
-> FROM Reserves R
-> WHERE R.bid = B.bid
-> AND R.sid = S.sid ) );
+--------+
| sname |
+--------+
| Dustin |
+--------+
1 row in set (0.01 sec)

Geethanjali College of Engineering Page 135

 Database Management Systems Lab Dept. of CSE.

WEEK 16: Date:

Database Design and Implementation: MINI DATABASE PROJECT

Objectives:
Student will be able to learn to avoid problems that are associated with updating redundant data.

Outcomes:
Student gains the knowledge to build the database that does not have redundant data.

Work space:

Geethanjali College of Engineering Page 136