Course Module 4. Working with databases - Lecture: SQL language

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 namesthem 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, WHEREyou 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 BYand HAVINGwe 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.