SQL Notes
1. SQL Data Definition and Data Types
- Data Definition Language (DDL): Commands to define database objects: CREATE, ALTER,
DROP, TRUNCATE, RENAME.
- Data Types:
• Numeric: INT, SMALLINT, DECIMAL(p,s), FLOAT, REAL
• Character/String: CHAR(n), VARCHAR(n), TEXT
• Date/Time: DATE, TIME, DATETIME, TIMESTAMP
• Boolean: BOOLEAN
2. Constraints
- NOT NULL: Column cannot be NULL
- UNIQUE: Column values must be unique
- PRIMARY KEY: Unique + Not Null identifier
- FOREIGN KEY: Maintains referential integrity
- CHECK: Ensures condition is met
- DEFAULT: Provides default value
3. Queries (SELECT)
Example:
SELECT column1, column2
FROM table_name
WHERE condition
GROUP BY column
HAVING condition
ORDER BY column ASC|DESC;
4. DML – INSERT, UPDATE, DELETE
INSERT INTO Students (id, name, age) VALUES (1, 'John', 20);
UPDATE Students SET age = 21 WHERE id = 1;
DELETE FROM Students WHERE id = 1;
5. Views
- Virtual table based on query.
CREATE VIEW student_view AS
SELECT name, age FROM Students WHERE age > 18;
6. Integrity
- Entity Integrity: Primary key unique and not NULL
- Referential Integrity: Foreign key matches existing PK
- Domain Integrity: Valid values as per constraints
7. Procedures and Functions
CREATE PROCEDURE getAllStudents()
BEGIN
SELECT * FROM Students;
END;
CREATE FUNCTION getAge(student_id INT) RETURNS INT
BEGIN
DECLARE age INT;
SELECT s.age INTO age FROM Students s WHERE s.id = student_id;
RETURN age;
END;
8. Cursor
DECLARE cursor_name CURSOR FOR SELECT name FROM Students;
OPEN cursor_name;
FETCH NEXT FROM cursor_name;
CLOSE cursor_name;
DEALLOCATE cursor_name;
9. Triggers
CREATE TRIGGER age_check
BEFORE INSERT ON Students
FOR EACH ROW
BEGIN
IF NEW.age < 18 THEN
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Age must be >= 18';
END IF;
END;
10. Embedded SQL
EXEC SQL SELECT name INTO :student_name
FROM Students WHERE id = :student_id;
11. Dynamic SQL
EXECUTE IMMEDIATE 'SELECT * FROM ' || table_name;
