DBMS

You are designing a database for a university. Each department can offer multiple courses, and each course can be taught by multiple instructors. However, each course can be associated with only one department. How would you represent this relationship in an ER diagram?

• A many-to-many relationship between Department and Course, with a connecting entity for Instructor

• A one-to-many relationship between Department and Course, with a many-to-many relationship between Course and Instructor

• A one-to-many relationship between Department and Instructor, with a many-to-many relationship between Course and Department

• A one-to-many relationship between Instructor and Course, with a one-to-many relationship between Department and Instructor

In a banking application, you need to store the history of transactions. Each transaction can be associated with one or more accounts, and you need to ensure data integrity and avoid anomalies. Which normalization level should be applied to the transaction table?

• 1NF

• 2NF

• 3NF

• BCNF

A database for a hospital needs to ensure that patient records cannot be deleted as long as they have associated medical records. Which of the following constraints would you use to enforce this rule?

• PRIMARY KEY

• FOREIGN KEY with ON DELETE CASCADE

• FOREIGN KEY with ON DELETE NO ACTION

• UNIQUE

You need to retrieve the top 5 highest-paid employees in each department from the employees table, which has columns emp_id, department_id, and salary. Which SQL query would you use?

• SELECT emp_id, department_id, salary FROM employees ORDER BY department_id, salary DESC LIMIT 5;

• SELECT emp_id, department_id, salary FROM employees WHERE salary IN (SELECT DISTINCT TOP 5 salary FROM employees ORDER BY department_id, salary DESC);

• SELECT emp_id, department_id, salary FROM (SELECT emp_id, department_id, salary, DENSE_RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) as rank FROM employees) WHERE rank <= 5;

• SELECT emp_id, department_id, salary FROM employees GROUP BY department_id ORDER BY salary DESC LIMIT 5;

A sales database has a table orders with columns order_id, customer_id, order_date, and total_amount. You are asked to find the total revenue generated for each customer in the current year. Which SQL query would you use?

• A sales database has a table orders with columns order_id, customer_id, order_date, and total_amount. You are asked to find the total revenue generated for each customer in the current year. Which SQL query would you use?

• SELECT customer_id, SUM(total_amount) FROM orders WHERE order_date >= DATE_SUB(CURRENT_DATE(), INTERVAL 1 YEAR) GROUP BY customer_id;

• SELECT customer_id, total_amount FROM orders WHERE YEAR(order_date) = YEAR(CURRENT_DATE());

• SELECT customer_id, SUM(total_amount) FROM orders WHERE order_date BETWEEN DATE_SUB(CURRENT_DATE(), INTERVAL 1 YEAR) AND CURRENT_DATE() GROUP BY customer_id;

A university database contains a table with student IDs and their grades. To find students who have received the same grade in multiple subjects, which SQL clause combination would be most appropriate?

• SELECT, WHERE

• GROUP BY, HAVING

• ORDER BY, LIMIT

• DISTINCT, COUNT

A database is experiencing performance issues due to the large size of a particular table. Which of the following techniques would most likely improve query performance?

• Add more columns to the table

• Normalize the table

• Denormalize the table

• Denormalize the table

In a library database, books are categorized under different genres. To find the genre with the most books, which SQL function would be used?

• MAX()

• COUNT(*)

• GROUP BY

• AVG()

You have two tables, one with customer data and another with orders. To retrieve customers who have not placed any orders, which SQL join type should you use?

• INNER JOIN

• LEFT JOIN

• RIGHT JOIN

• FULL OUTER JOIN

In a multi-user environment, you need to ensure that a row in a table is not updated simultaneously by two different users. Which database feature will help manage this?

• Transactions

• Indexing

• Locking

• Caching