SQL Basics for Beginners

Hello everyone Let's take a brief look at the basics of SQL. Let's find out what SQL is, what commands, methods and features there are.

Let's start!

Basic terminology

Database - is some organized set of information.

Relational database - is a database built on a relational data model (according to the mathematical theory of relations).

Database management system (DBMS) - is a set of programs that allows you to manage the creation and use of a database.

SQL Language

SQL (structured query language) - is a programming language designed to work with relational databases.

The peculiarity of SQL - is a declarative programming language, we describe what we want to get, not how

Types of SQL statements

Groups of SQL statements:

• Operators for working with database objects.
• Data manipulation operators.
• Transaction Management Commands
• Data protection and management operators.

We will consider only data manipulation operators, because it is with them that you will most often meet in the course of your work.

SELECT request. Basic syntax

• Minimum query:

SELECT * FROM table WHERE condition;

• "Separate" and "order by" operators:

SELECT DISTINCT f1, f2 FROM table
WHERE condition ORDER BY f2 DESC;

• Graph operator:

SELECT QUANTITY (*) FROM table;

Subqueries

Example: print the count of different values in the "f1" field of the table.

SELECT COUNT(*) FROM (SELECT DISTINCT f1 FROM table WHERE condition);

The subquery is written in parentheses, the result of its work is a table.

The exists operator

A quick way to check if a query returns at least one value.

SELECT smth FROM table 
WHERE EXISTS (SELECT * FROM table2 WHERE table2.field = table.field1);

Sometimes it is convenient to use aliases:

SELECT field1 as f1, field2 as f2
FROM table AS first_table
WHERE EXISTS
(SELECT * FROM table2 AS second_table
WHERE second_table.field = first_table.f1);

Group by operator

SELECT field, COUNT(*) FROM table 
WHERE condition GROUP BY field HAVING having_condition;

In the HAVING operator, unlike where, aggregating functions can be used.

Some aggregating functions:

• SUM
• COUNT
• MIN
• MAX

Merging tables

SELECT column_name(s) FROM table1
UNION (UNION ALL)
SELECT column_name(s) FROM table2

Keys

Types of key relationships:

• 1 to 1
• 1 to many
• many to 1

Many to many relationship

JOIN operator

Changing the contents of tables

• Adding an element:

INSERT INTO table_name values (v1, v2, …);
INSERT INTO table_name (col1, col2) VALUES (v1, v2);

• Updating an element:

UPDATE table_name SET col1 = val1, col2 = val2 WHERE condition;

• Deleting an element:

DELETE FROM table_name WHERE condition;

I hope my efforts will help someone to deal with such a topic as SQL.

I wish you good luck!

FAQ

I am a beginner, how should I learn Python?

Look into the following series:

Would you mentor me?

Of course I am ready to participate in the training. The big difficulty is that English is not my native language and it will be possible to communicate through a translator

Would you like to collaborate on our organization?

If you have interesting ideas, we will be happy to invite your writing from our organization. Write in private messages or in social networks below

Connect to me on