MansourM61/sql-database-guide.md

## sql-database-guide.md

      
    Raw
  

              sql-database-guide.md
            
          
    SQL Guide

SQLite

sqlite3 Shell


To load a database file (from command line):
sqlite3 <database-file>


To show all database in the current connection:
.databases


To open a database:
.open <database-file>


To add a database to the current connection:
attach database <database-file> as <database-name>


To list all tables:
.tables


To show a table schema:
.schema <table-name>


To show index of a table:
.indexes <table-name>


To save the query results to a local file:
.output <file-name>
To save all subsequent result to the same file:
.output


To read a SQL statement from a file and execute it:
.read <SQL-filename>


MySQL

mysql Shell


To connect to the server (from command line):
mysql -h <host> -P <port> -u <user> -p <database>


To create a new user (as root):
CREATE USER '<user>'@'localhost' IDENTIFIED BY '<password>'


To list all users (as root):
SELECT user FROM mysql.user;


To create the database:
CREATE DATABASE <database>;


To give the user the access to the database:
GRANT ALL PRIVILEGES ON <database>.* TO '<user>'@'localhost';


To show databases:
SHOW DATABASES;


To use a database:
USE <database>;


To show tables:
SHOW TABLES;


To create a table:
CREATE TABLE <table-name> (<column-name> <column-type>, ...);


To check a table:
DESCRIBE <table-name>;


To populate the table from a local file (a CSV file with <tab> as delimiter; NULL values are represented by "\N"):
LOAD DATA LOCAL INFILE 'csv/file/path' INTO TABLE <table-name>;
if line termination is "\r\n" (Windows-based editors), use:
LOAD DATA LOCAL INFILE 'csv/file/path' INTO TABLE <table-name> LINES TERMINATED BY '\r\n';
if line termination is "\r" (Mac-based editors), use:
LOAD DATA LOCAL INFILE 'csv/file/path' INTO TABLE <table-name> LINES TERMINATED BY '\r';
if other delimiter is used:
LOAD DATA LOCAL INFILE 'csv/file/path' INTO TABLE <table-name> FIELDS TERMINATED BY '<delimiter>';
To ignore the header lines ( line at the beginning):
LOAD DATA LOCAL INFILE 'csv/file/path' INTO TABLE <table-name> IGNORE <n> LINES;
To populate specific columns of the table with data from the file:
LOAD DATA LOCAL INFILE 'csv/file/path' INTO TABLE <table-name> (<column-name>, ...);


To verify that the data was inserted in the table:
TABLE <table-name>;


SQL Syntax


Structured Query Language (SQL) is a standard language for accessing and manipulating databases.


SQL is based on Relational Database Management System (RDBMS).


The data in RDBMS is stored in database objects called tables.


Databases contains one or more tables and each table is identified by a unique name.


Table has columns fields and each row is a record stored in the table.


Normalization is the process of organizing data in a database so that it

Eliminate repeating groups in individual tables..
Create a separate table for each set of related data.
Identify each set of related data with a primary key.


Actions in SQL are done using SQL statements.


SQL keywords are generally not case-sensitive. But as a tradition, it is written in all capital.


A field with a NULL value is a field with no value.


Dot notation (membership operator) is used to extract a column from a record.


Single line comments start with --.


Multi-line comments start with /* and end with */.


List of common SQL statements:


SQL statement
Syntax
Description
Example


SELECT
SELECT column1, column2, ... FROM table_name;
selects data from a database
SELECT CustomerName, City FROM Customers;


SELECT *
SELECT * FROM table_name;
returns all the columns from a database
SELECT * FROM Customers;


SELECT DISTINCT
SELECT DISTINCT column1, column2, ... FROM table_name;
returns only distinct (different) values
SELECT DISTINCT Country FROM Customers;


WHERE
SELECT column1, column2, ... FROM table_name WHERE condition;
selects data from the table while applying a filter
SELECT * FROM Customers WHERE CustomerID>80;


ORDER BY
SELECT column1, column2, ...FROM table_name ORDER BY column1, column2, ... ASC | DESC;
sorts the result-set in ascending or descending order.
SELECT * FROM Customers ORDER BY Country ASC, CustomerName DESC;


INSERT INTO
INSERT INTO table_name [(column1, column2, column3, ...)] VALUES (value1, value2, value3, ...);
inserts new records in a table (if you are adding values for all the columns of the table, you do not need to specify the column names in the SQL query.)
INSERT INTO Customers (CustomerName, ContactName, Address, City, PostalCode, Country) VALUES ('Cardinal', 'Tom B. Erichsen', 'Skagen 21', 'Stavanger', '4006', 'Norway');


UPDATE
UPDATE table_name SET column1 = value1, column2 = value2, ... WHERE condition;
modifies the existing records in a table
UPDATE Customers SET ContactName='Juan' WHERE Country='Mexico';


UPDATE
UPDATE table_name SET column1 = value1, column2 = value2;
modifies the existing records in a whole table
UPDATE Customers SET ContactName='Juan';


DELETE
DELETE FROM table_name WHERE condition;
deletes existing records in a table
DELETE FROM Customers WHERE CustomerName='Alfreds Futterkiste';


DELETE
DELETE FROM table_name;
deletes all records in a table
DELETE FROM Customers;


DROP TABLE
DROP TABLE table_name;
deletes the table completely
DROP TABLE Customers;


AS
SELECT column_name AS alias_name FROM table_name;
creates alias for column
SELECT CustomerID AS ID, CustomerName AS Customer FROM Customers;


AS
SELECT column_name(s) FROM table_name AS alias_name;
creates alias for table
SELECT * FROM Customers AS Persons;


[INNER] JOIN
SELECT column_name(s) FROM table1 INNER JOIN table2 ON table1.column_name = table2.column_name;
returns records that have matching values in both tables
SELECT Orders.OrderID, Customers.CustomerName, Orders.OrderDate FROM Orders INNER JOIN Customers ON Orders.CustomerID=Customers.CustomerID;


LEFT JOIN
SELECT column_name(s) FROM table1 LEFT JOIN table2 ON table1.column_name = table2.column_name;
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.
SELECT Customers.CustomerName, Orders.OrderID FROM Customers LEFT JOIN Orders ON Customers.CustomerID = Orders.CustomerID ORDER BY Customers.CustomerName;


RIGHT JOIN
SELECT column_name(s) FROM table1 RIGHT JOIN table2 ON table1.column_name = table2.column_name;
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.
SELECT Orders.OrderID, Employees.LastName, Employees.FirstName FROM Orders RIGHT JOIN Employees ON Orders.EmployeeID = Employees.EmployeeID ORDER BY Orders.OrderID;


FULL [OUTER] JOIN
SELECT column_name(s) FROM table1 FULL OUTER JOIN table2 ON table1.column_name = table2.column_name WHERE condition;
returns all records when there is a match in left (table1) or right (table2) table records.
SELECT Customers.CustomerName, Orders.OrderID FROM Customers FULL OUTER JOIN Orders ON Customers.CustomerID=Orders.CustomerID ORDER BY Customers.CustomerName;


UNION
SELECT column_name(s) FROM table1 UNION SELECT column_name(s) FROM table2;
combines the result-set of two or more SELECT statements with the same number of columns, similar data types, and same column order
SELECT City FROM Customers UNION SELECT City FROM Suppliers ORDER BY City;


UNION ALL
SELECT column_name(s) FROM table1 UNION ALL SELECT column_name(s) FROM table2;
Similar to UNION, but allows duplicates
SELECT City FROM Customers UNION ALL SELECT City FROM Suppliers ORDER BY City;


GROUP BY
SELECT column_name(s) FROM table_name WHERE condition GROUP BY column_name(s) ORDER BY column_name(s);
often used with aggregate functions, group the result-set by one or more columns.
SELECT Shippers.ShipperName, COUNT(Orders.OrderID) AS NumberOfOrders FROM Orders LEFT JOIN Shippers ON Orders.ShipperID = Shippers.ShipperID GROUP BY ShipperName;


HAVING
SELECT column_name(s) FROM table_name WHERE condition GROUP BY column_name(s) HAVING condition ORDER BY column_name(s);
the same as WHERE but can use aggregate functions
SELECT COUNT(CustomerID), Country FROM Customers GROUP BY Country HAVING COUNT(CustomerID) > 5 ORDER BY COUNT(CustomerID) DESC;


ANY
SELECT column_name(s) FROM table_name WHERE column_name operator ANY (SELECT column_name FROM table_name WHERE condition);
returns TRUE if any of the subquery values meet the condition
SELECT ProductName FROM Products WHERE ProductID = ANY (SELECT ProductID FROM OrderDetails WHERE Quantity = 10);


ALL
SELECT column_name(s) FROM table_name WHERE column_name operator ALL (SELECT column_name FROM table_name WHERE condition);
returns TRUE if all of the subquery values meet the condition
SELECT ProductName FROM Products WHERE ProductID = ALL (SELECT ProductID FROM OrderDetails WHERE Quantity = 10);


SELECT INTO
SELECT column1, column2, column3, ... INTO newtable [IN externaldb] FROM oldtable WHERE condition;
copies data from one table into a new table
SELECT * INTO CustomersBackup2017 IN 'Backup.mdb' FROM Customers;


INSERT INTO SELECT
INSERT INTO table2 (column1, column2, column3, ...) SELECT column1, column2, column3, ... FROM table1 WHERE condition;
copies data from one table and inserts it into another table. The data types in source and target tables must match.
INSERT INTO Customers (CustomerName, City, Country) SELECT SupplierName, City, Country FROM Suppliers;


CASE
CASE 
   WHEN condition1 THEN result1 
   WHEN condition2 THEN result2 
   WHEN conditionN THEN resultN 
   ELSE result 
 END;
goes through conditions and returns a value when the first condition is met
SELECT OrderID, Quantity, 
 CASE 
   WHEN Quantity > 30 THEN 'The quantity is greater than 30' 
   WHEN Quantity = 30 THEN 'The quantity is 30' 
   ELSE 'The quantity is under 30' 
 END AS QuantityText 
 FROM OrderDetails;


A SQL statement such as:
SELECT COUNT(CustomerID), Country
FROM Customers
GROUP BY Country;
selects Country column and counts the number of CustomerID grouped under the same Country.


Subquery is a query that returns a result set of one column.


ALL is used with SELECT, WHERE and HAVING statements.


List of operator used in WHERE clause:


Operator
Description
Example


=
Equal
WHERE CustomerID=1


>
Greater than
WHERE CustomerID>0


<
Less than
WHERE CustomerID<80


>=
Greater than or equal
WHERE CustomerID>=80


<=
Less than or equal
WHERE CustomerID<=80


<>
Not equal. Note: In some versions of SQL this operator may be written as !=
WHERE CustomerID<>80


BETWEEN
Between a certain range
WHERE CustomerID BETWEEN 80 AND 100


LIKE
Search for a pattern
WHERE CustomerName LIKE 'a%'


IN
To specify multiple possible values for a column
WHERE Country IN ('Germany', 'France', 'UK')


AND
filter records based more than one condition where all are true
WHERE Country = 'Spain' AND CustomerName LIKE 'G%'


OR
filter records based more than one condition where at least one is true
WHERE City = 'Berlin' OR CustomerName LIKE 'G%' OR Country = 'Norway'


NOT
filter records based on a condition where it is not true
WHERE CustomerID NOT BETWEEN 10 AND 60


IS NULL
whether a field is null
WHERE Address IS NULL


IS NOT NULL
whether a field is not null
WHERE Address IS NOT NULL


EXISTS
returns TRUE if the subquery returns one or more records.
SELECT SupplierName FROM Suppliers WHERE EXISTS (SELECT ProductName FROM Products WHERE Products.SupplierID = Suppliers.supplierID);


ANY
allows you to perform a comparison between a single column value and a range of other values
SELECT ProductName FROM Products WHERE ProductID = ANY (SELECT ProductID FROM OrderDetails WHERE Quantity = 10);


Conditions can be grouped by parenthesis.


List of patterns when using LIKE:


Symbol
Meaning
Example


%
Represents zero or more characters
SELECT * FROM Customers WHERE CustomerName LIKE '%mer%';


_
Represents a single character
SELECT * FROM Customers WHERE City LIKE 'L___on';


[]
Represents any single character within the brackets (not available in PostgreSQL or MySQL)
SELECT * FROM Customers WHERE CustomerName LIKE '[bsp]%';


^
Represents any character not in the brackets (not available in PostgreSQL or MySQL)
SELECT * FROM Customers WHERE CustomerName LIKE '[^bsp]%';


-
Represents any single character within the specified range (not available in PostgreSQL or MySQL)
SELECT * FROM Customers WHERE CustomerName LIKE '[a-f]%';


List of aggregate functions:


Function Name
Description
Example


MIN()
returns the smallest value within the selected column
SELECT MIN(Price) FROM Products;


MAX()
returns the largest value within the selected column
SELECT MIN(Price) AS SmallestPrice FROM Products;


COUNT()
returns the number of rows in a set
SELECT COUNT(ProductName) FROM Products;


SUM()
returns the total sum of a numerical column
SELECT SUM(Quantity) FROM OrderDetails WHERE ProductId = 11;


AVG()
returns the average value of a numerical column
SELECT AVG(Price) AS [average price], CategoryID FROM Products GROUP BY CategoryID;


Aggregate functions ignore null values (except for COUNT()).


COUNT(DISTINCT column) will ignore duplicates.


Aggregate functions are often used with the GROUP BY clause of the SELECT statement.


SQL aliases are used to give a table, or a column in a table, a temporary name during the query.


To use space-separated words as alias, wrap them in [] or double quote.


You can combine columns and return the query as an alias by using on one of the following syntaxes:
SELECT column_name, column1 + ', ' + column2 + ... AS alias_name FROM table_name;
or
SELECT column_name, CONCAT(column1, ', ', column2, ...) AS alias_name FROM table_name;
or
SELECT column_name, (column1 || ', ' || column2 || ...) AS alias_name FROM table_name;


IFNULL() (in MySQL) lets you return an alternative value if an expression is NULL:
SELECT ProductName, UnitPrice * (UnitsInStock + IFNULL(UnitsOnOrder, 0))
FROM Products;


ISNULL() (in SQL Server) lets you return an alternative value when an expression is NULL:
SELECT ProductName, UnitPrice * (UnitsInStock + ISNULL(UnitsOnOrder, 0))
FROM Products;


COALESCE() can be used in both MySQL and SQL Server to return an alternative value when an expression is NULL:
SELECT ProductName, UnitPrice * (UnitsInStock + COALESCE(UnitsOnOrder, 0))
FROM Products;


use the following syntax to store a procedure that is repeatedly used in SQL
CREATE PROCEDURE procedure_name
AS
sql_statement
GO;
To run the stored procedure:
EXEC procedure_name;


To create a database:
CREATE DATABASE databasename;


To drop an existing database:
DROP DATABASE databasename;


To create a full back up of an existing SQL database:
BACKUP DATABASE databasename
TO DISK = 'filepath' [WITH DIFFERENTIAL];
Differential back up reduces the back up time (since only the changes are backed up).


To create a table:
CREATE TABLE table_name (
    column1 datatype [constraint],
    column2 datatype [constraint],
    column3 datatype [constraint],
....
);
SQL constraints are used to specify rules for the data in a table.


List of constraints:


Constraint
Description


NOT NULL
Ensures that a column cannot have a NULL value


UNIQUE
Ensures that all values in a column are different


PRIMARY KEY
A combination of a NOT NULL and UNIQUE. Uniquely identifies each row in a table


FOREIGN KEY
Prevents actions that would destroy links between tables


CHECK
Ensures that the values in a column satisfies a specific condition


DEFAULT
Sets a default value for a column if no value is specified


CREATE INDEX
Used to create and retrieve data from the database very quickly


AUTO_INCREMENT
Allows a unique number to be generated automatically when a new record is inserted into a table.


Example:
CREATE TABLE Persons (
    ID int NOT NULL,
    LastName varchar(255) NOT NULL,
    FirstName varchar(255),
    Age int,
    PRIMARY KEY (ID)
);


To remove the data from a table:
TRUNCATE TABLE table_name;


To add, delete, or modify columns in an existing table as well as to add and drop various constraints on an existing table:
ALTER TABLE table_name
ADD column_name datatype;

ALTER TABLE table_name
DROP COLUMN column_name;

ALTER TABLE table_name
RENAME COLUMN old_name to new_name;

-- SQL Server
ALTER TABLE table_name
ALTER COLUMN column_name datatype;

-- My SQL
ALTER TABLE table_name
MODIFY COLUMN column_name datatype;
The data type of a column defines what value the column can hold: integer, character, money, date and time, binary, and so on.


In SQL, a view is a virtual table based on the result-set of an SQL statement. A view always shows up-to-date data! The database engine recreates the view, every time a user queries it.
CREATE [OR REPLACE] VIEW view_name AS
SELECT column1, column2, ...
FROM table_name
WHERE condition;


To remove a view:
DROP VIEW view_name;


List of string data type in MySQL:


Data type
Description


CHAR(size)
A FIXED length string (can contain letters, numbers, and special characters). The size parameter specifies the column length in characters - can be from 0 to 255. Default is 1


VARCHAR(size)
A VARIABLE length string (can contain letters, numbers, and special characters). The size parameter specifies the maximum string length in characters - can be from 0 to 65535


BINARY(size)
Equal to CHAR(), but stores binary byte strings. The size parameter specifies the column length in bytes. Default is 1


VARBINARY(size)
Equal to VARCHAR(), but stores binary byte strings. The size parameter specifies the maximum column length in bytes.


TINYBLOB
For BLOBs (Binary Large Objects). Max length: 255 bytes


TINYTEXT
Holds a string with a maximum length of 255 characters


TEXT(size)
Holds a string with a maximum length of 65,535 bytes


BLOB(size)
For BLOBs (Binary Large Objects). Holds up to 65,535 bytes of data


MEDIUMTEXT
Holds a string with a maximum length of 16,777,215 characters


MEDIUMBLOB
For BLOBs (Binary Large Objects). Holds up to 16,777,215 bytes of data


LONGTEXT
Holds a string with a maximum length of 4,294,967,295 characters


LONGBLOB
For BLOBs (Binary Large Objects). Holds up to 4,294,967,295 bytes of data


ENUM(val1, val2, val3, ...)
A string object that can have only one value, chosen from a list of possible values. You can list up to 65535 values in an ENUM list If a value is inserted that is not in the list, a blank value will be inserted. The values are sorted in the order you enter them


SET(val1, val2, val3, ...)
A string object that can have 0 or more values, chosen from a list of possible values. You can list up to 64 values in a SET list


List of numeric data type in MySQL:


Data type
Description


BIT(size)
A bit-value type. The number of bits per value is specified in size. The size parameter can hold a value from 1 to 64. The default value for size is 1.


TINYINT(size)
A very small integer. Signed range is from -128 to 127. Unsigned range is from 0 to 255. The size parameter specifies the maximum display width (which is 255)


BOOL
Zero is considered as false, nonzero values are considered as true.


BOOLEAN
Equal to BOOL


SMALLINT(size)
A small integer. Signed range is from -32768 to 32767. Unsigned range is from 0 to 65535. The size parameter specifies the maximum display width (which is 255)


MEDIUMINT(size)
A medium integer. Signed range is from -8388608 to 8388607. Unsigned range is from 0 to 16777215. The size parameter specifies the maximum display width (which is 255)


INT(size)
A medium integer. Signed range is from -2147483648 to 2147483647. Unsigned range is from 0 to 4294967295. The size parameter specifies the maximum display width (which is 255)


INTEGER(size)
Equal to INT(size)


BIGINT(size)
A large integer. Signed range is from -9223372036854775808 to 9223372036854775807. Unsigned range is from 0 to 18446744073709551615. The size parameter specifies the maximum display width (which is 255)


FLOAT(size, d)
A floating point number. The total number of digits is specified in size. The number of digits after the decimal point is specified in the d parameter. This syntax is deprecated in MySQL 8.0.17, and it will be removed in future MySQL versions


FLOAT(p)
A floating point number. MySQL uses the p value to determine whether to use FLOAT or DOUBLE for the resulting data type. If p is from 0 to 24, the data type becomes FLOAT(). If p is from 25 to 53, the data type becomes DOUBLE()


DOUBLE(size, d)
A normal-size floating point number. The total number of digits is specified in size. The number of digits after the decimal point is specified in the d parameter


DOUBLE PRECISION(size, d)


DECIMAL(size, d)
An exact fixed-point number. The total number of digits is specified in size. The number of digits after the decimal point is specified in the d parameter. The maximum number for size is 65. The maximum number for d is 30. The default value for size is 10. The default value for d is 0.


DEC(size, d)
Equal to DECIMAL(size,d)


List of time and date data type in MySQL:


Data type
Description


DATE
A date. Format: YYYY-MM-DD. The supported range is from '1000-01-01' to '9999-12-31'


DATETIME(fsp)
A date and time combination. Format: YYYY-MM-DD hh:mm:ss. The supported range is from '1000-01-01 00:00:00' to '9999-12-31 23:59:59'. Adding DEFAULT and ON UPDATE in the column definition to get automatic initialization and updating to the current date and time


TIMESTAMP(fsp)
A timestamp. TIMESTAMP values are stored as the number of seconds since the Unix epoch ('1970-01-01 00:00:00' UTC). Format: YYYY-MM-DD hh:mm:ss. The supported range is from '1970-01-01 00:00:01' UTC to '2038-01-09 03:14:07' UTC. Automatic initialization and updating to the current date and time can be specified using DEFAULT CURRENT_TIMESTAMP and ON UPDATE CURRENT_TIMESTAMP in the column definition


TIME(fsp)
A time. Format: hh:mm:ss. The supported range is from '-838:59:59' to '838:59:59'


YEAR
A year in four-digit format. Values allowed in four-digit format: 1901 to 2155, and 0000. MySQL 8.0 does not support year in two-digit format.


List of string data type in SQL Server:


Data type
Description
Max char length
Storage


char(n)
Fixed-length non-Unicode character data (n must be between 1 and 8000)
8,000
n bytes (uses one byte for each character)


varchar(n)
Variable-length non-Unicode character data (n must be between 1 and 8000)
8,000
n bytes + 2 bytes


varchar(max)
Variable-length non-Unicode character data

up to 2 GB


nchar(n)
Fixed-length Unicode character data (n must be between 1 and 4000)
4,000
2 * n bytes (uses two bytes for each character)


nvarchar(n)
Variable-length Unicode character data (n must be between 1 and 4000)
4,000
2 * n bytes + 2 bytes (uses two bytes for each character)


nvarchar(max)
Variable-length Unicode character data

up to 2 GB


binary(n)
Fixed-length binary data (n must be between 1 and 8000)
8,000
n bytes


varbinary(n)
Variable-length binary data (n must be between 1 and 8000)
8,000
actual length of data entered + 2 bytes


varbinary(max)
Variable-length binary data
2GB


List of numeric type in SQL Server:


Data type
Description
Storage


bit
Integer that can be 0, 1, or NULL


tinyint
Allows whole numbers from 0 to 255
1 byte


smallint
Allows whole numbers between -32,768 and 32,767
2 bytes


int
Allows whole numbers between -2,147,483,648 and 2,147,483,647
4 bytes


bigint
Allows whole numbers between -9,223,372,036,854,775,808 and 9,223,372,036,854,775,807
8 bytes


decimal(p,s)
Fixed precision and scale numbers. Allows numbers from -10^38 +1 to 10^38 –1. The p parameter indicates the maximum total number of digits that can be stored (both to the left and to the right of the decimal point). p must be a value from 1 to 38. Default is 18. The s parameter indicates the maximum number of digits stored to the right of the decimal point. s must be a value from 0 to p. Default value is 0
5-17 bytes


numeric(p,s)
Fixed precision and scale numbers. Allows numbers from -10^38 +1 to 10^38 –1. The p parameter indicates the maximum total number of digits that can be stored (both to the left and to the right of the decimal point). p must be a value from 1 to 38. Default is 18. The s parameter indicates the maximum number of digits stored to the right of the decimal point. s must be a value from 0 to p. Default value is 0
5-17 bytes


smallmoney
Monetary data from -214,748.3648 to 214,748.3647
4 bytes


money
Monetary data from -922,337,203,685,477.5808 to 922,337,203,685,477.5807
8 bytes


float(n)
Floating precision number data from -1.79E + 308 to 1.79E + 308.The n parameter indicates whether the field should hold 4 or 8 bytes. float(24) holds a 4-byte field and float(53) holds an 8-byte field. Default value of n is 53.
4 or 8 bytes


real
Floating precision number data from -3.40E + 38 to 3.40E + 38
4 bytes


List od date and time data type in SQL Server:


Data type
Description
Storage


datetime
From January 1, 1753 to December 31, 9999 with an accuracy of 3.33 milliseconds
8 bytes


datetime2
From January 1, 0001 to December 31, 9999 with an accuracy of 100 nanoseconds
6-8 bytes


smalldatetime
From January 1, 1900 to June 6, 2079 with an accuracy of 1 minute
4 bytes


date
Store a date only. From January 1, 0001 to December 31, 9999
3 bytes


time
Store a time only to an accuracy of 100 nanoseconds
3-5 bytes


datetimeoffset
The same as datetime2 with the addition of a time zone offset
8-10 bytes


timestamp
Stores a unique number that gets updated every time a row gets created or modified. The timestamp value is based upon an internal clock and does not correspond to real time. Each table may have only one timestamp variable


List of miscellaneous data type in SQL Server:


Data type
Description


sql_variant
Stores up to 8,000 bytes of data of various data types, except text, ntext, and timestamp


uniqueidentifier
Stores a globally unique identifier (GUID)


xml
Stores XML formatted data. Maximum 2GB


cursor
Stores a reference to a cursor used for database operations


table
Stores a result-set for later processing
SQL statement	Syntax	Description	Example
SELECT	SELECT column1, column2, ... FROM table_name;	selects data from a database	`SELECT CustomerName, City FROM Customers;`
SELECT *	SELECT * FROM table_name;	returns all the columns from a database	`SELECT * FROM Customers;`
SELECT DISTINCT	SELECT DISTINCT column1, column2, ... FROM table_name;	returns only distinct (different) values	`SELECT DISTINCT Country FROM Customers;`
WHERE	SELECT column1, column2, ... FROM table_name WHERE condition;	selects data from the table while applying a filter	`SELECT * FROM Customers WHERE CustomerID>80;`
ORDER BY	SELECT column1, column2, ...FROM table_name ORDER BY column1, column2, ... ASC \| DESC;	sorts the result-set in ascending or descending order.	`SELECT * FROM Customers ORDER BY Country ASC, CustomerName DESC;`
INSERT INTO	INSERT INTO table_name [(column1, column2, column3, ...)] VALUES (value1, value2, value3, ...);	inserts new records in a table (if you are adding values for all the columns of the table, you do not need to specify the column names in the SQL query.)	`INSERT INTO Customers (CustomerName, ContactName, Address, City, PostalCode, Country) VALUES ('Cardinal', 'Tom B. Erichsen', 'Skagen 21', 'Stavanger', '4006', 'Norway');`
UPDATE	UPDATE table_name SET column1 = value1, column2 = value2, ... WHERE condition;	modifies the existing records in a table	`UPDATE Customers SET ContactName='Juan' WHERE Country='Mexico';`
UPDATE	UPDATE table_name SET column1 = value1, column2 = value2;	modifies the existing records in a whole table	`UPDATE Customers SET ContactName='Juan';`
DELETE	DELETE FROM table_name WHERE condition;	deletes existing records in a table	`DELETE FROM Customers WHERE CustomerName='Alfreds Futterkiste';`
DELETE	DELETE FROM table_name;	deletes all records in a table	`DELETE FROM Customers;`
DROP TABLE	DROP TABLE table_name;	deletes the table completely	`DROP TABLE Customers;`
AS	SELECT column_name AS alias_name FROM table_name;	creates alias for column	`SELECT CustomerID AS ID, CustomerName AS Customer FROM Customers;`
AS	SELECT column_name(s) FROM table_name AS alias_name;	creates alias for table	`SELECT * FROM Customers AS Persons;`
[INNER] JOIN	SELECT column_name(s) FROM table1 INNER JOIN table2 ON table1.column_name = table2.column_name;	returns records that have matching values in both tables	`SELECT Orders.OrderID, Customers.CustomerName, Orders.OrderDate FROM Orders INNER JOIN Customers ON Orders.CustomerID=Customers.CustomerID;`
LEFT JOIN	SELECT column_name(s) FROM table1 LEFT JOIN table2 ON table1.column_name = table2.column_name;	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.	`SELECT Customers.CustomerName, Orders.OrderID FROM Customers LEFT JOIN Orders ON Customers.CustomerID = Orders.CustomerID ORDER BY Customers.CustomerName;`
RIGHT JOIN	SELECT column_name(s) FROM table1 RIGHT JOIN table2 ON table1.column_name = table2.column_name;	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.	`SELECT Orders.OrderID, Employees.LastName, Employees.FirstName FROM Orders RIGHT JOIN Employees ON Orders.EmployeeID = Employees.EmployeeID ORDER BY Orders.OrderID;`
FULL [OUTER] JOIN	SELECT column_name(s) FROM table1 FULL OUTER JOIN table2 ON table1.column_name = table2.column_name WHERE condition;	returns all records when there is a match in left (table1) or right (table2) table records.	`SELECT Customers.CustomerName, Orders.OrderID FROM Customers FULL OUTER JOIN Orders ON Customers.CustomerID=Orders.CustomerID ORDER BY Customers.CustomerName;`
UNION	SELECT column_name(s) FROM table1 UNION SELECT column_name(s) FROM table2;	combines the result-set of two or more SELECT statements with the same number of columns, similar data types, and same column order	`SELECT City FROM Customers UNION SELECT City FROM Suppliers ORDER BY City;`
UNION ALL	SELECT column_name(s) FROM table1 UNION ALL SELECT column_name(s) FROM table2;	Similar to UNION, but allows duplicates	`SELECT City FROM Customers UNION ALL SELECT City FROM Suppliers ORDER BY City;`
GROUP BY	SELECT column_name(s) FROM table_name WHERE condition GROUP BY column_name(s) ORDER BY column_name(s);	often used with aggregate functions, group the result-set by one or more columns.	`SELECT Shippers.ShipperName, COUNT(Orders.OrderID) AS NumberOfOrders FROM Orders LEFT JOIN Shippers ON Orders.ShipperID = Shippers.ShipperID GROUP BY ShipperName;`
HAVING	SELECT column_name(s) FROM table_name WHERE condition GROUP BY column_name(s) HAVING condition ORDER BY column_name(s);	the same as WHERE but can use aggregate functions	`SELECT COUNT(CustomerID), Country FROM Customers GROUP BY Country HAVING COUNT(CustomerID) > 5 ORDER BY COUNT(CustomerID) DESC;`
ANY	SELECT column_name(s) FROM table_name WHERE column_name operator ANY (SELECT column_name FROM table_name WHERE condition);	returns TRUE if any of the subquery values meet the condition	`SELECT ProductName FROM Products WHERE ProductID = ANY (SELECT ProductID FROM OrderDetails WHERE Quantity = 10);`
ALL	SELECT column_name(s) FROM table_name WHERE column_name operator ALL (SELECT column_name FROM table_name WHERE condition);	returns TRUE if all of the subquery values meet the condition	`SELECT ProductName FROM Products WHERE ProductID = ALL (SELECT ProductID FROM OrderDetails WHERE Quantity = 10);`
SELECT INTO	SELECT column1, column2, column3, ... INTO newtable [IN externaldb] FROM oldtable WHERE condition;	copies data from one table into a new table	`SELECT * INTO CustomersBackup2017 IN 'Backup.mdb' FROM Customers;`
INSERT INTO SELECT	INSERT INTO table2 (column1, column2, column3, ...) SELECT column1, column2, column3, ... FROM table1 WHERE condition;	copies data from one table and inserts it into another table. The data types in source and target tables must match.	`INSERT INTO Customers (CustomerName, City, Country) SELECT SupplierName, City, Country FROM Suppliers;`
CASE	CASE WHEN condition1 THEN result1 WHEN condition2 THEN result2 WHEN conditionN THEN resultN ELSE result END;	goes through conditions and returns a value when the first condition is met	`SELECT OrderID, Quantity,` `CASE` `WHEN Quantity > 30 THEN 'The quantity is greater than 30'` `WHEN Quantity = 30 THEN 'The quantity is 30'` `ELSE 'The quantity is under 30'` `END AS QuantityText` `FROM OrderDetails;`
Operator	Description	Example
=	Equal	`WHERE CustomerID=1`
>	Greater than	`WHERE CustomerID>0`
<	Less than	`WHERE CustomerID<80`
>=	Greater than or equal	`WHERE CustomerID>=80`
<=	Less than or equal	`WHERE CustomerID<=80`
<>	Not equal. Note: In some versions of SQL this operator may be written as !=	`WHERE CustomerID<>80`
BETWEEN	Between a certain range	`WHERE CustomerID BETWEEN 80 AND 100`
LIKE	Search for a pattern	`WHERE CustomerName LIKE 'a%'`
IN	To specify multiple possible values for a column	`WHERE Country IN ('Germany', 'France', 'UK')`
AND	filter records based more than one condition where all are true	`WHERE Country = 'Spain' AND CustomerName LIKE 'G%'`
OR	filter records based more than one condition where at least one is true	`WHERE City = 'Berlin' OR CustomerName LIKE 'G%' OR Country = 'Norway'`
NOT	filter records based on a condition where it is not true	`WHERE CustomerID NOT BETWEEN 10 AND 60`
IS NULL	whether a field is null	`WHERE Address IS NULL`
IS NOT NULL	whether a field is not null	`WHERE Address IS NOT NULL`
EXISTS	returns TRUE if the subquery returns one or more records.	`SELECT SupplierName FROM Suppliers WHERE EXISTS (SELECT ProductName FROM Products WHERE Products.SupplierID = Suppliers.supplierID);`
ANY	allows you to perform a comparison between a single column value and a range of other values	`SELECT ProductName FROM Products WHERE ProductID = ANY (SELECT ProductID FROM OrderDetails WHERE Quantity = 10);`
Symbol	Meaning	Example
%	Represents zero or more characters	`SELECT * FROM Customers WHERE CustomerName LIKE '%mer%';`
_	Represents a single character	`SELECT * FROM Customers WHERE City LIKE 'L___on';`
[]	Represents any single character within the brackets (not available in PostgreSQL or MySQL)	`SELECT * FROM Customers WHERE CustomerName LIKE '[bsp]%';`
^	Represents any character not in the brackets (not available in PostgreSQL or MySQL)	`SELECT * FROM Customers WHERE CustomerName LIKE '[^bsp]%';`
-	Represents any single character within the specified range (not available in PostgreSQL or MySQL)	`SELECT * FROM Customers WHERE CustomerName LIKE '[a-f]%';`
Function Name	Description	Example
MIN()	returns the smallest value within the selected column	`SELECT MIN(Price) FROM Products;`
MAX()	returns the largest value within the selected column	`SELECT MIN(Price) AS SmallestPrice FROM Products;`
COUNT()	returns the number of rows in a set	`SELECT COUNT(ProductName) FROM Products;`
SUM()	returns the total sum of a numerical column	`SELECT SUM(Quantity) FROM OrderDetails WHERE ProductId = 11;`
AVG()	returns the average value of a numerical column	`SELECT AVG(Price) AS [average price], CategoryID FROM Products GROUP BY CategoryID;`
Constraint	Description
NOT NULL	Ensures that a column cannot have a NULL value
UNIQUE	Ensures that all values in a column are different
PRIMARY KEY	A combination of a NOT NULL and UNIQUE. Uniquely identifies each row in a table
FOREIGN KEY	Prevents actions that would destroy links between tables
CHECK	Ensures that the values in a column satisfies a specific condition
DEFAULT	Sets a default value for a column if no value is specified
CREATE INDEX	Used to create and retrieve data from the database very quickly
AUTO_INCREMENT	Allows a unique number to be generated automatically when a new record is inserted into a table.
Data type	Description
CHAR(size)	A FIXED length string (can contain letters, numbers, and special characters). The size parameter specifies the column length in characters - can be from 0 to 255. Default is 1
VARCHAR(size)	A VARIABLE length string (can contain letters, numbers, and special characters). The size parameter specifies the maximum string length in characters - can be from 0 to 65535
BINARY(size)	Equal to CHAR(), but stores binary byte strings. The size parameter specifies the column length in bytes. Default is 1
VARBINARY(size)	Equal to VARCHAR(), but stores binary byte strings. The size parameter specifies the maximum column length in bytes.
TINYBLOB	For BLOBs (Binary Large Objects). Max length: 255 bytes
TINYTEXT	Holds a string with a maximum length of 255 characters
TEXT(size)	Holds a string with a maximum length of 65,535 bytes
BLOB(size)	For BLOBs (Binary Large Objects). Holds up to 65,535 bytes of data
MEDIUMTEXT	Holds a string with a maximum length of 16,777,215 characters
MEDIUMBLOB	For BLOBs (Binary Large Objects). Holds up to 16,777,215 bytes of data
LONGTEXT	Holds a string with a maximum length of 4,294,967,295 characters
LONGBLOB	For BLOBs (Binary Large Objects). Holds up to 4,294,967,295 bytes of data
ENUM(val1, val2, val3, ...)	A string object that can have only one value, chosen from a list of possible values. You can list up to 65535 values in an ENUM list If a value is inserted that is not in the list, a blank value will be inserted. The values are sorted in the order you enter them
SET(val1, val2, val3, ...)	A string object that can have 0 or more values, chosen from a list of possible values. You can list up to 64 values in a SET list
Data type	Description
BIT(size)	A bit-value type. The number of bits per value is specified in size. The size parameter can hold a value from 1 to 64. The default value for size is 1.
TINYINT(size)	A very small integer. Signed range is from -128 to 127. Unsigned range is from 0 to 255. The size parameter specifies the maximum display width (which is 255)
BOOL	Zero is considered as false, nonzero values are considered as true.
BOOLEAN	Equal to BOOL
SMALLINT(size)	A small integer. Signed range is from -32768 to 32767. Unsigned range is from 0 to 65535. The size parameter specifies the maximum display width (which is 255)
MEDIUMINT(size)	A medium integer. Signed range is from -8388608 to 8388607. Unsigned range is from 0 to 16777215. The size parameter specifies the maximum display width (which is 255)
INT(size)	A medium integer. Signed range is from -2147483648 to 2147483647. Unsigned range is from 0 to 4294967295. The size parameter specifies the maximum display width (which is 255)
INTEGER(size)	Equal to INT(size)
BIGINT(size)	A large integer. Signed range is from -9223372036854775808 to 9223372036854775807. Unsigned range is from 0 to 18446744073709551615. The size parameter specifies the maximum display width (which is 255)
FLOAT(size, d)	A floating point number. The total number of digits is specified in size. The number of digits after the decimal point is specified in the d parameter. This syntax is deprecated in MySQL 8.0.17, and it will be removed in future MySQL versions
FLOAT(p)	A floating point number. MySQL uses the p value to determine whether to use FLOAT or DOUBLE for the resulting data type. If p is from 0 to 24, the data type becomes FLOAT(). If p is from 25 to 53, the data type becomes DOUBLE()
DOUBLE(size, d)	A normal-size floating point number. The total number of digits is specified in size. The number of digits after the decimal point is specified in the d parameter
DOUBLE PRECISION(size, d)
DECIMAL(size, d)	An exact fixed-point number. The total number of digits is specified in size. The number of digits after the decimal point is specified in the d parameter. The maximum number for size is 65. The maximum number for d is 30. The default value for size is 10. The default value for d is 0.
DEC(size, d)	Equal to DECIMAL(size,d)
Data type	Description
DATE	A date. Format: YYYY-MM-DD. The supported range is from '1000-01-01' to '9999-12-31'
DATETIME(fsp)	A date and time combination. Format: YYYY-MM-DD hh:mm:ss. The supported range is from '1000-01-01 00:00:00' to '9999-12-31 23:59:59'. Adding DEFAULT and ON UPDATE in the column definition to get automatic initialization and updating to the current date and time
TIMESTAMP(fsp)	A timestamp. TIMESTAMP values are stored as the number of seconds since the Unix epoch ('1970-01-01 00:00:00' UTC). Format: YYYY-MM-DD hh:mm:ss. The supported range is from '1970-01-01 00:00:01' UTC to '2038-01-09 03:14:07' UTC. Automatic initialization and updating to the current date and time can be specified using DEFAULT CURRENT_TIMESTAMP and ON UPDATE CURRENT_TIMESTAMP in the column definition
TIME(fsp)	A time. Format: hh:mm:ss. The supported range is from '-838:59:59' to '838:59:59'
YEAR	A year in four-digit format. Values allowed in four-digit format: 1901 to 2155, and 0000. MySQL 8.0 does not support year in two-digit format.
Data type	Description	Max char length	Storage
char(n)	Fixed-length non-Unicode character data (n must be between 1 and 8000)	8,000	n bytes (uses one byte for each character)
varchar(n)	Variable-length non-Unicode character data (n must be between 1 and 8000)	8,000	n bytes + 2 bytes
varchar(max)	Variable-length non-Unicode character data		up to 2 GB
nchar(n)	Fixed-length Unicode character data (n must be between 1 and 4000)	4,000	2 * n bytes (uses two bytes for each character)
nvarchar(n)	Variable-length Unicode character data (n must be between 1 and 4000)	4,000	2 * n bytes + 2 bytes (uses two bytes for each character)
nvarchar(max)	Variable-length Unicode character data		up to 2 GB
binary(n)	Fixed-length binary data (n must be between 1 and 8000)	8,000	n bytes
varbinary(n)	Variable-length binary data (n must be between 1 and 8000)	8,000	actual length of data entered + 2 bytes
varbinary(max)	Variable-length binary data	2GB
Data type	Description	Storage
bit	Integer that can be 0, 1, or NULL
tinyint	Allows whole numbers from 0 to 255	1 byte
smallint	Allows whole numbers between -32,768 and 32,767	2 bytes
int	Allows whole numbers between -2,147,483,648 and 2,147,483,647	4 bytes
bigint	Allows whole numbers between -9,223,372,036,854,775,808 and 9,223,372,036,854,775,807	8 bytes
decimal(p,s)	Fixed precision and scale numbers. Allows numbers from -10^38 +1 to 10^38 –1. The p parameter indicates the maximum total number of digits that can be stored (both to the left and to the right of the decimal point). p must be a value from 1 to 38. Default is 18. The s parameter indicates the maximum number of digits stored to the right of the decimal point. s must be a value from 0 to p. Default value is 0	5-17 bytes
numeric(p,s)	Fixed precision and scale numbers. Allows numbers from -10^38 +1 to 10^38 –1. The p parameter indicates the maximum total number of digits that can be stored (both to the left and to the right of the decimal point). p must be a value from 1 to 38. Default is 18. The s parameter indicates the maximum number of digits stored to the right of the decimal point. s must be a value from 0 to p. Default value is 0	5-17 bytes
smallmoney	Monetary data from -214,748.3648 to 214,748.3647	4 bytes
money	Monetary data from -922,337,203,685,477.5808 to 922,337,203,685,477.5807	8 bytes
float(n)	Floating precision number data from -1.79E + 308 to 1.79E + 308.The n parameter indicates whether the field should hold 4 or 8 bytes. float(24) holds a 4-byte field and float(53) holds an 8-byte field. Default value of n is 53.	4 or 8 bytes
real	Floating precision number data from -3.40E + 38 to 3.40E + 38	4 bytes