SQL Grammer _ BASIC

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

• 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

Distinct

Select all the different values from the Country column in the Customers table.
select distinct country from customers

Not

Use the NOT keyword to select all records where City is NOT “Berlin”.
select * from customers where not city=’berlin’;

Or

Select all records where the City column has the value ‘Berlin’ or ‘London’.
select * FROM Customers where City = ‘Berlin’ or city = ‘london’;

Order by

Select all records from the Customers table, sort the result alphabetically, first by the column Country, then, by the column City.
SELECT * FROM Customers ORDER BY Country, City;

Insert into values

Insert a new record in the Customers table.
INSERT INTO Customers (CustomerName,Address,City,PostalCode,Country)
VALUES(‘Hekkan Burger’,’Gateveien 15′,’Sandnes’,’4306′,’Norway’);

Is null / Is not null

Select all records from the Customers where the PostalCode column is empty.
SELECT * FROM Customers WHERE PostalCode IS NULL;

Update set

Update the City column of all records in the Customers table.
UPDATE Customers SET City = ‘Oslo’;

Update the City value and the Country value.
update Customers set City = ‘Oslo’, country= ‘Norway’ WHERE CustomerID = 32;

Delete from

Delete all the records from the Customers table.
DELETE FROM Customers;

Min / Max / Avg / Sum

Use the MIN function to select the record with the smallest value of the Price column.
SELECT MIN(Price) FROM Products;

Count

Use the correct function to return the number of records that have the Price value set to 18.
SELECT COUNT(*) FROM Products WHERE Price = 18;

Like / not like

Select all records where the value of the City column starts with the letter “a”.
SELECT * FROM Customers WHERE City LIKE ‘a%’;
‘%a’
‘%a%’
‘a%b’
‘_a%’ > the second letter of the City is an “a”.

In / not in

Use the IN operator to select all the records where Country is either “Norway” or “France”
SELECT * FROM Customers WHERE Country IN (‘Norway’,’France’);

Between / not between

Use the BETWEEN operator to select all the records where the value of the Price column is between 10 and 20.
SELECT * FROM Products WHERE Price BETWEEN 10 AND 20;
BETWEEN ‘Geitost’ AND ‘Pavlova‘; > alphabetically

Left Join

SELECT * FROM Orders
LEFT JOIN Customers ON Orders.CustomerID = Customers.CustomerID;

Inner join

Choose the correct JOIN clause to select all records from the two tables where there is a match in both tables.
SELECT * FROM Orders INNER JOIN Customers ON Orders.CustomerID=Customers.CustomerID;

Right join

Choose the correct JOIN clause to select all the records from the Customers table plus all the matches in the Orders table.
SELECT * FROM Orders RIGHT JOIN Customers ON Orders.CustomerID=Customers.CustomerID;

Order by Count

List the number of customers in each country, ordered by the country with the most customers first.
SELECT COUNT(CustomerID),Country FROM Customers GROUP BY Country
ORDER BY COUNT(CustomerID) DESC;

 LIMIT 

SELECT * FROM Customers LIMIT 3;

JOIN

INNER JOIN: Returns records that have matching values in both tables
LEFT JOIN: Returns all records from the left table, and the matched records from the right table
RIGHT JOIN: Returns all records from the right table, and the matched records from the left table
CROSS JOIN: Returns all records from both tables

SELF JOIN

T1 and T2 are different table aliases for the same table.

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;

Join : columns
union : rows 

UNION

The UNION operator selects only distinct values by default. To allow duplicate values, use UNION ALL:

SELECT City FROM Customers UNION SELECT City FROM Suppliers ORDER BY City;

SELECT City FROM Customers UNION ALL SELECT City FROM Suppliers ORDER BY City;

HAVING

SELECT COUNT(CustomerID), Country FROM Customers GROUP BY Country
HAVING COUNT(CustomerID) > 5 ORDER BY COUNT(CustomerID) DESC;

EXISTS

The EXISTS operator returns TRUE if the subquery returns one or more records.

SELECT SupplierName FROM Suppliers WHERE EXISTS (SELECT ProductName FROM Products WHERE Products.SupplierID = Suppliers.supplierID AND Price < 20);

ANY (or) / ALL (and)

ANY means that the condition will be true if the operation is true for any of the values in the range.
ALL means that the condition will be true only if the operation is true for all values in the range.

SELECT ProductName FROM Products
WHERE ProductID = ANY (SELECT ProductID FROM OrderDetails WHERE Quantity = 10);

SELECT ProductName FROM Products
WHERE ProductID = ALL(SELECT ProductID FROM OrderDetails  WHERE Quantity = 10);

CASE

The CASE statement goes through conditions

SELECT OrderID, Quantity,
CASE
WHEN Quantity > 30 THEN ‘The quantity is greater than 30’
WHEN Quantity = 30 THEN ‘The quantity is 30’
ELSE ‘The quantity is under 30’
END AS QuantityText
FROM OrderDetails;

IFNULL() / COALESCE()

The MySQL IFNULL() function lets you return an alternative value if an expression is NULL.

The example below returns 0 if the value is NULL:

SELECT ProductName, UnitPrice * (UnitsInStock + IFNULL(UnitsOnOrder, 0)) FROM Products;

SELECT ProductName, UnitPrice * (UnitsInStock + COALESCE(UnitsOnOrder, 0)) FROM Products;

COMMENTS

SELECT * FROM Customers — WHERE City=’Berlin’;

/*Select all the columns
of all the records
in the Customers table:*/
SELECT * FROM Customers;

 

SQL Tutorial II (INTERMEDIATE)