SQL Query Essentials Exam

SQL Query Essentials Course Exam

1. True or False:
SQL queries are written in plain text.

True

False

2. SQL is an acronym for:

Simple Query Language

Symbolic Quantification Logarithm

Structured Query Language

Something Quite Luxurious

3. The process of structuring data into related tables to reduce data redundancy is called:

Data Normalization

Query Optimization

Distinct Management

None of the above

4. In Microsoft SQL Server Management Studio a query is executed by:

a. Pressing F5 on the keyboard

b. Clicking “Execute” on the SQL Editor toolbar

c. Typing “RUN” in the Query Editor and pressing the ENTER key

d. All of the above

e. Answers a and b

f. None of the above

5. True or false:
The only way to indicate what database is used in Microsoft SQL Server Managment Studio is the “USE” statement.

True

False

6. Which SQL statement is used to indicate what fields or columns should be included in the query results:

CHOOSE

PICK

SELECT

INCLUDE

7. Which SQL statement is used to indicate which table(s) will be used to retrieve data?

WHERE

SELECT

HAVING

FROM

8. Which SQL statement is used to indicate selection criteria in a query?

CRITERIA

CHOOSE

WHERE

FILTER ON

9. True or false:
Parentheses cannot be used in the WHERE clause of a query to group criteria that contain “AND” and “OR”.

True

False

10. True or false:
The BEWTEEN operator includes the lower and upper values in the result set if the values are present in the data.

True

False

11. Which of these statements is a valid WHERE clause in a query?

WHERE fielda >= fieldb

WHERE fielda => fieldb

WHERE fielda =< fieldb

All of the above

None of the above

12. Which of these statements is a valid WHERE clause in a query?

WHERE LastName = “Smith”

WHERE LastName = ‘Smith’

WHERE LastName <> “Smith”

All of the above

13. Which of the following WHERE clauses will return results with only last names that start with the letter J?

WHERE LastName > ‘J’

WHERE LastName = ‘J*’

WHERE LastName = ‘J_’

WHERE LastName Like ‘J*’

WHERE LastName Like ‘J%’

14. Which of these statements is a valid WHERE clause in a query (datefield represents a column stored as DATE)?

WHERE datefield = 2001-07-04

WHERE datefield = ‘2001-07-04’

WHERE datefield = 20010704

All of the above

15. Which of these statements will limit the query results to 5 rows of data in Microsoft SQL Server?

SELECT FIRST 5 fielda, fieldb, fieldc

SELECT fielda, fieldb, fieldc WITH LIMIT 5

SELECT GET 5 fielda, fieldb, fieldc

SELECT TOP 5 fielda, fieldb, fieldc

16. Which of the following can be used to create a comment in SQL?

a. – – (Two dashes)

b. /* */ (A slash with an asterisk followed by an asterisk with a slash)

c. ! (An exclamation point)

d. All of the above

e. Answers a and b

17. True or false:
A comment that starts with – – (two dashes) can extend to multiple lines.

True

False

18. True or false:
An Inner Join must include the word “Inner” when used in SQL.

True

False

19. Which statement is true about Inner Joins?

An Inner Join returns all data from both tables even if there is not a matching value.

An Inner Join returns all data from the first table and only data from the second table where there is a matching value.

An Inner Join returns data from both tables where there is a matching value.

All queries must contain at least one Inner Join.

20. True or false:
A Left Outer Join must include the word “Outer” when used in SQL.

True

False

21. Which statement is true about Left Outer Joins?

A Left Outer Join returns all data from both tables even if there is not a matching value.

A Left Outer Join returns all data from the first table and only data from the second table where there is a matching value.

A Left Outer Join only returns data from the first table if there is a matching value in the second table.

A Left Outer Join cannot be used if the query also contains a Group By statement.

22. True or false:
A Full Outer Join must include the word “Outer” when used in SQL.

True

False

23. Which statement is true about Full Outer Joins?

A Full Outer Join includes all data from both tables even if there is not a matching value.

A Full Outer Join includes all data from the first table and only data from the second table where there is a matching value.

A Full Outer Join only includes data from both tables where there is a matching value.

A Full Outer Join cannot be used if the query also contains a Group By statement.

24. True or false:
It is a best practice to use table aliases when a query uses data from more than one table.

True

False

25. True or false:
It is necessary to use the word “As” when creating a table alias in SQL.

True

False

26. Which of the following are part of best practices when writing queries in SQL?

Adding comments

Formatting SQL with line breaks and indentation

Capitalizing key words

Limiting the use of “SELECT *”

All of the above

27. Which of the following methods can eliminate duplicate data rows in a result set?

a. SELECT DISTINCT

b. List all fields in a GROUP BY clause

c. DEDUPE ON

d. SELECT NO DUP

e. Answers a and b

f. Answers a, b, and d

28. Which of the following is not an aggregate function included in SQL?

SUM

AVE

MIN

MAX

None of the above. (All are aggregate functions in SQL.)

29. True or false:
The COUNT function counts all rows of a specified field in a result set even if that field contains NULLS in the result set.

True

False

30. True or false:
The HAVING clause must come before the GROUP BY clause in an SQL query.

True

False

31. Which statement is true regarding the HAVING clause?

The HAVING clause allows criteria on aggregated values to be added to a query.

All SQL queries must include a HAVING clause.

The HAVING clause is placed before the FROM clause in a query.

The HAVING clause determines the order of the results in a query.

32. True or false:
The order of results cannot be controlled in SQL.

True

False

33. True or false:
The ORDER BY statement can only order results based on values in one column.

True

False

34. True or false:
The ORDER BY statement can only order results in ascending order.

True

False

35. Which of the following is a valid ORDER BY statement?

a. ORDER BY fielda DESC

b. ORDER BY fielda

c. ORDER BY fielda DOWN

d. ORDER BY fielda UP

e. Answers a and b

f. Answers a, b, c, d

g. Answers a, c, d

36. Which of the following can evaluate if a field is null and return a supplied value if it is null?

a. ISNULL function

b. FILL function

c. SUPPLY function

d. All of the above

e. Answers a and b

37. Which of the following is a valid CASE statement?

CASE fielda = 1 THEN ‘Good’ ELSE ‘BAD’ END as ColumnA

CASE WHEN fielda = 1 THEN ‘Good’ END as ColumnA

CASE WHEN fielda = 1 THEN ‘Good’ ELSE ‘BAD’ as ColumnA

None of the above.

38. True or false:
The CASE statement must always include an ELSE clause.

True

False

39. True or false:
SQL Server Management Studio cannot save query results in a pipe delimited format.

True

False

40. True or false:
SQL Server Management Studio places quotes around text fields when exporting to a csv file by default.

True

False

41. Which of the following criteria will exclude both Smith and Jones from the results?

a. WHERE LastName <> (‘Smith’, ‘Jones’)

b. WHERE LastName <> ‘Smith’ or LastName <> ‘Jones’

c. WHERE LastName <> ‘Smith’ and LastName <> ‘Jones’

d. WHERE LastName NOT IN (‘Smith’, ‘Jones’)

e. None of the above.

f. Answers c and d.

42. Which of the following will count the number of unique last names in the Employees table?

a. SELECT LastName FROM Employees GROUP BY LastName

b. SELECT COUNT (LastName) as NameCount FROM Employees GROUP BY LastName

c. SELECT COUNT (DISTINCT LastName) as NameCount FROM Employees

d. Answers a and c

43. Which of the following criteria will limit the results to only include last names of Jones, Miller, and Smith?

a. WHERE LastName = ‘Jones’ and LastName = ‘Miller’ and LastName = ‘Smith’

b. WHERE LastName = ‘Jones’ or LastName = ‘Miller’ or LastName = ‘Smith’

c. WHERE LastName IN (‘Jones’, ‘Miller’, ‘Smith’)

d. Answers a and c

e. Answers b and c

44. Which of the following criteria will return all work dates in calendar year 2016? (WorkDate is stored as DATE)

WHERE WorkDate >= ‘2016-01-01’ or WorkDate <='2016-12-31'

WHERE WorkDate BETWEEN ‘2016-01-01’ and ‘2016-12-31’

WHERE WorkDate IN (‘2016-01-01’, ‘2016-12-31’)

All of the above

45. Which of the following will return the total hours worked for each employee ID?

SELECT EmployeeID, TOTAL(HoursWorked) as TotalHours FROM EmployeeHours

SELECT EmployeeID, SUM(HoursWorked) as TotalHours FROM EmployeeHours GROUP BY EmployeeID

SELECT SUM(HoursWorked) as TotalHours FROM EmployeeHours

SELECT EmployeeID, TOTAL(HoursWorked) as TotalHours FROM EmployeeHours GROUP BY EmployeeID

46. What type of join is the following statement?

JOIN OfficeLocations ON Employees.LocationID=OfficeLocations.LocationID

Full Join

Left Join

Outer Join

Inner Join

47. True or false:
The following is a valid SQL query:

	SELECT 
		EmployeeID,
		CASE WHEN StartDate >= '2016-01-01'
		     THEN 'After Merger'
		     ELSE 'Before Merger'  
		     AS StartIndicator
	FROM Employees

True

False

48. True or false:
The following is a valid SQL query:

	SELECT 
		EmployeeID
		FirstName
		LastName
	FROM Employees

True

False

49. True or false:
The following is a valid SQL query:

	SELECT
		FirstName,
		LastName
	FROM Employees
	ORDER BY LastName, FirstName

True

False

50. Which of the following are optional sections in a SELECT query:

WHERE

GROUP BY

ORDER BY

All of the above

Question 1 of 50

Quick Links

Categories