Coding Notes

Notes

• Why learn SQL?
  • One of the most popular languages for development and data science.
  • Used with MySQL, PostgreSQL, Microsoft SQL, and Oracle SQL.
  • Relatively easy to learn.
  • Second most popular language for professional developers according to 2024 Stack Overflow Survey
• Selecting Queries
• Select ALL
• SELECT * FROM tablename; will get all columns in tablename.
• A semi-colon at the end of a statement lets SQL know you have finished a full statement that is ready to run.
• * is a wild card operator meant to get every column/row.
• Selecting Columns
  • SELECT columnname1, columnname2, columnname3 FROM tablename; will return the tablenames data for just those three columns.
• When writing SQL it is not required that you use all caps, but it's nice and easier to read.
• Filtering Results
  • Filter results using the WHERE clause.
  • SELECT column1 from tablename WHERE column1 = 'string'; will display all rows in tablename where column1 is the string 'string'.
• SQL doesn't care about spacing or indentation, use it to format and make the code more human readable.
• Numerical Filtering
  • Show if rows column is greater than number by WHERE column1 > 3;.
  • Show if rows column is greater than or equal to number by WHERE column1 >= 3;.
• Not Equal
  • Show if rows column does not exactly equal value by WHERE column1 != 'value'.
  • Show if rows column does not exactly equal value by WHERE column1 <> 'value'.
• LIKE and NOT LIKE
  • Used for finding posible matches.
  • You can use the % wildcard operator like WHERE column1 LIKE '%string%'. This would return any row with column1 having 'string' in the string, like 'string', 'string-otherstuff', 'other stuff string', etc.
  • You can use the % wildcard operator like WHERE column1 NOT LIKE '%string%'. This would return any row with column1 not having 'string' in the string, like 'otherstuff', 'other-stuff', 'other stuff', etc.
  • You can use the _ wildcard operator like WHERE column1 LIKE 'string_'. This would return any row with column1 having 'string' in the string as well as a following character, like 'string1', 'stringb', etc.
  • You can use the _ wildcard operator like WHERE column1 NOT LIKE 'string_'. This would return any row with column1 not having 'string' in the string, like 'otherstuff', 'other-stuff', 'other stuff', etc.
• Wildcard Operators
  • % is used for any number of any characters.
  • _ is used for one of any characters.
• The AND Operator
  • WHERE column1 LIKE '%string%' AND column2 NOT LIKE 'string_'; would return any rows with column one having 'string' with any other characters around it and column2 with 'string' and any single character after it.
  • You can add infinate ANDs, just don't put the semi-colon till the end of the last one.
• BETWEEN
  • BETWEEN gets all cells from a column that fall between two integers, inclusive.
  • WHERE column1 BETWEEN 1 AND 10; would return all rows with cells in column one that are 1, 2, 3, 4, 5, 6, 7, 8, 9, or 10.
• The OR Operator
  • Return one one of the conditions is true.
  • WHERE column1 = 1 OR column2 = 2; would show any rows with the column1 cell equaling 1 or any rows with column2 cell equaling 2.
  • If AND comes after OR the AND is a part of the OR.
  • WHERE column1 = 1 OR column2 = 2 AND column3 = 3; would show ay rows with 1 in the column1 cell or any rows with 2 in the column2 cell and 3 in the column3 cell.
  • You can surround a statement in parenthasis to group them together and separate them from any statements outside the parenthasis.
  • WHERE (column1 = 1 OR column2 = 2) AND column3 = 3; would show any rows with 3 in the column3 cell and either 1 in the column1 cell or 2 in the column2 cell.
• If you are going to reference a column in the WHERE you do not need to include it in the SELECT.
• The IN Operator
  • Used to select results that are within a set of values.
  • WHERE column1 IN ('string1', 'string2', 'string3'); would show any rows that have column one equal to 'string1', 'string2', or 'string3'.
  • Kind of like having multiple OR clauses without writing all that.
• The IS Operator
  • WHERE column1 IS false; would only display rows with the cell in column1 being false.
  • It checks if a value is null.
• ORDER BY
  • ORDER BY column1
  • If the column is a string it will order in ascending order, if it is numberical it will order it is also ascending.
  • ORDER BY column1 DESC will order by column1 in descending order.
  • ORDER BY column1 DESC, column2 will first order by column1 in descending order than column2 in decending order.
  • ORDER BY column1 DESC, column2 ASC; will order by column1 in descending order than column2 in ascending order.
• LIMIT
  • LIMIT 1; would limit your results to one row.
• SELECT column1, column2, column3, column4 FROM tablename WHERE column1 LIKE '%string%' AND column2 IS false ORDER BY column3 LIMIT 5; would show top five rows in ascending order of column3 with 'string' in their cell at column1 and with column2 as false.
• COUNT and SUM
  • Allows us to turn the values within a single column from multiple values into one single value.
  • COUNT: SELECT COUNT(*) FROM tablename WHERE column1 IS true; returns the number of rows in tablename where column1 is true.
  • SUM: SELECT SUM(column1) FROM tablename returns the total sum of all cells in column1.
  • MAX: SELECT MAX(column1) FROM tablename returns the max value from column1.
  • MIN: SELECT MIN(column1) FROM tablename returns the min value from column1.
  • AVG: SELECT AVG(column1) FROM tablename returns the average of all values from column1 with lots of decimals. You can then use CEIL or FLOOR to get rid of the decimals.
• You can use an alias to rename the column so SELECT COUNT(*) AS alias_name FROM tablename would return a table with the column name alias_name and a cell with the total count of rows in tablename.
• CEIL and FLOOR
  • SELECT FLOOR(AVG(column1)) FROM tablename will get the average of column1 and round down to the nearest whole number.
  • SELECT CEIL(AVG(column1)) FROM tablename will get the average of column1 and round up to the nearest whole number.
• GROUP BY
  • Used for more complex aggregates.
  • SELECT column1, COUNT(column1) FROM tablename GROUP BY column1; will return two columns, column1 and the total number of times each value had been used in column1 in a count column.
• HAVING
  • Allows us to write conditions which use aggregations using an aggregate column in a condition or through an error.
  • HAVING count(column1) > 1; returns only rows where the count from column1 is greater than 1.
• Databases are dynamic resources, we can add modify and delete data.
  • These operations are known as Data Manipulation Language (DML) or CRUD Commands.
• Returning Data
  • DML Commands don't return data from the database.
• INSERT INTO
  • INSERT INTO tablename (column1, column2, column3) VALUES ('string1', 1, FALSE), ('string2', 2, TRUE);
• UPDATE
  • Used to update previously populated data.
  • UPDATE tablename SET column1 = TRUE WHERE column2 LIKE 'string'; updates any rows with column2 containing 'string' to have TRUE in column1.
  • UPDATE tablename SET column1 = TRUE, column3 = 1 WHERE column2 LIKE 'string'; updates any rows with column2 containing 'string' to have TRUE in column1 and 1 in column3.
• DELETE
  • Removes records from the table.
  • DELETE FROM tablename WHERE column1 = 2; deletes all rows that have 2 in column1.