4 - DATE functions

NOW() returns the current date and time.

SELECT NOW(),CURDATE(),CURTIME()

will result in something like this:

NOW()	CURDATE()	CURTIME()
2014-11-22 12:45:34	2014-11-22	12:45:34

CREATE TABLE Orders
(
OrderId int NOT NULL,
ProductName varchar(50) NOT NULL,
OrderDate datetime NOT NULL DEFAULT NOW(),
PRIMARY KEY (OrderId)
)



Therefor:
INSERT INTO Orders (ProductName) VALUES ('Jarlsberg Cheese')

resulst in :

OrderId	ProductName	OrderDate
1	Jarlsberg Cheese	2014-11-22 13:23:44.657

_____________________________________________________________________________

MySQL Date Functions

The following table lists the most important built-in date functions in MySQL:

Function	Description
NOW()	Returns the current date and time
CURDATE()	Returns the current date
CURTIME()	Returns the current time
DATE()	Extracts the date part of a date or date/time expression
EXTRACT()	Returns a single part of a date/time
DATE_ADD()	Adds a specified time interval to a date
DATE_SUB()	Subtracts a specified time interval from a date
DATEDIFF()	Returns the number of days between two dates
DATE_FORMAT()	Displays date/time data in different formats

_____________________________________________________________________________

SELECT EXTRACT(YEAR FROM OrderDate) AS OrderYear,
EXTRACT(MONTH FROM OrderDate) AS OrderMonth,
EXTRACT(DAY FROM OrderDate) AS OrderDay

FROM Orders
WHERE OrderId=1

example

The following SELECT statement:

SELECT DATEDIFF('2014-11-29','2014-11-30') AS DiffDate

will result in this:

DiffDate
-1

_____________________________________________________________________________

3 - SQL Agrregate /String Functions

SQL Aggregate Functions

SQL aggregate functions return a single value, calculated from values in a column.

Function	Description
AVG()	Returns the average value
COUNT()	Returns the number of rows
FIRST()	Returns the first value
LAST()	Returns the last value
MAX()	Returns the largest value
MIN()	Returns the smallest value
ROUND()	Rounds a numeric field to the number of decimals specified
SUM()	Returns the sum

• SELECT AVG(Price) AS PriceAverage FROM Products; //returns a single value (the avg of all the prices column in products table)
• SELECT ProductName, Price FROM Products
  WHERE Price>(SELECT AVG(Price) FROM Products);

• SELECT COUNT(DISTINCT column_name) FROM table_name;
• SELECT COUNT(CustomerID) AS OrdersFromCustomerID7 FROM Orders WHERE CustomerID=7;
• SELECT COUNT(*) AS NumberOfOrders FROM Orders;//determines numbers of rows in Orders table

• SELECT MAX(Price) AS HighestPrice FROM Products;
• SELECT MIN(Price) AS SmallestOrderPrice FROM Products;

Many database systems have adopted the IEEE 754 standard for arithmetic operations, according to which the default rounding behavior is "round half to even." In this scheme, .5 is rounded to the nearest even integer. So, both 11.5 and 12.5 would be rounded to 12.

• SELECT ROUND(column_name,decimals) FROM table_name;
• SELECT ProductName, ROUND(Price,0) AS RoundedPrice FROM Products;

• SELECT SUM(Quantity) AS TotalItemsOrdered FROM OrderDetails;

_______________________________________________________________________

SQL String Functions

Function	Description
CHARINDEX	Searches an expression in a string expression and returns its starting position if found
CONCAT()
LEFT()
LEN() / LENGTH()	Returns the length of the value in a text field
LOWER() / LCASE()	Converts character data to lower case
LTRIM()
SUBSTRING() / MID()	Extract characters from a text field
PATINDEX()
REPLACE()
RIGHT()
RTRIM()
UPPER() / UCASE()	Converts character data to upper case

2 - More Keywords

CREATE INDEX index_name
ON table_name (column_name)

CREATE UNIQUE INDEX index_name
ON table_name (column_name)

ALTER TABLE table_name DROP INDEX index_name  //MySQL
ALTER TABLE table_name ADD column_name datatype
ALTER TABLE table_name DROP COLUMN  MODIFY column_name datatype//MySQL


DROP TABLE table_name;
DROP DATABASE database_name;
Delete only entire data in a table, TRUNCATE TABLE table_name;



//MySQL-------------------------------------------
CREATE TABLE Persons
(
ID int NOT NULL AUTO_INCREMENT,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255),
PRIMARY KEY (ID)
)

ALTER TABLE Persons AUTO_INCREMENT=100

INSERT INTO Persons (FirstName,LastName)
VALUES ('Lars','Monsen')
//MySQL-------------------------------------------END

VIEW a view is a virtual table based on the result-set of an SQL statement.

CREATE VIEW view_name AS 

SELECT column_name(s) FROM table_name WHERE condition;

query the view : SELECT * FROM <<View-Name>>
Updating a View:CREATE OR REPLACE VIEW view_name AS
SELECT column_name(s) FROM table_name WHERE condition ;Delete View: DROP VIEW view_name;

NULL is not same as 0;

SELECT LastName,FirstName,Address FROM Persons WHERE Address IS NULL;

SELECT LastName,FirstName,Address FROM Persons WHERE Address IS NOT NULL;

GROUP BY used in conjunction with the aggregate functions to group the result-set by one or more columns.

SELECT column_name, aggregate_function(column_name) FROM table_name
WHERE column_name operator value
GROUP BY column_name;

HAVING vs where 

HAVING clause was added to SQL because the WHERE keyword could not be used with aggregate functions.

SELECT column_name, aggregate_function(column_name) FROM table_name
WHERE column_name operator value
GROUP BY column_name
HAVING aggregate_function(column_name) operator value;

1 - Basic SQL Query statements

Reference: https://www.w3schools.com/sql/sql_select.asp

Customers

Categories

Employees

Orders

Products

OrderDetails

Shippers

Suppliers

_______________________________________________________________________

The SELECT statement is used to select data from a database.

The result is stored in a result table, called the result-set.

SELECT column_name,column_name FROM table_name;

ex:SELECT CustomerName,City FROM Customers;

SELECT * FROM table_name;

ex:SELECT * FROM Customers;

_______________________________________________________________________

The DISTINCT keyword can be used to return only distinct (different) values.

SELECT DISTINCT column_name,column_name FROM table_name;

ex:SELECT DISTINCT City FROM Customers;

_______________________________________________________________________

WHERE

SELECT column_name,column_name FROM table_name
WHERE column_name operator value;

ex1: SELECT * FROM Customers WHERE Country='Mexico';

ex2: SELECT * FROM Customers WHERE CustomerID=1;

_______________________________________________________________________

LIMIT

SELECT column_name,column_name FROM table_name
LIMIT 5; // select first 5 rows(MYSQL). varies for different DBS.. some use SELECT TOP instead(Oracle)

_______________________________________________________________________

AND OR Operators

ex1: SELECT * FROM Customers WHERE Country='Germany' AND City='Berlin';

ex2: SELECT * FROM Customers WHERE City='München' OR City='Berlin';

ex3: SELECT * FROM Customers WHERE Country='Germany' AND 

                                        (City='München' OR City='Berlin');

_______________________________________________________________________

ORDER BY  --- ASC/DESC

SELECT column_name, column_name FROM table_name

ORDER BY column_name ASC|DESC, column_name ASC|DESC;

ex: SELECT * FROM Customers ORDER BY  Country ASC, CustomerName DESC;

_______________________________________________________________________

INSERT INTO  --- VALUES

INSERT INTO Customers (CustomerName, ContactName, Address, City, PostalCode, Country)
VALUES ('Cardinal','Tom B. Erichsen','Skagen 21','Stavanger','4006','Norway');

_______________________________________________________________________

UPDATE <<tablename>> SET <<column-name>> = value WHERE (condition);

ex: UPDATE Customers SET ContactName='Alfred Schmidt', City='Frankfurt'

WHERE CustomerID=1;

_______________________________________________________________________

DELETE FROM table_name WHERE (condition);

or

DELETE * FROM table_name;//deletes all records

_______________________________________________________________________