Skip to main content

0502 | SQL Queries

Basic queries​

  • keywords are not case sensitive | SELECT <=> select
  • query ends with semicolon (;)
  • example | SELECT employee_id, device_id FROM employees;
  • example | SELECT * FROM employees;

SELECT​

  • indicates which columns to return
  • SELECT * <=> SELECT all
  • the SELECT keyword always comes with the FROM keyword

FROM​

  • indicates which table to query

Syntax​

  • the rules that determine what is correctly structured in a computing language

Query a database​

ORDER BY​

  • sequences the records returned by a query based on a specified column or columns in either ascending or descending order
  • at the end of the query
  • specify a column to base the sort on
  • default | in ascending order
    • if you choose a column containing numeric data, it sorts the output from the smallest to largest
    • if the column contains alphabetic characters, such as in the example with the city column, it orders the records from the beginning of the alphabet to the end
  • example | sorting in ascending order 
    ---------------------------------
    SELECT customerid, city, country
    FROM customers
    ORDER BY city;
    ---------------------------------
  • sorting in descending order:
    • with the DESC keyword
    • to sort numbers from largest to smallest, or alphabetically from Z to A
    • following ORDER BY with the DESC keyword
  • example | sorting in descending order 
    ---------------------------------
    SELECT customerid, city, country
    FROM customers
    ORDER BY city DESC;
    ---------------------------------
  • sorting based on multiple columns:
    • to choose multiple columns to order by
    • sorts the output by country, and for rows with the same country, it sorts them based on city
  • example | sort based on multiple columns 
    ---------------------------------
    SELECT customerid, city, country
    FROM customers
    ORDER BY country, city;
    ---------------------------------

Basic filters on SQL queries​

  • to apply wildcards to the filter, you need to use the LIKE operator instead of an equals sign (=)

Filtering​

  • selecting data that match a certain condition

Operator​

  • a symbol or keyword that represents an operation
  • example | equal to symbol <=> '='
  • example | coutry = 'USA'

WHERE​

  • indicates the condition for a filter
  • after the keyword WHERE, the specific condition is listed using operators
  • example | WHERE country = 'USA'

Filtering for patterns​

  • use the percentage sign (%) to act as a wildcard for unspecified characters
  • example | 'East%' | return all records that start with East
  • When searching for patterns with the percentage sign, we cannot use the equals operator

Wildcard​

  • a special character that can be substitued with any other character
  • can be placed after a string, before a string, or in both locations
  • % (percentage sign) | substitutes for any number of other characters
  • _ (underscore symbol) | substitutes for one other character

LIKE​

  • used with WHERE to search for a pattern in a column
  • an operator
  • example | WHERE office LIKE 'East%'
  • example | SELECT * FROM log_in_attempts WHERE country LIKE 'US%';