Azure Cloud Where To Start Microsoft Azure by mukilan October 30, 2022 written by mukilan October 30, 2022 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: name provider prod_family price Cell Phone A200 Trix Phones 850 Cell Phone A310 Trix Phones 1200 Headset Zey Audio 50 Portable Speaker Zey Audio 230 Sound Box Hulk1 Zey Audio 340 Sound Box Hulk2 Zey Audio 530 Headset Zey Audio 58 Tablet X8 8 inch MiniSOft Tablets 300 Tablet X12 12 inch MiniSoft Tablets 430 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: name rentability Cell Phone A200 350 Cell Phone A310 460 Headset 26 Portable Speaker 90 Sound Box Hulk1 120 Sound Box Hulk2 210 Headset 26 Tablet X8 8 inch 145 Tablet X12 12 inch 215 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. name provider prod_ family price Headset Zey Audio 50 Sound Box Hulk1 Zey Audio 340 Portable Speaker Zey Audio 230 Sound Box Hulk2 Zey Audio 530 Headset Zey Audio 58 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: name provider prod_ family price Headset Zey Audio 50 Headset Zey Audio 58 Portable Speaker Zey Audio 230 Sound Box Hulk1 Zey Audio 340 Sound Box Hulk2 Zey Audio 530 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: name provider prod_ family price Headset Zey Audio 50 Headset Zey Audio 58 Portable Speaker Zey Audio 230 Sound Box Hulk2 Zey Audio 530 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: name rentability stock potential rentability Tablet X12 12 inch 215 140 30100 Cell Phone A310 460 50 23000 Cell Phone A200 350 35 12250 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. 0 comment 0 FacebookTwitterPinterestEmail mukilan previous post Where to Start AWS ( Amazon Web Server) next post SQL Server Developer Beginner You may also like Microsoft Azure December 19, 2022 Leave a Comment Cancel Reply Save my name, email, and website in this browser for the next time I comment.