• LinkedIn Intro to SQL
• Codespaces save everything you do on a virtual machine; Github Codespaces
• Forking lets you create your own repository, and will let you push your own changes

Syntax

SELECT column_names FROM table_name [WHERE condition] [ORDER BY {columns/expr}] [ASC/DESC].

Fetch data

• Arithmetic operators: + - * /
  • Order of precedence: * / + -
• Character operators
  • || is for concatenation
  • '' and "" for character strings

Filter data

• Type of Conditions: Comparison
  • =, <, >, <>, <=, >=
  • IS NULL
  • LIKE checks for a specific character pattern, like _D% (_ meaning 1 single character, and % meaning multiple characters)
  • IN (..., ...) a set
  • BETWEEN AND
• Type of Conditions: Logical
  • NOT IN
  • AND
  • OR
• Order of precedence: arithmetic operators, concatenation, comparison, logical
  • Parenthesis will override the precedence

Sort data

• ORDER BY Clause
• default is ascending
• e.g. ORDER BY deptno, salary DESC
  • it first sorts by deptno in an ascending order
  • and in each category of deptno value, it sorts salaries in a descending order

Types of Functions

Single row functions: works with 1 row at a time; returns 1 result per row

Number functions: ROUND(92.423, 2)

Case function

SELECT name,job,salary,
CASE job WHEN 'Manager' THEN 1.20*salary
	WHEN 'Analyst' THEN 1.15*salary
		WHEN 'Clerk' THEN 1.10*salary
		ELSE salary
		END 'Revised Salary'
FROM emp_tab;

Character functions

• UPPER, LOWER.
• SUBSTR('HellowWorld1234567890', 6, 10).
• LENGTH.
• INSTR('HelloWord', 'e').
• TRIM('Hello World', 'World').
• REPLACE('Hello World', 'Hello', 'Good morning').

Multiple rows function: works with a group of rows at a time; returns 1 result for every group of rows **CANNOT be used in WHERE

• SUM.
• AVG.
• COUNT.
  • COUNT(*)
• MAX, MIN.
• VARIANCE.
• STDDEV (standard deviation).
• e.g. SELECT MIN(salary),MAX(salary) FROM emp_tab WHERE deptno=30
• DISTINCT is to eliminate any duplicated values

GROUP BY clause

• can use only column names instead of alias names
• HAVING clause used to filter the groups' results
• SELECT column, GROUP_FUNCTION(column),.... FROM table [WHERE condition] [GROUP BY column] [HAVING group_condition] [ORDER by column].

Multiple tables

• JOINS
  • Inner: retrieves all rows matching in both tables
  • Right
  • Left
  • Full Outer
• Syntax: SELECT columns FROM table_name1 join_name table_name2 ON join_conditions.