Table of Contents

SQL Keywords

• SELECT - Determines which columns to include.

  • SELECT * to cover all columns in your table(s).
  • SELECT col1, col2, ... to choose specific columns.
  • SELECT DISTINCT col1, col2, ... to choose columns, then filter out duplicate rows. Identical rows are shown only once.
  • Use aggregate functions instead of columns if you want to find something specific.
    • SELECT COUNT(col1, col2, ...) instead of showing the selected table, show the number of rows are in it.
    • This will create a new column.
  • Include AS if you want to rename a selected column
    • SELECT salary AS totalSalary

• FROM - Determines which table the columns are drawn from.

  • If you need to include columns from multiple tables, use a JOIN here. If you use a JOIN, you need to:
    • Reference the columns by their tables. SELECT table1.name, table2.city
    • Use ON to specify which PK/FK column the tables share. ON table1.employeeid = table2.employeeid
  • You can draw from the same table twice to compare table entries. But you need to:

    • Use AS to give a nickname to distinguish them.

    • Use nickname.column in the SELECT statement

      Select junior.Name AS Employee
      FROM Employee as junior, Employee as manager
      WHERE manager.Id = junior.ManagerId AND junior.Salary > manager.Salary
      

• WHERE - Filter rows using custom rules

  • a.column = b.column Filter rows where 2 columns have the same value.

  • LIKE - Match row values to a string, similar to regular expressions.

    • Use % as placeholder for any characters.
    • Use _ as placeholder for a single character
    • Can use regular expressions in your string
      • [aeiou] to represent a single character
    • Examples
      • WHERE name LIKE %iva - Only include rows where name value starts with “iva”.
      • LIKE %iva% - Contains “iva”
      • LIKE %van - Ends with “van”
      • LIKE S___ - Starts with “S”, then followed by 3 characters.

  • IN - Value must be in the given set of possible values.

    • WHERE first_name IN ('Laura', 'Adam', 'Mike');
    • Can also use another query as your set of values.

  • NOT - Combine with IN or LIKE other keywords. Include everything that doesn’t satisfy your rule.

    SELECT MAX(Salary) as SecondHighestSalary
    FROM Employee
    WHERE Salary NOT IN (
    	SELECT MAX(Salary)
    	FROM Employee
    	)
    

  • AND - Require multiple rules to be satisfied.

  • OR - Require one of several rules to be satisfied.

• LIMIT - Filter the top N rows

  • SELECT FROM col1 LIMIT 5
  • Include OFFSET to skip N rows before applying LIMIT​
    • LIMIT 1 OFFSET 3 - Show only the 4th row.

• ORDER BY - Sort selection table by one column

  • Add ASC or DESC for sort order.
  • SELECT * FROM table ORDER BY name ASC - Sort all rows by value in name column, A→Z

Constraints

• Constraints are added to table columns to restrict what values can be used for them on insertion, or how the values are used.
  • Main ones: NOT NULL, UNIQUE, PK, FK
• PK = Unique ID for table entries.
  • Must be unique for each row.
  • Every table has 1 PK. But you can combine multiple columns into 1 PK.
• FK = Shared column between 2 tables.
  • One tables FK is another’s PK
  • Used to Join two tables together

Joins

• Joins are used to merge columns from two different tables together into one table.

• 3 main types of Joins

  • Inner Join - Include only rows that exist in both tables.
    • If PK value in table 1’s row matches the FK value in table 2’s row, the columns can be completely merged. This row will show up in a inner join.
    • All rows will be completely filled.
  • Left Join - Include all rows from the left table.
    • Some rows will be half filled.
    • If the right table has a row with matching PK/FK value, that data is also included in the merged row.
    • If the right table doesn’t have a matching row, those fields will be blank.
    • Right Join does the same thing but for the right table’s rows.
  • Full Join - Include all rows from both tables.
    • Many rows will be half empty.

    ![JoinDiagram.jpg](<https://s3-us-west-2.amazonaws.com/secure.notion-static.com/c68b6d8b-034b-40e2-ab5e-b1416c3592e5/JoinDiagram.jpg>)
  

• 2 Special Joins

  • Self Join
    • When you SELECT FROM the same table twice
    • Use when there’s some internal relationship or subgroups within the table entries
    • Employees table where some employees are managers of other employees
  • Cross Join
    • Shows every combination of rows between the two tables.
    • Number of rows in result = rows(table1) * rows(table2)

Manipulating Table Data

Concatenating Columns

SELECT 
	first_name,
	last_name,
	first_name || ' ' || last_name full_name	-- Newly created column: full_name
FROM users

• Similar to concatenating strings in Java.
  • column1 || ' ' || column2 is the same as string1 + " " + string2