SQLite
Listed here is my series to learn SQLite. These lessons are perfectly suited for anyone that desires to learn how to query a database.
We start with the basics, using SQLite, and focus on the command that are used retrieve the data. Once you learn the commands, you can practically use any web or GUI tool to create complex queries.
You’ll never feel limited by what you can do, nor frustrated that you’re restricted to a few canned report to rely upon.
You don’t have to be a computer wiz to follow along. I try to use plain English to explain the concepts. You’ll learn SQLite by doing!
Though the courses can help the tech minded, such as developers, I really wrote them for the average computer user.
It is my hope all you HR, Business Analysts, Engineers, and Accountants will find my site, learn SQL, and make your jobs and lives easier.
Latest Posts
-
Understanding SQL COUNT and Other Aggregate Function
We have seen how to use the SQL Count function in a GROUP BY query, but did you know there are several other functions you can use there as well? In this tutorial, let’s look at several other SQL aggregate functions, and how they can be used to summarize your data. For these examples, we’ll
-
Understanding SQL GROUP BY: A Guide to Data Summarization
The SQL Group BY clause is used to summarize your SQL results. Rather than displaying each row in detail, when GROUP BY is added to your query, one row per grouping is returned. Using groups comes in handy when you want to calculate totals. All of the examples in this tutorial are based on PizzaDB.
-
SQL Like
The SQL LIKE operator is used to find specific patterns or values within your data. With its wildcard characters and versatile syntax, you can craft queries that match your precise needs. Whether you are searching for names that start with a certain letter, addresses that contain a specific word, or numbers within a specific range,
-
SQL Conditional Operators
In this lesson we going learn how to use multiple SQL conditional operators with the WHERE clause. You may know of these as logical operators, since they return either TRUE or FALSE. In the past lesson you learned that rows failing to meet the WHERE clause condition are filtered out. The same concept holds to
-
SQL Where Clause
In this lesson we’ll learn how to use the SQL WHERE clause to filter your results. In prior lessons we have talked about using the SELECT statement to return one or more columns, but always it returned every row. What if we want to return a limited set of rows? How can we filter our
-
Select Distinct SQL Tutorial
The SELECT DISTINCT statement is used to return unique rows based on combination of values in the specified columns. When working with data, it’s often helpful to identify unique values rather than displaying duplicates. That’s where the DISTINCT keyword comes in handy! By adding DISTINCT to your SELECT statement, you can remove duplicate records and
-
Master the Art of SQL Query Validation: Best Practices and Essential Tips
Are you looking to master the art of SQL query validation? Look no further! In this article, we will share the best practices and essential tips to help you become a pro at ensuring the accuracy and reliability of your SQL queries. SQL query validation is a critical step in the development process to prevent
-
Master the Art of Creating Tables in SQL: A Step-by-Step Guide to Database Design
Are you ready to unlock the power of SQL and become a master of database design? Look no further! In this comprehensive step-by-step guide, we will take you on a journey through the art of creating tables in SQL. Whether you are a beginner or an experienced programmer, this guide will provide you with the
-
Use ROW_NUMBER to Create a Unique ID
Learn how to use the ROW_NUMBER() windows function to generate a unique ID. Problem: You have a table named Teams with information about various sports teams, including their names, points, and conferences. You want to add a unique identifier to each row in the table to track them easily, but you don’t have a unique
-
Compare Two Moving Averages in SQL
Problem We want to compare two moving averages in SQL. We’ll use window functions to do this, as it is easier write and manage the query using them. Background Moving averages are a commonly used tool in financial analysis that smooths out the price data by averaging the closing prices of a stock over a
-
Find and Remove Duplicates in SQL
Problem You want to remove duplicates in SQL. You know it is easy to find duplicates using GROUP BY, but how do your remove all but one? Background Here’s an example of how to find duplicate records using a window function in SQL: Suppose we have a table of students that includes columns for student
-
How to Find Duplicates using a Windows Function?
Problem You need to find duplicates and then identify the records by their primary keys. Background Here’s an example of how to find duplicate records using a window function in SQL: Suppose we have a table of students that includes columns for student ID, name, and age. We want to find all the students who
-
Calculate a Moving Average in SQL using A Windows Function
Problem How do you create a moving average using windows functions in SQL? Background There is no built-in function to create a moving average, but you can use the AVG() window function to do so. For our example we calculate the 4 week moving average of weekly sales. Here is the table we’ll use: Note:
-
Calculate a Running Total in SQL using a Windows Function
Problem How do you create a running total on a column using SQL? Background There is no built-in function to create a running total, but you can use a windows function to do so. In this case well construction a running total of Weekly Product Sales. For this solution we’ll work with the WinCode WeeklySales
-
Working with ROW_NUMBER, RANK, and DENSE_RANK
Introduction Window functions are an essential tool for data analysis, particularly in the SQL language. They allow you to perform complex calculations, like running totals, moving averages, and ranking values, on a set of data, without the need for subqueries or self-joins. In this article, we’ll explore three popular ranking functions in SQL: ROW_NUMBER(), RANK(),
-
Window Functions vs Group By Queries?
In this article let’s look at window functions vs GROUP BY queries. We’ll do so by looking at how each statement works and giving examples. Though you can use both GROUP BY and window functions to create summary values, the way SQL includes the results are different. Let’s first look at how GROUP BY does
-
SQL Joins – The Ultimate Guide
SQL Joins are important to master. As you progress from a beginner to advanced beginner, you’ll soon need to combine data from more than one table. To do this, you’ll one of several SQL join types. In this series of articles I’ll show you how to write a query that combines, or joins, data from
-
Do Partial Matches Using LIKE
In this lesson you are going to explore how to do pattern matching with the SQL Where clause LIKE operator. Using this phrase allows us perform partial matches of data values and obtain answers to questions which can’t be done with conventional comparisons. The lesson’s objectives are to: learn about the like clause understand wild cards
-
What is SQLite3?
SQLite3 Review SQLite3 is a compact free database you can use easily create and use a database. Though SQLite3 is not a full-featured database, it supports a surprisingly large set of the SQL standard, and is ideal for those just starting to learn SQL as well for developers that need a simple database engine to
-
How to Install SQLite and the Sample Database
This is the first in a series of lessons devoted to teaching you SQL. We are going to install SQLite and then move on to the fundamentals , which means learning the command line. Do worry it is not as hard as it seem, and really, the payoff is huge. For once you learn SQL