Section A — 1 Mark Questions
1. Which SQL command is used to add new rows to a table? [1 Mark]
Answer: INSERT
2. What is the primary purpose of the SELECT command? [1 Mark]
Answer: To retrieve data from a database.
3. Which command is used to modify existing data in a table? [1 Mark]
Answer: UPDATE
4. Which command is used to remove records from a table? [1 Mark]
Answer: DELETE
5. What keyword is used to eliminate duplicate rows in a result set? [1 Mark]
Answer: DISTINCT
6. Which clause is used to filter rows in a SELECT query? [1 Mark]
Answer: WHERE clause
7. What is a database view? [1 Mark]
Answer: A virtual table based on the result-set of an SQL statement.
8. Which JOIN type returns all records from both tables when there is a match? [1 Mark]
Answer: INNER JOIN
9. What does the UNIQUE constraint ensure? [1 Mark]
Answer: It ensures that all values in a column are different.
10. Which operator is used to check if a value is within a range? [1 Mark]
Answer: BETWEEN
11. What is the purpose of the LIKE operator? [1 Mark]
Answer: It is used in a WHERE clause to search for a specified pattern in a column.
12. Which constraint ensures a column cannot contain NULL values? [1 Mark]
Answer: NOT NULL
13. What is a nested subquery? [1 Mark]
Answer: A query within another SQL query.
14. Which clause is used to sort the result set? [1 Mark]
Answer: ORDER BY
15. What is the function of the COUNT() aggregate function? [1 Mark]
Answer: It returns the number of rows that match a specified criterion.
16. Which keyword is used to limit the number of records returned? [1 Mark]
Answer: LIMIT (or TOP depending on dialect)
17. What does a LEFT JOIN return? [1 Mark]
Answer: All rows from the left table and the matched rows from the right table.
18. What is the purpose of the GROUP BY clause? [1 Mark]
Answer: To group rows that have the same values into summary rows.
19. Which constraint defines a relationship between two tables? [1 Mark]
Answer: FOREIGN KEY
20. What is the function of the HAVING clause? [1 Mark]
Answer: It is used to filter data after aggregation (GROUP BY).
Section B — 2/3 Mark Questions
1. Differentiate between DELETE and DROP. [2 Marks]
Answer: DELETE is a DML command used to remove specific rows from a table while keeping the table structure. DROP is a DDL command that removes the entire table structure and its data from the database.
2. Explain the purpose of a FOREIGN KEY constraint with an example. [3 Marks]
Answer: A foreign key is a field in one table that refers to the primary key in another table. It ensures referential integrity. For example, an 'Orders' table may have a 'CustomerID' foreign key that must exist in the 'Customers' table.
3. Briefly describe the two types of subqueries. [2 Marks]
Answer: Non-correlated subqueries execute independently of the outer query once. Correlated subqueries depend on the outer query, executing once for each row processed by the outer query.
4. What is the role of an UPDATE statement? Provide a generic syntax. [2 Marks]
Answer: The UPDATE statement modifies existing records. Syntax: UPDATE table_name SET column1 = value1 WHERE condition.
5. List three aggregate functions and their uses. [3 Marks]
Answer: 1. SUM(): Calculates the total of a numeric column. 2. AVG(): Returns the average value. 3. MAX(): Returns the largest value in a column.
6. Explain the difference between WHERE and HAVING clauses. [3 Marks]
Answer: WHERE filters rows before the grouping process occurs. HAVING filters the result set after the GROUP BY operation is applied. WHERE cannot be used with aggregate functions, while HAVING can.
7. What is a View, and why is it used? [2 Marks]
Answer: A view is a virtual table representing the result of a query. It is used to simplify complex queries for users and to enhance security by restricting access to specific columns.
8. Briefly explain the implementation of an INNER JOIN. [2 Marks]
Answer: An INNER JOIN combines rows from two or more tables based on a related column between them, returning only the rows where there is a match in both tables.
9. What are the two types of joins commonly used? [2 Marks]
Answer: INNER JOIN (returns only matched rows) and OUTER JOIN (includes LEFT, RIGHT, or FULL joins that return matched rows plus unmatched rows from one or both tables).
10. Explain the UNIQUE constraint. [2 Marks]
Answer: The UNIQUE constraint ensures that all values in a column are distinct, preventing duplicate entries. Unlike PRIMARY KEY, a table can have multiple UNIQUE columns.
Section C — 5 Mark Questions
1. Explain the concept of JOINs in SQL and describe three types of joins with examples. [5 Marks]
Answer:
Introduction
JOINs are used to combine rows from two or more tables based on a related column between them.
Types of Joins
- INNER JOIN: Returns records that have matching values in both tables. Example: SELECT * FROM A INNER JOIN B ON A.id = B.a_id.
- LEFT JOIN: Returns all records from the left table and the matched records from the right table. If no match, NULL is returned for right-side columns.
- FULL OUTER JOIN: Returns all records when there is a match in either the left or the right table.
Conclusion
Joins allow for normalized database design, where data is split across tables to minimize redundancy, while still allowing the retrieval of related information.
2. Discuss the importance of constraints in database management and explain any three standard constraints. [5 Marks]
Answer:
Importance of Constraints
Constraints enforce rules on data in a table, ensuring accuracy, reliability, and integrity of the data within the database.
Types of Constraints
- PRIMARY KEY: Uniquely identifies each record in a table. It cannot contain NULL values.
- NOT NULL: Ensures that a column cannot have a NULL value, forcing the user to provide a value during data entry.
- CHECK: Ensures that all values in a column satisfy a specific condition, such as age >= 18.
Application
By implementing these at the database level, developers prevent invalid data from entering the system regardless of which application interface is used.
3. Describe the process of using Nested Subqueries and provide a scenario where they are essential. [5 Marks]
Answer:
Introduction
A nested subquery is a query embedded within another SQL query, often used to perform complex operations where the outer query relies on the result of the inner query.
Process
The inner query is executed first. Its resulting output is then passed to the outer query, which uses this result to filter or compute its final set of data. This allows for multi-step logic within a single SQL command.
Scenario
Suppose you need to find all employees earning more than the average salary. The inner query would calculate the AVG(salary), and the outer query would select employee names where salary > (inner_query_result).
Conclusion
Subqueries allow for cleaner and more logical expression of complex data requirements that would otherwise require multiple separate queries or application-side processing.
4. Explain the role of the DML group of commands: SELECT, INSERT, UPDATE, and DELETE. [5 Marks]
Answer:
Overview
Data Manipulation Language (DML) is the subset of SQL used to manage and manipulate the data contained in database objects.
Commands
- SELECT: Used to retrieve and fetch data from a database. It is the most frequently used command.
- INSERT: Adds new rows or records into an existing table.
- UPDATE: Used to modify existing data in one or more records in a table.
- DELETE: Removes one or more existing records from a database table.
Importance
DML commands are the primary interface for users to interact with the application data. Without these, a database would be a static storage container rather than a dynamic information management system.
5. Explain the purpose and implementation of Database Views. What are their advantages? [5 Marks]
Answer:
Introduction
A View is a virtual table based on the result set of an SQL SELECT statement. It does not store the data itself; rather, it stores the query definition.
Implementation
A view is created using the CREATE VIEW statement followed by the query. Once created, it can be queried just like a normal table.
Advantages
- Simplicity: It hides complex joins and subqueries from the end-user, presenting a simple interface.
- Security: By creating a view that only exposes specific columns, administrators can restrict access to sensitive data like salaries or passwords.
- Consistency: Views provide a consistent interface for users even if the underlying table structure changes.
Conclusion
Views are a powerful abstraction tool that helps in maintaining database structure while providing flexible data access.