SQL Grammer _ BASIC
SELECT– extracts data from a databaseUPDATE– updates data in a databaseDELETE– deletes data from a databaseINSERT INTO– inserts new data into a databaseCREATE DATABASE– creates a new databaseALTER DATABASE– modifies a databaseCREATE TABLE– creates a new tableALTER TABLE– modifies a tableDROP TABLE– deletes a tableCREATE 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;
More SQL Study from Eunice..
https://eunice0121.com/category/sql/
More SQL info….
https://www.w3schools.com/mysql/default.asp