[Syntax] Data Manipulation Language

1. What is DML?

DML(Data Manipulation Language) is a family of computer language including commands permitting users to manipulate data in database. This manipulation involves inserting data into database tables, retreiving existing data, deleting data from existing tables and modifying existing data.

 

2. Select Cluases

Query is a precise request for information retreval with database and information systems.

 

2.1 SELECT

SELECT clause choose columns from database.

 

SELECT [col_name1], 
       [col_name2 from subquery](SELECT ... FROM ... ;),
       [col_name3] AS alias_name,
       ...

 

2.2 FROM

FROM clause specifies table from database.

 

SELECT ...
  FROM [table_name] or [table from subquery](SELECT ... FROM ...;)

 

2.3 LIMIT

Limit clause specifies rows to show.

 

SELECT ...
  FROM ... 
   ...
 LIMIT 3;

 

2.4 WHERE

WHERE clause is used to retrieve rows from table which make specific conditions. We can use all boolean operator used in Python, such as <, <=, >, >=, =, !=, <>.

 

SELECT ...
  FROM ...
 WHERE [condition_1] OR [condition_2] AND [condition_3]
   ...;

 

IN

We can use IN clause to specify a list of values we want to match against.

 

SELECT ...
  FROM [table_name]
 WHERE [col_name1] IN ('value1', 'value2', ...)
   ...;

 

LIKE

We can also matches a part of a string in specific column. We should use % characters to specify the patterns.

 

SELECT ...
    FROM ...
WHERE [col_name] LIKE "%pattern%";

 

2.5 GROUP BY/HAVING

GROUP BY clause is used to group rows in unique values. When we need data in specific conditions after applying grouping we use HAVING.

 

SELECT [col_name1], FUNC([col_name2]) AS [new_col1] 
  FROM ... 
 GROUP BY [col_name1], [col_name2], ...
 HAVING [condition_1]

 

2.6 ORDER BY

ORDER BY clause is used when we want to sort tables in specific columns. We can replace column name by integer on positional argument in SELECT clause, but this replacement hurt readability of codes.

 

SELECT ...
  FROM ...
 WHERE ...
   ...
 ORDER BY [col_name1], [col_name2] DESC;

 

2.7 AS

When we apply aggregation functions to columns, the final name of calculated columns is like 'AVG(col)'. To prevent this, we can make new column name with AS clause. We can also apply this cluase to tables.

 

SELECT COUNT([col_name1]) AS [new_col1], 
       (SELECT [col_name2] FROM [table_name]) AS [new_col2],
   ...
  FROM ... 
   ...;

 

2.8 DISTINCT

DISTINCT clause takes unique variable in specific column.

 

SELECT COUNT(DISTINCT [col_name1]) AS 'count of unique variable'
  FROM [table_name]
...

 

2.9 CASE WHEN

We can use if/then logic in SQL using CASE ~ WHEN clause. With this clause, we can do binning rows by conditions.

 

SELECT [col_name1], 
        CASE 
        WHEN [condition_1] THEN [value_1]
        WHEN [condition_2] THEN [value_2]
        ELSE [value_3]
        END AS [new_col], 
        ...
  FROM [table_name]
   ...;

 

3. Aggregate Functions 

Aggregate function is a function where the values of multiple rows are grouped together to form a single summary value. The common used aggregate functions is AVG(), COUNT(), MAX(), MIN(), MED(), SUM().

 

Systems of applying aggregate functions in tables works by rows. When we use WHERE clause, SQL makes bundles of rows in table making specific conditions and also GROUP BY clause follows this. Aggregation is applied on this bundles of rows.

 

SELECT SUM([col_name1]) AS [new_col1], 
       ...
  FROM [table_name]
 WHERE [condition_1] ... 
 GROUP BY [col1_name2] 
   ...;

 

CAST

SQL supports that standard arithmetic operators(+, -, *, /), arithmetic between two integers returns an integer. So when we need result in float type, we need to change data type using CAST(AS Flloat) function. 

 

SELECT CAST([col_name1] AS Float) / CAST([col_name2] AS Float) AS [new_col1], 
       ...
  FROM ...
   ...;

 

ROUND

We can make value in specific number decimal places using ROUND() function.

 

SELECT ROUND([col_name1], 3) AS [rounded_col]
  FROM ...
   ...;

 

||

We can concatenate string type data into a single column using || operator.

 

SELECT "string1 : " || [col_name1] AS [new_col],
       ...
  FROM ...
   ...;

 

4. Order of clause

1. FROM
2. WHERE
3. GROUP BY
4. HAVING
5. SELECT
6. ORDER BY
7. LIMIT

 

5. INSERT

An SQL INSERT clause adds one ore more records to any single table in a relational database.

 

INSERT INTO [table_name] ([column_name1], [column_name2], ...) 
     VALUES (([value11], [value12], ...), 
             ([value21], [value22], ...), 
             ...);

 

6. UPDATE

An SQL UPDATE clause changes the data of one or more records in a table. Either all the rows can be updated, or a subset may be chosen using a WHERE clause.

 

UPDATE [table_name]
SET
[column_name1] = [expression],
[column_name2] = [expression],
...
WHERE [condition_1];

 

7. DELTE

The DELETE clause removes one or more records from a table.

 

DELETE FROM [table_name]
 WHERE [condition_1];