Skip to main content

Posts

INDEX

                                                                                   INDEX      No.     Article                                                                       1.     Introduction of SQL           2.     Table Basic           3.     Keys and Constraints           4.     Referential integrity           5.     INDEX           6.     VIEW           7.     Basic Queries           8.     DATATYPES           9.     Basic keywords and Its uses           10.    SQL Aliases           11.    Date Functions           12.    INSERT UPDATE and DELETE Queries           13.   Filtering Results           14.   Ordering Results           15.   Aggregate Functions                                                                                                         Start>>           
Recent posts

15. Aggregate Functions

SQL has many built-in functions for performing calculations on data. Aggregate Functions AVG() Function The AVG() function returns the average value of a numeric column. § Syntax SELECT AVG ( column_name ) FROM table_name § Example SQL statement gets the average value of the "Price" column from the "Products" table: SELECT AVG ( Price ) AS PriceAverage FROM Products SQL statement selects the "ProductName" and "Price" records that have an above average price: SELECT ProductName , Price FROM Products WHERE Price >( SELECT AVG ( Price ) FROM Products ); COUNT() Function The COUNT() function returns the number of rows that matches a specified criteria.The COUNT(column_name) function returns the number of values (NULL values will not be counted) of the specified column: § Syntax SELECT COUNT ( column_name ) FROM table_name ; MAX() Function The MAX() function returns the largest value of the s

14. Ordering Results

ORDER BY used to sort the result-set by one or more columns. It Sorts the records in ascending order by default. To sort the records in a descending order, you can use the DESC keyword. SELECT column_name , column_name FROM table_name ORDERBY column_name ASC | DESC , column_name ASC | DESC ; SELECT * FROM Customers ORDERBY Country DESC ;      <<Prev                                                                                      Next>>

13. Filtering Results

WHERE and HAVING are filters. They specify a series of search conditions, and only those rows that meet the terms of the search conditions are used to build the result set. Where SELECT * FROM Customers WHERE CustomerID = 1 ; GROUP BY The GROUP BY statement is used in conjunction with the aggregate functions to group the result-set by one or more columns. SELECT column_name , aggregate_function ( column_name ) FROM table_name WHERE column_name operator value GROUPBY column_name ; § Example SELECT Shippers . ShipperName , COUNT ( Orders . OrderID ) AS NumberOfOrders FROM Orders LEFTJOIN Shippers ON Orders . ShipperID = Shippers . ShipperID GROUPBY ShipperName ; Having § The HAVING clause is typically used together with the GROUP BY clause to filter the results of aggregate values. § The HAVING clause was added to SQL because the WHERE keyword could not be used with aggregate functions. SELECT column_name , aggregate_function ( colu

12. INSERT UPDATE and DELETE Queries

INSERT The INSERT INTO statement is used to insert new records in a table INSERTINTO table_name VALUES ( value1 , value2 , value3 ,...); Or INSERTINTO table_name ( column1 , column2 , column3 ,...) VALUES ( value1 , value2 , value3 ,...); UPDATE Used to update existing records in a table. UPDATE table_name SET column1 = value1 , column2 = value2 ,... WHERE some_column = some_value ; DELETE Used to delete rows in a table. DELETEFROM table_name WHERE some_column = some_value ; <<Prev                                                                           Next>>