INFORMATICS

MSSQL Tutorial

The SELECT statement is used to select data from a database.
The SQL SELECT Statement
The SELECT statement is used to select data from a database.

SELECT Syntax:
SELECT column1, column2, ...
FROM table_name;

SELECT * FROM table_name;

The SQL SELECT DISTINCT Statement
The SELECT DISTINCT statement is used to return only distinct (different) values.
SELECT DISTINCT Syntax
SELECT DISTINCT column1, column2, ...
FROM table_name;

The SQL WHERE Clause
The WHERE clause is used to filter records.
WHERE Syntax
SELECT column1, column2, ...
FROM table_name
WHERE condition;

The SQL AND, OR and NOT Operators
The WHERE clause can be combined with AND, OR, and NOT operators.
AND Syntax
SELECT column1, column2, ...
FROM table_name
WHERE condition1 AND condition2 AND condition3 ...;

OR Syntax
SELECT column1, column2, ...
FROM table_name
WHERE condition1 OR condition2 OR condition3 ...;

The SQL ORDER BY Keyword
The ORDER BY keyword is used to sort the result-set in ascending or descending order.
The ORDER BY keyword sorts the records in ascending order by default. To sort the records in descending order, use the DESC keyword.
ORDER BY Syntax
SELECT column1, column2, ...
FROM table_name
ORDER BY column1, column2, ... ASC|DESC;

The SQL INSERT INTO Statement
The INSERT INTO statement is used to insert new records in a table.
INSERT INTO Syntax
INSERT INTO table_name (column1, column2, column3, ...)
VALUES (value1, value2, value3, ...);

SQL NULL Values
A field with a NULL value is a field with no value.
IS NULL Syntax
SELECT column_names
FROM table_name
WHERE column_name IS NULL;

A field with a NULL value is a field with no value.
IS NOT NULL Syntax
SELECT column_names
FROM table_name
WHERE column_name IS NOT NULL;

SQL UPDATE Statement
The UPDATE statement is used to modify the existing records in a table.
UPDATE Syntax
UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition;

SQL DELETE Statement
The DELETE statement is used to delete existing records in a table.
DELETE Syntax
DELETE FROM table_name WHERE condition;

SQL TOP, LIMIT, FETCH FIRST or ROWNUM Clause
The SELECT TOP clause is used to specify the number of records to return.
SQL Server / MS Access Syntax:

SELECT TOP number|percent column_name(s)
FROM table_name
WHERE condition;
MySQL Syntax:

SELECT column_name(s)
FROM table_name
WHERE condition
LIMIT number;

SQL MIN() and MAX() Functions
MIN() Syntax
SELECT MIN(column_name)
FROM table_name
WHERE condition;
MAX() Syntax
SELECT MAX(column_name)
FROM table_name
WHERE condition;

SQL COUNT(), AVG() and SUM()
The COUNT() function returns the number of rows that matches a specified criterion.
COUNT() Syntax
SELECT COUNT(column_name)
FROM table_name
WHERE condition;
The AVG() function returns the average value of a numeric column.

The AVG() function returns the average value of a numeric column.
AVG() Syntax
SELECT AVG(column_name)
FROM table_name
WHERE condition;
The SUM() function returns the total sum of a numeric column.

The SUM() function returns the total sum of a numeric column.
SUM() Syntax
SELECT SUM(column_name)
FROM table_name
WHERE condition;

SQL LIKE Operator
The LIKE operator is used in a WHERE clause to search for a specified pattern in a column.
LIKE Syntax
SELECT column1, column2, ...
FROM table_name
WHERE columnN LIKE pattern;

SQL IN Operator
The IN operator allows you to specify multiple values in a WHERE clause.
IN Syntax
SELECT column_name(s)
FROM table_name
WHERE column_name IN (value1, value2, ...);

SQL BETWEEN Operator
BETWEEN Syntax
SELECT column_name(s)
FROM table_name
WHERE column_name BETWEEN value1 AND value2;

SQL Aliases
SQL aliases are used to give a table, or a column in a table, a temporary name.
Alias Column Syntax
SELECT column_name AS alias_name
FROM table_name;
Alias Table Syntax
SELECT column_name(s)
FROM table_name AS alias_name;

SQL Joins
A JOIN clause is used to combine rows from two or more tables, based on a related column between them.
SQL LEFT JOIN Keyword The LEFT JOIN keyword returns all records from the left table (table1), and the matching records from the right table (table2). The result is 0 records from the right side, if there is no match.
LEFT JOIN Syntax
SELECT column_name(s)
FROM table1
LEFT JOIN table2
ON table1.column_name = table2.column_name;

SQL RIGHT JOIN Keyword
The RIGHT JOIN keyword returns all records from the right table (table2), and the matching records from the left table (table1). The result is 0 records from the left side, if there is no match.
RIGHT JOIN Syntax
SELECT column_name(s)
FROM table1
RIGHT JOIN table2
ON table1.column_name = table2.column_name;

FULL OUTER JOIN Syntax
SELECT column_name(s)
FROM table1
FULL OUTER JOIN table2
ON table1.column_name = table2.column_name
WHERE condition;

SQL Self Join
A self join is a regular join, but the table is joined with itself.
Self Join Syntax
SELECT column_name(s)
FROM table1 T1, table1 T2
WHERE condition;

SQL UNION Operator]
The UNION operator is used to combine the result-set of two or more SELECT statements.
UNION Syntax
SELECT column_name(s) FROM table1
UNION
SELECT column_name(s) FROM table2;

SQL GROUP BY Statement
The GROUP BY statement groups rows that have the same values into summary rows, like "find the number of customers in each country".
GROUP BY Syntax
SELECT column_name(s)
FROM table_name
WHERE condition
GROUP BY column_name(s)
ORDER BY column_name(s);

SQL HAVING Clause
The HAVING clause was added to SQL because the WHERE keyword cannot be used with aggregate functions.
HAVING Syntax
SELECT column_name(s)
FROM table_name
WHERE condition
GROUP BY column_name(s)
HAVING condition
ORDER BY column_name(s);

The SQL EXISTS Operator
The EXISTS operator is used to test for the existence of any record in a subquery.
EXISTS Syntax
SELECT column_name(s)
FROM table_name
WHERE EXISTS
(SELECT column_name FROM table_name WHERE condition);

SQL ANY and ALL Operators
The ANY and ALL operators allow you to perform a comparison between a single column value and a range of other values.
ANY Syntax
SELECT column_name(s)
FROM table_name
WHERE column_name operator ANY
(SELECT column_name
FROM table_name
WHERE condition);

SQL SELECT INTO Statement
The SELECT INTO statement copies data from one table into a new table.
SELECT INTO Syntax
Copy all columns into a new table:

SELECT *
INTO newtable [IN externaldb]
FROM oldtable
WHERE condition;
Copy only some columns into a new table:

SELECT column1, column2, column3, ...
INTO newtable [IN externaldb]
FROM oldtable
WHERE condition;

The SQL INSERT INTO SELECT Statement
INSERT INTO SELECT Syntax
Copy all columns from one table to another table:

INSERT INTO table2
SELECT * FROM table1
WHERE condition;
Copy only some columns from one table into another table:

INSERT INTO table2 (column1, column2, column3, ...)
SELECT column1, column2, column3, ...
FROM table1
WHERE condition;

CASE Syntax
CASE
WHEN condition1 THEN result1
WHEN condition2 THEN result2
WHEN conditionN THEN resultN
ELSE result
END;

Single Line Comments
-- Single Line Comments