27th Oct 2022 8 minutes read What Are the Basic SQL Queries? Ignacio L. Bisso sql learn sql online practice Table of Contents What Is a SQL Query? Our Example Table Basic SQL Queries Query 1: Selecting All the Data from a Table Query 2: Selecting Specific Columns from a Table Query 3: Doing Simple Computations Query 4: Filtering Data Query 5: Sorting Data in the Query Result Combining It All to Solve Business Problems Continue Learning Basic SQL Queries! You can access the data in a database using some basic SQL queries. In this article, we review the fundamentals SQL queries every beginner should know. Databases are everywhere, and they contain different kinds of data. Almost all of them support SQL, a language that lets you read and manipulate the data inside a database. In this article, I’ll show you the basic SQL queries that you can use to perform many common everyday data tasks. The best way to learn SQL is our interactive SQL Basics course. It has 120+ exercises, starting with the very simple examples and then moving on to concepts like JOINs and WHERE conditions. You can read more about SQL in our article What Is SQL?. You can also discover why SQL is not difficult to learn. What Is a SQL Query? A database is a computer program that stores large amounts of data. Databases are typically organized in tables. Each table stores data for a thing, person, place, or some other object (i.e. describing customers, products, appointments, or employees, for example). Once you have the data stored in a database, you can create reports using this data – for example, you can create a list of the customers who made transactions in the last month. You create database reports with the help of SQL, or Structured Query Language. SQL is the programming language used to talk to databases. A SQL query is an expression, similar to an English sentence, that defines the set of data to be retrieved from the database. You can think of a SQL query as a question you sent to the database; after that, you expect the database will respond to the question by sending back the data. In this article, we’ll show you the most basic SQL queries. These can be used to perform common data tasks. You can easily modify these SQL queries and use them on your own database. Before we start the queries, let’s look at the data we will be using. Our Example Table The queries will be based on a table called products. This table stores all the product information for an e-commerce company. You can see the columns of the table in the image below: prod_codenameprod_ familycostpricestockproviderstatus 1001Cell Phone A200Phones50085035Trixavailable 1002Cell Phone A310Phones740120050Trixavailable 1003HeadsetAudio2450150Zeysold out 2001Portable SpeakerAudio14023050Zeyavailable 2002Sound Box Hulk1Audio2203400Zeydiscontinued 2003Sound Box Hulk2Audio32053012Zeyavailable 2004HeadsetAudio325885Zeyavailable 3001Tablet X8 8 inchTablets15530035MiniSOftavailable 3002Tablet X12 12 inchTablets21543040MiniSoftavailable The names of the columns are self-explanatory. Perhaps the most important column is prod_code. It stores a numeric code identifying each product (i.e. the ID of each product). Also, it’s good to note that the status column can have only four valid values: ‘available’, ‘sold out’, ‘arriving soon’ or ‘discontinued’. Now that we know what data to expect, let’s start writing some SQL queries! Basic SQL Queries Query 1: Selecting All the Data from a Table The first query is a simple way to retrieve all the information from a database table. The following query shows all records in all the product table columns: SELECT * FROM products This SELECT statement uses the * symbol to represent “all the columns”. After that, the FROM clause indicates the table to be read. As we can see below, the result is the complete table: all records and all columns. You can use this query to peek into a table to get to know what kind of data there is in the table. prod_codenameprod_ familycostpricestockproviderstatus 1001Cell Phone A200Phones50085035Trixavailable 1002Cell Phone A310Phones740120050Trixavailable 1003HeadsetAudio2450150Zeysold out 2001Portable SpeakerAudio14023050Zeyavailable 2002Sound Box Hulk1Audio2203400Zeydiscontinued 2003Sound Box Hulk2Audio32053012Zeyavailable 2004HeadsetAudio325885Zeyavailable 3001Tablet X8 8 inchTablets15530035MiniSOftavailable 3002Tablet X12 12 inchTablets215430140MiniSoftavailable Query 2: Selecting Specific Columns from a Table You can also only show a few columns from a table. Let’s suppose we want a price list with the columns name, provider, prod_family and price. The query is very simple: SELECT name, provider, prod_family, price FROM products We specified the columns we want to extract from the table: name, provider, prod_family, and price. In the second line of the query, we use the FROM clause to indicate the table to read. The result is: nameproviderprod_familyprice Cell Phone A200TrixPhones850 Cell Phone A310TrixPhones1200 HeadsetZeyAudio50 Portable SpeakerZeyAudio230 Sound Box Hulk1ZeyAudio340 Sound Box Hulk2ZeyAudio530 HeadsetZeyAudio58 Tablet X8 8 inchMiniSOftTablets300 Tablet X12 12 inchMiniSoftTablets430 Query 3: Doing Simple Computations We can do computations within a SQL query. These let us obtain a calculated value derived from other values stored in the database table. Let’s calculate products’ rentability amounts using the price and cost columns and then show the amount alongside the product name. Here’s the query we’d use: SELECT name, price - cost AS rentability FROM products The second line of the query has the arithmetic expression price - cost. The AS keyword allows to assign the name rentability to the result of this arithmetic expression. The new information is shown as a column in the query result: namerentability Cell Phone A200350 Cell Phone A310460 Headset26 Portable Speaker90 Sound Box Hulk1120 Sound Box Hulk2210 Headset26 Tablet X8 8 inch145 Tablet X12 12 inch215 Query 4: Filtering Data Usually you don’t want to see all the data in a database. You want to see only the rows that match certain criteria: products from one product family or with a price below a certain threshold. In SQL, this is called filtering the data. The WHERE clause filters the data you want from the rest of the data in the table. It extracts records that match certain conditions. For example, suppose we want a price list for products in the “Audio” family. We can use the following query to get the result: SELECT name, provider, prod_family, price FROM products WHERE prod_family = ‘Audio’ The WHERE clause indicates that we only want to obtain those records where the condition prod_family = ‘Audio’ is TRUE. Other records (those belonging to the Phones or Tablets families) will not be part of the result. In other words, we are filtering records from the table products. nameproviderprod_ familyprice HeadsetZeyAudio50 Sound Box Hulk1ZeyAudio340 Portable SpeakerZeyAudio230 Sound Box Hulk2ZeyAudio530 HeadsetZeyAudio58 Query 5: Sorting Data in the Query Result Often you want to see query results in a certain order, such as products from the cheapest to the most expensive or product names in alphabetical order. The clause ORDER BY is used to define how the records will be ordered in the query result. Let’s suppose we want to order the products from our previous query by name. Note how we use ORDER BY in this query: SELECT name, provider, prod_family, price FROM products WHERE prod_family = ‘Audio’ ORDER BY name We can see the same records in alphabetical order by product name: nameproviderprod_ familyprice HeadsetZeyAudio50 HeadsetZeyAudio58 Portable SpeakerZeyAudio230 Sound Box Hulk1ZeyAudio340 Sound Box Hulk2ZeyAudio530 Combining It All to Solve Business Problems You can combine all the SQL features shown in this article into one query. Combining SQL features in this way creates a more complex query that can solve real business problems. The result of the last query in the previous section is similar to a price list of Audio family products. However, it shows products that are discontinued or sold out. In the next query, we’ll add a second logical condition in the WHERE clause to include only products that are currently available: SELECT name, provider, prod_family, price FROM products WHERE prod_family = ’Audio’ AND status = ’available’ ORDER BY name The conditions in the WHERE clause are connected with the AND logical connector; thus, the result will include only records where both logical conditions are TRUE. We can see this in the following results: nameproviderprod_ familyprice HeadsetZeyAudio50 HeadsetZeyAudio58 Portable SpeakerZeyAudio230 Sound Box Hulk2ZeyAudio530 In the next example query, we’ll try combining all the SQL clauses covered in this article. Let’s suppose that we are planning a marketing campaign and we need to choose two products to promote. We want products with a rentability greater than $200 and with a stock greater than 25 units. We will also order the report by a metric called “potential rentability” which is calculated with the formula: (price - cost) * stock. Here is the query: SELECT name, price - cost AS rentability, stock, (price - cost) * stock AS potential_rentability FROM products WHERE price - cost > 200 AND stock > 25 ORDER BY (price - cost) * stock DESC; The previous query calculates two metrics. One metric is rentability which is calculated using the formula price - cost. The other metric is potential_rentability, which is calculated using the formula (price - cost) * stock. In the WHERE clause, the query specifies two conditions: the records to return must have a rentability greater than 200 and a stock greater than 25 units. Finally, the result of the query is ordered by the metric potential_rentability in descending (largest to smallest) order. Below is the result: namerentabilitystockpotential rentability Tablet X12 12 inch21514030100 Cell Phone A3104605023000 Cell Phone A2003503512250 Continue Learning Basic SQL Queries! In this article, we covered the basic SQL queries that can be used to perform simple data tasks. However, SQL offers many more clauses and techniques that allow you to do more operations on data. Our interactive SQL Basics course is the best way to master foundational SQL. It contains over 120 exercises that cover basic SQL queries (like the ones shown in this article) and other SQL constructions that allow you to combine data from multiple tables and compute various statistics. If you already know the basics of SQL, our SQL Basic Cheat Sheet is a handy reference resource. It will give you a concise reminder of SQL syntax with example queries. Tags: sql learn sql online practice