One Pager Cheat Sheet

• Learn the essentialSQLcommands including SELECT, FROM, WHERE, and more to effectively work withNew York City Airbnbdata.
• A Data Definition Language (DDL) can be used to create, alter, drop, and truncate tables in the database, permanently changing their structure.
• The ALTER command can be used to add new columns, such as size_square_metres, to a database table.
• Through the use of Data Manipulation Language (DML), we can modify, retrieve, delete or update the data in our database.
• The UPDATE command can be used to modify data in a database by specifying WHERE conditions, such as changing the name of a listing from 'Clean & quiet apt home by the park' to 'Luxurious Apt. facing Kensington's Most Famous Park'.
• TCL commands are used to manage DML operations and ensure consistency of the database using SAVEPOINT, COMMIT, and ROLLBACK statements.
• The ROLLBACK command is used to undo a transaction that has not been saved to the database and to return to a previously created SAVEPOINT.
• Using Data Control Language (DCL) commands like GRANT and REVOKE, we can control a user's access and privileges to our database objects.
• The REVOKE command is used to remove access previously given to a user from database objects, like tables and views.
• By using SELECT and SELECT DISTINCT, the DQL (Data Query Language) allows us to query data from the database and get distinct data as required.
• No, the command SELECT (*) does not remove duplicates, rather it returns all columns in the query; to remove duplicates, SELECT DISTINCT should be used.
• Aggregate functions such as COUNT, SUM, AVG, MAX, MIN, GROUP BY and HAVING are used to process data and generate results based on a given criteria, and can be ordered using ORDER BY.
• The COUNT() function is used to determine the total number of records in a given table, and is an important aggregate function for quickly gauging the size of a dataset.
• SQL provides powerful querying capabilities to filter data using WHERE, AND, OR, BETWEEN, LIKE, IN, IS NULL and IS NOT NULL filters.
• We can use the BETWEEN command to return data from our query that is within a certain range, in this case listings with a 'minimum_nights' requirement of between 7 and 14 days.
• With INNER JOIN, LEFT JOIN, RIGHT JOIN, FULL JOIN and UNION, you can combine rows from two or more tables, either appending matching columns or appending all rows with the same number and name of columns.
• The SQL command INNER JOIN selects records with matching values in two or more tables to combine rows and return matching records.
• With knowledge of the essential SQL commands, you can master the SQL language and become an SQL expert.