- To retrieve data from a table or view the SELECT statement is used.
- To filter the result set a WHERE clause is ADDED to the SELECT statement.
- To SORT the result set the ORDER BY clause is used with the SELECT statement.
- The manipulation and formatting of the result set is done using Concatenation, aliases, and string literals.
- To retrieve columns from related tables and group the results into a single result set the JOIN clause is used.
- The following are different types of JOINS namely: INNER, LEFT OUTER, RIGHT OUTER, FULL OUTER, and CROSS.
- JOIN operators can combine more than two tables.
- Using different aliases of the same table A, the table A can be joined to itself.
- Aggregate functions perform calculations on expressions that are provided as input to the function.
- Use the GROUP BY clause when aggregation needs to be applied based on the data in specific rows rather than the entire table.
- We can include all columns into the GROUP BY clause.that are listed in a SELECT, WHERE, or ORDER BY clause
- To provide additional summary information use ROLLUP and CUBE.
- Use the GROUPING function to display the rows which hold summary data provided by the ROLLUP or CUBE operators.
- To provide enhanced readability to your GROUP BY queries use GROUPING SETS.
- The UNION operator is used to add result sets from two or more SELECT statements.
- The EXCEPT operator is used for extracting rows that are in the left SELECT statement and do not have matching rows in the right SELECT statement.
- The INTERSECT operator returns only rows that are common by the two SELECT
- The APPLY operator passes the results from a query as input to apply a table-valued
function for each row in the result set.
- OUTER APPLY retrieves all rows from the outer table along with the results returned by the function when rows are common, whereas CROSS APPLY returns only those rows from the outer table where a match exists within the function results.
- To provide more meaningful result sets use built-in functions.
- To manipulate and return date information use date and time functions.
- Use string functions to format or return information about string expressions.
Modifying Data—The INSERT, UPDATE, DELETE, and MERGE Statements
- To add new rows to a table use the INSERT statement.
- The UPDATE statement allows you to modify changes to the existing data in a table.
It allows you not only to modify the value in a column, it also allows you to add or
remove a value from a single column in the table without affecting the rest of the row
- The DELETE statement allows you to delete one or more rows from a table.
- The OUTPUT clause allows you to redirect information to the calling application, or
to an object such as a table or a table variable, about the INSERT, UPDATE, or DELETE statement performed.
- The MERGE statement is used to perform DML actions on a target table based on whether or not a row matches information found in a source table.