All Articles

Sql Basics

Intro

SQL (Structured Query Language) is the de facto relational database langauge.

There are 4 fundamental operations in SQL.

SELECT

For querying records.

INSERT

For inserting records.

UPDATE

For mutating records.

DELETE

For deleting records.

These are the four fundamental record-level operations in SQL.

  • SELECT
  • INSERT
  • UPDATE
  • DELETE

Let’s unpack each of these operations.

SELECT

Firstly, the columns we wish to select from each record is specified.

SELECT *

Here * indicates that we are returning all columns from each record.

SELECT col1, col2, col3

Here we are selecting a subset of columns from each reacord. Namely, columns col1, col2, col3.

Next, we need to specify where the data lives. That is, in which database and table. We will assume here that the data of interest lives in a single table and omit any discussion of joins. We will also herein assume that we are selecting all columns from each record. If the data lives in table table which itself lives in a database db, then the full script would read as follows.

SELECT * FROM db.table

Not only would this return all columns from each record, it would return all records in the table. Why? There are no predicates present.

If we wished to return only a subset of the records themselves, this is accomplished using predicates.

Predicates

Common predicates include:

  • =
  • >
  • <
  • >
  • >=
  • <=
  • IN
  • BETWEEN
  • LIKE
  • IS NULL
  • IS NOT NULL

Predicates may be combined using the logical conjunctions:

  • AND
  • OR

Predicates are instantiated using the WHERE operator. In this sense, the component involving the WHERE operator + a predicate is often referred to as a where clause.

The following is an example of the LIKE predicate operator.

SELECT * FROM db.table
WHERE col1 LIKE 'a%'

This returns all records where the entry in the first column begins with the character 'a'. The % sign acts as a wild card.

The following is an example of combining predicates using a logical conjunction.

SELECT * FROM db.table
WHERE col1 LIKE 'a%' OR col1 LIKE 'b%'

This returns all records where the entry in the first column begins with the character 'a' or 'b'.

INSERT

Like SELECT, the INSERT operator requires that you specify a database and table of interest. Namely, where you would like to insert a record. Unlike SELECT, this is specified first, as demonstrated below.

INSERT INTO db.table

Secondly, the fields (columns) which you would like to populate must be specified. Some fields must be populated in the sense that the underlying schema may demand some fields to be non-null. That possibility aside, suppose we wanted to populate columns col1, col2, col3. The above script would continue to read as follows.

INSERT INTO db.table (col1, col2, col3)

Finally, the values themselves must be specified. Suppose that the first two columns are of type NVARCHAR and that the third column is of type int. Suppose further that we wish to populate the first two fields with the values 'John' and 'Smith', respectively, and the third field with the value 123. The final script would then read as follows.

INSERT INTO db.table (col1, col2, col3)
VALUES ('John', 'Smith', 123);

All remaining unspecified fields will assume a value of NULL (assuming that the schema allows it).

UPDATE

Like INSERT, the database and table in which you would like to insert a new record must be specified first.

UPDATE db.table

Next, you need to manually specify what fields you would like to mutate and what new values you would like them to assume. Suppose we want to mutate the record inserted above such that 'John' and 'Smith' are replaced with 'Jane' and 'Doe' and 123 is replaced with 456. We will make the simplifying assumption that col0 is a unique id and that the unique id of the of the above reacord is 0. The full script would then read as follows.

UPDATE db.table
SET col1='Jane', col2='Doe', col3=456
WHERE col0=0;

DELETE

Deletion requires only that you specify the database and table from which you would like to delete records and a where clause to specify which records are to be deleted.

DELETE FROM db.table
WHERE condition;

These operations are usually collectively referred to as CRUD which stands for Create. Remove. Update. Delete.

Published 25 Jun 2018