SQL Multiple Choice Questions and answers

REMOVE

DELETE

DELETE Query is used to delete existing rows from database table

DROP

CLEAR

truncate table TABLENAME

TRUNCATE statement is a Data Definition Language (DDL) which deletes all rows from table and clear it.

Delete table TABLENAME

Drop TABLENAME

Remove TABLENAME

Primary keys can contain NULL values and table can have multiple primary key.

Primary keys cannot contain NULL values and a table can have only one primary key.

Primary key cannot be NULL and table can have multiple Primary keys

Primary key can be NULL and table can have only one primary key.

COUNT(*)

COUNT(*) takes null value row in to consideration.

COUNT()

MAX()

NUMBER()

UPDATE

Data definition language (DDL) are used to create and modify the structure of a database and database objects, like Create, alter, and drop schema objects.

TRUNCATE

ALTER

None of the above

ASC

ASC is default order for rows returned by SQL Query

DESC

There is no default order

None of the above

A view can be created as read only and can be created as a join on two or more tables.

A view cannot be created as read only and cannot be created as a join on two or more tables.

A view cannot have an ORDER BY clause in the SELECT statement.

A view cannot be created with a GROUP BY clause in the SELECT statement.

SELECT employee_id, department_id, department_name, salary FROM employees
WHERE department_id IN (SELECT department_id
FROM departments);

SELECT employee_id, department_id, department_name, salary
FROM employees
NATURAL JOIN departments;

SELECT employee_id, d.department_id, department_name, salary
FROM employees e
JOIN departments d
ON e.department_id = d.department_id;

SELECT employee_id, department_id, department_name, salary
FROM employees
JOIN departments
USING (e.department_id, d.department_id);

When you want all matched and unmatched data from only one table.

When you want all unmatched data from one table.

When you want all matched data from both tables.

When you want all unmatched data from both tables.

Intersection, projection, join

Selection, projection, join

Difference, projection, product

Selection, intersection, join

DECIMAL

FLOAT

NUMERIC

CHARACTER

SQL LIKES

SQL Temp

SQL aliases

SQL Wildcards

Column Alias

Strings

Dates

All of the above

SQL Server 2008

SQL Server 2012

SQL Server 2014

SQL Server 2014 provides a new in-memory capability for tables that can fit entirely in memory (also known as Hekaton).

SQL Server 2016

LIKE 

BETWEEN 

EXISTS

None of the above

Only one database relation

Two database relation

Multiple database relation

None of the above

View definition

View schema

From clause

Where clause

SQL Server 2012

LocalDB was introduced in SQL Server 2012 CTP3. It’s basically a new version of SQL Express, with same functionality, dedicated to developers.

SQL Server 2008

SQL Server 2014

SQL Server 2016

AVG

SUM

Min/Max

Both, A and B.

CountIf

MAX

Count

SUM

True

False

Unknown

None of the above

SELECT * FROM [Sample].[dbo].[Students] 
WHERE EmailId NOT LIKE '%_@__%.__%'

SELECT * FROM [Sample].[dbo].[Students] 
WHERE EmailId LIKE '%_@__%.__%'

SELECT * FROM [Sample].[dbo].[Students] 
WHERE EmailId NOT LIKE '%_@__.__%'

None of the above

The PATINDEX() function returns the position of a pattern in a string and Search is case-insensitive.

PATIndex() take 2 arguments

The PATINDEX() function returns the position of a pattern in a string and Search is case-sensitive.

PATIndex() take 2 arguments

The PATINDEX() function returns the position of a pattern in a string and Search is case-sensitive.

PATIndex() take only 1 argument

The PATINDEX() function returns the position of a pattern in a string and Search is case-insensitive.

PATIndex() take only 1 argument

Combination of relational and XML data type columns

Hybrid model may yield better performance in that you have more control over the indexes created on the relational columns and locking characteristics.

Combination of relational and non relational data type columns

Using XML with triggers

Using XML with views