SQL - Weebly



SQL Introduction – 8DML (Data Manipulation Language)SQL?FunctionsSQL has many built-in functions for performing calculations on data.SQL Aggregate FunctionsSQL aggregate functions return a single value, calculated from values in a column.Useful aggregate functions:AVG() - Returns the average valueCOUNT() - Returns the number of rowsFIRST() - Returns the first valueLAST() - Returns the last valueMAX() - Returns the largest valueMIN() - Returns the smallest valueSUM() - Returns the sumThe AVG() FunctionThe AVG() function returns the average value of a numeric column.SQL AVG() SyntaxSELECT AVG(column_name) FROM table_nameDemo DatabaseIn this tutorial we will use the well-known Northwind sample database.Below is a selection from the "Products" table:ProductIDProductNameSupplierIDCategoryIDUnitPrice1Chais1110 boxes x 20 bags182Chang1124 - 12 oz bottles193Aniseed Syrup1212 - 550 ml bottles104Chef Anton's Cajun Seasoning2248 - 6 oz jars21.355Chef Anton's Gumbo Mix2236 boxes25The following SQL statement gets the average value of the "Price" column from the "Products" table:ExampleSELECT AVG(Price) AS PriceAverage FROM Products;The following SQL statement selects the "ProductName" and "Price" records that have an above average price:ExampleSELECT ProductName, Price FROM ProductsWHERE Price>(SELECT AVG(Price) FROM Products);The COUNT() FunctionThe COUNT() function returns the number of rows that matches a specified criteria.SQL COUNT(column_name) SyntaxThe COUNT(column_name) function returns the number of values (NULL values will not be counted) of the specified column:SELECT COUNT(column_name) FROM table_name;SQL COUNT(*) SyntaxThe COUNT(*) function returns the number of records in a table:SELECT COUNT(*) FROM table_name;SQL COUNT(DISTINCT column_name) SyntaxThe COUNT(DISTINCT column_name) function returns the number of distinct values of the specified column:SELECT COUNT(DISTINCT column_name) FROM table_name;Note:?COUNT(DISTINCT) works with ORACLE and Microsoft SQL Server, but not with Microsoft Access.Demo DatabaseIn this tutorial we will use the well-known Northwind sample database.Below is a selection from the "Orders" table:OrderIDCustomerIDEmployeeIDOrderDateShipperID10265721996-07-251102668731996-07-263102672541996-07-291The following SQL statement counts the number of orders from "CustomerID"=7 from the "Orders" table:ExampleSELECT COUNT(CustomerID) AS OrdersFromCustomerID7 FROM OrdersWHERE CustomerID=7;The following SQL statement counts the total number of orders in the "Orders" table:ExampleSELECT COUNT(*) AS NumberOfOrders FROM Orders;The following SQL statement counts the number of unique customers in the "Orders" table:ExampleSELECT COUNT(DISTINCT CustomerID) AS NumberOfCustomers FROM Orders;The FIRST() FunctionThe FIRST() function returns the first value of the selected column.SQL FIRST() SyntaxSELECT FIRST(column_name) FROM table_name;Note:?The FIRST() function is only supported in MS Access.MySQL SyntaxSELECT?column_name?FROM?table_nameORDER BY?column_name?ASCLIMIT 1;ExampleSELECT CustomerName FROM CustomersORDER BY CustomerID ASCLIMIT 1;The LAST() FunctionThe LAST() function returns the last value of the selected column.SQL LAST() SyntaxSELECT LAST(column_name) FROM table_name;Note:?The LAST() function is only supported in MS Access.MySQL SyntaxSELECT?column_name?FROM?table_nameORDER BY?column_name?DESCLIMIT 1;ExampleSELECT CustomerName FROM CustomersORDER BY CustomerID DESCLIMIT 1;The MAX() FunctionThe MAX() function returns the largest value of the selected column.SQL MAX() SyntaxSELECT MAX(column_name) FROM table_name;The following SQL statement gets the largest value of the "Price" column from the "Products" table:ExampleSELECT MAX(Price) AS HighestPrice FROM Products;The MIN() FunctionThe MIN() function returns the smallest value of the selected column.SQL MIN() SyntaxSELECT MIN(column_name) FROM table_name;The following SQL statement gets the smallest value of the "Price" column from the "Products" table:ExampleSELECT MIN(Price) AS SmallestOrderPrice FROM Products;The SUM() FunctionThe SUM() function returns the total sum of a numeric column.SQL SUM() SyntaxSELECT SUM(column_name) FROM table_name;Demo DatabaseIn this tutorial we will use the well-known Northwind sample database.Below is a selection from the "OrderDetails" table:OrderDetailIDOrderIDProductIDQuantity110248111221024842103102487254102491495102495140The following SQL statement finds the sum of all the "Quantity" fields for the "OrderDetails" table:ExampleSELECT SUM(Quantity) AS TotalItemsOrdered FROM OrderDetails;The GROUP BY StatementThe GROUP BY statement is used in conjunction with the aggregate functions to group the result-set by one or more columns.SQL GROUP BY SyntaxSELECT column_name, aggregate_function(column_name)FROM table_nameWHERE column_name operator valueGROUP BY column_name;Demo DatabaseIn this tutorial we will use the well-known Northwind sample database.Below is a selection from the "Orders" table:OrderIDCustomerIDEmployeeIDOrderDateShipperID102489051996-07-043102498161996-07-051102503441996-07-082And a selection from the "Shippers" table:ShipperIDShipperNamePhone1Speedy Express(503) 555-98312United Package(503) 555-31993Federal Shipping(503) 555-9931And a selection from the "Employees" table:EmployeeIDLastNameFirstNameBirthDatePhotoNotes1DavolioNancy1968-12-08EmpID1.picEducation includes a BA....2FullerAndrew1952-02-19EmpID2.picAndrew received his BTS....3LeverlingJanet1963-08-30EmpID3.picJanet has a BS degree....SQL GROUP BY ExampleNow we want to find the number of orders sent by each shipper.The following SQL statement counts as orders grouped by shippers:ExampleSELECT Shippers.ShipperName,COUNT(Orders.OrderID) AS NumberOfOrders FROM OrdersLEFT JOIN ShippersON Orders.ShipperID=Shippers.ShipperIDGROUP BY ShipperName;GROUP BY More Than One ColumnWe can also use the GROUP BY statement on more than one column, like this:ExampleSELECT Shippers.ShipperName, Employees.LastName,COUNT(Orders.OrderID) AS NumberOfOrdersFROM ((OrdersINNER JOIN ShippersON Orders.ShipperID=Shippers.ShipperID)INNER JOIN EmployeesON Orders.EmployeeID=Employees.EmployeeID)GROUP BY ShipperName,LastName;The HAVING ClauseThe HAVING clause was added to SQL because the WHERE keyword could not be used with aggregate functions.SQL HAVING SyntaxSELECT column_name, aggregate_function(column_name)FROM table_nameWHERE column_name operator valueGROUP BY column_nameHAVING aggregate_function(column_name) operator value;Demo DatabaseIn this tutorial we will use the well-known Northwind sample database.Below is a selection from the "Orders" table:OrderIDCustomerIDEmployeeIDOrderDateShipperID102489051996-07-043102498161996-07-051102503441996-07-082And a selection from the "Employees" table:EmployeeIDLastNameFirstNameBirthDatePhotoNotes1DavolioNancy1968-12-08EmpID1.picEducation includes a BA....2FullerAndrew1952-02-19EmpID2.picAndrew received his BTS....3LeverlingJanet1963-08-30EmpID3.picJanet has a BS degree....SQL HAVING ExampleNow we want to find if any of the customers have a total order of less than 2000.We use the following SQL statement:The following SQL statement finds if any of the employees has registered more than 10 orders:ExampleSELECT Employees.LastName, COUNT(Orders.OrderID) AS NumberOfOrders FROM (OrdersINNER JOIN EmployeesON Orders.EmployeeID=Employees.EmployeeID)GROUP BY LastNameHAVING COUNT(Orders.OrderID) > 10;ow we want to find the if the employees "Davolio" or "Fuller" have more than 25 ordersWe add an ordinary WHERE clause to the SQL statement:ExampleSELECT Employees.LastName, COUNT(Orders.OrderID) AS NumberOfOrders FROM OrdersINNER JOIN EmployeesON Orders.EmployeeID=Employees.EmployeeIDWHERE LastName='Davolio' OR LastName='Fuller'GROUP BY LastNameHAVING COUNT(Orders.OrderID) > 25; ................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download