An Introductory SQL Tutorial: How to Write Simple Queries
Published: March 21, 2022
How to Query a SQL Database:
- Make sure that you have a database management application (ex. MySQL Workbench, Sequel Pro).
- If not, download a database management application and work with your company to connect your database.
- Understand your database and its hierarhcy.
- Find out which fields are in your tables.
- Begin writing a SQL query to pull your desired data.
Ever heard of SQL? You may have heard about it in the context of data analysis, but never thought it would apply to you as a marketer. Or, you may have thought, “That's for the advanced data users. I could never do that.”
Well, you couldn't be more wrong! The most successful marketers are data-driven, and one of the most important parts of being data-driven is collecting data from databases quickly. SQL is the most popular tool out there for doing just that.
If your company already stores data in a database, you may need to learn SQL to access the data. But not to worry — you're in the right place to get started. Let's jump right in.
Why Use SQL?
SQL (often pronounced like “sequel”) stands for Structured Query Language, and it's used when companies have a ton of data that they want to manipulate. The beauty of SQL is that anyone working at a company that stores data in a relational database can use it. (And chances are, yours does.)
For example, if you work for a software company and want to pull usage data on your customers, you can do that with SQL. If you’re helping develop a website for an ecommerce company that has data about customer purchases, you can use SQL to find out which customers are purchasing which products. Of course, these are just a few of many possible applications.
Think about it this way: Have you ever opened a very large data set in Excel, only for your computer to freeze or even shut down? SQL allows you to access only certain parts of your data at a time so you don't have to download all the data into a CSV, manipulate it, and possibly overload Excel. In other words, SQL takes care of the data analysis that you may be used to doing in Excel.
How to Write Simple SQL Queries
Before we begin, make sure you have a database management application that will allow you to pull data from your database. Some options include MySQL or Sequel Pro .
Start by downloading one of these options, then talk to your company’s IT department about how to connect to your database. The option you choose will depend on your product's back end , so check with your product team to make sure you select the correct one.
Understand the hierarchy of your database
Next, it's important to become accustomed to your database and its hierarchy. If you have multiple databases of data, you'll need to hone in on the location of the data you want to work with.
For example, let's pretend we're working with multiple databases about people in the United States. Enter the query “SHOW DATABASES;”. The results may show that you have a couple of databases for different locations, including one for New England.
Within your database, you'll have different tables containing the data you want to work with. Using the same example above, let's say we want to find out which information is contained in one of the databases. If we use the query “SHOW TABLES in NewEngland;”, we'll find that we have tables for each state in New England: people_connecticut, people_maine, people_massachusetts, people_newhampshire, people_rhodeisland, and people_vermont.
Finally, you need to find out which fields are in the tables. Fields are the specific pieces of data that you can pull from your database. For example, if you want to pull someone's address, the field name may not just be “address” — it may be separated into address_city, address_state, address_zip. In order to figure this out, use the query “Describe people_massachusetts;”. This provides a list of all of the data that you can pull using SQL.
Let's do a quick review of the hierarchy using our New England example:
- Our database is: NewEngland.
- Our tables within that database are: people_connecticut, people_maine, people_massachusetts, people_newhampshire, people_rhodeisland, and people_vermont.
- Our fields within the people_massachusetts table include: address_city, address_state, address_zip, hair_color, age, first_name, and last_name.
Now, let’s write some simple SQL queries to pull data from our NewEngland database.
Basic SQL Queries
To learn how to write a SQL query, let's use the following example:
Who are the people who have red hair in Massachusetts and were born in 2003 organized in alphabetical order?
SELECT chooses the fields that you want displayed in your chart. This is the specific piece of information that you want to pull from your database. In the example above, we want to find the people who fit the rest of the criteria.
Here is our SQL query:
FROM pinpoints the table that you want to pull the data from. In the earlier section, we learned that there were six tables for each of the six states in New England: people_connecticut, people_maine, people_massachusetts, people_newhampshire, people_rhodeisland, and people_vermont. Because we're looking for people in Massachusetts specifically, we'll pull data from that specific table.
WHERE allows you to filter a query to be more specific. In our example, we want to filter our query to include only people with red hair who were born in 2003. Let's start with the red hair filter.
hair_color = 'red'
hair_color could have been part of your initial SELECT statement if you'd wanted to look at all of the people in Massachusetts along with their hair color. But if you want to filter to see only people with red hair, you can do so with a WHERE statement.
Besides equals (=), BETWEEN is another operator you can use for conditional queries. A BETWEEN statement is true for values that fall between the specified minimum and maximum values.
In our case, we can use BETWEEN to pull records from a specific year, like 2003. Here’s the query:
birth_date BETWEEN '2003-01-01' AND '2003-12-31'
AND allows you to add additional criteria to your WHERE statement. Remember, we want to filter by people who had red hair in addition to people who were born in 2003. Since our WHERE statement is taken up by the red hair criteria, how can we filter by a specific year of birth as well?
That's where the AND statement comes in. In this case, the AND statement is a date property — but it doesn't necessarily have to be. (Note: Check the format of your dates with your product team to make sure they are in the correct format.)
OR can also be used with a WHERE statement. With AND, both conditions must be true to appear in results (e.g., hair color must be red and must be born in 2003). With OR, either condition must be true to appear in results (e.g., hair color must be red or must be born in 2003).
Here’s what an OR statement looks like in action:
hair_color = ‘red’
NOT is used in a WHERE statement to display values in which the specified condition is untrue. If we wanted to pull up all Massachusetts residents without red hair, we can use the following query:
Calculations and organization also can be done within a query. That's where the ORDER BY and GROUP BY functions come in. First, we'll look at our SQL queries with the ORDER BY and then GROUP BY functions. Then, we'll take a brief look at the difference between the two.
An ORDER BY clause allows you to sort by any of the fields that you have specified in the SELECT statement. In this case, let's order by last name.
GROUP BY is similar to ORDER BY, but aggregates data that has similarities. For example, if you have any duplicates in your data, you can use GROUP BY to count the number of duplicates in your fields.
Here is your SQL query:
ORDER BY VS. GROUP BY
To show the difference between an ORDER BY statement and a GROUP BY statement, let's step outside our Massachusetts example briefly to look at a very simple dataset. Below is a list of four employees' ID numbers and names.
If we were to use an ORDER BY statement on this list, the names of the employees would get sorted in alphabetical order. The result would look like this:
If we were to use a GROUP BY statement instead, the employees would be counted based on the number of times they appeared in the initial table. Note that Peter appeared twice in the initial table, so the result would look like this:
With me so far? Okay, let's return to the SQL query we've been creating about red-haired people in Massachusetts who were born in 2003.
Depending on the amount of data you have in your database, it may take a long time to run your queries. This can be frustrating, especially if you’ve made an error in your query and now need to wait before continuing. If you want to test a query, the LIMIT function lets you limit the number of results you get.
For example, if we suspect there are thousands of people who have red hair in Massachusetts, we may want to test out our query using LIMIT before we run it in full to make sure we're getting the information we want. Let's say, for instance, we only want to see the first 100 people in our result.
In addition to retrieving information from a relational database, SQL can also be used to modify the contents of a database. Of course, you’ll need permissions to make changes to your company’s data. But, in case you’re ever in charge of managing the contents of a database, we’ll share some queries you should know.
First is the INSERT INTO statement, which is for putting new values into your database. If we want to add a new person to the Massachusetts table, we can do so by first providing the name of the table we want to modify, and the fields within the table we want to add to. Next, we write VALUE with each respective value we want to add.
Here’s what that query could look like:
people_massachusetts (address_city, address_state, address_zip, hair_color, age, first_name, last_name)
(Cambridge, Massachusetts, 02139, blonde, 32, Jane, Doe)
Alternatively, if you are adding a value to every field in the table, you don’t need to specify fields. The values will be added to columns in the order that they are listed in the query.
If you only want to add values to specific fields, you must specify these fields. Say we only want to insert a record with first_name, last_name, and address_state — we can use the following query:
people_massachusetts (first_name, last_name, address_state)
(Jane, Doe, Massachusetts)
If you want to replace existing values in your database with different values, you can use UPDATE. What if, for example, someone is recorded in the database as having red hair when they actually have brown hair? We can update this record with UPDATE and WHERE statements:
hair_color = ‘brown’
first_name = ‘Jane’
last_name = ‘Doe’
Or, say there’s a problem in your table where some values for “address_state” appear as “Massachusetts” and others appear as “MA”. To change all instances of “MA” to “Massachusetts” we can use a simple query and update multiple records at once:
address_state = ‘Massachusetts’
address_state = MA
Be careful when using UPDATE. If you don’t specify which records to change with a WHERE statement, you’ll change all values in the table.
DELETE removes records from your table. Like with UPDATE, be sure to include a WHERE statement, so you don’t accidentally delete your entire table.
Or, if we happened to find several records in our people_massachusetts table who actually lived in Maine, we can delete these entries quickly by targeting the address_state field, like so:
address_state = ‘maine’
Bonus: Advanced SQL Tips
Now that you’ve learned how to create a simple SQL query, let's discuss some other tricks that you can use to take your queries up a notch, starting with the asterisk.
When you add an asterisk character to your SQL query, it tells the query that you want to include all the columns of data in your results.
In the Massachusetts example we've been using, we've only had two column names: first_name and last_name. But let's say we had 15 columns of data that we want to see in our results — it would be a pain to type all 15 column names in the SELECT statement. Instead, if you replace the names of those columns with an asterisk, the query will know to pull all of the columns into the results.
Here's what the SQL query would look like:
% (percent symbol)
The percent symbol is a wildcard character, meaning it can represent one or more characters in a database value. Wildcard characters are helpful for locating records that share common characters. They are typically used with the LIKE operator to find a pattern in the data.
For instance, if we wanted to get the names of every person in our table whose zip code begins with “02”, we can write this query:
address_zip LIKE '02%'
Here, “%” stands in for any group of digits that follow “02”, so this query turns up any record with a value for address_zip that begins with “02”.
LAST 30 DAYS
Once I started using SQL regularly, I found that one of my go-to queries involved trying to find which people took an action or fulfilled a certain set of criteria within the last 30 days.
Let's pretend today is December 1, 2021. You could create these parameters by making the birth_date span between November 1, 2021 and November 30, 2021. That SQL query would look like this:
birth_date BETWEEN '2021-11-01' AND '2021-11-30'
But, that would require thinking about which dates cover the last 30 days, and you'd have to update this query constantly.
Instead, to make the dates automatically span the last 30 days no matter which day it is, you can type this under AND: birth_date >= (DATE_SUB(CURDATE(),INTERVAL 30))
(Note: You'll want to double-check this syntax with your product team because it may differ based on the software you use to pull your SQL queries.)
Your full SQL query would therefore look like this:
birth_date >= (DATE_SUB(CURDATE(),INTERVAL 30))
In some cases, you may want to count the number of times that a criterion of a field appears. For example, let's say you want to count the number of times the different hair colors appear for the people you are tallying up from Massachusetts. In this case, COUNT will come in handy so you don't have to manually add up the number of people who have different hair colors or export that information to Excel.
Here's what that SQL query would look like:
AVG calculates the average of an attribute in the results of your query, excluding NULL values (empty). In our example, we could use AVG to calculate the average age of Massachusetts residents in our query.
Here’s what our SQL query could look like:
SUM is another simple calculation you can do in SQL. It calculates the total value of all attributes from your query. So, if we wanted to add up all the ages of Massachusetts residents, we can use this query:
MIN and MAX
MIN and MAX are two SQL functions that give you the smallest and largest values of a given field. We can use it to identify the oldest and youngest members of our Massachusetts table:
This query will give us the record of the oldest:
And this query gives us the oldest:
There may be a time when you need to access information from two different tables in one SQL query. In SQL, you can use a JOIN clause to do this.
(For those familiar with Excel formulas , this is similar to using the VLOOKUP formula when you need to combine information from two different sheets in Excel.)
Let's say we have one table that has data of all Massachusetts residents' user IDs and their birthdates. In addition, we have an entirely separate table containing all Massachusetts residents' user IDs and their hair color.
If we want to figure out the hair color of Massachusetts residents born in the year 2003, we'd need to access information from both tables and combine them. This works because both tables share a matching column: user IDs.
Because we're calling out fields from two different tables, our SELECT statement is also going to change slightly. Instead of just listing out the fields we want to include in our results, we'll need to specify which table they're coming from. (Note: The asterisk function may come in handy here so your query includes both tables in your results.)
To specify a field from a specific table, all we have to do is combine the name of the table with the name of the field. For example, our SELECT statement would say “table.field” — with the period separating the table name and the field name.
We're also assuming a few things in this case:
- The Massachusetts birthdate table includes the following fields: first_name, last_name, user_id, birthdate
- The Massachusetts hair color table includes the following fields: user_id, hair_color
Your SQL query would therefore look like:
birthdate_massachusetts JOIN haircolor_massachusetts USING (user_id)
This query would join the two tables using the field “user_id” which appears in both the birthdate_massachusetts table and the haircolor_massachusetts table. You’re then able to see a table of people born in 2003 who have red hair.
Use a CASE statement when you want to return different results to your query based on which condition is met. Conditions are evaluated in order. Once a condition is met, the corresponding result is returned and all following conditions are skipped over.
You can include an ELSE condition at the end in case no conditions are met. Without an ELSE, the query will return NULL if no conditions are met.
Here’s an example of using CASE to return a string based on the query:
WHEN hair_color = ‘brown’ THEN ‘This person has brown hair.’
WHEN hair_color = ‘blonde’ THEN ‘This person has blonde hair.’
WHEN hair_color = ‘red’ THEN ‘This person has red hair.’
ELSE ‘Hair color not known.’
Basic SQL Queries Marketers Should Know
Congratulations. you're ready to run your own SQL queries! While there's a lot more you can do with SQL, I hope you found this overview of the basics helpful so you can get your hands dirty. With a strong foundation of the basics, you'll be able to navigate SQL better and work toward some of the more complex examples.
Editor's note: This post was originally published in March 2015 and has been updated for comprehensiveness.
Don't forget to share this post!
6 Best Free Website Builders to Check Out in 2023 [+Pros & Cons]
The 10 Best Content Management Software Tools in 2023
12 of the Best Programming Languages to Learn in 2022
The 7 Best Leadpages Alternatives in 2022
Does My Business Need a Website? 12 Reasons Why & 5 Reasons Why Not
How to Add HTML Embed Codes to Your Website [Quick Tip]
The 12 Best ClickFunnels Alternatives & Competitors in 2023
How to Hire a Freelance Web Developer
Do I Still Need a .com TLD For My Business?
Unlock the power of data and transform your business with HubSpot's comprehensive guide to data analytics.
- Creating an SQL Statement
How to Create a SQL Statement
Use this guide to review how to create a select SQL statement to use in the SQL Executer.
As you review the statements and clauses below, note that brackets identify optional information.Include a bracketed item if you want to select more specific data.
- For helpful hints and problem solutions, SQL Review and Troubleshooting .
Note: If you don't know the SQL programming language, you can use QueryLink by clicking the Queries button in FinancialLink , EmployeeLink , Student/ Class Info tools , and DataLink .
1. Start your query with the select statement.
- select [all | distinct] A select statement queries the database and retrieves selected data that match the specified criteria.
2. Add field names you want to display.
Note: You can find field and data table names in the Data Models section of DataLink .
3. Add your statement clause(s) or selection criteria.
- from table1[, table2] The from clause specifies the table(s) that contain the data.
- where conditions The where clause specifies the selection condition(s) by which data is retrieved.
- order by [column-list] The order by clause specifies the ordering or sorting of rows.
- group by [column-list] The group by clause groups the resulting rows in sets.
- having conditions The having clause allows a search condition and is used with the group by clause.
4. Review your select statement.
select [all | distinct] field1[,field2] from table1[,table2] [ where conditions] [ group by field-list] [ having conditions] [ order by field-list]
- The example below shows SQL being used in the SQL Executer to produce a phone list of all ACT staff by last name.
- The list will show last names, first names, phone extensions, and e-mail addresses.
Sql database, sql references, sql examples, sql statements.
Most of the actions you need to perform on a database are done with SQL statements.
SQL statements consists of keywords that are easy to understand.
The following SQL statement returns all records from a table named "Customers":
Select all records from the Customers table:
In this tutorial we will teach you all about the different SQL statements.
A database most often contains one or more tables. Each table is identified by a name (e.g. "Customers" or "Orders"), and contain records (rows) with data.
In this tutorial we will use the well-known Northwind sample database (included in MS Access and MS SQL Server).
Below is a selection from the Customers table used in the examples:
The table above contains five records (one for each customer) and seven columns (CustomerID, CustomerName, ContactName, Address, City, PostalCode, and Country).
Keep in Mind That...
- SQL keywords are NOT case sensitive: select is the same as SELECT
In this tutorial we will write all SQL keywords in upper-case.
Semicolon after SQL Statements?
Some database systems require a semicolon at the end of each SQL statement.
Semicolon is the standard way to separate each SQL statement in database systems that allow more than one SQL statement to be executed in the same call to the server.
In this tutorial, we will use semicolon at the end of each SQL statement.
Some of The Most Important SQL Commands
- SELECT - extracts data from a database
- UPDATE - updates data in a database
- DELETE - deletes data from a database
- INSERT INTO - inserts new data into a database
- CREATE DATABASE - creates a new database
- ALTER DATABASE - modifies a database
- CREATE TABLE - creates a new table
- ALTER TABLE - modifies a table
- DROP TABLE - deletes a table
- CREATE INDEX - creates an index (search key)
- DROP INDEX - deletes an index
If you want to report an error, or if you want to make a suggestion, do not hesitate to send us an e-mail:
Top references, top examples, get certified.
- Chat with a consultant
SQL example statements for retrieving data from a table
Select statements, learning more about sql.
Structured Query Language (SQL) is a specialized language for updating, deleting, and requesting information from databases . SQL is an ANSI and ISO standard, and is the de facto standard database query language. A variety of established database products support SQL, including products from Oracle and Microsoft SQL Server. It is widely used in both industry and academia, often for enormous, complex databases.
In a distributed database system, a program often referred to as the database's "back end" runs constantly on a server, interpreting data files on the server as a standard relational database. Programs on client computers allow users to manipulate that data, using tables, columns, rows, and fields. To do this, client programs send SQL statements to the server. The server then processes these statements and returns result sets to the client program.
An SQL SELECT statement retrieves records from a database table according to clauses (for example, FROM and WHERE ) that specify criteria. The syntax is:
In the above SQL statement:
- The SELECT clause specifies one or more columns to be retrieved; to specify multiple columns, use a comma and a space between column names. To retrieve all columns, use the wild card * (an asterisk).
- The FROM clause specifies one or more tables to be queried. Use a comma and space between table names when specifying multiple tables.
- The WHERE clause selects only the rows in which the specified column contains the specified value. The value is enclosed in single quotes (for example, WHERE last_name='Vader' ).
- The semicolon ( ; ) is the statement terminator. Technically, if you're sending only one statement to the back end, you don't need the statement terminator; if you're sending more than one, you need it. It's best practice to include it.
Following are examples of SQL SELECT statements:
The server back end would reply with a result set similar to this:
The subsequent result set might look like:
To make a WHERE clause find inexact matches, add the pattern-matching operator LIKE . The LIKE operator uses the % (percent symbol) wild card to match zero or more characters, and the underscore ( _ ) wild card to match exactly one character. For example:
The result set might look like:
- If you used the % wild card instead (for example, '%en' ) in the example above, the result set might look like: +------------+------------+-----------+ | First_Name | Last_Name | Nickname | +------------+------------+-----------+ | Ben | Smith | Brainiac | | Glen | Jones | Peabrain | | Jen | Peters | Sweetpea | | Steven | Griffin | Nobrainer | +------------+------------+-----------+ 4 rows in set (0.05 sec)
To learn more about SQL programming, Indiana University students, faculty, and staff can download materials for self-study from IT Training.
For the general public, various online tutorials are available, such as the w3schools.com SQL Tutorial .
This is document ahux in the Knowledge Base. Last modified on 2023-07-07 12:49:02 .
- SQL Server training
- Write for us!
Learn to write basic SQL Queries
Essentially, SQL language allows us to retrieve and manipulate data on the data tables. In this article, we will understand and gain the ability to write fundamental SQL queries. At first, we will take a glance at the main notions that we need to know about in order to write database queries.
What is the T-SQL?
SQL is the abbreviation of the Structured Query Language words and, it is used to query the databases. Transact-SQL (T-SQL) language is an extended implementation of the SQL for the Microsoft SQL Server. In this article, we will use the T-SQL standards in the examples.
What is a Relational Database?
Most simply, we can define the relational database as the logical structure in which data tables are kept that can relate to each other.
What is a Data Table?
A table is a database object that allows us to keep data through columns and rows. We can say that data tables are the main objects of the databases because they are holding the data in the relational databases.
Assume that we have a table that holds the history class students’ details data. It is formed in the following columns.
Name: Student name
SurName: Student surname
Lesson: Opted lesson
Age: Student age
PassMark: Passing mark
We will use this table in our demonstrations in this article. The name of this data table is Student.
Our First Query: SELECT Statement
The SELECT statement can be described as the starting or the zero point of the SQL queries. The SELECT statement is used to retrieve data from the data tables. In the SELECT statement syntax, at first, we specify the column names and separate them with a comma if we use a single column we don’t use any comma in the SELECT statements. In the second step, we write the FROM clause and as a last, we specify the table name. When we consider the below example, it retrieves data from Name and Surname columns, the SELECT statement syntax will be as below:
If we want to retrieve data from only the Name column, the SELECT statement syntax will be as below:
Tip: We can easily try all these examples in this article by ourselves in the SQL Fiddle over this link . After navigating to the link, we need to clear the query panel and execute the sample queries.
The asterisk ( * ) sign defines all columns of the table. If we consider the below example, the SELECT statement returns all columns of the Student table.
- Our main purpose should be to get results from the SQL queries as soon as possible with the least resource consumption and minimum execution time. As possible, we have to avoid using the asterisk (*) sign in the SELECT statements. This usage type causes the consume more IO, CPU and Network cost. As a result, if we don’t need all columns of the table in our queries we can abandon to use asterisk sign and only use the necessary columns
Filtering the Data: WHERE Clause
WHERE clause is used to filter the data according to specified conditions. After the WHERE clause, we must define the filtering condition. The following example retrieves the students whose age is bigger and equal to 20.
LIKE operator is a logical operator that provides to apply a special filtering pattern to WHERE condition in SQL queries. Percentage sign ( % ) is the main wildcard to use as a conjunction with the LIKE operator. Through the following query, we will retrieve the students whose names start with J character.
IN operator enables us to apply multiple value filters to WHERE clause. The following query fetches the students’ data who have taken the Roman and European History lessons.
The BETWEEN operator filters the data that falls into the defined begin and end value. The following query returns data for the students whose marks are equal to and bigger than 40 and smaller and equal to 60.
Sorting the Data: ORDER BY Statement
ORDER BY statement helps us to sort the data according to the specified column. The result set of the data can be sorted either ascending or descending. ASC keyword sorts the data in ascending order and the DESC keyword sorts the data in descending order. The following query sorts the students’ data in descending order according to the PassMark column expressions.
By default ORDER BY statement sorts data in ascending order. The following example demonstrates the default usage of the ORDER BY statement.
Eliminating the Duplicate Data: DISTINCT Clause
The DISTINCT clause is used to eliminate duplicate data from the specified columns so the result set is populated only with the distinct (different) values. In the following example, we will retrieve Lesson column data, however, while doing so, we will retrieve only distinct values with the help of the DISTINCT clause
As we can see, the DISTINCT clause has removed the multiple values and these values added to the result set only once.
In this section, we can test our learnings.
Question – 1:
Write a query that shows student name and surname whose ages are between 22 and 24.
Question – 2:
Write a query that shows student names and ages in the descending order who takes Roman and Ancient History lessons.
In this article, we learned how we can write the basic SQL queries, besides that we demonstrated usage of the queries with straightforward examples.
- Recent Posts
- SQL Unit Testing reference guide for beginners - August 11, 2023
- SQL Cheat Sheet for Newbies - February 21, 2023
- SQL Practice: Common Questions and Answers for the final round interviews - January 26, 2023
- SQL Order by Clause overview and examples
- Calculate SQL Percentile using the PERCENT_RANK function in SQL Server
- Top SQL Server Books
- Temporal Table applications in SQL Data Warehouse environments
- Managing untrusted foreign keys