One Pager Cheat Sheet

  • A database is a system used to save and process data in an efficient manner, usually stored in a tabular format to insert and manipulate information.
  • SQL is a global standard structured query language, which is used to perform operations on relational databases like inserting, deleting, and updating, and is considered intuitive and easy-to-use.
  • The SQL statement CREATE TABLE is used to create a new table in a database, specifying a mandatory table_name and the optional columns with their datatypes.
  • Using the CREATE TABLE statement, we can create a new table or derive one from an existing table in an existing database.
  • We can create a new table called Class containing "studies" information by passing column names and their corresponding data types to a query.
  • SQL DESC and DESCRIBE are alias statements used to list information about a table, such as key attributes and data types.
  • DESCRIBE is a helpful SQL statement for seeing information about a table, such as column names, data types and null value presence.
  • The SELECT statement is used to query a table for specific data, returning a result-set of its associated columns and properties.
  • The SELECT statement in SQL is an intuitive way to retrieve columns from a given table.
  • The statement SELECT * will extract all columns from a table.
  • INSERT, UPDATE, and DELETE are SQL statements used to manipulate values stored in a table.
  • To update or delete records from a table in a database, you can use UPDATE or DELETE SQL statements respectively.
  • The BETWEEN operator is used with the WHERE clause to select values within a specified range and is not valid without it.
  • We can use the BETWEEN operator to perform an operation on a specified range, by combining it with the SELECT/ FROM and WHERE statements.
  • The order of operations in the WHERE clause is determined by the order of precedence which consists of relational operators (>=, >, <=, <) followed by the OR operator, and finally the equality checks (= and !=).
  • The order of operations within the WHERE clause can be enforced with parentheses (()) to selectively filter data using the SELECT/ FROM statement.
  • You can find students whose attendance count is greater than 80 in English by using logical operators.
  • The WHERE clause works on individual records, while theHAVING clause works on grouped data and allows us to use aggregate functions.
  • We group the data and then use the HAVING clause to apply the condition.
  • Aggregate functions, such as AVG(), return a single value calculated from a set of input values.
  • Aggregate functions are used to compute and return a single value from multiple values in a SQL database, such as MIN(), MAX(), SUM(), AVG(), and COUNT().
  • The MIN() function is used to find the row with the minimum value of a column in a query.
  • The MAX() function is used to find the maximum value of a column.
  • The SUM() function can be used to find the total amount of values from a column.
  • The AVG() function is used to calculate the mean value of a column.
  • The COUNT() function is used to find the number of rows for a given column, not ignoring null values by default.
  • The JOIN clause is used to join data from multiple tables based on their common columns, forming relationships and returning a single result set.
  • The JOIN clause is used in SQL to combine two or more tables based on a mutual column, and can be executed with four types of joins: INNER, RIGHT OUTER, LEFT OUTER, and FULL OUTER.