SQL: DDL-DML, queries, views, stored procedures, triggers and SQL injection issues. - Question Bank

1. Which SQL statement is used to undo all the changes made during a transaction?
A) COMMIT;
B) SAVEPOINT;
C) ROLLBACK;
D) ABORT;
2. Which SQL statement is used to save all the changes made during a transaction?
A) ROLLBACK;
B) UNDO;
C) COMMIT;
D) SAVEPOINT;
3. Which SQL statement is used to create a transaction?
A) START TRANSACTION;
B) BEGIN TRANSACTION;
C) CREATE TRANSACTION;
D) TRANSACTION START;
4. What is a Foreign Key in SQL?
A) A key that uniquely identifies a record in its own table.
B) A key that links to the primary key of another table, establishing a relationship.
C) A key used for indexing purposes only.
D) A temporary key used during transaction processing.
5. In SQL, what is a Primary Key?
A) A column that can contain NULL values.
B) A column or set of columns that uniquely identifies each record in a table.
C) A column used to link to another table.
D) A column that stores a foreign reference.
6. What is a key difference between `DELETE` and `TRUNCATE` in SQL?
A) `DELETE` removes all rows, `TRUNCATE` removes only specific rows.
B) `DELETE` is a DML command and can be rolled back; `TRUNCATE` is a DDL command and generally cannot be rolled back.
C) `TRUNCATE` is slower than `DELETE`.
D) `DELETE` logs each row deletion, while `TRUNCATE` does not log.
7. Which SQL command is used to delete all records from a table without deleting the table structure?
A) DELETE FROM table_name;
B) DROP TABLE table_name;
C) TRUNCATE TABLE table_name;
D) REMOVE ALL FROM table_name;
8. What is the purpose of an index in a database?
A) To store redundant data for faster retrieval.
B) To improve the speed of data retrieval operations on a database table.
C) To enforce data integrity constraints.
D) To automatically update records when changes occur.
9. Which SQL statement is used to create an index on a table?
A) CREATE INDEX
B) ADD INDEX
C) MAKE INDEX
D) BUILD INDEX
10. What is the difference between `UNION` and `UNION ALL` in SQL?
A) `UNION ALL` removes duplicates, while `UNION` keeps them.
B) `UNION` removes duplicates, while `UNION ALL` keeps them.
C) `UNION` can only be used with two tables, while `UNION ALL` can be used with multiple.
D) There is no functional difference.
11. What does `UNION` operator do in SQL?
A) It combines the result-sets of two or more SELECT statements, removing duplicate rows.
B) It combines the result-sets of two or more SELECT statements, keeping all rows including duplicates.
C) It joins two tables based on a common column.
D) It filters rows based on a condition.
12. Which SQL statement is used to remove a column from an existing table?
A) ALTER TABLE table_name REMOVE column_name;
B) DROP COLUMN column_name FROM table_name;
C) ALTER TABLE table_name DROP COLUMN column_name;
D) DELETE COLUMN column_name FROM table_name;
13. Which SQL statement is used to add a new column to an existing table?
A) ALTER TABLE table_name ADD column_name datatype;
B) ADD COLUMN column_name datatype TO table_name;
C) CREATE COLUMN column_name datatype IN table_name;
D) MODIFY TABLE table_name ADD column_name datatype;
14. What is a primary defense against SQL Injection attacks?
A) Using complex SQL queries
B) Implementing parameterized queries or prepared statements
C) Storing sensitive data in plain text
D) Disabling all database constraints
15. Which of the following is a common vulnerability that can lead to SQL Injection?
A) Using parameterized queries (prepared statements)
B) Improper validation and sanitization of user input
C) Encrypting database connections
D) Regularly updating database software
16. What is SQL Injection?
A) A method to speed up SQL query execution.
B) A code injection technique used to attack data-driven applications, in which malicious SQL statements are inserted into an entry field for execution.
C) A way to create views in SQL.
D) A process for optimizing database performance.
17. What does `AFTER UPDATE` in a trigger definition signify?
A) The trigger fires before the UPDATE operation begins.
B) The trigger fires only if the UPDATE fails.
C) The trigger fires after the UPDATE operation is completed.
D) The trigger fires before any row is updated.
18. What does `BEFORE INSERT` in a trigger definition signify?
A) The trigger fires after the INSERT operation is completed.
B) The trigger fires before the INSERT operation begins.
C) The trigger fires only if the INSERT fails.
D) The trigger fires after a specific number of rows are inserted.
19. What is a potential benefit of using stored procedures?
A) Increased network traffic
B) Reduced database security
C) Improved performance and code reusability
D) Simpler query writing for complex operations
20. What is a common characteristic of stored procedures?
A) They are always executed immediately upon table modification.
B) They can accept input parameters and return output values.
C) They are dynamically generated for each query.
D) They are primarily used for defining table structures.
21. Which type of `JOIN` returns only the rows where there is a match in both tables?
A) LEFT JOIN
B) RIGHT JOIN
C) FULL OUTER JOIN
D) INNER JOIN
22. Which type of `JOIN` returns all rows when there is a match in either the left or the right table?
A) INNER JOIN
B) LEFT JOIN
C) RIGHT JOIN
D) FULL OUTER JOIN
23. Which type of `JOIN` returns all rows from the left table, and the matched rows from the right table, with NULLs for non-matches on the right?
A) INNER JOIN
B) RIGHT JOIN
C) LEFT JOIN
D) FULL OUTER JOIN
24. What is the purpose of a `JOIN` clause in SQL?
A) To delete records from a table.
B) To combine rows from two or more tables based on a related column.
C) To create a new table.
D) To update existing records.
25. Which SQL clause allows you to specify a condition to filter rows *after* grouping has taken place?
A) WHERE
B) FILTER
C) HAVING
D) GROUP WHERE
26. Which SQL clause allows you to specify a condition to filter rows *before* any grouping takes place?
A) HAVING
B) WHERE
C) FILTER
D) CONDITIONS
27. In a SQL query, what does the `AS` keyword typically do?
A) It filters records based on a condition.
B) It specifies a condition for grouping.
C) It is used to rename a column or table using an alias.
D) It joins two tables together.
28. Which of the following are DML statements?
A) CREATE, ALTER, DROP
B) SELECT, INSERT, UPDATE, DELETE
C) GRANT, REVOKE
D) BEGIN TRANSACTION, COMMIT
29. What is the primary purpose of Data Manipulation Language (DML) in SQL?
A) To define and manage database schema objects
B) To retrieve and modify data within tables
C) To manage transactions
D) To control database security
30. Which of the following are DDL statements?
A) SELECT, INSERT, UPDATE
B) CREATE, ALTER, DROP
C) GRANT, REVOKE
D) COMMIT, ROLLBACK
31. What is the primary purpose of Data Definition Language (DDL) in SQL?
A) To manipulate data within tables
B) To define and manage database schema objects
C) To control user access to the database
D) To retrieve data from the database
32. Which SQL command is used to remove a trigger from the database?
A) DROP TRIGGER
B) DELETE TRIGGER
C) REMOVE TRIGGER
D) ALTER TRIGGER
33. Which SQL command is used to remove a stored procedure from the database?
A) DROP PROCEDURE
B) DELETE PROCEDURE
C) REMOVE PROCEDURE
D) ALTER PROCEDURE
34. Which SQL command is used to remove a view from the database?
A) REMOVE VIEW
B) DELETE VIEW
C) DROP VIEW
D) ALTER VIEW
35. A trigger is a set of actions that are automatically executed by the database system. When are triggers typically executed?
A) When a specific event occurs on a table (INSERT, UPDATE, DELETE)
B) When a user logs into the database
C) When a stored procedure is called
D) At a scheduled time interval
36. Which SQL statement is used to create a trigger?
A) CREATE EVENT
B) CREATE TRIGGER
C) CREATE ACTION
D) CREATE AUTOMATION
37. What is a stored procedure in SQL?
A) A temporary table
B) A pre-compiled SQL statement or set of statements stored in the database
C) A virtual table based on the result-set of an SQL statement
D) A mechanism to automatically execute SQL statements in response to events
38. Which SQL statement is used to create a stored procedure?
A) CREATE ROUTINE
B) CREATE PROCEDURE
C) CREATE STOREDPROC
D) CREATE FUNCTION
39. A view is a type of SQL query that is stored in the database. What is it often referred to as?
A) Stored Function
B) Stored Procedure
C) Virtual Table
D) Temporary Table
40. Which SQL command is used to create a view?
A) CREATE VIEW
B) MAKE VIEW
C) NEW VIEW
D) ADD VIEW
41. Which SQL clause is used with GROUP BY to filter groups based on a specified condition?
A) WHERE
B) FILTER
C) HAVING
D) GROUP FILTER
42. Which SQL clause is used to group rows that have the same values into summary rows?
A) GROUP BY
B) CLUSTER BY
C) GROUP ROWS
D) COLLECT BY
43. Which SQL keyword is used to return only unique values?
A) UNIQUE
B) DISTINCT
C) SINGLE
D) ONE
44. Which SQL keyword is used to specify conditions for a query?
A) CONDITION
B) FILTER
C) WHERE
D) HAVING
45. Which SQL statement is used to modify the structure of a table?
A) MODIFY TABLE
B) ALTER TABLE
C) CHANGE TABLE
D) UPDATE TABLE
46. Which SQL statement is used to delete a table from a database?
A) DELETE TABLE
B) REMOVE TABLE
C) DROP TABLE
D) DESTROY TABLE
47. Which SQL statement is used to create a new table in a database?
A) CREATE TABLE
B) NEW TABLE
C) MAKE TABLE
D) ADD TABLE
48. Which clause is used to sort the result-set in ascending or descending order?
A) SORT BY
B) ORDER BY
C) ARRANGE BY
D) SEQUENCE BY
49. Which SQL statement is used to extract data from a database?
A) GET
B) OPEN
C) SELECT
D) EXTRACT
50. Which SQL statement is used to delete records from a table?
A) DELETE
B) REMOVE
C) ERASE
D) DELETE RECORD
All Question Banks
E-Book SQL: DDL-DML, queries, views, stored procedures, triggers and SQL injection issues. One Line Questions SQL: DDL-DML, queries, views, stored procedures, triggers and SQL injection issues. Online Test SQL: DDL-DML, queries, views, stored procedures, triggers and SQL injection issues.