SQL: DDL-DML, queries, views, stored procedures, triggers and SQL injection issues. - One Line Questions
1.
What is a key difference between `DELETE` and `TRUNCATE` in SQL? —
`DELETE` is a DML command and can be rolled back; `TRUNCATE` is a DDL command and generally cannot be rolled back.
2.
What is the difference between `UNION` and `UNION ALL` in SQL? —
`UNION` removes duplicates, while `UNION ALL` keeps them.
3.
In SQL, what is a Primary Key? —
A column or set of columns that uniquely identifies each record in a table.
4.
What is a Foreign Key in SQL? —
A key that links to the primary key of another table, establishing a relationship.
5.
What is SQL Injection? —
A code injection technique used to attack data-driven applications, in which malicious SQL statements are inserted into an entry field for execution.
6.
What is a stored procedure in SQL? —
A pre-compiled SQL statement or set of statements stored in the database
7.
Which SQL statement is used to insert new records in a table? —
INSERT INTO
8.
Which SQL statement is used to add a new column to an existing table? —
ALTER TABLE table_name ADD column_name datatype;
9.
Which SQL statement is used to remove a column from an existing table? —
ALTER TABLE table_name DROP COLUMN column_name;
10.
Which SQL statement is used to undo all the changes made during a transaction? —
ROLLBACK;
11.
Which SQL keyword is used to specify conditions for a query? —
WHERE
12.
Which SQL statement is used to create a trigger? —
CREATE TRIGGER
13.
Which SQL statement is used to create an index on a table? —
CREATE INDEX
14.
Which SQL statement is used to create a stored procedure? —
CREATE PROCEDURE
15.
Which SQL statement is used to create a new table in a database? —
CREATE TABLE
16.
Which SQL command is used to create a view? —
CREATE VIEW
17.
Which of the following are DML statements? —
SELECT, INSERT, UPDATE, DELETE
18.
Which SQL statement is used to delete records from a table? —
DELETE
19.
Which SQL command is used to delete all records from a table without deleting the table structure? —
TRUNCATE TABLE table_name;
20.
Which SQL statement is used to delete a table from a database? —
DROP TABLE
21.
Which SQL command is used to remove a stored procedure from the database? —
DROP PROCEDURE
22.
Which SQL command is used to remove a trigger from the database? —
DROP TRIGGER
23.
Which SQL statement is used to extract data from a database? —
SELECT
24.
Which SQL clause is used to group rows that have the same values into summary rows? —
GROUP BY
25.
Which SQL clause allows you to specify a condition to filter rows *before* any grouping takes place? —
WHERE
26.
What is a potential benefit of using stored procedures? —
Improved performance and code reusability
27.
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? —
LEFT JOIN
28.
Which type of `JOIN` returns all rows when there is a match in either the left or the right table? —
FULL OUTER JOIN
29.
What does `UNION` operator do in SQL? —
It combines the result-sets of two or more SELECT statements, removing duplicate rows.
30.
In a SQL query, what does the `AS` keyword typically do? —
It is used to rename a column or table using an alias.
31.
Which type of `JOIN` returns only the rows where there is a match in both tables? —
INNER JOIN
32.
Which SQL statement is used to modify the structure of a table? —
ALTER TABLE
33.
Which SQL command is used to remove a view from the database? —
DROP VIEW
34.
Which SQL statement is used to save all the changes made during a transaction? —
COMMIT;
35.
Which of the following are DDL statements? —
CREATE, ALTER, DROP
36.
Which clause is used to sort the result-set in ascending or descending order? —
ORDER BY
37.
Which SQL statement is used to create a transaction? —
BEGIN TRANSACTION;
38.
A view is a type of SQL query that is stored in the database. What is it often referred to as? —
Virtual Table
39.
What does `BEFORE INSERT` in a trigger definition signify? —
The trigger fires before the INSERT operation begins.
40.
What does `AFTER UPDATE` in a trigger definition signify? —
The trigger fires after the UPDATE operation is completed.
41.
What is a common characteristic of stored procedures? —
They can accept input parameters and return output values.
42.
What is the primary purpose of Data Manipulation Language (DML) in SQL? —
To retrieve and modify data within tables
43.
What is the purpose of a `JOIN` clause in SQL? —
To combine rows from two or more tables based on a related column.
44.
What is the primary purpose of Data Definition Language (DDL) in SQL? —
To define and manage database schema objects
45.
What is the purpose of an index in a database? —
To improve the speed of data retrieval operations on a database table.
46.
Which SQL keyword is used to return only unique values? —
DISTINCT
47.
Which SQL statement is used to modify existing records in a table? —
UPDATE
48.
What is a primary defense against SQL Injection attacks? —
Implementing parameterized queries or prepared statements
49.
Which of the following is a common vulnerability that can lead to SQL Injection? —
Improper validation and sanitization of user input
50.
A trigger is a set of actions that are automatically executed by the database system. When are triggers typically executed? —
When a specific event occurs on a table (INSERT, UPDATE, DELETE)