Record and Fields

Header is the Header

The SELECT command is used to extract field data from a table

Select * from db name

ORDER BY age ASC/DESC

LIMIT 10 : to get limited data

OFFSET 3 : skips the values

price+delivery AS TOTAL , *+/-

CONCAT(first_name, last_name) AS full_name : join two string

Filtering data: WHERE : SELECT * FROM studio WHERE name = 'Walt Disney'

Wild Card

1. LIKE keyword : WHERE title LIKE 'The Avengers%'

   1. The % symbol can replace any number of characters (one, multiple or none)
   2. The % special symbol is known as a wildcard
   3. ‘%A%’ : % can be of anything

2. The underscore symbol _ is another wildcard and represents 1 single character only.

   1. any number of characters: %
   2. 1 single character: _

3. LOWER() and UPPER() : for case sensitive things:

   1. SELECT LOWER(title) FROM movies;
   2. WHERE LOWER(title) LIKE '%cooking%’

4. Conditions:

   1. GREATER THEN OR EQUAL TO: >=
   2. LESS THEN OR EQUAL TO: <=
   3. NOT EQUAL TO : <>

5. Aggrigator 1.

   SELECT MAX(year)
   FROM movies;
   MIN(year)
   COUNT(year)
   SUM(year)
   AVG(year)
   

Multiple Queries with SemiColon

GROUP BY genra:

SELECT genre, AVG(budget)
FROM movies
GROUP BY genre;

You can combine GROUP BY with WHERE filters. Data is filtered first, then grouped. Data is filtered first and groped later

SELECT genre, AVG(budget)
FROM movies
WHERE budget > 50
GROUP BY genre;

HAVING allows you to filter data that has been grouped.

SELECT genre, AVG(budget)
FROM movies
GROUP BY genre
HAVING AVG(budget) > 50;

REmoving duplicate ids

--checking for id duplicates
SELECT id, COUNT(id)
FROM employees
GROUP BY id
HAVING COUNT(id) > 1;

/*checking for combination 
duplicates of id and name values */
SELECT id, name
FROM employees
GROUP BY id, name
HAVING COUNT(id) > 1;

SELECT *
FROM employees
GROUP BY id, name, department, age
HAVING Count(name) > 1;

Use DISTINCT to eliminate duplicate values

/* DISTINCT is used before 
the field we want to extract */
SELECT DISTINCT name
FROM employees;

NULL

SELECT * 
FROM movies 
WHERE genre IS NULL //WHERE genre IS NOT NULL

1. Use IS NULL to find missing values or use IS NOT NULL to extract filled values. 2. Use SELECT DISTINCT followed by duplicated row/record 🆔/name to remove duplicates.

Best Selling Product Code:

SELECT product, SUM(qty)
FROM sales
WHERE month = 'June'
GROUP BY product