Operators

Question 1

INTERSECT

Answer 1

Combines two SELECT statements and returns only the dataset that is common in both the statements

Question 2

EXCEPT

Answer 2

Returns the distinct rows that are present in the result set of the first query but not in the result set of the second query.

Question 3

LIKE

Answer 3

The LIKE command is used in a WHERE clause to search for a specified pattern in a column.

You can use two wildcards with LIKE:

% - Represents zero, one, or multiple characters
_ - Represents a single character (MS Access uses a question mark (?) instead)

Question 4

REGEXP

Answer 4

Used to search for a specified pattern using regular expression pattern matches.

Question 5

UNION

Answer 5

Combines the data from the result of two or more SELECT statements into a single result set. This operator removes any duplicates present in the results being combined.

Question 6

UNION ALL

Answer 6

Combines the data from the result of two or more SELECT statements into a single result set. This operator DOES NOT REMOVE any duplicates present in the results being combined.

Question 7

IS NULL

Answer 7

Used to test for empty values (NULL values).

Question 8

AND

Answer 8

Displays a record if all the conditions stated in the query are TRUE.

Example:
SELECT * FROM Customers
WHERE Country = ‘Germany’
AND City = ‘Berlin’
AND PostalCode > 12000;

This will display all Customers that are located in Berlin, Germany with a postal code 12000.

Question 9

OR

Answer 9

Displays a record in which any of the conditions stated in the query are TRUE.

Example:
SELECT * FROM Customers
WHERE Country = ‘Germany’
OR City = ‘Berlin’
OR PostalCode > 12000;

This will display all Customers that are located in any city named Berlin (regardless of the country it’s in), customer in any city in Germany and customers that have their postal code as 12000.

Question 10

=

Answer 10

Returns values that are “Equal To” the value input in the query

Question 11

>

Answer 11

Returns values that are “Greater Than” the value input in the query

Question 12

<

Answer 12

Returns values that are “Less Than” the value input in the query

Question 13

> =

Answer 13

Returns values that are “Equal To or Greater Than” the value input in the query

Question 14

<=

Answer 14

Returns values that are “Equal To or Less Than” the value input in the query

Question 15

<>

Answer 15

Returns values that are “NOT Equal To” the value input in the query

Question 16

%

Answer 16

This represents the “modulo”. It is used to get the remainder from a division.

Question 17

IN

Answer 17

The IN operator allows you to specify multiple values in a WHERE clause. Example:

SELECT customerName, state
FROM customers
WHERE state IN (‘MA’, ‘CA’, ‘PA’)

This will return all of the customers that are registered in the database “customers” as located in the states of MA (Massachussets), CA (California) or PA (Pennsylvania).

Question 18

NOT IN

Answer 18

The NOT IN operator will return all records that are NOT any of the values in the list in a WHERE clause.
Example:

SELECT customerName, country
FROM customers
WHERE state NOT IN (‘USA’, ‘France’, ‘Germany’)

This will return all of the customers that are registered in the database “customers” and are NOT located in USA, France or Germany.

Question 19

CROSS JOIN

Answer 19

Used to generate a paired combination of each row of the first table with each row of the second table.

Question 20

INNER JOIN

Answer 20

Selects records that have matching values in both tables.

Question 21

When using a JOIN operator, if both tables are using the same field name what syntax could you use to call for that specific field in each table?

Answer 21

USING (nameofthefield);

Question 22

When using a JOIN operator, if the fields on each table do NOT share the same name, what syntax should you use to call for that specific field from each table?

Answer 22

ON table1.nameofthefield = table2.nameofthefieldonthistable

Question 23

Which table will LEFT JOIN retrieve the information from?

Answer 23

The first table

Question 24

Which table will RIGHT JOIN retrieve the information from?

Answer 24

The second table

Question 25

LEFT JOIN or LEFT OUTER JOIN

Answer 25

Returns all records from the left table (table1), and the matching records from the right table (table2)

Question 26

RIGHT JOIN or RIGHT OUTER JOIN

Answer 26

Returns all records from the right table (table2), and the matching records from the right table (table1)

Question 27

SELF JOIN

Answer 27

Used to join a table to itself as if the table were two tables

Question 28

What do you need to specify in a SELF JOIN?

Answer 28

The table aliases

Question 29

ORDER BY

Answer 29

Used to sort the fetched data in either ascending or descending according to one or more columns. If not specified it will order in ascending order by default.

SELECT column1, column2, …
FROM table_name
ORDER BY column1, column2, … ASC|DESC;

Question 30

What keyword do you need to add to the ORDER BY syntax to organize the results in ascending order?

Answer 30

ASC

Question 31

What keyword do you need to add to the ORDER BY syntax to organize the results in descending order?

Answer 31

DESC

Question 32

What function can we use to sort the results in a customized sort order?

Answer 32

field

Question 33

LIMIT

Answer 33

Used to return a limited number of records.

SELECT * FROM Customers
LIMIT 3;

You can also specify the starting point.

SELECT * FROM Customers
LIMIT 100, 3;

The ‘100’ is specifying the line number you want the query to start from. If not specified, it will automatically start from the first line.

Question 34

GROUP BY

Answer 34

Groups rows that have the same values into summary rows, like “find the number of customers in each country”.

Question 35

HAVING

Answer 35

Filters a result set to ONLY include records that fulfill a specified condition. It is used in aggregate functions instead of the clause WHERE, which doesn’t work in aggregate functions.

Question 36

What keyword do we use instead of WHERE in an aggregate function to meet certain conditions?

Answer 36

HAVING

WHERE does not work in aggregate functions

Question 37

WITH ROLLUP

Answer 37

Is an extension of the GROUP BY clause that produces sub-total of each group set (in addition to the grouped rows).

Question 38

EXIST