SQL Querying Databases

--DISTINCT returns distinct results

--SELECT DISTINCT column1, column2, ... FROM table_name;

--single line comments

/* multi
line
comments*/

--ORDER BY allows you to sort records, sorts records in

ascending order by default, use DESC to sort in descending
order.
ORDER BY column1, column2, ...

--INSERT INTO statement is used to insert new records in a

table.
--Two ways:
--First way specifies both column names and values to be
inserted
INSERT INTO table_name (column1, column2, …)
VALUES (value1, value2, ...);
--Second way does not however make sure that the order of the
values is the same order as the columns in the table.
INSERT INTO table_name
VALUES (value1, value2, ...);
--It is also possible to only insert data in specific columns
--Following SQL statement will insert a new record but only in
specific columns.
INSERT INTO Customers (CustomerName, City, Country)
VALUES (‘Cardinal’, ‘Stavanger’, ‘Norway’);
--any columns with no values will have a default value of NULL
unless It is specified that these columns cannot be NULL

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

--If a field in a table is optional, it is possible to insert
a new record or update a record without adding a value to this
field. Then the field will be saved with a NULL value
Use IS NULL and NOT NULL operators to test for NUL values
SELECT column_names FROM table_name WHERE column_name IS NULL;
SELECT column_names FROM table_name WHERE column_name IS NOT
NULL;

--The UPDATE statement is used to modify the existing records

in a table
UPDATE table_name SET column1 = value1, column2 = value2, ...
WHERE condition;
--The WHERE clause determines how many records will be updated
--If you omit the WHERE clause, all records will be updated
UPDATE Customers SET ContactName = ‘Jaun’;
--All contact names will have the value of ‘Jaun’

--The DELETE statement is used to delete existing records in a

table
DELETE FROM table_name WHERE condition;
--Can delete rows in a table without deleting the table
--table structure, attributes and indexes will be intact
DELETE FROM table_name;

--MySQL uses LIMIT to limit the amount of records returned

SELECT column_names FROM table_name WHERE condition LIMIT
number;
--MIN() function returns smallest value of selected column
SELECT MIN(column_name) FROM table_name WHERE condition;
--MAX() function returns largest value of selected column
SELECT MAX(column_name) FROM table_name WHERE condition;
--COUNT() function returns the number of rows that matches a
criteria
SELECT COUNT(column_name) FROM table_name WHERE condition;
--AVG() function returns the average value of a numeric column
SELECT AVG(column_name) FROM table_name WHERE condition;
--SUM() function returns the total sum of a numeric column
SELECT SUM(column_name) FROM table_name WHERE condition;

--LIKE operator is used in a WHERE clause to search for a

specified pattern in a column
-- % represents zero, one or multiple characters
-- _ represents a single character
SELECT column1, column2,... FROM table_name WHERE columnN LIKE
pattern;
‘a%’ Finds any values that start with “a”
‘%a’ Ends with “a”
‘%or%’ has “or” in any position
‘_r%’ have “r” in second position
‘a_%_%’ starts with “a” and there are at least 3 characters in
length
‘a%o’ start with “a” ends with “o”
‘[abc]%’ start with either ‘a’ or ‘b’ or ‘c’
‘[!abc]%’ does not start with either ‘a’ or ‘b’ or ‘c’

A wildcard character is used to substitute any other

characters in a string. Used with the LIKE operator, which is
used in a WHERE clause to search for a specified pattern in a
column.

--The IN operator allows you to specify multiple values in a

WHERE clause, it is a shorthand for multiple OR conditions
SELECT column_names FROM table_name WHERE column_name
IN (value1, value2, ...);
--or:
SELECT column_names FROM table_name WHERE column_name
IN (SELECT STATEMENT);

--The BETWEEN operator selects values within a given range.

The values can be numbers, text, or dates. It is inclusive,
begin and end values are included
SELECT column_names FROM table_name WHERE column_name BETWEEN
value1 AND value2;
--Can use NOT aswel:
SELECT column_names FROM table_name WHERE column_name NOT
BETWEEN value1 AND value2;
--can be done between numbers,strings,letters,dates

--SQL aliases are used to give a table, or a column in a

table, a temporary name. They are often used to make column or
table names more readable. They only exist for the duration of
the query
SELECT column_name AS alias_name FROM table_name;
SELECT column_name FROM table_name AS alias_name;
--A JOIN clause is used to combine rows from one or more
tables, based on a related column between them.
--(INNER) JOIN returns records that have matching values in
both tables
--LEFT (OUTER) JOIN returns all records from left table and
matched records from the right
--RIGHT (OUTER) JOIN returns all records from right table and
matched records from the left
--FULL (OUTER) JOIN returns all records when there is a match
in either left or right table

SELECT column_names FROM table1 JOIN table2 ON table1.column =

table2.column;
--can join multiple tables at once

--INNER JOIN keyword selects records that have matching values

in both table
SELECT column_names FROM table1 INNER JOIN table2 ON
table1.column_name = table2.column_name;

--LEFT JOIN keyword returns all records from the left table,
and matched records from the right table. The result is NULL
from the right side if there is no match.
SELECT column_names FROM table1 LEFT JOIN table2 ON
table1.column_name = table2.column_name;

--RIGHT JOIN keyword returns all records from the right table
and the matched records from the left table. The result is
NULL from the left side, when there is no match.
SELECT column_names FROM table1 RIGHT JOIN table2 ON
table1.column_name = table2.column_name;

--FULL OUTER JOIN keyword returns all records when there is a

match in either left or right table records.
SELECT column_names FROM table1 FULL OUTER JOIN table2 ON
table1.column_name = table2.column_name;

--A self JOIN is a regular join, but the table is joined with
itself.
SELECT column_names FROM table1 T1, table1 T2 WHERE condition;

--The UNION operator is used to combine the result-set of two

or more SELECT statements. Each SELECT statement within UNION
must have the same number of columns, the columns must also
have similar data types and the columns in each must also be
in the same order
SELECT column_names FROM table1
UNION
SELECT column_names FROM table2;
--The UNION operator selects only distinct values by default.
To allow duplicate values, use UNION ALL
SELECT column_names FROM table1
UNION ALL
SELECT column_names FROM table2;

--The GROUP BY statement is often used with aggregate

functions to group the result-set by one or more columns.
SELECT column_names FROM table_name WHERE condition
GROUP BY column_names
ORDER BY column_names;

--The HAVING clause was added to SQL because the WHERE keyword
could not be used with aggregate functions.
SELECT column_names FROM table_name WHERE condition GROUP BY
column_names HAVING condition ORDER BY column_name;

--The EXISTS operator is used to test for the existence of any

record in a subquery, It returns true if one or more records
are returned.
SELECT column_names FROM table_name WHERE EXISTS (SELECT
column_name FROM table_name WHERE condition);

--The ANY and ALL operators are used with a WHERE or HAVING
clause
--The ANY operator returns true if any of the subquery values
meet the condition
SELECT column_names FROM table_name WHERE column_name operator
ANY
(SELECT column_name FROM table_name WHERE condition);
--The ALL operator returns true if all of the subquery values
meet the condition
SELECT column_names FROM table_name WHERE column_name operator
ALL
(SELECT column_name FROM table_name WHERE condition);

--The SELECT INTO statement copies data from one table into a
new table.
--Copy all columns into a new table:
SELECT * INTO newtable [IN externaldb] FROM oldtable WHERE
condition;
--Copy some columns into a new table:
SELECT column1, column2, ... INTO newtable [IN externaldb]
FROM oldtable WHERE condition;

--The INSERT INTO SELECT statement copies data from one table
and inserts it into another table. It requires that data types
in source and target tables match. 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, ...) SELECT column1,
column2, ...
FROM table1 WHERE condition;

--The CASE statement goes through conditions and returns a

value when the first condition is met. So, once a condition is
true is 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 result
ELSE result
END;

--The MySQL IFNULL() function lets you return an alternative

value if an expression is NULL:
SELECT IFNULL(value_to_check, default_value) FROM table_name;
--We can also use the COALESCE() function:
SELECT COALESCE(value_to_check, default_value) FROM
table_name;

--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 call it to execute it. You can pass
parameters to a stored procedure, so that the stored procedure
can act based on the parameter values that is passed.
--Creates a stored procedure: (parameter is optional)
-CREATE PROCEDURE procedure_name @parameter_name data_type
AS sql_statement
GO;
--Executes a stored procedure:
EXEC procedure_name;
--Example:
CREATE PROCEDURE SelectALLCustomers @City nvarchar(30)
AS SELECT * FROM Customers WHERE City = @City
GO;

SQL database
--Data types:
/*
CHAR(size) fixed length string, 255 characters max
VARCHAR(size) variable length string, 255 characters max
TINYTEXT string with max length 255 characters
TEXT String with max length 65,535 characters
BLOB for Binary Large Objects, 65,535 bytes of data
MEDIUMTEXT string with max length 16,777,215
MEDIUMBLOB for BLOBs, 16,777,215 bytes of data
LONGTEXT string with max length 4,294,967,295
LONGBLOB for BLOBs, 4,294,967,295 bytes of data
ENUM(x,y,z,…) can enter list of possible values, 65535 list
size
Values stored in order you enter
SET Can store up to 64 items and more than one
choice

TINYINT(size) 1 byte -128 to 127 signed, 0 to 255 UNSIGNED

SMALLINT(size) 2 bytes -32768 to 32767 signed, 0 to 65535
UNSIGNED
MEDIUMINT(size)3 bytes 0 to 224-1 UNSIGNED
INT(size) 4 bytes 0 to 232-1 UNSIGNED
BIGINT(size) 5 bytes 0 to 240-1 UNSIGNED
FLOAT(size,d) small number with a floating decimal point
d = max number of digits to right of the
decimal point
DOUBLE(size,d) large number with a floating decimal point
DECIMAL(size,d)DOUBLE stored as a string, allows for fixed
decimal point

DATE() A date. FORMAT:YYYY-MM-DD

DATATIME() A data and time combination. YYYY-MM-DD
HH:MI:SS
TIMESTAMP() Values are stored as number of seconds since
Unix epoch
(‘1970-01-01 00:00:00’ UTC) YYYY-MM-DD
HH:MI:SS
TIME() HH:MI:SS
YEAR() A year in two-digit or four-digit format
1901 to 2155 or 70 to 69 representing 1970 to
2069
*/
--The CREATE DATABASE statement is used to create a new SQL
database.
CREATE DATABASE databasename;

--The DROP DATABASE statement is used to drop an existing SQL

database.
DROP DATABASE databasename;

--The CREATE TABLE statement is used to create a new table in

a database
CREATE TABLE table_name (
column1 datatype,
column2 datatype,
column3 datatype,
...
);
--The column parameters specify the names of the columns of
the table.
--The datatype parameter specifies the type of data the column
can hold
--A copy of an existing table can also be created using CREATE
TABLE
--The new table gets the same column definitions. All columns
or specific columns can be selected
--If you create a new table using an existing table, the new
table will be filled with the existing values from the old
table.
CREATE TABLE new_table_name AS
SELECT column1, column2, ...
FROM existing_table_name
WHERE ....;

--DROP TABLE statement is used to drop an existing table in a

database
DROP TABLE table_name;

--TRUNCATE TABLE statement is used to delete the data inside a

table, but not the table itself.
TRUNCATE TABLE table_name;

--ALTER TABLE statement is used to add, delete, or modify

columns in an existing table and can also be used to add and
drop various constraints on an existing table.
--To add a column:
ALTER TABLE table_name
ADD column_name datatype;
--To drop a column:
ALTER TABLE table_name
DROP COLUMN column_name;
--To change the data type of a column in a table:
ALTER TABLE table_name
MODIFY COLUMN column_name datatype;

--SQL constraints are used to specify rules for data in a

table
--Constraints can be specified when the table is created with
the CREATE TABLE or after the table has been created with the
ALTER TABLE statement.
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.
Constraints are used to limit the type of data that can go
into a table. This ensures the accuracy and reliability of the
data in the table. If there is any violation between the
constraint and the data action, the action is aborted.
Constraints can be column level or table level. Column level
constraints apply to a column, and table level constraints
apply to the whole table.
Following constraints are commonly used:

NOT NULL ensures that the column cannot be NULL

UNIQUE ensures that all values in a column are
different
PRIMARY KEY a combination of NOT NULL and UNIQUE, unique id
each row
FOREIGN KEY uniquely identifies a row/record in another
table
CHECK ensures that all values in a column satisfies a
condition
DEFAULT sets a default value when no value specified
INDEX used to create and retrieve data very quickly

Can have many UNIQUE constraints per table but only one
PRIMARY KEY
PRIMARY KEY may consists of single or multiple fields,
composite key
Table containing the foreign key is called the child table,
and the table containing the candidate key is called the
referenced or parent table. FOREIGN KEY is used to link two
table together. The FOREIGN KEY constraint is used to prevent
actions that would destroy links between tables. The FOREIGN
KEY constraint also prevents invalid data from being inserted
into the foreign key column, because it has to be one of the
value contained in the table it points to. CHECK constraint
used to limit value range that can be in a column.
*/
--MySQL:
CREATE TABLE Persons (
....
PersonID PRIMARY KEY (ID,LastName) --composite primary
key
Age int CHECK (Age>=18) DEFAULT 18
);
--For alter:
ALTER TABLE Persons
ADD PRIMARY KEY (ID);
--Foreign key
CREATE TABLE Orders (
....
FOREIGN KEY (PersonID) REFERENCES Persons(PersonID)
);
--To create an index
CREATE INDEX index_name ON table_name (col1, col2, ...);
--To create an unique index
CREATE UNIQUE INDEX index_name ON table_name (col1,
col2, ...);

--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.
Default start value is 1.
CREATE TABLE Persons (
ID int NOT NULL AUTO_INCREMENT=100; --will start at 100
);

/*
As long as your data contains only the date portion, your
queries will work as expected. However, if a time portion is
involved, it gets more complicated.
MySQL has the following data types for dates and times:
DATE YYYY-MM-DD
DATETIME YYYY-MM-DD HH:MI:SS
TIMESTAMP YYYY-MM-DD HH:MI:SS
YEAR YYYY or YY
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. Views always show up-to-date
data. The database engine recreates the data, using the view’s
SQL statement, every time the user queries a view.
*/
CREATE VIEW view_name AS SELECT col1, col2, ...
FROM table_name WHERE condition;
--A view can be updated using CREATE OR REPLACE VIEW command:
CREATE OR REPLACE VIEW view-name AS SELECT col1, col2, ...
FROM table_name WHERE condition;
--View can also be dropped
DROP VIEW view_name;

/*
SQL injection is a code injection technique that has the
potential to destroy your database. It is one of the most
common web hacking techniques. SQL injection is the placement
of malicious code in SQL statements via web page input.

SQL injection usually occurs when you ask a user for input,
like their uername/userid, and instead of a name/id, the user
gives you an SQL statement that you will unknowingly run on
your database.
The following example creates a SELECT statement by adding a
variable to a select string. The variable is fetched from user
input:
txtUserId = getRequestString(“UserId”);
txtSQL = “SELECT * FROM Users WHERE UserId = “ + txtUserId;

The original purpose of the code above is to create an SQL

statement to select a user, with a given user id. If there is
nothing to prevent a user from entering “wrong” input, the
user can enter “smart” input like:
‘105 OR 1=1’
Then the SQL statement will look like this:
SELECT * FROM Users WHERE UserId = 105 or 1=1;
This statement is valid and will return ALL rows from the
“Users” table, since ‘OR 1=1’ is always TRUE
If the users table contains names and passwords then the
statement would be like:
SELECT UserId, Name, Password FROM Users WHERE UserId = 105 or
1=1;
A hacker can get access to all the user names and passwords in
a database.

Example of a user login on a web site:

uName = getRequestString("username");
uPass = getRequestString("userpassword");
sql = 'SELECT * FROM Users WHERE Name ="' + uName + '" AND
Pass ="' + uPass + '"'
A hacker might get access to user names and passwords in a
database by simply inserting “ OR “”=” into the user name or
password text box.
The code at the server could make a valid statement like:
SELECT * FROM Users WHERE Name=”” or “”=”” AND Pass=”” or
“”=””
Returns all rows as OR “”=”” is always true

Most databases support batched SQL statement.

A batch of SQL statements is a group of two or more SQL
statements, separated by semicolons.
An example of an userID entry on a website:
txtUserId = getRequestString("UserId");
txtSQL = "SELECT * FROM Users WHERE UserId = " + txtUserId;
With the input as:
105; DROP TABLE Suppliers
Would make a valid statement that can remove a part of your
database:
SELECT * FROM Users WHERE UserId = 105; DROP TABLE Suppliers;

To protect a web site from SQL injection, you can use SQL
parameters. SQL parameters are values that are added to an SQL
query at execution time, in a controlled manner. Parameters
are represented in the SQL statement by a @ marker. The SQL
engine checks each parameter to ensure that it is correct for
its column and are treated literally, and not as part of the
SQL to be executed.
If you want your web site to be able to store and retrieve
data from a database, your web server should have access to a
database-system that uses the SQL language. You may need to
look for SQL hosting plans with your ISP. The most common SQL
hosting databases are MS SQL Server, Oracle, MySQL, and MS
Access.
MS SQL Server is popular for websites with high traffic, very
powerful, robust and full featured SQL database system.
Oracle is a popular database software for websites with high
traffic, powerful, robust, full featured SQL database system,
but is run by assholes who only want your money.
MySQL is a popular choice for web sites, powerful, robust and
full featured SQL database system. Inexpensive alternative to
expensive Mirosoft and Oracle solutions
Access is great for simple databases, not suited for high-
traffic, and not as powerful as the rest.

Bitwise operators:
& AND
| OR
^ exclusive OR

Compound operators
+= Add equals
-= Subtract equals
*= Multiple equals
/= Divide equals
%= Modulo equals
&= AND equals
^-= exclusive equals
|*= OR equals

See https://www.w3schools.com/sql/sql_ref_mysql.asp for full

list of functions
*/