DB Exam

Database Systems (Week 5 & 6)

CREATE DATABASE & USE

Commands to create and select an active database.

Basic Creation

basic-creation

CREATE DATABASE db_name;
SHOW DATABASES;
USE db_name;

CREATE TABLE with Constraints

Creating a table with Primary Key, Foreign Key, Default, and cascades.

💡

PRIMARY KEY = UNIQUE + NOT NULL

💡

UNIQUE = No duplicate values allowed

💡

NOT NULL = Cannot be empty

💡

DEFAULT = Automatically assigns a value

💡

FOREIGN KEY = Links table to a parent table

💡

ON DELETE CASCADE = Removes dependent rows automatically.

Students and Departments Example

students-and-departments-example

CREATE TABLE students (
  id INT PRIMARY KEY,
  name VARCHAR(50) NOT NULL,
  email VARCHAR(100) UNIQUE,
  age INT DEFAULT 18,
  dept_id INT,
  FOREIGN KEY (dept_id) REFERENCES departments(id) ON DELETE CASCADE
);

ALTER TABLE

Modifying existing table structure.

Common Alternations

common-alternations

-- Add Column
ALTER TABLE students ADD city VARCHAR(50);

-- Modify Column
ALTER TABLE students MODIFY age INT;

-- Rename Column
ALTER TABLE students CHANGE COLUMN email abc VARCHAR(20);

-- Drop Column
ALTER TABLE students DROP COLUMN city;

-- Add Constraint
ALTER TABLE students ADD CONSTRAINT chk_age CHECK (age >= 18);

DROP, TRUNCATE & RENAME

Removing tables entirely, clearing data, or renaming them.

💡

DROP: Deletes the entire table structure and data (DDL).

💡

TRUNCATE: Empties the table but keeps the structure intact. Very fast! (DDL).

💡

DELETE: Removes rows one by one, can be rolled back (DML).

Remove Operations

remove-operations

DROP DATABASE db_name;
DROP TABLE students;

TRUNCATE TABLE students; -- Clears all data, resets identity

RENAME TABLE Orders TO Customer_Orders;