Simple SQL Cheater

Database Operations

Create, Use, and Drop Database

CREATE DATABASE student_db;
USE student_db;
DROP DATABASE student_db;

Table Operations (DDL) & Relationships

Create Table (with Primary & Foreign Keys)

CREATE TABLE students (
    student_id INT PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    birth_date DATE,
    gpa DECIMAL(3,2),
    course_id INT,
    FOREIGN KEY (course_id) REFERENCES courses(course_id)
);

Alter Table (Modify/Add Column)

ALTER TABLE students ADD email VARCHAR(100);
ALTER TABLE students MODIFY first_name VARCHAR(100);
ALTER TABLE students DROP COLUMN gpa;

Drop Table

DROP TABLE students;

Data Manipulation (DML)

Insert Data

INSERT INTO students (student_id, first_name, last_name, course_id)
VALUES (1, 'John', 'Doe', 101);

Update Data

UPDATE students 
SET first_name = 'Jane', course_id = 102 
WHERE student_id = 1;

Delete Data

DELETE FROM students WHERE student_id = 1;

Advanced Data Manipulation (DQL)

Basic Select & Order By

SELECT student_id, first_name 
FROM students 
WHERE course_id = 101 
ORDER BY first_name DESC;

JOIN Queries

SELECT students.first_name, courses.course_name 
FROM students
JOIN courses ON students.course_id = courses.course_id;

Aggregate Functions & Group By

SELECT course_id, COUNT(student_id) as total_students, AVG(gpa) as average_gpa
FROM students 
GROUP BY course_id;

Subqueries

SELECT first_name, last_name 
FROM students 
WHERE course_id IN (
    SELECT course_id FROM courses WHERE department = 'IT'
);

View Operations

Create View

CREATE VIEW IT_Students AS
SELECT first_name, last_name FROM students WHERE course_id = 101;

Querying Views

SELECT * FROM IT_Students;

Drop View

DROP VIEW IT_Students;

Data Types Reference

Data Types required for exam

INT         -- Integer numbers
VARCHAR(n)  -- Variable-length text (e.g., VARCHAR(50))
CHAR(n)     -- Fixed-length text (e.g., CHAR(5))
DECIMAL(p,s)-- Exact decimal (e.g., DECIMAL(10,2) for currency)
DATE        -- Date format (YYYY-MM-DD)
TIME        -- Time format (HH:MM:SS)
DATETIME    -- Date and time combination