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
35% Percent Complete
By Kevin McAleer, 3 Minutes
Module 6 focuses on more advanced querying techniques in SQLite, including working with multiple tables and performing joins. You will learn how to retrieve data from multiple tables, establish relationships, and use join operations to combine data.
Real-world databases often involve multiple tables that are related to each other. To retrieve meaningful data, we need to understand how to work with these tables collectively.
Tables can be related to each other through common attributes or columns. Common types of relationships include:
Understanding these relationships allows us to retrieve related data effectively.
To combine data from multiple tables, we use SQL join operations. SQLite supports several types of joins, including inner joins, outer joins, left joins, and right joins.
The inner join returns only the records that have matching values in both tables being joined.
# Inner join example query = ''' SELECT books.title, authors.name FROM books INNER JOIN authors ON books.author_id = authors.id ''' result = connection.execute(query) data = result.fetchall()
In this example, we join the “books” and “authors” tables based on the common column “author_id” and retrieve the book titles along with the corresponding author names.
Outer joins return all records from one table and the matching records from the other table. If no match is found, NULL values are filled in for the missing data.
# Left outer join example query = ''' SELECT books.title, authors.name FROM books LEFT OUTER JOIN authors ON books.author_id = authors.id ''' result = connection.execute(query) data = result.fetchall()
In this example, we perform a left outer join between the “books” and “authors” tables, retrieving all books and the corresponding author names. If there is no matching author, NULL is returned for the author name.
We can join multiple tables together to retrieve data across related tables.
# Joining multiple tables example query = ''' SELECT books.title, authors.name, genres.genre_name FROM books INNER JOIN authors ON books.author_id = authors.id INNER JOIN genres ON books.genre_id = genres.id ''' result = connection.execute(query) data = result.fetchall()
In this example, we join the “books,” “authors,” and “genres” tables, retrieving book titles along with author names and genre names.
To make SQL queries more readable, we can use table aliases to provide shorthand names for tables.
# Using table aliases example query = ''' SELECT b.title, a.name FROM books AS b INNER JOIN authors AS a ON b.author_id = a.id ''' result = connection.execute(query) data = result.fetchall()
In this example, we use the aliases “b” and “a” for the “books” and “authors” tables, respectively, to simplify the query.
By understanding how to work with multiple tables and perform joins, you will be able to retrieve and combine data from various sources, providing more comprehensive insights from your databases.
< Previous Next >