79 examples of SQL requests

A cheat list of various types of SQL queries that can become helpful templates in solving your problems

CREATE DATABASE IF NOT EXIST `new_base` --will create a new database "new_base" if it does not exist
USE new_base --now let's choose our base "new_base"

/*
Create a new table with columns id name and date, and give them the basic types of columns
*/
CREATE TABLE `users` (`id` INT UNSIGNED PRIMARY KEY AUTO_INCREMENT, `name` VARCHAR(20), `age` TINYINT UNSIGNED);

/*
And when do we need to clear the table?
*/
TRUNCATE TABLE names; -- clear the table "names"

/*
And if you want to delete the table completely?
*/
DROP TABLE names; -- delete the table names
    DROP DATABASE (IF EXIST) `new_database`; --delete "new_database" database completely

/*
Add the "salary" column to the "users" table after the age column. And set the parameters for the column DECIMAL(7,2) NOT NULL DEFAULT `0.00`
*/
ALTER TABLE `users` ADD `salary` DECIMAL(7,2) NOT NULL DEFAULT `0.00` AFTER `age`;

SELECT – command that allows you to select data in a table

    SELECT * FROM `Users` --allows you to select all records (symbol *) from the "Users" table
    SELECT Code, Name FROM `country` --list through a comma all the columns you want to get from "country" table
        SELECT DISTRICT Name FROM `country`-- DISTRICT will help not to deduce repetition of elements
        SELECT DISTRICT Name FROM `country` LIMIT 5 --LIMIT 5 will display no more than 5 records
        SELECT DISTRICT Name FROM `country` LIMIT 5 OFFSET 5 --Output only 5 records, starting with number 5
        SELECT DISTRICT Name FROM `country` LIMIT 5,5 --Analog of the upper term
     

       /*
       ORDER BY - tidies up our data
       */
        SELECT Name, Population FROM `country` ORDER BY Population --Entries will be sorted by column "Population" from smallest to largest
        /*
        You can sort by 2 columns at a time. First, the rows will be sorted by the first column "Region", and then within it by the column 
        "Population"
        */
        SELECT Region, Population, Name FROM country ORDER BY Region, Population;
        SELECT Name, Population FROM country ORDER BY Population DESC --Сhange the sort from larger to smaller
        SELECT Name, Population FROM country ORDER BY Population ASC --Return sort from lower to highest (default)
        SELECT Region, Population, Name FROM country ORDER BY Region DESC, Population --The "Region" field will be sorted from larger to smaller


        /*
        WHERE - sorts our data
        */
         SELECT Name, Population FROM country WHERE Population > 100000000000 ORDER BY Population DESC LIMIT 5 --WHERE is used to sort data. In this query, we select countries with a population of more than 1,000,000,000 people and separate them from larger to smaller
         
         /* WHERE can use the following operations
         = - exactly
         > - more
         <- less
         >= - more evenly
         <= - less than equal
         <>,! = - is not equal
         BETWEEN - between WHERE Population values BETWEEN 100000000 AND 200000000
         IS NULL - when WHERE IndepYear IS NULL is not set for the field, WHERE IndepYear IS NOT NULL
         */

INSERT allows you to insert data into a table:

--Insert in the Products table in the column Product Name, Manufacturer, Price 3 new product
INSERT INTO Products (ProductName, Manufacturer, Price)
	VALUES ('SONY XPERIA2', 'SONY', 10), ('SONY XPERIA3', 'SONY', 20), ('SONY XPERIA4', 'SONY', 30);
-- !If NULL is allowed in the column, it can be omitted

--Copy data from another table
INSERT INTO Products_copy (Id, ProductName, Manufacturer, ProductCount, Price)
	SELECT Id, ProductName, Manufacturer, ProductCount, Price FROM Products;

Operators AND OR IN NOT LIKE AS

    SELECT Name, Population, IndepYear FROM country WHERE IndepYear IS NULL AND Population > 1000000 --Data can be sorted by multiple columns using the AND statement. Lines that meet both conditions will be selected
    SELECT Name, Population, IndepYear FROM country WHERE IndepYear IS NULL OR Population > 1000000 --The AND operator can be replaced by the OR operator and data that meet at least one condition will be selected
    -- !AND has a higher priority than OR so this condition is met first. This can often cause incorrect data.
    -- To avoid this error, you need to use parentheses as shown in the example below
    SELECT * FROM country WHERE (IndepYear IS NULL OR Population > 4000000) AND Continent = 'North America' ORDER BY Population;
    SELECT * FROM country WHERE Code IN ('ABW', 'AFG', 'AGO') --The IN operator is used to select multiple values
    SELECT Name, Population FROM country WHERE NOT Population > 1000000000 ORDER BY Population DESC --The NOT operator will help you choose opposite values
    
    /*
    The LIKE operator will allow you to select all occurrences in which this phrase is present. The% symbol will replace any characters
    */
    SELECT Name, Region FROM country WHERE Region LIKE '%estern%';
    SELECT Name, Region FROM country WHERE Name LIKE 'A%a';
    -- ! Symbol "_" replaces any one character in a string
    

CONCAT – combines the values from the column and fills them with a new pseudostovets

     SELECT CONCAT(Name, ', ', Continent, ', ', Region) AS full_name FROM country LIMIT 10 -- The CONCAT function combines values from a column and fills them with a new pseudo-column full_name ',' acts as a delimiter
     /*
     Another example of creating a new SurfaceArea pseudo-column in which the result of dividing the Population column by SurfaceArea is entered
     */
     SELECT Name, SurfaceArea, Population, (Population / SurfaceArea) AS Density FROM country ORDER BY Population DESC LIMIT 10;

Other frequently used functions

SELECT CONCAT_WS(', ', Name, Continent, Region) AS full_name FROM country LIMIT 10 --CONCAT-like function, which immediately specifies the desired delimiter (first parameter)
SELECT Name, FORMAT(SurfaceArea, 1, 'de_DE') AS area_de, FORMAT(SurfaceArea, 1, 'de_DE') AS area_ru, Population FROM country ORDER BY Population DESC LIMIT 10 --Formats value 1 - indicates the number of decimal places. de_DE character locale (often varies from country to country)
SELECT Name, UCASE(Name) AS u1, LCASE(Name) AS l1 FROM country LIMIT 10 --UCASE translates a string to uppercase, LCASE translates it to lowercase

SELECT LTRIM('   test   ') -- The function will remove the spaces on the left
SELECT RTRIM('   test   ') -- The function will remove the spaces on the right
SELECT TRIM('   test   ') -- The function removes spaces from the left and from the right

--CEIL - rounds the number up
SELECT Name, SurfaceArea, Population, (Population / SurfaceArea) AS Density, CEIL(Population / SurfaceArea) AS Density2 FROM country ORDER BY Population DESC LIMIT 10
--FLOOR - rounds the number down
SELECT Name, SurfaceArea, Population, (Population / SurfaceArea) AS Density, FLOOR(Population / SurfaceArea) AS Density2 FROM country ORDER BY Population DESC LIMIT 10;
--ROUND rounds a number according to the rules of mathematics, you can specify how many decimal places to leave
SELECT Name, SurfaceArea, Population, (Population / SurfaceArea) AS Density, ROUND( (Population / SurfaceArea), 1) AS Density2 FROM country ORDER BY Population DESC LIMIT 10;

SELECT NOW() --NOW - gets the current date on the server
SELECT CURDATE() --gets the current date and time on the server
     SELECT DATE_FORMAT(NOW(), '%d %M %Y') --allows you to format the date

SELECT COUNT(*) FROM country --Allows you to get the number of rows in the "country" tabble
     SELECT COUNT(LifeExpectancy) FROM country --Skips when in the NULL field
     SELECT SUM(quantity) AS q, SUM(quantity * price) AS t FROM oc_order_product
	WHERE order_id = 155

SELECT MIN(Population) FROM country WHERE Population > 0 --Selects the minimum value in the column
SELECT AVG(Population) FROM country --Returns the average value in a column

SELECT order_id, SUM(quantity), SUM(quantity * price) AS t FROM oc_order_product GROUP BY order_id --Allows you to sort the data by the order_id column
SELECT order_id, SUM(quantity), SUM(quantity * price) AS t FROM oc_order_product WHERE order_id > 156 GROUP BY order_id HAVING COUNT(*) = 1 --Allows you to sort the data by the order_id column, the number of which will be equal to 1

One request can be nested in another. The nested query is executed first

SELECT Name FROM country WHERE Code IN (
	SELECT CountryCode FROM countrylanguage WHERE Language = 'Dutch'
)
SELECT order_id FROM oc_order WHERE email = 'antony@mail.com';
SELECT order_id, SUM(quantity * price) AS t FROM oc_order_product WHERE order_id IN (160, 163, 164) GROUP BY order_id;
SELECT order_id, SUM(quantity * price) AS t FROM oc_order_product WHERE order_id IN (
	SELECT order_id FROM oc_order WHERE email = 'vasya@mail.com'
) GROUP BY order_id;

Let’s take data from different tables

SELECT 
	order_id, 
	SUM(total) AS t,
	(SELECT email FROM oc_order WHERE oc_order_product.order_id = oc_order.order_id) AS email
FROM oc_order_product GROUP BY order_id;

Merge tables

SELECT city.Name, country.Name AS country FROM city, country WHERE country.Code = city.CountryCode;

SELECT Customers.FirstName, Products.ProductName, Orders.CreatedAt
	FROM Orders, Customers, Products
		WHERE Orders.CustomerId = Customers.Id AND Orders.ProductId = Products.Id;
		
SELECT C.FirstName, P.ProductName, O.CreatedAt
	FROM Orders AS O, Customers AS C, Products AS P
		WHERE O.CustomerId = C.Id AND O.ProductId = P.Id;

/* Merge tables using JOIN*/

SELECT city.Name, country.Name AS country FROM city, country WHERE country.Code = city.CountryCode;
можна замінити JOIN
SELECT city.Name, country.Name AS country FROM city JOIN country ON country.Code = city.CountryCode;

SELECT city.Name, country.Name AS country, country.Code, countrylanguage.`Language`
 FROM country
  JOIN city ON country.Code = city.CountryCode
  JOIN countrylanguage ON countrylanguage.CountryCode = country.Code
  	WHERE countrylanguage.IsOfficial = 'T';

LEFT JOIN -- works similarly to usual JOIN but deduces all records, and in which there is no data puts NULL
     SELECT city.Name, country.Name AS country, country.Code, countrylanguage.`Language`
 FROM country
  LEFT JOIN city ON country.Code = city.CountryCode
  LEFT JOIN countrylanguage ON countrylanguage.CountryCode = country.Code
  	WHERE countrylanguage.IsOfficial = 'T';

/* The UNION operator combines 2 data from two tables and discards duplicates */
SELECT FirstName, LastName FROM Clients
UNION
SELECT FirstName, LastName FROM Employees

UNION ALL -- selects all and even duplicate entries
SELECT FirstName AS fn, LastName AS ln, 'client' AS role FROM Clients
UNION
--Сan select data in one table
SELECT FirstName, LastName, 'employee' AS role FROM Employees
ORDER BY ln
SELECT Name, Population FROM country WHERE Continent = 'Europe' AND Population > 50000000
	UNION
SELECT Name, Population FROM country WHERE Continent = 'Asia' AND Population > 100000000
	ORDER BY Population DESC

Copy another table

CREATE TABLE Products_copy AS SELECT * FROM Products;
--! SELECT does not save column properties (eg auto_increment)
CREATE TABLE Products_copy (Id INT AUTO_INCREMENT PRIMARY KEY) AS SELECT * FROM Products --set autoincrement in the request
CREATE TABLE Products_copy LIKE Products --copies with data

Delete, change, add data to the table

DELETE FROM Products_copy WHERE Id > 11 --Deletes data from the table
ALTER TABLE Products_copy MODIFY Manufacturer VARCHAR(255) --Change the table column with the ALTER TABLE command
ALTER TABLE Products_copy ADD test INT --Add a column to the table
DROP TABLE Products_copy --Delete the table

Representation

--VIEW command create virtual table
CREATE VIEW city_info AS
	SELECT city.Name AS city_name, country.Name AS country_name, country.Code, countrylanguage.Language 
	    FROM country
	     JOIN city ON city.CountryCode = country.Code
	     JOIN countrylanguage ON countrylanguage.CountryCode = country.Code
	     WHERE countrylanguage.IsOfficial = 'T';

SELECT * FROM city_info --we can refer to the virtual table by conventional methods

DROP VIEW IF EXISTS city_info --delete the representation

Stored procedures

--That is, in essence, stored procedures represent a set of instructions that are executed as a single unit
--To create a stored procedure, use the CREATE PROCEDURE and CREATE PROC commands.
DELIMITER //
CREATE PROCEDURE city_info()
BEGIN
	SELECT city.Name AS city_name, country.Name AS country_name, country.Code, countrylanguage.Language 
	    FROM country
	     JOIN city ON city.CountryCode = country.Code
	     JOIN countrylanguage ON countrylanguage.CountryCode = country.Code
	     WHERE countrylanguage.IsOfficial = 'T';   
END //

EXEC city_info -- perform the procedure city_info
DROP PROCEDURE city_info -- delete procedure city_info

--transfer parameters to procedure

DELIMITER //
CREATE PROCEDURE city_info(l INT)
BEGIN
    SELECT city.Name AS city_name, country.Name AS country_name, country.Code, countrylanguage.Language 
    FROM country
     JOIN city ON city.CountryCode = country.Code
     JOIN countrylanguage ON countrylanguage.CountryCode = country.Code
     WHERE countrylanguage.IsOfficial = 'T' LIMIT l;
END //


CALL city_info(5)