3 Complex Queries Flashcards
What SQL command is used to retrieve data from a database?
The SELECT command is used to retrieve data from a database.
How can you filter records in a SQL query?
By using the WHERE clause to specify conditions that the records must meet.
What does the percentage symbol % represent in a LIKE operator query?
The percentage symbol % represents any number of characters in a LIKE operator query.
What does the underscore symbol _ represent in a LIKE operator query?
The underscore symbol _ represents exactly one character in a LIKE operator query.
In SQL, what does the term ‘case-insensitive’ mean?
Case-insensitive means that the comparison does not distinguish between uppercase and lowercase letters.
What is the purpose of the NULL value in SQL?
NULL is used to represent missing or unknown data in a database.
What is a common use case for the DISTINCT clause in SQL?
To find unique values in a column
such as unique languages spoken in a country
How does the ORDER BY clause affect the result set of a query?
The ORDER BY clause sorts the result set by one or more specified columns, either in ascending or descending order.
What is the function of the AND operator in a WHERE clause?
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.
What is the function of the OR operator in a WHERE clause?
The OR operator combines multiple conditions in a WHERE clause and includes a record if at least one of the conditions is true.
What does the statement value NOT IN (value1, value2, ...) do?
It filters records where the specified value is not equal to any of the values in the list.
What is the difference between the BETWEEN operator and comparison operators?
The BETWEEN operator checks if a value falls within a range inclusively, while comparison operators check for less than, greater than, etc.
How can you use nested SELECT statements in SQL?
You can use nested SELECT statements (subqueries) to perform more complex queries where the result of one query is used in another.
What is the result of using the WHERE clause with a NULL value?
Example:SELECT *
FROM customers
WHERE last_name IS NULL;
- A
WHEREclause that checks for equality withNULL& will return no results. - As
NULLcannot be equal to anything, including itself.
What is the importance of primary keys in relation to data integrity in a database?
Primary keys ensure that each record in a table is unique and not null, maintaining data integrity within the database.
ABS(n)
Returns the absolute value of n
Example: SELECT ABS(-5); Result: 5
LOG(n)
Returns the natural logarithm of n
Example: SELECT LOG(10); Result: 2.302585092904046
POW(x, y)
Returns x to the power of y
Example: SELECT POW(2, 3); Result: 8
RAND()
Returns a random number between 0 (inclusive) and 1 (exclusive)
Example: SELECT RAND(); Result: 0.11831825703225868 (will vary)
ROUND(n, d)
Returns n rounded to d decimal places
Example: SELECT ROUND(16.25, 1); Result: 16.3
SQRT(n)
Returns the square root of n
Example: SELECT SQRT(25); Result: 5
CONCAT(s1, s2, …)
Concatenates string arguments
Example: SELECT CONCAT(‘Dis’, ‘en’, ‘gage’); Result: Disengage
LOWER(s)
Returns the lowercase version of s
Example: SELECT LOWER(‘MySQL’); Result: ‘mysql’
REPLACE(s, from, to)
Replaces occurrences of from with to in s
Example: SELECT REPLACE(‘This and that’, ‘and’, ‘or’); Result: ‘This or that’
SUBSTRING(s, pos, len)
- 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’
TRIM(s)
Removes leading and trailing spaces
Example: SELECT TRIM(‘ test ‘); Result: ‘test’
UPPER(s)
Converts s to uppercase
Example: SELECT UPPER(‘mysql’); Result: MYSQL
What does the function CURDATE() return?
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 ‘2024-12-26’
What does the function CURTIME() return?
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’
What does the function NOW() return?
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’
What is the purpose of the DATE(expr) function?
Extracts date from datetime expression
SELECT DATE(‘2013-03-25 22:11:45’); Returns ‘2013-03-25’
What is the purpose of the TIME(expr) function?
Extracts time from datetime expression
SELECT TIME(‘2013-03-25 22:11:45’); Returns ‘22:11:45’
What does the DAY('d') function return?
Returns the day from date d
SELECT DAY(‘2016-10-25’); Returns 25
What does the MONTH(d) function return?
Returns the month from date d
SELECT MONTH(‘2016-10-25’); Returns 10
What does the YEAR('d') function return?
Returns the year from date d
SELECT YEAR(‘2016-10-25’); Returns 2016
What does the HOUR(t) function return?
Returns hour from time t
SELECT HOUR(‘22:11:45’); Returns 22
What does the MINUTE(t) function return?
Returns minute from time t
SELECT MINUTE (‘22:11:45’);
Returns 11
What does the SECOND('t') function return?
Returns second from time t
SELECT SECOND(‘22:11:45’); Returns 45
What does the DATEDIFF(expr1, expr2) function return?
- Returns
expr1 - expr2in number of days or time. - Values, given
expr1andexpr2are date, time, or datetime values
SELECT DATEDIFF(‘2013-03-10’, ‘2013-03-04’); Returns 6
What does the TIMEDIFF('expr1', 'expr2') function return?
- Returns
expr1 - expr2in number of days or time. - Values, given
expr1andexpr2are date, time, or datetime values
SELECT TIMEDIFF(‘10:00:00’, ‘09:45:30’); Returns 00:14:30
Date & Time functions
The function _____ extracts the day from a date.
DAY('d')
Date & Time functions
The function _____ extracts the time from a datetime expression.
TIME('t')
What is the result format of the CURDATE('d') function?
'YYYY-MM-DD'
What is the result format of the CURTIME('t') function?
'HH:MM:SS'
What is the result format of the NOW() function?
'YYYY-MM-DD', 'HH:MM:SS'
What would be the result of SELECT HOUR('22:11:45')?
22
What would be the result?
SELECT DATEDIFF('2013-03-10', '2013-03-04')
6
What would be the result?
SELECT TIMEDIFF('10:00:00', '09:45:30')
00:14:30
Refer to the table
What would the SELECT statement produce?
SELECT TIME(Due) FROM Assignment WHERE ID = 2;
23:59:00
TIME() extracts the time from ‘2019-11-02 23:59:00’.
Refer to the table
What would the SELECT statement produce?
SELECT DAY(Due) FROM Assignment WHERE ID = 4;
14
DAY() extracts the day from ‘2019-11-14’
Refer to the table
What would the SELECT statement produce?
SELECT HOUR(Assigned) + MINUTE(Assigned) FROM Assignment WHERE ID = 2;
42
HOUR() extracts the hour from ‘12:30:00’, and MINUTE() extracts the minute. 12 + 30 = 42.
Refer to the table
What would the SELECT statement produce?
SELECT DATEDIFF(Due, Assigned) FROM Assignment WHERE ID = 1;
1
DATEDIFF() calculates the number of days from ‘2019-11-01’ to ‘2019-11-02’, which is just 1 day.
Refer to the Problem Table
What are the results from each SELECT statement?
SELECT ABS(X - Y) FROM Problem;
3 8 7
ABS(X - Y) finds the absolute value of the X column values minus the Y column values.
Refer to the Problem Table
What are the results from each SELECT statement?
SELECT ROUND(X / Y, 0) FROM Problem;
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 = 2.5 rounded is 3,
- 9 / 1 rounded is 9, and
- 3 / 10 = 0.3 rounded is 0
Refer to the Problem Table
What are the results from each SELECT statement?
SELECT ROUND(SQRT(X), 1) FROM Problem;
2.2 3.0 1.7
- The
SQRT()function finds the square root of theXcolumn, thenROUND()rounds the result to one decimal place. - Ex:
SQRT(5) = 2.23606797749979, andROUND(2.23606797749979, 1)rounds to2.2.
Refer to the Problem Table
What are the results from each SELECT statement?
SELECT X, Y, POW(X, Y) FROM Problem;
- 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.
Refer to the Avatar Table
What are the results from each SELECT statement?
SELECT CONCAT('Super ', Name)
FROM Avatar
WHERE ID = 1;
Super Link
Refer to the Avatar Table
What are the results from each SELECT statement?
SELECT LOWER(BestMove) FROM Avatar WHERE ID = 3;
psystrike
Refer to the Avatar Table
What are the results from each SELECT statement?
SELECT SUBSTRING(BestMove, 7, 6) FROM Avatar WHERE ID = 4;
Finale
SELECT SUBSTRING(BestMove, 7, 6) means that value in BestMove : “Mario Finale” starts at position 7 and is 6 characters long.
Refer to the Avatar Table
What are the results from each SELECT statement?
SELECT REPLACE(Name, 'Kn', 'Fr') FROM Avatar WHERE ID = 2;
Meta Fright
REPLACE() replaces ‘Kn’ in ‘Meta Knight’ with ‘Fr’.
