sql_learning.md

SQL Learning

SQL means Structured Query Language. It is a language designed to manage data stored in relational databases.

---

Concepts

Relational Databases

A database composed by other databases allowing relationships to be created between them.

Table

A set of data composed by rows and columns.

Rows

A record in the table composed with the data type that the table allows.

Columns

A set of data composed by a single data type.

Common Types

The type keywords are usually defined by each database system management (such as PostgreSQL). The common types are:

• INTEGER: a number
• TEXT: a text string
• DATE: the date formatted as YYYY-MM-DD
• REAL: a decimal value

---

Statements

Statements are texts that the database recognizes as valid command and ends with ;. A statement can be represented as:

CLAUSE table_name (parameters)

Translating the representation to a real world example, it becomes:

CREATE TABLE cute_dogs (
  name TEXT,
  photo TEXT
);

Where:

• CREATE_TABLE is the clause, which is responsible to perform tasks, and is also known as commands. By convention, clauses are written in uppercase mode.

• cute_dogs is the name of the table.

• url TEXT is the parameters given, which can be interpreted as:

  • url is the column name
  • TEXT is the column type

The above example creates a table called "cute_dogs", which defines url as the only column, which will be composed by TEXT type data.

---

Commands

CREATE

CREATE DATABASE

Creates a database with the given name.

CREATE DATABASE database_name;

Example:

CREATE DATABASE cute_dogs_gallery;

CREATE TABLE

Creates a table with the given name and uses the parameters to define the columns names and types. The parameteres are separated by comma and are composed by key-value space separated pairs.

CREATE TABLE table_name (column_name column_type, ...);

Example:

CREATE TABLE cute_dogs (
  name TEXT,
  photo TEXT
);

---

INSERT INTO

It pushes the given values to the specified table.

INSERT INTO table_name (table_columns, ...)
  VALUES (column_value, ...);

Example:

INSERT INTO cute_dogs (name, photo)
  VALUES (
    (
      "Husky",
      "http://www.cutestpaw.com/wp-content/uploads/2016/02/Happy-pup..jpg"
    ),
    (
      "Marley",
      "http://www.cutestpaw.com/wp-content/uploads/2016/02/sleeping-time.jpg"
    ),
  );

---

SELECT

It retrieves the data set of the specified column at a given table.

SELECT column_name FROM table_name;

Example:

SELECT photo FROM cute_dogs;
SELECT (name, photo) FROM cute_dogs;
SELECT * FROM cute_dogs;