Joined: Thu Apr 05, 2007 9:57 pm Posts: 17 Has thanked: 0 time Have thanks: 0 time
DML Statements(Advanced)
Code:
SELECT <column list> FROM <table names> [WHERE <condition>] [GROUP BY <column list>] [HAVING <condition>] [ORDER BY <column list>]
1. Using Group by and Order by
Example 1: (without aggregate function equivalent to using distinct)
Give all different unit prices from the product table ordered Ascendingly
Code:
SELECT unitprice FROM products GROUP BY unitprice ORDER BY unitprice Asc;
Example 2:
For each unit price, find the number of products having that unit price ordered Descendingly by unit price with a not null unit price
Code:
SELECT unitprice, count(*) FROM products WHERE unitprice <> NULL GROUP BY unitprice ORDER BY unitprice Desc;
Count is a Group Function , search for others and see example(s) on them? ط·آ£ط¢آ¢ط£آ¢أ¢â‚¬ع‘ط¢آ¬ط·آ¥أ¢â‚¬إ“Grouping columnsط·آ£ط¢آ¢ط£آ¢أ¢â‚¬ع‘ط¢آ¬ط£آ¢أ¢â€ڑآ¬ط¥â€™
2.Using Having Clause
Example 1:
What are the cities where more than 1 employee live?
Code:
SELECT city FROM employees GROUP BY city HAVING count (employeeid) >1;
Example 2:
Get the id of the customer that ordered more than one order
Code:
SELECT customerid , count (*) [as Orders] FROM orders GROUP BY customerid HAVING count (*) >1;
Select Customers.companyName as Name, count(Orders.Customerid) as Orders From Orders , Customers where Orders.Customerid = Customers.Customerid group by (Customers.companyName);
Example 1:
Get the names, prices and supplier names for each product
Code:
SELECT Products.ProductName,Suppliers.ContactName,Products.UnitPrice FROM Products, Suppliers WHERE Products.SupplierID = Suppliers.SupplierID
Example 2:
State the supplier names for each category
Code:
SELECT Categories.CategoryName, Suppliers.CompanyName FROM Categories,Products, Suppliers WHERE Suppliers.SupplierID = Products.SupplierID AND Products.CategoryID = Categories.CategoryID;
Users browsing this forum: No registered users and 2 guests
You cannot post new topics in this forum You cannot reply to topics in this forum You cannot edit your posts in this forum You cannot delete your posts in this forum You cannot post attachments in this forum
