SQL Exercises. SQL Basics for Beginners with Lessons Learning SQL Queries

Birthday people:
Charyshkin P.P. (PeterChar)
Health and success!
Upcoming birthdays. There are exercises on the operator on the site SELECT(149 exercises at the training stage and 234 at the rating stage) and for other data manipulation operators - - (currently 41 exercises). For exercises on SELECT The participants are rated. Look
Test conditions

Today we have 1730 participants ( 219 new). Problems solved at the rating stage: 119 (35 by SELECT and 84 by DML), at the training stage - 3612

Practical knowledge of SQL language

The site will help anyone who wants to acquire or improve their skills in writing language data manipulation operators SQL. The essence of the training is that you yourself write statements that must return or change the data required by the task. In this case, if the answer is incorrect, you will be able to find out what data the correct request returns, and also see what your request returned. In addition, it is possible to execute any operators DML to existing databases by disabling the check option. The exercises have different difficulty levels (from 1 to 5), which are indicated in the second column of the list of exercises. Exercises on data sampling are offered (operator SELECT) and data modification exercises (operators INSERT, UPDATE, DELETE and MERGE). Based on the results of solving problems, a rating of participants is maintained on the site. In this case, the sampling exercises are divided into three stages: the first (6 exercises) without time control for completing a separate task, the second (starting with exercise 7) - with time control for completing each task. In the third stage, which is called optimization and starts with problem 139, it is required not only to solve the problem correctly, but also the time to complete the request must be commensurate with the time to complete the author’s solution.
The exercises of the first stage are available without registration, and the tasks can be solved in any order. Registration is required to complete the remaining exercises. Registration is free, like all other services on the site. In the third column of the list of exercises, the numbers of correctly completed exercises will be marked (“OK”) for registered visitors. Having visited our site subsequently, you will not need to remember which exercises you have already completed and which ones you have not. Once registered, you subsequently enter the username and password specified during registration. If you log in without authorization, the system will not track your progress. A forum is available for authorized users where you can discuss solutions to the proposed exercises.

NOTE: An incorrectly formulated query may return "correct" data in the current state of the database. Therefore, you should not be surprised if the results of an incorrect query match the correct results, but the query is assessed as incorrect by the verification system.

ATTENTION: For the site to function correctly, your browser must allow the use of Cookies and JavaScript.
Because help pages open in a subwindow, your Web filter, if used, must allow subwindows to open.

Certification

Based on the test results, you can order on the website certificate"SQL Data Manipulation Language Specialist" confirming your qualifications. We maintain the quality of the certificate by periodically replacing tasks and increasing certification requirements.

SQL Syntax Used

Visitor queries are actually executed by the SQL server, which places restrictions on the valid statement syntax. We currently use Microsoft SQL Server 2017, and at the training stage - additionally MariaDB-10.2.13 (MySQL 8 compatible), PostgreSQL 10.3 And Oracle Database 11g. Therefore, the user needs to adhere to the syntax of these implementations when writing their queries. Note that the syntax of the SQL language implemented in Microsoft SQL Server is quite close to the standard SQL-92. However, there are a number of deviations, among which we can note the lack of a natural connection of tables (NATURAL JOIN). The help on the SQL data manipulation language available on the site, in accordance with the standard, contains the necessary information for learning the language and performing exercises. There you can also find the specifics of the implementation used (SQL Server).

top scores

Person	Scores	Days	Days_2	Days_3	Scores_3
Krasovsky E.A. (pegoopik)	671	3289	210.553	14.398	250
Kostomarov A.V. (al29)	647	2617	4143.636	2280.923	250
Doshchenko V.N. (mcrain)	630	2759	2035.474	520.375	248

Most modern web applications interact with databases, usually using a language called SQL. Luckily for us, this language is very easy to learn. In this article we will look at simple SQL queries and learn how to use them to interact with MySQL database.

What will you need?

SQL (Structured Query Language) a language specifically designed to interface with database management systems such as MySQL, Oracle, Sqlite and others... To complete SQL requests in this article, I advise you to install MySQL to your local computer. I also recommend using phpMyAdmin as a visual interface.

All this is available in everyone's favorite Denver. I think everyone should know what it is and where to get it :). Can also use WAMP or MAMP.

Denver has a built-in MySQL console. This is what we will use.

CREATE DATABASE:database creation

Here is our first request. We will create our first database for further work.

To begin, open MySQL console and log in. For WAMP The default password is empty. That is, nothing :). For MAMP - "root". For Denver, we need to clarify.

After login, enter the following line and click Enter:

CREATE DATABASE my_first_db;

Note that a semicolon (;) is added at the end of the query, just like in other languages.

Also commands in SQL case sensitive. We write them in capital letters.

Options formally: Character SetAnd Collation

If you want to install character set (character set) and collation (comparison) can be write the following command:

CREATE DATABASE my_first_db DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;

Finds a list of character sets that are supported in MySQL.

SHOW DATABASES:displays a list of all databases

This command is used to list all available databases.

DROP DATABASE:deleting a database

You can delete an existing DB using this query.

Be careful with this command as it runs without warning. If there is data in your database, it will all be deleted.

USE:Database selection

Technically this is not a query, but a statement and does not require a semicolon at the end.

It tells MySQL select the default database for the current session. Now we are ready to create tables and do other things with the database.

What is a table in a database?

You can represent the table in the database as Excel file.

Just like in the picture, tables have column names, rows and information. By using SQL queries we can create such tables. We may also add, read, update and delete information.

CREATE TABLE: Creating a table

C Using this query we can create tables in the database. Unfortunately, the documentation MySQL not very clear for beginners on this issue. The structure of this type of query can be very complex, but we'll start with something easy.

The following query will create a table with 2 columns.

CREATE TABLE users (username VARCHAR(20), create_date DATE);

Please note that we can write our queries on multiple lines and with tabs for indentation.

The first line is simple. We simply create a table called "users". Next, in parentheses, separated by commas, is a list of all columns. After each column name we have information types, such as VARCHAR or DATE.

VARCHAR(20) means that the column is of type string and can be a maximum of 20 characters in length. DATE is also an information type that is used to store dates in the following format: "YYYY - MM-DD".

PRIMARY KEY ( primary keyh)

Before we run the next query, we must also include a column for "user_id", which will be our primary key. You can think of PRIMARY KEY as information that is used to identify each row in a table.

CREATE TABLE users (user_id INT AUTO_INCREMENT PRIMARY KEY, username VARCHAR(20), create_date DATE);

INT makes a 32-bit integer type (for example, numbers). AUTO_INCREMENT automatically generates a new value ID every time we add new series of information. This is not necessary, but it makes the whole process easier.

This column does not have to be an integer value, but it is most often used. Having a Primary Key is also optional, but is recommended for database architecture and performance.

Let's run the query:

SHOW TABLES:show all tables

This query allows you to get a list of tables that are in the database.

EXPLAIN:Show table structure

To show the structure of an existing table, you can use this query.

Columns are displayed with all properties.

DROP TABLE:delete table

Same as DROP DATABASES, this query deletes the table and its contents without warning.

ALTER TABLE: change table

This query can also contain complex structure due to the greater number of changes it can make to the table. Let's look at examples.

(if you deleted the table in the previous step, create it again for tests)

ADDING A COLUMN

ALTER TABLE users ADD email VARCHAR(100) AFTER username;

Due to the good readability of SQL, I think there is no point in explaining it in detail. We are adding a new column "email" after "username".

REMOVING A COLUMN

It was also very easy. Use this request with caution because your data may be deleted without warning.

Restore the column you just deleted for further experiments.

MAKING CHANGES IN A COLUMN

Sometimes you may want to make changes to the properties of a column, and you don't have to completely delete it to do this.

This query renamed the user column to "user_name" and changed its type from VARCHAR(20) to VARCHAR(30). This change should not change the data in the table.

INSERT: Adding Information to a Table

Let's add some information to the table using the following query.

As you can see, VALUES() contains a list of values ​​separated by commas. All values ​​are enclosed in single columns. And the values ​​must be in the order of the columns that were defined when the table was created.

Notice that the first value is NULL for the PRIMARY KEY field called "user_id". We do this so that the ID is generated automatically, since the column has the AUTO_INCREMENT property. When information is added for the first time the ID will be 1. The next row will be 2, and so on...

ALTERNATIVE OPTION

There is another query option for adding rows.

This time we are using the SET keyword instead of VALUES and it does not have parentheses. There are several nuances:

You can skip the column. For example, we didn't assign a value to "user_id", which would default to its AUTO_INCREMENT value. If you omit a column with a VARCHAR type, then an empty row will be added.

Each column must be referred to by name. Because of this, they can be mentioned in any order, unlike the previous version.

ALTERNATIVE OPTION 2

Here's another option.

Again, since there are references to the column name, you can set the values ​​in any order.

LAST_INSERT_ID()

You can use this query to get the ID that was AUTO_INCREMENT for the last row of the current session.

NOW()

Now it's time to show how you can use the MySQL function in queries.

The NOW() function displays the current date. So you can use it to automatically set the date of a column to the current one when you insert a new row.

Please note that we received 1 warning, but please ignore it. The reason for this is that NOW() also serves to output temporary information.

SELECT: Reading data from a table

If we add information to a table, then it would be logical to learn how to read it from there. This is where the SELECT query will help us.

Below is the simplest possible SELECT query to read a table.

In this case, the asterisk (*) means that we have requested all fields from the table. If you only want certain columns, the query would look like this.

ConditionWHERE

Most often, we are not interested in all columns, but only in some. For example, let's assume that we only need an email address for the user "nettuts".

WHERE allows you to set conditions in a query and make detailed selections.

Note that for equality, one equal sign (=) is used, not two, as in programming.

You can also use comparisons.

AND or OR can be used to combine conditions:

Note that numeric values ​​must not be in quotes.

IN()

This is useful for sampling on multiple values

LIKE

Allows you to make "wildcard" requests

The % icon is used as a "wildcard". That is, anything could be in its place.

ConditionORDER BY

If you want to get the result in an ordered form according to any criterion

The default order is ASC (smallest to largest). For the opposite, DESC is used.

LIMIT ... OFFSET ...

You can limit the number of results received.

LIMIT 2 takes only the first 2 results. LIMIT 1 OFFSET 2 gets 1 result after the first 2. LIMIT 2, 1 means the same thing (just note offset comes first and then limit ).

UPDATE: Make changes to the information in the table

This query is used to change information in a table.

In most cases, it is used in conjunction with a WHERE clause, since you will most likely want to make changes to certain columns. If there is no WHERE clause, the changes will affect all rows.

You can also use LIMIT to limit the number of rows to which changes need to be made.

DELETE: Removing information from a table

Just like UPDATE, this query is used with WHERE:

To delete the contents of a table, you can simply do this:

DELETE FROM users;

But it's better to use TRUNCATE

In addition to deleting, this request also resets values AUTO_INCREMENT and when adding rows again, the countdown will start from zero. DELETE does not do this and the countdown continues.

Disabling Lowercase Values ​​and Special Words

String values

Some characters need to be disabled ( escape ), or there may be problems.

A backslash is used for this.(\).

Special words

Because in MySQL there are many special words ( SELECT or UPDATE ), to avoid errors when using them, you must use quotes. But not ordinary quotes, but like this(`).

That is, you will need to add a column named " delete ", you need to do it like this:

Conclusion

Thank you for reading to the end. I hope you found this article helpful. It's not over yet! To be continued:).

Welcome to my blog site. Today we’ll talk about sql queries for beginners. Some webmasters may have a question. Why learn sql? Isn't it possible to get by?

It turns out that this will not be enough to create a professional Internet project. Sql is used to work with databases and create applications for WordPress. Let's look at how to use queries in more detail.

What it is

Sql is a structured query language. Designed to determine the type of data, provide access to it and process information in short periods of time. It describes the components or some results that you want to see on the Internet project.

To put it simply, this programming language allows you to add, change, search and display information in the database. The popularity of mysql is due to the fact that it is used to create dynamic Internet projects that are based on a database. Therefore, to develop a functional blog, you need to learn this language.

What can it do

The sql language allows you to:

• create tables;
• change to receive and store various data;
• combine information into blocks;
• protect data;
• create requests in access.

Important! Once you understand sql, you can write applications for WordPress of any complexity.

What structure

The database consists of tables that can be presented as an Excel file.

It has a name, columns and a row with some information. You can create such tables using sql queries.

What you need to know

Key Points to Learn Sql

As noted above, queries are used to process and enter new information into a database consisting of tables. Each line is a separate entry. So, let's create a database. To do this, write the command:

Create database 'bazaname'

We write the database name in Latin in quotes. Try to come up with a clear name for it. Do not create a database like “111”, “www” and the like.

After creating the database, install:

SET NAMES 'utf-8'

This is necessary for the content on the site to be displayed correctly.

Now let's create a table:

CREATE TABLE 'bazaname' . 'table' (

id INT(8) NOT NULL AUTO_INCREMENT PRIMARY KEY,

log VARCHAR(10),

pass VARCHAR(10),

date DATE

In the second line we wrote three attributes. Let's see what they mean:

• The NOT NULL attribute means that the cell will not be empty (the field is required);
• The AUTO_INCREMENT value is auto-completion;
• PRIMARY KEY - primary key.

How to add information

To fill the fields of the created table with values, the INSERT statement is used. We write the following lines of code:

INSERT INTO 'table'

(login, pass, date) VALUES

('Vasa', '87654321', '2017-06-21 18:38:44');

In brackets we indicate the names of the columns, and in the next - the values.

Important! Maintain consistency in column names and values.

How to update information

To do this, use the UPDATE command. Let's see how to change the password for a specific user. We write the following lines of code:

UPDATE 'table' SET pass = '12345678' WHERE id = '1'

Now change the password ‘12345678’. Changes occur in the line with “id”=1. If you do not write the WHERE command, all lines will change, not a specific one.

I recommend that you purchase the book " SQL for dummies " With its help, you can work professionally with the database step by step. All information is structured according to the principle from simple to complex, and will be well perceived.

How to delete an entry

If you wrote something wrong, correct it using the DELETE command. Works the same as UPDATE. We write the following code:

DELETE FROM 'table' WHERE id = '1'

Sampling information

To retrieve values ​​from the database, use the SELECT command. We write the following code:

SELECT * FROM 'table' WHERE id = '1'

In this example, we select all available fields in the table. This happens if you enter an asterisk “*” in the command. If you need to select some sample value, write this:

SELECT log , pass FROM table WHERE id = '1'

It should be noted that the ability to work with databases will not be enough. To create a professional Internet project, you will have to learn how to add data from a database to pages. To do this, familiarize yourself with the PHP web programming language. It will help you with this cool course by Mikhail Rusakov .

Deleting a table

Occurs using a DROP request. To do this, we will write the following lines:

DROP TABLE table;

Displaying a record from a table based on a specific condition

Consider this code:

SELECT id, countri, city FROM table WHERE people>150000000

It will display records of countries with a population of more than one hundred and fifty million.

An association

It is possible to link several tables together using Join. See how it works in more detail in this video:

PHP and MySQL

Once again I want to emphasize that requests when creating an Internet project are commonplace. To use them in PHP documents, follow the following algorithm:

• Connect to the database using the mysql_connect() command;
• Using mysql_select_db() we select the desired database;
• We process the request using mysql_fetch_array();
• Close the connection with the mysql_close() command.

Important! Working with a database is not difficult. The main thing is to write the request correctly.

Beginner webmasters will think about it. What should you read on this topic? I would like to recommend Martin Graber's book " SQL for mere mortals " It is written in such a way that beginners will understand everything. Use it as a reference book.

But this is a theory. How does this work in practice? In reality, an Internet project must not only be created, but also brought to the TOP of Google and Yandex. The video course will help you with this “ Website creation and promotion ».

Video instruction

Still have questions? Watch the online video for more details.

Conclusion

So, figuring out how to write sql queries is not as difficult as it seems, but any webmaster needs to do this. The video courses described above will help with this. Subscribe to my VKontakte group to be the first to know when new interesting information appears.