SQL Multiple Choice Questions (MCQs) with Answers - A Comprehensive Guide

Introduction to SQL

In order to understand SQL, it is important to first familiarize oneself with the concepts of databases and database management systems (DBMS).

Data refers to collection of facts about a particular object. A database, on the other hand, is a methodical collection of small units of data. A relational DBMS comprises tools that enable users to manipulate, organize and visualize the contents of a database while adhering to standardized rules that facilitate rapid communication between the database and the user.

Once we have a grasp of data, databases, and DBMS/RDBMS, we are ready to explore SQL. SQL or Structured Query Language is the language used by users to interact with databases to extract the required data. It allows for storage, manipulation, and retrieval of data from a database.

Features of SQL

SQL enables users to interact with databases to extract and manipulate data. With SQL, we can create our own databases and add data in the form of tables.

The following functionalities can be performed using SQL:

• Create or delete a database.
• Create, alter, or delete tables in a database.
• Retrieve data from tables.
• Insert data into tables.
• Update data in tables.
• Delete data from tables.
• Create views in a database.
• Execute various aggregate functions.

SQL MCQs

  <p>1. SQL stands for:</p>
  <ol type="a">
    <li>Standard Query Language</li>
    <li>Structured Question Language</li>
    <li>Structured Query Language</li>
    <li>Standard Quick Language</li>
  </ol>

  <p>2. Which of the following is not a data type in SQL?</p>
  <ol type="a">
    <li>Integer</li>
    <li>Date</li>
    <li>Float</li>
    <li>Decimal</li>
    <li>Array</li>
  </ol>

  <p>3. What statement is used to add or modify data in a table in SQL?</p>
  <ol type="a">
    <li>SELECT</li>
    <li>ADD</li>
    <li>INSERT</li>
    <li>UPDATE</li>
  </ol>

  <p>4. Which of the following functions is used to return the minimum value in a column?</p>
  <ol type="a">
    <li>MAX()</li>
    <li>MIN()</li>
    <li>AVG()</li>
    <li>COUNT()</li>
  </ol>

  <p>5. What command is used to delete a table?</p>
  <ol type="a">
    <li>DELETE</li>
    <li>ALTER</li>
    <li>DROP</li>
    <li>TRUNCATE</li>
  </ol>

Common Relational Database Management Systems (RDBMS)

In the world of database management, there are several commonly used RDBMS systems. Some of the well-known RDBMS systems include:

Oracle

MySQL

Microsoft SQL Server

The aforementioned options are all examples of popular RDBMS systems used in different applications.

Creating a Table in SQL

In SQL, the command used to create a new table is

CREATE TABLE

.This statement in SQL drops/deletes a table called student.

Types of Unicode Character String Types in SQL

In SQL, the types of Unicode character string types are nchar and ntext. These datatypes are used to store Unicode character data. Therefore, the correct answer is option C, which is "Both A and B". Option D, "None of the above" is incorrect as nchar and ntext are indeed valid datatypes in SQL.

Understanding BLOB in SQL

In SQL, BLOB stands for Binary Large Objects. It is an object datatype that allows users to store large amounts of binary data, such as images and documents, in a database. BLOBs are used to efficiently and quickly store and retrieve large and complex data. They are particularly useful in applications that require the storage of multimedia data like audio and video files, photos, and other such documents.

Therefore, the correct answer to the given question is "Binary Large Objects".

Most Appropriate Data Type for Storing a String of up to 255 Characters

In MySQL, the most appropriate datatype for storing a string of up to 255 characters is

TINYTEXT

. This is because it can hold a string of up to 255 characters or 255 bytes.

TEXT

can hold up to 65,535 characters,

BLOB

can hold large binary data, and

BINARY

is for storing binary strings. Therefore,

TINYTEXT

is the most suitable choice for the given requirement.

Behavior of empty ENUM list in MySQL

When an ENUM list is created without any values and no value is inserted into it, a blank value is automatically inserted into the list. This is the default behavior of MySQL.

CREATE TABLE example_table (
  id INT NOT NULL AUTO_INCREMENT,
  example ENUM('option1', 'option2', 'option3'),
  PRIMARY KEY (id)
);

In the above example, if no value is inserted into the 'example' column, it will automatically be given a blank value.

Range of Integers for MEDIUMINT Datatype in SQL

The MEDIUMINT data type in SQL can hold integers in the range of -8388608 to 8388607 for signed numbers and 0 to 16777215 for unsigned numbers.

This code snippet adds a new column to the existing table called STUDENT. The new column is named ADDRESS and it has a data type of VARCHAR2 with a maximum length of 20 characters. The ALTER TABLE command is used to modify the structure of a table after it has been created.

Deleting All Rows from a Table

The TRUNCATE command is used to delete all rows from a table and free up space for new data. This is a faster operation than using DELETE, as it does not log individual row deletions and has less overhead.

The syntax for TRUNCATE is as follows:

TRUNCATE TABLE table_name;

Note that TRUNCATE cannot be undone, and also resets any AUTO_INCREMENT values to 1 if the table has that property.

Commands in the Data Control Language

The two commands that belong to the Data Control Language are REVOKE and GRANT.

GRANT privileges TO user;
REVOKE privileges FROM user;

The GRANT command is used to give a user certain privileges, while the REVOKE command is used to remove those same privileges from the user. These commands are critical for managing user access to a database.

This code snippet deletes the rows from the STUDENTS table where the AGE column value is equal to 16 and then performs a rollback operation which undoes the changes made during the delete operation. Therefore, the correct answer is: Deletes the rows from the table where AGE = 16 and performs an undo operation on the delete operation.

Usage of Wildcard in WHERE Clause

Wildcards in WHERE clause is used when an exact match is not possible in a SELECT statement. It helps in filtering the data based on patterns, rather than exact matches. Using wildcards in WHERE clause provides flexibility in searching for data and making queries more efficient. Therefore, option C, "An exact match is not possible in a SELECT statement" is the correct answer.

What is DDL?

DDL stands for "Data Definition Language." It is a series of commands used to define and organize data in a database. The full form of DDL is Data Definition Language.

Understanding the DEFAULT Constraint in SQL

In SQL, the DEFAULT constraint is used to set a default value for a column in a table. This value is used when a value for that field is not provided.

For example, we can set a DEFAULT constraint for the "age" column in a "users" table. If a new record is inserted without providing a value for "age", the value set in DEFAULT constraint will be assigned to it.

Here's an example of how to set the DEFAULT constraint for a column:

Code:

CREATE TABLE users (
  id INT PRIMARY KEY,
  name VARCHAR(50),
  age INT DEFAULT 18
);

In this example, if no value is provided for "age" when a new record is inserted, the default value of 18 will be used instead.

Other types of constraints in SQL include UNIQUE, NOT NULL, and CHECK.

Understanding Rows in a Relation

In the context of relations in a database, the rows are also known as tuples.

Therefore, the correct option is:

Tuple

Automatic Rollback Before Commit During Shutdown

During a transaction, if a shutdown occurs before committing, the automatic action that takes place is a rollback. The rollback is automatically done to ensure that any changes made during the transaction are not left incomplete or in an inconsistent state. This is important to maintain the integrity of the data and avoid any potential errors or data loss. Therefore, it is recommended to properly handle any potential shutdown scenarios during database transactions to ensure that data is properly handled and no data loss occurs.

Identifying Tuples in Database

Out of the given options, the correct answer is "Super key."

A super key is a set of one or more attributes that can uniquely identify a tuple in a relation. It is often used in database design to ensure data integrity and eliminate duplicates.

While a schema defines the structure of a database, an attribute is a characteristic or property of an entity, and a domain defines the set of possible values for an attribute.

Identifying the Most Basic SQL Operations

In SQL, there are four basic operations that are commonly referred to as the "CRUD" operations:

• Create: this operation is used for creating new tables, views, or other database objects. It can also be used for adding new records to an existing table.
• Read: this operation is used for retrieving data from one or more tables in the database. It is used to get information on existing records.
• Update: this operation is used for modifying existing records in a table. It allows you to update multiple records at once.
• Delete: this operation is used for deleting one or more records from a table.

Therefore, the answer to the question is 4.

// Example of a CRUD operation in SQL - Creating a new table

CREATE TABLE employees (
   id INT PRIMARY KEY,
   first_name VARCHAR(50),
   last_name VARCHAR(50),
   email VARCHAR(100),
   hire_date DATE
);

Identifying SQL Commands

The SQL commands are SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, ALTER, and many more.

Out of the given options, DELETE is a SQL command and not "not a SQL command". Therefore, the given statement is incorrect.

Explanation:

This SQL code selects the top 5 rows from the "students" table and returns all columns. So, the correct answer is "Select the top 5 entries for all the columns in the students' table."

Rephrased:

Which option below best fits the definition of an artificial key that is used to uniquely identify each record:

A) Primary Key B) Foreign Key C) Surrogate Key D) Composite Key

Answer: C) Surrogate Key

//Code example

//Table creation with a surrogate key
CREATE TABLE example_table (
   surrogate_key INT NOT NULL GENERATED ALWAYS AS IDENTITY,
   data_column1 VARCHAR(50),
   data_column2 INT,
   PRIMARY KEY(surrogate_key)
);

Explanation: The above code creates a table with a surrogate key. The surrogate_key column is set to be an integer that is automatically generated and set to not allow null values. This key will uniquely identify each record in the table, making it the surrogate key. The data_column1 and data_column2 are just placeholders for actual data. The PRIMARY KEY statement at the end specifies that the surrogate_key column is the primary key for the table.

Restricting the number of rows returned from a SQL query

To restrict the number of rows returned from a SQL query, we use the LIMIT command. This command can be added to the end of a SELECT statement to limit the number of rows returned.

For example, the following SQL statement will return the first 10 rows from the "customers" table:

SELECT * FROM customers LIMIT 10;

Other common SQL commands that can be used in a query include WHERE, LIKE, and GROUP BY. However, only the LIMIT command will restrict the number of rows returned from a query.

Valid Logical Operators in SQL

In SQL,

SOME

,

ALL

and

AND

are valid logical operators used for conditional statements.

Therefore, the correct answer is:

All of the above

This means that all of the options mentioned - SOME, ALL and AND are valid logical operators in SQL.

SQL Function for Comparing String Similarities

In SQL, the

SOUNDEX

function is used to compare the similarity of two strings and return a four-character code representing their similarity. Thus, the correct answer to the question is: SOUNDEX. The

DIFFERENCE

function returns an integer value representing the difference between the SOUNDEX values of two strings. The

CONCAT

function is used to concatenate two or more strings.

Command for Selecting Specific Columns

The command used for selecting specific columns in SQL is called "PROJECTION".

Primary Key Constraints

In a database, a primary key constraint is used to uniquely identify each record in a table. The primary key must always have a value, which means it cannot be null. Therefore, the correct choice in this case is "NOT NULL".

Valid SQL types

There are several valid SQL types:

NUMERIC

CHARACTER

FLOAT

All of the above options are valid SQL types.

Using DAY Function to Get Specified Day of Month for a Given Date

To get the specified day of the month for a given date, we use the DAY function. It is important to note that the date argument must be a valid date format recognized by SQL Server.

SELECT DAY('2022-06-29') AS SpecifiedDayOfMonth;

In the above example, we use the DAY function to extract the specified day of the month (29) from the given date '2022-06-29'.

SQL Comparison Operators

In SQL, the BETWEEN operator is used to match a value to a range of values, while the IN operator is used to compare a value to a list of specified values. The ANY operator is used to compare a value to a set of values returned by a subquery, while the ALL operator is used to compare a value to all values returned by a subquery. Therefore, the correct operator used to compare a value to a list of literal values that have been specified is the IN operator.

SELECT column_name(s)
FROM table_name
WHERE column_name IN (value1, value2, ...);

Understanding Self Joins in SQL

In SQL, a self-join is a regular join, but the table is joined with itself. It allows you to join a table with itself as if the table were two hypothetical tables, one with the original rows and the other with a copy of the rows.

Self-join is a powerful technique for combining and comparing the data in the same table. It can be used to create hierarchical queries, find missing values, and do time-based analysis, among other things.

The self-join command involves referencing the same table twice in a SQL statement, but with different aliases. The aliases help distinguish between the copies of the table, and they allow the query to specify which copy of the table is involved in the join.

To perform a self-join, we use the JOIN clause and the ON clause to specify the conditions for the join. The ON clause specifies the relationship between the two copies of the table.

In summary, self-join is a valuable tool for SQL developers to analyze and compare data within the same table.

Answer: B) Self-join

SELECT A.column, B.column
FROM table AS A JOIN table AS B
ON (A.column = B.column)

Explanation of NATURAL JOIN

The NATURAL JOIN operation is a type of JOIN operation in relational databases. It returns all the records from two different tables where the common fields between the tables have the same values.

NATURAL JOIN combines two tables based on the columns with the same name and data types, eliminating duplicate columns.

It is also known as an EQUIJOIN, which involves a combination of Projection and cartesian product operations. Therefore, option C, "Combination of Projection and cartesian product," is the correct answer.

Deleting a Trigger in SQL

In SQL, the

DROP

command is used to delete a trigger. The syntax for dropping a trigger is as follows:

DROP TRIGGER trigger_name;

Replace

trigger_name

with the name of the trigger that you want to delete.

To delete a trigger, make sure you have the necessary privileges to do so, and then use the above command. The trigger will be deleted from the database.

In summary,

DROP

command is used to delete a trigger in SQL.

How to View Triggers in a Database?

To view all the triggers currently available in a database, you can use the following SQL command:

SHOW TRIGGERS;

This will display a list of all the triggers including its name, table, associated event (INSERT, UPDATE, DELETE), and timing (BEFORE or AFTER).

It is a useful command for database administrators to check the triggers in the database and ensure that they are functioning correctly.

Explanation:

The minimal super key that uniquely identifies each record in a table is referred to as the candidate key. Therefore, option C, "Candidate key," is the correct answer.

Choosing the Appropriate Foreign Key Constraint

When setting up a relationship between two tables in a relational database, it is important to choose the correct foreign key constraint to ensure data integrity.

The most important foreign key constraint is referential integrity, which ensures that the foreign key values in the child table always correspond to a primary key value in the parent table.

Entity integrity refers to the uniqueness of a primary key within a table, while domain integrity ensures that data is entered in the appropriate format and within its defined range. These constraints are important for overall data accuracy, but referential integrity is the most critical constraint for maintaining the relationships between tables.

Therefore, the correct answer to the given question is: Referential integrity.

Understanding the NOLOCK Keyword in SQL

When working with SQL, the NOLOCK keyword allows for dirty reads, meaning that a transaction can read data that has not yet been committed. This keyword is equivalent to READ UNCOMMITTED.

Here are the equivalents of the NOLOCK keyword:

• NOLOCK = READ UNCOMMITTED
• READPAST = READ COMMITTED with the SKIP LOCKED option
• UPDLOCK = SERIALIZABLE with the HOLDLOCK option
• XLOCK = EXCLUSIVE with the HOLDLOCK option

It's important to note that using the NOLOCK or READ UNCOMMITTED keyword can lead to inconsistent data, as transactions may be reading data that has not yet been fully committed. It's best to use this keyword only when necessary and with caution.

SQL Query Replacement

The following SQL query can replace the given query:

SELECT Name, ID
FROM Student natural join Courses;

This query uses the natural join clause to join the two tables on their common attribute and retrieves only the required columns, Name and ID. Option B is the correct answer. The other options either do not retrieve the required result or miss one or more necessary clauses.

Solution

The output of the given code snippet will be "0".

Explanation:

The given query uses several SQL functions such as MOD(), TRUNCATE() and ROUND() to perform mathematical operations on the values.

MOD(2500, 20) returns the modulus of 2500 divided by 20, which is 0.

TRUNCATE(MOD(2500, 20), -1) takes the value returned by MOD(2500,20) and truncates it to one decimal place (the -1 argument). Since the original value is already an integer, it remains the same.

Finally, ROUND(TRUNCATE(MOD(2500,20),-1),2) rounds the truncated value to two decimal places.

Therefore, the final output of the query is 0.

Changing Name in Users Table

A subquery is a query that is nested inside a SELECT, INSERT, UPDATE, or DELETE statement, or inside another subquery. It is also called an inner query or inner select. A subquery can be used anywhere an expression is allowed. It returns data to be used in the main query as a condition to further restrict the data to be retrieved.

In SQL, a subquery can be placed in a WHERE or HAVING clause of another query. The purpose of the subquery is to retrieve data that will be used in the main query to filter the results. It is commonly used with the IN, NOT IN, EXISTS, or NOT EXISTS operator.

For example:

SELECT *
FROM orders
WHERE customer_id IN (SELECT customer_id FROM customers WHERE country = 'USA');

In the above example, the subquery is `(SELECT customer_id FROM customers WHERE country = 'USA')`. The subquery returns a list of customer_ids that are from the USA. This list is used as a condition in the WHERE clause of the main query to filter the results. The main query returns all orders made by customers from the USA.

Subqueries can also be used in other clauses, such as the SELECT clause, to perform calculations or retrieve specific data. However, it is important to optimize subqueries as they can affect the performance of the query.

Explanation:

The AS clause is used for renaming columns in SQL. It provides an alias, or temporary name, for a table or column. This can make queries easier to read and understand, especially when working with multiple tables and complex joins. So, the correct answer is "Rename".

Valid SQL Constraints

In SQL, constraints are used to specify rules for the data in a table.

NOT NULL: This constraint ensures that a column cannot have a NULL value.

CHECK: This constraint ensures that the values in a column meet a specific condition.

UNIQUE: This constraint ensures that all values in a column are unique.

Therefore, option D: All of the above is the correct answer.

--Examples of constraint usage in SQL:

CREATE TABLE Persons (

PersonID int NOT NULL,

LastName varchar(255) NOT NULL,

FirstName varchar(255) NOT NULL,

Age int,

CONSTRAINT PK_PersonID PRIMARY KEY (PersonID),

CONSTRAINT CHK_Age CHECK (Age>=18),

CONSTRAINT UC_LastNameFirstName UNIQUE (LastName, FirstName)

);

Composite Key - Also known as Concatenated Key

A composite key is a key made up of two or more fields in a database table that uniquely identifies a record. It is also known as a concatenated key since it is composed of one or more columns. A composite key is useful when no single attribute uniquely identifies the record.

Understanding SQL Procedures

In SQL, a program that performs common actions on database data and is also stored within the database is called a stored procedure. These procedures are saved in SQL code and are reusable for future operations. Stored procedures increase efficiency and improve database security by allowing users to execute pre-written commands rather than submitting new code each time.

In addition to stored procedures, there are also triggers and stored functions in SQL, but they serve different purposes.

Explanation of SQL Injections

SQL injections can occur when data is used to dynamically construct an SQL Query, and unintended data from an untrusted source enters the application. Therefore, the answer is option C: Both A and B.

// Example of SQL Injection vulnerability
// Unsafe PHP code that uses unsanitized user input to construct an SQL query
$username = $_POST['username'];
$password = $_POST['password'];
$sqlQuery = "SELECT * FROM users WHERE username='" . $username . "' AND password='" . $password . "'";

// An attacker can submit malicious SQL statements as the 'username' or 'password' parameter
// For example: ' OR '1'='1' --
// The resulting SQL query constructed by the script will become:
// SELECT * FROM users WHERE username='' OR '1'='1' --' AND password='' or '1'='1' --
// This will bypass the authentication mechanism and return all users in the 'users' table

Column-level Constraints

Out of the given constraints, the NOT NULL constraint can only be applied at the column level. It indicates that a column must contain a value and cannot have a NULL value.

The other constraints - UNIQUE, CHECK, and PRIMARY KEY - can be applied both at the column level as well as at the table level.

What is a Transaction?

A transaction is a group of operations that are executed as a single logical unit of work. It is a series of database operations that must be executed atomically, i.e., all or nothing. If any of the operations in a transaction fail, the entire transaction is rolled back and no changes are made to the database.

For example, if someone wants to transfer money from one account to another, this transaction involves two operations - debiting the amount from one account and crediting it to the other. If either one of these operations fails, i.e., the debit or credit operation fails, the entire transfer should be rolled back to keep the data consistent.

In short, a transaction helps to maintain the integrity and consistency of data in a database.

// Example of a Transaction in SQL Server
BEGIN TRANSACTION

UPDATE dbo.Customers
SET FirstName = 'John'
WHERE CustomerID = 1

UPDATE dbo.Orders
SET OrderDate = '2022-01-01'
WHERE CustomerID = 1
 
COMMIT TRANSACTION // all changes are made atomically

ROLLBACK TRANSACTION // rollback the entire transaction if any of the operations fail

Identifying Concurrency-Based Protocols

// All of the following protocols are used in concurrency:

// Lock-based protocol
// This protocol is used to serialize concurrent access to shared resources by using locks.

// Two-phase locking protocol
// This protocol is used to ensure serializability in database systems by acquiring locks on data items in two phases.

// Timestamp ordering protocol
// This protocol is used to make sure that transactions in a database are executed atomically and in a serializable order based on their timestamps.

// Therefore, the correct answer is All.

Invalid SQL Data Types

SQL is a language used for managing data held in relational databases. Here are some of the invalid SQL data types:

DECIMAL

- This is not a valid SQL data type. Other valid numeric data types are

INTEGER

,

FLOAT

, and

NUMERIC

.

CHARACTER

- The correct name is

CHAR

.

None of the above

is incorrect as DECIMAL is an invalid SQL data type.

Full Form of SQL

SQL stands for Structured Query Language, which is a programming language used for managing and manipulating relational databases.

// No code to provide for this explanation

Identifying Non-DDL Command

In SQL, there are various commands or queries that can be used depending on the task at hand. Some of these commands fall under the category of Data Definition Language (DDL). The DDL commands are primarily used for defining database objects such as tables, indexes, and views. On the other hand, the Data Manipulation Language (DML) commands are used to manipulate data within the database.

Out of the given options of commands, only one does not belong to the category of DDL commands. It is the UPDATE command. This command is a Data Manipulation Language (DML) command used to modify or change data within an existing table in the database.

DDL commands: CREATE, ALTER, DROP, TRUNCATE, RENAME, GRANT, REVOKE <br>
DML commands: INSERT, UPDATE, DELETE, SELECT

Identifying TCL Commands

To identify if a command is a TCL command, we can check if it is used to manage transactions in a database. The two commands mentioned, ROLLBACK and SAVEPOINT, are examples of TCL commands as they are used to manage transactions in databases.

rollback;

savepoint my_savepoint_name;

What are SQL views?

SQL views are a virtual table created by a SQL query, i.e., a named, stored select query.

They are also known as virtual tables as they do not store any data of their own, but instead, they are a tailored representation of data contained in one or more underlying database tables or other views.

Invalid Aggregate Function in SQL

There is no invalid aggregate function in SQL among the options given. All of the functions listed; Count(), Sum(), and Avg(), are valid aggregate functions in SQL.

SELECT COUNT(column_name) FROM table_name;

The above code represents the syntax of using the COUNT() function to count the number of rows that contain non-null values for a specified column in a table.

SELECT SUM(column_name) FROM table_name;

Similarly, the SUM() function computes the total sum of values in a specified column from a table.

SELECT AVG(column_name) FROM table_name;

Lastly, the AVG() function computes the average value of all the numeric values in a specified column from a table.