Learning SQL Notes #3: Query Primer (CH. 3)

• Query Mechanics
• Query Clauses
  • SELECT
  • FROM
    • Table Links
    • Table Aliases
  • GROUP BY and HAVING (CH. 8)
  • ORDER BY
• Filtering
  • WHERE
    • OR operator
    • AND operator
    • NOT operator
    • Expressions
    • NULL

Complete sometime this summer:

• Finish Join Notes;
• Finish GROUP BY Notes;

Query Mechanics

• Do you have permission to execute the statement?
• Do you have permission to access the desired data?
• Is your statement syntax correct?

Query Clauses

SELECT

• Literals, such as numbers or strings
• Expressions, such as transaction.amount * −1
• Built-in function calls, such as ROUND(transaction.amount, 2)
• User-defined function calls

SELECT version(), user(), database();

Results:

SELECT row1 AS r1;/*Column Aliases*/

SELECT DISTINCT row1 /*Removing Duplicates-should know beforehand whether duplicates are possible*/

R codes：

unique()

FROM

• Permanent tables (i.e., created using the create table statement)
• Derived tables (i.e., rows returned by a subquery and held in memory)

  SELECT *
  FROM 
  (SELECT first_name, last_name, email
  FROM customer
  WHERE first_name = 'JESSIE'
  ) AS cust;
  

• Temporary tables (i.e., volatile data held in memory): any data inserted into a temporary table will disappear at some point

  CREATE TEMPORARY TABLE actors_j 
  (actor_id smallint(5), 
  first_name varchar(45), 
  last_name varchar(45)
  );
  

• Virtual tables (i.e., created using the create view statement): When you issue a query against a view, your query is merged with the view definition to create a final query to be executed.

  CREATE VIEW cust_vw AS 
  SELECT customer_id, first_name, last_name, active
  FROM customer;
  

Table Links

See JOIN in the next note.

Table Aliases

FROM customer AS c;

GROUP BY and HAVING (CH. 8)

[] Haven’t done

ORDER BY

1. ORDER BY col1, col2, etc;
   

   R codes：

   df[order(col1),]
      
   require(tidyverse)
   df %>%
   arrange(col1)
   

2. ORDER BY col1;
   ORDER BY col1 desc;
   

   R codes：

   df[order(-col1),]
      
   require(tidyverse)
   df %>%
   arrange(desc(col1))
   

3. SELECT col1, col2, col3;
   FROM table1
   ORDER BY 3; /*equivalent to ORDER BY col3*/
   

Filtering

WHERE

(...) AND (...)
(...) OR (...)

See operators and expressions for details.

OR operator

AND operator

NOT operator

Expressions

An expression can be any of the following:

• A number
• A column in a table or view
• A string literal, such as ‘Maple Street’
• A built-in function, such as concat(‘Learning’, ' ‘, ‘SQL’)
• A subquery
• A list of expressions, such as (‘Boston’, ‘New York’, ‘Chicago’)

Operators:

• Comparison operators, such as =, !=, <, <=, >, >=, <>, like, in, between, is null, exists
• Arithmetic operators, such as +, −, *, /, DIV (integer division) and (% or MOD) for modulus

Note:

1. = can be used for date/string/number;
2. ‘between and’ can be used for date/string/number;
3. ‘between and’ is inclusive;
4. col1 (not) in (‘A’,‘B’)/subqueries;
5. built-in function: left(name, 1) in (‘A’,‘B’);
6. wildcards/regular expressions:
   • Strings beginning/ending with a certain character
   • Strings beginning/ending with a substring
   • Strings containing a certain character anywhere within the string
   • Strings containing a substring anywhere within the string
   • Strings with a specific format, regardless of individual characters

NULL

Null is used for various cases where a value cannot be supplied, such as:

• Not applicable Such as the employee ID column for a transaction that took place at an ATM machine
• Value not yet known Such as when the federal ID is not known at the time a customer row is created
• Value undefined Such as when an account is created for a product that has not yet been added to the database

Note:

• An expression can be null, but it can never equal null. IS NULL/IS NOT NULL.
• Two nulls are never equal to each other.