B.S.W.E. Bachelor of Software Engineering Database Systems & SQL 2 — Questions and Answers
Question 1: What is a database index and what does it improve?
- A list of all tables
- A data structure that improves query performance (Correct answer)
- A backup of the database
- A primary key constraint
Correct answer: A data structure that improves query performance
A database index is a data structure that provides fast lookup of data based on column values, significantly improving SELECT query performance.
Question 2: What does the GROUP BY clause do in SQL?
- Sorts results in ascending order
- Groups rows sharing a common value for aggregate functions (Correct answer)
- Filters rows based on conditions
- Joins multiple tables
Correct answer: Groups rows sharing a common value for aggregate functions
GROUP BY groups rows that have the same values in specified columns, enabling aggregate functions like COUNT, SUM, and AVG per group.
Question 3: What is a stored procedure in SQL?
- A backup procedure for the database
- A precompiled set of SQL statements stored in the database (Correct answer)
- A trigger that fires on data changes
- A view that stores query results
Correct answer: A precompiled set of SQL statements stored in the database
A stored procedure is a precompiled collection of SQL statements that can be executed as a single named unit, improving performance and reusability.
Question 4: What is the difference between WHERE and HAVING in SQL?
- WHERE filters before grouping; HAVING filters after grouping (Correct answer)
- WHERE is for strings; HAVING is for numbers
- WHERE is used with JOINs; HAVING is not
- They are interchangeable
Correct answer: WHERE filters before grouping; HAVING filters after grouping
WHERE filters individual rows before aggregation, while HAVING filters groups after the GROUP BY clause has been applied.
Question 5: What is a database view?
- A physical table storing data
- A virtual table based on a SELECT query (Correct answer)
- A database entity-relationship diagram
- A stored procedure without parameters
Correct answer: A virtual table based on a SELECT query
A view is a virtual table defined by a stored SQL query that can be queried like a regular table but stores no data itself.
Question 6: Which normal form eliminates transitive dependencies?
- First Normal Form (1NF)
- Second Normal Form (2NF)
- Third Normal Form (3NF) (Correct answer)
- Boyce-Codd Normal Form (BCNF)
Correct answer: Third Normal Form (3NF)
Third Normal Form (3NF) eliminates transitive dependencies by ensuring every non-key attribute depends only on the primary key.
B.S.W.E. Bachelor of Software Engineering Database Systems & SQL 2
What is a database index and what does it improve?