2.1 Introduction to SQL
If you have a database (DB), and in it - a table with some data, then the most common task is to find certain data in this table. That's what SQL was invented for 40 years ago.
SQL stands for Structured Query Language .
The simplest SQL query looks like this:
SELECT column1, column2, … columnN FROM table
Let's say you have an employee table with a list of your startup's employees:
id | name | occupation | salary | age | join_date |
---|---|---|---|---|---|
1 | Ivanov Ivan | Programmer | 100,000 | 25 | 2012-06-30 |
2 | Petrov Petr | Programmer | 80,000 | 23 | 2013-08-12 |
3 | Ivanov Sergey | Tester | 40,000 | thirty | 2014-01-01 |
4 | Rabinovich Moisha | Director | 200,000 | 35 | 2015-05-12 |
5 | Kirienko Anastasia | Office Manager | 40,000 | 25 | 2015-10-10 |
6 | Vaska | cat | 1,000 | 3 | 2018-01-01 |
You want to write a query that will display employee names
them as well salaries
, then you need to write a query:
SELECT name, salary FROM employee
You will get the query result:
name | salary |
---|---|
Ivanov Ivan | 100,000 |
Petrov Petr | 80,000 |
Ivanov Sergey | 40,000 |
Rabinovich Moisha | 200,000 |
Kirienko Anastasia | 40,000 |
Vaska | 1,000 |
If you want to display all the columns of your table , then instead of listing the names of all the columns, you can simply write an asterisk. Example:
SELECT * FROM employee
You will get the query result:
id | name | occupation | salary | age | join_date |
---|---|---|---|---|---|
1 | Ivanov Ivan | Programmer | 100,000 | 25 | 2012-06-30 |
2 | Petrov Petr | Programmer | 80,000 | 23 | 2013-08-12 |
3 | Ivanov Sergey | Tester | 40,000 | thirty | 2014-01-01 |
4 | Rabinovich Moisha | Director | 200,000 | 35 | 2015-05-12 |
5 | Kirienko Anastasia | Office Manager | 40,000 | 25 | 2015-10-10 |
6 | Vaska | cat | 1,000 | 3 | 2018-01-01 |
2.2 Advanced view of SQL query
The SQL language was designed as user-friendly as possible.
First, the case of the request text does not matter . You can write SELECT, Select, or select and everything will work. Secondly, line breaks are not taken into account in any way . The DBMS will still turn the query into one long string, so you can write it however you like.
As you probably already guessed, the SELECT and FROM keywords are not limited. Otherwise there wouldn't be so much talk around SQL. The extended view of the SQL query looks like this:
SELECT columns
FROM table
WHERE condition
GROUP BY columns
HAVING columns
ORDER BY sorting
With the help of a keyword, WHERE
you can set a condition / filter for the selected rows.
Example 1 . Let's write a query that will select employees with the profession "Programmer":
SELECT * FROM employee WHERE occupation = 'Programmer'
And we get the following query result:
id | name | occupation | salary | age | join_date |
---|---|---|---|---|---|
1 | Ivanov Ivan | Programmer | 100,000 | 25 | 2012-06-30 |
2 | Petrov Petr | Programmer | 80,000 | 23 | 2013-08-12 |
As you can see, as a result of the query execution, only rows where the employee's profession is called "Programmer" are displayed.
The keywords GROUP BY
, ORDER BY
and HAVING
we will cover in the next lectures. And in this we will analyze a couple more examples with the word WHERE.
Example 2 . Now let's write a query that will show us all employees with a salary greater than 100K. Here's what it will look like:
SELECT * FROM employee WHERE salary > 100000
We get the following query result:
id | name | occupation | salary | age | join_date |
---|---|---|---|---|---|
4 | Rabinovich Moisha | Director | 200,000 | 35 | 2015-05-12 |
Example 3 . Now let's try something more difficult. How to display all employees who were hired in 2015? And like this:
SELECT * FROM employee WHERE YEAR(join_date) = 2015
We get the following query result:
id | name | occupation | salary | age | join_date |
---|---|---|---|---|---|
4 | Rabinovich Moisha | Director | 200,000 | 35 | 2015-05-12 |
5 | Kirienko Anastasia | Office Manager | 40,000 | 25 | 2015-10-10 |
In this query, we use a special function YEAR()
that allows us to get the year from a date and then compare the year of the date with the number 2015.
2.3 Comments in SQL queries
And one more important point is comments in SQL queries. Comments are a very useful thing. Firstly, you can write explanations and / or your ideas in them. Secondly, with the help of comments, you can disable broken code. Or comment out the old version of the code.
SQL, like Java, has single-line and multi-line comments. Moreover, a multi-line comment looks just like in Java. Its appearance:
/*
comment text
comment text
comment text
*/
Of course, it can be used in one line too. Example:
/*comment text*/
There is also a type of comment "from the beginning to the end of the line", similar to the Java "//". Only in SQL you need to write two minus characters and a space . General view of such a comment:
-- comment text
Example:
SELECT * FROM employee -- WHERE YEAR(join_date) = 2015
In the example above, we commented out the query condition, so MySQL will only execute the query:
SELECT * FROM employee
2.4 SQL pronunciation
If you communicate with foreign customers or pass an online interview for an American company, then you may be asked about your experience with the sequel language . You will honestly say that you did not work with him and immediately flunk the interview.
And the thing is that the SQL language was originally called SEQUEL, which is pronounced like a sequel (ˈsēkwəl), so very often in the USA and other English-language sources you will hear not “escuel”, but “sequel”. SQL is written, and the sequel is read. Here is such a historical paradox.