Common SQL Clauses

When attempting to access and manipulate a database, SQL (Structured Query Language) is one of the top language options to use. SQL code revolve around queries, which are requests or commands for some part of the database. Once you get the hang of the query syntax, it can be very intuitive to use. Here are some of the more common SQL clauses.

SELECT and FROM

Simply put, SELECT indicates what exactly you want to pull from the database. FROM points to the specific database that you are pulling from. For example, if you wanted to retrieve a customer ID from the "customers" table, it may look like this:

SELECT customer_id FROM Customer

Something to keep in mind is the asterisk (). This character represents all records in the data. In this case, if you wanted everything, and not just the customer ID, you would replace "customer_id" with "" in the above example.

WHERE

WHERE is a clause that allows the user to filter the data under a specific condition. One thing to note is that WHERE can only be used on ungrouped or unaggregated data. In the next example, let's say we want the customer ID's of all customers who ordered 30 or more units.

SELECT customer_ID
FROM Customer
WHERE Order_Quantity > 30

GROUP BY

GROUP BY does exactly what its name indicates. It groups the data by a certain feature. For example, a user who wants customer ID's grouped into the states they live in would use:

SELECT customer_ID
FROM Customer
GROUP BY State

HAVING

HAVING works similarly to WHERE, the difference being that HAVING is used on aggregated data (most commonly after a GROUP BY). Below is an example of when HAVING would be used. Here, the user is searching for all states that have a total of more than 250 orders. In order to do this, we GROUP BY State and sum all the orders for each customer in that state.

SELECT SUM(Orders) as total
FROM Customer
GROUP BY State
HAVING total > 250

ORDER BY

ORDER BY sorts the data on a given feature. One thing to note is that the default sorting is in ascending order, but there are optional ASC/DESC parameters that give the user the option to control the order. Here's an example of getting customer ID's after ordering customer last names in descending order

SELECT customer_id
FROM Customer
ORDER BY last_name

The above clauses are some of the most commonly used ones and are crucial to know when using SQL.