SQL: your first database query

A school register, a clinic queue, a banking app, an online shop catalogue: behind each of them sits a database, and the language most often used to get information out of it is SQL. You do not need to be a programmer to write your first query. In this guide we explain what a database and a table are by comparing them with Excel, set up a free practice environment on your computer, and then work through SELECT, FROM, WHERE, ORDER BY, LIMIT, COUNT, SUM, AVG, GROUP BY and a simple JOIN using two small tables. At the end you will find common mistakes, a practice exercise and a checklist.

What a database and a table are

A database is a system for storing data in an orderly way and searching it quickly. The most common kind is the relational database: data is kept in tables, and the tables are linked to one another.

If you have worked with Excel or Google Sheets, many of the ideas will feel familiar:

There are important differences too:

• Strict column types. If the age column is defined as a number, you cannot put “20 years” in it.
• Volume and users. A database is built for millions of records and for many people working with it at the same time.
• The formula lives in the query, not in a cell. You write a query, the database returns the result as a new table, and the original data stays unchanged.

A single table or a set of tables from a database is often called a dataset, especially when the data has been prepared for analysis.

What SQL is and why you need it

SQL (Structured Query Language) is the standard language for working with relational databases. In SQL you describe not “how to search” but “what you want”. For example: “give me the names of the students from Tashkent”. The database works out for itself how to find them quickly.

Many systems speak SQL: SQLite, MySQL, PostgreSQL, Microsoft SQL Server, Oracle and others. Each has its own extras (this is called a “dialect”), but the core commands in this article work almost the same everywhere. One difference is worth mentioning straight away: LIMIT, which caps the number of rows returned, works in SQLite, MySQL and PostgreSQL, whereas Microsoft SQL Server uses TOP instead.

For anyone working in data analysis, SQL is the main everyday tool. An accountant, a manager or a member of staff at a training centre can also use it to answer questions such as “how many new students joined this month” without waiting for a programmer.

A free environment for practice

You learn SQL by writing queries, and you do not need a server to do it.

• SQLite is a free system that keeps the entire database in a single file and needs almost no setting up.
• DB Browser for SQLite is a free, open-source program for opening SQLite databases in a windowed interface and writing queries. Versions for Windows, macOS and Linux are available from its official website (sqlitebrowser.org).
• Online SQL editors (for example, SQLite Online or DB Fiddle) let you practise in the browser without installing anything. Do not upload personal or work data to them.

Creating a database in DB Browser

1. Open the program and click New Database. Give the file a name, for example study_centre.db, and save it.
2. A table-creation window (Edit table definition) opens. Close it with Cancel: we will create the tables with a query.
3. Go to the Execute SQL tab and paste in the script below.
4. Click the Execute all button or press F5.
5. To write the changes to the file, click Write Changes (Ctrl + S). Until you do, nothing is saved to the file.
6. On the Browse Data tab, check that the tables have appeared.

CREATE TABLE courses (
  id INTEGER PRIMARY KEY,
  title TEXT,
  duration_weeks INTEGER
);

CREATE TABLE students (
  id INTEGER PRIMARY KEY,
  name TEXT,
  city TEXT,
  age INTEGER,
  course_id INTEGER,
  score INTEGER
);

INSERT INTO courses VALUES
  (1, 'Computer literacy', 8),
  (2, 'English language', 12),
  (3, 'Data analytics', 10),
  (4, 'Mobile photography', 6);

INSERT INTO students VALUES
  (1, 'Dilnoza', 'Tashkent', 19, 3, 88),
  (2, 'Jasur', 'Samarkand', 24, 1, 75),
  (3, 'Malika', 'Namangan', 17, 2, 92),
  (4, 'Sardor', 'Tashkent', 31, 3, 64),
  (5, 'Nodira', 'Bukhara', 22, 1, 81),
  (6, 'Bekzod', 'Kokand', 27, 2, NULL),
  (7, 'Gulnora', 'Samarkand', 35, 3, 79),
  (8, 'Otabek', 'Tashkent', 20, 4, 70);

This is a hypothetical training centre: the names, ages and scores are made up. course_id points to id in the courses table. Bekzod has not been given a score yet, so it is NULL, meaning “no value”. We avoid spaces and apostrophes in table and column names (students, course_id), which prevents a lot of trouble.

If your data is in Excel, save it as CSV and turn it into a table in DB Browser via File → Import → Table from CSV file.

First queries: SELECT, FROM, WHERE

SELECT and FROM

The simplest query has two parts: SELECT says which columns you need, and FROM says which table they come from.

SELECT name, city FROM students;

The result is the name and city of all eight students. An asterisk selects every column: SELECT * FROM students;, but in large tables list only the ones you need. You do not have to write commands in capitals, but it makes them easier to read. The semicolon separates one query from the next.

WHERE: choosing rows by a condition

WHERE does the job of a filter in Excel: it keeps only the rows that match a condition.

SELECT name, city FROM students
WHERE city = 'Tashkent';

Text values go in single quotes, numbers without quotes: WHERE age > 25 leaves Sardor (31), Bekzod (27) and Gulnora (35). The comparison operators are =, <> (not equal), >, <, >= and <=.

Several conditions: AND, OR, IN, LIKE

• WHERE city = 'Samarkand' AND age < 30: both conditions must be true. Result: Jasur.
• WHERE city = 'Bukhara' OR city = 'Namangan': at least one. Result: Malika and Nodira.
• WHERE city IN ('Bukhara', 'Namangan'): a shorter way to write the previous query.
• WHERE age BETWEEN 18 AND 25: a range, with both limits included.
• WHERE name LIKE 'S%': names starting with “S”. % stands for any number of characters. Result: Sardor.

When you mix AND and OR, use brackets: WHERE (city = 'Bukhara' OR city = 'Namangan') AND age > 18. Without them the query may return something other than what you expect.

Sorting and limiting: ORDER BY, LIMIT

ORDER BY sorts the result: ASC is ascending (the default) and DESC is descending. LIMIT caps the number of rows returned. Together they answer questions such as “who are the top three”:

SELECT name, score FROM students
ORDER BY score DESC
LIMIT 3;

ORDER BY city, name sorts by city first, then by name. Where NULL ends up depends on the system: in SQLite it counts as the smallest value, so with DESC it comes last.

An important rule: without ORDER BY, the order of rows is not guaranteed. If order matters, always state it explicitly.

Calculations: COUNT, SUM, AVG and GROUP BY

Aggregate functions

Aggregate functions produce a single result from many rows, much like the SUM, AVERAGE and COUNT formulas in Excel.

SELECT COUNT(*) AS total,
       COUNT(score) AS with_score,
       ROUND(AVG(score), 1) AS avg_score,
       MAX(score) AS top_score
FROM students;

COUNT(*) counts all rows, giving 8, while COUNT(score) counts only the rows that have a score, giving 7, because Bekzod’s is NULL. AVG also ignores NULL and averages over seven students. AS names the column, and ROUND(..., 1) rounds to one decimal place.

SUM adds values up: SELECT SUM(duration_weeks) FROM courses; shows that all the courses together last 36 weeks. MIN and MAX give the smallest and largest values.

GROUP BY: calculating per group

GROUP BY gathers rows with the same value into groups and calculates a result for each, similar to a pivot table in Excel. How many students are there in each city?

SELECT city, COUNT(*) AS cnt
FROM students
GROUP BY city
ORDER BY cnt DESC, city;

The rule: SELECT should contain only the grouped column and aggregate functions. If you add the ungrouped name, most systems will return an error, while SQLite will show an arbitrary name from the group, which is even more dangerous because the mistake stays hidden.

HAVING: filtering groups

WHERE filters rows before grouping, and HAVING filters the finished groups. Courses with at least two students:

SELECT course_id, COUNT(*) AS cnt
FROM students
GROUP BY course_id
HAVING COUNT(*) >= 2;

Courses 1, 2 and 3 remain, while course 4, where only Otabek studies, drops out.

Linking two tables: the idea of JOIN

The students table holds not the course title but only its number (course_id). If the title were repeated in every row, changing it would mean fixing hundreds of places. So the title is stored once in the courses table, and JOIN brings the two tables together when needed, rather like VLOOKUP in Excel.

SELECT s.name, c.title
FROM students AS s
JOIN courses AS c ON s.course_id = c.id
WHERE c.title = 'Data analytics';

s and c are short names for the tables. ON s.course_id = c.id is the linking condition: rows are paired where the student’s course_id equals the course’s id. s.name and c.title show which table each column comes from.

Combine JOIN, GROUP BY and aggregates and you get a real report:

SELECT c.title,
       COUNT(*) AS students_cnt,
       ROUND(AVG(s.score), 1) AS avg_score
FROM students AS s
JOIN courses AS c ON s.course_id = c.id
GROUP BY c.title
ORDER BY students_cnt DESC, c.title;

Look at the “English language” row: there are two students, but the average is Malika’s score alone, because Bekzod’s is NULL. In a real report this should never be left unexplained.

A plain JOIN (in full, INNER JOIN) returns only rows that have a match in both tables. To see courses without any students as well, you use LEFT JOIN, which is a topic for the next step.

Common mistakes

• Checking with = NULL. WHERE score = NULL returns no rows at all, because NULL means “unknown” and is not equal to anything, not even itself. The correct form is WHERE score IS NULL or WHERE score IS NOT NULL. In our table the first one returns Bekzod.
• Mixing up quotes. Text goes in single quotes: 'Tashkent'. Double quotes ("...") are meant, by the standard, for table and column names.
• Apostrophes in Uzbek Latin script. If data is stored in Uzbek Latin, the query 'Qo'qon' will break: SQL decides the text ends after “Qo” and returns an error. An apostrophe inside text is written twice: 'Qo''qon'. The same applies to names such as O’g’iloy or Sa’dulla.
• Letter case. In SQLite, WHERE city = 'tashkent' finds nothing, because the database stores “Tashkent” with a capital letter. LIKE, on the other hand, is case-sensitive in some systems and not in others.
• DELETE or UPDATE without WHERE. DELETE FROM students; deletes every row in the table, and UPDATE students SET score = 0; sets everyone’s score to zero. The system will not ask “Are you sure?”. The safe routine: first write a SELECT with exactly the same condition to see which rows will change, then swap it for DELETE. Back up any important database first. In DB Browser, Revert Changes undoes changes that have not been written yet, but a work server may have no such button.
• Clause order. The order in which you write clauses is fixed: SELECT, FROM, JOIN, WHERE, GROUP BY, HAVING, ORDER BY, LIMIT. Put WHERE after GROUP BY and you will get an error.
• Ignoring error messages. “no such column” or “syntax error” points to where the problem is: most often a typo in a name, an extra comma or an unclosed quote.

Practice exercise and checklist

Using the database created by the script above, write the following queries yourself. Guess the result first, then run the query and compare.

1. The names and ages of the students from Samarkand.
2. Students aged 18 to 25, sorted by age in ascending order.
3. The number of students with a score above 80.
4. Each course title and the highest score in it (JOIN, GROUP BY, MAX).
5. The student without a score and the title of their course.
6. A harder one: add a new course to the courses table (INSERT INTO), then read about LEFT JOIN and write a query that also shows courses with no students.

Answers to check against: 1) Jasur 24, Gulnora 35; 2) Dilnoza 19, Otabek 20, Nodira 22, Jasur 24; 3) 3; 4) Computer literacy 81, Data analytics 88, English language 92, Mobile photography 70; 5) Bekzod, English language.

Then move on to your own data: for example, load a list of the books in your home library or the hours of training at a sports club into SQLite via CSV and write queries to answer your own questions.

Checklist before running a query

• The columns you need are listed explicitly; SELECT * is for checking only.
• Text is in single quotes, with any apostrophe inside doubled.
• NULL is checked only with IS NULL or IS NOT NULL.
• Where AND and OR are mixed, brackets are in place.
• If order matters, ORDER BY is there.
• With GROUP BY, SELECT contains only grouped columns and aggregates.
• Before DELETE or UPDATE, a SELECT with the same condition has been run and a backup made.
• The result makes sense: the number of rows and the values are within the expected range.

A query of a few lines can replace a long stretch of manual work in Excel. If you practise for 15–20 minutes a day, the core commands will soon become second nature.

If you would like to learn SQL, Excel and data analysis systematically with a teacher, take a look at our association’s free data analytics programme and other training programmes. When you are ready, submit an application and our specialists will get in touch.