Useful SQL Commands with Examples

So you’re harnessing the power of using SQL. Although it’s one of the easier programming languages to understand, it still holds some complexities.

Below we will go through some useful SQL commands that you can use as a reference guide, like the bread and butter for your SQL projects. We will be using a dataset which contains information on New York Cities Airbnb listings available on Kaggle.

A. Data Definition Language (DDL)

By using DDL we can change the structure of our tables. Since all the command of DDL are auto committed it permanently saves all the changes in the database.

1. CREATE

Let's suppose you want to create a table with information about the type of rooms in the listings. The command below allows you to create a new database or table.

1CREATE TABLE room_details(
2  room_type TEXT, 
3  room_size INTEGER
4);

2. ALTER

Now let’s say you want to add a column to the airbnb_listings table which states if the property is reserved or not. The next command is used to modify (add, drop, rename, etc) the structure of the data in your database, it should be noted the data will remain unchanged.

1ALTER TABLE airbnb_listings
2ADD listings_reserved BOOLEAN;

3. DROP

So you’ve decided that the table you created no longer serves a purpose and you want to delete it. The DROP command deletes a database or table. Before running this command you should be aware that it will delete your whole table, including all data, indexes, and more, so make sure you are certain before you run.

1DROP TABLE room_details;

To delete a specific column we can combine the ALTER TABLE command with the DROP command.

1ALTER TABLE airbnb_listings 
2DROP COLUMN listings_reserved;

4. TRUNCATE

This command is used to remove all data entries from a table in a database while keeping the table and structure in place.

1TRUNCATE TABLE airbnb_listings;  

B. Data Manipulation Language (DML)

By using DML we can modify, retrieve, delete and update the data in our database.

1. INSERT

Suppose we have a new listing we would like to add to our database, to add this new record we use the INSERT INTO command. This command allows you to add one or more rows.

1INSERT INTO airbnb_listings(id, name, host_id, neighbourhood_group, price) 
2VALUES (1001, 'Luxury Villa', 2345, 'Brooklyn', 225);

2. DELETE

The new listings pulls out last minute so we need to remove the data from our table. To remove data we can simply use the DELETE command based on conditions specified with the WHERE command.

1DELETE FROM airbnb_listings
2WHERE id = 1001;

3. UPDATE

Imagine you need to update data in your table because one listing increased their price. The UPDATE command allows you to do this based on conditions specified after the WHERE command.

1UPDATE airbnb_listings
2set price = 200
3WHERE id = 2539;

C. Transaction Control Language (TCL)

TCL commands are used to maintain consistency of our databases and for management of transaction made by DML commands. We can only use TCL commands with DML commands like INSERT, DELETE and UPDATE.

1. SAVEPOINT

The SAVEPOINT command allows us to pick a point in a transaction and save it so that we can roll back to it. This is similar to how you can backup your pictures to the cloud or you save a backup of an important project.

1SAVEPOINT SAVEPOINT_NAME;

2. COMMIT

By using the COMMIT statement we end the current transaction removing any existing savepoints that may be in use and make permanent all changes preformed in the transaction. Once the statement is run, we cannot roll back the transaction.

1DELETE FROM airbnb_listings
2WHERE id = 1001;
3COMMIT

3. ROLLBACK

To undo a transaction that are not saved to the database we use the ROLLBACK command. This can only be used to undo transactions that has came after the last COMMIT or ROLLBACK command that was ran. You can also rollback to a SAVEPOINT that has been created before.

1ROLLBACK TO SAVEPOINT_NAME;

D. Data Control Language (DCL)

By using DCL we can permit a user to access, modify or work on the different privileges in order to control the database.

1. GRANT

Suppose you hire a freelancer to update your airbnb_listings database with new information. By using the GRANT command you can give the user access to database objects such as tables, views or the database itself. The below example gives the user named ‘usr_maria’ SELECT and UPDATE access on the airbnb_listings table.

1GRANT SELECT, UPDATE ON airbnb_listings TO usr_maria;

2. REVOKE

After the freelancer has completed her work you now want to remove the users permission. You can do so by using the REVOKE command.

1REVOKE SELECT, UPDATE ON airbnb_listings FROM usr_maria;

E. Data Query Language (DQL)

By using DQL we can fetch data from the database.

1. SELECT

You use the SELECT command almost every time you query data with SQL. It allows you to define what data you want your query to return. By using the SELECT command with an asterisk () all of the columns in the table airbnb_listings* are returned.

1SELECT * FROM airbnb_listings;

Using the following command we can find out all the neighborhoods we have listings in.

1SELECT neighbourhood_group
2FROM airbnb_listings;

However you will notice there are duplicate results. To overcome this we use the SELECT DISTINCT command which will eliminate duplicates and only return distinct data.

1SELECT DISTINCT neighbourhood_group
2FROM airbnb_listings;

F. Aggregate Functions

An aggregate function is used to combine a set of values and return a single result.

1. COUNT

We want to find out the number of Airbnb listings we have. To do so we use the COUNT() function which will add up the number of rows where the specified column is not NULL.

1SELECT COUNT()
2FROM airbnb_listings;

2. SUM

Let’s say we want to find the total number_of_reviews left on our listings. Using the SUM function we can return the total sum of a numeric column.

1SELECT SUM(number_of_reviews) 
2FROM airbnb_listings; 

3. AVERAGE

Let’s say you want to see what the average price is across your listings is. By using the AVG function the average value of the numeric column will be returned.

1SELECT AVG(price) 
2FROM airbnb_listings;

4. MAXIMUM & MINIMUM

To find what is the maximum and minimum price of our listings we use the MIN and MAX functions.

1 --  Max
2SELECT MAX(price) 
3FROM airbnb_listings;
4
5 --  Min
6SELECT MIN(price) 
7FROM airbnb_listings;

5. GROUP BY

Let’s say we want to find the average price for each neighborhood. We can do this using the GROUP BY statement. It groups rows with the same values into summary rows and is commonly used with aggregate functions like AVG.

1SELECT neighbourhood, AVG(price)
2FROM airbnb_listings
3GROUP BY neighbourhood;

6. HAVING

HAVING acts in the same way as the WHERE clause with the difference being that HAVING is used for aggregate functions. Let’s suppose want to return the names of the clients who have more than 2 listings on Airbnb.

1SELECT COUNT(host_id), name
2FROM airbnb_listings
3GROUP BY host_name
4HAVING COUNT(host_id) > 2;

7. ORDER BY

To order the results obtained from a query we use the ORDER BY command. By default the order will be ascending (A -> Z / 1 -> 100) however we can change this using DESC. Let’s say for example we want to order our listings by price.

1SELECT name, price
2FROM airbnb_listings
3ORDER BY price;
4
5 -- In descending order
6SELECT name, price
7FROM airbnb_listings
8ORDER BY price DESC;

G. Filtering Data

One of the most powerful feature of using SQL is the ability to rapidly filter data to match a certain criteria you are searching for.

1. WHERE

By using the WHERE filters with a query it allows us to only return results that match a set condition. We can use this together with conditional operators like =, >, <, >=, <=, etc.

1SELECT *
2FROM airbnb_listings 
3WHERE price < 300;

2. AND

We can combine two or more conditions into a single query by using the AND command. All of the conditions must be met for the result to be returned. Let’s say we want results for listings over $300 in Brooklyn.

1SELECT name
2FROM airbnb_listings
3WHERE price < 300 AND neighbourhood_group = 'Brooklyn';

3. OR

We can combine two or more conditions into a single query by using the OR command. It differs from AND in the sense that only one of the conditions must be met for a result to be returned. Therefore from our example all listings priced over $300 will be returned as well as all listings located in Brooklyn.

1SELECT name
2FROM airbnb_listings
3WHERE price < 300 OR neighbourhood_group = 'Brooklyn';

4. BETWEEN

We can specify a range using the BETWEEN filter. For example creating a price range for listings as follows.

1SELECT name, price
2FROM airbnb_listings
3WHERE price BETWEEN 200 AND 400; 

5. LIKE

We can search for patterns within our data using the LIKE command. Suppose you want to find listings with the word 'country' in the title, the code below will achieve this.

1SELECT name
2FROM airbnb_listings
3WHERE name LIKE ‘%country%’;

Other operators for LIKE:

• %x — will select all values that begin with x
• %x% — will select all values that include x
• x% — will select all values that end with x
• x%y — will select all values that begin with x and end with y
• _x% — will select all values have x as the second character
• x% — will select all values that begin with x and are at least two characters long. You can add additional characters to extend the length requirement, i.e. x___%

6. IN

By using the IN command we can specify multiple values we want to select from with the WHERE command.

1SELECT name, neighbourhood_group, price
2FROM airbnb_listings
3WHERE neighbourhood_group IN ('Brooklyn', 'Manhattan');

7. IS NULL & IS NOT NULL

IS NULL will return only rows with a NULL value, while IS NOT NULL does the opposite returning only rows without a NULL value. Let’s say we want to find listings which have received no reviews in the past month.

1-- IS NULL
2SELECT name
3FROM airbnb_listings
4WHERE reviews_per_month IS NULL;
5
6-- IS NOT NULL
7SELECT name
8FROM airbnb_listings
9WHERE reviews_per_month IS NOT NULL;

H. Combining Data

So you want to combine your table with another. Let's first take a look at joins to accomplish this goal. A JOIN clause is used to combine rows from two or more tables allowing you to fetch combined results.

For this example let's imagine we're joining with another table called 'booking_listings' which contains listings from the website Booking.com.

1. INNER JOIN

INNER JOIN selects records that have matching values in both tables.

1SELECT name
2FROM airbnb_listings
3INNER JOIN booking_listings
4ON airbnb_listings.host_id = booking_listings.host_id;

2. LEFT JOIN

LEFT JOIN selects records from the left table that match records in the right table. In the below example the left table is airbnb_listings.

1SELECT name
2FROM airbnb_listings
3LEFT JOIN booking_listings
4ON airbnb_listings.host_id = booking_listings.host_id;

3. RIGHT JOIN

RIGHT JOIN selects records from the right table that match records in the left table. In the below example the right table is booking_listings.

1SELECT name
2FROM airbnb_listings
3RIGHT JOIN booking_listings
4ON airbnb_listings.host_id = booking_listings.host_id;

4. FULL JOIN

FULL JOIN selects records that have a match in the left or right table. Think of it as the “OR” JOIN compared with the “AND” JOIN (INNER JOIN).

1SELECT name
2FROM airbnb_listings
3FULL OUTER JOIN booking_listings
4ON airbnb_listings.host_id = booking_listings.host_id;

5. UNION

Union on the other hand allows you to append rows to each other. Unlike joins which append matching columns, union can append unrelated rows provided they have the same number and name of columns.

1SELECT price, name FROM  airbnb_listings
2UNION 
3SELECT price, name FROM booking_listings;

You can think of union as a way of combining the results of two queries. You can use the UNION ALL syntax to return all the data, regardless of duplicates.

1SELECT price, name FROM  airbnb_listings
2UNION ALL
3SELECT price, name FROM booking_listings;

Conclusion

To master the SQL language having knowledge of SQL commands is necessary. With the previously discussed commands you should be well on your way to becoming an SQL expert!

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.