Select examples

SELECT -    choose column/columns
FROM    -    choose table/tables
WHERE    -    choose condition/conditions, we can use AND & OR
GROUP BY - group rows to one row by grouping field/fields.
ORDER BY    -    choose to order the result ASC(default)/DESC
LIMIT    -    choose how many records to show

Get all records from table employee.
SELECT * FROM employee;

Get only 2 columns from table employee but still return all records.
SELECT firstName,lastName FROM employees;

Get name of cities from column city but return only unuique names. duplicate names will be removed. 
SELECT DISTINCT city FROM employees;

Get all columns data only if the city column is equal to 'Tel Aviv'.
SELECT * FROM employees WHERE city = 'Tel Aviv';

Get 2 columns data from employee table only if city is equal to 'Ramat Gan' and the status column is equal to 1.
SELECT firstName,lastName FROM employees WHERE city = 'Ramat Gan' AND status = 1;

Get all columns data from products only if the price column data is less or equal to 60 and the category_id is equal to 2.
SELECT * FROM products WHERE price <= 60 AND categorie_id = 2;

Get 2 columns data from product and sort then by the price from low to high.
SELECT title,price FROM products ORDER BY price ASC;

Get all records data from employee table and sort it first by city from high to low and then firstName will be sorted from low to high.
SELECT * FROM employees ORDER BY city DESC,firstName ASC;

Get 2 columns data from employee table only when the status column is equal to 1, 
then sort the result by firstName from low to high in return only the 3 first records.
SELECT firstName,lastName FROM employees WHERE status = 1 ORDER BY firstName ASC LIMIT 3;

Get total number of records only when the city is equal to 'Tel Aviv'.
SELECT COUNT(*) FROM employees WHERE city = 'Tel Aviv';

Get total number of unique city name from employee table only when the status column is equal to 1. 
duplicated city name will not be added to the total count.
SELECT COUNT(DISTINCT city) FROM employees WHERE status = 1;

When we use a const or a function that return a value we use an alias to refer later to that column. 
COUNT(DISTINCT city) turn to count_city.
SELECT COUNT(DISTINCT city) as count_city FROM employees WHERE status = 1;

OPERATORS IN QUERY
------------------------------

Get all columns data from employee only when the city is equal to 'Ramat Gan' !!!!OR!!!! 'Tel Aviv' order by the city name from low to high.
SELECT * FROM employees WHERE city IN ('Ramat Gan','Tel Aviv') ORDER BY city;

Get 2 columns data from products only when the price column data is between 50 and 100 order by price column from low to high.
SELECT title,price FROM products WHERE price BETWEEN 50 AND 100 ORDER BY price;

Get 2 columns data from products only when the value of title include the string "Dell"
SELECT title,price FROM products WHERE title LIKE '%Dell%';

Get 2 columns data from products only when the value of title end with the string "fa"
SELECT title,price FROM products WHERE title LIKE '%fa';

Get 2 columns data from products only when the value of title start with the string "AM"
SELECT title,price FROM products WHERE title LIKE 'AM%';

Get 2 columns data from products only when the value of title end with the string "7.5" or the value exist in the first placeholder or second placeholder.
SELECT title,price FROM products WHERE price LIKE '__7.5%';

Get 2 columns data from products only when the value of price is !!!NOT!!! between 50 and 100 order by price column from low to high.
SELECT title,price FROM products WHERE price NOT BETWEEN 50 AND 100 ORDER BY price;

AGGREGATE FUNCTIONS
----------------------------------

Get total records from employees when the status column is equal to 1.
SELECT COUNT(employeeID) actice FROM employees WHERE status = 1;

Get rounded sum of price column from products table only when the column category_id is equal to 2.
SELECT ROUND(SUM(price),2) price_sum FROM products WHERE categorie_id = 2;

Get the highest value from column price.
SELECT MAX(price) FROM products;

Get rounded minimum price from products.
SELECT ROUND(MIN(price),2) min_price FROM products;

Get rounded average price from products.
SELECT ROUND(AVG(price),2) avg_price FROM products;

GROUP BY :

We use group by to generate a single row for a columns who are declared in the GROUP BY.
Every column/columns in the group by should be olso in the select statement.

Group result by city and return the city and total employees per city (unique city).
SELECT city,COUNT(firstName) FROM employees GROUP BY city;

Group result by city and return the city and total employees per city who are active order by city from low to high.
SELECT city,COUNT(firstName) AS counter FROM employees
WHERE status = 1 GROUP BY city ORDER BY city ASC;

HAVING
-----------

HAVING can be only apply when the GROUP BY exist in statement.
HAVING is like a WHERE statement on the GROUP BY.

Group result by city and return the city and total employees per city who are active and after the grouping we check that 
the city 'Tel Aviv' is not in city column value. order by city from low to high.
SELECT city,COUNT(firstName) AS counter FROM employees
WHERE status = 1 GROUP BY city HAVING city != 'Tel Aviv' ORDER BY city ASC;

Group result by categorie_id and return the categorie_id and average price per categorie_id who have 1 in visibility column and after the grouping we check that
the rounded avg computed column is greater then 100. order by categorie_id from low to high.
SELECT categorie_id,ROUND(AVG(price),2) AS avg FROM products
WHERE visibility = 1 GROUP BY categorie_id HAVING avg > 100 ORDER BY categorie_id ASC;