SQL Querying Basics
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
Thank you for visiting.