How to write SQL queries?

https://www.tutorialrepublic.com/lib/images/sql-illustration.png

In the last post blog we saw how to use the SELECT function in SQL and how to write some basic queries.
Today the aim is to start from the basics and add some complexity at it using both filtering and ordering for retrieving and adding specific data to SQL database tables.

So let’s do this!

In this post blog we’ll start by connecting to sample_database.db.

Recall that we can do this by running the following commands using Python:

import sqlite3 
conn = sqlite3.connect('sample_database.db')
cur = conn.cursor()

We saw previously how to use ORDER BY and LIMIT.

Order By

This query modifier allows us to order the table rows returned by a certain SELECT statement.

Here’s a boilerplate SELECT statement that uses ORDER BY:

cur.execute('''SELECT column_name 
               FROM table_name 
               ORDER BY column_name ASC|DESC;
               ''').fetchall()

When using ORDER BY, the default is to order in ascending order. If we want to specify though, we can use ASC for "ascending" or DESC for "descending."

Limit

If we want to select extremes from a database table , for example, the employee with the highest paycheck or the patient with the most recent appointment, we can use ORDER BY in conjunction with LIMIT.

cur.execute('''SELECT column_name 
               FROM table_name 
               ORDER BY column_name ASC|DESC
               LIMIT x;
               ''').fetchall()

Between

As we’ve seen, being able to sort and select specific datasets is important. We can use this query modifier for example select all of the patients whose age is between 1 and 3. To create such a query, we can use BETWEEN. Here's a boilerplate SELECT statement using BETWEEN:

cur.execute('''SELECT column_name 
               FROM table_name 
               WHERE column_name
               BETWEEN value1 AND value2;                                                                   '              ''').fetchall()

Null

We can select rows where the element is NULL. For example:

cur.execute('''SELECT column_name 
               FROM table_name 
               WHERE column_name
               IS null;                                                                   '              ''').fetchall()

https://ittutorial.org/wp-content/uploads/2019/11/rdtyuj%C4%B1.png

SQL aggregate function

SQL aggregate functions are SQL statements that can get the average of a column’s values, retrieve the minimum and maximum values from a column, sum values in a column, or count a number of records that meet certain conditions.

Count

Count is a SQL aggregate function which counts the number of records that meet a certain condition. Here’s a standard SQL query using COUNT:

cur.execute('''SELECT COUNT (column_name) 
               FROM table_name
               WHERE column_name;
               ''').fetchall()

Distinct

The DISTINCT keyword allows us to omit duplicates from our results. This is achieved by grouping similar values together .

Here’s a boilerplate SELECT statement using DISTINCT:

cur.execute('''SELECT DISTINCT column_name 
               FROM table_name
               WHERE column_name;
               ''').fetchall()

Min Function

The Min function returns the smallest value in the specified table field.

For example:

cur.execute('''SELECT MIN (column_name) 
               FROM table_name
               ''').fetchall()

Max Function

Just as the name suggests, the Max Function is the opposite of the Min Function. It returns the largest value from the specified table field.

cur.execute('''SELECT MAX(column_name) 
               FROM table_name
               ''').fetchall()

Sum Function

Suppose we want a report that gives total amount of payments made so far. We can use the SQL Sum Function which returns the sum of all the values in the specified column. SUM works on numeric fields only. Null values are excluded from the result returned.

cur.execute('''SELECT SUM (column_name) 
               FROM table_name
               ''').fetchall()

Avg Function

SQL Avg Function returns the average of the values in a specified column. Just like the SUM function, it works only on numeric data types.

cur.execute('''SELECT AVG(column_name) 
               FROM table_name
               ''').fetchall()

Conclusions

In this post blog, we reviewed our SQL knowledge by learning how to modify our data using statements like ORDER BY. Additionally, we learned how to filter and limit our results using the BETWEEN, IS NULL, and LIMIT statements. SQL also supports the standard aggregate functions COUNT, SUM, AVG, MINand MAX. SUM and AVG functions only work on numeric data. Finally If we want to exclude duplicate values from the aggregate function results, use the DISTINCTkeyword.

For today it’s all guys. Hope you enjoyed it and see you for the next episode!