Prepared by Ntlakanipho Mgaguli MySQL Exam Day Cheat Sheet 1

1. Database Operations PRIMARY KEY -- Unique , NOT NULL -- Parent tables first
FOREIGN KEY -- Must exist in parent INSERT INTO Department VALUES (1 , ’ IT ’) ;
-- Create & verify d a t a b a s e NOT NULL -- Cannot be empty
CREATE DATABASE l a s t n a m e _ s t u d e n t n o _ p r o j e c t ; UNIQUE -- No d u p l i c a t e s ( can be NULL ) -- Then child tables
SHOW DATABASES ; CHECK -- Custom c o n d i t i o n INSERT INTO Employee ( emp_id , dept_id )
USE l a s t n a m e _ s t u d e n t n o _ p r o j e c t ; DEFAULT -- Default value VALUES (1 , 1) ;
SELECT DATABASE () ; AUTO _INCREME NT -- Auto number
-- M u l t i p l e rows
-- Delete d a t a b a s e ( CAREFUL !) INSERT INTO table_name VALUES
DROP DATABASE database_name ; (1 , ’A ’) , (2 , ’B ’) , (3 , ’C ’) ;
5. Relationships Quick Guide
Always verify with SHOW after CREATE! UPDATE & DELETE:
One-to-Many (1:N): DANGER!
-- Parent ( one side ) WITHOUT WHERE = ALL ROWS!
2. Table Creation Order CREATE TABLE Department (
dept_id INT PRIMARY KEY -- Safe update
Parent tables first! (no foreign keys) ); UPDATE table SET col = value
WHERE id = 1;
→ Then child tables (with foreign keys) -- Child ( many side ) - FK here !
CREATE TABLE Employee ( -- Safe delete
-- Basic syntax emp_id INT PRIMARY KEY , DELETE FROM table WHERE id = 1;
CREATE TABLE table_name ( dept_id INT ,
column_name datatype constraints , FOREIGN KEY ( dept_id ) -- DANGER - affects all !
PRIMARY KEY ( column ) , REFERENCES Department ( dept_id ) UPDATE table SET col = value ;
FOREIGN KEY ( column ) ); DELETE FROM table ;
REFERENCES parent_table ( column )
); Many-to-Many (N:N):
-- Verify tables -- Need j u n c t i o n table !
SHOW TABLES ; CREATE TABLE Student ( 7. SELECT Query Order
DESC table_name ; student_id INT PRIMARY KEY
SHOW CREATE TABLE table_name ; );
SELECT columns -- 1
FROM table -- 2
CREATE TABLE Course (
JOIN table2 ON ... -- 3
course_id INT PRIMARY KEY
WHERE condition -- 4
);
3. Common Data Types GROUP BY columns
HAVING condition
-- 5
-- 6
CREATE TABLE Enrollment (
ORDER BY columns -- 7
student_id INT ,
Numeric String course_id INT ,
LIMIT number ; -- 8
INT VARCHAR(n) PRIMARY KEY ( student_id , course_id ) ,
DECIMAL(m,d) CHAR(n) FOREIGN KEY ( student_id )
FLOAT TEXT REFERENCES Student ( student_id ) ,
Date/Time Other
FOREIGN KEY ( course_id ) 8. Essential SELECT Patterns
REFERENCES Course ( course_id )
DATE BOOLEAN );
DATETIME ENUM(’a’,’b’) -- Pattern matching
WHERE name LIKE ’J % ’ -- starts with J
TIMESTAMP WHERE name LIKE ’% son ’ -- ends with son
6. Data Manipulation WHERE name LIKE ’% oh % ’ -- c o n t a i n s oh

4. Constraints Reference -- NULL h a n d l i n g

INSERT - Order matters! WHERE column IS NULL
Prepared by Ntlakanipho Mgaguli MySQL Exam Day Cheat Sheet 2

WHERE column IS NOT NULL

-- Try d u p l i c a t e primary key INSERT INTO ...;
-- M u l t i p l e values INSERT INTO table ( id , name ) SELECT * FROM table ; -- Verify !
WHERE id IN (1 , 2 , 3) VALUES (1 , ’ Test ’) ; -- Fails if id =1 exists
WHERE age BETWEEN 18 AND 25 4. Test Integrity - Show success case - Show failure
-- Try NULL primary key
-- Sorting INSERT INTO table ( id , name ) case - Get error screenshot
ORDER BY column ASC -- default VALUES ( NULL , ’ Test ’) ; -- Always fails 5. Run Queries - Before screenshot (if UP-
ORDER BY column DESC DATE/DELETE) - Run query - After screenshot
Referential Integrity Test:
-- Insert with invalid FK
INSERT INTO Employee ( emp_id , dept_id )
9. JOIN Types VALUES (99 , 999) ; -- Fails if dept 999 missing 14. Quick Formulas
-- Delete parent with c h i l d r e n
-- INNER JOIN ( m a t c h i n g only )
DELETE FROM Department
Find Duplicates:
SELECT * FROM A
WHERE dept_id = 1; -- Fails if e m p l o y e e s exist
INNER JOIN B ON A . id = B . a_id ; SELECT column , COUNT (*)
FROM table
-- LEFT JOIN ( all from left ) GROUP BY column
SELECT * FROM A HAVING COUNT (*) > 1;
LEFT JOIN B ON A . id = B . a_id ; 12. Common Error Messages
-- RIGHT JOIN ( all from right ) Delete Duplicates (keep one):
SELECT * FROM A Error Meaning
RIGHT JOIN B ON A . id = B . a_id ; Duplicate entry ’X’ Primary key al- DELETE t1 FROM table t1
for key ’PRIMARY’ ready exists INNER JOIN table t2
Cannot add or up- Foreign key value WHERE t1 . id > t2 . id
AND t1 . email = t2 . email ;
date a child row doesn’t exist in par-
10. Aggregate Functions ent
Cannot delete par- Has child records Rank Results:
COUNT (*) -- count all rows ent row
COUNT ( col ) -- count non - NULL SELECT name , score ,
SUM ( col ) -- total
Column ’X’ cannot NOT NULL con- RANK () OVER ( ORDER BY score DESC )
AVG ( col ) -- average be null straint FROM students ;
MAX ( col ) -- maximum
MIN ( col ) -- minimum

-- With GROUP BY
13. Exam Workflow
SELECT dept , COUNT (*) , AVG ( salary )
FROM Employee 1. Create Database 15. Last Minute Reminders
GROUP BY dept
HAVING AVG ( salary ) > 50000; CREATE DATABASE name ;
SHOW DATABASES ; -- S c r e e n s h o t ! ALWAYS:
Remember: WHERE before GROUP BY,
USE name ;
• Use semicolons ;
HAVING after GROUP BY • Name with prefix (lastname )
2. Create Tables (parent→child)
• Take screenshots at each step
CREATE TABLE ...; • Test with SELECT before DELETE
11. Testing Integrity SHOW TABLES ; -- S c r e e n s h o t ! • Check constraint spelling
DESC each_table ; -- S c r e e n s h o t !
• Foreign keys on ”many” side
Entity Integrity Test: 3. Insert Data (parent→child) • Junction tables for N:N
Prepared by Ntlakanipho Mgaguli MySQL Exam Day Cheat Sheet 3

DANGER! Time Management: Good Luck! You’ve got this!

NEVER: Database creation: 5 min
• DELETE/UPDATE without WHERE Table design: 15 min
• Create child before parent Data insertion: 10 min
• Insert into child before parent Testing: 10 min
• Use = NULL (use IS NULL) Queries: 20 min
• Forget ON condition in JOIN