SQL Introduction
SQL is the language you use to work with data stored in a relational database.
What is SQL?
- SQL stands for Structured Query Language
- It lets you read data from a database
- It lets you add, change and delete data
- It lets you create new databases and tables
- It is an international standard (ANSI and ISO), so the same basic commands work in MySQL, PostgreSQL, SQL Server, Oracle and SQLite
Many people pronounce it "S-Q-L", and many say "sequel". Both are fine.
What is a Relational Database?
A relational database keeps data in tables. A table looks like a sheet in Excel:
- Each column holds one kind of information, such as a name or a city
- Each row (also called a record) holds the information about one thing, such as one student
- Tables can be related to each other through shared values, which is where the word "relational" comes from
The software that stores the tables and runs your SQL is called a DBMS (Database Management System). MySQL, PostgreSQL and SQL Server are all examples.
The Sample Database
This tutorial uses one small database for an IT institute. It has two tables.
The students table:
| id | name | city | age | marks | course_id |
|---|---|---|---|---|---|
| 1 | Ali Khan | Karachi | 19 | 78 | 1 |
| 2 | Ayesha Siddiqui | Lahore | 21 | 85 | 2 |
| 3 | Bilal Ahmed | Karachi | 22 | 64 | 1 |
| 4 | Fatima Noor | Islamabad | 20 | 92 | 3 |
| 5 | Hamza Raza | Karachi | 18 | 70 | NULL |
| 6 | Sana Iqbal | Lahore | 23 | 88 | 2 |
The courses table:
| id | title | duration_months |
|---|---|---|
| 1 | Web Development | 6 |
| 2 | Python Programming | 4 |
| 3 | Graphic Designing | 3 |
| 4 | Digital Marketing | 3 |
The course_id column in students points to the id column in courses. So Ali Khan (course_id 1) studies Web Development. Hamza Raza has NULL, which means "no value": he has not chosen a course yet.
Keep these two tables in mind. Every example in the following lessons runs on exactly this data, so you can check each result by looking back here.
Your First Query
A query is a request sent to the database. This one asks for every row and every column of the courses table:
Example
SELECT * FROM courses;| id | title | duration_months |
|---|---|---|
| 1 | Web Development | 6 |
| 2 | Python Programming | 4 |
| 3 | Graphic Designing | 3 |
| 4 | Digital Marketing | 3 |
The * means "all columns". This query asks only for the names of the courses that last 3 months:
Example
SELECT title FROM courses WHERE duration_months = 3;| title |
|---|
| Graphic Designing |
| Digital Marketing |
Even without any training, you can almost read it as English: "select the title from courses where the duration is 3 months".
What Can SQL Do?
| Task | SQL command |
|---|---|
| Read data | SELECT |
| Add new rows | INSERT |
| Change rows | UPDATE |
| Remove rows | DELETE |
| Create a table | CREATE TABLE |
| Change a table | ALTER TABLE |
| Remove a table | DROP TABLE |
Where Do I Run SQL?
You need a database system to run SQL. Good free options are MySQL, PostgreSQL and SQLite. Online playgrounds, such as DB Fiddle, also let you type a query in the browser. The commands in this tutorial are standard SQL and run on all of them.
Exercise
Exercise
In a database table, what is a row?