Lrrr teaches data science

Introduction to SQL: Part I

Here we will see simple SELECT statements.

Retrieving records is accomplished by SELECT statement. To select all columns, we simply use *

								
									SELECT * 
									FROM employees;

To pick only certain columns, we specify column names after the SELECT statement:

								
									SELECT id, name 
									FROM employees;

Let's bring all employees from the "electronics" department with WHERE statement:

								
									SELECT id, name, department 

									FROM employees

									WHERE department = 'electonics';

We can also add multiple conditions to our WHERE statement:

								
									SELECT id, name, department 

									FROM employees

									WHERE department = 'electonics' AND sales > 500;

New columns can be added and named dynamically with SELECT statement:

								
									SELECT id, name, sales*0.15 as commission 

									FROM employees

However we have to be careful using WHERE statements with dynamically created columns. For example, this query will fail:

								
									SELECT id, name, sales*0.15 as commission 

									FROM employees

									WHERE commission > 90;

This is because WHERE statement is evaluated before creation of the new column. To fix this we need to include the whole calculation in the WHERE statement

								
									SELECT id, name, sales*0.15 as commission 

									FROM employees

									WHERE sales*0.15 > 90;

Now that we acquired commissions for each of the employees, we may want to order them based on the commission they make. This is when ORDER BY statement comes handy. Note that the order of statements is important in SQL; ORDER BY has to be the last. Also important to remember that ORDER BY actually is evaluated last in a SQL query therefore, contrary to WHERE, 'commission' will be evaluated correctly in ORDER BY. Here is a list of functions and their execution order:

1. FROM
2. WHERE
3. GROUP BY
4. HAVING
5. SELECT
6. ORDER BY

								
									SELECT id, name, sales*0.15 as commission 

									FROM employees

									WHERE sales*0.15 > 90
									ORDER BY commission DESC;

What if we want to bring only the employees with five highest commissions, there are various methods based on the database we use. ORACLE, by default, creates two columns automatically when a table is created: ROWNUM and ROWID. We can use ROWNUM column to limit our results:

								
									SELECT id, name, sales*0.15 as commission 

									FROM employees

									WHERE sales*0.15 > 90 AND ROWNUM < 6
									ORDER BY commission DESC;

Did you notice the fact that resulting table is not correct? Well, it isn't. The reason is again related to the timeline of execution. WHERE is executed before ORDER BY therefore what we get is the first 5 rows and then order them. What we need is order the rows first then limit it. To do that we put our main query into parantheses and make it a subquery. This way it will be executed before everything else outside parentheses.

								
									SELECT * FROM

									(SELECT id, name, sales*0.15 as commission 

									FROM employees

									WHERE sales*0.15 > 90

									ORDER BY commission DESC)

									WHERE rownum < 6;

We can create new columns based on conditional logic. Let's say we want to seperate employee sales into brackets. All we have to do this use CASE-WHEN operation:

								
									SELECT name, department, 

									CASE WHEN sales > 700 THEN 'high' 

									     WHEN sales < 500 THEN 'low' 

									     ELSE 'average'

									END AS salarybracket

									FROM employees

Let's say we want to find a particular substring, for example we waould like to get all employees whose name starts with 'm'. In this case we use LIKE operator:

								
									SELECT id, name, department

									FROM employees

									WHERE name LIKE 'm%';

% represents one or more characters. If we want to substitute only one character, we use underscore '_' instead. For example 'me_' will retrieve both 'meg' and 'mel' but not 'melissa'.

Dealing with Nulls

There are times when NULLs get in our way to get the result we want. Consider we want to list employees and sales in the electronics department and order the list based on ascending sales. Based on what we learnt so far:

								
									SELECT id, name, sales

									FROM employees

									WHERE department = 'electronics'

									ORDER BY sales;

If we want to get NULLs first, we have two options. One, we can convert NULLs to zero on-the-go with the COALESCE function: COALESCE(sales,0) will return first non-NULL value in the parentheses, in this case zero.

								
									SELECT id, name, COALESCE(sales)

									FROM employees

									WHERE department = 'electronics'

									ORDER BY COALESCE(sales);

Another option is specific to ORACLE:

								
									SELECT id, name, sales

									FROM employees

									WHERE department = 'electronics'

									ORDER BY sales NULLS FIRST;

And here's an advanced query. Say we want to list all employes in the order of sales if employee is a salesperson but in the order of id if employee is not a salesperson. This requires combining ORDER BY with CASE WHEN operant.

								
									SELECT id, name, role, sales

									FROM employees

									ORDER BY CASE WHEN role = 'salesperson' THEN sales

									ELSE id

Note that we cannot use two different data types in the CASE WHEN, i.e. CASE WHEN role = 'salesperson' THEN sales ELSE department will raise an error cos department and sales are VARCHAR2 and NUMBER data types, respectively.