How To Extract Data From Sql Query

Extracting Data From a Database

Filtering Data with Queries

SQL or Sequel is the standard language for communicating with relational databases. Creating or modifying databases using queries is an important skill for data scientists or analysts equally databases are i of the most common sources of data.

Whatsoever well meaning company or organization keeps its own data. This data may include consumer data, purchase or sale, inventory and so on. A database is but a collection of related data. A database is made upward of tables which in turn are made up of fields.

Nosotros will exist working with a sample SALES database of an eastward-commerce company courtesy of my grooming with Digital Explorers.

The database has 3 tables:

BasketProducts
Products
Baskets

SQL Basics

One of the principal advantages of SQL is its simple syntax. These elementary clauses tin be combined in any number of ways to make complex queries. The basic SQL syntax is the SELECT statement. It is usually the first statement of an SQL query. The basic order of writing an SQL query is equally follows:

          SELECT
FROM
WHERE
Group BY
HAVING
ORDER Past
LIMIT

These are the basic SQL commands. They are executed in the post-obit sequence:

          FROM
WHERE
Grouping BY
HAVING
SELECT
Order BY
LIMIT

The SELECT argument indicates the data you want to be displayed. This tin can exist a selection of columns or calculated fields.

The FROM clause indicates the tabular array where SQL should search for the fields specified in the SELECT clause. If the column/field does not exist in the table, we become an error.

          SELECT *            
FROM Baskets
LIMIT 5

The above query will render the complete Baskets table — all rows and columns. The LIMIT command is used to select the superlative n rows.

          user_id    basket_id      event_time        abandond   discount
473613801   12878    2019-12-01 00:17:50        ane        0.0
547853931   12879    2019-12-01 00:20:47        0        0.0
433063720   12880    2019-12-01 00:25:37        1        0.0
494077766   12881    2019-12-01 00:26:xv        0        0.0
579950721   12882    2019-12-01 00:43:51        0        0.0

To select specific columns, the column names are specified with the SELECT clause. The column names tin can as well exist saved nether a different allonym using AS with the SELECT argument.

          SELECT basket_id Every bit Customers, abandond
FROM Baskets
LIMIT five                      
            OUTPUT:                    Customers abandond
12878        1
12879        0
12880        1
12881        0
12882        0

Columns can likewise be computed in SQL. Beneath is the calculation of Turn a profit expected from each particular in the store in descending order. Unproblematic calculations in SQL include MIN, MAX, COUNT, Boilerplate, SUM.

          SELECT price-price AS Profit
FROM Products
Gild Past Profit DESC
LIMIT 10          OUTPUT:          Profit
115.12
96.59
84.08
81.03
74.14
70.81
68.01
58.66
57.04
55.66

Filtering Data with SQL

To filter through the records/rows in SQL, yous need the WHERE clause. Using a combination of functions or characters with the WHERE clause allows united states of america to select specific portions of our information. Nosotros will focus on the Baskets and Products table in our database.

Using '='

To make exact matches across rows in our tabular array, we utilise the equals sign. Beneath are examples for numeric and text data types. Annotation that we enclose text data in quotes when using the equals sign. The equals sign is instance sensitive and text information must match exactly.

To view customers who completed purchases (did not abandon cart)

          SELECT *
FROM Baskets
WHERE abandond = 0;

To filter products of a particular brand

          SELECT *
FROM Products
WHERE make = 'runail';

Using Relational Expressions

We can also combine the WHERE clause with relational expressions. These expressions include

> or<: Selects rows with values greater than or less than a specified value
>=: Selects rows with values greater than or equal to a given value
<= : Selects rows with values less than or equal to a given value

          -- Which customers had discount less than 25%          SELECT user_id, discount
FROM Baskets
WHERE discount < 0.25

The above query will render the columns user_id and discount columns for customers who had discount less than 25%. We can include customers with 25% discount past using <= .

To select only customers with 25% disbelieve, nosotros employ== or =.

Using Logical Expressions

These logical expressions follow the aforementioned rules every bit in math. We specify condition(southward) which we want to be met. With the AND operation, both conditions take to be Truthful, while with the OR operator, only one of the conditions has to be TRUE. The Non operator negates the condition specified. Logical expressions include:

AND & : Used when 2 conditions must be strictly met
OR | : Only one of the specified conditions has to be met
NOT : Negates the status specified

          -- Number of customers who did not abandon their cart and had 25% disbelieve          SELECT COUNT(user_id) equally USERS
FROM Baskets
WHERE (abandond=0) & (discount == 0.25)          OUTPUT:          USERS
966

Some other case:

          -- How many customers had discount of 25% or did not abandon their carts          SELECT count(user_id) as USERS
FROM Baskets
WHERE(abandond = 0) | (disbelieve = 0.25);          OUTPUT:          USERS
20730

Detect the change in the number of users because of a change in logical operator.

          -- How many users did non have 25% discount and did not consummate their purchase          SELECT count(user_id) as USERS
FROM Baskets
WHERE NOT((abandond = 0) & (discount == 0.25));          OUTPUT:          USERS
24642

These operations can be combined to brand complex queries if demand be.

WHERE … IN

If you want to brand a selection from a limited number of options, the WHERE… IN clause is just the affair for you lot.

          -- Which customers had discounts of either 15% or 50%          SELECT *
FROM Baskets
WHERE discount IN(0.fifteen,0.5)
LIMIT 10

Our Output:

                      user_id            basket_id            event_time            abandond            disbelieve            
356011474   12889     2019-12-01 01:xviii:32     1         0.v
556337981   12892     2019-12-01 02:00:03     0         0.15
580026195   12917     2019-12-01 04:25:54     one         0.5
562396937   12921     2019-12-01 04:52:51     0         0.xv
559344368   12938     2019-12-01 06:00:09     1         0.15
580100376   12974     2019-12-01 07:31:33     0         0.v
490632556   12976     2019-12-01 07:32:04     1         0.five
458855541   13006     2019-12-01 08:24:11     0         0.15
506885505   13030     2019-12-01 08:57:35     0         0.5
528539896   13039     2019-12-01 09:14:41     0         0.5

We can also negate the WHERE...IN clause using WHERE...Non IN . If we negate the example above, we will get all customers who did not get 15% or 50% discount.

WHERE…Between

To select data inside a range, we tin can also use the WHERE…Between clause. What differentiates this clause from < or > is that this is inclusive of the boundaries. It is similar to <= or >= in this regard.

          -- Which customers have discounts betwixt 15% and 50%          SELECT *
FROM Baskets
WHERE discount BETWEEN 0.15 AND 0.five
LIMIT 10;

Output:

                      user_id    basket_id       event_time       abandond   discount            
356011474    12889    2019-12-01 01:18:32      1         0.5
556337981    12892    2019-12-01 02:00:03      0         0.15
580026195    12917    2019-12-01 04:25:54      1         0.five
562396937    12921    2019-12-01 04:52:51      0         0.15
559344368    12938    2019-12-01 06:00:09      1         0.15
571600331    12942    2019-12-01 06:25:l      0         0.25
557560536    12943    2019-12-01 06:26:22      0         0.25
534305618    12973    2019-12-01 07:26:17      0         0.25
580100376    12974    2019-12-01 07:31:33      0         0.5
490632556    12976    2019-12-01 07:32:04      one         0.5

WHERE…LIKE

The WHERE…Similar clause is used when the exact term to match is unknown. Nosotros can use this clause in conjunction with wildcards at the beginning or end of the lucifer give-and-take.

If we want to search for a particular brand from the Products table simply cannot call back the full name of the brand, nosotros can make a query search using portions we know. Below is a query search for a brand that ends with '-nail'.

          SELECT *
FROM Products
WHERE brand LIKE "%nail"
LIMIT 10;

OUTPUT:

          product_id    brand    price    toll
4554          runail   0.38     0.32
4600          runail   0.38     0.two
4607          runail   0.38     0.xix
4640          runail   0.37     0.35
4689          runail   0.38     0.26
4768          runail   0.38     0.26
4820          runail   0.38     0.27
4870          runail   0.38     0.28
5887055      jessnail  ane.1      0.88
5894905      jessnail  ane.1      0.nine

The LIKE clause is non case sensitive, and therefore nosotros become the same result as before when we run the query below:

          SELECT *
FROM Products
WHERE brand LIKE "%NAIL"
LIMIT 10;

General SQL Conventions

SQL is not case sensitive. Code tin can be written in capital or pocket-sized messages. It is however, proficient practise to capitalize SQL commands such every bit SELECT, FROM, WHERE, Gild By, ALTER, DROP etc.

The data stored in SQL tables are case sensitive. To lucifer a data in SQL table, it must be in the same format. To go over this, use the LIKE command or its variation depending on the RDBMS.

Avoid Spaces in Table and Variable names. In our queries above, we did not have to enclose column names in quotes even when nosotros used the AS command. If these column names had spaces, we would need to specify these names in quotes so that SQL understands that it is i proper noun.

SQL ignores white space in code. The lawmaking below will run without errors.

          SELECT                        basket_id
FROM Baskets
LIMIT                5;

The arrangement of SQL is not strict and the command above can also be written in a single line.

          SELECT basket_id  FROM Baskets LIMIT  v;

It is yet good practice to write SQL across several lines with a different command clause. This improves readability and makes information technology easy to spot errors.

Not all SQL requires you to end code with a semi-colon. And so make certain to check this feature on your RDBMS.

Employ comments. This is a general dominion when coding. Comments make our queries easier to read and understand. With comments, we are able to follow the thought process behind the plan or in this example, query.

Conclusion

SQL is adequately elementary to learn and write. While the SQL syntax is full general beyond dissimilar RDBMS, small-scale variations may exist. Exist sure to cheque for this when switching to a new RDBMS.

Written by Anita Igbine