Step-4- in SQl-server2000

Page 1 of 1

[ 1 post ]

Author

Question

msi_fci

Question subject: Step-4- in SQl-server2000

Posted: Sat Apr 07, 2007 7:16 pm

Joined: Thu Apr 05, 2007 9:57 pm
Posts: 17
Has thanked: 0 time
Have thanks: 0 time

DML Statements(Subqueries)

A) Using Subquery in WHERE Clause

Code:

SELECT <column list>
FROM <table names>
WHERE expression operator 
               ( SELECT <column list>
                                             FROM <table names>)

Example 1:

Get the names of the customers who have ordered at least one order

Code:

SELECT  Customers.ContactName
FROM Customers
WHERE Customers.CustomerID IN 
   (
    SELECT Orders.CustomerID
    FROM Orders
   );

Example 2:

Get the supplier names of the suppliers who supply category number 3

Code:

SELECT Suppliers.ContactName
FROM Suppliers
WHERE  Suppliers.SupplierID IN
  (
   SELECT Products.SupplierID
   FROM Products
   WHERE Products.CategoryID=3
  );

Example 3:

Get the books titles priced higher than the lowest priced book that has a type 'trad_cook'

Code:

SELECT Title
FROM Titles
WHERE Price >
 (
   SELECT MIN(Price)
   FROM Titles
   WHERE Type = 'trad_cook'
);

Code:

SELECT <column list>
FROM <table names>,        ( SELECT <column list>
                                        FROM <table names>)

[WHERE <condition>]

Example:

Get the names of all customers and employees living in USA , but with customers not working as Sales Agents

Code:

SELECT C. ContactName "Cust Name", E.FirstName + ' ' + E.LastName "Emp Name" 
FROM Customers C , 
           (
                            SELECT FirstName,LastName
                            FROM Employees
                                   WHERE Country = 'USA'
                                 ) E
WHERE C.Country = 'USA'
AND C.ContactTitle <> 'Sales Agent';

Code:

SELECT <column list> , ( SELECT <column list>
                                     FROM <table names>)

FROM <table names>      
[WHERE <condition>]

Example:

Get the name , job (title) and number of orderes requested by each customer

Code:

SELECT ContactName ,ContactTitle , (SELECT COUNT(*) 
                                                               FROM Orders 
                                                               WHERE Customers.CustomerId = Orders.CustomerId  
                            ) AS "Number of Orders"      
FROM Customers;

Note:

When using subquery in the where clause , take care when using operators ;
If the subquery returns more than one value you canØ·Â·Ø¢Â£Ø·Â¢Ø¢Â¢Ø·Â£Ø¢Â¢Ø£Â¢Ã¢â€šÂ¬Ø¹â€˜Ø·Â¢Ø¢Â¬Ø·Â£Ø¢Â¢Ø£Â¢Ã¢â€šÂ¬Ã¢â‚¬Ú†Ø·Â¢Ø¢Â¢t use operator such as = , but you may use the IN operator.

Page 1 of 1

[ 1 post ]

Similar topics
Topic title	Forum	Author	Comments
unit step and impulse function L.T	General Discussion	loomy	0
(MS Press) Microsoft Visual CSharp 2008 - Step By Step	offers	Casper	0
Cisco - Press - Configuring CallManager and Unity A Step-by-	offers	Casper	0
TCP IP First Step	offers	Casper	0
Cisco Press - Routing First-Step 2004	offers	Casper	0

Codemiles.com » Database Forums » SQL Server

All times are UTC [ DST ]

Users browsing similar posts

Users browsing this forum: No registered users and 1 guest

Jump to:

Previous Question | Next Question