# SQL Cheatsheet

• Split a string into multiple columns
• Date and Time
  • Set current date CURDATE()
• CREATE
• DELETE all records
• DELETE conditional records
• Functions
  • Select by the number of segments in a path
• Advance query examples
• Drop All Tables

Action	Command	Notes
COUNT	SELECT COUNT(Id) FROM stock;
MAX	SELECT MAX(Quantity) FROM stock;
SUM	SELECT SUM(Price * Quantity) FROM stock;
SUM	SELECT SUM(Price) FROM stock;

# Split a string into multiple columns

Splits a name into first and last name

UPDATE users
SET lastname = SUBSTRING_INDEX(name, ' ', -1),
    firstname = SUBSTRING_INDEX(name, ' ', 1);

# Date and Time

# Set current date CURDATE()

UPDATE my_table
SET date_column = CURDATE();

# CREATE

CREATE TABLE IF NOT EXISTS table_name (
    column1 datatype,
   ....
);

# DELETE all records

DELETE FROM my_table;

# DELETE conditional records

DELETE FROM my_table WHERE [condition];

# Functions

REPLACE(str_or_field, find_string, replace_with)

SELECT title, REPLACE(title, 'Section', 'Module')
FROM chapters WHERE title LIKE '%Section%';

# Select by the number of segments in a path

SELECT route_prefix
FROM pages
WHERE LENGTH(route_prefix) - LENGTH(REPLACE(route_prefix, '/', '')) + 1 = 2;

Explanation:

AND LENGTH(route) - LENGTH(REPLACE(route, '/', '')) + 1 = 2 filters the results further to only include rows where the number of segments in the route column is 2. This is done by counting the number of slashes in the route string and adding 1 to get the number of segments.

This will only work with the exact number of slashes

# Advance query examples

id	type	title	courses	main_category_id	sub_category_id
1	main	Main Category 1	null	null	null
2	sub	Sub Category 1	null	1	null
3	item	Item 1	EP01	1	2
4	item	Item 2		1	2
5	sub	Sub Category 2	null	1	null
6	item	Item 3		1	5
7	item	Item 4		1	5

-- sub query or IN condition (1, 76, 89)
SELECT main_category_id
FROM exam_prep_outlines
WHERE AND courses LIKE '%EP04%';
-- laravel equivalent
$mainCategories = ExamPrepOutline::where('courses', 'like', "%$code%")
    ->groupBy('main_category_id')
    ->pluck('main_category_id');

SELECT * FROM exam_prep_outlines
WHERE (type = 'item' AND courses LIKE '%EP04%')
   OR (type = 'sub' AND main_category_id IN (
       SELECT main_category_id
       FROM exam_prep_outlines
       WHERE courses LIKE '%EP04%'
   ));
-- laravel equivalent
$courseOutline = ExamPrepOutline::where('courses', 'like', "%$code%")
    ->orWhere('type', 'sub')
    ->whereIn('main_category_id', $mainCategories)
    ->get();

Output table

id	type	title	courses	courses	main_cat_id	sub_cat_id
2	sub	A	Infant	1
3	item	1	Feeding behaviours at different ages	EP04, EP12	1	2
5	item	3	Infant anatomy and anatomical/oral challenges	EP04	1	2
9	item	7	Normal infant behaviours	EP04	1	2
10	item	8	Nutritional requirements - including preterm	EP04, EP10	1	2
12	item	10	Skin tone, muscle tone, reflexes	EP04	1	2
15	item	13	Stooling and voiding	EP04	1	2
16	sub	B	Maternal	1
85	item	9	Breastfeeding dyad relationship	EP04, EP13	76
90	item	1	Effective milk transfer	EP04	89
91	item	2	First hour	EP04	89
92	item	3	Latching (attaching)	EP04	89
93	item	4	Managing supply	EP03, EP04	89
94	item	5	Milk expression	EP04. EP06	89
95	item	6	Position of the breastfeeding dyad (hands-off)	EP04	89
97	item	8	Skin-to-skin (kangaroo care)	EP04	89

# Drop All Tables

This script first disables foreign key checks to avoid issues with foreign key constraints, generates the DROP TABLE statements, and then re-enables foreign key checks. Note that you need to manually execute the generated DROP TABLE statements after generating the initial script as shown below.

SET FOREIGN_KEY_CHECKS = 0;

-- Generate DROP TABLE statements for all tables
SET @tables = NULL;
SELECT GROUP_CONCAT('`', table_name, '`') INTO @tables
FROM information_schema.tables
WHERE table_schema = (SELECT DATABASE());

-- Execute the DROP TABLE statements
SET @tables = CONCAT('DROP TABLE IF EXISTS ', @tables);
PREPARE stmt FROM @tables;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET FOREIGN_KEY_CHECKS = 1;