Intro to Database Systems - Part 6 & 7 : Basic SQL

This part won’t cover the notions of passing from relational algebra to SQL since this has already been covered in the previous post. In this section, we will start by introducing a main overview of a basic SQL query. Then, we structure this overview into 10 keywords and 6 short notions commonly used in SQL :

The most basic form of a SQL query looks like the following :

More generally, we’ll have:

Before moving on with the common notions, it’s important to have in mind that an SQL query returns a set of the initial table, not a multi-set: the returning table doesn’t contain duplicates, unless specified otherwise. This is a major difference with the relational algebra.

1) When to use * (star) ?

Simply when you want to extract all columns satisfying a certain condition from that db. Example:

2) OPERATORS

operators.PNG

We’ll notice that the WHERE clause has the following form:

The usual arithmetic operators don’t really need an introduction. Let’s spend some time understanding the most particular ones: BETWEEN, LIKE and IN.

The BETWEEN keyword expresses values within a range.

The LIKE keyword searches a specific pattern in a column.

The IN keyword allows us to force pick a value from the columns.

3) The DISTINCT keyword

We use DISTINCT to list different values:

4) Renaming

Renaming has the following form:

Renaming to a column name that requires spaces involves either using square brackets or “ ”.

We can also merge columns in a single one under a single name. The new column will contain values separated by commas.

5) Evaluations within queries

The following is an example of how we can add 1 to all the columns selected:

6) Constraints

SQL provides also a possibility to force constants default values .

7) The ORDER BY keyword

This keyword is used to sort columns in ascending order by default. We need to add ASC or DESC if we want to precise the order.

8) Range variables

Range variables are just a way to precise which columns we want in the case where we have 2 databases containing the same attribute.

This SQL command will produce something similar to the following:

var.PNG

9) The JOIN keyword

We use JOIN to subset from a merge of two tables. We subset the specific rows when there is at least one match in BOTH tables.

Let’s illustrate the procedure with an example. If we want the orderIDs, customerNames, and the customerCountries of all the customers that order something, we will :

10) The UNION keyword

We use the UNION keyword when we want to merge 2 relations.

Those relations imply 2 things:

Again, let’s illustrate the process with an example: we need to find the skaters that have participated in a regional OR a local competition. We can translate this in 2 ways:

11) The INTERSECT keyword

We use the INTERSECT keyword when we want to have queries satisfying a condition that’s valid in both intersected relations. If a record exists in one query and not in the other, it will be omitted from the INTERSECT results.

Again, those relations imply 2 things:

Let’s illustrate the process with an example: we need to find the skaters that have participated in a regional AND a local competition. We can translate this in 2 ways:

12) The EXCEPT keyword

We use the EXCEPT keyword when we want to have queries satisfying a condition that’s valid in both intersected relations. If a record exists in one query and not in the other, it will be omitted from the INTERSECT results.

Again, those relations imply 2 things:

Let’s illustrate the process with an example: we need to find skaters that have participated in a local but not in a regional competition

13) The ALL keyword : sets & multisets

Returning multisets imply returning duplicates. SLQ queries return sets. We can force the SQL query to return a multiset by using the keyword ALL (UNION ALL, INTERSECT ALL, EXCEPT ALL). We’ll obtain:

14) The IN keyword

We already mentioned that the IN keyword forces the WHERE column_name IN (value1,value2,…) condition to pick within a predefined value.

The best example to illustrate this scenario is the following: find skaters who have NOT participated in competition 103.

15) The EXISTS keyword

The EXISTS keyword works like the IN keyword, but on a subquery. We will have WHERE EXISTS ( subquery ); . The main query returns a result if the subquery is non empty.

If we need to find names of skaters who have participated in competition 103, we will :

16) The ALL/ANY/SOME keywords : quantifiers

Those keywords work just like the EXISTS keyword. They are optimizers. the following examples are equivalent of the same scenario.

Here is a quick overview of the scenarios where ALL can be used :

For the keyword ANY or SOME, both are equivalent and interchangeable:

We will continue with more advanced SQL queries in the next post.

 
75
Kudos
 
75
Kudos

Now read this

Intro to Database Systems - Part 3 : The Relational Model

The entity-relationship model described in the previous lecture is a diagram. The main purpose is to clearly picture the constraints of the database to someone that is not necessarily coming from a computer science field. The model that’... Continue →