3 Complex Queries

Question 1

What SQL command is used to retrieve data from a database?

Answer 1

The SELECT command is used to retrieve data from a database.

Question 2

How can you filter records in a SQL query?

Answer 2

By using the WHERE clause to specify conditions that the records must meet.

Question 3

What does the percentage symbol % represent in a LIKE operator query?

Answer 3

The percentage symbol % represents any number of characters in a LIKE operator query.

Question 4

What does the underscore symbol _ represent in a LIKE operator query?

Answer 4

The underscore symbol _ represents exactly one character in a LIKE operator query.

Question 5

In SQL, what does the term ‘case-insensitive’ mean?

Answer 5

Case-insensitive means that the comparison does not distinguish between uppercase and lowercase letters.

Question 6

What is the purpose of the NULL value in SQL?

Answer 6

NULL is used to represent missing or unknown data in a database.

Question 7

What is a common use case for the DISTINCT clause in SQL?

Answer 7

To find unique values in a column

such as unique languages spoken in a country

Question 8

How does the ORDER BY clause affect the result set of a query?

Answer 8

The ORDER BY clause sorts the result set by one or more specified columns, either in ascending or descending order.

Question 9

What is the function of the AND operator in a WHERE clause?

Answer 9

The AND operator combines multiple conditions in a WHERE clause and requires all conditions to be true for a record to be included in the result set.

Question 10

What is the function of the OR operator in a WHERE clause?

Answer 10

The OR operator combines multiple conditions in a WHERE clause and includes a record if at least one of the conditions is true.

Question 11

What does the statement value NOT IN (value1, value2, ...) do?

Answer 11

It filters records where the specified value is not equal to any of the values in the list.

Question 12

What is the difference between the BETWEEN operator and comparison operators?

Answer 12

The BETWEEN operator checks if a value falls within a range inclusively, while comparison operators check for less than, greater than, etc.

Question 13

How can you use nested SELECT statements in SQL?

Answer 13

You can use nested SELECT statements (subqueries) to perform more complex queries where the result of one query is used in another.

Question 14

What is the result of using the WHERE clause with a NULL value?

Example:
SELECT * FROM customers WHERE last_name IS NULL;

Answer 14

1. A WHERE clause that checks for equality with NULL & will return no results.
2. As NULL cannot be equal to anything, including itself.

Question 15

What is the importance of primary keys in relation to data integrity in a database?

Answer 15

Primary keys ensure that each record in a table is unique and not null, maintaining data integrity within the database.

Question 16

ABS(n)

Answer 16

Returns the absolute value of n

Example: SELECT ABS(-5); Result: 5

Question 17

LOG(n)

Answer 17

Returns the natural logarithm of n

Example: SELECT LOG(10); Result: 2.302585092904046

Question 18

POW(x, y)

Answer 18

Returns x to the power of y

Example: SELECT POW(2, 3); Result: 8

Question 19

RAND()

Answer 19

Returns a random number between 0 (inclusive) and 1 (exclusive)

Example: SELECT RAND(); Result: 0.11831825703225868 (will vary)

Question 20

ROUND(n, d)

Answer 20

Returns n rounded to d decimal places

Example: SELECT ROUND(16.25, 1); Result: 16.3

Question 21

SQRT(n)

Answer 21

Returns the square root of n

Example: SELECT SQRT(25); Result: 5

Question 22

CONCAT(s1, s2, …)

Answer 22

Concatenates string arguments

Example: SELECT CONCAT(‘Dis’, ‘en’, ‘gage’); Result: Disengage

Question 23

LOWER(s)

Answer 23

Returns the lowercase version of s

Example: SELECT LOWER(‘MySQL’); Result: ‘mysql’

Question 24

REPLACE(s, from, to)

Answer 24

Replaces occurrences of from with to in s

Example: SELECT REPLACE(‘This and that’, ‘and’, ‘or’); Result: ‘This or that’

Question 25

SUBSTRING(s, pos, len)

Answer 25

* Extracts a substring of *s* starting at *pos* with length *len* * For instance ```SELECT SUBSTRING(BestMove, 7, 6)``` Means that value in `BestMove` lets say "Mario Finale" starts at position 7 and is 6 characters long. Result: "Finale" ## Footnote Example: `SELECT SUBSTRING`('Boomerang', 1, 4); Result: 'Boom'

Question 26

TRIM(s)

Answer 26

Removes leading and trailing spaces ## Footnote Example: `SELECT TRIM`(' test '); Result: 'test'

Question 27

UPPER(s)

Answer 27

Converts *s* to uppercase ## Footnote Example: `SELECT UPPER`('mysql'); Result: MYSQL

Question 28

What does the function CURDATE() return?

Answer 28

Returns the current date, time, or date and time in ``` 'YYYY-MM-DD', 'HH:MM:SS', or 'YYYY-MM-DD HH:MM:SS' format ``` ## Footnote SELECT CURDATE(); returns '2024-12-26'

Question 29

What does the function CURTIME() return?

Answer 29

Returns the current date, time, or date and time in ``` 'YYYY-MM-DD', 'HH:MM:SS', or 'YYYY-MM-DD HH:MM:SS' format ``` ## Footnote SELECT CURTIME(); Returns '21:05:44'

Question 30

What does the function NOW() return?

Answer 30

Returns the current date, time, or date and time in ``` 'YYYY-MM-DD', 'HH:MM:SS', or 'YYYY-MM-DD HH:MM:SS' format ``` ## Footnote SELECT NOW(); Returns '2019-01-25 21:05:44'

Question 31

What is the purpose of the DATE(*expr*) function?

Answer 31

Extracts date from datetime expression ## Footnote SELECT DATE('2013-03-25 22:11:45'); Returns '2013-03-25'

Question 32

What is the purpose of the TIME(expr) function?

Answer 32

Extracts time from datetime expression ## Footnote SELECT TIME('2013-03-25 22:11:45'); Returns '22:11:45'

Question 33

What does the `DAY('d')` function return?

Answer 33

Returns the day from date `d` ## Footnote SELECT DAY('2016-10-25'); Returns 25

Question 34

What does the MONTH(d) function return?

Answer 34

Returns the month from date d ## Footnote SELECT MONTH('2016-10-25'); Returns 10

Question 35

What does the `YEAR('d')` function return?

Answer 35

Returns the year from date `d` ## Footnote SELECT YEAR('2016-10-25'); Returns 2016

Question 36

What does the HOUR(t) function return?

Answer 36

Returns hour from time t ## Footnote SELECT HOUR('22:11:45'); Returns 22

Question 37

What does the `MINUTE(t)` function return?

Answer 37

Returns minute from time t ## Footnote SELECT MINUTE ('22:11:45'); Returns 11

Question 38

What does the `SECOND('t')` function return?

Answer 38

Returns second from time `t` ## Footnote SELECT SECOND('22:11:45'); Returns 45

Question 39

What does the `DATEDIFF(expr1, expr2)` function return?

Answer 39

* Returns `expr1 - expr2` in number of days or time. * Values, given `expr1` and `expr2` are *date, time, or datetime* values ## Footnote SELECT DATEDIFF('2013-03-10', '2013-03-04'); Returns 6

Question 40

What does the `TIMEDIFF('expr1', 'expr2')` function return?

Answer 40

* Returns `expr1 - expr2` in number of days or time. * Values, given `expr1` and `expr2` are date, time, or datetime values ## Footnote SELECT TIMEDIFF('10:00:00', '09:45:30'); Returns 00:14:30

Question 41

# Date & Time functions The function _____ extracts the day from a date.

Answer 41

`DAY('d')`

Question 42

# Date & Time functions The function _____ extracts the time from a datetime expression.

Answer 42

`TIME('t')`

Question 43

What is the result format of the `CURDATE('d')` function?

Answer 43

`'YYYY-MM-DD'`

Question 44

What is the result format of the `CURTIME('t')` function?

Answer 44

`'HH:MM:SS'`

Question 45

What is the result format of the NOW() function?

Answer 45

`'YYYY-MM-DD', 'HH:MM:SS'`

Question 46

What would be the result of `SELECT HOUR('22:11:45')`?

Answer 46

22

Question 47

What would be the result? ``` SELECT DATEDIFF('2013-03-10', '2013-03-04') ```

Answer 47

6

Question 48

What would be the result? ``` SELECT TIMEDIFF('10:00:00', '09:45:30') ```

Answer 48

00:14:30

Question 49

# Refer to the table What would the SELECT statement produce? ``` SELECT TIME(Due) FROM Assignment WHERE ID = 2; ```

Answer 49

23:59:00 ## Footnote TIME() extracts the time from '2019-11-02 23:59:00'.

Question 50

# Refer to the table What would the SELECT statement produce? ``` SELECT DAY(Due) FROM Assignment WHERE ID = 4; ```

Answer 50

14 ## Footnote DAY() extracts the day from '2019-11-14'

Question 51

# Refer to the table What would the SELECT statement produce? ``` SELECT HOUR(Assigned) + MINUTE(Assigned) FROM Assignment WHERE ID = 2; ```

Answer 51

42 ## Footnote HOUR() extracts the hour from '12:30:00', and MINUTE() extracts the minute. 12 + 30 = 42.

Question 52

# Refer to the table What would the SELECT statement produce? ``` SELECT DATEDIFF(Due, Assigned) FROM Assignment WHERE ID = 1; ```

Answer 52

1 ## Footnote DATEDIFF() calculates the number of days from '2019-11-01' to '2019-11-02', which is just 1 day.

Question 53

# Refer to the `Problem` Table What are the results from each `SELECT` statement? ``` SELECT ABS(X - Y) FROM Problem; ```

Answer 53

``` 3 8 7 ``` ## Footnote `ABS(X - Y)` finds the absolute value of the `X` column values minus the `Y` column values.

Question 54

# Refer to the `Problem` Table What are the results from each `SELECT` statement? ``` SELECT ROUND(X / Y, 0) FROM Problem; ```

Answer 54

``` 3 9 0 ``` * After dividing the numbers in the X column by the numbers in the Y column, the answer is rounded to 0 decimal places: 1. 5 / 2 = 2.5 rounded is 3, 2. 9 / 1 rounded is 9, and 3. 3 / 10 = 0.3 rounded is 0

Question 55

# Refer to the `Problem` Table What are the results from each `SELECT` statement? ``` SELECT ROUND(SQRT(X), 1) FROM Problem; ```

Answer 55

``` 2.2 3.0 1.7 ``` * The `SQRT()` function finds the square root of the `X` column, then `ROUND()` rounds the result to one decimal place. * Ex: `SQRT(5) = 2.23606797749979`, and `ROUND(2.23606797749979, 1)` rounds to `2.2`.

Question 56

# Refer to the `Problem` Table What are the results from each `SELECT` statement? ``` SELECT X, Y, POW(X, Y) FROM Problem; ```

Answer 56

* The first column is X, the next column is Y, and the last column is X to the power of Y. * Ex: `POW(5, 2) = 25`.

Question 57

# Refer to the `Avatar` Table What are the results from each `SELECT` statement? ``` SELECT CONCAT('Super ', Name) FROM Avatar WHERE ID = 1; ```

Answer 57

``` Super Link ```

Question 58

# Refer to the `Avatar` Table What are the results from each `SELECT` statement? ``` SELECT LOWER(BestMove) FROM Avatar WHERE ID = 3; ```

Answer 58

``` psystrike ```

Question 59

# Refer to the `Avatar` Table What are the results from each `SELECT` statement? ``` SELECT SUBSTRING(BestMove, 7, 6) FROM Avatar WHERE ID = 4; ```

Answer 59

``` Finale ``` ## Footnote `SELECT SUBSTRING(BestMove, 7, 6)` means that value in `BestMove` : "Mario Finale" starts at position 7 and is 6 characters long.

Question 60

# Refer to the `Avatar` Table What are the results from each `SELECT` statement? ``` SELECT REPLACE(Name, 'Kn', 'Fr') FROM Avatar WHERE ID = 2; ```

Answer 60

``` Meta Fright ``` ## Footnote `REPLACE()` replaces 'Kn' in 'Meta Knight' with 'Fr'.