DEV Community

Cover image for SQL Basics for Beginners
Vadim Kolobanov for Abstract

Posted on • Updated on • Originally published at dev.to

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;
Enter fullscreen mode Exit fullscreen mode
  • "Separate" and "order by" operators:
SELECT DISTINCT f1, f2 FROM table
WHERE condition ORDER BY f2 DESC;
Enter fullscreen mode Exit fullscreen mode
  • Graph operator:
SELECT QUANTITY (*) FROM table;
Enter fullscreen mode Exit fullscreen mode

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);
Enter fullscreen mode Exit fullscreen mode

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);

Enter fullscreen mode Exit fullscreen mode

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);
Enter fullscreen mode Exit fullscreen mode

Group by operator

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

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
Enter fullscreen mode Exit fullscreen mode

Keys

Types of key relationships:

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

Keys


Many to many relationship

ManyToMany


JOIN operator

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);
Enter fullscreen mode Exit fullscreen mode
  • Updating an element:
UPDATE table_name SET col1 = val1, col2 = val2 WHERE condition;
Enter fullscreen mode Exit fullscreen mode
  • Deleting an element:
DELETE FROM table_name WHERE condition;
Enter fullscreen mode Exit fullscreen mode

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:

Learning Python
Step by Step to Junior
Ideas

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

Write me on Face....oh...Meta

My Twitter

Become a patron

Top comments (3)

Collapse
 
vadimkolobanov profile image
Vadim Kolobanov

Of course! No problem! I will be very glad if the material helps as many people as possible! Just don't forget to leave a link to the source. This will help attract even more

Collapse
 
arsalan0974 profile image
arsalan0974

Thank you sir

Collapse
 
vadimkolobanov profile image
Vadim Kolobanov

Always happy to help