Composing Basic SQL Queries

Your First SQL Statement

SQL is a computer language designed for managing data in relational databases. SQL syntax is different from Python's. Here are its basic characteristics:

The beginning of a single-line comment is marked with two hyphens: --


1-- a single-line comment in SQL

A multi-line comment is put between /* and */:


1/* a multi-line comment
2has
3several
4lines */

Commands are written in capital letters:


1SELECT, WHERE, FROM

Every statement (or query) ends with a semicolon ; :


1SELECT * FROM table_name;
2-- A statement that requests all the data from the table ends with ";"
3WHERE
4column_name IN (1,7,9);
5-- A statement that selects data by condition also ends with ";"

Line breaks after each keyword:


1SELECT
2column_1,
3column_2,
4column_3,
5column_4
6FROM
7table_name
8WHERE
9column_1 = value_1 AND
10column_2 = value_2 AND
11column_4 = value_3;

To select data from tables, you need to write a statement, or query. A statement is a request written according to SQL syntax. Your statement should specify what data to select and how to process it.

The SELECT operator takes the selection you need. SELECT statements look like this:


1SELECT
2 column_1,
3 column_2,
4 column_3 ...
5FROM
6 table_name;
7--Select columns from the table

We have two keywords in our statement: SELECT and FROM. SELECT specifies the necessary columns from the database table. To select all columns from the table, add * symbol to the SELECT operator. FROM specifies the table from which the data should be taken.

Converting Data Types

Some aggregate functions can only be used with number values.

The data in a field might look like numbers but actually be stored as strings in the database. This happens often, most commonly because of mistakes in designing databases.

We can use a CAST statement to convert the data type of values in a column:


1CAST (*column_name* AS *data_type*)

column_name is the field whose data type is to be converted. data_type is the desired type. We can also write this:


1*column_name* :: *data_type*

Working With Dates

Truncating date to a fragment


1SELECT
2  DATE_TRUNC('date_fragment_to_be_truncated_to', column) AS new_column_with_date
3FROM
4 Table_with_all_dates

Retrieving date fragments


1SELECT
2  EXTRACT(date_fragment FROM column) AS new_column_with_date
3FROM
4 Table_with_all_dates