[go: up one dir, main page]
Scribd
Upload
40 views4 pages

Department of Information Technology: S.Y. Btech (It) Sub: Dbms Lab Experiment No: 5 To Study and Implement Subqueries

Uploaded by

Chahat

This document discusses subqueries in SQL. It defines a subquery as a SELECT statement nested inside another statement. It describes where subqueries can be used (SELECT, INSERT, UPDATE, DELETE statements) and common subquery types including IN, EXISTS, and comparison operators. Examples are provided to demonstrate subqueries used with IN, comparison operators, and EXISTS. The document concludes with an exercise asking the reader to write 10 SQL statements using subqueries.

Copyright:

© All Rights Reserved
Download as PDF, TXT or read online from Scribd
Download as pdf or txt

Department of Information Technology: S.Y. Btech (It) Sub: Dbms Lab Experiment No: 5 To Study and Implement Subqueries

Uploaded by

Chahat
40 views4 pages
This document discusses subqueries in SQL. It defines a subquery as a SELECT statement nested inside another statement. It describes where subqueries can be used (SELECT, INSERT, UPDATE, DELETE statements) and common subquery types including IN, EXISTS, and comparison operators. Examples are provided to demonstrate subqueries used with IN, comparison operators, and EXISTS. The document concludes with an exercise asking the reader to write 10 SQL statements using subqueries.

Original Title

Exp 5

Copyright

© © All Rights Reserved

Available Formats

PDF, TXT or read online from Scribd

Did you find this document useful?

Is this content inappropriate?

This document discusses subqueries in SQL. It defines a subquery as a SELECT statement nested inside another statement. It describes where subqueries can be used (SELECT, INSERT, UPDATE, DELETE statements) and common subquery types including IN, EXISTS, and comparison operators. Examples are provided to demonstrate subqueries used with IN, comparison operators, and EXISTS. The document concludes with an exercise asking the reader to write 10 SQL statements using subqueries.

Copyright:

© All Rights Reserved
Download as PDF, TXT or read online from Scribd
Download as pdf or txt
40 views4 pages

Department of Information Technology: S.Y. Btech (It) Sub: Dbms Lab Experiment No: 5 To Study and Implement Subqueries

Uploaded by

Chahat
This document discusses subqueries in SQL. It defines a subquery as a SELECT statement nested inside another statement. It describes where subqueries can be used (SELECT, INSERT, UPDATE, DELETE statements) and common subquery types including IN, EXISTS, and comparison operators. Examples are provided to demonstrate subqueries used with IN, comparison operators, and EXISTS. The document concludes with an exercise asking the reader to write 10 SQL statements using subqueries.

Copyright:

© All Rights Reserved
Download as PDF, TXT or read online from Scribd
Download as pdf or txt
You are on page 1/ 4

Department of Information Technology

S.Y. BTech (IT)


SUB: DBMS LAB
Experiment No: 5

To study and implement Subqueries

Defining Subqueries inside SELECT Statements


A subquery is a SELECT statement that returns a single value and is nested inside a
SELECT, INSERT, UPDATE, or DELETE statement or inside another subquery. A
subquery can be used anywhere an expression is allowed. A subquery is also called an
inner query or inner select, while the statement containing a subquery is called an outer
query or an outer select.
In the following example, a subquery is nested in the WHERE clause of the outer
SELECT statement:

Product Table
ProductID, ProductName, SupplierID, CategoryID, QuantityPerUnit, UnitPrice,
UnitsInStock

SELECT ProductName
FROM Products
WHERE UnitPrice =
(
SELECT UnitPrice
FROM Products
WHERE ProductName = ’Sir Rodney’’s Scones’
)

The embedded SELECT statement first identifies the UnitPrice value for Sir Rodney’s
Scones, which is $10. The $10 value is then used in the outer SELECT statement to return
the product name of all products whose unit price equals $10.

If a table appears only in a subquery and not in the outer query, then columns from that
table cannot be included in the output (the select list of the outer query).

Types of Subqueries

Subqueries can be specified in many places within a SELECT statement. Statements that
include a subquery usually take one of the following formats, however:
 WHERE <expression> [NOT] IN (<subquery>)
 WHERE <expression> <comparison_operator> [ANY | ALL] (<subquery>)
 WHERE [NOT] EXISTS (<subquery>)
1
Department of Information Technology

Subqueries that Are Used with IN or NOT IN

The result of a subquery introduced with IN (or with NOT IN) is a list of zero or more
values. After the subquery returns the result, the outer query makes use of it. In the
following example, a subquery is nested inside the WHERE clause and the IN keyword is
used:

SELECT Pub_name
FROM Publishers
WHERE Pub_id IN
(
SELECT Pub_id
FROM Titles
WHERE Type = ’business’
)
You can evaluate this statement in two steps. First, the inner query returns the
identification numbers of the publishers that have published business books. Second,
these values are substituted into the outer query, which finds the names that match the
identification numbers in the Publishers table.
Subqueries introduced with the NOT IN keywords also return a list of zero or more
values. The query is exactly the same as the one in subqueries with IN, except that NOT
IN is substituted for IN.

Subqueries that Are Used with Comparison Operators


Comparison operators that introduce a subquery can be modified with the keyword ALL
or ANY. The SOME keyword is a SQL-92 standard equivalent for ANY. Subqueries
introduced with a modified comparison operator return a list of zero or more values and
can include a GROUP BY or HAVING clause. These subqueries can be restated with
EXISTS.
The ALL and ANY keywords each compare a scalar value with a single-column set of
values. The ALL keyword applies to every value, and the ANY keyword applies to at
least one value. In the following example, the greater than (>) comparison operator is
used with the ANY keyword:

SELECT Title
FROM Titles
WHERE Advance > ANY
(
SELECT Advance
FROM Publishers INNER JOIN Titles
ON Titles.Pub_id = Publishers.Pub_id
2
Department of Information Technology

AND Pub_name = ’Algodata Infosystems’


)
This statement finds the titles that received an advance larger than the minimum advance
amount paid by Algodata Infosystems (which, in this case, is $5,000). The WHERE
clause in the outer SELECT statement contains a subquery that uses a join to retrieve
advance amounts for Algodata Infosystems. The minimum advance amount is then used
to determine which titles to retrieve from the Titles table.

Subqueries that Are Used with EXISTS and NOT EXISTS


When a subquery is introduced with the keyword EXISTS, it functions as an existence
test. The WHERE clause of the outer query tests for the existence of rows returned by the
subquery. The subquery does not actually produce any data; instead, it returns a value of
TRUE or FALSE.
In the following example, the WHERE clause in the outer SE LECT statement contains
the subquery and uses the EXISTS keyword:

SELECT Pub_name
FROM Publishers
WHERE EXISTS
(
SELECT * FROM Titles
WHERE Titles.Pub_id = Publishers.Pub_id
AND Type = ’business’
)

To determine the result of this query, consider each publisher’s name in turn. In this case,
the first publisher’s name is Algodata Infosystems, which has identification number
1389. Are there any rows in the Titles table in which Pub_id is 1389 and the type is
business? If so, Algodata Infosystems should be one of the values selected. The same
process is repeated for each of the other publishers’ names.

The NOT EXISTS keywords work like EXISTS, except the WHERE clause in which
NOT EXISTS is used is satisfied if the subquery returns no rows.

3
Department of Information Technology

------------------------------------------------EXERCISE------------------------------------------------------
Implement the SQL statements for the following questions

1. Find the lname, fname who have been issued movies.

2. Find out the movie number which has been issued to Allan.

3. Display all the movie title whose price is same as the price of movie ‘The Firm’

4. Find the customer name and area with invoice number ‘I10’.

5. Find the names and movie numbers of all the customers who have been issued a
movie.

6. Find out which customers have been issued movie number 9.

7. Find the name of the movie issued to Tina and Allan.

8. List the movie number, movie name issued to all the customers.

9. Find the customer names and phone numbers who have been issued movies
before 01-aug-95.

10. Find the type and movie number of movie issued to cust_id ‘A01’ and ‘A02’.

You might also like