Schematic SQL subquery Instance

1 Creating a sample Table

Create a sample table first;

---------------------------Create Customers table-------------------------CREATE TABLE Customers (cust_id char (10)   Not NULL, Cust_name char (a) not NULL, Cust_address char ($) NULL, cust_city char () NULL, cust_state    CHAR (5) NULL, Cust_zip char (TEN) NULL, Cust_country char ($) NULL, Cust_contact char (a) NULL, Cust_email char (255) NULL);----------------------------create OrderItems table--------------------------CREATE table OrderItems (order_num int NOT NULL, Order_item int. NOT NULL, prod_id char (TEN) not NULL, q uantity int NOT NULL, item_price decimal (8,2) is not null);------------------------Create Orders table-------- --------------CREATE TABLE Orders (order_num int not NULL, order_date datetime NOT NULL, cust_id char (TEN) NO T NULL);--------------------------Create Products Table------------------------CREATE TABLE products (prod_id char (1 0) Not NULL, vend_id Char(ten) not NULL, Prod_name char (255) is not NULL, Prod_price decimal (8,2) is not NULL, Prod_desc varchar (+) N ULL);-------------------------Create vendors Table-----------------------CREATE TABLE vendors (vend_id char) NO T NULL, Vend_name char (a) not NULL, vend_address char () NULL, vend_city char () NULL, Vend_state char ( 5) NULL, Vend_zip char (TEN) NULL, Vend_country char () null),------------------------Define primary keys------- ---------------ALTER TABLE Customers with NOCHECK ADD CONSTRAINT pk_customers PRIMARY KEY CLUSTERED (cust_id); ALTER TABLE orderitems with NOCHECK ADD CONSTRAINT pk_orderitems PRIMARY KEY CLUSTERED (Order_num, Order_item); ALTER TABLE Orders with NOCHECK ADD CONSTRAINT pk_orders PRIMARY KEY CLUSTERED (order_num); ALTER TABLE products with NOCHECK ADD CONSTRAINT pk_products PRIMARY KEY CLUSTERED (prod_id); ALTER TABLE vendors with NOCHECK ADD CONSTRAINT pk_vendors PRIMARY KEY CLUSTERED (vend_id);------------------------Define foreign keys----------------------ALTER TABLE orderitems addconstraint fk_orderitems_orders foreign KEY (Order_num) REFERENCES Orders (order_num), CONSTRAINT fk_orderitems_products FOREIGN KEY (prod_id) REFERENCES products (prod_id); ALTER TABLE Orders ADD CONSTRAINT fk_orders_customers FOREIGN KEY (cust_id) REFERENCES Customers (cust_id); ALTER TABLE Products AddConstraint fk_products_vendors FOREIGN KEY (vend_id) REFERENCES vendors (vend_id);

-----------------------------Populate Customers table---------------------------INSERT into Customers (cust_id, Cust _name, Cust_address, cust_city, Cust_state, Cust_zip, Cust_country, Cust_contact, Cust_email) VALUES (' 1000000001 ', ' Village Toys ', ' Maple Lane ', ' Detroit ', ' MI ', ' 44444 ', ' USA ', ' John Smith ', ' [email protected] '; INSERT into Cust Omers (cust_id, Cust_name, cust_address, cust_city, Cust_state, Cust_zip, Cust_country, cust_contact) VALUES (' 1000000002 ', ' Kids place ', ' 333 South Lake Drive ', ' Columbus ', ' OH ', ' 43333 ', ' USA ', ' Michelle Green '; INSERT into custome RS (cust_id, Cust_name, cust_address, cust_city, Cust_state, Cust_zip, Cust_country, Cust_contact, Cust_email) VALUES (' 1000000003 ', ' Fun4all ', ' 1 Sunny place ', ' Muncie ', ' in ', ' 42222 ', ' USA ', ' Jim Jones ', ' [email protected] '); INSERT INT O Customers (cust_id, Cust_name, cust_address, cust_city, Cust_state, Cust_zip, Cust_country, Cust_contact, Cust_email) VALUES (' 1000000004 ', ' fun4all ', ' 829 Riverside Drive ', ' PhoeNix ', ' AZ ', ' 88888 ', ' USA ', ' Denise L. Stephens ', ' [email protected] '); INSERT into Customers (cust_id, Cust_name, cus T_address, Cust_city, Cust_state, Cust_zip, Cust_country, cust_contact) VALUES (' 1000000005 ', ' The Toy Store ', ' 4545 53rd S Treet ', ' Chicago ', ' IL ', ' 54545 ', ' USA ', ' Kim Howard ');---------------------------Populate Vendors Table-------------------------INSERT into vendors (vend_id, Vend_name, vend_address, vend_city, Vend_state, Vend_zip, Vend_country) VALUES (' BRS01 ', ' Bears R Us ', ' 123 Main Street ', ' Bear Town ', ' MI ', ' 44444 ', ' USA '); INSERT into vendors (vend_ ID, Vend_name, vend_address, vend_city, Vend_state, Vend_zip, Vend_country) VALUES (' BRE02 ', ' Bear Emporium ', ' Park Street ', ' anytown ', ' OH ', ' 44333 ', ' USA '); INSERT into Vendors (vend_id, Vend_name, vend_address, vend_city, Vend_state, Vend_zip, Vend_country) VALUES (' DLL01 ', ' Doll House Inc. ', ' 555 high Street ', ' Dollsville ', ' CA ', ' 99999 ', ' USA '); INSERT into vendors (vend_id, Vend_name, vend_address, vend_city, Vend_state, Vend_ziP, Vend_country) VALUES (' FRB01 ', ' furball Inc ', ' 5th Avenue ', ' New York ', ' NY ', ' 11111 ', ' USA '); INSERT into Vendors ( vend_id, Vend_name, vend_address, vend_city, Vend_state, Vend_zip, Vend_country) VALUES (' FNG01 ', ' fun and games ', ' 42 Galaxy Road ', ' London ', NULL, ' N16 6PS ', ' England '); INSERT into Vendors (vend_id, Vend_name, vend_address, vend_city, Vend_ State, Vend_zip, Vend_country) VALUES (' JTS01 ', ' jouets et ours ', ' 1 Rue amusement ', ' Paris ', NULL, ' 45678 ', ' France ');----- -----------------------Populate Products Table--------------------------INSERT to Products (prod_id, vend_id, Prod_ Name, Prod_price, Prod_desc) VALUES (' BR01 ', ' BRS01 ', ' 8 inch teddy bear ', 5.99, ' 8 inch teddy bear, comes with cap and Jack ET '); INSERT into Products (prod_id, vend_id, Prod_name, Prod_price, Prod_desc) VALUES (' BR02 ', ' BRS01 ', ' inch Teddy ' , 8.99, ' inch teddy bear, comes with cap and jacket '); INSERT to Products (prod_id, vend_id, Prod_name, Prod_price, pro D_DESC) VALUES (' BR03 ', ' BRS01 ', ' inch Teddy BeaR ', 11.99, ' inch teddy bear, comes with cap and jacket '); INSERT to Products (prod_id, vend_id, Prod_name, Prod_price,  PROD_DESC) VALUES (' BNBG01 ', ' DLL01 ', ' Fish bean bag toy ', 3.49, ' Fish bean bag toy, complete with bean bag worms with which  To feed it '): INSERT into Products (prod_id, vend_id, Prod_name, Prod_price, Prod_desc) VALUES (' BNBG02 ', ' DLL01 ', ' Bird bean  Bag toy ', 3.49, ' Bird bean bag toy, eggs is not included '); INSERT to Products (prod_id, vend_id, Prod_name, Prod_price, PROD_DESC) VALUES (' BNBG03 ', ' DLL01 ', ' Rabbit bean bag toy ', 3.49, ' Rabbit bean bag toy, comes with bean bag carrots '); inse RT into Products (prod_id, vend_id, Prod_name, Prod_price, Prod_desc) VALUES (' RGAN01 ', ' DLL01 ', ' raggedy Ann ', 4.99, ' Inc H raggedy Ann doll '); INSERT into Products (prod_id, vend_id, Prod_name, Prod_price, Prod_desc) VALUES (' RYL01 ', ' FNG01 ', ' Ki Ng Doll ', 9.49, ' inch King doll with Royal garments and Crown '); INSERT to Products (prod_id, vend_id, Prod_name, Prod_ Price, Prod_desc) VALUES ('RYL02 ', ' FNG01 ', ' Queen doll ', 9.49, ' inch Queen doll with royal garments and Crown ');--------------------------Popula Te Orders table------------------------INSERT into Orders (Order_num, Order_date, cust_id) VALUES (20005, ' 2012-05-01 ', ' 1000000001 '); insert into Orders (Order_num, Order_date, cust_id) VALUES (20006, ' 2012-01-12 ', ' 1000000003 '); Orders (Order_num, Order_date, cust_id) VALUES (20007, ' 2012-01-30 ', ' 1000000004 '); INSERT into Orders (Order_num, Order_ Date, cust_id) VALUES (20008, ' 2012-02-03 ', ' 1000000005 '); INSERT into Orders (Order_num, order_date, cust_id) VALUES ( 20009, ' 2012-02-08 ', ' 1000000001 ');------------------------------Populate OrderItems Table----------------------------INSERT into OrderItems (Order_num, Order_item, prod_id, Quantity, Item_price) VALUES (  20005, 1, ' BR01 ', +, 5.49); INSERT into OrderItems (Order_num, Order_item, prod_id, Quantity, Item_price) VALUES (20005, 2, ' BR03 ', 10.99, INSERT into OrderItems (Order_num, Order_item, prod_id, quantity, item_Price) VALUES (20006, 1, ' BR01 ', $, 5.99); INSERT into OrderItems (Order_num, Order_item, prod_id, quantity, Item_price) VALUES (20006, 2, ' BR02 ', ten, 8.99); INSERT into OrderItems (Order_num, Order_item, prod_id, quantity, Item_price) VALUES (  20006, 3, ' BR03 ', ten, 11.99); INSERT into OrderItems (Order_num, Order_item, prod_id, Quantity, Item_price) VALUES (20007, 1, ' BR03 ', ' 11.49 ', INSERT into OrderItems (Order_num, Order_item, prod_id, Quantity, Item_price) VALUES (20007, 2, ' BNBG01 ') , 2.99); INSERT into OrderItems (Order_num, Order_item, prod_id, Quantity, Item_price) VALUES (20007, 3, ' BNBG02 ', 100, 2 INSERT into OrderItems (Order_num, Order_item, prod_id, Quantity, Item_price) VALUES (20007, 4, ' BNBG03 ', +, 2.99); I Nsert into OrderItems (Order_num, Order_item, prod_id, Quantity, Item_price) VALUES (20007, 5, ' RGAN01 ', +, 4.49); INSERT in To OrderItems (Order_num, Order_item, prod_id, Quantity, Item_price) VALUES (20008, 1, ' RGAN01 ', 5, 4.99); INSERT into Orderi TEMs (Order_num, Order_item, prod_id, QuAntity, Item_price) VALUES (20008, 2, ' BR03 ', 5, 11.99); INSERT into OrderItems (Order_num, Order_item, prod_id, quantity, it Em_price) VALUES (20008, 3, ' BNBG01 ', ten, 3.49); INSERT into OrderItems (Order_num, Order_item, prod_id, quantity, item_ Price) VALUES (20008, 4, ' BNBG02 ', ten, 3.49); INSERT into OrderItems (Order_num, Order_item, prod_id, quantity, Item_price) VALUES (20008, 5, ' BNBG03 ', ten, 3.49); INSERT into OrderItems (Order_num, Order_item, prod_id, quantity, Item_price) VALUES ( 20009, 1, ' BNBG01 ', +, 2.49); INSERT into OrderItems (Order_num, Order_item, prod_id, Quantity, Item_price) VALUES (20009, 2, ' BNBG02 ', +, 2.49); INSERT into OrderItems (Order_num, Order_item, prod_id, Quantity, Item_price) VALUES (20009, 3, ' BNB G03 ', 250, 2.49);

The Create and insert data scripts for the sample tables can be downloaded from the bottom of this page;

http://www.forta.com/books/0672336073/

The following is the case of the Customers and Orders tables;

2 independent single-valued subquery (Independent scalar subquery)

A subquery returns a single value, not a dataset

SELECT * FROM   customers as C  where c.cust_id= (  select o.cust_id from Orders as O where o.order_num = 20008);

Note: When using single-valued standalone subqueries, make sure that the subquery returns a single value, not a dataset;
If the data set is returned, the following conditions will appear:

3 independent Multi-valued subqueries

The subquery returns a data set

SELECT * FROM  customers as C where c.cust_id in (  

4 related subqueries

The most basic execution logic for a correlated subquery is to put "each row" in the outer table into a subquery (to understand the key of the correlated subquery)

SELECT * from   Customers as C  where exists (     select * from  Orders as O     

5 using subqueries as calculated fields

Statistic the customer's name, the state in which the customer is located, the number of orders of the customer;

Select Cust_name,cust_state, (select COUNT (*) from Orders where orders.cust_id=customers.cust_id) as Ordersfrom Customersorder by Cust_name;

The third column is a subquery that counts the number of orders per customer;

The sub-query of the third column is performed as follows;

Select COUNT (*) from Orders where cust_id= ' 1000000001 ';

Schematic SQL subquery Instance