SQL

What is SQL?

SQL, or Structured Query Language, is a language designed to allow both technical and non-technical users query, manipulate, and transform data from a relational database.

Relational databases

A relational database represents a collection of related (two-dimensional) tables. Each of the tables are similar to an Excel spreadsheet, with a fixed number of named columns (the attributes or properties of the table) and any number of rows of data.

To retrieve data from a SQL database, we need to write SELECT statements. SELECT column, another_column, … FROM mytable;

In order to filter certain results from being returned, we need to use a WHERE clause in the query. SELECT column, another_column, … FROM mytable WHERE condition AND/OR another_condition AND/OR … ;

When writing WHERE clauses with columns containing text data, SQL supports a number of useful operators to do things like case-insensitive string comparison and wildcard pattern matching, SQL Operators

Filtering and sorting Query results by using:

1. To discard rows that have a duplicate column value by using the DISTINCT keyword .. SELECT DISTINCT column, another_column, … FROM mytable WHERE condition(s);

2. To sort your results by a given column in ascending or descending order using the ORDER BY clause .. SELECT column, another_column, … FROM mytable WHERE condition(s) ORDER BY column ASC/DESC; Note: Another clause which is commonly used with the ORDER BY clause are the LIMIT and OFFSET clauses. SELECT column, another_column, … FROM mytable WHERE condition(s) ORDER BY column ASC/DESC LIMIT num_limit OFFSET num_offset;

3. When inserting data into a database, we need to use an INSERT statement .. `INSERT INTO mytable VALUES (value_or_expr, another_value_or_expr, …); Note: each row of data you insert should contain values for every corresponding column in the table.

4. To adding new data, a common task is to update existing data, which can be done using an UPDATE statement .. UPDATE mytable SET column = value_or_expr, other_column = another_value_or_expr, … WHERE condition;

5. To delete data from a table in the database, you can use a DELETE statement .. DELETE FROM mytable WHERE condition;

6. To create a new database table using the CREATE TABLE statement ..

CREATE TABLE IF NOT EXISTS mytable (
    column DataType TableConstraint DEFAULT default_value,
    another_column DataType TableConstraint DEFAULT default_value,
    …
);

7. In some rare cases, you may want to remove an entire table including all of its data and metadata, and to do so, you can use the DROP TABLE statement, which differs from the DELETE statement in that it also removes the table schema from the database entirely ..

DROP TABLE IF EXISTS mytable;