SQL Statement

Most of the actions you need to perform on a database are done with SQL statements.

• CREATE DATABASE: statement is used to create a new SQL database.

CREATE DATABASE databasename;

• DROP: The DROP DATABASE statement is used to drop an existing SQL database.

DROP DATABASE databasename;

• BACKUP: The BACKUP DATABASE statement is used in SQL Server to create a full back up of an existing SQL database.

BACKUP DATABASE databasename
TO DISK = 'filepath';

• CREATE TABLE: The CREATE TABLE statement is used to create a new table in a database.

CREATE TABLE table_name (
    column1 datatype,
    column2 datatype,
    column3 datatype,
   ....
);

• DROP TABLE: The DROP TABLE statement is used to drop an existing table in a database.

DROP TABLE table_name;

• ALTER TABLE: The ALTER TABLE statement is used to add, delete, or modify columns in an existing table.
  The ALTER TABLE statement is also used to add and drop various constraints on an existing table.

• ALTER TABLE - ADD Column: To add a column in a table, use the following syntax:
  

ALTER TABLE table_name
ADD column_name datatype;

• ALTER TABLE - DROP COLUMN: To delete a column in a table, use the following syntax (notice that some database systems don't allow deleting a column):

ALTER TABLE table_name
DROP COLUMN column_name;

• ALTER TABLE - RENAME COLUMN: To rename a column in a table, use the following syntax:

ALTER TABLE table_name
RENAME COLUMN old_name to new_name;

• ALTER TABLE - ALTER/MODIFY DATATYPE: To change the data type of a column in a table, use the following syntax:

ALTER TABLE table_name
ALTER COLUMN column_name datatype;

• NOT NULL: The NOT NULL constraint enforces a column to NOT accept NULL values.
• This enforces a field to always contain a value, which means that you cannot insert a new record, or update a record without adding a value to this field.

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

• SQL NOT NULL on ALTER TABLE: To create a NOT NULL constraint on the "Age" column when the "Persons" table is already created, use the following SQL:

ALTER TABLE Persons
ALTER COLUMN Age int NOT NULL;

• UNIQUE: The UNIQUE constraint ensures that all values in a column are different.
• Both the UNIQUE and PRIMARY KEY constraints provide a guarantee for uniqueness for a column or set of columns.
• A PRIMARY KEY constraint automatically has a UNIQUE constraint.
• However, you can have many UNIQUE constraints per table, but only one PRIMARY KEY constraint per table.

CREATE TABLE Persons (
    ID int NOT NULL UNIQUE,
    LastName varchar(255) NOT NULL,
    FirstName varchar(255),
    Age int
);

• SQL UNIQUE Constraint on ALTER TABLE: To create a UNIQUE constraint on the "ID" column when the table is already created, use the following SQL:

ALTER TABLE Persons
ADD UNIQUE (ID);

• PRIMER KEY: The PRIMARY KEY constraint uniquely identifies each record in a table.
• Primary keys must contain UNIQUE values, and cannot contain NULL values.
• A table can have only ONE primary key; and in the table, this primary key can consist of single or multiple columns (fields).

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

• FOREIGN KEY: The FOREIGN KEY constraint is used to prevent actions that would destroy links between tables.
• A FOREIGN KEY is a field (or collection of fields) in one table, that refers to the PRIMARY KEY in another table.
• The table with the foreign key is called the child table, and the table with the primary key is called the referenced or parent table.

CREATE TABLE Orders (
    OrderID int NOT NULL,
    OrderNumber int NOT NULL,
    PersonID int,
    PRIMARY KEY (OrderID),
    FOREIGN KEY (PersonID) REFERENCES Persons(PersonID)
);

• CHECK: The CHECK constraint is used to limit the value range that can be placed in a column.
• If you define a CHECK constraint on a column it will allow only certain values for this column.
• If you define a CHECK constraint on a table it can limit the values in certain columns based on values in other columns in the row.

CREATE TABLE Persons (
    ID int NOT NULL,
    LastName varchar(255) NOT NULL,
    FirstName varchar(255),
    Age int,
    CHECK (Age>=18)
);

• DEFAULT: The DEFAULT constraint is used to set a default value for a column.
• The default value will be added to all new records, if no other value is specified.

CREATE TABLE Persons (
    ID int NOT NULL,
    LastName varchar(255) NOT NULL,
    FirstName varchar(255),
    Age int,
    City varchar(255) DEFAULT 'Sandnes'
);

• INDEX: The CREATE INDEX statement is used to create indexes in tables.
• Indexes are used to retrieve data from the database more quickly than otherwise. The users cannot see the indexes, they are just used to speed up searches/queries.

CREATE INDEX index_name
ON table_name (column1, column2, ...);
OR
CREATE UNIQUE INDEX index_name
ON table_name (column1, column2, ...);

• AUTO INCREMENT: Auto-increment allows a unique number to be generated automatically when a new record is inserted into a table.
• Often this is the primary key field that we would like to be created automatically every time a new record is inserted.

CREATE TABLE Persons (
    Personid int NOT NULL AUTO_INCREMENT,
    LastName varchar(255) NOT NULL,
    FirstName varchar(255),
    Age int,
    PRIMARY KEY (Personid)
);

• DATES: MySQL 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
• TIMESTAMP - format: YYYY-MM-DD HH:MI:SS

• YEAR - format YYYY or YY

• 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
  

SELECT * FROM Orders WHERE OrderDate='2008-11-11'

• VIEWS: In SQL, a view is a virtual table based on the result-set of an SQL statement.
• A view contains rows and columns, just like a real table. The fields in a view are fields from one or more real tables in the database.
• You can add SQL statements and functions to a view and present the data as if the data were coming from one single table.
• A view is created with the CREATE VIEW statement.

CREATE VIEW Syntax
CREATE VIEW view_name AS
SELECT column1, column2, ...
FROM table_name
WHERE condition;

• Updating a View: can be updated with the CREATE OR REPLACE VIEW statement.

CREATE OR REPLACE VIEW view_name AS
SELECT column1, column2, ...
FROM table_name
WHERE condition;

• Dropping a View: A view is deleted with the DROP VIEW statement.

DROP VIEW view_name;

• SELECT: is used to select data from a database.

SELECT CustomerName, City FROM Customers;

• SELECT DISTINCT: is used to return only distinct (different) values.

SELECT DISTINCT Country FROM Customers;

• WHERE: It is used to extract only those records that fulfill a specified condition.

SELECT column1, FROM table_name WHERE condition;

• ORDER BY: is used to sort the result-set in ascending or descending order.

SELECT * FROM Products
ORDER BY Price;

• AND: The AND operator is used to filter records based on more than one condition, like if you want to return all customers from Spain that starts with the letter 'G':

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

• OR: The OR operator is used to filter records based on more than one condition, like if you want to return all customers from Germany but also those from Spain:

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

• NOT: The NOT operator is used in combination with other operators to give the opposite result, also called the negative result.

SELECT column1, column2, ...
FROM table_name
WHERE NOT condition;

• INSERT INTO: is used to insert new records in a table.
• It is possible to write the INSERT INTO statement in two ways:
• 1. Specify both the column names and the values to be inserted:

INSERT INTO table_name (column1, column2, column3, ...)
VALUES (value1, value2, value3, ...);

• 2. 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. However, make sure the order of the values is in the same order as the columns in the table. Here, the INSERT INTO syntax would be as follows:

INSERT INTO table_name
VALUES (value1, value2, value3, ...);

• NULL VALUES: It is not possible to test for NULL values with comparison operators, such as =, <, or <>.
• We will have to use the IS NULL and IS NOT NULL operators instead. IS NULL Syntax

SELECT column_names
FROM table_name
WHERE column_name IS NULL;

IS NOT NULL Syntax

SELECT column_names
FROM table_name
WHERE column_name IS NOT NULL;

• UPDATE: is used to modify the existing records in a table.

UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition;

• DELETE: is used to delete existing records in a table.

DELETE FROM table_name WHERE condition;

• MIN: returns the smallest value of the selected column.

SELECT MIN(column_name)
FROM table_name
WHERE condition;

• MAX: returns the largest value of the selected column.

SELECT MAX(column_name)
FROM table_name
WHERE condition;

• COUNT: returns the number of rows that matches a specified criterion.

SELECT COUNT(column_name)
FROM table_name
WHERE condition;

• SUM: returns the total sum of a numeric column.

SELECT SUM(column_name)
FROM table_name
WHERE condition;

• AVG: returns the average value of a numeric column.

SELECT AVG(column_name)
FROM table_name
WHERE condition;

• LIKE: The LIKE operator is used in a WHERE clause to search for a specified pattern in a column.
• There are two wildcards often used in conjunction with the LIKE operator:
• A wildcard character is used to substitute one or more characters in a string.

SELECT column1, column2, ...
FROM table_name
WHERE columnN LIKE pattern;

• IN: The IN operator allows you to specify multiple values in a WHERE clause.
• The IN operator is a shorthand for multiple OR conditions.

SELECT column_name(s)
FROM table_name
WHERE column_name IN (value1, value2, ...);

• BETWEEN: The BETWEEN operator selects values within a given range. The values can be numbers, text, or dates.
• The BETWEEN operator is inclusive: begin and end values are included.

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

• ALIASES (AS): SQL aliases are used to give a table, or a column in a table, a temporary name.
• Aliases are often used to make column names more readable.
• An alias only exists for the duration of that query.
• An alias is created with the AS keyword.

SELECT column_name AS alias_name
FROM table_name;
SELECT column_name(s)
FROM table_name AS alias_name;

• INNER JOIN: The INNER JOIN keyword selects records that have matching values in both tables.
• SELECT column_name(s)

FROM table1
INNER JOIN table2
ON table1.column_name = table2.column_name;

• LEFT JOIN: 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.

SELECT column_name(s)
FROM table1
LEFT JOIN table2
ON table1.column_name = table2.column_name;

• RIGHT JOIN: 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.

SELECT column_name(s)
FROM table1
RIGHT JOIN table2
ON table1.column_name = table2.column_name;

• FULL JOIN: The FULL OUTER JOIN keyword returns all records when there is a match in left (table1) or right (table2) table records.

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

• SELF JOIN: A self join is a regular join, but the table is joined with itself.

SELECT column_name(s)
FROM table1 T1, table1 T2
WHERE condition;

• UNION: The UNION operator is used to combine the result-set of two or more SELECT statements.
• Every SELECT statement within UNION must have the same number of columns
• The columns must also have similar data types
• The columns in every SELECT statement must also be in the same order

SELECT column_name(s) FROM table1
UNION
SELECT column_name(s) FROM table2;

• UNION ALL: The UNION operator selects only distinct values by default. To allow duplicate values, use UNION ALL:

SELECT column_name(s) FROM table1
UNION ALL
SELECT column_name(s) FROM table2;

• GROUP BY: The GROUP BY statement groups rows that have the same values into summary rows, like "find the number of customers in each country".

SELECT column_name(s)
FROM table_name
WHERE condition
GROUP BY column_name(s)
ORDER BY column_name(s);

• HAVING: The HAVING clause was added to SQL because the WHERE keyword cannot be used with aggregate functions.

SELECT column_name(s)
FROM table_name
WHERE condition
GROUP BY column_name(s)
HAVING condition
ORDER BY column_name(s);

• EXISTS: The EXISTS operator is used to test for the existence of any record in a subquery.
• The EXISTS operator returns TRUE if the subquery returns one or more records.

SELECT column_name(s)
FROM table_name
WHERE EXISTS
(SELECT column_name FROM table_name WHERE condition);

• ANY: returns a boolean value as a result and returns TRUE if ANY of the subquery values meet the condition.
• ANY means that the condition will be true if the operation is true for any of the values in the range.

SELECT column_name(s)
FROM table_name
WHERE column_name operator ANY
  (SELECT column_name
  FROM table_name
  WHERE condition);

• ALL: The ALL operator:

1. returns a boolean value as a result
2. returns TRUE if ALL of the subquery values meet the condition
3. is used with SELECT, WHERE and HAVING statements
4. ALL means that the condition will be true only if the operation is true for all values in the range.

SELECT ALL column_name(s)
FROM table_name
WHERE condition;

• SELECT INTO: The SELECT INTO statement copies data from one table 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;

• INSERT INTO SELECT: The INSERT INTO SELECT statement copies data from one table and inserts it into another table.
• The INSERT INTO SELECT statement requires that the data types in source and target tables match.
• Note: The existing records in the target table are unaffected.
• 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: The CASE expression goes through conditions and returns a value when the first condition is met (like an if-then-else statement). So, once a condition is true, it will stop reading and return the result. If no conditions are true, it returns the value in the ELSE clause.
• If there is no ELSE part and no conditions are true, it returns NULL.

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

• STORED PROCEDURE: A stored procedure is a prepared SQL code that you can save, so the code can be reused over and over again.
• So if you have an SQL query that you write over and over again, save it as a stored procedure, and then just call it to execute it.
• You can also pass parameters to a stored procedure, so that the stored procedure can act based on the parameter value(s) that is passed.

CREATE PROCEDURE procedure_name
AS
sql_statement
GO;
Execute a Stored Procedure
EXEC procedure_name;

• COMMENTS: Single line comments start with --.
• Any text between -- and the end of the line will be ignored (will not be executed).

-- Select all:
SELECT * FROM Customers;