Skip to content

Latest commit

 

History

History
289 lines (211 loc) · 7.39 KB

c76a.md

File metadata and controls

289 lines (211 loc) · 7.39 KB

lang.psql.select

To read rows and data from your database tables

Synopsis

  SELECT [COMMNAD] <column-name> [AS <column-name>]
  FROM <table-name> [AS <table-alias>] [COMMANDS];'

Overview

One of the main SQL operations is to read and query data. To do so we use the SELECt command to specify what we want to grab and FROM to know from what table to grab the values

Commands

  • FROM: What table to select the fields from
  • WITH: Create a temporary in memory table representation of a sub-query
  • DISTINCT: Only grab non repeated records
  • WHERE: Perform conditional filter
  • LIMIT: Limit the number of records that are returned
  • OFFSET: What row to start selecting from
  • ORDER BY: Sort on ascending or descending order
  • GROUP BY: Groups rows that have the same values into summary rows
  • AS: Change a column display name
  • UNION: Combine the results of multiple queries
  • INTERSECT: List of the values that are in both queries
  • EXCEPT: List of the values that are not in both queries

Globs

You can use some globs inside your data query

  • *: Select every single column from an specified table

Cookbook

Select some columns

To select and view specified fields:

  SELECT first_name, last_name FROM person;

Select every column in a table

This is how you perform a basic read operation

  SELECT * FROM <table-name>;

Change the column name of a field

By default if you don't specify a column name, it will use either:

  • The function name
  • The field name
  • ?colum? as an undefined

To give a column a especial name you can use the AS command to give a display name

  SELECT brand, model,
  ROUND(price - (price * .10), 2) AS discounted_price
  FROM car;

Reference a table by an alias

You can abbreviate the name of your tables when combining columns from different tables with aliases, so you don't have to be repeating

  SELECT b.base_id, s.supply_id, s.name
  FROM base AS b
  JOIN supply AS s;

Select only unique records in a field

If you want to view only the non repeated elements in a columns you can use the DISTINCT command.

  SELECT DISTINCT <column-name> FROM <table-name>;

Limit how many and starting from where records are recover

If you only want to display only a certain amount of results you can use the LIMIT command

You can also specified an offset for what row the limit starts grabbing from with the OFFSET command

  SELECT * FROM person OFFSET <row-number> LIMIT <row-number>;

Handle empty records

If you don't give a constrain to the records in a field. When queering this fields you might find yourself with empty values.

To replace this empty values with some default value you can use the COALESCE() function

  SELECT COALESCE(email, "No email given") FROM person;

Display summary of repeated values

You can use the GROUP BY command to group repeated values into summary rows

It is often used with aggregation functions, to group the result set into one or more columns

  • COUNT()
  • MAX()
  • MIN()
  • SUM()
  • AVG()
  SELECT COUNT(CustomerID), Country FROM Customers
  GROUP BY Country;

Perform arithmetic on a column records

If you want to perform some arithmetic operation on the values of the records in a field, you can do so with arithmetic operators (*, +, /, etc).

By default this will be displayed in an extra unnamed column and not directly on the original column

  SELECT id, brand, model, price,
  ROUND(price * .10, 2), ROUND(price - (price * 0.1), 2)
  FROM car;

Union: Combine the results of different queries

This will combine the results of different SELECT queries with the UNION command.

The values of the columns don't need to match, but the number of columns and datya type do

  SELECT country FROM customers
  UNION
  SELECT region FROM employees;

When performing union of two sorted views the individual sorting of the views will be lost the only way to avoid this by encosing each query with ()

Perform union on two separetly sorted queries

When uniting two separetly sorted queries SQL will return an unsorted result, If you want to preserve the sorting from each query then surround each query with ()

  (SELECT name FROM bands ORDER BY name)
  UNION
  (SELECT name FROM albums ORDER BY name)

Interception: Combine the intersection of values in different queries

You can display the intersecting results (same values) of different queries with the INTERSECT command.

By default this will display only the distinct values, if you want to display also the repeated values you can use INTERSECT ALL

  SELECT country FROM customers
  INTERESECT [ALL]
  SELECT country FROM suppliers;

Difference: Combine the values from different queries that don't match

If you want only the values that are not the same in different queries use the EXCEPT command

By default this will display only the distinct values, if you want to display also the repeated values you can use EXCEPT ALL

  SELECT country FROM customers
  EXCEPT [ALL]
  SELECT country FROM suppliers;

Perform nested queries (sub-queries)

You can perform sub queries to fill columns in another queries by embeding them like an other selected column with ()

  SELECT b.base_id, s.supply_id, s.name,
      (
        SELECT quantitiy FROM inventory
         WHERE base_id = b.base_id AND supply_id = s.supply_id
      )
  FROM base AS b
  JOIN supply AS s;

You can also perform nested queries inside filtering statments

   WHERE buyPrice > ( SELECT AVG(buyPrice) FROM products)

Create a CTE (Common Table Expression)

Instead of writing a subquery multiple times iside your query or embedding it iside parts of your query every time you need it. You can use the WITH statement to create an in memory table representation of your query

This will only exit and be able to be used inside the query where it has been defined and is done on top of the start of the main query

It can not be used by other queries unless you redifined it from within that other query. This is how it defers from temporary tables since in temporary tables the table persists and can be used by other queries during the duration of the sessio

  • The syntax is:
  WITH <CTE_name> [(column_definition)] AS (
    CTE_query_definition
  );
  • An example:
  WITH cte_film AS (
      SELECT
          film_id,
          title,
          (CASE
              WHEN length < 30 THEN 'Short'
              WHEN length < 90 THEN 'Medium'
              ELSE 'Long'
          END) length
      FROM
          film
  )
  SELECT
      film_id,
      title,
      length
  FROM
      cte_film
  WHERE
      length = 'Long'
  ORDER BY
      title;

Sort the selected fields

You can sort in descending or ascending order with the ORDER BY command

Filter based on conditions

You can filter data that satisfies a condition with the WHERE command