Practice Questions — Data Types and Constraints (NOT NULL, UNIQUE, PRIMARY KEY)

CREATE TABLE students (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(60) NOT NULL,
    email VARCHAR(100) NOT NULL UNIQUE,
    phone VARCHAR(15),
    dob DATE NOT NULL,
    cgpa DECIMAL(4,2) DEFAULT 0.00 CHECK (cgpa >= 0 AND cgpa <= 10),
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Quick sanity check
DESCRIBE students;

-- (a) age           -> TINYINT UNSIGNED   (range 0-255, 1 byte)
-- (b) aadhaar       -> CHAR(12)            (fixed 12 digits, leading zeros)
-- (c) email         -> VARCHAR(100)        (variable, index-friendly)
-- (d) blog body     -> MEDIUMTEXT          (can exceed 64KB)
-- (e) price INR     -> DECIMAL(10,2)       (exact money, no float errors)
-- (f) order time    -> TIMESTAMP           (auto time-zone, for audit)
-- (g) joining_date  -> DATE                (year-month-day only)

CREATE DATABASE IF NOT EXISTS hr;
USE hr;
DROP TABLE IF EXISTS employees;

CREATE TABLE employees (
    emp_id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50) NOT NULL,
    email VARCHAR(100) NOT NULL UNIQUE,
    salary DECIMAL(10,2) NOT NULL CHECK (salary > 0),
    department ENUM('HR','IT','Finance','Sales','Marketing') NOT NULL,
    hire_date DATE NOT NULL DEFAULT (CURRENT_DATE)
);

-- 3 valid inserts
INSERT INTO employees (name, email, salary, department) VALUES
    ('Kavya Nair',    'kavya@corp.com',  60000.00, 'HR'),
    ('Arjun Mehta',   'arjun@corp.com',  85000.00, 'IT'),
    ('Sneha Patel',   'sneha@corp.com',  72000.50, 'Finance');

-- 1 invalid insert (negative salary)
INSERT INTO employees (name, email, salary, department)
VALUES ('Rahul Joshi', 'rahul@corp.com', -50000.00, 'Sales');
-- Expected error:
-- ERROR 3819 (HY000): Check constraint 'employees_chk_1' is violated.

CREATE TABLE orders (
    order_id INT AUTO_INCREMENT PRIMARY KEY,
    emp_id INT NOT NULL,
    amount DECIMAL(10,2) NOT NULL CHECK (amount > 0),
    order_date DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (emp_id) REFERENCES employees(emp_id)
);

-- Valid
INSERT INTO orders (emp_id, amount) VALUES (1, 1500.00);
INSERT INTO orders (emp_id, amount) VALUES (2, 2499.99);

-- Invalid: emp_id 999 does not exist
INSERT INTO orders (emp_id, amount) VALUES (999, 500.00);
-- Expected:
-- ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails

SELECT * FROM orders;

DROP TABLE IF EXISTS payments_decimal;
DROP TABLE IF EXISTS payments_float;

CREATE TABLE payments_decimal (amount DECIMAL(10,2));
CREATE TABLE payments_float   (amount FLOAT);

-- Insert 10 rows of 0.01 at a time, 10 times => 100 rows
INSERT INTO payments_decimal VALUES
 (0.01),(0.01),(0.01),(0.01),(0.01),(0.01),(0.01),(0.01),(0.01),(0.01);
INSERT INTO payments_decimal SELECT * FROM payments_decimal;
INSERT INTO payments_decimal SELECT * FROM payments_decimal;  -- now 40 rows
INSERT INTO payments_decimal SELECT * FROM payments_decimal;  -- now 80 rows
INSERT INTO payments_decimal VALUES
 (0.01),(0.01),(0.01),(0.01),(0.01),(0.01),(0.01),(0.01),(0.01),(0.01),
 (0.01),(0.01),(0.01),(0.01),(0.01),(0.01),(0.01),(0.01),(0.01),(0.01);
-- now 100 rows

INSERT INTO payments_float VALUES
 (0.01),(0.01),(0.01),(0.01),(0.01),(0.01),(0.01),(0.01),(0.01),(0.01);
INSERT INTO payments_float SELECT * FROM payments_float;
INSERT INTO payments_float SELECT * FROM payments_float;
INSERT INTO payments_float SELECT * FROM payments_float;
INSERT INTO payments_float VALUES
 (0.01),(0.01),(0.01),(0.01),(0.01),(0.01),(0.01),(0.01),(0.01),(0.01),
 (0.01),(0.01),(0.01),(0.01),(0.01),(0.01),(0.01),(0.01),(0.01),(0.01);

SELECT SUM(amount) AS total_decimal FROM payments_decimal;
-- +---------------+
-- | total_decimal |
-- +---------------+
-- |          1.00 |   (100 * 0.01 = exact 1.00)

SELECT SUM(amount) AS total_float FROM payments_float;
-- +----------------------+
-- | total_float          |
-- +----------------------+
-- | 0.9999999776482582   |   (binary float drift!)

-- CONCLUSION:
-- Over just 100 tiny transactions the FLOAT sum is already off by ~0.0001.
-- On 10 million real transactions, this becomes a visible mismatch.
-- Always use DECIMAL for money.

-- Reset
DROP TABLE IF EXISTS enrollments;
DROP TABLE IF EXISTS students;
DROP TABLE IF EXISTS courses;

-- students
CREATE TABLE students (
    student_id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(60) NOT NULL,
    email VARCHAR(100) NOT NULL UNIQUE,
    dob DATE NOT NULL
);

-- courses
CREATE TABLE courses (
    course_id INT AUTO_INCREMENT PRIMARY KEY,
    course_name VARCHAR(80) NOT NULL UNIQUE,
    credits TINYINT NOT NULL CHECK (credits BETWEEN 1 AND 6)
);

-- enrollments (junction)
CREATE TABLE enrollments (
    student_id INT NOT NULL,
    course_id INT NOT NULL,
    enrolled_on DATE NOT NULL DEFAULT (CURRENT_DATE),
    marks TINYINT CHECK (marks BETWEEN 0 AND 100),
    PRIMARY KEY (student_id, course_id),
    FOREIGN KEY (student_id) REFERENCES students(student_id),
    FOREIGN KEY (course_id)  REFERENCES courses(course_id)
);

INSERT INTO students (name, email, dob) VALUES
    ('Aarav Sharma', 'aarav@college.edu', '2005-03-15'),
    ('Priya Iyer',   'priya@college.edu', '2004-11-22'),
    ('Rohan Verma',  'rohan@college.edu', '2005-07-08');

INSERT INTO courses (course_name, credits) VALUES
    ('Database Systems', 4),
    ('Data Structures',  4),
    ('Operating Systems', 3);

INSERT INTO enrollments (student_id, course_id, marks) VALUES
    (1, 1, 87),
    (1, 2, 92),
    (2, 1, 78),
    (2, 3, 85),
    (3, 2, 91);

-- Verify with a simple JOIN (covered in Chapter 11)
SELECT s.name, c.course_name, e.marks
FROM enrollments e
JOIN students s ON s.student_id = e.student_id
JOIN courses  c ON c.course_id  = e.course_id;

-- Expected: 5 rows with student name, course name, and marks.