SQL - sirdnt/quintessence GitHub Wiki

SQL stands for Structured Query Language.

Basic

CREATE TABLE

Syntax

  • size parameter specifies the maximum length of the table's column.
CREATE TABLE table_name
(
column_name1 data_type(size),
column_name2 data_type(size),
column_name3 data_type(size),
....
columnN data_type(size)
PRIMARY KEY(UserID)
);

Data type

  • INT -A normal-sized integer that can be signed or unsigned.

  • FLOAT(M,D) - A floating-point number that cannot be unsigned. You can optionally define the display length (M) and the number of decimals (D).

  • DOUBLE(M,D) - A double precision floating-point number that cannot be unsigned. You can optionally define the display length (M) and the number of decimals (D).

  • DATE - A date in YYYY-MM-DD format.

  • DATETIME - A date and time combination in YYYY-MM-DD HH:MM:SS format.

  • TIMESTAMP - A timestamp, calculated from midnight, January 1, 1970

  • TIME - Stores the time in HH:MM:SS format.

  • CHAR(M) - Fixed-length character string. Size is specified in parenthesis. Max 255 bytes.

  • VARCHAR(M) - Variable-length character string. Max size is specified in parenthesis.

  • BLOB - "Binary Large Objects" and are used to store large amounts of binary data, such as images or other types of files.

  • TEXT - Large amount of text data.

Sample create user table

CREATE TABLE Users (
 id int ,
 username varchar (30),
 password varchar(20),
 PRIMARY KEY  (id)
);

SQL Constraints

  • SQL constraints are used to specify rules for table data.
  • NOT NULL - Indicates that a column cannot contain any NULL value.
  • UNIQUE - Does not allow to insert a duplicate value in a column. The UNIQUE constraint maintains the uniqueness of a column in a table. More than one UNIQUE column can be used in a table.
  • PRIMARY KEY - Enforces the table to accept unique data for a specific column and this constraint create a unique index for accessing the table faster.
  • CHECK - Determines whether the value is valid or not from a logical expression.
  • DEFAULT - While inserting data into a table, if no value is supplied to a column, then the column gets the value set as DEFAULT.
// this means that the name column disallows NULL values.
name varchar(100) NOT NULL

Auto-increment allows a unique number to be generated when a new record is inserted into a table. By default, the starting value for AUTO_INCREMENT is 1, and it will increment by 1 for each new record.

UserID int NOT NULL AUTO_INCREMENT, PRIMARY KEY (UserID)

Usage constraints

CREATE TABLE Users (
id int NOT NULL AUTO_INCREMENT,
username varchar(40) NOT NULL, 
password varchar(10) NOT NULL,
PRIMARY KEY(id)
);

SELECT (or get data from database)

SHOW DATABASES; //command list databases manage by server
SHOW TABLES; //command list tables manage in current selected database
SHOW COLUMNS FROM <table_name>; //command list columns in a given table

SELECT <column_list> FROM <table_name>; // command select from table data specify in column list
SELECT FirstName, LastName, City  FROM customers;
SELECT *  FROM customers;

//Multiple Queries
SELECT FirstName FROM customers;
SELECT City FROM customers;

//Distinct ignore duplicate data
SELECT DISTINCT column_name1, column_name2 FROM table_name;

//LIMIT use for limit the result to retrieve just a subset of records
SELECT <column_list> FROM <table_name> LIMIT <number_of_records>;
SELECT ID, FirstName, LastName, City FROM customers LIMIT 5; //retrieve 5 records from customers table
//LIMIT by pickup
SELECT ID, FirstName, LastName, City FROM customers LIMIT 3,4; // pick up 4 records, starting from the third position:

// Fully Qualified Names
SELECT customers.City FROM customers;

// ORDER BY is used with SELECT to sort the returned data.
SELECT * FROM customers ORDER BY FirstName; //default order result by ascending
// ORDER BY multiple column
SELECT * FROM customers ORDER BY LastName, Age; // order by priority LastName > Age

INSERT

  • Make sure the order of the values is in the same order as the columns in the table.
INSERT INTO table_name
VALUES (value1, value2, value3,...);

Sample

INSERT INTO Employees 
VALUES (8, 'Anthony', 'Young', 35);
  • Alternatively, we can specify the table's column names in the INSERT INTO statement:
INSERT INTO table_name (column1, column2, column3, ...,columnN)  
VALUES (value1, value2, value3,...valueN);

Sample

INSERT INTO Employees (ID, FirstName, LastName, Age) 
VALUES (8, 'Anthony', 'Young', 35);

//Specific rows only so that age column will be insert with value = 0
INSERT INTO Employees (ID, FirstName, LastName) 
VALUES (9, 'Samuel', 'Clark');

UPDATE

//If we omit the WHERE clause, all records in the table will be updated!
UPDATE table_name
SET column1=value1, column2=value2, ...
WHERE condition;

Sample

// update salary for employee has ID = 1
UPDATE Employees SET Salary=5000 WHERE ID=1;

//Also we can update multiple columns
UPDATE Employees SET Salary=5000, FirstName='Robert' WHERE ID=1;

DELETE

DELETE FROM table_name
WHERE condition; 
// Remove 1 row from table where id = 1
DELETE FROM Employees WHERE ID=1;

// Remove rows in range with condition id > 5 && id < 10
DELETE  FROM people WHERE  id>5 AND  id<10;

Intermediate

WHERE clause is used to extract only those records that fulfill a specified criterion.

SELECT <column_list> FROM <table_name> WHERE condition;
SELECT * FROM customers WHERE ID = 7;

The following comparison operators can be used in the WHERE clause:

  • =
  • !=
  • >
  • <
  • <=
  • >=
  • BETWEEN inclusive range
SELECT EventId, EventName
FROM EventMaster
WHERE EventDate BETWEEN '10/15/2009' AND '10/18/2009'

Filtering with AND, OR

Logical Operators

  • AND TRUE if both expressions are TRUE
  • OR TRUE if ether expression is TRUE
  • IN TRUE if the operand is equal to one of a list of expressions
  • NOT return TRUE if expression is not TRUE
SELECT * FROM customers WHERE Age >= 30 AND Age <= 40;
SELECT * FROM customers WHERE City = 'New York' OR City = 'Chicago';
SELECT * FROM customers WHERE City = 'New York' AND (Age=30 OR Age=35); //combine AND + OR

// This is bad query
SELECT * FROM customers WHERE City = 'New York' OR City = 'Los Angeles' OR City = 'Chicago';
// The better is using In instead of OR in this case
SELECT * FROM customers WHERE City IN ('New York', 'Los Angeles', 'Chicago');

// NOT IN are using opposite with IN
SELECT * FROM customers WHERE City NOT IN ('New York', 'Los Angeles', 'Chicago'); // we don't want to select customers who's city in ('New York', 'Los Angeles', 'Chicago')

ALTER TABLE

The ALTER TABLE command is used to add, delete, or modify columns in an existing table. We would also use the ALTER TABLE command to add and drop various constraints on an existing table.
Adds a new column named DateOfBirth:

//All rows will have the default value in the newly added column, which, in this case, is NULL.
ALTER TABLE People ADD DateOfBirth date;

Delete a new column named DateOfBirth:

//The column, along with all of its data, will be completely removed from the table.
ALTER TABLE People DROP COLUMN DateOfBirth;

Rename the column called FirstName to name.

ALTER TABLE People CHANGE FirstName name varchar(100);

Rename the entire table using the RENAME command:

RENAME TABLE People TO Users;

Delete the entire table, use the DROP TABLE command:

//delete table named People
DROP TABLE People;

CONCAT Function

SELECT CONCAT(FirstName, ', ' , City) FROM customers; //-> result will be : "John, New York"
SELECT CONCAT(FirstName,', ', City) AS new_column FROM customers; // assign a custom name to the resulting column using the AS keyword

Arithmetic Operators

The Arithmetic operators include addition (+), subtraction (-), multiplication (*) and division (/).

// adds 500 to each employee's salary and selects the result
SELECT ID, FirstName, LastName, Salary+500 AS Salary FROM employees;

Functions

UPPER, LOWER, SQRT, AVG, SUM

// UPPER function converts all letters in the specified string to uppercase otherwise using LOWER.
SELECT FirstName, UPPER(LastName) AS LastName FROM employees;
// Calculate the square root of each Salary:
SELECT Salary, SQRT(Salary) FROM employees;
// AVG function returns the average value of a numeric column:
SELECT AVG(Salary) FROM employees;
// SUM of all employee salary
SELECT SUM(Salary) FROM employees;

Subqueries

A subquery is a query within another query. Let's consider an example. We might need the list of all employees whose salaries are greater than the average. First, calculate the average then compare with the average this can be accomplished by subqueries:

SELECT FirstName, Salary FROM employees 
WHERE  Salary > (SELECT AVG(Salary) FROM employees) 
ORDER BY Salary DESC;

Like Operator

The LIKE keyword is useful when specifying a search condition within WHERE clause.
SQL pattern matching enables you to use "_" to match any single character and "%" to match an arbitrary number of characters (including zero characters).

SELECT <column_name> FROM table_name WHERE <column_name> LIKE pattern;

Sample

//select employees whose FirstNames begin with the letter A:
SELECT * FROM employees WHERE FirstName LIKE 'A%';
//selects all employees with a LastName ending with the letter "s":
SELECT * FROM employees WHERE LastName LIKE '%s';

MIN Function

//to know the minimum salary among the employees.
SELECT MIN(Salary) AS Salary FROM employees;

Advance

Joining tables

//sample select id, name (from customers), name , amount (from orders) where order relate to customer by foreign key
SELECT customers.ID, customers.Name, orders.Name, orders.Amount FROM customers, orders
WHERE customers.ID=orders.Customer_ID ORDER BY customers.ID;

Give table nickname

SELECT ct.ID, ct.Name, ord.Name, ord.Amount FROM customers AS ct, orders AS ord
WHERE ct.ID=ord.Customer_ID ORDER BY ct.ID;

Type of join INNER JOIN, LEFT JOIN, RIGHT JOIN

//the ON keyword for specifying the inner join condition.
SELECT column_name(s) FROM table1 INNER JOIN table2 ON table1.column_name=table2.column_name;

The LEFT JOIN returns all rows from the left table, even if there are no matches in the right table.

SELECT table1.column1, table2.column2...
FROM table1 LEFT OUTER JOIN table2
ON table1.column_name = table2.column_name;

The RIGHT JOIN returns all rows from the right table, even if there are no matches in the left table.

SELECT table1.column1, table2.column2...
FROM table1 RIGHT OUTER JOIN table2
ON table1.column_name = table2.column_name;

Views

A VIEW is a virtual table that is based on the result-set of an SQL statement.Views allow us to:

  • Structure data in a way that users or classes of users find natural or intuitive.
  • Restrict access to the data in such a way that a user can see and (sometimes) modify exactly what they need and no more.
  • Summarize data from various tables and use it to generate reports.
CREATE VIEW view_name AS
SELECT column_name(s)
FROM table_name
WHERE condition;

Create a View:

// create a view that displays each employee's FirstName and Salary.
CREATE VIEW List AS SELECT FirstName, Salary FROM  Employees;

//query the List view as you would query an actual table.
SELECT * FROM List;

Update a View

CREATE OR REPLACE VIEW view_name AS
SELECT column_name(s)
FROM table_name
WHERE condition;
CREATE OR REPLACE VIEW List AS
SELECT FirstName, LastName, Salary
FROM  Employees;

Delete a View

// delete View with name List
DROP VIEW List;

UNION

UNION combines multiple datasets into a single dataset, and removes any existing duplicates. //To use UNION columns in queries must be the same

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

Sample

SELECT ID, FirstName, LastName, City FROM First
UNION
SELECT ID, FirstName, LastName, City FROM Second;

// If columns don't match exactly across all queries, we can use a NULL (or any other) value such as:

SELECT FirstName, LastName, Company FROM businessContacts
UNION
SELECT FirstName, LastName, **NULL** FROM otherContacts;

UNION ALL combines multiple datasets into one dataset, but does not remove duplicate rows.

SELECT ID, FirstName, LastName, City FROM First
UNION ALL
SELECT ID, FirstName, LastName, City FROM Second;

Note

  • SQL is case insensitive.
  • A single SQL statement can be placed on one or more text lines.
  • multiple SQL statements can be combined on a single text line.
  • White spaces and multiple lines are ignored in SQL.
  • Using "fully qualified name" (pattern table.column) is especially useful when working with multiple tables that may share the same column names.
  • When working with text columns, surround any text that appears in the statement with single quotation marks (').
    SELECT * FROM customers WHERE City = 'New York';
  • The OUTER keyword is optional, and can be omitted.
  • Views are being updated dynamically
⚠️ **GitHub.com Fallback** ⚠️