Basics
Queries & Operations
Joins & Functions
Data Constraints & Aggregate Functions
Advanced SQL Topics
Database Design & Security
Courses
  • Placement 360 CourseExplore

    • Keys and Constraints Quiz

    Last Updated :
    Discuss
    Comments

    Question 1

    You want to create a 'Enrollments' table to link students to the courses they've enrolled in. Which of the following is the most appropriate and robust table definition for 'Enrollments'?

    • A

      CREATE TABLE Enrollments (StudentID INT, CourseID INT,

      PRIMARY KEY (StudentID, CourseID), FOREIGN KEY (StudentID)

      REFERENCES Students(StudentID), FOREIGN KEY (CourseID)

      REFERENCES Courses(CourseID));

    • B

      CREATE TABLE Enrollments (StudentID INT, CourseID INT,

      FOREIGN KEY (StudentID) REFERENCES Students(StudentID),

      FOREIGN KEY (CourseID) REFERENCES Courses(CourseID));

    • C

      CREATE TABLE Enrollments (StudentID INT, CourseID INT,

      PRIMARY KEY (StudentID, CourseID));

    • D

      CREATE TABLE Enrollments (EnrollmentID INT PRIMARY KEY,

      StudentID INT, CourseID INT);

    Question 2

    Which of the following statements is true regarding the relationship between PRIMARY KEY and UNIQUE constraints in SQL?

    • A

      A table can have multiple PRIMARY KEY constraints but only one UNIQUE constraint.

    • B

      Both PRIMARY KEY and UNIQUE constraints allow multiple NULL values.

    • C

      A PRIMARY KEY constraint is functionally equivalent to a UNIQUE constraint combined with a NOT NULL constraint.

    • D

      A UNIQUE constraint automatically creates a clustered index, while a PRIMARY KEY creates a non-clustered index.

    Question 3

    You want to ensure that the price of any product is always greater than 0. Which of the following constraints is the most appropriate for this?

    • A

      ALTER TABLE Products ADD CONSTRAINT

      nn_price NOT NULL (Price);

    • B

      ALTER TABLE Products ADD CONSTRAINT

      unq_price UNIQUE (Price);

    • C

      ALTER TABLE Products ADD CONSTRAINT

      chk_price CHECK (Price > 0);

    • D

      ALTER TABLE Products ADD CONSTRAINT

      df_price DEFAULT 0.01 FOR Price;

    Question 4

    You want to ensure that every user has a unique email address, but some users might not have provided their email yet. Which of the following is the best way to define the 'Email' column?

    • A

      Email VARCHAR(255) NOT NULL

    • B

      Email VARCHAR(255) UNIQUE

    • C

      Email VARCHAR(255) UNIQUE NOT NULL

    • D

      Email VARCHAR(255) PRIMARY KEY

    Question 5

    The 'Employees' table has a 'DeptID' column that is a FOREIGN KEY referencing the 'DepartmentID' in the 'Departments' table. What happens if you try to delete a department that still has employees assigned to it?

    • A

      The department and all its employees will be deleted.

    • B

      The operation will fail due to a FOREIGN KEY constraint violation.

    • C

      The department will be deleted, but the employees' 'DeptID' will remain unchanged, leading to orphaned records.

    • D

      The department will be deleted, and the 'DeptID' for the affected employees will be set to NULL.

    Question 6

    Which of the following is NOT a valid use of a CHECK constraint?

    • A

      Ensuring that a 'Gender' column can only contain 'Male', 'Female', or 'Other'.

    • B

      Ensuring that a 'StartDate' is always before an 'EndDate' in the same row.

    • C

      Ensuring that the sum of all values in a 'Salary' column does not exceed a certain amount.

    • D

      Ensuring that a 'Discount' column is always between 0 and 1.

    Question 7

    In a table of 'Orders', you want to ensure that every order has a unique 'OrderNumber'. You also have an 'InvoiceNumber' which should also be unique, but not all orders have been invoiced yet. How would you define these columns?

    • A

      OrderNumber INT PRIMARY KEY, InvoiceNumber INT PRIMARY KEY

    • B

      OrderNumber INT NOT NULL, InvoiceNumber INT

    • C

      OrderNumber INT UNIQUE, InvoiceNumber INT UNIQUE

    • D

      OrderNumber INT PRIMARY KEY, InvoiceNumber INT UNIQUE

    Question 8

    You have a table with a 'Status' column that should only accept the values 'Active', 'Inactive', or 'Pending'. Which constraint should you use?

    • A

      CREATE TABLE MyTable (Status VARCHAR(10) CHECK (Status IN ('Active', 'Inactive', 'Pending')));

    • B

      CREATE TABLE MyTable (Status VARCHAR(10) NOT NULL);

    • C

      CREATE TABLE MyTable (Status VARCHAR(10) DEFAULT 'Pending');

    • D

      CREATE TABLE MyTable (Status VARCHAR(10) UNIQUE);

    Question 9

    You want to ensure that the bonus is never more than 20% of the salary. Which query would you use?

    • A

      ALTER TABLE Employees ADD CONSTRAINT

      unq_bonus UNIQUE (Bonus, Salary);

    • B

      ALTER TABLE Employees ADD CONSTRAIN

      chk_bonus CHECK (Bonus <= Salary * 0.2);

    • C

      ALTER TABLE Employees ADD CONSTRAINT

      chk_bonus CHECK (Bonus <= (SELECT Salary * 0.2

      FROM Employees));

    • D

      ALTER TABLE Employees ADD CONSTRAINT

      fk_bonus FOREIGN KEY (Bonus)

      REFERENCES Salaries(MaxBonus);

    Question 10

    You have a 'Products' table where each product has a unique 'ProductCode'. You also want to assign a unique 'SKU' to each product, but some products might not have an SKU assigned yet. Define the table.

    • A

      CREATE TABLE Products (ProductCode VARCHAR(20) NOT NULL, SKU VARCHAR(20));

    • B

      CREATE TABLE Products (ProductCode VARCHAR(20) UNIQUE, SKU VARCHAR(20) UNIQUE);

    • C

      CREATE TABLE Products (ProductCode VARCHAR(20) PRIMARY KEY, SKU VARCHAR(20) UNIQUE);

    • D

      CREATE TABLE Products (ProductCode VARCHAR(20) PRIMARY KEY, SKU VARCHAR(20) PRIMARY KEY);

    Tags:
    SQL

    There are 10 questions to complete.

    Take a part in the ongoing discussion