SQL

Topics related to SQL:

Getting started with SQL

SQL is Structured Query Language used to manage data in a relational database system. Different vendors have improved upon the language and have variety of flavors for the language.

NB: This tag refers explicitly to the ISO/ANSI SQL standard; not to any specific implementation of that standard.

SELECT

SELECT determines which columns' data to return and in which order FROM a given table (given that they match the other requirements in your query specifically - where and having filters and joins).

SELECT Name, SerialNumber
FROM ArmyInfo

will only return results from the Name and Serial Number columns, but not from the column called Rank, for example

SELECT *
FROM ArmyInfo

indicates that all columns will be returned. However, please note that it is poor practice to SELECT * as you are literally returning all columns of a table.

JOIN

Joins, as their name suggests, are a way of querying data from several tables in a joint fashion, with the rows displaying columns taken from more than one table.

Example Databases and Tables

UPDATE

Indexes

Indexes are a way of speeding up read queries by sorting the rows of a table according to a column.

The effect of an index is not noticeable for small databases like the example, but if there are a large number of rows, it can greatly improve performance. Instead of checking every row of the table, the server can do a binary search on the index.

The tradeoff for creating an index is write speed and database size. Storing the index takes space. Also, every time an INSERT is done or the column is updated, the index must be updated. This is not as expensive an operation as scanning the entire table on a SELECT query, but it is still something to keep in mind.

CREATE TABLE

Table names must be unique.

UNION / UNION ALL

UNION and UNION ALL clauses combine the result-set of two or more identically structured SELECT statements into a single result / table.

Both the column count and column types for each query have to match in order for a UNION / UNION ALL to work.

The difference between a UNION and a UNION ALL query is that the UNION clause will remove any duplicate rows in the result where the UNION ALL will not.

This distinct removal of records can significantly slow queries even if there are no distinct rows to be removed because of this if you know there wont be any duplicates (or don't care) always default to UNION ALL for a more optimised query.

ALTER TABLE

CASE

The simple CASE expression returns the first result whose compareX value is equal to the input_expression.

The searched CASE expression returns the first result whose conditionX is true.

INSERT

Primary Keys

ORDER BY

GROUP BY

Filter results using WHERE and HAVING

Window Functions

Common Table Expressions

Official documentation: WITH clause

A Common Table Expression is a temporary result set, and it can be result of complex sub query. It is defined by using WITH clause. CTE improves readability and it is created in memory rather than TempDB database where Temp Table and Table variable is created.

Key concepts of Common Table Expressions:

Can be used to break up complex queries, especially complex joins and sub-queries.
Is a way of encapsulating a query definition.
Persist only until the next query is run.
Correct use can lead to improvements in both code quality/maintainability and speed.
Can be used to reference the resulting table multiple times in the same statement (eliminate duplication in SQL).
Can be a substitute for a view when the general use of a view is not required; that is, you do not have to store the definition in metadata.
Will be run when called, not when defined. If the CTE is used multiple times in a query it will be run multiple times (possibly with different results).

Views

LIKE operator

LIKE condition in WHERE clause is used to search for column values that matches the given pattern. Patterns are formed using following two wildcard characters

% (Percentage Symbol) - Used for representing zero or more characters
_ (Underscore) - Used for representing a single character

Functions (Aggregate)

In database management an aggregate function is a function where the values of multiple rows are grouped together as input on certain criteria to form a single value of more significant meaning or measurement such as a set, a bag or a list.

MIN        returns the smallest value in a given column
MAX        returns the largest value in a given column
SUM        returns the sum of the numeric values in a given column
AVG        returns the average value of a given column
COUNT      returns the total number of values in a given column
COUNT(*)   returns the number of rows in a table
GROUPING   Is a column or an expression that contains a column in a GROUP BY clause.
STDEV      returns the statistical standard deviation of all values in the specified expression.
STDEVP     returns the statistical standard deviation for the population for all values in the specified expression.
VAR        returns the statistical variance of all values in the specified expression. may be followed by the OVER clause.
VARP       returns the statistical variance for the population for all values in the specified expression.

Aggregate functions are used to compute against a "returned column of numeric data" from your SELECT statement. They basically summarize the results of a particular column of selected data. - SQLCourse2.com

All aggregate functions ignore NULL values.

DELETE

String Functions

String functions reference for Transact-SQL / Microsoft

String functions reference for MySQL

String functions reference for PostgreSQL

Data Types

AND & OR Operators

Triggers

TRUNCATE

TRUNCATE is a DDL (Data Definition Language) command, and as such there are significant differences between it and DELETE (a Data Manipulation Language, DML, command). While TRUNCATE can be a means of quickly removing large volumes of records from a database, these differences should be understood in order to decide if using a TRUNCATE command is suitable in your particular situation.

TRUNCATE is a data page operation. Therefore DML triggers (ON DELETE) associated with the table won't fire when you perform a TRUNCATE operation. While this will save a large amount of time for massive delete operations, however you may then need to manually delete the related data.
TRUNCATE will release the disk space used by the deleted rows, DELETE will release space
If the table to be truncated uses identity columns (MS SQL Server), then the seed is reset by the TRUNCATE command. This may result referential integrity problems
Depending the security roles in place and the variant of SQL in use, you may not have the necessary permissions to perform a TRUNCATE command

MERGE

Foreign Keys

Finding Duplicates on a Column Subset with Detail

To select rows with out duplicates change the WHERE clause to "RowCnt = 1"
To select one row from each set use Rank() instead of Sum() and change the outer WHERE clause to select rows with Rank() = 1

Sequence

Comments

Subqueries

Subqueries can appear in different clauses of an outer query, or in the set operation.

They must be enclosed in parentheses (). If the result of the subquery is compared to something else, the number of columns must match. Table aliases are required for subqueries in the FROM clause to name the temporary table.

Execution blocks

Stored Procedures

Stored Procedures are SQL statements stored in the database that can be executed or called in queries. Using a stored procedure allows encapsulation of complicated or frequently used logic, and improves query performance by utilizing cached query plans. They can return any value a standard query can return.

Other benefits over dynamic SQL expressions are listed on Wikipeida.

DROP Table

DROP TABLE removes the table definition from the schema along with the rows, indexes, permissions, and triggers.

Row number

Transactions

A transaction is a logical unit of work containing one or more steps, each of which must complete successfully in order for the transaction to commit to the database. If there are errors, then all of the data modifications are erased and the database is rolled back to its initial state at the start of the transaction.

CREATE FUNCTION

CREATE FUNCTION creates a user-defined function that can be used when doing a SELECT, INSERT, UPDATE, or DELETE query. The functions can be created to return a single variable or a single table.

SQL Group By vs Distinct

Table Design

The Open University (1999) Relational Database Systems: Block 2 Relational Theory, Milton Keynes, The Open University.

cross apply, outer apply

Synonyms

CREATE Database

SKIP TAKE (Pagination)

EXPLAIN and DESCRIBE

Information Schema

IN clause

NULL

SQL Injection

Cascading Delete

Order of Execution

DROP or DELETE Database

DROP DATABASE is used for dropping a database from SQL. Be sure to create a backup of your database before dropping it to prevent accidental loss of information.

EXCEPT

EXCEPT returns any distinct values from the dataset to the left of the EXCEPT operator that are not also returned from the right dataset.

TRY/CATCH

TRY/CATCH is a language construct specific to MS SQL Server's T-SQL.

It allows error handling within T-SQL, similar to that seen in .NET code.

XML

GRANT and REVOKE

Grant permissions to users. If the WITH GRANT OPTION is specified, the grantee additionally gains the privilege to grant the given permission or revoke previously granted permissions.

Functions (Scalar/Single Row)

Scalar or Single-Row functions are used to operate each row of data in the result set, as opposed to aggregate functions which operate on the entire result set.

There are ten types of scalar functions.

Configuration functions provide information about the configuration of the current SQL instance.
Conversion functions convert data into the correct data type for a given operation. For example, these types of functions can reformat information by converting a string to a date or number to allow two different types to be compared.
Date and time functions manipulate fields containing date and time values. They can return numeric, date, or string values. For example, you can use a function to retrieve the current day of the week or year or to retrieve only the year from the date.

The values returned by date and time functions depend on the date and time set for the operating system of the computer running the SQL instance.

Logical function that performs operations using logical operators. It evaluates a set of conditions and returns a single result.
Mathematical functions perform mathematical operations, or calculations, on numeric expressions. This type of function returns a single numeric value.
Metadata functions retrieve information about a specified database, such as its name and database objects.
Security functions provide information that you can use to manage the security of a database, such as information about database users and roles.
String functions perform operations on string values and return either numeric or string values.

Using string functions, you can, for example, combine data, extract a substring, compare strings, or convert a string to all uppercase or lowercase characters.

System functions perform operations and return information about values, objects, and settings for the current SQL instance
System statistical functions provide various statistics about the current SQL instance – for example, so that you can monitor the system's current performance levels.

SQL