Skip to content
  1. SQLite Database Basics with DB Browser for SQLite
  2. INSERT INTO & UPDATE
  3. More SQL Syntax:
  4. Building a Database with Python (SQLite)
  5. Querying a Database with Python (SQLite)
  6. More SQLite Examples:
  7. SQL Native Visualization

SQL Querying Basics

Python Tutorials

Contents

In this SQL tutorial we have some basic SQL query examples. You can also use them with Python. 

Kindly refer to these tutorials for SQL applications in Python:

1- SELECT *: Returning the Whole Table

Let’s start with the most simple SQL example using SELECT * this is used to return all the columns in a database table.

SELECT * FROM Fam

This will return all the columns from the table Fam.

('Pam', 50, 'Unknown')
('Miranda', 32, 'Unknown')
('Pascal', 45, 'Unknown')
('Dave', 12, 'Unknown')
('Emmy', 23, 'Unknown')
('Michael', 35, 'Unknown')

2- Conditional Queries

This is conditional SQL Querying. It can be done by using WHERE syntax. It will return values based on the statement after WHERE.

SELECT * FROM Fam WHERE Age > 30

We can expect all the columns to be returned but only for rows where Age value is above 30.

('Pam', 50, 'Unknown')
('Miranda', 32, 'Unknown')
('Pascal', 45, 'Unknown')
('Michael', 35, 'Unknown')

Or use BETWEEN AND to query for a range of values.

SELECT * FROM Fam WHERE Age BETWEEN 5 AND 25 

('Dave', 12, 'Unknown')
('Emmy', 23, 'Unknown')

3- Picking Specific Columns

Instead of using * to return all, we can also handpick columns, this can easily be achieved by specifying column names instead of star (asterisk) operator.

SELECT Name, Age WHERE Age > 30 

('Pam', 50)
('Miranda', 32)
('Pascal', 45)
('Michael', 35)

4- LIMIT

You can limit the amount of results in your query. This can be achieved by LIMIT statement.

SELECT Name, Age WHERE Age < 50 LIMIT 2

This code will only return 2 rows from the results of the query.

('Miranda', 32)
('Pascal', 45)

5- SQL Scalar Functions

You can use a bunch of very useful scalar functions in your SQL queries as well. These functions are:

  • MIN, 
  • MAX, 
  • COUNT, 
  • AVG, 
  • SUM, 
  • ROUND

This example will count all the rows in a database table.

SELECT COUNT(*) FROM Fam

6

Similarly, you can use AVG, SUM or MAX or MIN on a specific column or the whole table. Round will return numbers with decimals in a rounded form.

6- SQL String Functions

We can also use convenient string functions in our SQLite queries. 

LENGTH can be used to get the length of a text.

UPPER will return a text with all uppercases. (UCASE in MySQL)

LOWER will return a text with all lowercases. (LCASE in MySQL)

SELECT UPPER(Name) FROM Fam

(‘PAM’)
(‘MIRANDA’)
(‘PASCAL’)
(‘DAVE’)
(‘EMMY’)
(‘MICHAEL’)

Summary

In this Python Tutorial, we’ve learned basic SQLite query syntax, rules and functions. This content was SQLite specific but most of it would also apply to other SQL based databases such as MySQL.

In specific, we have learned how to:

  • SELECT all,
  • SELECT WHERE conditions apply
  • SELECT specific columns,
  • LIMIT Query results
  • Use scalar functions: count, min, max, avg, sum, round
  • Use string functions: length, upper, lower
In the tutorial we’ve used a simple databases for demonstration purposes. If you need help constructing a similar database you can check out this tutorial:

Thank you for visiting.