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’
