nested,inner queries, subqueries

• Nested,inner,subquery, Sql Queries

select *
from Customers
where CustomerID in
    (select customerID
    from Orders
    where OrderID in
        (select orderID
        from [Order Details]  
        where ProductID = 1
        )
    )
   
   
   

• How subqueries work

Subqueries, also called inner queries, appear within a where or having clause of another SQL statement or in the select list of a statement. You can use subqueries to handle query requests that are expressed as the results of other queries. A statement that includes a subquery operates on rows from one table, based on its evaluation of the subquery's select list, which can refer either to the same table as the outer query, or to a different table.


For example, this subquery lists the names of all authors whose royalty split is more than $75:

select au_fname, au_lname
from authors
where au_id in
   (select au_id
    from titleauthor
    where royaltyper > 75)

select statements that contain one or more subqueries are sometimes called nested queries or nested select statements.

Multiple levels of nesting
"Find the names of authors who have participated in writing at least one popular computing book:"

select au_lname, au_fname
from authors
where au_id in
   (select au_id
    from titleauthor
    where title_id in
       (select title_id
        from titles
        where type = "popular_comp") )
       
       
Subqueries in update, delete, and insert statements

The following query doubles the price of all books published by New Age Books.
update titles
set price = price * 2
where pub_id in
   (select pub_id
    from publishers
    where pub_name = "New Age Books")
   

You can remove all records of sales of business books with this nested select statement
delete salesdetail
where title_id in
   (select title_id
    from titles
    where type = "business")
   
An equivalent delete statement using a join is:
delete salesdetail
from salesdetail, titles
where salesdetail.title_id = titles.title_id
and type = "business"

http://manuals.sybase.com/onlinebooks/group-as/asg1250e/sqlug/@Generic__BookTextView/13332;pt=13262



the name of every song by Metallica that contains the lyric “justice” with the following subquery
SELECT song_name FROM Album
WHERE band_name = ‘Metallica’
AND song_name IN
(SELECT song_name FROM Lyric
WHERE song_lyric LIKE ‘%justice%’);

all Metallica songs in the “And Justice for All” album that do not contain the word “justice” by way of the following code:
SELECT song_name FROM Album
WHERE album_name = ‘And Justice for All’
AND band_name = ‘Metallica’
AND song_name NOT IN
(SELECT song_name FROM Lyric
WHERE song_lyric LIKE ‘%justice%’);


a list of Metallica songs performed by Damage, Inc. from my Cover table
SELECT Album.song_name FROM Album
WHERE Album.band_name = ‘Metallica’
AND EXISTS
(SELECT Cover.song_name FROM Cover
WHERE Cover.band_name = ‘Damage, Inc.’
AND Cover.song_name = Album.song_name);


For example, I want to verify Album table entries for every Metallica song. Also, I want to return the album names that have missing tracks. Conveniently, the AlbumInfo table contains a column (album_tracks) signaling how many tracks there should be.
SELECT AlbumInfo.album_name FROM AlbumInfo
WHERE AlbumInfo.band_name = ‘Metallica’
AND album_tracks <>
(SELECT COUNT(*) FROM Album
WHERE Album.album_name = AlbumInfo.album_name);


The next example will return every Metallica album, the number of tracks it should contain, and how many entries are included in the Album table:
SELECT AlbumInfo.album_name, album_tracks,
(SELECT COUNT(*) FROM Album
WHERE Album.album_name = AlbumInfo.album_name)
FROM  AlbumInfo
WHERE AlbumInfo.band_name = ‘Metallica’;


changing the album_tracks value in the AlbumInfo table to the actual number of entries in the Album table:
UPDATE AlbumInfo SET album_tracks =
SELECT COUNT(*) FROM Album
WHERE AlbumInfo.album_name = Album.album_name)
WHERE AlbumInfo.band_name = ‘Metallica’;


Subselect comparison keywords (ALL, SOME, ANY)

SELECT * FROM AlbumSales
WHERE album_gross >
ALL (SELECT album_costs FROM AlbumProduction);

http://www.techrepublic.com/article/use-sql-subselects-to-consolidate-queries/1045787