MySQL

The SQL CREATE DATABASE StatementThe CREATE DATABASE statement is used to create a new SQL database.CREATE DATABASE databasename;Example:CREATE DATABASE marinaDB;

The SQL CREATE TABLE StatementThe CREATE TABLE statement is used to create a new table in a database.CREATE TABLE table_name (   column1 datatype,   column2 datatype,   column3 datatype,  ....);Example:CREATE TABLE Marina(   MarinaID int,   LastName varchar(255),   FirstName varchar(255),   Address varchar(255),   City varchar(255));

SQL Create ConstraintsConstraints can be specified when the table is created with the CREATE TABLE statement, or after the table is created with the ALTER TABLE statement.CREATE TABLE Marina (   column1 datatype constraint,   column2 datatype constraint,   column3 datatype constraint,   ....);The following constraints are commonly used in SQL:NOT NULL - Ensures that a column cannot have a NULL valueUNIQUE - Ensures that all values in a column are differentPRIMARY KEY - A combination of a NOT NULL and UNIQUE. Uniquely identifies each row in a tableFOREIGN KEY - Prevents actions that would destroy links between tablesCHECK - Ensures that the values in a column satisfies a specific conditionDEFAULT - Sets a default value for a column if no value is specifiedCREATE INDEX - Used to create and retrieve data from the database very quickly

SQL NOT NULL on CREATE TABLEThe following SQL ensures that the "ID", "LastName", and "FirstName" columns will NOT accept NULL values when the "Marina" table is created:Example:CREATE TABLE Marina(   ID int NOT NULL,    LastName varchar(255) NOT NULL,   FirstName varchar(255) NOT NULL,   Age int);

SQL UNIQUE Constraint on CREATE TABLEThe following SQL creates a UNIQUE constraint on the "ID" column when the "Marina" table is created:Example:CREATE TABLE Marina(   ID int NOT NULL UNIQUE,   LastName varchar(255) NOT NULL,   FirstName varchar(255),   Age int);

SQL PRIMARY KEY on CREATE TABLEThe following SQL creates a PRIMARY KEY on the "ID" column when the "Marina" table is created:Example:CREATE TABLE Marina(   ID int NOT NULL,   LastName varchar(255) NOT NULL,   FirstName varchar(255),   Age int,    PRIMARY KEY (ID));

SQL FOREIGN KEY on CREATE TABLEThe following SQL creates a FOREIGN KEY on the "MarinaID" column when the "Orders" table is created:Example:CREATE TABLE MarinaT(   MarinaID int NOT NULL,   LastName varchar(255) NOT NULL,   FirstName varchar(255),   Age int,    PRIMARY KEY (ID));CREATE TABLE Orders (   OrderID int NOT NULL,   OrderNumber int NOT NULL,   PersonID int,    PRIMARY KEY (OrderID),    FOREIGN KEY (MarinaID) REFERENCES MarinaT(MarinaID));

SQL CHECK on CREATE TABLEThe following SQL creates a CHECK constraint on the "Age" column when the "Marina" table is created. The CHECK constraint ensures that the age of a person must be 18, or older:Example:CREATE TABLE Marina(   ID int NOT NULL,   LastName varchar(255) NOT NULL,   FirstName varchar(255),   Age int,    CHECK (Age>=18));

SQL DEFAULT on CREATE TABLEThe following SQL sets a DEFAULT value for the "City" column when the "Marina" table is created:Example:CREATE TABLE Marina(   ID int NOT NULL,   LastName varchar(255) NOT NULL,   FirstName varchar(255),   Age int,   City varchar(255) DEFAULT 'Sandnes');

SQL ALTER TABLE StatementThe 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 table_nameADD column_name datatype;Example:ALTER TABLE CustomersADD Email varchar(255);

The SQL DROP TABLE StatementThe DROP TABLE statement is used to drop an existing table in a database.DROP TABLE table_name;Example:DROP TABLE Marina;

The SQL DROP DATABASE StatementThe DROP DATABASE statement is used to drop an existing SQL database.DROP DATABASE databasename;Example:DROP DATABASE marinaDB;

The SQL SELECT StatementThe SELECT statement is used to select data from a database.SELECT column1, column2, ...FROM table_name;Example:Return data from the Customers table:SELECT CustomerName, City FROM Customers;

The SQL SELECT DISTINCT StatementThe SELECT DISTINCT statement is used to return only distinct (different) values.SELECT DISTINCT column1, column2, ...FROM table_name;Example:Select all the different countries from the "Customers" table:SELECT DISTINCT Country FROM Customers;

The SQL WHERE ClauseThe WHERE clause is used to filter records.It is used to extract only those records that fulfill a specified condition.SELECT column1, column2, ...FROM table_nameWHERE condition;Example:Select all customers from Mexico:SELECT * FROM CustomersWHERE Country='Mexico';Operators:The following operators can be used in the WHERE clause:=: Equal>: Greater than<: Less than>=: Greater than or equal<=: Less than or equal<>: Not equal.BETWEEN: Between a certain rangeLIKE: Search for a patternIN: To specify multiple possible values for a columnExample:Select all customers with a CustomerID greater than 80:SELECT * FROM CustomersWHERE CustomerID > 80;

The SQL AND OperatorThe WHERE clause can contain one or many AND operators.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_nameWHERE condition1 AND condition2 AND condition3 ...;ExampleSelect all customers from Spain that starts with the letter 'G':SELECT *FROM CustomersWHERE Country = 'Spain' AND CustomerName LIKE 'G%';

The SQL LIKE OperatorThe 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: The percent sign % represents zero, one, or multiple characters The underscore sign _ represents one, single characterLIKE SyntaxSELECT column1, column2, ...FROM table_nameWHERE columnN LIKE pattern;Example:Select all customers that starts with the letter "a":SELECT * FROM CustomersWHERE CustomerName LIKE 'a%';

SQL Wildcard CharactersA wildcard character is used to substitute one or more characters in a string.Wildcard characters are used with the LIKE operator. The LIKE operator is used in a WHERE clause to search for a specified pattern in a column.Wildcard Characters%: Represents zero or more characters_: Represents a single character[]: Represents any single character within the brackets*-:Represents any single character within the specified range Example:Return all customers that starts with the letter 'a':SELECT * FROM CustomersWHERE CustomerName LIKE 'a%';

ESCAPE clause in LIKE operatorThe ESCAPE clause is supported in the LIKE operator to indicate the escape character. ESCAPE characters are used in the pattern string to indicate that any wildcard character that occurs after the escape character in the pattern string should be treated as a regular character.The default escape character is backslash (\)SyntaxLIKE 'pattern string' ESCAPE 'c' ExampleSELECT * FROM emp WHERE ENAME LIKE 'J%$_%' ESCAPE '$'; This matches all records with names that start with letter ’J’ and have the ’_’ character in them.SELECT * FROM emp WHERE ENAME LIKE 'JOE$_JOHN' ESCAPE '$'; This matches only records with name ’JOE_JOHN’.

The SQL OR OperatorThe WHERE clause can contain one or more OR operators.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_nameWHERE condition1 OR condition2 OR condition3 ...;Example:Select all customers from Germany or Spain:SELECT *FROM CustomersWHERE Country = 'Germany' OR Country = 'Spain';

The NOT OperatorThe NOT operator is used in combination with other operators to give the opposite result, also called the negative result.In the select statement below we want to return all customers that are NOT from Spain:SELECT column1, column2, ...FROM table_nameWHERE NOT condition;Example:Select only the customers that are NOT from Spain:SELECT * FROM CustomersWHERE NOT Country = 'Spain';

NULL ValuesIt 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 SyntaxSELECT column_namesFROM table_nameWHERE column_name IS NULL;IS NOT NULL SyntaxSELECT column_namesFROM table_nameWHERE column_name IS NOT NULL;

The SQL IN OperatorThe IN operator allows you to specify multiple values in a WHERE clause.The IN operator is a shorthand for multiple OR conditions.SyntaxSELECT column_name(s)FROM table_nameWHExample:Return all customers from 'Germany', 'France', or 'UK'SELECT * FROM CustomersWHERE Country IN ('Germany', 'France', 'UK');ERE column_name IN (value1, value2, ...);

The SQL BETWEEN OperatorThe 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. SyntaxSELECT column_name(s)FROM table_nameWHERE column_name BETWEEN value1 AND value2;Example:Selects all products with a price between 10 and 20:SELECT * FROM Products

The SQL EXISTS OperatorThe 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.EXISTS SyntaxSELECT column_name(s)FROM table_nameWHERE EXISTS(SELECT column_name FROM table_name WHERE condition);Example:SELECT SupplierNameFROM SuppliersWHERE EXISTS (SELECT ProductName FROM Products WHERE Products.SupplierID = Suppliers.supplierID AND Price < 20);

The SQL ORDER BYThe ORDER BY keyword is used to sort the result-set in ascending(ASC) or descending(DESC) order.SELECT column1, column2, ...FROM table_nameORDER BY column1, column2, ... ASC|DESC;Example:Sort the products by price:SELECT * FROM ProductsORDER BY Price;

SQL AliasesSQL 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.SyntaxWhen alias is used on column:SELECT column_name AS alias_nameFROM table_name;When alias is used on table:SELECT column_name(s)FROM table_name AS alias_name;Example:SELECT CustomerID AS IDFROM Customers;

The SQL SELECT TOP ClauseThe SELECT TOP clause is used to specify the number of records to return.The SELECT TOP clause is useful on large tables with thousands of records. Returning a large number of records can impact performance.Example:Select only the first 3 records of the Customers table:SELECT TOP 3 * FROM Customers;

The SQL ANY and ALL OperatorsThe ANY and ALL operators allow you to perform a comparison between a single column value and a range of other values.The SQL ANY OperatorThe ANY operator:returns a boolean value as a resultreturns TRUE if ANY of the subquery values meet the conditionANY means that the condition will be true if the operation is true for any of the values in the range.ANY SyntaxSELECT column_name(s)FROM table_nameWHERE column_name operator ANY (SELECT column_name  FROM table_name  WHERE condition);ALL Syntax With SELECTSELECT ALL column_name(s)FROM table_nameWHERE condition;Example ANY:SELECT ProductNameFROM ProductsWHERE ProductID = ANY (SELECT ProductID  FROM OrderDetails  WHERE Quantity = 10);Example ALL:SELECT ALL ProductNameFROM ProductsWHERE TRUE;

SQL Aggregate FunctionsAn aggregate function is a function that performs a calculation on a set of values, and returns a single value.Aggregate functions are often used with the GROUP BY clause of the SELECT statement. The GROUP BY clause splits the result-set into groups of values and the aggregate function can be used to return a single value for each group.The most commonly used SQL aggregate functions are:MIN() - returns the smallest value within the selected columnMAX() - returns the largest value within the selected columnCOUNT() - returns the number of rows in a setSUM() - returns the total sum of a numerical columnAVG() - returns the average value of a numerical column

The SQL MIN() and MAX() FunctionsThe MIN() function returns the smallest value of the selected column.The MAX() function returns the largest value of the selected column.SyntaxSELECT MIN(column_name)FROM table_nameWHERE condition;SELECT MAX(column_name)FROM table_nameWHERE condition;MIN Example:Find the lowest price in the Price column:SELECT MIN(Price)FROM Products;MAX Example:Find the highest price in the Price column:SELECT MAX(Price)FROM Products;

The SQL COUNT() FunctionThe COUNT() function returns the number of rows that matches a specified criterion.SyntaxSELECT COUNT(column_name)FROM table_nameWHERE conExample:Find the total number of rows in the Products table:SELECT COUNT(*)FROM Products;dition;

The SQL SUM() FunctionThe SUM() function returns the total sum of a numeric column.SyntaxSELECT SUM(column_name)FROM table_nameWHERE condition;Example:Return the sum of all Quantity fields in the OrderDetails table:SELECT SUM(Quantity)FROM OrderDetails;

The SQL AVG() FunctionThe AVG() function returns the average value of a numeric column.SyntaxSELECT AVG(column_name)FROM table_nameWHERE condition;Example:Find the average price of all products:SELECT AVG(Price)FROM Products;

The SQL GROUP BY StatementThe GROUP BY statement groups rows that have the same values into summary rows, like "find the number of customers in each country".The GROUP BY statement is often used with aggregate functions (COUNT(), MAX(), MIN(), SUM(), AVG()) to group the result-set by one or more columns.GROUP BY SyntaxSELECT column_name(s)FROM table_nameWHERE conditionGROUP BY column_name(s)ORDER BY column_name(s);Example:SELECT COUNT(CustomerID), CountryFROM CustomersGROUP BY Country;

The SQL HAVING ClauseThe HAVING clause was added to SQL because the WHERE keyword cannot be used with aggregate functions.HAVING SyntaxSELECT column_name(s)FROM table_nameWHERE conditionGROUP BY column_name(s)HAVING conditionORDER BY column_name(s);Example:SELECT COUNT(CustomerID), CountryFROM CustomersGROUP BY CountryHAVING COUNT(CustomerID) > 5;

The SQL INSERT INTO StatementThe INSERT INTO statement is used to insert new records in a table.INSERT INTO table_name (column1, column2, column3, ...)VALUES (value1, value2, value3, ...);Example:INSERT INTO Customers (CustomerName, ContactName, Address, City, PostalCode, Country)VALUES ('Cardinal', 'Tom B. Erichsen', 'Skagen 21', 'Stavanger', '4006', 'Norway');Insert Multiple RowsIt is also possible to insert multiple rows in one statement.To insert multiple rows of data, we use the same INSERT INTO statement, but with multiple values:Example:INSERT INTO Customers (CustomerName, ContactName, Address, City, PostalCode, Country)VALUES('Cardinal', 'Tom B. Erichsen', 'Skagen 21', 'Stavanger', '4006', 'Norway'),('Greasy Burger', 'Per Olsen', 'Gateveien 15', 'Sandnes', '4306', 'Norway'),('Tasty Tee', 'Finn Egan', 'Streetroad 19B', 'Liverpool', 'L1 0AA', 'UK');

The SQL UPDATE StatementThe UPDATE statement is used to modify the existing records in a table.UPDATE table_nameSET column1 = value1, column2 = value2, ...WHERE condition;Example:UPDATE CustomersSET ContactName = 'Alfred Schmidt', City= 'Frankfurt'WHERE CustomerID = 1;

The SQL DELETE StatementThe DELETE statement is used to delete existing records in a table.DELETE SyntaxDELETE FROM table_name WHERE condition;Example:DELETE FROM Customers WHERE CustomerName='Alfreds Futterkiste';