SQL cheat sheet vs Bài tập SQL cơ bản

SQL cheat sheet (123 dòng):

----- Basic Commands - Nhóm lệnh cơ bản -----

-- Select data from a table

SELECT column1, column2

FROM table_name;

-- Select all columns

SELECT * FROM table_name;

-- Insert data into a table

INSERT INTO table_name (column1, column2)

VALUES (value1, value2);

-- Update data in a table

UPDATE table_name

SET column1 = value1, column2 = value2

WHERE condition;

-- Delete data from a table

DELETE FROM table_name

WHERE condition;

----- Filtering Data - Lọc -----

-- Where clause

SELECT column1, column2

FROM table_name

WHERE condition;

-- Logical operators

SELECT column1, column2

FROM table_name

WHERE condition1 AND condition2;

WHERE condition1 OR condition2;

-- Pattern matching

SELECT column1, column2

FROM table_name

WHERE column1 LIKE 'pattern%';

----- Aggregating Data - Tổng hợp -----

-- Count

SELECT COUNT(column_name)

FROM table_name;

-- Sum

SELECT SUM(column_name)

FROM table_name;

-- Average

SELECT AVG(column_name)

FROM table_name;

-- Min and Max

SELECT MIN(column_name)

FROM table_name;

SELECT MAX(column_name)

FROM table_name;

----- Grouping and Sorting - Nhóm vs Sắp xếp -----

-- Group By

SELECT column1, COUNT(*)

FROM table_name

GROUP BY column1;

-- Having (with Group By)

SELECT column1, COUNT(*)

FROM table_name

GROUP BY column1

HAVING COUNT(*) > 1;

-- Order By

SELECT column1, column2

FROM table_name

ORDER BY column1 ASC;

ORDER BY column1 DESC;

----- Joining Tables - Nối bảng -----

-- Inner Join

SELECT a.column1, b.column2

FROM table1 a

INNER JOIN table2 b ON a.common_column = b.common_column;

-- Left Join

SELECT a.column1, b.column2

FROM table1 a

LEFT JOIN table2 b ON a.common_column = b.common_column;

-- Right Join

SELECT a.column1, b.column2

FROM table1 a

RIGHT JOIN table2 b ON a.common_column = b.common_column;

-- Full Outer Join

SELECT a.column1, b.column2

FROM table1 a

FULL OUTER JOIN table2 b ON a.common_column = b.common_column;

-----Miscellaneous - Lặt vặt-----

-- Create Table

CREATE TABLE table_name (

    column1 datatype PRIMARY KEY,

    column2 datatype,

    column3 datatype

);

-- Drop Table

DROP TABLE table_name;

-- Alter Table

ALTER TABLE table_name

ADD column_name datatype;

ALTER TABLE table_name

DROP COLUMN column_name;

Bài tập SQL cơ bản (58 dòng):

----- BÀI TẬP SQL CƠ BẢN -----

----- Basic Queries -----

--Select all records from a table named employees.

SELECT * FROM employees;

--Select the first_name and last_name columns from the employees table.

SELECT first_name, last_name

FROM employees;

----- Filtering Data -----

--Select all records from employees where the age is greater than 30.

SELECT * FROM employees

WHERE age > 30;

--Select records from employees where the department is 'Sales'.

SELECT * FROM employees

WHERE department = 'Sales';

----- Aggregating Data -----

--Count the number of employees in the employees table.

SELECT COUNT(*) AS employee_count

FROM employees;

--Calculate the average salary of employees.

SELECT AVG(salary) AS average_salary

FROM employees;

--Grouping and Sorting

--Group employees by department and count the number of employees in each department.

SELECT department, COUNT(*) AS department_count

FROM employees

GROUP BY department;

--Select all records from employees and sort them by last_name in ascending order.

SELECT * FROM employees

ORDER BY last_name ASC;

----- Joining Tables -----

--Perform an inner join between employees and departments tables on the department_id column.

SELECT e.first_name, e.last_name, d.department_name

FROM employees e

INNER JOIN departments d ON e.department_id = d.department_id;

--Perform a left join between employees and projects tables to get all employees and their project names (if any).

SELECT e.first_name, e.last_name, p.project_name

FROM employees e

LEFT JOIN projects p ON e.employee_id = p.employee_id;

----- Advanced Queries -----

--Select the top 5 highest-paid employees.

SELECT first_name, last_name, salary

FROM employees

ORDER BY salary DESC

LIMIT 5;

--Find employees who do not have a manager (assuming manager_id is NULL for such employees).

SELECT * FROM employees

WHERE manager_id IS NULL;

Sưu tầm bởi #drM