SQL: basic queries

SQL (Structured Query Language) is the standard language for working with relational databases. In SQL you write not “how” but “what” you need: you describe what the result should be, and the DBMS itself carries out the search. The examples below use a “students” table (fields id, name, grade, score).

To read data we use SELECT: SELECT name, score FROM students returns the name and score of every student, and SELECT * FROM students returns all fields. A condition is set with WHERE: SELECT name FROM students WHERE grade = 11 AND score >= 80. To sort the result write ORDER BY score DESC (descending). There are also aggregate functions: COUNT(*) is the number of records, AVG(score) the average and MAX(score) the largest value.

Commands that change data: INSERT INTO students (name, grade, score) VALUES (“Ali”, 11, 90) adds a new record; UPDATE students SET score = 95 WHERE name = “Ali” updates an existing one; DELETE FROM students WHERE id = 3 deletes a record. Forgetting WHERE in UPDATE and DELETE is dangerous: the command affects every record in the table. So it is better to check such commands first with SELECT.

In Python an SQLite database is used through the standard sqlite3 module: con = sqlite3.connect("school.db"), then cur = con.execute("SELECT name FROM students") and for row in cur: print(row[0]). After commands that change data, con.commit() is called to save the changes. Do not insert user values into a query as plain text: use the ? placeholder, as in con.execute("SELECT * FROM students WHERE name = ?", (name,)), which protects against SQL injection.

Key takeaways

• In SQL you describe what the result should be, and the DBMS carries out the search.
• SELECT … FROM … WHERE … ORDER BY … is used to select, sort and filter data.
• Functions such as COUNT, AVG and MAX give a summary result.
• INSERT, UPDATE and DELETE add, update and delete data; do not forget WHERE.
• In Python the sqlite3 module is used, and values are passed through the ? placeholder.