README.md

📘 SQL Constraints

This README contains SQL exercises demonstrating the use of constraints such as NOT NULL, CHECK, DEFAULT, UNIQUE, and functions like NULLIF() and IFNULL().

---

1. 🔒 NOT NULL Constraint

CREATE TABLE Employees (
    EmpID INT NOT NULL,
    Name VARCHAR(50) NOT NULL,
    Department VARCHAR(30),
    PRIMARY KEY (EmpID)
);

-- ❌ Insert without EmpID
INSERT INTO Employees (Name, Department) VALUES ('John', 'HR');

-- ❌ Insert without Name
INSERT INTO Employees (EmpID, Department) VALUES (1, 'HR');

---

2. 💰 CHECK Constraint (Single Column)

CREATE TABLE Products (
    ProductID INT NOT NULL,
    ProductName VARCHAR(50) NOT NULL,
    Price DECIMAL(10, 2) CHECK (Price > 0),
    PRIMARY KEY (ProductID)
);

-- ❌ Negative price
INSERT INTO Products VALUES (1, 'Pen', -10.00);

-- ❌ Price = 0
INSERT INTO Products VALUES (2, 'Pencil', 0.00);

-- ✅ Valid product
INSERT INTO Products VALUES (3, 'Notebook', 25.50);

---

3. 🎓 CHECK Constraint (Multiple Columns)

CREATE TABLE Students (
    StudentID INT NOT NULL,
    Name VARCHAR(50) NOT NULL,
    Age INT CHECK (Age >= 18),
    Marks INT CHECK (Marks BETWEEN 0 AND 100),
    PRIMARY KEY (StudentID)
);

-- ❌ Age < 18
INSERT INTO Students VALUES (1, 'Amit', 16, 80);

-- ❌ Marks > 100
INSERT INTO Students VALUES (2, 'Sita', 20, 105);

-- ✅ Valid student
INSERT INTO Students VALUES (3, 'Rahul', 21, 85);

---

4. 🕒 DEFAULT Constraint

CREATE TABLE Orders (
    OrderID INT PRIMARY KEY,
    OrderStatus VARCHAR(20) DEFAULT 'Pending',
    CreatedDate DATE DEFAULT CURRENT_DATE
);

-- ✅ Insert with default values
INSERT INTO Orders (OrderID) VALUES (101);

-- 🔍 View the default values
SELECT * FROM Orders WHERE OrderID = 101;

---

5. 🧮 NULLIF() and IFNULL() Functions

CREATE TABLE Customers (
    ID INT PRIMARY KEY,
    Name VARCHAR(50),
    City VARCHAR(50),
    Salary DECIMAL(10, 2)
);

INSERT INTO Customers VALUES 
(1, 'Ramesh', 'Delhi', 5000),
(2, 'Anil', 'Anil', NULL),
(3, 'Sunita', NULL, 7000);

-- 🔎 NULLIF
SELECT ID, NULLIF(Name, City) AS Result FROM Customers;

-- 🔁 IFNULL
SELECT ID, IFNULL(Salary, 5500) AS FinalSalary FROM Customers;

---

6. 🛠️ ALTER Table – Add & Drop Constraints

CREATE TABLE Vehicles (
    VehicleID INT PRIMARY KEY,
    Model VARCHAR(50),
    Year INT
);

-- ➕ Add CHECK constraint
ALTER TABLE Vehicles ADD CONSTRAINT chk_year CHECK (Year >= 2000);

-- ❌ Insert invalid Year
INSERT INTO Vehicles VALUES (1, 'Toyota', 1995);

-- ➖ Drop the constraint
ALTER TABLE Vehicles DROP CONSTRAINT chk_year;

---

7. 🔑 UNIQUE Constraint

CREATE TABLE Users (
    UserID INT PRIMARY KEY,
    Username VARCHAR(50) UNIQUE,
    Email VARCHAR(100) UNIQUE
);

-- ❌ Duplicate Username
INSERT INTO Users VALUES (1, 'kulani', 'kulani@gmail.com');
INSERT INTO Users VALUES (2, 'kulani', 'kulani123@gmail.com');

-- ❌ Duplicate Email
INSERT INTO Users VALUES (3, 'john', 'kulani@gmail.com');