Brainscape's Knowledge GenomeTM

Browse over 1 million classes created by top students, professors, publishers, and experts.


See full index

C175 Data Management Foundations > 3 Complex Queries > Flashcards

3 Complex Queries Flashcards

1
Q

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

A

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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
2
Q

How can you filter records in a SQL query?

A

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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
3
Q

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

A

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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
4
Q

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

A

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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
5
Q

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

A

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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
6
Q

What is the purpose of the NULL value in SQL?

A

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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
7
Q

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

A

To find unique values in a column, such as unique languages spoken in a country.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
8
Q

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

A

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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
9
Q

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

A

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.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
10
Q

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

A

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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
11
Q

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

A

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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
12
Q

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

A

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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
13
Q

How can you use nested SELECT statements in SQL?

A

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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
14
Q

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

A

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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
15
Q

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

A

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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
16
Q

ABS(n)

A

Returns the absolute value of n

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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
17
Q

LOG(n)

A

Returns the natural logarithm of n

Example: SELECT LOG(10); Result: 2.302585092904046

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
18
Q

POW(x, y)

A

Returns x to the power of y

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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
19
Q

RAND()

A

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

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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
20
Q

ROUND(n, d)

A

Returns n rounded to d decimal places

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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
21
Q

SQRT(n)

A

Returns the square root of n

Example: SELECT SQRT(25); Result: 5

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
22
Q

CONCAT(s1, s2, …)

A

Concatenates string arguments

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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
23
Q

LOWER(s)

A

Returns the lowercase version of s

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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
24
Q

REPLACE(s, from, to)

A

Replaces occurrences of from with to in s

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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
25
Q

SUBSTRING(s, pos, len)

A
  • 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”

Example: SELECT SUBSTRING(‘Boomerang’, 1, 4); Result: ‘Boom’

26
Q

TRIM(s)

A

Removes leading and trailing spaces

Example: SELECT TRIM(‘ test ‘); Result: ‘test’

27
Q

UPPER(s)

A

Converts s to uppercase

Example: SELECT UPPER(‘mysql’); Result: ‘MYSQL’

28
Q

What does the function CURDATE() return?

A

Returns the current date, time, or date and time in

'YYYY-MM-DD', 'HH:MM:SS', or
'YYYY-MM-DD HH:MM:SS' format

SELECT CURDATE(); returns ‘2019-01-25

29
Q

What does the function CURTIME() return?

A

Returns the current date, time, or date and time in

'YYYY-MM-DD', 'HH:MM:SS', or
'YYYY-MM-DD HH:MM:SS' format

SELECT CURTIME(); Returns ‘21:05:44’

30
Q

What does the function NOW() return?

A

Returns the current date, time, or date and time in

'YYYY-MM-DD', 'HH:MM:SS', or
'YYYY-MM-DD HH:MM:SS' format

SELECT NOW(); Returns ‘2019-01-25 21:05:44’

31
Q

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

A

Extracts date from datetime expression

SELECT DATE(‘2013-03-25 22:11:45’); Returns ‘2013-03-25’

32
Q

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

A

Extracts time from datetime expression

SELECT TIME(‘2013-03-25 22:11:45’); Returns ‘22:11:45’

33
Q

What does the DAY(d) function return?

A

Returns the day from date d

SELECT DAY(‘2016-10-25’); Returns 25

34
Q

What does the MONTH(d) function return?

A

Returns the month from date d

SELECT MONTH(‘2016-10-25’); Returns 10

35
Q

What does the YEAR(d) function return?

A

Returns the year from date d

SELECT YEAR(‘2016-10-25’); Returns 2016

36
Q

What does the HOUR(t) function return?

A

Returns hour from time t

SELECT HOUR(‘22:11:45’); Returns 22

37
Q

What does the MINUTE(t) function return?

A

Returns minute from time t

SELECT MINUTE (‘22:11:45’);
Returns 11

38
Q

What does the SECOND(t) function return?

A

Returns second from time t

SELECT SECOND(‘22:11:45’); Returns 45

39
Q

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

A
  • Returns expr1 - expr2 in number of days or time.
  • Values, given expr1 and expr2 are date, time, or datetime values

SELECT DATEDIFF(‘2013-03-10’, ‘2013-03-04’); Returns 6

40
Q

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

A
  • Returns expr1 - expr2 in number of days or time.
  • Values, given expr1 and expr2 are date, time, or datetime values

SELECT TIMEDIFF(‘10:00:00’, ‘09:45:30’); Returns 00:14:30

41
Q

Date & Time functions

The function _____ extracts the day from a date.

A

DAY

42
Q

Date & Time functions

The function _____ extracts the time from a datetime expression.

A

TIME

43
Q

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

A

YYYY-MM-DD

44
Q

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

A

HH:MM:SS

45
Q

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

A

YYYY-MM-DD HH:MM:SS

46
Q

What would be the result of SELECT HOUR(‘22:11:45’)?

A

22

47
Q

What would be the result of SELECT DATEDIFF(‘2013-03-10’, ‘2013-03-04’)?

A

6

48
Q

What would be the result of SELECT TIMEDIFF(‘10:00:00’, ‘09:45:30’)?

A

00:14:30

49
Q

Refer to the table

What would the SELECT statement produce?

SELECT TIME(Due) 
FROM Assignment 
WHERE ID = 2;
A

23:59:00

TIME() extracts the time from ‘2019-11-02 23:59:00’.

50
Q

Refer to the table

What would the SELECT statement produce?

SELECT DAY(Due) 
FROM Assignment 
WHERE ID = 4;
A

14

DAY() extracts the day from ‘2019-11-14’

51
Q

Refer to the table

What would the SELECT statement produce?

SELECT HOUR(Assigned) + MINUTE(Assigned) 
FROM Assignment 
WHERE ID = 2;
A

42

HOUR() extracts the hour from ‘12:30:00’, and MINUTE() extracts the minute. 12 + 30 = 42.

52
Q

Refer to the table

What would the SELECT statement produce?

SELECT DATEDIFF(Due, Assigned) 
FROM Assignment WHERE 
ID = 1;
A

1

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

53
Q

Refer to the Problem Table

What are the results from each SELECT statement?
~~~
SELECT ABS(X - Y)
FROM Problem;
~~~

A 
3
8
7

ABS(X - Y) finds the absolute value of the X column values minus the Y column values.

54
Q

Refer to the Problem Table

What are the results from each SELECT statement?

SELECT ROUND(X / Y, 0) 
FROM Problem;
A 
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: 5 / 2.5 rounded is 3, 9 / 1 rounded is 9, and 3 / 10 rounded is 0.
55
Q

Refer to the Problem Table

What are the results from each SELECT statement?

SELECT ROUND(SQRT(X), 1) 
FROM Problem;
A 
2.0
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.
56
Q

Refer to the Problem Table

What are the results from each SELECT statement?

SELECT X, Y, POW(X, Y) 
FROM Problem;
A
  • 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.
57
Q

Refer to the Avatar Table

What are the results from each SELECT statement?

SELECT CONCAT('Super ', Name) 
FROM Avatar 
WHERE ID = 1;
A 
Super Link
58
Q

Refer to the Avatar Table

What are the results from each SELECT statement?

SELECT LOWER(BestMove) 
FROM Avatar 
WHERE ID = 3;
A 
psystrike
59
Q

Refer to the Avatar Table

What are the results from each SELECT statement?

SELECT SUBSTRING(BestMove, 7, 6) 
FROM Avatar 
WHERE ID = 4;
A 
Finale

SELECT SUBSTRING(BestMove, 7, 6) means that value in BestMove : “Mario Finale” starts at position 7 and is 6 characters long.

60
Q

Refer to the Avatar Table

What are the results from each SELECT statement?

SELECT REPLACE(Name, 'Kn', 'Fr') 
FROM Avatar 
WHERE ID = 2;
A 
Meta Fright

REPLACE() replaces ‘Kn’ in ‘Meta Knight’ with ‘Fr’.

C175 Data Management Foundations (19 decks)

Brainscape helps you reach your goals faster, through stronger study habits.
© 2024 Bold Learning Solutions. Terms and Conditions