SQL(caps)Structured
Query Language (SQL) is a
special-purpose programming language designed for managing data held in a
Relational Database Management System (RDBMS). SQL-like languages can also
be used in Relational Data Stream Management Systems (RDSMS), or in
"not-only SQL" (NoSQL) databases.
SQL comprises of 3 major sub-languages:
- Data Definition Language (DDL): to create and modify the structure of the database;
- Data Manipulation Language (DML): to perform Read, Insert, Update and Delete operations on the data of the database;
- Data Control Language (DCL): to control the access of the data stored in the database.
Many SQL databases are implemented as client/server systems; the term "SQL server" describes such a database. At the same time, Microsoft makes a database that is named "SQL Server". While that database speaks a dialect of SQL, information specific to that database is not on topic in this tag but belongs into the SQL Server documentation.
1] FUNDAMENTALS OF SQL
I) CREATE
CREATE TABLE "TABLE_NAME"(
"COLUMN1" "DATA_TYPE" CONSTRAINTS,
"COLUMN2" "DATA_TYPE" CONSTRAINTS,
"COLUMN3" "DATA_TYPE" CONSTRAINTS,
..............................
"COLUMN N" "DATA_TYPE" CONSTRAINTS);
II) INSERT
INSERT INTO "TABLE_NAME" (COL1, COL2, ……..COL_N)
VALUES (Col_val_1,Col_val_2, ……. COL_N);
- Import data from file(PostgreSQL)
COPY TABLE_NAME(column1, column2,… ) FROM
'FILE_PATH' DELIMITER ',' CSV HEADER;
COPY TABLE_NAME(column1, column2,… ) FROM
'FILE_PATH' DELIMITER ',';
III) SELECT
SELECT * FROM "TABLE_NAME";
- For select one column
SELECT "COLUMN_NAME" FROM "TABLE_NAME";
- For select multiple columns
SELECT "COLUMN1, COLUMN2, …" FROM "TABLE_NAME";
- For select all columns
SELECT * FROM "TABLE_NAME";
IV) DISTINCT
SELECT DISTINCT(*) FROM "TABLE_NAME";
V) WHERE
SELECT "COLUMN_NAME(S)"
FROM "TABLE_NAME"
WHERE CONDITION;
VI) AND/OR
SELECT "COLUMN_NAME(S)"
FROM "TABLE_NAME"
WHERE CONDITION AND/OR CONDITION;
VII) UPDATE
UPDATE "TABLE_NAME"
SET COL_1 = VAL_1, COL_2 = VAL_2, …
WHERE CONDITION;
VIII) DELETE
- For delete all rows:
DELETE FROM "TABLE_NAME";
- For delete single/multiple row(s):
DELETE FROM "TABLE_NAME"
WHERE CONDITION;
IX) ALTER
- ADD COLUMN:
ALTER TABLE "TABLE_NAME"
ADD "COLUMN_NAME" "DATA_TYPE";
- DROP COLUMN:
ALTER TABLE "TABLE_NAME"
DROP "COLUMN_NAME";
- MODIFY DATA TYPE:
ALTER TABLE "TABLE_NAME"
ALTER COLUMN "COL_NAME" TYPE NEW_DATA_TYPE;
- RENAME COLUMN:
ALTER TABLE "TABLE_NAME"
RENAME COLUMN "COL_NAME" TO "NEW_NAME";
- ADD CONSTRAINTS:
ALTER TABLE "TABLE_NAME"
ADD CONSTRAINT COL_NAME CHECK CONDITION;
2] FILTERING COMMANDS
I) IN
SELECT COL_NAME FROM TABLE_NAME
WHERE COL_NAME IN ('VAL1', 'VAL2', ...);
II) BETWEEN
SELECT COL_NAME(S) FROM TABLE_NAME
WHERE COL_NAME BETWEEN 'VAL1' AND 'VAL2';
III) LIKE
- "%" - matches any string of any length.
- "_" - matches a single character.
SELECT COL_NAME FROM TABLE_NAME
WHERE COL_NAME LIKE 'PATTERN';
3] ORDERING COMMANDS
I) ORDER BY
SELECT COL_NAME(s) FROM TABLE_NAME
ORDER BY COL_NAME ASC/DESC;
II) LIMIT
SELECT COL_NAME(S) FROM TABLE_NAME
[WHERE & ORDER BY – Optional]
LIMIT LIMIT_VALUE;
4] ALIAS
I) AS
SELECT COL_NAME as COL_ALIAS
FROM TABLE_NAME;
5] AGGREGATE COMMANDS
I) COUNT()
SELECT COUNT(COL_NAME) FROM TABLE_NAME;
II) SUM()
SELECT SUM(COL_NAME) FROM TABLE_NAME;
III) MIN()
SELECT MIN(COL_NAME) FROM TABLE_NAME;
IV) MAX()
SELECT MAX(COL_NAME) FROM TABLE_NAME;
V) AVG()
SELECT AVG(COL_NAME) FROM TABLE_NAME;
6] GROUP BY COMMANDS
SELECT COL_NAME, COUNT(*)
FROM TABLE_NAME
GROUP BY COL_NAME;
SELECT department, COUNT(*)
FROM employees
GROUP BY department;
7] CONDITIONAL STATEMENT
I) CASE
SELECT COL_NAME,
CASE
WHEN condition1 THEN result1
WHEN condition2 THEN result2
ELSE result3
END AS alias_name
FROM TABLE_NAME;
SELECT name,
CASE
WHEN score >= 90 THEN 'A'
WHEN score >= 80 THEN 'B'
ELSE 'C'
END AS grade
FROM students;
8] JOINS
I) INNER JOIN
SELECT columns
FROM table1
INNER JOIN table2 ON table1.column = table2.column;
SELECT employees.name, departments.department_name
FROM employees
INNER JOIN departments ON employees.department_id = departments.department_id;
II) LEFT JOIN
SELECT columns
FROM table1
LEFT JOIN table2 ON table1.column = table2.column;
SELECT employees.name, departments.department_name
FROM employees
LEFT JOIN departments ON employees.department_id = departments.department_id;
III) RIGHT JOIN
SELECT columns
FROM table1
RIGHT JOIN table2 ON table1.column = table2.column;
SELECT employees.name, departments.department_name
FROM employees
RIGHT JOIN departments ON employees.department_id = departments.department_id;
IV) FULL JOIN
SELECT columns
FROM table1
FULL JOIN table2 ON table1.column = table2.column;
SELECT employees.name, departments.department_name
FROM employees
FULL JOIN departments ON employees.department_id = departments.department_id;
9] SUBQUERY
I) Subquery in SELECT
SELECT column1,
(SELECT column2 FROM table2 WHERE table2.column3 = table1.column3) AS alias_name
FROM table1;
SELECT name,
(SELECT AVG(score) FROM exams WHERE exams.student_id = students.id) AS average_score
FROM students;
10] VIEW & INDEX
I) VIEW
CREATE VIEW view_name AS
SELECT columns
FROM table
WHERE condition;
CREATE VIEW high_salary_employees AS
SELECT name, salary
FROM employees
WHERE salary > 50000;
II) INDEX
CREATE INDEX index_name
ON table_name (column1, column2, ...);
CREATE INDEX idx_employee_name
ON employees (name);
11] STRING FUNCTIONS
I) CONCAT
SELECT CONCAT(string1, string2, ...) AS new_string
FROM table_name;
SELECT CONCAT(first_name, ' ', last_name) AS full_name
FROM employees;
II) SUBSTRING
SELECT SUBSTRING(column_name, start_position, length) AS substring
FROM table_name;
SELECT SUBSTRING(name, 1, 3) AS short_name
FROM employees;
12] Mathematical Functions
I) ROUND
SELECT ROUND(column_name, decimal_places) AS rounded_number
FROM table_name;
SELECT ROUND(salary, 2) AS rounded_salary
FROM employees;
II) ABS
SELECT ABS(column_name) AS absolute_value
FROM table_name;
SELECT ABS(profit_loss) AS absolute_profit_loss
FROM financials;
13] DATE-TIME FUNCTIONS
I) CURRENT_DATE
SELECT CURRENT_DATE AS today_date;
SELECT CURRENT_DATE AS today_date;
II) DATE_ADD
SELECT DATE_ADD(date_column, INTERVAL value unit) AS new_date
FROM table_name;
SELECT DATE_ADD(hire_date, INTERVAL 1 YEAR) AS next_anniversary
FROM employees;
14] PATTERN MATCHING
I) REGEXP
SELECT column_name
FROM table_name
WHERE column_name REGEXP 'pattern';
SELECT name
FROM employees
WHERE name REGEXP '^[A-Z]';
15] DATA TYPE CONVERSION FUNCTIONS
I) CAST
SELECT CAST(column_name AS new_data_type) AS new_column_name
FROM table_name;
SELECT CAST(salary AS DECIMAL(10, 2)) AS salary_decimal
FROM employees;
II) CONVERT
SELECT CONVERT(column_name, new_data_type) AS new_column_name
FROM table_name;
SELECT CONVERT(salary, DECIMAL(10, 2)) AS salary_decimal
FROM employees;