Section A — 1 Mark Questions
1. Which SQL command is used to remove access privileges from a user? [1 Mark]
Answer: The REVOKE command is used to remove access privileges.
2. What does the COMMIT command do in SQL? [1 Mark]
Answer: It permanently saves all changes made during the current transaction to the database.
3. Name the aggregate function used to find the average value of a numeric column. [1 Mark]
Answer: The AVG() function.
4. Which TCL command is used to undo changes up to a specific point? [1 Mark]
Answer: The ROLLBACK command is used to undo changes, or specifically, a SAVEPOINT can be referenced to roll back to a partial state.
5. Which character function converts a string to uppercase? [1 Mark]
Answer: The UPPER() function.
6. What is the result of ROUND(15.67, 1)? [1 Mark]
Answer: 15.7.
7. Which function returns the current system date? [1 Mark]
Answer: SYSDATE or CURRENT_DATE depending on the SQL dialect.
8. What does the GRANT command perform? [1 Mark]
Answer: It provides specific user privileges on database objects.
9. How do you count the total number of rows in a table? [1 Mark]
Answer: By using the COUNT(*) function.
10. Which function returns the length of a string? [1 Mark]
Answer: The LENGTH() function.
11. Define a SAVEPOINT. [1 Mark]
Answer: A point in a transaction to which you can roll back without canceling the entire transaction.
12. What does the TRUNC() function do? [1 Mark]
Answer: It truncates a numeric value to a specified number of decimal places.
13. Which function is used to join two strings together? [1 Mark]
Answer: The CONCAT() function.
14. What is the purpose of the MIN() function? [1 Mark]
Answer: It returns the smallest value in a selected column.
15. Name a function to extract the year from a date. [1 Mark]
Answer: The YEAR() or EXTRACT(YEAR FROM date) function.
16. What happens when you execute ROLLBACK without a SAVEPOINT? [1 Mark]
Answer: All uncommitted changes in the current transaction are discarded.
17. Which function calculates the absolute value of a number? [1 Mark]
Answer: The ABS() function.
18. What is the default return of the MOD() function? [1 Mark]
Answer: The remainder of a division operation.
19. Can GRANT be used to give select permissions only? [1 Mark]
Answer: Yes, using GRANT SELECT ON table_name TO user_name.
20. What does the POWER(m, n) function return? [1 Mark]
Answer: It returns m raised to the power of n.
Section B — 2/3 Mark Questions
1. Differentiate between COMMIT and ROLLBACK. [2 Marks]
Answer: COMMIT makes all database changes permanent, whereas ROLLBACK reverses changes that have not yet been committed to the database.
2. Explain the use of GRANT with an example. [3 Marks]
Answer: GRANT is a DCL command used to grant access privileges to database users. Example: GRANT SELECT, INSERT ON Employees TO user1; This gives user1 permission to read and add data to the Employees table.
3. Describe the purpose of SAVEPOINT. [2 Marks]
Answer: A SAVEPOINT allows for partial rollbacks within a long transaction. It enables the user to undo specific operations without aborting the entire set of changes.
4. How do aggregate functions behave with NULL values? [3 Marks]
Answer: Most aggregate functions like SUM(), AVG(), and MAX() ignore NULL values in their calculations. COUNT(*) includes rows with NULLs, but COUNT(column_name) ignores NULLs.
5. Explain SUBSTR() and INSTR() functions. [3 Marks]
Answer: SUBSTR() extracts a portion of a string based on position and length. INSTR() returns the numeric position of a specific character or substring within a given string.
6. How does ROUND() differ from TRUNC() for the number 12.78? [2 Marks]
Answer: ROUND(12.78, 1) returns 12.8 because it rounds up, while TRUNC(12.78, 1) returns 12.7 because it simply chops off the remaining digits.
7. Explain the role of DCL in database security. [2 Marks]
Answer: DCL commands like GRANT and REVOKE ensure database security by controlling user access and restricting operations that unauthorized users can perform on data objects.
8. Provide an example of a date function. [3 Marks]
Answer: The MONTHS_BETWEEN(date1, date2) function returns the number of months between two dates. For example, MONTHS_BETWEEN('2023-03-01', '2023-01-01') returns 2.
9. What is the significance of the MAX() function? [2 Marks]
Answer: MAX() is an aggregate function that finds the highest value in a specified column, which is useful for identifying the latest date or the highest salary in a dataset.
10. Explain the syntax of REVOKE. [3 Marks]
Answer: The syntax is: REVOKE privilege_name ON object_name FROM user_name. It removes previously granted permissions, for example: REVOKE UPDATE ON Orders FROM guest_user.
Section C — 5 Mark Questions
1. Explain the concept of Transaction Control Language (TCL) and its primary commands. [5 Marks]
Introduction
TCL commands are used to manage transactions in a database, ensuring data integrity and consistency by controlling how and when changes are finalized.
Commands
- COMMIT: Permanently saves the current transaction changes to the disk.
- ROLLBACK: Reverts the database state to the last commit point if an error occurs.
- SAVEPOINT: Marks a point in a transaction, allowing partial rollback rather than complete reversal.
Importance
TCL ensures that a series of SQL operations (like transferring money) are treated as a single atomic unit, meaning all operations either succeed together or are rolled back completely if any part fails.
2. Discuss different types of aggregate functions in SQL with examples. [5 Marks]
Explanation
Aggregate functions perform a calculation on a set of values and return a single value. These are typically used with the GROUP BY clause.
Types and Examples
- SUM(column): Calculates the total sum of a numeric column. Example: SELECT SUM(salary) FROM staff;
- AVG(column): Computes the average value of a column. Example: SELECT AVG(price) FROM products;
- MIN(column) / MAX(column): Finds the lowest and highest values. Example: SELECT MAX(age) FROM users;
- COUNT(column): Returns the number of non-null entries.
Observation
These functions are essential for generating reports, summaries, and analytical insights from raw database records.
3. Elaborate on numeric functions with their syntax and usage. [5 Marks]
Overview
Numeric functions are used to perform mathematical calculations on numbers stored in a database.
Functions
- ROUND(n, m): Rounds a number n to m decimal places.
- TRUNC(n, m): Truncates a number n at the m-th decimal position without rounding.
- MOD(m, n): Returns the remainder of m divided by n.
- POWER(m, n): Raises m to the power of n.
- ABS(n): Returns the absolute (positive) value of n.
Example
If a table contains prices, using ROUND(price, 2) ensures that the output is formatted to two decimal points, which is standard for financial applications.
4. Describe the implementation of GRANT and REVOKE for database security. [5 Marks]
Introduction
Grant and Revoke are DCL commands that act as the gatekeepers of a database, ensuring that only authorized individuals can view or modify sensitive information.
Grant Command
The GRANT command assigns permissions. Syntax: GRANT [privileges] ON [object] TO [user]. This allows administrators to provide specific rights like SELECT, INSERT, or DELETE to users based on their role.
Revoke Command
The REVOKE command withdraws previously assigned permissions. Syntax: REVOKE [privileges] ON [object] FROM [user]. This is critical when an employee changes roles or leaves the organization.
Conclusion
Effective use of DCL minimizes the risk of accidental or malicious data modification by enforcing the principle of least privilege.
5. Explain important date and time functions in SQL. [5 Marks]
Purpose
Date and time functions allow developers to manipulate and format temporal data, which is crucial for tracking history, calculating durations, and filtering records by time.
Key Functions
- SYSDATE/CURRENT_DATE: Retrieves the current system date and time.
- ADD_MONTHS(date, n): Adds a specified number of months to a date.
- MONTHS_BETWEEN(date1, date2): Calculates the number of months between two specific dates.
- LAST_DAY(date): Returns the last day of the month for the given date.
- EXTRACT(field FROM date): Pulls out specific components like the year, month, or day from a date object.
Application
These functions are heavily used in business logic, such as calculating the number of days a payment is overdue or generating reports for the current fiscal quarter.