CARTESIAN PRODUCT
Find all the combinations of customers and salespeoples.
SELECT Customers.*,Salespeople.* FROM Customers,Salespeople
NATURAL JOIN
- takes cartesian product,then apply filter that that common fields of both tables should be equal.
Find all the customers with their salesperson names assigned to them.
SELECT Customers.cname,Customers.snum,Salespeople.snum,Salespeople.sname FROM Customers,Salespeople WHERE Salespeople.snum=Customers.snum;
UN-NATURAL JOIN
- condition is based on fields which are not common in both tables.
Find the combination of salespeople and customers who share a city.
SELECT Customers.cname,Customers.city,Salespeople.sname,Salespeople.city FROM Salespeople,Customers WHERE Salespeople.city=Customers.city;
COMPOUND PREDICATES
- conditions which include join-predicate(condition based on 2 fields of different tables) and simple predicates.
Find the combination of salespeople and customers who live in ahmd and bombay.
SELECT Customers.cname,Customers.city,Salespeople.sname,Salespeople.city FROM Salespeople,Customers WHERE Salespeople.city IN ('ahmd','bombay') AND Salespeople.city=Customers.city;
EQUI-JOINS and NON-EQUIJOIN
- when conditions in JOIN are based on equality then it is called EQUI-JOIN.
- above query was of EQUI-JOIN.
NON-EQUIJOIN
- when conditions in a JOIN are based on SQL operator(>,>=,<,<=,<>) other than equality(=),then such a join is known as NON-EQUIJOIN.
Find all the customer,salespersom combinations such that Customer name precedes the Salesperson name alphabetically and customer has a rating less than 200;
SELECT cname AS Customername,sname AS Salespeoplename FROM Salespeople,Customers
WHERE Customers.snum=Salespeople.snum AND cname < sname AND rating < 200;
JOINING A TABLE TO ITSELF (ALIASES)
Find all customers with same rating.
SELECT first.cname,first.rating,second.cname,second.rating FROM Customers first,Customers second
WHERE first.rating=second.rating AND first.cnum > second.cnum;
find all orders where salesperson who made a sale is not the one assigned.
SELECT a.snum,b.snum,a.cnum,a.onum FROM Orders a,Customers b WHERE a.cnum=b.cnum AND a.snum <> b.snum;
Find all the customers located in cities where salesperson nayan has customers
SELECT DISTINCT b.cnum,b.cname FROM Customers a,Customers b WHERE a.snum=(SELECT snum FROM Salespeople WHERE Salespeople.sname='nayan') AND b.city=a.city;
SUB-QUERIES
- you can nest queries inside one another.
- inner query generates values that are tested in the predicate of the outer query.
Find all the orders taken by Salesperson sanjay.
SELECT * FROM Orders WHERE snum = (SELECT snum FROM Salespeople WHERE sname='sanjay');
USING AGGREGATE FUNCTIONS WITH SUB-QUERIES
Find all the orders whose amounts are greater than the averge for 1-jan-2000.
SELECT * FROM orders WHERE amount > (SELECT avg(amount) FROM Orders WHERE odate ='1-JAN-2000');
USING IN WITH SUB-QUERIES THAT PRODUCE MULTIPLE ROWS
- when sub-queries return multiple rows,the use special operator IN.
Find all the orders taken by salesperson in ahmd.
SELECT * FROM Orders WHERE snum IN (SELECT snum FROM Salespeople WHERE city='ahmd');
Count the customers with rating above the avergage rating of 'ahmd' city customers.
SELECT rating,COUNT(cnum) FROM Customers GROUP BY rating HAVING rating > (SELECT AVG(rating) FROM Customers where city='ahmd');
INSERT INTO Customers VALUES (11,'jayesh','bombay',9,1002);
COR-RELATED SUBQUERIES
- when in a sub-query you are referring to the table in outer query,then it is known as correlated sub-queries.
- The sub-query is executed repeatedly,once for each row of the main query table.
Find all customers with orders on 1-jan-2000
SELECT * FROM Customers outer WHERE '1-JAN-2000' IN (SELECT odate FROM orders inner WHERE outer.cnum=inner.cnum);
PROCEDURE TO EXECUTE THE QUERY :
i) Select a row from the table named in outer query.This is called current Candidate row.
ii) Store the values from this candidate row in the alias named in the FROM clause of the outer query.
iii) Perform the sub-query with the current value.
iv) Evaluate the predicate of the outer query on the basis of the results of sub-query.This will determine whether the candidate row is selected for output.
v) Repeat the procedure for the next candidate row of the outer table.
ALTERNATE METHOD : Find all customers with orders on 1-jan-2000
SELECT * FROM Customers first,Orders second WHERE first.cnum=second.cnum AND second.odate='1-JAN-2000';
SUB-QUERY : find the names and numbers of salesperson who have more than 1 customer.
SELECT snum,sname FROM Salespeople outer WHERE 1 < (SELECT COUNT(*) FROM Customers WHERE snum=outer.snum);
Find all the orders with above-average amounts for their customers.
(E.g. if a customer has 3 orders,then find the avg. amount of 3 orders for that customer,then out of this 3 records for this customer,select that record who amount is greater than the average amount found earlier.)
SELECT * FROM Orders outer WHERE amount > (SELECT AVG(amount) FROM Orders inner WHERE inner.cnum=outer.cnum);