Chapter 2: SQL Basics – Retrieving Data
2.1 Understanding Tables, Columns, and Rows
SQL databases store data in tables, which consist of columns (fields) and rows (records). Each row represents a unique data entry, and each column defines the type of data stored.
Create a Sample Database: employees
CREATE TABLE employees (
id INTEGER PRIMARY KEY IDENTITY(1,1),
name TEXT NOT NULL,
department TEXT NOT NULL,
salary FLOAT NOT NULL
);
Insert Sample Data
INSERT INTO employees (name, department, salary) VALUES
('Alice', 'IT', 80000),
('Bob', 'HR', 60000),
('Charlie', 'IT', 90000),
('David', 'Marketing', 75000);
Table Preview
| ID | Name | Department | Salary |
|---|---|---|---|
| 1 | Alice | IT | 80000 |
| 2 | Bob | HR | 60000 |
| 3 | Charlie | IT | 90000 |
| 4 | David | Marketing | 75000 |
2.2 The SELECT Statement
The SELECT statement retrieves data from a database.
SELECT name, salary FROM employees;
Output:
| Name | Salary |
|---|---|
| Alice | 80000 |
| Bob | 60000 |
| Charlie | 90000 |
| David | 75000 |
2.3 Filtering Data with WHERE Conditions
SELECT name, salary FROM employees WHERE department = 'IT';
Output:
| Name | Salary |
|---|---|
| Alice | 80000 |
| Charlie | 90000 |
2.4 Sorting Results with ORDER BY
SELECT name, salary FROM employees ORDER BY salary DESC;
Output:
| Name | Salary |
|---|---|
| Charlie | 90000 |
| Alice | 80000 |
| David | 75000 |
| Bob | 60000 |
2.5 Using DISTINCT to Remove Duplicates
SELECT DISTINCT department FROM employees;
Output:
| Department |
|---|
| IT |
| HR |
| Marketing |
2.6 Exercise: Basic Data Retrieval
Task 1: Retrieve all columns from the employees table.
SELECT * FROM employees;
Task 2: Select employees whose salary is greater than $75,000 and sort them in descending order of salary.
SELECT name, salary FROM employees WHERE salary > 75000 ORDER BY salary DESC;
Task 3: Get a list of unique department names.
SELECT DISTINCT department FROM employees;
Try running these queries in an SQL editor or interactive database tool to practice retrieving data! This chapter provides a fundamental understanding of retrieving and filtering data using SQL. The next chapter will explore JOINs and relationships between tables.