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

Multiple Choice Questions (MCQs)

Q1: What is the purpose of SQL constraints?
(a) To increase database storage
(b) To enforce rules on data in tables
(c) To delete duplicate records
(d) To speed up query execution
Ans: (b)

Q2: Which of the following is NOT an SQL constraint?
(a) NOT NULL
(b) PRIMARY KEY
(c) INDEX
(d) FOREIGN KEY
Ans: (c)

Q3: What does the NOT NULL constraint do?
(a) Ensures a column does not have duplicate values
(b) Ensures a column cannot have NULL values
(c) Ensures referential integrity
(d) Automatically generates unique values
Ans: (b)

Q4: Which SQL constraint ensures all values in a column are unique?
(a) FOREIGN KEY
(b) CHECK
(c) UNIQUE
(d) DEFAULT
Ans: (c)

Q5: What happens if a UNIQUE constraint is applied to a column that already has duplicate values?
(a) The database ignores the duplicates
(b) The duplicate values are automatically removed
(c) An error occurs
(d) The constraint is applied without effect
Ans: (c)

Q6: Which constraint is used to set a default value for a column?
(a) UNIQUE
(b) CHECK
(c) DEFAULT
(d) PRIMARY KEY
Ans: (c)

Q7: What is the main purpose of the FOREIGN KEY constraint?
(a) To enforce referential integrity between tables
(b) To ensure all values in a column are unique
(c) To allow NULL values
(d) To set a default value for a column
Ans: (a)

Q8: Which constraint can be used to ensure that an age column only accepts values greater than 18?
(a) NOT NULL
(b) UNIQUE
(c) CHECK
(d) PRIMARY KEY
Ans: (c)

Higher Order Thinking Skills (HOTS)

Q1: How can you ensure that every employee in an HR system has a unique Employee ID and that it cannot be left blank?
Ans: To guarantee that each employee has a unique and non-null Employee ID, we use the PRIMARY KEY constraint.
Example:
CREATE TABLE Employees (
    EmployeeID INT PRIMARY KEY, -- Ensures uniqueness and prevents NULL values
    Name NVARCHAR(100),
    Email NVARCHAR(100) UNIQUE, -- Optional: Ensures email uniqueness
    Department NVARCHAR(50)
);

• This ensures no two employees have the same EmployeeID, and it cannot be left empty.

Q2: In an e-commerce database, how do you ensure that product SKUs (Stock Keeping Units) are unique, but some products might not have an SKU assigned yet?
Ans: Since SKUs must be unique but not mandatory, we use the UNIQUE constraint instead of PRIMARY KEY.
Example:
CREATE TABLE Products (
    ProductID INT PRIMARY KEY,
    ProductName NVARCHAR(100) NOT NULL,
    SKU NVARCHAR(50) UNIQUE -- Ensures uniqueness but allows NULL values
);

• If an SKU is provided, it must be unique, but leaving it blank (NULL) is allowed.

Q3: How do you prevent an order from being placed for a customer that doesn’t exist in the system?
Ans: Use a FOREIGN KEY constraint to ensure that CustomerID in Orders refers to a valid customer in the Customers table.
Example:
CREATE TABLE Customers (
    CustomerID INT PRIMARY KEY,
    Name NVARCHAR(100)
);

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

• This prevents inserting an order with a CustomerID that doesn’t exist in the Customers table.

Q4: How can you enforce that an employee's salary is always above a company-mandated minimum value?
Ans: A CHECK constraint ensures that salary values meet the business rule.
Example:
CREATE TABLE Employees (
    EmployeeID INT PRIMARY KEY,
    Name NVARCHAR(100),
    Salary DECIMAL(10,2) CHECK (Salary >= 30000)
);

• Any attempt to insert or update an employee with a salary below 30,000 will be rejected.

Q5: How do you make sure new orders automatically use the current date if no order date is provided?
Ans: Use the DEFAULT constraint to assign the current date when no value is given.
Example:
CREATE TABLE Orders (
    OrderID INT PRIMARY KEY,
    OrderDate DATETIME DEFAULT GETDATE(),
    CustomerID INT
);
INSERT INTO Orders (OrderID, CustomerID) VALUES (1, 101);

• OrderDate will automatically be set to the current date.

Fill in the Blanks

1. The ___________ constraint ensures that each record in a table is uniquely identifiable.
Ans: PRIMARY KEY

2. The ___________ constraint is used to limit the range of values in a column.
Ans: CHECK

3. A ___________ establishes a relationship between two tables by linking a column to another table's primary key.
Ans: FOREIGN KEY

4. The ___________ constraint assigns a pre-defined value to a column if no value is provided.
Ans: DEFAULT

5. The ___________ constraint ensures that a column must always have a value.
Ans: NOT NULL

True or False

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

2. The DEFAULT constraint sets a column value when no value is provided.
Ans: True

3. A FOREIGN KEY column must always reference a column with a UNIQUE constraint.
Ans: True

4. The CHECK constraint can only be applied to numeric columns.
Ans: False

5. The UNIQUE constraint allows duplicate NULL values.
Ans: True

Hands-On Questions

Q1: Write an SQL query to create a Products table with a ProductID column as the primary key and a Price column that does not accept negative values.
Ans:
CREATE TABLE Products (
ProductID INT PRIMARY KEY,
ProductName VARCHAR(100) NOT NULL,
Price DECIMAL(10,2) CHECK (Price >= 0)
);

Q2: Modify the Customers table to add a CHECK constraint ensuring that the Age column only accepts values greater than 18.
Ans:
ALTER TABLE Customers
ADD CONSTRAINT chk_age CHECK (Age > 18);

Q3: Create an Orders table where the CustomerID column is a FOREIGN KEY referencing the Customers table.
Ans:
CREATE TABLE Orders (
OrderID INT PRIMARY KEY,
CustomerID INT,
OrderDate DATE NOT NULL,
TotalAmount DECIMAL(10,2),
FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID)
);

Q4: Write an SQL statement to add a UNIQUE constraint to the Email column of the Users table.
Ans:
ALTER TABLE Users
ADD CONSTRAINT unique_email UNIQUE (Email);

Q5: Design an SQL table with a DEFAULT constraint on a CreatedAt column, setting the default value to the current timestamp.
Ans:
CREATE TABLE Logs (
LogID INT PRIMARY KEY,
EventDescription VARCHAR(255) NOT NULL,
CreatedAt TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);