Assignment: SQL Constraints (Advanced) | SQL for Beginners - Software Development PDF Download

Multiple Choice Questions (MCQs)

Q1: Which SQL constraint is used to establish a link between two tables?
(a) CHECK
(b) FOREIGN KEY
(c) DEFAULT
(d) UNIQUE
Ans: (b)

Q2: The CHECK constraint is used to:
(a) Ensure that all values in a column are unique
(b) Define a default value for a column
(c) Restrict the values that can be entered into a column
(d) Establish a relationship between two tables
Ans: (c)

Q3: What happens if you try to insert a NULL value into a column with the NOT NULL constraint?
(a) The NULL value is accepted
(b) The value is replaced with zero
(c) The database throws an error
(d) The constraint is ignored
Ans: (c)

Q4: How many PRIMARY KEY constraints can a table have?
(a) One
(b) Two
(c) Unlimited
(d) Zero
Ans: (a)

Q5: What is the difference between a FOREIGN KEY and a PRIMARY KEY?
(a) A FOREIGN KEY uniquely identifies records in a table, while a PRIMARY KEY enforces referential integrity
(b) A PRIMARY KEY ensures uniqueness within a table, while a FOREIGN KEY references a primary key in another table
(c) A FOREIGN KEY allows NULL values, while a PRIMARY KEY does not
(d) There is no difference between them
Ans: (b)

Q6: Which SQL constraint ensures that an email column does not contain duplicate values?
(a) CHECK
(b) UNIQUE
(c) DEFAULT
(d) FOREIGN KEY
Ans: (b)

Q7: If a DEFAULT constraint is set on a column and a user inserts a NULL value, what happens?
(a) The default value is applied
(b) NULL is stored in the column
(c) An error is thrown
(d) The constraint is ignored
Ans: (a)

Q8: What does the CASCADE option do in a FOREIGN KEY constraint?
(a) Deletes related records automatically when the referenced record is deleted
(b) Prevents deletion of referenced records
(c) Ignores foreign key violations
(d) Applies the CHECK constraint to foreign key values
Ans: (a)

Higher Order Thinking Skills (HOTS)

Q1: How does enforcing referential integrity with a FOREIGN KEY constraint help in an e-commerce platform managing customer orders?
Ans: A FOREIGN KEY constraint ensures every order is placed by a valid registered customer, preventing orphaned orders.
SQL Implementation:
CREATE TABLE Customers (
    CustomerID INT PRIMARY KEY,
    Name NVARCHAR(100),
    Email NVARCHAR(100) UNIQUE
);

CREATE TABLE Orders (
    OrderID INT PRIMARY KEY,
    CustomerID INT,
    OrderDate DATETIME DEFAULT GETDATE(),
    FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID)
);

Q2: How can the CHECK constraint be used in a ride-sharing app to enforce valid fare and driver ratings?
Ans: CHECK constraints ensure fares are non-negative and driver ratings are within 1–5.
SQL Implementation:
CREATE TABLE Rides (
    RideID INT PRIMARY KEY,
    DriverID INT,
    Fare DECIMAL(10,2) CHECK (Fare >= 0),
    Rating INT CHECK (Rating BETWEEN 1 AND 5)
);

Q3: How can the DEFAULT constraint help automate record creation in a hotel booking system?
Ans: DEFAULT constraint automatically sets booking date to current date and status to 'Pending'.
SQL Implementation:
CREATE TABLE Bookings (
    BookingID INT PRIMARY KEY,
    GuestName NVARCHAR(100),
    BookingDate DATETIME DEFAULT GETDATE(),
    Status NVARCHAR(20) DEFAULT 'Pending'
);

INSERT INTO Bookings (BookingID, GuestName) VALUES (1, 'John Doe');

Q4: How does the UNIQUE constraint help prevent duplicate registrations in a social media platform?
Ans: UNIQUE constraint ensures no two users can share the same username or email.
SQL Implementation:
CREATE TABLE Users (
    UserID INT PRIMARY KEY,
    Username NVARCHAR(50) UNIQUE,
    Email NVARCHAR(100) UNIQUE
);

Q5: How does CASCADE DELETE help maintain data integrity in an online marketplace?
Ans: ON DELETE CASCADE automatically removes a seller’s products when their account is deleted.
SQL Implementation:
CREATE TABLE Sellers (
    SellerID INT PRIMARY KEY,
    Name NVARCHAR(100)
);

CREATE TABLE Products (
    ProductID INT PRIMARY KEY,
    ProductName NVARCHAR(100),
    SellerID INT,
    FOREIGN KEY (SellerID) REFERENCES Sellers(SellerID) ON DELETE CASCADE
);

Fill in the Blanks

1. The ____________ constraint ensures that values in a column match values in another table's primary key.
Ans: FOREIGN KEY

2. The ____________ constraint assigns a preset value to a column when no value is provided.
Ans: DEFAULT

3. The ____________ constraint ensures that a column cannot have empty values.
Ans: NOT NULL

4. The ____________ constraint prevents duplicate values in a specified column.
Ans: UNIQUE

5. The ____________ constraint validates input values based on a specified condition.
Ans: CHECK

True or False

1. A PRIMARY KEY column can contain duplicate values.
Ans: False

2. A FOREIGN KEY column can reference multiple tables.
Ans: False

3. A UNIQUE constraint allows NULL values, while a PRIMARY KEY does not.
Ans: True

4. The CHECK constraint can enforce multiple conditions in a single column.
Ans: True

5. The DEFAULT constraint overrides manually inserted values.
Ans: False

Hands-On Questions

Q1: Write an SQL query to create a Students table where StudentID is the primary key and a ClassID column has a foreign key reference to a Classes table.
Ans:
CREATE TABLE Students (
    StudentID INT PRIMARY KEY,
    StudentName VARCHAR(100) NOT NULL,
    ClassID INT,
    FOREIGN KEY (ClassID) REFERENCES Classes(ClassID)
);

Q2: Create a Users table where the Email column has a UNIQUE constraint, and the SignupDate column has a DEFAULT value of the current date.
Ans:
CREATE TABLE Users (
    UserID INT PRIMARY KEY,
    Email VARCHAR(255) UNIQUE,
    SignupDate DATE DEFAULT CURRENT_DATE
);

Q3: Modify the Employees table to add a CHECK constraint ensuring that the Salary column only accepts values greater than 30,000.
Ans:
ALTER TABLE Employees
ADD CONSTRAINT chk_salary CHECK (Salary > 30000);

Q4: Write an SQL statement to create a Departments table where the ManagerID column is a FOREIGN KEY referencing the Employees table with an ON DELETE CASCADE option.
Ans:
CREATE TABLE Departments (
    DepartmentID INT PRIMARY KEY,
    DepartmentName VARCHAR(100) NOT NULL,
    ManagerID INT,
    FOREIGN KEY (ManagerID) REFERENCES Employees(EmployeeID) ON DELETE CASCADE
);

Q5: Design a table Orders with a PRIMARY KEY on OrderID, a FOREIGN KEY on CustomerID, and a CHECK constraint ensuring that OrderAmount is always greater than zero.
Ans:
CREATE TABLE Orders (
    OrderID INT PRIMARY KEY,
    CustomerID INT,
    OrderAmount DECIMAL(10,2) CHECK (OrderAmount > 0),
    OrderDate DATE NOT NULL,
    FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID)
);