WorksheetsQuiz on collegeDB
Total questions: 31
Worksheet time: 18mins
CREATE DATABASE CollegeDB;
USE CollegeDB;
CREATE TABLE Department (
DeptID INT PRIMARY KEY AUTO_INCREMENT,
DeptName VARCHAR(50) NOT NULL UNIQUE,
Location VARCHAR(50)
);
CREATE TABLE Student (
StudentID INT PRIMARY KEY AUTO_INCREMENT,
Name VARCHAR(50) NOT NULL,
Gender CHAR(1) CHECK (Gender IN ('M', 'F')),
DOB DATE NOT NULL,
DeptID INT,
Email VARCHAR(100) UNIQUE,
Phone VARCHAR(15) DEFAULT 'N/A',
FOREIGN KEY (DeptID) REFERENCES Department(DeptID)
ON DELETE SET NULL
ON UPDATE CASCADE
);
CREATE TABLE Course (
CourseID INT PRIMARY KEY AUTO_INCREMENT,
CourseName VARCHAR(100) NOT NULL,
Credits INT CHECK (Credits BETWEEN 1 AND 6),
DeptID INT NOT NULL,
FOREIGN KEY (DeptID) REFERENCES Department(DeptID)
ON DELETE CASCADE
);
CREATE TABLE Enrollment (
EnrollmentID INT PRIMARY KEY AUTO_INCREMENT,
StudentID INT NOT NULL,
CourseID INT NOT NULL,
EnrollDate DATE DEFAULT (CURRENT_DATE),
Grade CHAR(2) CHECK (Grade IN ('A', 'B', 'C', 'D', 'F', 'NA')),
FOREIGN KEY (StudentID) REFERENCES Student(StudentID)
ON DELETE CASCADE,
FOREIGN KEY (CourseID) REFERENCES Course(CourseID)
ON DELETE CASCADE,
UNIQUE (StudentID, CourseID)
);
Based on the above passage, which column is the primary key in the Department table?
DeptName
DeptID
Location
CourseID
Which column is the primary key in the Department table?
DeptName
DeptID
Location
CourseID
The Student table has a Gender column with a CHECK constraint. Which values are valid?
'M' and 'F'
'Male' and 'Female'
Any single letter
Only 'F'
What happens if a Department is deleted that still has students linked to it?
Error occurs
Student records are deleted
Student DeptID becomes NULL
No effect
Which constraint ensures no two students enroll in the same course twice?
PRIMARY KEY
UNIQUE
CHECK
DEFAULT
Which table has both StudentID and CourseID as foreign keys?
Course
Department
Enrollment
Student
The default value of Phone in the Student table is:
NULL
'Unknown'
'N/A'
Empty string
The Course table has a CHECK constraint on credits. Which entry is invalid?
3
5
6
8
If the Department name must not be repeated, which constraint is used?
NOT NULL
UNIQUE
DEFAULT
FOREIGN KEY
Which constraint ensures that no NULL values are allowed in DeptName?
UNIQUE
NOT NULL
CHECK
FOREIGN KEY
Which of the following is a referential integrity constraint?
UNIQUE
CHECK
FOREIGN KEY
DEFAULT
To get all students from the “Computer Science” department:
INNER JOIN
LEFT JOIN
CROSS JOIN
RIGHT JOIN
To find students who have not enrolled in any course, which join would be most suitable?
INNER JOIN
LEFT JOIN with WHERE EnrollmentID IS NULL
CROSS JOIN
RIGHT JOIN
To count total courses offered by each department:
HAVING
WHERE
GROUP BY
ORDER BY
To show students born after 2002:
GROUP BY
HAVING
WHERE
ORDER BY
What is returned by:
Number of students
Number of courses
Total enrollment records
Total departments
To change a student’s department, which command is used?
ALTER TABLE
UPDATE
INSERT
RENAME
Which SQL keyword is used to prevent duplicate rows in query output?
DISTINCT
UNIQUE
CHECK
GROUP BY
To delete a department permanently:
DROP DATABASE
DELETE FROM Department
TRUNCATE Department
ALTER TABLE Department
What happens when we insert a student with duplicate email?
Inserts successfully
Fails due to UNIQUE constraint
Replaces old record
Sets email to NULL
Which query lists all courses with 4 credits?
SELECT * FROM Course WHERE Credits = 4;
SELECT CourseName FROM Course HAVING Credits = 4;
SELECT * FROM Course GROUP BY Credits = 4;
SELECT Credits = 4 FROM Course;
If you delete a course, what happens to enrollments linked to it?
They remain
They are also deleted
Set to NULL
Database error
Which SQL statement adds a new column “Address” to the Student table?
ADD COLUMN Address VARCHAR(100)
ALTER TABLE Student ADD Address VARCHAR(100);
UPDATE Student ADD Address VARCHAR(100);
INSERT COLUMN Address VARCHAR(100);
Which of the following is NOT a DML command?
SELECT
INSERT
DELETE
CREATE
Which command removes all data from Course but keeps the structure?
DROP TABLE
DELETE FROM Course
TRUNCATE TABLE Course
REMOVE TABLE Course
To change the default phone value from 'N/A' to 'Unknown':
ALTER TABLE Student MODIFY Phone DEFAULT 'Unknown';
UPDATE Student SET DEFAULT 'Unknown';
CHANGE DEFAULT Phone TO 'Unknown';
INSERT DEFAULT 'Unknown';
Which query lists the number of students per department?
SELECT DeptID, COUNT(*) FROM Student GROUP BY DeptID;
SELECT DeptID, COUNT(*) FROM Student;
SELECT DeptID FROM Student;
SELECT COUNT(*) FROM Student;
Which of these ensures data consistency between tables?
DEFAULT
FOREIGN KEY
CHECK
UNIQUE
Which of these will violate referential integrity?
Deleting a department having students (ON DELETE SET NULL)
Inserting student with non-existing DeptID
Updating department name
Viewing data
To display department names and number of courses per department:
SELECT D.DeptName, COUNT(C.CourseID) FROM Department D JOIN Course C ON D.DeptID = C.DeptID GROUP BY D.DeptName;
SELECT D.DeptName FROM Department;
SELECT COUNT(C.CourseID) FROM Course;
SELECT D.DeptName, C.CourseID FROM Department D JOIN Course C;
Which of the following ensures automatic date entry in Enrollment table?
DEFAULT constraint
CHECK constraint
UNIQUE constraint
PRIMARY KEY
