Computer Unit Test - SQL Quick Notes
1. Language and Data Manipulation Language (DML)
- DDL: Defines/modifies structure (CREATE, DROP, ALTER)
- DML: Manages data (INSERT, UPDATE, DELETE, SELECT)
2. Introduction to MySQL
- Open-source RDBMS
- Data stored in tables (rows & columns)
- Use primary key to identify rows uniquely
3. Creating a Database
CREATE DATABASE school;
4. Creating a Table
CREATE TABLE students (
id INT PRIMARY KEY,
name VARCHAR(50),
age INT,
grade CHAR(2)
);
5. Data Types (Important)
- INT: Whole numbers
- FLOAT: Decimal numbers
- CHAR(n): Fixed-length text
- VARCHAR(n): Variable-length text
- DATE: YYYY-MM-DD
- BOOLEAN: TRUE/FALSE
6. DDL Commands
DROP TABLE students;
DROP DATABASE school;
ALTER TABLE students ADD gender CHAR(1);
ALTER TABLE students DROP COLUMN grade;
ALTER TABLE students MODIFY age FLOAT;
7. Data Query Language (DQL)
SELECT name, age FROM students WHERE age > 15;
SELECT * FROM students WHERE age BETWEEN 13 AND 17;
SELECT * FROM students WHERE age > 13 AND grade = 'A';
SELECT * FROM students WHERE grade IS NULL;
SELECT * FROM students WHERE grade IS NOT NULL;
8. Data Manipulation (DML)
INSERT INTO students (id, name, age, grade) VALUES (1, 'Aditya', 16, 'A');
DELETE FROM students WHERE id = 1;
UPDATE students SET age = 17 WHERE name = 'Aditya';
9. Aggregate Functions
SELECT SUM(age) FROM students;
SELECT COUNT(*) FROM students;
SELECT MIN(age) FROM students;
SELECT MAX(age) FROM students;
Tips
- SQL commands end with a semicolon (;)
- Use single quotes for text/date
- SQL is not case-sensitive
