Build Your Own AI Assistant Part 1 - Creating the Assistant
116820 Views
Is the new Raspberry Pi AI Kit better than Google Coral?
114678 Views
Control Arduino with Python using Firmata / PyFirmata
87081 Views
How to Map with LiDAR - using a Raspberry Pi Zero 2W, RPLidar and Rviz
57314 Views
Creating a Supercomputer with a Raspberry Pi 5 Cluster and Docker Swarm!
53588 Views
Node-Red Automation, MQTT, NodeMCU & MicroPython
52067 Views
LidarBot
Snaszy NAS a 3D printed NAS for Raspberry Pi
Waveshare CM5 boards
The Best Arduino Robot for Beginners
SMARS Lab upgrade with PyCharm
Chicken Nugget Piano
Mini-Rack 3D Design Tutorial
0h 20m
Using the Raspberry Pi Pico's Built-in Temperature Sensor
0h 24m
Getting Started with SQL
0h 32m
Introduction to the Linux Command Line on Raspberry Pi OS
0h 42m
How to install MicroPython
0h 8m
Wall Drawing Robot Tutorial
0h 22m
Learn Linux from the basics to advanced topics.
Learn how to use a Raspberry Pi Pico
Learn MicroPython the best language for MicroControllers
Learn Docker, the leading containerization platform. Docker is used to build, ship, and run applications in a consistent and reliable manner, making it a popular choice for DevOps and cloud-native development.
Learn how to build SMARS robots, starting with the 3D Printing the model, Designing SMARS and Programming SMARS
Learn how to build robots, starting with the basics, then move on to learning Python and MicroPython for microcontrollers, finally learn how to make things with Fusion 360.
Learn Python, the most popular programming language in the world. Python is used in many different areas, including Web Development, Data Science, Machine Learning, Robotics and more.
Learn how to create robots in 3D, using Fusion 360 and FreeCAD. The models can be printed out using a 3d printer and then assembled into a physical robot.
Learn how to create Databases in Python, with SQLite3 and Redis.
KevsRobots Learning Platform
40% Percent Complete
By Kevin McAleer, 3 Minutes
Module 7 delves into advanced SQL queries in SQLite. You will learn about aggregating data, using grouping and filtering conditions, and working with subqueries and nested queries.
The GROUP BY clause allows us to group rows based on specific columns and perform aggregate functions on those groups.
GROUP BY
# Grouping data example query = ''' SELECT genre, COUNT(*) AS count FROM books GROUP BY genre ''' result = connection.execute(query) data = result.fetchall()
In this example, we group books based on their genre and use the COUNT() function to count the number of books in each genre.
COUNT()
The HAVING clause allows us to filter groups based on specific conditions after the GROUP BY operation.
HAVING
# Filtering grouped data example query = ''' SELECT genre, COUNT(*) AS count FROM books GROUP BY genre HAVING COUNT(*) > 5 ''' result = connection.execute(query) data = result.fetchall()
In this example, we retrieve genres with more than 5 books by using the HAVING clause to filter the groups.
SQLite provides various built-in functions and expressions that allow us to perform calculations and transformations on data within queries.
# Using built-in functions example query = ''' SELECT AVG(price) AS average_price, MAX(price) AS max_price, MIN(price) AS min_price FROM books ''' result = connection.execute(query) data = result.fetchall()
In this example, we calculate the average, maximum, and minimum prices of books using the AVG(), MAX(), and MIN() functions.
AVG()
MAX()
MIN()
Subqueries, also known as nested queries, allow us to use the result of one query within another query.
# Subquery example query = ''' SELECT title, author FROM books WHERE genre IN ( SELECT genre FROM books WHERE year > 2000 ) ''' result = connection.execute(query) data = result.fetchall()
In this example, we retrieve the titles and authors of books that belong to genres found in a subquery. The subquery selects genres of books published after 2000.
SQLite supports advanced SQL techniques, including views, common table expressions (CTEs), and window functions. These techniques can provide powerful ways to structure and manipulate data in queries.
Views are virtual tables based on the result of a query. They allow us to simplify complex queries and reuse query logic.
CTEs are named temporary result sets that we can reference within a query. They provide a convenient way to break down complex queries into smaller, more manageable parts.
Window functions perform calculations across a set of rows in a query result. They allow us to perform calculations such as ranking, row numbering, and cumulative sums.
By mastering advanced SQL queries and techniques, you will have the skills to perform complex data analysis and manipulation within SQLite databases.
< Previous Next >