3.1: Special operators and clauses

Question 1

What is the purpose of the IN operator in SQL?

Answer 1

The IN operator is used in a WHERE clause to determine if a value matches one of several specified values.

Question 2

Give an example of a query using the IN operator.

Answer 2

SELECT * 
FROM CountryLanguage 
WHERE Language IN ('Dutch', 'Kongo', 'Albanian');

Question 3

What does the BETWEEN operator do in SQL?

Answer 3

The BETWEEN operator checks if a value is within a specified range, inclusive of the minimum and maximum values.

Question 4

How is the BETWEEN operator written in SQL?

Answer 4

It is written as value BETWEEN minValue AND maxValue, which is equivalent to value >= minValue AND value <= maxValue.

Question 5

What is the function of the LIKE operator in SQL?

Answer 5

• % matches any number of characters. Ex: LIKE 'L%t' matches “Lt”, “Lot”, “Lift”, and “Lol cat”.
• _ matches exactly one character. Ex: LIKE 'L_t' matches “Lot” and “Lit” but not “Lt” and “Loot”.

Question 6

Provide an example of a query using the LIKE operator.

Answer 6

SELECT * FROM CountryLanguage 
WHERE Language LIKE 'A%';

Question 7

What does the DISTINCT clause do in SQL?

Answer 7

The DISTINCT clause returns only unique or distinct values from a column in a SELECT statement.

Question 8

Write a query that uses the DISTINCT clause.

Answer 8

SELECT DISTINCT Language
 FROM CountryLanguage 
WHERE IsOfficial = 'F'
;

Question 9

Explain the use of the ORDER BY clause in SQL.

Answer 9

The ORDER BY clause is used to sort the result set of a query by one or more columns in ascending or descending order.

Question 10

How would you sort results in descending order by a specific column?

Answer 10

1. By using the DESC keyword, e.g.,

SELECT * 
FROM CountryLanguage 
ORDER BY Language DESC;

Question 11

What is the effect of using NULL in the IN operator?

Answer 11

Including NULL in the list with the IN operator will generally result in no matches unless explicitly handled, as NULL is not considered equal to any value.

Question 12

What is the syntax for the BETWEEN operator?

Answer 12

The syntax is written as: value BETWEEN minValue AND maxValue.

Question 13

Can the LIKE operator handle case sensitivity?

Answer 13

By default, the LIKE operator performs case-insensitive matching unless followed by the BINARY keyword.

Question 14

What is the result of a query using SELECT DISTINCT?

Answer 14

It returns only unique entries from the specified column, removing duplicates from the result set.

Question 15

SELECT * 
FROM CountryLanguage 
WHERE Language IN ('Dutch', 'Kongo', 'Albanian');

Explain the result

Answer 15

ABW   Dutch      T   5.3
AGO   Kongo      F   13.2
ALB   Albanian   T   97.9

• The IN operator is used in a WHERE clause to determine if a value matches one of several values.
• The SELECT statement in the figure below uses the IN operator to select only rows where the Language column has a Dutch, Kongo, or Albanian value.

Question 16

Refer to the Country table

Explain the results

SELECT Name 
FROM Country 
WHERE Continent IN ('Asia', 'Europe', 'South America');

Answer 16

Afghanistan is in Asia, Albania, and Andorra are in Europe, and no countries are in South America.

Question 17

Refer to the Country table

Explain the results

SELECT Name 
FROM Country 
WHERE Code IN ('AGO', 'Aruba', 'Europe', NULL);

Answer 17

Angola’s Code is AGO, but other values listed are not used in the Code column.

Question 18

What is the alternatrive way BETWEEN

SELECT Name
FROM Employee
WHERE HireDate >= '2000-01-01' AND HireDate <= '2020-01-01';

Answer 18

SELECT Name
FROM Employee
WHERE HireDate BETWEEN '2000-01-01' AND '2020-01-01';

Question 19

Using the BETWEEN syntax

What are the alternative ways to determine if a value is between two other values

Answer 19

1. value BETWEEN minValue AND maxValue
2. value >= minValue AND value <= maxValue

Question 20

Using the LIKE syntax

Explain the result.

SELECT *
FROM CountryLanguage
WHERE CountryCode LIKE 'A_W';

Answer 20

Using A_W with the LIKE operator matches ‘A’, any character, then ‘W’.

Question 21

Using the LIKE syntax

Explain the results.

SELECT *
FROM CountryLanguage
WHERE Language LIKE 'A%n';

Answer 21

Using A%n with the LIKE operator matches ‘A’, any number of characters, then ‘n’.

Question 22

1. If the answer is
2. What can the LIKE satement be depicted as?

Question 23

What can the LIKE satement be depicted as?

1. If the answer is Arawakan and Caribbean

SELECT Language 
FROM CountryLanguage 
WHERE Language \_\_\_\_\_\_\_;

Answer 23

LIKE '%r%n'

Both languages have any number of characters before “r” and any number of characters before “n”.

Question 24

What can the LIKE satement be depicted as?

1. If the answer is Chibcha

SELECT Language 
FROM CountryLanguage 
WHERE Language \_\_\_\_\_\_\_;

Answer 24

LIKE '%cha'

Only Chibcha ends with “cha”.

Question 25

What can the LIKE satement be depicted as?

1. If the answer is Maori

SELECT Language 
FROM CountryLanguage 
WHERE Language \_\_\_\_\_\_\_;

Answer 25

LIKE '%m_o%'

Only Maori has an “m” followed by a single character and “o”.

Question 26

What can the LIKE satement be depicted as?

1. If the answer is English

SELECT Language 
FROM CountryLanguage 
WHERE Language \_\_\_\_\_\_\_;

Answer 26

LIKE BINARY '%E%'

BINARY makes the pattern matching case-sensitive. Only English has a capital “E”.

Question 27

DISTINCT Clause

If the result set is listed; what can the query be depicted as?

ARG  Distrito Federal
BRA  São Paulo
KEN  Central
NPL  Central
VEN  Distrito Federal

Answer 27

SELECT DISTINCT CountryCode, District 
FROM City;

The ‘NPL’ and ‘Central’ row only appears once in the result set because of the DISTINCT clause.

Question 28

DISTINCT Clause

If the result set is listed; what can the query be depicted as?

ARG  Distrito Federal
BRA  São Paulo
KEN  Central
NPL  Central
NPL  Central
VEN  Distrito Federal

Answer 28

SELECT CountryCode, District 
FROM City;

Every country code and district appears in the result set because the query does not use a DISTINCT clause.

Question 29

DISTINCT Clause

If the result set is listed; what can the query be depicted as?

Distrito Federal
São Paulo
Central

Answer 29

SELECT DISTINCT District 
FROM City;

Duplicate ‘Central’ and ‘Distrito Federal’ rows do not appear in the result set because of the DISTINCT clause.

Question 30

DISTINCT Clause

If the result set is listed; what can the query be depicted as?

Distrito Federal
São Paulo
Central
Central
Central
Distrito Federal

Answer 30

SELECT District
FROM City;

Every district appears in the result set because the query does not use a DISTINCT clause.

Question 31

ORDER BY Clause

If the result set is listed; what can the query be depicted as?

Answer 31

SELECT Name, District, Population 
FROM City 
ORDER BY Name;

The rows are in ascending order by Name. Text is ordered in a case-insensitive fashion

Question 32

ORDER BY Clause

If the result set is listed; what can the query be depicted as?

Answer 32

SELECT Name, District, Population 
FROM City 
ORDER BY Population;

The Population column is in ascending order.

Question 33

ORDER BY Clause

If the result set is listed; what can the query be depicted as?

Answer 33

SELECT Name, District, Population 
FROM City 
ORDER BY Disctrict DESC;

The rows are ordered by District in descending order.

Question 35

ORDER BY Clause

If the result set is listed; what can the query be depicted as?

Answer 35

SELECT Name, District, Population 
FROM City 
ORDER BY District, Population;

Within each District grouping, the rows are then ordered by Population in ascending order.