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_familypriceCell Phone A200TrixPhones850Cell Phone A310TrixPhones1200HeadsetZeyAudio50Portable SpeakerZeyAudio230Sound Box Hulk1ZeyAudio340Sound Box Hulk2ZeyAudio530HeadsetZeyAudio58Tablet X8 8 inchMiniSOftTablets300Tablet 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: namerentabilityCell Phone A200350Cell Phone A310460Headset26Portable Speaker90Sound Box Hulk1120Sound Box Hulk2210Headset26Tablet X8 8 inch145Tablet X12 12 inch215 If you’re interested in learning more about SQL but have no prior knowledge of programming or databases, take a look at our SQL Basics course. 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_ familypriceHeadsetZeyAudio50Sound Box Hulk1ZeyAudio340Portable SpeakerZeyAudio230Sound Box Hulk2ZeyAudio530HeadsetZeyAudio58 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_ familypriceHeadsetZeyAudio50HeadsetZeyAudio58Portable SpeakerZeyAudio230Sound Box Hulk1ZeyAudio340Sound 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_ familypriceHeadsetZeyAudio50HeadsetZeyAudio58Portable SpeakerZeyAudio230Sound 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 rentabilityTablet X12 12 inch21514030100Cell Phone A3104605023000Cell 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.
