SQL (Structured Query Language) : MCQs & PYQs with Explanation

Option B

In SQL, the GROUP BY clause is used to arrange rows into groups based on the same values in one or more columns. It is commonly used with aggregate functions such as COUNT(), SUM(), AVG(), MAX(), and MIN().
The GROUP BY clause helps summarize data.

Table : Student

SELECT Department, SUM(Students)
FROM Student
GROUP BY Department;

Option C

A Query Language allows users to specify what data they want rather than how the data should be retrieved. 
For example, in SQL, a user can write a query to fetch required records without worrying about the internal procedures used by the DBMS to access the data. This makes query languages Non-Procedural and easy to use.

Option A

SQL Injection is a malicious technique in which attackers provide crafted input to SQL queries, tricking the database into executing unintended commands. This can lead to unauthorized data access, modification, or deletion. It is a major security concern for databases connected to web applications.

Option B

In SQL, the REVOKE command is used to remove privileges that were previously granted to a user.

• The GRANT is used to give privileges.
• The DELETE is used to remove rows from a table.

Option A

In SQL, the DELETE statement removes rows that satisfy the WHERE condition. If the WHERE clause is omitted, there is no condition to restrict the deletion, so all rows in the table are deleted. The table structure remains intact.
The SQL command DELETE FROM Student; deletes every record from the Student table.

Option C

A Trigger in SQL is a set of actions automatically executed by the database in response to a specific event, such as an INSERT, UPDATE, or DELETE on a table. Triggers are often used for tasks like maintaining audit logs, enforcing business rules, or validating data automatically.

The action part of a trigger (the code that gets executed) can be performed in two ways:
Row-Level Trigger: The trigger is executed once for every row (tuple) that is modified by the operation.
Statement-Level Trigger: The trigger is executed only once, regardless of how many tuples are affected by the operation.

Option D

The error in this SQL statement occurs because the FROM clause is missing, which is required to specify the table from which data should be retrieved. A correct SELECT query must always include the FROM keyword followed by the table name. Additionally, if a column name contains spaces (for example, last name), it should be enclosed in quotes or brackets to avoid syntax errors.

Option C

A View is a virtual table created using a SELECT query. It allows users to see only specific columns or rows of a table while hiding the remaining data. This is commonly used for data security and access control.
E.g.:
CREATE VIEW Employee_View AS
SELECT Empid, Name
FROM Employee;
Here, users can see only Empid and Name, while other sensitive columns like salary,   designation, or address remain hidden.

Option C

In SQL, the GROUP BY clause is used to divide records into groups based on a column (rating). 
The query then calculates an aggregate value (average age) for each group. The HAVING clause is used to filter these groups based on a condition.

After grouping the records based on rating and applying the condition that only those groups should be considered where the number of sailors is greater than one, we find that four ratings (7, 8, 10, 3) satisfy the condition. 
Since the query returns one result (average age) for each valid group, the total number of rows returned is 4.

Option D

In embedded SQL, we write SQL statements inside a host programming language like C, Java, or COBOL. When we want to fetch data from a table and store it directly into host variables, we use the SELECT INTO statement.
The SELECT is used to read data from a table.
The INTO is used to store the fetched values into host language variables.
EXEC SQL SELECT column_name INTO :host_name FROM table_name WHERE condition;

E.g.:
Host variables in C:
char student_name[50];
int student_marks;
Embedded SQL SELECT INTO:
EXEC SQL SELECT name, marks
     INTO :student_name, :student_marks
     FROM Student
     WHERE roll_no=101;

Option B

SQL (Structured Query Language) is a Non-Procedural or Declarative language, which means the user specifies what data is required, not how to retrieve it. The DBMS decides the best way to execute the query.

Option C

In SQL, rows or records from a table are removed using the DELETE command. It allows deleting specific rows using a condition (WHERE clause) or all rows if no condition is given.
TRUNCATE also removes all rows, but it is considered a DDL command and does not allow conditions, it deletes the entire table data at once, so it is not typically treated as a row-by-row removal operation.

Option C

Schema change statements in SQL are used to modify the structure of existing database objects like tables, columns, or constraints. These changes can include adding or removing tables, columns, or other schema elements. The most common schema change statements are DROP and ALTER.

• The DROP statement is used to delete schema elements, such as tables or columns, from the database. It can remove an entire schema or individual database objects.
• The ALTER statement is used to modify the structure of existing objects, like adding or dropping columns, modifying column data types, or renaming columns. ALTER allows database schemas to evolve over time without losing data.

Option B

The index is created on (country, city, street), which is a Composite Index. In a composite index, the database efficiently uses the index only when queries include the leftmost columns.
Queries using country or country and city can use the index. However, a query using only street skips the leading columns, so the index becomes least useful and the database may perform a full table scan.

Option C

A view in a database is a virtual table that represents the data retrieved by a stored SQL query from one or more base tables. 

Unlike base tables and indexes, which physically exist and store data on disk, a view does not store data itself. Instead, it holds only the SQL query defining it. 

When a user queries the view, the database engine executes the stored query dynamically on the underlying base tables to fetch the current data. This allows views to provide a customized and simplified representation of data, enforce security by controlling data access, and reduce complexity without duplicating storage. 

Views serve as logical abstractions that help organize and present data efficiently without physically occupying storage space.