What is a database?

Databases abbreviated as DB. Any collection of related information: Phone book, top 5 best friends, shopping list,
…

Databases can be stored in different ways: on paper, in your mind, on a computer, comments section

Database Management System DBMS

are software systems used to store, retrieve, and run queries on data

Types of databases
1. SQL(Relational Databases) organize data into one or more tables: Each table has columns and rows, A
unique key identifies each row
2. Non-Relational( NoSQL / not just SQL) Organize data is anything but a traditional table: Key-value
stores, documents, graphs, flexible tables"

Relational databases(SQL)
1. Relational databases management systems (RDBMS): a software application that helps users create
and maintain a Relational database(Cơ sở dữ liệu quan hệ): MySQL, Oracle, PostgreSQL, MariaDB
etc
2. Structured Query Language(SQL): SQL is a standardized language for interacting with relational database
management systems.
3. used SQL to perform CRUD operations as well as other administrative tasks like management, security,
backup, etc.,
4. used to define tables and structures,"

SQL
SQL consists of several components, each serving its own unique purpose in database communication:

1. Queries: This is the component that allows you to retrieve data from a database. The SELECT statement is
most commonly used for this purpose.
2. Data Definition Language (DDL) Ngôn ngữ định nghĩa dữ liệu: It lets you to create, alter, or delete
databases and their related objects like tables, views, etc. Commands include CREATE, ALTER, DROP,
and TRUNCATE.
3. Data Manipulation Language (DML) Ngôn ngữ thao tác dữ liệu: It lets you manage data within database
objects. These commands include SELECT, INSERT, UPDATE, and DELETE.
4. Data Control Language (DCL): It includes commands like GRANT and REVOKE, which primarily deal
with rights, permissions, and other control-level management tasks for the database system."

SQL - Data Definition Language (DDL)

1. DDL(Data Definition Language): Its primary function is to create, modify, and delete database structures but
not data: CREATE DROP, ALTER, TRUNCATE, RENAME

CREATE: This command is used to create the database or its objects (like table, index, function, views, store
procedure, and triggers).

CREATE TABLE table_name (

column1 data_type(size),

column2 data_type(size),

...

);

DROP: This command is used to delete an existing database or table.

ALTER: This is used to alter the structure of the database. It is used to add, delete/drop or modify columns in an
existing table.

TRUNCATE: This is used to remove all records from a table, including all spaces allocated for the records which
are removed.
TRUNCATE TABLE table_name;

RENAME: This is used to rename an object in the database.

-- To rename a table

ALTER TABLE table_name

RENAME TO new_table_name;

-- To rename a column

ALTER TABLE table_name

RENAME COLUMN old_column_name TO new_column_name;

SQL - Data Manipulation Language (DML):

The purpose of DML is to INSERT, RETRIEVE, UPDATE and DELETE DATA from the database.

non-Relational Database Management Systems (NRDBMS)

1. NRDBMS helps users to create and maintain non-relational databases: MongoDB, dynamo DB,
Apache Cassandra, firebase, etc
2. Implementation specific

Database Queries

a query in database management is a request for data. Example a Google search is a query( but you can use any
languages)

Table and Key

1. Primary key: A primary key is a unique identifier for a row of data. Include: - A surrogate key is a primary
key that has no mapping to the real world. - A natural key is a key that has a mapping to the real world, just
like a Social Security Number.
2. A FOREIGN KEY is a column or combination of columns used to establish and enforce(thực thi) a link
between the data in two tables.. A particular table can have more than one foreign key on it.
SQL 1. SELECT: This keyword retrieves data( truy xuất dữ liệu) from a database.
Keywords 2. FROM: Used in conjunction with SELECT to specify the table from which to fetch data.
3. WHERE: Used to filter records. Incorporating a WHERE clause, you might specify conditions
that must be met(đáp ứng).
4. INSERT INTO: This command is used to insert new data into a database.
Ex: INSERT INTO Customers (CustomerID, CustomerName, ContactName, Address, City,
PostalCode, Country)
VALUES ('Cardinal','Tom B. Erichsen','Skagen 21','Stavanger','4006','Norway');
5. UPDATE: This keyword updates existing data within a table. For example,
UPDATE Customers SET ContactName='Alfred Schmidt', City='Frankfurt'
6. DELETE: This command removes one or more records from a table. For example DELETE
FROM Customers WHERE CustomerName='Alfreds Futterkiste';
7. CREATE DATABASE: As implied by its name, this keyword creates a new [Link]:
CREATE DATABASE mydatabase;
8. ALTER TABLE statement is used to add, delete/drop or modify columns in the existing table.
It is also used to add and drop constraints on the existing table.
ALTER TABLE table_name ADD column_name datatype;
ALTER TABLE table_name DROP COLUMN column_name;
ALTER TABLE table_name MODIFY COLUMN column_name datatype(size);
ALTER DATABASE, DROP DATABASE, CREATE TABLE, ALTER TABLE, DROP
TABLE: These keywords are used to modify databases and tables.
SQL is not case sensitive, you can write lower or UPPER but The convention(quy ước) is to write
them in ALL CAPS for readability
INSERT INTO values
INSERT INTO table_name (column1, column2, column3, ...)
VALUES (value1, value2, value3, ...);
specifies both the column names and the values to be inserted.
INSERT INTO set: insert data using the SET keyword. Here, you specify each column you
want to insert data into, and then the data for that column.
INSERT INTO table_name
SET column1 = value1, column2 = value2, ...;
INSERT INTO select: is used to copy data from one table and insert it into another table. Or, to
insert data into specific columns from another table.
INSERT INTO table_name1 (column1, column2, column3, ...)
SELECT column1, column2, column3, ...
FROM table_name2
WHERE condition;

UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition;
Data Types
1. INT: is used for whole numbers.

For example: CREATE TABLE Employees ( ID INT, Name VARCHAR(30) );

2. DECIMAL is used for decimal and fractional numbers.

For example: CREATE TABLE Items ( Price DECIMAL(5,2) );

3. CHAR is used for fixed-length strings.

For example: CREATE TABLE Employees ( Initial CHAR(1));

4. VARCHAR is used for variable-length strings.

For example: CREATE TABLE Employees (Name VARCHAR(30));

5. DATE is used for dates in the format (YYYY-MM-DD).

CREATE TABLE Employees ( BirthDate DATE);

6. DATETIME is used for date and time values in the format (YYYY-MM-DD HH:MI:SS).

CREATE TABLE Orders (OrderDate DATETIME);

7. BINARY is used for binary strings.

8. BOOLEAN is used for boolean values (TRUE or FALSE)."

Operators
1. Arithmetic Operators(Toán tử số học): These are used to perform mathematical operations.

+ : Addition

- : Subtraction

* : Multiplication

/ : Division

% : Modulus (Phần dư)

2. Comparison Operators(Toán tử so sánh): These are used in the where clause to compare one expression
with another.

= : Equal

!= or <> : Not equal

> : Greater than

< : Less than

>=: Greater than or equal

3. Logical Operators: They are used to combine the result set of two different component conditions.

AND: Returns true if both components are true.

OR : Returns true if any one of the component is true.

NOT: Returns the opposite boolean value of the condition.

<=: Less than or equal

4. Bitwise Operators: These perform bit-level operations on the inputs.

& : Bitwise AND

| : Bitwise OR
^ Bitwise exclusive OR

5. SQL Logical Operators

ALL TRUE if all of the subquery values meet the condition

AND TRUE if all the conditions separated by AND is TRUE

ANY TRUE if any of the subquery values meet the condition

BETWEEN TRUE if the operand is within the range of comparisons

EXISTS TRUE if the subquery returns one or more records

IN TRUE if the operand is equal to one of a list of expressions

LIKE TRUE if the operand matches a pattern

NOT Displays a record if the condition(s) is NOT TRUE

OR TRUE if any of the conditions separated by OR is TRUE

SOME TRUE if any of the subquery values meet the condition

^ : Bitwise XOR

JOIN
1. (INNER) JOIN: This type of join returns records with matching values in both tables.

SELECT table1.column1, table2.column2...

FROM table1

INNER JOIN table2

ON table1.matching_column = table2.matching_column;

2. LEFT (OUTER) JOIN: Returns all records from the left table, and matched records from the right table.

SELECT table1.column1, table2.column2...

FROM table1

LEFT JOIN table2

ON table1.matching_column = table2.matching_column;

3. RIGHT (OUTER) JOIN: Returns all records from the right table, and matched records from the left table.

SELECT table1.column1, table2.column2...

FROM table1

RIGHT JOIN table2

ON table1.matching_column = table2.matching_column;

4. FULL (OUTER) JOIN: Returns all records when either a match is found in either left (table1) or right
(table2) table records.

SELECT table1.column1, table2.column2...

FROM table1

FULL JOIN table2

ON table1.matching_column = table2.matching_column;
 5. SELF JOIN: A self-join is a join in which a table is joined with itself. It’s important to note that, since it’s
a join operation on the same table, alias(es) for table(s) must be used to avoid confusion during the join
operation.

SELECT a.column_name, b.column_name...

FROM table_name AS a, table_name AS b

WHERE a.common_field = b.common_field;

Example

If you want to find out all the employees and who their manager is, you can do so using a SELF JOIN:

SELECT [Link] AS Employee, [Link] AS Manage FROM EMPLOYEES a, EMPLOYEES b

WHERE [Link] = [Link];

This query will return the name of each employee along with the name of their respective manager.

6. Cross Join( CARTESIAN JOIN): The size of a Cartesian product result set is the number of rows in the
first table multiplied by the number of rows in the second table. This statement will return a result set
which is the combination of each row from Table 1 with each row from Table 2.

SELECT column_name(s) FROM table1, table2;

7. NATURAL JOIN:

The natural join is akin to an inner join, automatically linking two columns based on identical names. Note:
Columns should have the same data type in both tables

SELECT * FROM Customers NATURAL JOIN Orders;

Data Constraints
are used to specify rules for the data in a table. Constraints are used to limit the type of data that can go into a table.
This ensures the accuracy and reliability(độ tin cậy) of the data in the table.

Types of SQL Data Constraints

1. NOT NULL Constraint: Ensures that a column cannot have a NULL value.

CREATE TABLE Students (

ID int NOT NULL,
Name varchar(255) NOT NULL,
Age int
);

2. UNIQUE Constraint: Ensures that all values in a column are different.

CREATE TABLE Students (
ID int NOT NULL UNIQUE,
Name varchar(255) NOT NULL,
Age int
);

3. PRIMARY KEY Constraint: The PRIMARY KEY constraint uniquely identifies each record in a table.
Primary keys must contain UNIQUE values, and cannot contain NULL values. A table can have only ONE
primary key; and in the table, this primary key can consist of single or multiple columns (fields).

-* Auto_Increment: Tăng thứ tự vd ID = 1, 2, 3…

CREATE TABLE Orders (

OrderID int Auto_increament
OrderNumber int NOT NULL,
ID int,
PRIMARY KEY (OrderID),
);

INSERT INTO Orders(OrderNumber, ID) VALUES(123,23) ->> Order ID = 1

COMPOSITE PRIMARY KEY: Khóa chính tổng hợp: dùng Constraint

CREATE TABLE Customers (

CustomerID INT,
StoreID INT,
CONSTRAINT pk_CustomerID_StoreID PRIMARY KEY (CustomerID,StoreID) );

4. FOREIGN KEY Constraint: Prevents actions that would destroy links between tables. A FOREIGN KEY
is a field (or collection of fields) in one table that refers to the PRIMARY KEY in another table.

CREATE TABLE Orders (

OrderID int NOT NULL,
OrderNumber int NOT NULL,
ID int,
PRIMARY KEY (OrderID),
FOREIGN KEY (ID) REFERENCES Students(ID)
);

5. CHECK Constraint: The CHECK constraint ensures that all values in a column satisfy( thỏa mãn) certain
conditions.

CREATE TABLE Students (

 ID int NOT NULL,
Name varchar(255) NOT NULL,
Age int,
CHECK (Age>=18)
);

6. DEFAULT Constraint: Provides a default value for a column when none is specified.

CREATE TABLE Students (

ID int NOT NULL,
Name varchar(255) NOT NULL,
Age int,
City varchar(255) DEFAULT 'Unknown'
);

CREATE TABLE Orders (

ID int NOT NULL,
OrderNumber int NOT NULL,
OrderDate date DEFAULT GETDATE()
);

7. INDEX Constraint: Used to create and retrieve data from the database very quickly.

CREATE INDEX Syntax

Creates an index on a table. Duplicate values are allowed:
CREATE INDEX index_name
ON table_name (column1, column2, ...);

CREATE UNIQUE INDEX Syntax

Creates a unique index on a table. Duplicate values are not allowed:
CREATE UNIQUE INDEX index_name
ON table_name (column1, column2, ...);

DROP INDEX: Ex: DROP INDEX table_name.index_name;

On Delete
1. ON DELETE CASCADE

When you specify an “ON DELETE CASCADE” for a foreign key constraint, it means that if a record in the
parent table (referenced table) is deleted then all related records in the child table (referencing table)
will be automatically deleted.

CREATE TABLE parent_table_p (

id INT PRIMARY KEY
)
CREATE_TABLE child_table_c(
id INT PRIMARY KEY,
parent_id INT,
FOREIGN KEY (parent_id) REFERENCES parent_table_p(id) ON DELETE CASCADE
)
2. ON DELETE SET NULL
this means if a record in the parent table (referenced table) is deleted then the corresponding entry or
values in the child table (referencing table) will be set to NULL.

CREATE TBALE parent_table_p (

id INT PRIMARY KEY
)
CREATE_TABLE child_table_c(
id INT PRIMARY KEY,
parent_id INT,
FOREIGN KEY (parent_id) REFERENCES parent_table_p(id) ON DELETE SET NULL)
Wildcards
Wildcard Characters

Symbol Description

% Represents zero or more characters

_ Represents a single character

[] Represents any single character within the brackets *

^ Represents any character not in the brackets *

- Represents any single character within the specified range *

{} Represents any escaped character **

* Not supported in PostgreSQL and MySQL databases.

** Supported only in Oracle databases.

Union
The UNION operator is used to combine the result set of two or more SELECT statements.

Every SELECT statement within UNION must have the same number of columns

The columns must also have similar data types

The columns in every SELECT statement must also be in the same order

The UNION operator selects only distinct values by default. To allow duplicate values, use UNION ALL
SQL FUNCTIONs

SQL Numeric Functions

SQL numeric functions are used to perform operations on numeric data types such as integer, decimal, and float.
They’re fundamental in manipulating data in SQL commands and are commonly used in SELECT, UPDATE, DELETE
and INSERT statements.

1. ABS() Function: This function returns the absolute(tuyệt đối) (positive số dương) value of a
number.

SELECT ABS(-243); -> Kết quả là 243

2. Avg() Function: This function returns the average value of a column

SELECT AVG(price) FROM products;

3. COUNT() Function: This function returns the number of rows that matches a specified criterion.
 4. MIN() & MAX() Functions: MIN() function returns the smallest value of the selected column, and
MAX() function returns the largest value of the selected column
5. ROUND() Function: This function is used to round a numeric field to the nearest integer(số nguyên
gần nhất), you can, however, specify the number of decimals to be returned.
6. CEILING() Function: This function returns the smallest integer which is greater than, or equal to,
the specified numeric expression.(số nhỏ nhất lớn hơn biểu thức số đã chỉ định)
7. FLOOR() Function: This function returns the largest integer which is less than, or equal to, the
specified numeric expression.
8. SQRT() Function: This function returns the square root of a number. (Căn bậc hai)
9. MOD() function is a mathematical function that returns the remainder of the values from the
division of two numbers.(chia lấy dư)
10. PI() Function: This function returns the constant Pi.

SELECT PI();

These are just a few examples, SQL supports many more mathematical functions such as SIN, COS, TAN, COT,
POWER, etc.

Aggregate Functions:
These functions operate on a set of rows and return a single summarized result.

MIN() - returns the smallest value within the selected column

MAX() - returns the largest value within the selected column

COUNT() - returns the number of rows in a set

SUM() - returns the total sum of a numerical column

AVG() - returns the average value of a numerical column

***

COUNT(*) counts all the rows in the target table whether columns contain null values or not.

COUNT(column) counts the rows in the column of a table excluding null.

The AVG() function works only with numeric data types (INT, FLOAT, DECIMAL, etc.). It will return an error if
used with non-numeric data types."

To separate the results into groups of accumulated data, you can use the GROUP BY clause.

SELECT column1, aggregate_function(column2)

FROM table

GROUP BY column1;

”A group” is represented by ROW(s) that have the same value in the specific column(s). The GROUP BY clause
can be used in a SELECT statement to collect data across multiple records and group by some columns.

The HAVING clause is used with the GROUP BY clause, it applies to summarized group records, unlike the
‘where’ clause. It was added to SQL because the WHERE keyword could not be used with aggregate
functions.

SELECT column1, aggregate_function(column2)

FROM table

GROUP BY column1

HAVING conditions;"
String Functions
1. CONCAT Function: combines two or more strings into one string. The CONCAT_WS function will ignore
any NULL values, only joining the non-NULL values with the provided separator
2. SUBSTRING function extracts a string from a given string.

My SQL: SELECT SUBSTRING('SQL Tutorial', 1, 3); --> SQL 1: Start, 3:

Length

SQL Server: SELECT SUBSTRING('Hello World' FROM 1 FOR 5) as ExtractedString;

SELECT SUBSTRING([Link], 1, 3), SUBSTRING([Link], 1, 3)

FROM EmployeeErrors err JOIN EmployeeDemographics Dem

ON SUBSTRING([Link], 1, 4) =SUBSTRING([Link], 1, 4)

Ví dụ có những tên viết tắt Alex bảng err = Alexander bảng dem nhưng nếu không substring thì 2 thành phần này
không phải cũng 1 người

SUBSTRING_INDEX SUBSTRING_INDEX(string, delimiter, number)

Return a substring of a string before a specified number of delimiters occurs.

Positive number counts delimiter from left to right

Negative number counts delimiter from right to left

SELECT SUBSTRING_INDEX(14214-258-785214a-5247', '-', 1); ->result: 14214

3. LENGTH function returns the length of a string.
4. UPPER function converts all the letters in a string to uppercase, whereas the LOWER function to
lowercase.
5. TRIM function removes leading and trailing spaces of a string. You can also remove other specified
characters.

EX: SELECT TRIM('#! ' FROM ' #SQL Tutorial! ') AS TrimmedString;

SELECT TRIM(EmployeeID) as employee_trim

SELECT LTRIM(EmployeeID) as employee_trim

SELECT RTRIM(EmployeeID) as employee_trim

TRIM ( [ LEADING | TRAILING | BOTH ] [characters FROM ] string )

LEADING removes characters specified from the start of a string.
TRAILING removes characters specified from the end of a string.
BOTH (default positional behavior) removes characters specified from the
start and end of a string.
Ex: TRIM(Leading ‘x’ FROM ‘xxSQL Tutorial’ )
6. REPLACE() function in SQL to substitute all occurrences of a specified string.

REPLACE(input_string, string_to_replace, replacement_string)

Ex: REPLACE(LastName, “-Hi”, “ ”) as LastNameFixed

Conditional
1. CASE: is a conditional statement in SQL that performs different actions based on different conditions. It
allows you to perform IF-THEN-ELSE logic within SQL queries. It can be used in any statement or clause
that allows a valid expression.
 SELECT OrderID, Quantity,
(CASE
WHEN Quantity > 30 THEN 'Over 30'
WHEN Quantity = 30 THEN 'Equals 30'
ELSE 'Under 30'
END) AS QuantityText
FROM OrderDetails;

2. NULLIF: NULLIF is a built-in conditional function in SQL Server. The NULLIF function compares two
expressions and returns NULL if they are equal or the first expression if they are not.

SELECT
first_name,
last_name,
NULLIF(email, 'NA') AS email
FROM
users;

3. COALLESE: used to manage NULL values in data. It scans from left to right through the arguments and
returns the first argument that is not NULL.

COALESCE does not update the original data. It only returns the first non-NULL value in the runtime. To update any
NULL values permanently, you would need to use an UPDATE statement.

4. IFF: IIF function returns value_true if the condition is TRUE, or value_false if the condition is FALSE.

SELECT IIF (1>0, 'One is greater than zero', 'One is not greater than zero');

Date and Time

1. GETDATE() returns the current date and time as a DateTime datatype. It does not require any arguments.

SELECT GETDATE() AS CurrentDateTime;

2. DATEDIFF() returns the difference between two date values based on the unit of time you want to use.
The syntax is DATEDIFF(datepart, startdate, enddate).

SELECT DATEDIFF(day, '2022-01-01', '2022-01-15') AS DiffInDays;

3. DATEADD() adds or subtracts a specified time interval from a date.

SELECT DATEADD(year, 1, '2022-01-01') AS NewDate;

Required. The time/date interval to add. Can be one of the following values:
 year, yyyy, yy = Year
 quarter, qq, q = Quarter
 month, mm, m = month
 dayofyear, dy, y = Day of the year
 day, dd, d = Day
 week, ww, wk = Week
  weekday, dw, w = Weekday
 hour, hh = hour
 minute, mi, n = Minute
 second, ss, s = Second
 millisecond, ms = Millisecond

4. CONVERT() is used to convert from one data type to another, and it is

commonly used to format DateTime values.

CONVERT(data_type(length), expression, style)

SELECT CONVERT(varchar, '2017-08-25', 101);

Style:Optional
Without century With century Input/Output Standard

0 100 mon dd yyyy hh:miAM/PM Default

1 101 mm/dd/yyyy US

2 102 [Link] ANSI

3 103 dd/mm/yyyy British/French

4 104 [Link] German

5 105 dd-mm-yyyy Italian

6 106 dd mon yyyy -

7 107 Mon dd, yyyy -

8 108 hh:mm:ss -

9 109 mon dd yyyy hh:mi:ss:mmmAM (or PM) Default + millisec

10 110 mm-dd-yyyy USA

11 111 yyyy/mm/dd Japan

12 112 yyyymmdd ISO

5. CURRENT DAY: Returns the current date. SELECT CURRENT_DATE;
6. DATEPART is a useful function in SQL that allows you to extract a specific part of a
date or time field. You can use it to get the year, quarter, month, day of the year,
day, week, weekday, hour, minute, second, or millisecond from any date or time
expression

SELECT DATEPART(year, '2021-07-14') AS 'Year';

7. CURRENT_TIMESTAMP: allows you to store both date and time

SELECT CURRENT_TIMESTAMP();  2024-06-26 01:11:42

 CREATE TABLE table_name (
column1 TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
column2 VARCHAR(100),
...
);

Sub Queries
Subqueries, sometimes referred to as inner queries or nested queries, are queries that are
embedded(nhúng) within the clause of another SQL query. There are different types of SQL subqueries
that are frequently used including Scalar, Row, Column, and Table subqueries.

1. Scalar Subqueries

A scalar subquery is a query that returns exactly one column with a single value. This type of subquery
can be used anywhere in your SQL where expressions are allowed.

SELECT name
FROM student
WHERE roll_id = (SELECT roll_id FROM student WHERE name='John');

(Trong mệnh đề WHERE chỉ trả về 1 giá trị duy nhất có roll_id thỏa điều ki ện)

2. Row subquery

Row subqueries are used to return one or more rows to the outer SQL select query. However, the subquery
returns multiple columns and rows, so it cannot be directly used where scalar expressions are used.

SELECT * FROM student

WHERE (roll_id, age)=(SELECT MIN(roll_id),MIN(age) FROM student);

SELECT column_name [, column_name ]

FROM table1 [, table2 ]
WHERE (column_name [, column_name ])
IN (SELECT column_name [, column_name ]
FROM table_name
WHERE condition);

3. Column subquery

Column Subqueries are used to return one or more columns to the outer SQL select query. They are used when the
subquery is expected to return more than one column to the main query.

SELECT name, age FROM student

WHERE name in (SELECT name FROM student);

4. Table subquery

Table subqueries are used in the FROM clause and return a table that can be used as a table-reference in an SQL
statement. They come in handy when you want to perform operations such as joining multiple tables, union data
from multiple sources, etc.

SELECT name, age

FROM student
WHERE (name, age) IN (SELECT name, age FROM student);