DML¶

In this section, you will learn about possible ways to manipulate data in existing tables. The keywords INSERT, SELECT, UPDATE and DELETE will be discussed.

Insert¶

Entering data into created tables is possible with the command INSTER INTO. The basic version of the query looks like this:

sql INSERT INTO table [(column1, column2, ..., columnN)] VALUES (value1, value2, ..., valueN); `

This command allows you to enter a new row in the table, where each value corresponds to a column with an analogous position:

• value1 -> column1
• value2 -> column2
• valueN -> column3

For the table below:

CREATE TABLE Product
(
    ProductId INTEGER PRIMARY KEY,
    Name VARCHAR(40),
    Description VARCHAR(255)
);

An example of data entry will look like this:

INSERT INTO Product (ProductId, Name, Description) VALUES (1, 'Macbook Pro 16', 'Late 2019');

It is worth noting that if you want to enter data that is literal characters, (that is, enter data into a column of type, e.g. VARCHAR or NVARCHAR) we have to enter it inside the characters '. Additionally we are not obliged to set the values of all columns (it can be, for example, the default value or automatically generated), but the order of the values matters.

Data can also be entered without specifying column names, e.g .:

INSERT INTO Product VALUES (1, 'Macbook Pro 16', 'Late 2019');

However, in the above case, the values must be passed in the order of the columns entered when creating the table (which can be obtained using the commands described here).

An alternate form of the INSERT statement¶

The second version of the INSERT statement looks like this:

INSERT INTO table SET column1=value , column2=value2, columnN=valueN;

The instruction in this form allows you to set specific fields without going into the order of columns and values:

INSERT INTO Product SET ProductId=2, Name='Macbook Pro 13', Description='Early 2018';

Introduction of multiple lines¶

With the INSERT statement, we also have the option of entering multiple lines (but this way of entering data is not supported by all relational databases). The instruction in this form looks almost identical to the basic one, but the subsequent records must be enclosed in parentheses (), i.e.

INSERT INTO table_name (column1, column2, ..., columnN) VALUES
(value1A, value2A, ..., valueNA),
(value1B, value2B, ..., valueNB);

An actual example might look like this:

INSERT INTO Product (ProductId, Description, Name) VALUES
(3, 'Dell XPS 13', 'New 2020 model'),
(4, 'Iphone XS', 'Apple Flaghip Model from 2018');

Select¶

All data stored in the tables can be retrieved using the SELECT statement. The instruction itself can be very complex due to the large number of available clauses.

SELECT column1, column2, ..., columnN FROM table [WHERE condition] [ORDER BY column1, column2, ..., columnN [ASC | DESC]];

The above instruction means that we want to retrieve a set of entered values for columns: column1, column2, ..., columnN from a table namedtable, where individual records must meet the condition condition, and the results should be sorted against the indicated columns in the ORDER BY clause, ascending ASC or descending DESC.

Other examples will be done according to the table:

CREATE TABLE Product
(
    ProductId INTEGER PRIMARY KEY,
    Name VARCHAR(40),
    Description VARCHAR(255)
);

INSERT INTO Product (ProductId, Name, Description) VALUES
(3, 'Dell XPS 13', 'New 2020 model'),
(4, 'Iphone XS', 'Apple flagship model from 2018');

Display all contents of the table¶

To display the entire contents of the table, execute the command:

SELECT * FROM Product;

The * operator means that we include the entire structure of the Product table.

The same command can be performed, sorting in ascending order:

SELECT * FROM Product ORDER BY Name ASC;

The sorting itself can be done on multiple columns. In this case, the columns and the type of sort are separated by commas, e.g .:

SELECT * FROM Product ORDER BY Name ASC, Description DESC;

NOTE: ASC is the default sort type, ieORDER BY column means the same as ORDER BY column ASC.

Display the table based on the selected columns¶

In order to display data from specific columns, indicate their names, separating them with a comma, e.g .:

SELECT Name, Description FROM Product;

The above query will display data only for the indicated columns, columns not included will be ignored.

Aliases¶

As part of SELECT queries, you can modify the names of the displayed columns, which is done using the so-called aliases. We define the alias by adding the keyword AS and the name of our choice after the column name, e.g .:

SELECT Name AS product_name, Description AS product_description FROM Product;

The above query will display the names product_name andproduct_description in the returned result, instead of the actual names of these columns.

Criteria for downloading data¶

Specifying specific criteria for data collection is performed using the WHERE clause. Within it, we can use many operators, both relational and logical.

Relational operators¶

_ NOTE: _ In SQL, an inequality can also be represented by the characters <>.

Logical operators¶

Examples¶

SELECT * FROM Product WHERE Name='Macbook';

The above query is responsible for finding all records whose name is Macbook.

SELECT * FROM Product WHERE Name='Macbook' AND Description LIKE 'Late%';

The above query is responsible for retrieving all records that have the name Macbook and the description indicates that the value starts with the charactersLate. The LIKE operator, in addition to the % character, which stands for many (including zero) characters, also allows you to use the _ character, which represents any single character.

SELECT * FROM Product WHERE ProductId BETWEEN 3 and 10;

The query above will find all products that have a 'ProductId' between 3 and 10. It is worth noting that both the lower and upper limits are included in the search.

SELECT * FROM Product WHERE Name IN('Macbook', 'Dell');

As part of the query above, you can retrieve all records whose Name is one of those defined within the IN operator. Remember that by using many values inside the IN set, the execution time of such a query can increase significantly.

Limiting the number of records¶

The limitation of the returned records can be implemented with the use of the LIMIT clause, e.g .:

SELECT * FROM Product LIMIT 10;

The above query will allow you to return the first '10' records from the 'Product' table.

Within the same clause, we can restrict the retrieval of data starting from a specific item:

SELECT * FROM Product LIMIT 4,3; -- the first number indicates the position, the second number of records

The above query allows you to return records from the Product table, starting from item 4.

Update¶

The data in the tables can be modified or changed using the UPDATE statement, which looks like this:

UPDATE table SET column1=value1, column2=value2, ..., columnN=valueN [WHERE condition];

The above query allows you to set specific values for columns: column1,column2, column3 with optional conditioncondition.

The following command will allow you to change the name of each product from the Product table to the specific value indicated in the query:

UPDATE Product SET Name='Macbook Pro';

In order to update a specific table row, use the WHERE clause:

UPDATE Product SET Name='Macbook Pro' where Name='Macbook';

In the example above, only the records named Macbook will be renamed.

Delete¶

Deleting data from a table is possible with the DELETE statement, which is structurally as follows:

DELETE FROM Product [WHERE condition];

This command allows you to remove content from the Product table under an optional condition defined ascondition.

DELETE FROM Product;

The command above removes all records from the Product table. The command below will delete all records that have the text Macbook in their name.

DELETE FROM Product WHERE Name LIKE `Macbook%`;

Within the WHERE clause, we can use any relational or logical operators, e.g.

DELETE FROM Product WHERE Name='Macbook' AND ProductId IN(2,3,10);