Scribd
Upload
32 views

Experiment 7 Dbms

The document discusses nested queries, set operators like union, intersect, and minus in MySQL. It provides the syntax and rules for using these operators. It also gives examples of applying nested queries and set operators to a case study with input and output.

Uploaded by

Raj
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
32 views

Experiment 7 Dbms

The document discusses nested queries, set operators like union, intersect, and minus in MySQL. It provides the syntax and rules for using these operators. It also gives examples of applying nested queries and set operators to a case study with input and output.

Uploaded by

Raj
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
You are on page 1/ 4

EXPERIMENT NO:-07

Aim: Perform Nested and Complex

Theory:
NESTED QUERIES:

A subquery in MySQL is a query, which is nested into another SQL query and
embedded with SELECT, INSERT, UPDATE or DELETE statement along with the
various operators. We can also nest the subquery with another subquery. A subquery
is known as the inner query, and the query that contains subquery is known as the
outer query. The inner query executed first gives the result to the outer query, and then
the main/outer query will be performed. MySQL allows us to use subquery anywhere,
but it must be closed within parenthesis. All subquery forms and operations
supported by the SQL standard will be supported in MySQL also.

The following are the rules to use subqueries:

o Subqueries should always use in parentheses.


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

The following are the advantages of using subqueries:

o The subqueries make the queries in a structured form that allows us to isolate each
part of a statement.
o The subqueries provide alternative ways to query the data from the table; otherwise,
we need to use complex joins and unions.
o The subqueries are more readable than complex join or union statements.
o
Syntax :

SELECT column_list (s) FROM


table_name WHERE column_name
OPERATOR
(SELECT column_list (s) FROM table_name [WHERE])

25
SET Operator in MySQL :
Union

MySQL Union is an operator that allows us to combine two or more results from
multiple SELECT queries into a single result set. It comes with a default
feature that removes the duplicate rows from the result set. MySQL always uses the
name of the column in the first SELECT statement will be the column names of the
result set(output).

MySQL Union must follow these basic rules:

o The number and order of the columns should be the same in all tables that you are
going to use.
o The data type must be compatible with the corresponding positions of each
select query. o The column name selected in the different SELECT queries must be
in the same order.
Syntax :

SELECT column_list FROM table1


UNION
SELECT column_list FROM table2;

INTERSECT

The INTERSECT operator is a kind of SET operation in SQL that includes UNION,
UNION ALL, MINUS, and INTERSECT. The INTERSECT operator returns the distinct
(common) elements in two sets or common records from two or more tables.
In other words, it compares the result obtained by two queries and produces
unique rows, which are the result returned by both queries.

Since MySQL does not provide support for the INTERSECT operator. However, we
can use the INNER JOIN and IN clause to emulate this operator.

MINUS

The MINUS operator is a kind of SET operation in SQL which also includes
INTERSECT, UNION, and UNION ALL. The MINUS operator returns the unique element
from the first table/set, which is not found in the second table/set. In other words, it
will compare the results of two queries and produces the resultant row from the result
set obtained by the first query and not found in the result set obtained by the second
query.

Since MySQL does not provide support for MINUS operator. However, we can use a
LEFT JOIN clause to simulate this operator.

25
Application of Nested and Set operations on Specified Case study with
Input & Output:

NESTED QUERIES:

Q 68: List the employee belonging to the department of “Asmita“

ANS:

select sname from staff


where Specilist_of = (select Specilist_of from staff where sname = “Asmita”);

Q 69 : Display the different designation in department 104 or 107.

Ans :

select Specilist_of from staff


where emp_id = 104
UNION
select Specilist_of from staff where emp_id =107 ;
Intersect:

SELECT sname FROM staff INTERSECT SELECT emp_name FROM emp_new;

You might also like