Construct queries between two tables in SQL

This section also includes some common SQL commands beyond the bare minimum textbook examples.

• Queries are used to extract data and give data context.
• Queries can be used for filtering, sorting, grouping, and combining data from different tables.

BETWEEN

SELECT * FROM table_name
WHERE attribute1 BETWEEN value1 AND value2;

SELECT * FROM students
WHERE score BETWEEN 90 AND 100;

ORDER BY

SELECT * FROM table_name
WHERE condition
ORDER BY attribute ASC;

SELECT student_name
FROM tests_score
ORDER BY score ASC;

SELECT student_name
FROM tests_score
ORDER BY score ASC, student_name ASC;

GROUP BY

SELECT Attribute1, Attribute2
FROM table_name
WHERE condition
GROUP BY Attribute1, Attribute2
ORDER BY Attribute1;

SELECT AVG(score) AS average_score, subject
FROM Subjects
GROUP BY subject
ORDER BY AVG(score) DESC;

HAVING

• HAVING can only be used after GROUP BY
• it is used to filter groups, not individual rows

SELECT COUNT(Attribute2), Attribute
FROM table_name
GROUP BY Attribute
HAVING COUNT(Attribute2) > 2;

SELECT COUNT(CustomerID), CustomerCountry
FROM Customer
GROUP BY CustomerCountry
HAVING COUNT(CustomerID) > 2;

JOIN

• There are three common types of JOIN:
  • INNER JOIN
    • returns only records with matching values in both tables
  • LEFT JOIN
    • returns all rows from the left table and matching rows from the right table
    • if there is no match, right-side columns are NULL
  • RIGHT JOIN
    • returns all rows from the right table and matching rows from the left table
    • if there is no match, left-side columns are NULL

SELECT table_name1.attribute1,
       table_name1.attribute2,
       table_name1.attribute3,
       table_name2.attribute2
FROM table_name1
INNER JOIN table_name2
ON table_name1.attribute1 = table_name2.attribute2;

SELECT Item.ItemID, Item.ItemDesc, Item.ItemCost, Item.ArtistID,
       Artist.ArtistName, Artist.ArtistEmail
FROM Item
INNER JOIN Artist
ON Item.ArtistID = Artist.ArtistID
WHERE ItemCost > 100;

LIKE

• LIKE is used for non-exact matches
• wildcards:
  • _
    • one single character
  • %
    • any number of characters

SELECT * FROM Artist
WHERE ArtistName LIKE '%work';

SELECT * FROM Artist
WHERE ArtistName LIKE 'work%';

SELECT * FROM Artist
WHERE ArtistName LIKE '%work%';

CASE WHEN and CASE

SELECT name, score,
    CASE
        WHEN score >= 90 THEN 'A'
        WHEN score >= 80 THEN 'B'
        WHEN score >= 70 THEN 'C'
        ELSE 'Fail'
    END AS grade
FROM Scores;

SELECT name,
  CASE score
      WHEN 90 THEN 'Perfect'
      WHEN 80 THEN 'Good'
      ELSE 'Average'
  END AS grade
FROM Scores;

IF

SELECT name,
       IF(score >= 60, 'Pass', 'Fail') AS result
FROM Students;

IFNULL

• used when NULL may appear

SELECT name, salary + IFNULL(bonus, 0) AS total_income
FROM Employees;

NULLIF

• if two values are equal, return NULL

SELECT NULLIF(10, 10);
SELECT NULLIF(10, 5);

SELECT year_salary / NULLIF(months, 0) AS avg_salary
FROM employees;

Common operations in SQL

=
!=
BETWEEN a AND b
IN (x, y, z)
IS NULL
IS NOT NULL
AND
OR
NOT
/
DIV

Subquery

SELECT column_name
FROM table_name
WHERE column_name operator (
    SELECT column_name
    FROM table_name
    WHERE condition
);

SELECT name, grade
FROM Students
WHERE grade > (SELECT AVG(grade) FROM Students);

SELECT name, grade
FROM Students
WHERE grade IN (
    SELECT grade
    FROM Students
    ORDER BY grade DESC
    LIMIT 2
);