SQL Basics: Tables, Rows, Columns, and SELECT

SQL (Structured Query Language) is used to work with relational databases. A database stores organized information, usually in related tables.

A table is similar to a spreadsheet, but it has a defined structure:

• A table holds one type of entity, such as customers.
• A row is one complete record, such as one customer.
• A column stores one kind of attribute, such as city.
• A primary key is a column whose value uniquely identifies each row, such as customer_id.

A table is created with CREATE TABLE. Each column needs a name and a data type.

CREATE TABLE customers (  customer_id INTEGER PRIMARY KEY,  customer_name TEXT,  city TEXT);

Here, customer_id is the primary key. INTEGER stores whole numbers, while TEXT stores words and other character data.

Add rows with INSERT INTO:

INSERT INTO customers (customer_id, customer_name, city)VALUES  (101, 'Amina Lee', 'Bristol'),  (102, 'Rafael Cruz', 'Leeds');

Listing column names in the INSERT statement makes it clear which value belongs in each column.

Use SELECT to retrieve data from a table.

SELECT customer_nameFROM customers;

This returns only the customer_name column:

To retrieve several columns, separate them with commas:

SELECT customer_name, cityFROM customers;

To retrieve every column, use *:

SELECT *FROM customers;

SELECT * is useful for quick exploration, but naming the needed columns is usually clearer and avoids retrieving unnecessary data.

Suppose the table contains customers in several cities:

INSERT INTO customers (customer_id, customer_name, city)VALUES  (103, 'Noah Patel', 'Bristol');

To show only customers in Bristol, add a WHERE condition:

SELECT customer_name, cityFROM customersWHERE city = 'Bristol';

Step by step:

1. FROM customers chooses the table.
2. WHERE city = 'Bristol' keeps only matching rows.
3. SELECT customer_name, city chooses the displayed columns.

Result:

Text values are normally enclosed in single quotes. Column and table names are not.