SQL

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...

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>>

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...

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>>

The following table lists the most important built-in date functions in SQL Server: Function Description GETDATE() Returns the current date and time DATEPART() Returns a single part of a date/time DATEADD() Adds or subtracts a specified time interval from a date DATEDIFF() Returns the time between two dates CONVERT() Displays date/time data in different formats SQL Server comes with the following data types for storing a date or a date/time value in the database: § DATE - format YYYY-MM-DD § DATETIME - format: YYYY-MM-DD HH:MI:SS § SMALLDATETIME - format: YYYY-MM-DD HH:MI:SS § TIMESTAMP - format: a unique number <<Prev                                                                        ...