SQL Query Interview Questions and Answers in MCQ format

SQL Query MCQs

1. SELECT ____ FROM Employees; will return all the columns from the Employees table.

a) @
b) #
c) *
d) $

Answer: c) *

Explanation: ‘ * ‘ is used along with SELECT statement in SQL, if we wish to select all the columns available in a table.

Example - SELECT * FROM Employees;

If you want to fetch the values of specific columns or fields of a table, then you have to specify column names such as

SELECT column1_name, column2_name, columnN_name FROM Employees;


2. SELECT COUNT( DISTINCT Country) from Employees; will return

a) All the unique values of the column country
b) All the values of the column country
c) Will return the total number of all the values present in the column country
d) Will return the total number of the unique values only, from the column country.

Answer: d) Will return the total number of the unique values only, from the column country.

Explanation: (DISTINCT Country) will return only the values that are unique in the column Country and the COUNT function will return the count or the total number of the unique values from the column Country.

SELECT COUNT( DISTINCT Country) from Employees;
Output - 4


3. Using UPDATE without the WHERE clause will

a) Result in a syntax error
b) Update all the records in a table.
c) Update the first record only
d) Not update any records.

Answer: b) Update all the records in a table.

Explanation:The WHERE clause in the UPDATE statement specifies which record should be updated and if we omit the WHERE clause, all records in the table will be updated.

For example

Update Employees set Country = ‘India’; will update country of all employees as ‘India’


4. SELECT * FROM Employees WHERE Country LIKE '%a';

a) Return all the employees with the EmpName.
b) Return all the employees with an EmpName that starts with the letter a.
c) Return all the employees with an EmpName that ends with a letter a.
d) Return all the employee with an EmpName that contain the letter a.

Answer: c) Return all the employees with an EmpName that ends with a letter a.

Explanation:‘ % ‘ is a wildcard character in SQL which represents zero or more characters. ‘ %a ‘ will find any values that ends with the letter a and does not care how long is the value as long as it ends with the letter a.

Output


5. Which of the following is an example of an aggregate function in SQL?

a) COUNT()
b) AVG()
c) SUM()
d) All of the above

Answer: d) All of the above

Explanation: An aggregate function in SQL performs a calculation on multiple rows of a single column of a table and returns a single value. SQL provides many aggregate functions that include avg, count, sum, min, max, etc.


6. DROP DATABASE myDB; will

a) Delete all the records from the tables.
b) Delete all the tables from the database.
c) Delete the whole database and its structure.
d) All of the above.

Answer: d) All of the above.

Explanation: DROP DATABASE will delete the database and everything inside it, including the tables, attributes, index etc.


7. Select a query to create a new table from another table

a) CREATE TABLE new_table AS SELECT col1,col2 FROM existing_table WHERE .... ;
b) CREATE TABLE FROM existing_table AS new_table SELECT col1,col2 WHERE .... ;
c) CREATE TABLE AS new_table FROM existing_table SELECT col1,col2 WHERE .... ;
d) All are wrong.

Answer: a) CREATE TABLE new_table AS SELECT col1,col2 FROM existing_table WHERE .... ;

For Example

CREATE TABLE NewEmployees AS (SELECT * FROM Employees);


8. The DELETE statement will

a) Delete the existing records from the specified table.
b) Delete the whole structure of the table with all its attributes, indexes.
c) Delete the whole database.
d) Delete the existing records from the specified table along with their linked records from another table.

Answer: a) Delete the existing records from the specified table.

Explanation: The Delete statement will only delete the existing records from the table. If the WHERE clause is omitted, it will delete all the records from the table. The table and its structure will remain as it is.


9. Primary keys must contain ______ values and cannot contain _______ values.

a) VARCHAR, NULL
b) UNIQUE, REPEATED
c) UNIQUE, NULL
d) VARCHAR, REPEATED

Answer: c) UNIQUE, NULL

Explanation: The Primary key is used to uniquely identify each record in a table and every key must be unique and not null.


10.  Which command is used to add column to an existing table?

a) Update
b) Alter
c) Create
d) Insert

Answer: b) Alter

Explanation: Alter is a command that is used to add, delete, or modify columns in an existing table. It is also used to add and drop various constraints on an existing table.

The following SQL adds a "Dept" column to the "Employees" table:

ALTER TABLE Employees
ADD Dept varchar(255);


11. Which of the following is not a valid constraints used in SQL queries?

a) UNIQUE
b) CHECK
c) CREATE INDEX
d) RETURN

Answer: d) RETURN

Explanation: UNIQUE ensures that all values in a column are different.
CHECK ensures that the values in a column satisfies a specific condition.
CREATE INDEX is used to create and retrieve data from the database very quickly.


12. The wildcard character ' _ ' in an SQL SERVER query represents?

a) zero or more characters.
b) a single character.
c) any characters.
d) none of the above.

Answer: b) a single character.

Explanation: ‘ _ ‘ is a wildcard character in SQL SERVER that is used to represent a single character. In Microsoft Access the ‘ ?‘ represents a single character.


13. Wildcard characters are used with the ______ operator?

a) LIKE
b) IN
c) BETWEEN
d) HAVING

Answer: a) LIKE

Explanation: Wildcard characters are used with the LIKE operator to search for a specific pattern in a column.


14. Which of the following is true for the IN operator?

a) Specify multiple values in a WHERE clause.
b) Selects value in a given range.
c) Give the table a temporary name.
d) Search for a specified pattern.

Answer: a) Specify multiple values in a WHERE clause.

Explanation: The IN operator is a shorthand for multiple OR conditions.

Example

SELECT * FROM Employees
WHERE Country IN ('India', 'Australia');


15. TRUNCATE TABLE is used to

a) Delete the table.
b) Delete the data inside a table but not a table itself.
c) Both (a) and (b).
d) None of the above.

Answer: b) Delete the data inside a table but not a table itself.

Explanation: TRUNCATE works the same as the DELETE statement without the WHERE clause, where it will delete all the records from the table but not the table itself.


16. This command is used to remove specific rows from a table 'CUSTOMER' is:

a) REMOVE FROM CUSTOMER
b) DELETE FROM CUSTOMER
c) DELETE FROM CUSTOMER WHERE
d) ALTER FROM CUSTOMER

Answer: c) DELETE FROM CUSTOMER WHERE

Explanation: The DELETE statement will delete the specified rows mentioned in the WHERE clause, whereas the DELETE statement without the WHERE clause will delete all the rows from the table.


17. Which of the following is true about the HAVING clause?

a) Acts like a WHERE clause.
b) Added to WHERE clause to work with aggregate functions.
c) Filter records by specified conditions.
d) None of the above.

Answer: b) Added to WHERE clause to work with aggregate functions.

Explanation: The HAVING clause is used because the WHERE keyword cannot be used with aggregate functions.


18. Select all records from the Employees table where the Phone_number is empty.

SELECT * FROM Employees WHERE _________

a) Phone_number IS NULL.
b) Phone_number IS EMPTY.
c) Phone_number IS NOT TRUE
d) None of the above.

Answer: a) Phone_number IS NULL.

Explanation: A field with a NULL value is one that has been left blank during record insertion.


19. A sub query in an SQL SELECT statement is enclosed in

a) {......}
b) (......)
c) [......]
d) <.....>

Answer: b) (......)

Explanation: A sub query must always be enclosed in a pair of parentheses.
A Subquery is a query that is embedded in WHERE clause of another SQL query

The following statement returns Names of all the employees whose salary is greater than 50,000

Select EmpName from Employees where EmpId in (Select EmpId from Salary where Salary > 50,000);


20. Which of the following command is used to sort records that is fetched from the table?

a) SORT BY
b) ALIGN BY
c) ORDER BY
d) GROUP BY

Answer: c) ORDER BY

Explanation: ORDER BY is used to sort records in a result set in an ascending or descending order.
The following SQL statement selects all Employees from the "Employees" table, sorted by the "Country" column:

SELECT * FROM Employees
ORDER BY Country;


21. If we don’t specify ASC or DSC after the ORDER BY command, which of the following is used by default?

a) ASC
b) DSC
c) Nothing will be done.
d) None of the above.

Answer: a) ASC

Explanation: ORDER BY command sorts the result set in an ascending order by default.
Example

SELECT * FROM Employees
ORDER BY Country;

The above SQL statement will sort the records in an ascending order.


22. In an Employees table where the employee with the highest salary is at the top, consider the following query

SELECT TOP 1 salary
FROM(
SELECT TOP 3 salary
FROM Employees
ORDER BY salary ASC) AS employee
ORDER BY salary DSC;

The above SQL query will return which one of the following

a) The highest salary.
b) The second highest salary.
c) The third highest salary.
d) The top three highest salary.

Answer: c) The third highest salary.

Explanation: First, the sub query will return the top three records in an ascending order and the outer query will return the first record which is actually the third highest salary.


23. Write a query to retrieve the employee’s first name and last name and concatenate them in a single column?

a) SELECT CONCAT (emp_fname, ‘ ’, emp_lname) AS Fullname FROM Employees;
b) SELECT (emp_fname, ‘ ’, emp_lname) AS Fullname FROM Employees;
c) SELECT emp_fname, emp_lname CONCAT AS Fullname FROM Employees;
d) SELECT FROM Employees CONCAT (emp_fname, emp_name) AS Fullname;

Answer: a) SELECT CONCAT (emp_fname, ‘ ’, emp_lname) AS Fullname FROM Employees;


24. Which of the following is a correct SQL query?

a) SELECT emp_name, salary FROM Employees WHERE salary>10000 AND <20000;
b) SELECT emp_name, salary FROM Employees WHERE salary>10000 AND WHERE salary<20000;
c) SELECT emp_name, salary FROM Employees WHERE salary>10000 AND salary<20000;
d) None of these.

Answer: c) SELECT emp_name, salary FROM Employees WHERE salary>10000 AND salary<20000;


25. To change the structure of a table, which of the following command is used?

a) MODIFY
b) INSERT
c) ALTER
d) DELETE

Answer: c) ALTER