The present chapter covers on how to question a database by using a SQL query.
Figure 3.1 : Database schema
1. Basic query
Let’s suppose we’d like to get data about all employees. We’d like to have data about all attributes.
SELECT *
FROM Employee
We get the following table.
| {{h}} |
|---|
| {{r}} |
2. Query with basic filter
Let’s suppose we’d like to get data about the first name, last name and the number of kids of all employees having at least one kid.
SELECT First_name, Last_name, Kids_number
FROM Employee
WHERE Kids_number >= 1
We get the following table.
| {{h}} |
|---|
| {{r}} |
3. Query with a AND condition
Let’s suppose we’d like to get data about all employees having at least one kid and whose first name begins with the letter “R”. We’d like to have data about all attributes.
SELECT *
FROM Employee
WHERE Kids_number >= 1 AND First_name LIKE 'R%'
We get the following table.
| {{h}} |
|---|
| {{r}} |
4. Query with a OR condition
Let’s suppose we’d like to get data about all employees either having at least one kid or whose first name begins with the letter “D”. We’d like to have data about all attributes.
SELECT *
FROM Employee
WHERE Kids_number >= 1 OR First_name LIKE 'D%'
We get the following table.
| {{h}} |
|---|
| {{r}} |
5. Query with OR and AND conditions
Let’s suppose we’d like to get data about all employees either having at least one kid or whose first name begins with the letter “D”. On top of that, we only want employees who started working from the January 1st, 2014. We’d like to have data about all attributes.
SELECT *
FROM Employee
WHERE (Kids_number >= 1 OR First_name LIKE 'D%') AND Start >= '2014-01-01'
We get the following table.
| {{h}} |
|---|
| {{r}} |
Recap
Basic SQL queries use SELECT, FROM and WHERE clauses.
SELECT is for indicating the attributes (columns) we’d like to get data about. The symbol * in the SELECT means that we’d like to get data about all the attributes. FROM is for indicating the table containing the data. Finally WHERE filters the records satisfying one or several conditions.
IN allows to write more concise code. For example ... WHERE Kids_number=1 OR Kids_number=3 is equivalent to ... WHERE Kids_number IN (1,3).