Database and Table Operations

Basic database creation and table management examples:

-- Create database with UTF8 charset
CREATE DATABASE IF NOT EXISTS school_db 
  DEFAULT CHARACTER SET utf8mb4;

USE school_db;

-- Create student profile table
CREATE TABLE IF NOT EXISTS student_profiles (
  student_id SMALLINT UNSIGNED PRIMARY KEY,
  full_name VARCHAR(100),
  birth_year YEAR,
  contact_phone CHAR(15),
  is_enrolled BOOLEAN,
  enrollment_date DATE
) ENGINE=InnoDB CHARSET=utf8mb4;

-- Course catalog table
CREATE TABLE IF NOT EXISTS courses_offered (
  course_code CHAR(7) PRIMARY KEY,
  course_title VARCHAR(150),
  instructor VARCHAR(100),
  max_capacity SMALLINT
);

-- View table structure
DESCRIBE student_profiles;
SHOW CREATE TABLE courses_offered;

Data Type Demonstrations

Integer and decimal type examples:

-- Integer range testing
CREATE TABLE integer_ranges (
  tiny_val TINYINT,
  small_val SMALLINT,
  medium_val MEDIUMINT
);

INSERT INTO integer_ranges VALUES 
  (-128, -32768, 0),
  (127, 32767, 999999);

-- Decimal precision example
CREATE TABLE financial_records (
  transaction_id INT PRIMARY KEY,
  amount DECIMAL(10,2),
  tax_rate DECIMAL(5,4)
);

INSERT INTO financial_records VALUES
  (1001, 199.99, 0.0825),
  (1002, 500.00, 0.0650);

Table Structure Modifications

Schema alteration examples:

-- Add new column to existing table
ALTER TABLE student_profiles
ADD COLUMN major VARCHAR(50) AFTER contact_phone;

-- Modify column properties
ALTER TABLE courses_offered
MODIFY instructor VARCHAR(120) NOT NULL;

-- Rename table
RENAME TABLE student_profiles TO enrolled_students;

-- Add composite primary key
ALTER TABLE enrolled_students
ADD CONSTRAINT pk_student_courses 
PRIMARY KEY (student_id, course_code);

Data Manipulation Examples

CRUD operations demonstration:

-- Insert records with specified columns
INSERT INTO enrolled_students 
  (student_id, full_name, major) 
VALUES 
  (1001, 'Alice Johnson', 'Computer Science'),
  (1002, 'Bob Wilson', 'Mechanical Engineering');

-- Update records with conditions
UPDATE courses_offered
SET max_capacity = 35
WHERE course_code LIKE 'CS%';

-- Delete specific records
DELETE FROM financial_records
WHERE amount < 100.00;

-- Select with calculated fields
SELECT 
  course_code,
  course_title,
  (max_capacity * 0.8) AS recommended_size
FROM courses_offered;

Query Operations

Complex query examples:

-- Grouped aggregate query
SELECT 
  major,
  COUNT(*) AS student_count,
  AVG(TIMESTAMPDIFF(YEAR, birth_year, CURDATE())) AS avg_age
FROM enrolled_students
GROUP BY major
HAVING student_count > 10;

-- Multi-table join example
SELECT 
  s.full_name,
  c.course_title,
  f.amount
FROM enrolled_students s
JOIN course_enrollments ce ON s.student_id = ce.student_id
JOIN financial_records f ON ce.transaction_id = f.transaction_id
WHERE f.tax_rate > 0.07;

-- Date range filtering
SELECT * FROM enrollment_dates
WHERE enrollment_date BETWEEN '2023-01-01' AND '2023-12-31';