NEW
Font size
WorksheetsDBMS_II_CSE_C
Total questions: 15
Worksheet time: 11mins
A super key?
A single attribute that is guaranteed to be unique and not null
Any minimal set of attributes that uniquely identifies each tuple
Any set of attributes that functionally determines all attributes in the relation
A set of attributes chosen by the database designer as the main identifier
Why is a primary key often required to be NOT NULL and unique in a DBMS?
primary keys are automatically indexed and cannot contain nulls by definition
every attribute in a table must be unique and not null
SQL syntax does not allow nulls in any attribute
the primary key must uniquely and reliably identify each row
In a table Employee(EmpID, Name, DeptID, Email), where EmpID is the primary key, which of the following could reasonably be a candidate key.
(DeptID, Name)
Name
DeptID
Email
A foreign key primarily used for in a relational database
Automatically create indexes for faster queries
Enforce referential integrity between related tables
Uniquely identify rows within its own table
Minimize redundancy by decomposing relations
Consider a table Order(OrderID, OrderDate, CustomerID) where CustomerID is a foreign key referencing Customer(CustomerID). What must hold for any value stored in Order.CustomerID?
It must be the primary key of the Order table
It must be unique within the Order table
It must be null whenever the order has no products
It must exist as a CustomerID value in the Customer table
A composite key
A key that combines multiple attributes to uniquely identify a row
A primary key that is also a foreign key
A key defined on a derived or computed column
A key that changes automatically whenever a row is updated
In a relation with attributes (A, B, C, D), suppose (A, B) is a candidate key. Which of the following is always true?
(C, D) is a candidate key
A alone is a super key
(A, B, C) is a super key
B alone is a super key
Property of a candidate key in a relation
It is an attribute that allows null values but must be unique
It is a super key with no proper subset that is also a super key
It is a super key that is not chosen as primary key
It is any attribute set that participates in a foreign key relationship
Find the total sales amount per customer in a table named Orders with columns CustomerID, OrderDate, and Amount. Which query best uses aggregate functions with GROUP BY?
SELECT CustomerID, Amount FROM Orders GROUP BY CustomerID;
SELECT CustomerID, SUM(Amount) FROM Orders;
SELECT SUM(Amount) FROM Orders GROUP BY OrderDate;
SELECT CustomerID, SUM(Amount) FROM Orders GROUP BY CustomerID;
Given a table Orders(CustomerID, OrderDate, Amount), you want to display only customers whose total Amount is greater than 1000. Which clause should you use to filter based on the aggregate SUM(Amount) per customer?
WHERE SUM(Amount) > 1000
WHERE Amount > 1000
HAVING SUM(Amount) > 1000
ORDER BY SUM(Amount) > 1000
SELECT Country, COUNT(*) AS NumCustomers FROM Customers GROUP BY Country ORDER BY NumCustomers DESC;. What is the main purpose of the ORDER BY NumCustomers DESC part?
To group rows by NumCustomers before counting them
To sort the grouped result so that countries with more customers appear first
To filter out countries that have zero customers
To rename the NumCustomers column after grouping
SELECT Department, AVG(Salary) AS AvgSalary FROM Employees GROUP BY Department HAVING AVG(Salary) > 60000;. What does the HAVING clause do here?
It sorts departments by average salary
It filters individual rows where Salary > 60000 before grouping
It prevents NULL salaries from being included in the average
It filters departments whose average salary is greater than 60000 after grouping
List the top 3 products by total sales amount from a Sales(ProductID, Amount) table. Which combination of clauses is most appropriate?
WHERE, ORDER BY, HAVING
GROUP BY, ORDER BY, LIMIT
GROUP BY, HAVING, ORDER BY
ORDER BY, GROUP BY, HAVING
In which situation would you most likely use COUNT(DISTINCT CustomerID) instead of COUNT(*) in a query with GROUP BY?
When you want to speed up the query by avoiding duplicates
When you want to count all rows in each group, including duplicates
When you want to count only rows where CustomerID is NULL
When you want to count the number of unique customers per group
Difference between WHERE and HAVING in a query that uses GROUP BY
WHERE filters groups after aggregation; HAVING filters individual rows before grouping
WHERE and HAVING are interchangeable once GROUP BY is used
WHERE filters individual rows before grouping; HAVING filters groups after aggregation
WHERE can use aggregate functions, but HAVING cannot
