SQL Commands:

SELECT - extracts data from a database

UPDATE - updates data in a database

DELETE - deletes data from a database

INSERT INTO - inserts new data into a database

CREATE DATABASE - creates a new database

ALTER DATABASE - modifies a database

CREATE TABLE - creates a new table

ALTER TABLE - modifies a table

DROP TABLE - deletes a table

CREATE INDEX - creates an index (search key)

DROP INDEX - deletes an index

---

SELECT DISTINCT - Only returns distinctly different values

COUNT() - Will return a count of whatever is within the brackets

COUNT() AS ///// - This will do the same as a usual however this time addition of AS and what follows will then proceed to name the newly created table.

WHERE - Acts as a criteria setter of sorts, the filter for when you're shopping.

For WHERE - Text requires "" while Numbers DONT

The WHERE clause can contain one or more OR operators.

= - Equal

> - Greater than

< - Less than

>= - Greater than or equal

<= - Less than or equal

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

BETWEEN - Between a certain range

LIKE - Search for a pattern

IN - To specify multiple possible values for a column

Examples:

<> - WHERE Price <> 18;

BETWEEN - WHERE Price BETWEEN 18 AND 19;

LIKE - WHERE City LIKE 's%';

IN - WHERE City IN ('Paris', 'London');

ORDER BY - Used to sort in ascending / descending (Orders by the select Column and by default it does it in ASCending order)

SELECT * FROM Products

ORDER BY Price DESC; (ASC is done by default)

this works for numbers as well as alphabet so if it detects being a string value then the values will also once again in ASC order being ordered. With DESC once again working as well

it is also possible to ORDER BY 2 columns.

If this is done for example

ORDER BY Country, CustomerName;

The output would firstly consider Country, but if some rows have the same Country, it orders them by CustomerName:

AND - The AND operator is used to filter records based on more than one condition (Used very well along with WHERE)

The AND operator displays a record if all the conditions are TRUE.

The OR operator displays a record if any of the conditions are TRUE.

---

The OR operator displays a record if any of the conditions are TRUE.

The AND operator displays a record if all the conditions are TRUE.

NOT - Use of NOT Comes first before anything starts happening

WHERE NOT CustomerId < 50;

INSERT INTO - Two ways to write for this statement

INSERT INTO table_name (column1, column2, column3, ...)

VALUES (value1, value2, value3, ...);

(For specific columns also)

Example:

INSERT INTO Customers (CustomerName, ContactName, Address, City, PostalCode, Country)

VALUES ('Cardinal', 'Tom B. Erichsen', 'Skagen 21', 'Stavanger', '4006', 'Norway');

(Acts like variables being taken and carried along)

Example:

INSERT INTO Customers (CustomerName, City, Country)

VALUES ('Cardinal', 'Stavanger', 'Norway');

(Dont have to input into every column.) (Only whats specified)

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');

(Can add more then one at a single time.s)

---

INSERT INTO table_name

VALUES (value1, value2, value3, ...);

(For any and every column in the table)

NULL - Null value simply put is a field with no value (as in being nothing/completely nothing/blank/nada)

A NULL value is different from a zero value or a field that contains spaces. A field with a NULL value is one that has been left blank during record creation!

Comparsion values like BETWEEN, <>, <, > etc. all dont work as they need a value to actually properly compare where with a NULL value none is present requiring use of

IS NULL - WHERE column_name IS NULL; (think of it like a question: Is it NULL)

Always use IS NULL to look for NULL values.

IS NOT NULL - WHERE column_name IS NOT NULL; (again like a question: It is NOT NULL)

UPDATE - The UPDATE statement is used to modify the existing records in a table.

SET

Example:

UPDATE Customers

SET ContactName = 'Alfred Schmidt', City= 'Frankfurt'

WHERE CustomerID = 1;

It is the WHERE clause that determines how many records will be updated.

Example:

UPDATE Customers

SET ContactName='Juan';

(with no WHERE this will end up updating ALL records)

UPDATE & SET

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

Example:

DELETE FROM table_name WHERE condition;

Example:

DELETE FROM table_name;

(Will delete all rows in the table)

HOWEVER something seperate if you wish to actually just delete all the rows INCLUDING THE TABLE:

DROP TABLE table_name;

is how to do it.

Once again the brother to DELETE is FROM

DELETE FROM

Aggregation Functions:

MIN() - returns the smallest value within the selected column

MAX() - returns the largest value within the selected column

COUNT() - returns the number of rows in a set

SUM() - returns the total sum of a numerical column

AVG() - returns the average value of a numerical column

EXAMPLE:

SELECT MIN(Price)

(often used with the GROUP BY clause of the SELECT)

Example:

SELECT MIN(column_name)

FROM table_name

WHERE condition;

Example

SELECT MIN(Price) AS SmallestPrice, CategoryID

FROM Products

GROUP BY CategoryID;

Example for Count:

SELECT COUNT(*) AS [Number of records], CategoryID

FROM Products

GROUP BY CategoryID;

AVERAGE - AVG()

Example:

Example use of Average

In this case using to find the higher than average prices

SELECT * FROM Products

WHERE price > (SELECT AVG(price) FROM Products);

LIKE

----

LIKE - The LIKE operator is used in a WHERE clause to search for a specified pattern in a column.

% - The percent sign % represents zero, one, or multiple characters

- The underscore sign represents one, single character

* - The allstar sign represent EVERYTHING

% - Wildcard is a char that basically stands for all like the allstar(*)

_ - just one singular char that can be anything but only one of them

* - The allstar sign represent EVERYTHING

Example:

WHERE City LIKE 'a%'

% Represents zero or more characters

_ Represents a single character

[] Represents any single character within the brackets *

^ Represents any character not in the brackets *

- Represents any single character within the specified range *

{} Represents any escaped character ** (Oracle)

Examples:

SELECT * FROM Customers

WHERE CustomerName LIKE '%es';

SELECT * FROM Customers

WHERE City LIKE '_ondon';

SELECT * FROM Customers

WHERE CustomerName LIKE '[bsp]%';

SELECT * FROM Customers

WHERE CustomerName LIKE '[a-f]%';

SELECT * FROM Customers

WHERE CustomerName LIKE 'a__%';

SELECT * FROM Customers

WHERE Country LIKE 'Spain';

---

(MICROSOFT ACCESS - WILDCARDS)

Represents zero or more characters bl finds bl, black, blue, and blob

? Represents a single character h?t finds hot, hat, and hit

[] Represents any single character within the brackets h[oa]t finds hot and hat, but not hit

! Represents any character not in the brackets h[!oa]t finds hit, but not hot and hat

- Represents any single character within the specified range c[a-b]t finds cat and cbt

# Represents any single numeric character 2#5 finds 205, 215, 225, 235, 245, 255, 265, 275, 285, and 295

---

[acs] - Considering wildcards it could be any within the brackets for that singular char

this example a , c or s

First letter has to be anythign from a range

This how to do it:

WHERE City LIKE '[a-f]%';

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

The IN operator is a shorthand for multiple OR conditions.

Example:

SELECT * FROM Customers

WHERE Country IN ('Germany', 'France', 'UK');

NOT IN - Just like in but Not

Remember the right way around.

Between does indeed use AND

SELECT *

FROM Products

WHERE ProductName

BETWEEN 'Geitost' AND 'Pavlova'

;

AS is used overall for all your alias needs.

---

JOINING

-------

JOIN - A JOIN clause is used to combine rows from two or more tables, based on a related column between them.

Example of an Inner Join:

SELECT Orders.OrderID, Customers.CustomerName, Orders.OrderDate

FROM Orders

INNER JOIN Customers ON Orders.CustomerID=Customers.CustomerID;

Name. helps refer it to its original table.

// saves confusion is there were two with the same name.

Types of Joins:

(INNER) JOIN: Returns records that have matching values in both tables

LEFT (OUTER) JOIN: Returns all records from the left table, and the matched records from the right table

RIGHT (OUTER) JOIN: Returns all records from the right table, and the matched records from the left table

FULL (OUTER) JOIN: Returns all records when there is a match in either left or right table

INNER JOIN IS ALL

JOINS Combine similar rows between two or more tables.

Inner considers the two more tables being JOINED and if they both have matching values this inner join is used.

Outer Joins

Left - returns records from left and only the matched records from the right

And vise versa for the Right JOIN

The Full join being the last and final one refers to that all records of left and right tables.

---

INNER is all

Left is left records

right is right records

FULL is all records

SELF JOIN - Another join but this time only to itself.

Example:

SELECT A.CustomerName AS CustomerName1, B.CustomerName AS CustomerName2, A.City

FROM Customers A, Customers B

WHERE A.CustomerID <> B.CustomerID

AND A.City = B.City

ORDER BY A.City;

---

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

Mega example:

SELECT City, Country FROM Customers

WHERE Country='Germany'

UNION ALL

SELECT City, Country FROM Suppliers

WHERE Country='Germany'

ORDER BY City;

Result:

City Country

Aachen Germany

Berlin Germany

Berlin Germany

Brandenburg Germany

Cunewalde Germany

Cuxhaven Germany

Frankfurt Germany

Frankfurt a.M. Germany

Köln Germany

Leipzig Germany

Mannheim Germany

München Germany

Münster Germany

Stuttgart Germany

---

ALL TRUE - if all of the subquery values meet the condition

AND TRUE - if all the conditions separated by AND is TRUE

ANY TRUE - if any of the subquery values meet the condition

BETWEEN TRUE - if the operand is within the range of comparisons

EXISTS TRUE - if the subquery returns one or more records

IN TRUE - if the operand is equal to one of a list of expressions

LIKE TRUE - if the operand matches a pattern

NOT - Displays a record if the condition(s) is NOT TRUE

OR TRUE - if any of the conditions separated by OR is TRUE

SOME TRUE - if any of the subquery values meet the condition

---

+= Add equals

-= Subtract equals

*= Multiply equals

/= Divide equals

%= Modulo equals

&= Bitwise AND equals

^-= Bitwise exclusive equals

|*= Bitwise OR equals

---

= Equal to

> Greater than

< Less than

>= Greater than or equal to

<= Less than or equal to

<> Not equal to

---

& Bitwise AND

| Bitwise OR

^ Bitwise exclusive OR

---

+ Add

- Subtract

* Multiply

/ Divide

% Modulo

---

ALTER be exactly how you touch up those tables:

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.

https://www.w3schools.com/sql/default.asp