Lesson 3

Question 1

BETWEEN

Answer 1

determines if a value is between two other values

BETWEEN minValue AND maxValue

Question 2

LIKE

Answer 2

when used in a WHERE clause, matches text against a pattern using the two wildcard character %(percentage symbol) and _(underscore symbol)

Question 3

ORDER BY

Answer 3

orders selected rows by one or more columns in ascending order

DESC reverses to descending order

Question 4

function/argument

Answer 4

A function operates on an expression enclosed in parentheses, called an argument, and returns a value

Question 5

ABS()

Answer 5

returns the absolute value

SELECT ABS(-5); – returns 5

Question 6

LOWER()

Answer 6

returns lowercase

SELECT LOWER(MySQL); – returns mysql

Question 7

TRIM()

Answer 7

removes leading and trailing whitespace

SELECT TRIM(‘ hello ‘); – returns ‘hello’

LTRIM removes leading spaces
RTRIM removes traling spaces

Question 8

HOUR(), MINUTE(), SECOND()

Answer 8

returns hour, minute or second from timestamp

Question 9

aggregate function

Answer 9

processes values from a set of rows and returns a summary value

Question 10

COUNT()

Answer 10

count the number of rows

aggregate function

Question 11

MIN()

Answer 11

finds the minimum value

aggregate function

Question 12

MAX()

Answer 12

fins the maximum value

aggregate function

Question 13

SUM()

Answer 13

sums all the values in the set

aggregate function

Question 14

AVG()

Answer 14

computes the arithmetic mean of all the values

aggregate function

Question 15

HAVING

Answer 15

used with the GROUPBY clause to filter group results

Question 16

GROUP BY

Answer 16

used with aggregate functions to produce summary rows

Question 17

join

Answer 17

A join is a SELECT statement that combines data from two tables

Question 18

AS

Answer 18

to simplify queries or result tables, a column name can be replaced with an alias

Question 19

INNER JOIN

Answer 19

selects only matching left and right table rows

Question 20

FULL JOIN

Answer 20

selects all left and right table rows, regardless of match

Question 21

LEFT JOIN

Answer 21

selects all left table rows, but only matching left table rows

Question 22

RIGHT JOIN

Answer 22

selects all tight table rows, but only matching left table rows

confusing, most avoid its usage

Question 23

UNION

Answer 23

combines the two results into one table

Question 24

Equijoin

Answer 24

Question 25

Non-Equijoin

Answer 25

Question 26

self-join

Answer 26

joins a table to itself

Question 27

cross-join

Answer 27

combines two tables without comparing columns

Question 28

subquery

Answer 28

sometimes called nested or inner query, is a query within another SQL query

Question 29

materialized view

Answer 29

view for which view table data is stored at all times

is automatically changed as the underlying data is changed

Question 30

IN

Answer 30

used in WHERE clause to determine if a value matches one of several values