- ✓SQL, Structured Query Language, is the standard language used to create, modify, and query relational databases.
- ✓Data Definition Language commands including CREATE, ALTER, and DROP are used to define and change the structure of database objects.
- ✓Data Manipulation Language commands including INSERT, UPDATE, DELETE, and SELECT are used to manage the data within tables.
- ✓Constraints such as NOT NULL, UNIQUE, CHECK, and FOREIGN KEY are defined in SQL and enforce data integrity rules at the database level.
- ✓Practising SQL through hands-on exercises is the most effective way to build the fluency needed for professional database development work.
Listen to the full episode inside the course. Enrol to access all 80 episodes, plus assignments, tutor support and Student Finance funding.
Start learning →Alex: Today we're building a database using SQL. Sam, SQL is everywhere. What exactly is it and what can it do?
Sam: SQL stands for Structured Query Language, and it's the standard language for interacting with relational databases. It's been around since the 1970s and is one of the most enduringly important languages in computing. Almost every relational database system, including MySQL, PostgreSQL, SQL Server, Oracle, and SQLite, uses SQL, though each has some proprietary extensions on top of the standard.
Alex: What are the main categories of SQL commands?
Sam: There are four main categories. Data Definition Language, or DDL, includes commands for creating and modifying the structure of database objects: CREATE TABLE, ALTER TABLE, and DROP TABLE. Data Manipulation Language, or DML, handles the data itself: INSERT to add records, UPDATE to modify them, DELETE to remove them, and SELECT to query them. Then there's Data Control Language for managing permissions, and Transaction Control Language for managing database transactions.
Alex: Can you walk us through creating a table?
Sam: A CREATE TABLE statement defines the table name, its columns, and the constraints on those columns. You specify each column's name and data type, such as VARCHAR for text, INT for integers, DATE for dates. Then you add constraints: NOT NULL to prevent empty values, UNIQUE to enforce uniqueness, PRIMARY KEY to designate the primary key, and FOREIGN KEY with a REFERENCES clause to establish a relationship to another table. It's quite readable when you get used to it.
Alex: And then populating it?
Sam: The INSERT statement adds rows to a table. You specify the table name and then either list the columns you're inserting into followed by the VALUES, or use a SELECT statement to insert the results of a query into the table. The UPDATE statement modifies existing rows, using a WHERE clause to specify which rows to update. Always be careful with UPDATE; if you forget the WHERE clause, you'll update every row in the table.
Alex: What about querying data with SELECT?
Sam: SELECT is the heart of SQL. At its most basic, SELECT followed by column names FROM a table name retrieves the specified columns from the table. You add a WHERE clause to filter rows based on conditions. ORDER BY sorts the results. GROUP BY aggregates rows with common values, enabling aggregation functions like COUNT, SUM, AVG, MIN, and MAX. And JOIN clauses allow you to combine data from multiple related tables in a single query.
Alex: Joins sound powerful.
Sam: They're the defining feature of relational databases. An INNER JOIN returns rows where there's a match in both tables. A LEFT JOIN returns all rows from the left table and matching rows from the right. Understanding joins is essential for working with any real database, because the data you need is almost always spread across multiple related tables.
Alex: What platforms should students be practising on?
Sam: MySQL and PostgreSQL are both free, widely used, and have excellent documentation and community support. SQLite is embedded in Python and many other tools, making it great for quick practice. There are also excellent online practice platforms like SQLZoo, Mode Analytics, and HackerRank that let you practise SQL queries without any setup.
Alex: Great. So SQL is both the creation tool and the primary way you interact with a relational database throughout its lifetime.
Sam: Exactly. It's a remarkably powerful and enduring tool, and becoming fluent in it is genuinely career-changing.
Alex: Thanks Sam. Next we look at how to test a database against requirements.