SQL cheat sheet #1. DDL, DML and the first SELECT

SQL translated from English sounds like structured query language. It is a programming language designed to work with relational databases. The first SQL standard appeared in 1987, after which it was completed eight times. At the same time, the basis of the language has remained virtually unchanged since 1992. Nowadays, SQL continues to be one of the most powerful tools for manipulating relational data and creating complex reports. I will try all the advantages of this language on simple and understandable examples.

material part.

In order to learn SQL, you need to install database management software (DB) or get access to a running database server (MySQL, Postgresql, Oracle, MS SQL – even MS Access will do). One “small” nuance – the database must be relational (non-relational databases are a completely different story). All examples described in this article, I test on SQLite. It is a simple yet powerful open source database management engine. He actively applied around the world for a variety of purposes. For example, I very often use it as a local data store for UNIT tests in heavy projects. Due to its free and compact size, it is also ideal for learning SQL, because you do not need to spend time installing and configuring a database server, instead you can immediately write your first SQL query.

And now some theory.

The SQL standard provides for four groups of statements (in fact, these are subsets of the language), each of which has its own role.

DDL – data description language. With its help, fields, tables and other database objects are created and modified;
DML – data manipulation language. It is designed to add, delete, change data and make selections from the database;
DCL – a set of instructions for administering access to data;
TCL – Transaction Control Language – a set of instructions for managing transactions.

Training (from simple to complex).

I create a table that will store information about products.

Field	Data type
Item ID	Integer
Name of product	Text
Price per item	Decimal number
Unit of measurement of the quantity of goods	Text

In DDL, the table described above becomes a structure:

CREATE TABLE "product" (
	"product_id"	INTEGER NOT NULL UNIQUE,
	"product_name"	TEXT NOT NULL,
	"product_price"	REAL NOT NULL,
	"product_unit_name"	TEXT NOT NULL,
	PRIMARY KEY("product_id" AUTOINCREMENT)
);

Field product_id I define as a primary key, i.e. this field can uniquely identify each record in the table product . Then I fill the created table with test data (this is DML).

INSERT INTO "product" ("product_name", "product_price", "product_unit_name") VALUES ('Хлеб', 87, 'буханка');
INSERT INTO "product" ("product_name", "product_price", "product_unit_name") VALUES ('Молоко', 120, 'литр');
INSERT INTO "product" ("product_name", "product_price", "product_unit_name") VALUES ('Сахар', 53, 'кг');
INSERT INTO "product" ("product_name", "product_price", "product_unit_name") VALUES ('Рис', 94, 'кг');

And finally, I make the first select (this is also DML):

SELECT * 
  FROM product;

As a result, I learn:

Well, here it is – the first SELECT) By itself, it is not very useful. But all the strength and power of SQL, like all relational databases, is in relationships. This will be the next article.