SQL basic commands

 

DML – Data Manipulation Language

Create table:

CREATE TABLE nameoftable(id INTEGER PRIMARY KEY, name TEXT);

Add a row:

INSERT INTO nameoftable (nameOfcolumn1,2,3) VALUES (new1,new2,new3);

View the row you just created:

SELECT * FROM nameoftable;

Edit information in the table (for only existing records):

UPDATE nameofthetable
SET parameter = 22
WHERE id = 1;

Add new column in the table:

ALTER TABLE nameoftable ADD COLUMN nameofcolumn TEXT;

Delete rows with no value:

DELETE FROM name of t WHERE nameofcolumn IS NULL;
DELETE FROM nameoftable;
DROP TABLE IF EXISTS;
CREATE TABLE IF NOT EXISTS;

Return uniq values:

SELECT DISTINCT name of column FROM name of t;

Filter the result in rows only if condition is true:

SELECT * FROM nameoftable WHERE name of column > 8;

Operators:

= equals
!= not equals
> greater than
< less than
>= greater than or equal to
<= less than or equal to

Filter the result to compare similar values:

SELECT * FROM name of t WHERE name of column LIKE ‘set here your text parameter’;
 
i.g. ‘Se_en’ or ‘A%’ (begins with “a”) or ‘%a’ (ends with “a”) or %man% (contains this word)
 

SELECT * FROM name of t WHERE name of column BETWEEN ‘A’ AND ‘J’; (not incl ‘J”)
or BETWEEN 1990 AND 2000; (incl all years)
 

Combine operator AND:

SELECT * FROM movies
WHERE year BETWEEN 1990 and 2000
AND genre = ‘comedy’;)
 

OR operator:

SELECT * FROM movies
WHERE genre = ‘comedy’
OR year < 1980;

Sort the result:

SELECT * FROM name of t
ORDER BY name of column DESC;
 
DESC — from high to low, ASC — low to high

Limiting the results:

SELECT * FROM name of t
ORDER BY name of column ASC
LIMIT 3;

Functions

Calculate the number of rows:

SELECT COUNT(*) FROM nameoftable;
 

To combine the rows

CONCAT ( || ‘somethting’ ||):

SELECT *, CONCAT(age, name, salary) as concat FROM name of table;
SELECT ‘abc ‘ || ‘def’ || ‘ gh ‘ FROM DUAL;

Round to a whole number:

SELECT ROUND(column name, decimals кол-во знаков после запятой) from nameoftable;

SELECT ROUND(345.156, 2);

If null return your value:

SELECT name, NVL(id, 0) FROM nameoftable;

SUM of all the values in that column:

SELECT SUM(nameofcolumn) from nameoftabl;

Find the largest value in a column:

SELECT MAX(nameofcolumn) FROM nameoftabl;

Find the minimum value in a column:

SELECT MIN(nameofcolumn) FROM nameoftabl;

Find average value:

SELECT AVG(namecolumn) FROM nameoftable;
 

GROUP BY

is using with COUNT, MIN, MAX, AVG, SUM

 
AS — rename a column 

Multiple tables join

SELECT * FROM nameoftable1 JOIN nameoftable2 ON not1.id = not2.id;


  • left JOIN — every row in the lefttable is returned in the result set
  • right JOIN — every in the right
  • inner join=join

Union

merge the columns

SELECT column_name(s) FROM table1
UNION
SELECT column_name(s) FROM table2;
The UNION ALL clause allows us to utilize information from multiple tables in our queries, including duplicate values.
INTERSECT is used to combine two SELECT statements, but returns rows only from the first SELECT statement that are identical to a row in the second SELECT statement.
EXCEPT returns distinct rows from the first SELECT statement that aren’t output by the second SELECT statement

Dates

DATEDIFF
DATE_ADD(date, interval 45, DAY)
SELECT name_city, name_author, DATE_ADD('2020-01-01', INTERVAL (RAND() * 365) DAY) AS Date FROM city, author ORDER BY 1, Date DESC
Functionhow to useexample
count(x)
sum
max
CEILING(x)CEILING(4.2)=5
CEILING(-5.8)=-5
ROUND(x, k)ROUND(4.361)=4
ROUND(5.86592,1)=5.9
FLOOR(x)FLOOR(4.2)=4
FLOOR(-5.8)=-6
POWER(x, y)POWER(3,4)=81.0
SQRT(x)SQRT(4)=2.0
SQRT(2)=1.41…
ABS(x)ABS(-1) = 1
ABS(1) = 1
RAND()random