Functions Flashcards
Return column ‘first_name’ in all upper-case
SELECT UPPER(first_name) FROM employees
Return column ‘first_name’ in all upper-case
SELECT LOWER(first_name) FROM employees
Return the length of each value in column ‘first_name’
SELECT LENGTH(first_name) FROM employees
Trim the whitespace around the first_name values
SELECT TRIM(first_name) FROM employees
Combine columns first_name and last_name into one column called name (including a space between the two)
SELECT first_name ||’ ‘|| last_name FROM employees
What does SELECT first_name, (salary > 140000) FROM employees return?
It returns two columns: one which is first_name, and the other which is a column of booleans which return True or False depending on if the row’s ‘salary’ value is greater/less than 140000
What happens when you execute this:
SELECT SUBSTRING(‘This is test data’ FROM 1 FOR 4)
It will return 4 characters starting at the first character, which is ‘This’
What is the purpose of ‘SUBSTRING()’?
It will return a substring based on a starting location and the number of characters following it, e.g.
SELECT SUBSTRING(‘Hello’ FROM 2 FOR 2) –> el
Replace ‘Clothing’ values with ‘Attire’ in the department column in your SELECT statement
SELECT department, REPLACE(department, ‘Clothing’, ‘Attire’)
FROM departments
Return the domain names of all the emails in the ‘email’ column
SELECT SUBSTRING(email, POSITION(‘@’ IN email) + 1)
What does POSITION() do?
it returns the positions of the specified value in a column, i.e.
POSITION(‘value’ IN column)
What does COALESCE() do?
It fills nan values with a specified value in a specified column, i.e.
COALESCE(email, ‘Hi’)
Return the max, min, and rounded average of the salary column from employees
SELECT ROUND(AVG(salary)) average, MAX(salary) maximum, MIN(salary) minimum FROM employees
Return the number of rows in employees
SELECT COUNT(*) FROM employees
T/F: COUNT() will include the nans in a column in its count
False, it will skip nans
