SQL Test Online

Structured Query Language (SQL) is the programming language used to find out and modify the data stored in a relational database. Knowledge of designing SQL queries is important in software testing. Take the test below and find out your level of knowledge and the areas in which you should improve. Each question has exactly one correct answer. There is no need to consult any reference for answering these questions.

1. SELECT statement can fetch data from _______ table(s) of the database.
a. exactly one
b. at least two
c. one or two
d. any

2. What values can be included in the SELECT expression (the list following the SELECT keyword)?
a. Any columns
b. All columns
c. Computed values e.g. Price * 10
d. All of the above

3. Which function gives the total number of rows in a table?
a. SUM
b. COUNT
c. ROWCOUNT
d. This has to be done indirectly by executing a SQL query (e.g. SELECT * FROM Authors) and noticing the number of rows.

4. Which of the following SQL queries is correct?
a. SELECT * FROM Books WHERE Price BETWEEN 10 AND 25
b. SELECT * FROM Books WHERE Price BETWEEN 10, 25
c. SELECT * FROM Books WHERE Price BETWEEN (10, 25)
d. SELECT * FROM Books WHERE Price >10 AND Price < 25

5. Which JOIN clause returns only the matching values from two tables?
a. CROSS
b. INNER
c. LEFT OUTER
d. RIGHT OUTER

6. Which statement is correct for the GROUP BY clause?
a. GROUP BY allows grouping by only one column
b. GROUP BY needs to precede the WHERE clause
c. An aggregate function needs to be specified based on the column specified in GROUP BY
d. HAVING clause can be used in place of GROUP BY clause

7. What is true about Normalization?
a. It avoids data duplicities within and across tables.
b. It is easier to extend the database structure of a normalized database.
c. A normalized database structure is better than a de-normalized one when the SQL queries against it cannot be predicted in advance.
d. All of the above.

8. Which of these SQL queries is correct?
a. SELECT * FROM Employees ORDER BY LastName + FirstName
b. SELECT * FROM Employees ORDER BY LastName ORDER BY FirstName
c. SELECT FirstName, LastName FROM Employees ORDER BY LastName, FirstName DESCENDING
d. SELECT FirstName FROM Employees ORDER BY LastName, FirstName

9. Which of these statements is incorrect for the UNION operator?
a. Both SELECT statements have the same number of columns.
b. The UNION operator returns values that are duplicated in the two resultsets.
c. The column names returned by the UNION operator are taken from the first SELECT statement.
d. Either of the two SELECT statements can have WHERE, GROUP BY, HAVING and ORDER BY clauses.

10. Which of these is valid for a correlated sub query?
a. It is specified in the WHERE clause of the outer query.
b. It is specified in the FROM clause of the outer query.
c. It uses value in the outer query in its WHERE clause.
d. It is mentioned in the outer query's SELECT clause.

Click the Read More link for the correct answers. 

Correct answers
1. d
The SELECT statement has a FROM clause after which the table name(s) are specified. It can get data from any one table or multiple tables in the database.

2. d
The SELECT expression may specify any columns e.g. AuthorName, BookTitle or all columns with * or a computed value which may or may not be based on a column value e.g. Price * 100 and 'abc' respectively

3. b
The function COUNT gives the total number of rows in a table. ROWCOUNT is an attribute and not a function. It gives the number of rows affected by the previous SQL statement.

4. a
The correct condition has the syntax, value BETWEEN value AND value. d. is incorrect because it excludes books with price of exactly 10 or exactly 25.

5. b
CROSS join returns all rows from the two tables, LEFT OUTER join returns all rows from the first table and RIGHT OUTER join returns all rows from the second table.

6. c
An aggregate function e.g. COUNT, SUM, MAX or MIN has to be used with GROUP BY clause. For example, SELECT CustomerName, SUM(OrderValue) FROM Orders GROUP BY CustomerName

7. d
Each of a,b and c are true for a normalized database.

8. d
a. has a concatenation problem, b has a duplicate ORDER BY clause and c has DESCENDING order that does not work with all RDBMS

9. b
The UNION operator returns only the distinct rows in the two resultsets. UNION ALL is needed to return duplicate rows also.

10. c
Other answers may or may not be true for a correlated subquery. An example of a correlated subqquery (in italics) is
SELECT EmpID, EmpName FROM Employees as E WHERE EmpSalary <
(SELECT AVG(EmpSalary) FROM Employees WHERE EmpDeptId = E.EmpDeptId)