Data Analytics: SQL
What are the four types of JOINS?
(INNER) JOIN: Returns records that have matching values in both tables. LEFT JOIN: Returns all records from the left (first) table, and matched records from the right (second) table. RIGHT JOIN: Returns all records from the right (second) table, and matched records from the left (first) table. FULL (OUTER) JOIN: Returns all records when there is a match in the left (first) or right (second) tables.
What is SQL?
A standardized query language for requesting information from a database.
What is a wildcard operator?
A wildcard operator is used to substitute one or more characters in a string. % : represents 0 or more characters. _ : represents a single character. [] : represents any single character in brackets. ^/! : represents any character not in brackets. - : represents a range of characters in brackets. # : represents any single numeric character (like _ for nums)
How do you find the average of the values in a specified column?
AVG() AVG() function returns the average of the values in a specified column. The function follows SELECT and the name of the column is specified in the parentheses. SELECT AVG(PRICE) FROM PRODUCTS;
What does the BETWEEN operator do?
BETWEEN is used with AND in the WHERE clause; it selects values that lie within the specified range. WHERE PRICE BETWEEN 10 AND 20;
How do you find the number of values in a specified column?
COUNT() COUNT() function is used in the SELECT statement. It returns the number of values in the specified column. The function follows SELECT and the name of the column is specified in the parentheses. SELECT COUNT(ORDERID) FROM ORDERS;
What is the GROUP BY statement?
GROUP BY statement groups rows that have the same values intro summary rows. Often used with aggregate functions to group results. Comes after the WHERE clause and before the ORDER BY clause.
What does the IN/NOT IN operator do?
IN is used in the WHERE clause to specify multiple conditions; it's a shortcut for the OR operator, which gets messy when there are many conditions. SELECT COUNTRY FROM CUSTOMER WHERE COUNTRY NOT IN ('China', 'Cuba'); SELECT COUNTRY FROM CUSTOMER WHERE COUNTRY IN (SELECT COUNTRY FROM SUPPLIER);
How do you limit the number of results that are returned?
LIMIT The LIMIT clause limits the the number of records returned to the number specified.
How do you find the smallest or largest value in a column?
MIN() or MAX() MIN() and MAX() function are used in the SELECT statement and returns the smallest or largest values in the column specified. The function follow SELECT and the name of the column is placed in the parentheses. SELECT MAX(NUMORDERS) FROM CUSTOMER;
How do you sort results in ascending or descending order?
ORDER BY The ORDER BY clause sorts results in ascending (ASC) order or descending order (DESC). If ASC or DESC is not specified after ORDER BY, results are automatically sorted in ascending order. SELECT NAME, CITY FROM CUSTOMER ORDER BY CITY ASC;
How do you select data from the database while ignoring repeating values?
SELECT DISTINCT FROM SELECT DISTINCT returns only distinct/different values. Specify which columns you want after DISTINCT and from which table after FROM. SELECT DISTINCT COUNTRY FROM CUSTOMER;
How do you select data from the database?
SELECT FROM After SELECT, specify which columns you want from the database. After FROM, specify from which table in the database you are taking the columns from. SELECT NAME, CITY FROM CUSTOMER;
How do you find the sum of numerical values in a specified column?
SUM() SUM() function returns the sum of values in a specified column; it only works for columns with numeric values! It follows SELECT and the name of the column is specified in the parentheses.
What is the LIKE operator?
The LIKE operator is used in the WHERE clause to filter specific patterns using "%" or "_". % : Represents multiple characters. _ : Represents one character. ex. LIKE "%a%" --> returns values where a is in any position. LIKE "_a_" -- > returns values where a is the second letter in a three letter word.
What do the AND, OR, NOT operators do?
These operators can be used to apply AND, OR, NOT logic to the WHERE clause. AND: Displays records that satisfy both conditions in the WHERE clause (place between two conditions). OR: Displays records that satisfy at least one of the conditions in the WHERE clause (place between two conditions). NOT: Displays all records that do not satisfy the condition (place before the condition).
How do you filter out results returned using a specific standard?
WHERE Specify the condition after WHERE. Filter can be text or numerical; usually restricts some column name to a specific value. SELECT * FROM CUSTOMER WHERE NAME = 'Maya';
What is a JOIN?
A JOIN is used to combine rows from two or more tables, using a related column that they share; it must be used with ON = operator. JOIN comes after the SELECT FROM statement. One of the tables in the join in specified after FROM; the other table is specified after JOIN. After specifying the two tables that will be joined, we must specify ON which related column the join will be based off. Set the tableName1.relatedColumnName = tableName1.relatedColumnName
What is the HAVING clause?
The WHERE clause cannot be used with aggregate functions, so we use HAVING instead of WHERE when we need to use an aggregate function in our filter. * HAVING always follows GROUP BY. We cannot have HAVING without GROUP BY. SELECT COUNT(CustomerID), Country FROM Customers GROUP BY Country HAVING COUNT(CustomerID) > 5;
