SQL quiz questions

Say True or False. Give explanation if False.

A view doesn’t have data of its own.

1. True

2. False

Say True or False. Give explanation if False.

A DDL statement or a DCL statement is automatically committed.

1. True

2. False

Say True or False. Give explanation if False.

You can use a subquery in an INSERT statement.

1. True

2. False

Say True or False. Give explanation if False.

Attempting to delete a record with a value attached to an integrity constraint, returns an error.

1. True

2. False

Say True or False. Give explanation if False.

While inserting new rows in a table you must list values in the default order of the columns.

1. True

2. False

Say True or False. Give explanation if False.

Multiple column subqueries return more than one column from the inner SELECT statement.

1. True

2. False

Say True or False. Give explanation if False.

A multiple row subquery returns more than one row from the inner SELECT statement.

1. True

2. False

Say True or False. Give explanation if False.

All group functions ignore null values.

1. True

2. False

Say True or False. Give explanation if False.

The DISTINCT keyword allows a function consider only non-duplicate values.

1. True

2. False

Say True or False. Give explanation if False.

If a column value taking part in an arithmetic expression is NULL, then the result obtained would be NULLM.

1. True

2. False

What is the difference between inner and outer join? Explain with example.

Answer:

Inner Join

Inner join is the most common type of Join which is used to combine the rows from two tables and create a result set containing only such records that are present in both the tables based on the joining condition (predicate).

Inner join returns rows when there is at least one match in both tables

If none of the record matches between two tables, then INNER JOIN will return a NULL set. Below is an example of INNER JOIN and the resulting set.

SELECT dept.name DEPARTMENT, emp.name EMPLOYEE 
FROM DEPT dept, EMPLOYEE emp
WHERE emp.dept_id = dept.id

Outer Join

Outer Join, on the other hand, will return matching rows from both tables as well as any unmatched rows from one or both the tables (based on whether it is single outer or full outer join respectively).

Outer Join can be full outer or single outer

Notice in our record set that there is no employee in the department 5 (Logistics). Because of this if we perform inner join, then Department 5 does not appear in the above result. However in the below query we perform an outer join (dept left outer join emp), and we can see this department.

SELECT dept.name DEPARTMENT, emp.name EMPLOYEE 
FROM DEPT dept, EMPLOYEE emp
WHERE dept.id = emp.dept_id (+)

The (+) sign on the emp side of the predicate indicates that emp is the outer table here. The above SQL can be alternatively written as below (will yield the same result as above):

SELECT dept.name DEPARTMENT, emp.name EMPLOYEE 
FROM DEPT dept LEFT OUTER JOIN EMPLOYEE emp
ON dept.id = emp.dept_id  

Given these contents of the Customers table:

Id	Name			ReferredBy
1	John Doe		NULL
2	Jane Smith		NULL
3	Anne Jenkins		2
4	Eric Branford		NULL
5	Pat Richards		1
6	Alice Barnes		2

Here is a query written to return the list of customers not referred by Jane Smith:

SELECT Name FROM Customers WHERE ReferredBy <> 2;

What will be the result of the query? Why? What would be a better way to write it?

Answer:

Although there are 4 customers not referred by Jane Smith (including Jane Smith herself), the query will only return one: Pat Richards. All the customers who were referred by nobody at all (and therefore have NULL in their ReferredBy column) don’t show up. But certainly those customers weren’t referred by Jane Smith, and certainly NULL is not equal to 2, so why didn’t they show up?

SQL Server uses three-valued logic, which can be troublesome for programmers accustomed to the more satisfying two-valued logic (TRUE or FALSE) most programming languages use. In most languages, if you were presented with two predicates: ReferredBy = 2 and ReferredBy <> 2, you would expect one of them to be true and one of them to be false, given the same value of ReferredBy. In SQL Server, however, if ReferredBy is NULL, neither of them are true and neither of them are false. Anything compared to NULL evaluates to the third value in three-valued logic: UNKNOWN.

The query should be written:

SELECT Name FROM Customers WHERE ReferredBy IS NULL OR ReferredBy <> 2

Watch out for the following, though!

SELECT Name FROM Customers WHERE ReferredBy = NULL OR ReferredBy <> 2

This will return the same faulty set as the original. Why? We already covered that: Anything compared to NULL evaluates to the third value in the three-valued logic: UNKNOWN. That “anything” includes NULL itself! That’s why SQL Server provides the IS NULL and IS NOT NULL operators to specifically check for NULL. Those particular operators will always evaluate to true or false.

Even if a candidate doesn’t have a great amount of experience with SQL Server, diving into the intricacies of three-valued logic in general can give a good indication of whether they have the ability learn it quickly or whether they will struggle with it.

What are the different types of replication available in SQL server?

1. Snapshot Replication

2. Transactional Replication

3. Merge Replication

4. All of the above

Which of the following keyword is used to raise an error in stored procedure?

1. THROW

2. THROWERROR

3. RAISEERROR

4. ERROR

Which of the followings are valid Transaction levels in SQL Server?

1. READ COMMITTED

2. READ UNCOMMITTED

3. REPEATABLE READ

4. All of the above

Which of the following is not a valid data type in SQL server

1. xml

2. nvarchar

3. money

4. blob

What are the different types of temporary tables in SQL server?

1. Indexed and non-index temporary tables

2. Global and local temporary tables

3. Unique and shared temporary tables

4. Small and large temporary tables

What are the different types of trigger

1. DDL and DCL

2. DDL and DUL

3. DML and DTL

4. DML and DDL

What is a trigger?

1. A trigger is a SQL procedure that initiates an action when an event (INSERT, DELETE or UPDATE) occurs.

2. A trigger is an event that can be raised from Stored Procedures.

3. A trigger is a SQL procedure that performs some tasks on accessing db tables.

4. A trigger is a procedure which executes when an error occurred.

What are the authentication modes in SQL Server?

1. Windows mode

2. Mixed mode

3. All of the above

4. None of the above