An outer join will combine rows from different tables even if the join condition is not met. In a LEFT JOIN, every row in the left table is returned in the result set, and if the join condition is not met, then NULL values are used to fill in the columns from the right table.

The WITH clause stores the result of a query in a temporary table (temporary_movies) using an alias.

Multiple temporary tables can be defined with one instance of the WITH keyword.

The UNION clause is used to combine results that appear from multiple SELECT statements and filter duplicates.

For example, given a first_names table with a column name containing rows of data “James” and “Hermione”, and a last_names table with a column name containing rows of data “James”, “Hermione” and “Cassidy”, the result of this query would contain three names: “Cassidy”, “James”, and “Hermione”.

The CROSS JOIN clause is used to combine each row from one table with each row from another in the result set. This JOIN is helpful for creating all possible combinations for the records (rows) in two tables.

The given query will select the shirt_color and pants_color columns from the result set, which will contain all combinations of combining the rows in the shirts and pants tables. If there are 3 different shirt colors in the shirts table and 5 different pants colors in the pants table then the result set will contain 3 x 5 = 15 rows.

A foreign key is a reference in one table’s records to the primary key of another table. To maintain multiple records for a specific row, the use of foreign key plays a vital role. For instance, to track all the orders of a specific customer, the table order (illustrated at the bottom of the image) can contain a foreign key.

A primary key column in a SQL table is used to uniquely identify each record in that table. A primary key cannot be NULL. In the example, customer_id is the primary key. The same value cannot re-occur in a primary key column. Primary keys are often used in JOIN operations.

The JOIN clause allows for the return of results from more than one table by joining them together with other results based on common column values specified using an ON clause. INNER JOIN is the default JOIN and it will only return results matching the condition specified by ON.

Explanation: The queries above are labeled with the different elements that make up the simple workflow for joining two queries together with UNION. It is indicating to SQL to select column_1 and column_2 from table_1 and column_1 and column_2 from table_2. column_1, column_2, table_1, and table_2 are just placeholders for you to replace with the intended column and table names you want to use in your query. The semicolon at the end indicates that it is the end of query.'

Explanation: The queries above are indicating to SQL to select the product and cost columns from the transactions table and the product and cost columns from the r_transactions table. UNION will join these queries together and drop any duplicate data. The semicolon at the end indicates that it is the end of the query.

Explanation: The queries above are indicating to SWL to select the product and cost columns from the transactions table and the product and cost columns from the r_transactions table. UNION ALL will join these queries together and include all data, including duplicates. The semicolon at the end indicates that it is the end of the query.

Explanation: The query above is labeled with the different elements that allow us to rename column and table names. It is indicating to SQL to select column_1 (as c1) from table_1 (as t1) and group the results by c1. AS allows us to create aliases for the columns and tables we are interacting with in the query. These aliases make it easier when interacting with long and confusing column and table names. The semicolon at the end indicates that it is the end of the query.

Explanation: The query is the same as the previous one, but it removes the AS keyword. When renaming column and table names, we can either use AS after the full column or table name or we can put the aliases directly to the right of the actual column or table name (without using AS). Both work the same. The semicolon at the end indicates that it is the end of the query.

Explanation: The query above is labeled with the different elements that allow us to create subqueries. It is indicating to SQL to select all the values from the employees table where the pay_rate is greater than the average pay_rate from the employees table. This subquery makes our query dynamic rather than static. The semicolon at the end indicates that it is the end of the query.

Explanation: The query above labels the portion of our query that is the subquery. It is indicating to SQL to select two things: all the values from the employees column and the value that is returned from the subquery. Within the subquery, we are selecting the highest pay_rate value and renaming it as ceo. The semicolon at the end indicates that it is the end of the query.

Explanation: The query above is indicating to SQL to select the age column and range of pay_rate from the employees table. Then, the query groups our results by age. Within our query, MAX(pay_rate) - MIN(pay_rate) is renamed as pay_range. The semicolon at the end indicates that it is the end of the query.

Explanation: The query above is indicating to SQL to select the first_name, age, and vacation_accrued columns from the employees table. Then, the query filters our results by using WHERE and a subquery. Within the subquery, ANY compares each value in the vacation_accrued column from the employees table where age equals 63 and returns TRUE for any values that meet the condition. ANY and ALL are used with the WHERE or HAVING commands. The semicolon at the end indicates that it is the end of query.

Explanation: The query above is indicating to SQL to select the first_name, age, and vacation_accrued columns from the employees table. Then, the query filters our results by using WHERE and a subquery. Within the subquery, ALL returns TRUE if all of the values in the vacation_accrued column from the employees table meet the given condition. ANY and ALL are used with the WHERE or HAVING commands. The semicolon at the end indicates that it is the end of the query.

Explanation: The query above is labeled with the key components for writing correlated subqueries. It is indicating to SQL to select all of the values from the employees_pay table (aliased as e2) and the average pay_rate from the employees_pay table (aliased as e1) where e1 .department is equal to e2.department (aliased as average_pay). The semicolon at the end indicates that it is the end of the query.

​

filtering data ii (1).pdf

Explanation: The query above is indicating to SQL to select the fname column from the ages table. If the age is greater than or equal to 18, the result will be classified as an adult. If the age is less than 18, the result will be classified as a child. The semicolon at the end indicates that it is the end of the query.

Explanation: The query above is indicating to SQL to select the employee_id and pay_rate columns from the employees table. If the pay_rate is less than 20, the result will be classified as underpaid and aliased as payment. If the pay_rate is greater than 20, the result will be classified as acceptable and aliased as payment. the semicolon at the end indicates that it is the end of the query.

Explanation: The query above is indicating to SQL to select the count of employee_id as num_eps, average of pay_rate as avg_pay, and the pay_rate as payment. If the pay_rate is less than 20, the result will be classified as underpaid. If the pay_rate is greater than 20, the result will be classified as acceptable. The semicolon at the end indicates that it is the end of the query.

Explanation: The query above is labeled with the different elements that make up the syntax for using CASE. It is indicating to SQL to select column_1 from table_1 and to classify column_1 as "Less than 5" when column_1 <5, "More than 7" when column_1 > 7, and "Between 5 and 7" for any other result. END marks the end of the CASE statement and alias_name is the column name for the results. The semicolon at the end indicates that it is the end of the query.

Explanation: The query above is indicating to SQL to select the employee_id and pay_rate from the employees table and to classify pay_rate as "Underpaid" when pay_rate is less than 20, "Highly Paid" when pay_rate is greater than 50, and "Acceptable" for other cases. These results will be displayed in the payment column. The semicolon at the end indicates that it is the end of the query.

MySQL Data Types

Explanation: When creating new tables, it is important to consider which data type best suits each column. The above graphic introduces the seven different types of data types that SQL supports. These seven data types can be grouped into three main categories Numeric, String, and Date & Time.

Explanation: The query above is labeled with the key components that create a new table in DDL. It is indicating to DWL to create a table titled table_name with the columns column_1, column_2, and column_3. CREATE TABLE will create a table that contains the columns specified between the parentheses "(...)." Table)name, column_1, column_2, column_3, and data_type are all palceholders to be replaced with the correct able, columns, and data_types. The semicolon at the end indicates that it is the end of the query.

Explanation: The query above is indicating to SQL to create a new database titled database_name. As a data analyst, it is rate that you will be creating many databases, but it is valuable to have this query in your back pocket just in case! The semicolon at the end indicates that it is the end of the query.

Explanation: The query above is indicating to SQL to create a new table titled students with the CREATE TABLE keyword. Within this table, three columns named student_id, first_name, and last_name are created. Student_id contains a non-null integer data type, first-name contains a variable character data type of max length 45, and last_name contains a variable character data type of max length 45. Student_id is set as the primary key for the students table. The semicolon at the end indicates that it is the end of the query.

The query above is labeled with the key components used for deleting a table or a database. It is indicating to SQL to delete a table titled table_1 and a database titled database_name. DROP TABLE will delete a table and DROP DATABASE will delete a database. Table_1 and database_name are both placeholders to be replaced with the correct table and database names. The semicolon at the end indicates that it is the end of the query.

Explanation: The query above is labeled with the key components used for altering a table. It is indicating to SQL to change a table named table_1 by adding column_1 of data type INT. The command ALTER TSBLE allows for changes to be made to a table. ADDCOLUMN adds a column to a table. Table_1 and column_1 are both placeholders to be replaced with the correct table and column names. The semicolon at the end indicates that it is the end of the query.

Explanation: The query above is labeled with additional components used for altering a table. It is indicating to SQL to change a table named table_1 by changing the name of a column from old_column_name to new_column_name. CHANGE COLUMN allows for the data type and anme of a column to be changed. table_1, old_column_name, and new_column_name are placeholders to be replaced with the correct table, current column name, and new name for the column. The semicolon at the end indicates that it is the end of the query.

Explanation: The query above is labeled with the key components used for deleting the contents of a table. It is indicating to SQL to delete the contents of table_1. While DROP_TABLE removes the table and its contents entirely, TRUNCATE TABLE will only delete the contents of the table. Table_1 is a placeholder to be replaced with the correct table name and the semicolon at the end indicates that it is the end of the query.

Explanation: The four queries shown above show the four column constraints available in SQL. NOT NULL ensures that a column cannot have a null value, UNIQUE ensures all values in a column are different, CHECK ensures all values in a column satisfy a specific condition, and DEFAULT sets a default value for a column when no value is specified. The semicolon at the end indicates that it is the end of the query.

DML stands for Data Manipulation Language and refers to the set of SQL commands which add, modify, retrieve, delete, and update information in a database. SELCT, INSERT, UPDATE, and DELETE are the four commands that make up Data Manipulation Language. SELECT retrieves data from a database, INSERT adds data to a table, UPDATE changes existing data within a table, and DELETE removes rows from a table.

DCL stands for Data Control Language and refers to the set of SQL commands which control what users can do within a databased. GRANT and REVOKE are the two commands that make up Data Control Language. GRANT gives access or privileges to a database user and REVOKE removes specific access or privileges from a databased user.

TCL stands from Transaction Control Language and refers to the set of SQL commands which manage the changes users make to databases. Changes made to a database are not permanent until they are committed or a session is closed. SAVEPOINT, ROLLBACK, COMMIT, and SET TRANSACTION are the four commands that make up Transaction Control Language. SAVEPOINT saves transaction for a user so the user can rollback to that point if needed, ROLLBACK restores a database to the way it was before changes were made, COMMIT marks the end of a transaction and permanently saves the transaction into the databased, and SET TRANSACTION begins a database transaction and specifies distinctions of the transaction.

The query is indicating to SQL to return key information about the glam_bands table. The DESCRIBE keyword will return a table with Field, Type, Null, Key, Default, and Extra columns. The output of this query can be seen below. The semicolon at the end indicates that it is the end of the query.

The query is indicating to SQL to ALTER the glam_bands table by making the song and band columns primary keys. The ALTER TABLE keyword allows you to alter a table. The ADD PRIMARY KEY statement converts the columns provided between the parentheses --song and band --- to primary keys. The output of this query can be seen below. The semicolon at the end indicates that it is the end of the query.

The query is indicating to SQL to create a new view titled all_student_enrollments. After running this code, a view titled all_student_enrollments will appear in the "Views" drop-down menu. The semicolon at the end indicates that it is the end of the query.

The query is indicating to SQL to insert values value_1, value_2, value_3, value_4, value_5, value_6 into column_1, column_2, and column_3 in table_1. INSERT INTO is used to specify new VALUES to be added to a table. Table_1, column_1, column_2, column_3, value_1, value_2, value_3, value_4, value_5, and value_6 are all placeholders to be replaced with the correct table, columns, and values. The semicolon at the end indicates that it is the end of the query.

The query is indicating to SQL to insert the values 7 and "Human Resources" into the department_id and department columns in the department table. The semicolon at the end indicates that it is the end of the query.

The query indicating to SQL to set column_1 equal to value_1 and column_2 equal to value_2 when condition_1 is met in the table_1 table. UPDATE allows you to specify which table you want to update, SET allows you to set specific columns to new values, and WHERE allows you to filter which row(s) you want to update within the set columns. The semicolon at the end indicates that it is the end of the query.

The query is indicating to SQL to set the manager_name column equal to "Ben Blank" where the manager_id in the manger table is equal to 21. The semicolon at the end indicates that it is the end of the query.

The query is indicating to SQL to delete values from table_1 that meet condition_1. DELETE FROM is used to specify a table to delete values from. WHERE is optional, but if it is not included then all the rows in table_1 will be deleted. Table_1 and condition_1 are placeholders to be replaced with a certain table and optional condition. The semicolon at the end indicates that it is the end of the query.

The query is indicating to DWL to delete values from the db_admin table where the employee_id is equal to 1102024056, 1203032255, or 1110029732. The semicolon at the end indicates that it is the end of the query.

When performing the DELETE command in SQL, it is important to first set FOREIGN_KEY_CHECKS equal to 0. If FOREIGN_KEY_CHECKS is equal to 1, then it will return the error below.

Error Code: 1451. Cannot delete or update a parent row: a foreign key constraint fails.

string & date manipulation.pdf

The query is indicating to SQL to select the first_name column in all LOWER case and the last_name column in all UPPER case from the name_cases table. The result will be a table with all lower case first names in one column and all upper case last names in another column. The semicolon at the end indicates that it is the end of the query.

The query is indicating to SQL to select the first_name column and the last_name column from the name_cases table where the lower case of the first_name column equals "william." The semicolon at the end indicates that it is the end of the query.

The query is labeled with the different elements that make up the syntax for using CONCAT. It is indicating to SQL to join the values in the first_name and last_name columns from the employees table together with a space (" "). CONCAT stands for concatenation and is used to join multiple string values into a single column. The semicolon at the end indicates that it is the end of the query.

The query is indicating to SQL to concatenate the values in the first_name and last_name columns with a " " and return the length of those concatenated strings from the names2 table. The concatenated string is aliased as full_name and the length of the concatenated string is aliased as length. The semicolon at the end indicates that it is the end of the query.

The visual provides a powerful representation of what the trim function does. As shown, it removes extra white space that leads or trains a string.

The query is indicating to SQL to concatenate the values in the first_name and last_name columns with a " " and return the length of those concatenated strings from the names2 table. The values from the first_name and last_name columns are trimmed to eliminate leading and trailing white spaces. The concatenated string is aliased under full_name and the length of the concatenated string is aliased as length. The semicolon at the end indicates that it is the end of the query.

The query is indicating to SQL to select column_1 from table_1 where the values in column_1 are LIKE "%pattern_". The % tells SQL to search for values with zero, one, or multiple characters before "pattern" and tells SQL to search for results with a single character trailing "%pattern." Column1 and table_1 are placeholders for you to replace with column and table names. The semicolon at the end indicates that it is the end of the query.

The query is indicating to SQL to select the first_name and last_name columns from the employees table where the lower case values of the first_name column are LIKE "b%" and the lower case values of last_name are like "r%." The use of "b%" tells SQL to look for values that begin with b and are followed by zero, one, or multiple character; "r%" tells SQL to look for values that begin with r and are following by zero, one, or multiple characters. The semicolon at the end indicates that it is the end of the query.

The query is indicating to SQL to select all of the values from the grocery_inventory table where the product_name is like "%strawberry%" or "%strawberries%." "%strawberry%" tells SQL to look for values in the product_name table with zero, one, or multiple characters before strawberry and zero, one, or multiple characters trailing strawberry. "%strawberries%." tells SQL to look for values in the product_name table with zero,one, or multiple characters before strawberries and zero,one, or multiple characters trailing strawberries. The semicolon at the end indicates that it is the end of the query.

The query above is indicating to SQL to select a substring from the first_name column in the employees table starting at position 1 for a length of 1. This selection is aliased as first_initial. The SUBSTRING function allows you to grab a specific part of a string. The semicolon at the end indicates that it is the end of the query.

The query above is indicating to SQL to select the department_id column from the department table and alias the results as department. Within the department_id column, values of "Admin" are replaced with "Administration". The REPLACE function allows you to replace specific patterns with new patterns. The semicolon at the end indicates that it is the end of the query.

The query above is indicating to SQL to select the email column and the position of "@" in the email column from the employee_e table. The POSITION function will return an integer that represents the index value of the "@." The semicolon at the end indicates that it is the end of the query.

The query above is indicating to SQL to return the day of birth for each piece of datetime data in the date_of_birth column from the employees table. The DAY function will return one integer, eliminating the month, year, and date and returning only the day for each entry. The semicolon at the end indicates that it is the end of the query.

The query above is indicating to SQL to return the day of hire as day_of_hire, the month of hire as month_of_hire, and the year of hire as year_of_hire from the hire_date column in the employee_dates table. The DAY function will return one integer representing the day, the MONTH function will return one integer representing the month, and the YEAR function with return one integer representing the year. The semicolon at the end indicates that it is the end of the query.

The query above is indicating to SQL to return the difference in days between the hire_date and date_of_birth columns from the employee_dates table. The results are aliased as age_when_hired. The DATEDIFF function will return one integer representing the difference, in days, between two dates. The semicolon at the end indicates that it is the end of the query.