Introduction to SQL
## Learning Objectives
- Understand what SQL is and its purpose
- Learn about relational databases
- Set up a SQL environment
- Write your first SQL query
## What is SQL?
SQL (Structured Query Language) is a standard language for storing, manipulating, and retrieving data in relational databases.
```sql
SELECT first_name, last_name
FROM customers
WHERE city = 'New York';
```
## Relational Databases
### Tables
Data is organized in tables:
```sql
-- Table: employees
+------------+------------+-----------+--------+
| employee_id| first_name | last_name | salary |
+------------+------------+-----------+--------+
| 1 | Alice | Johnson | 75000 |
| 2 | Bob | Smith | 65000 |
| 3 | Carol | Williams | 80000 |
+------------+------------+-----------+--------+
```
### Key Concepts
- **Rows (Records)**: Individual entries
- **Columns (Fields)**: Attributes of data
- **Primary Key**: Unique identifier for each row
- **Foreign Key**: Links to another table
## Database Systems
- **MySQL**: Popular open-source
- **PostgreSQL**: Advanced open-source
- **SQLite**: Lightweight, file-based
- **SQL Server**: Microsoft enterprise
- **Oracle**: Enterprise solution
## Setting Up
### SQLite (Simplest)
```bash
# Install
sudo apt install sqlite3
# Create database
sqlite3 mydatabase.db
# Or use Python
python3 -c "import sqlite3; print(sqlite3.version)"
```
### MySQL
```bash
# Install
sudo apt install mysql-server
# Connect
mysql -u username -p
```
## Your First Query
```sql
-- Select all from a table
SELECT *
FROM employees;
```
```sql
-- Select specific columns
SELECT first_name, last_name
FROM employees;
```
## Database Setup
### Create Database
```sql
CREATE DATABASE company;
USE company;
```
### Create Table
```sql
CREATE TABLE employees (
employee_id INTEGER PRIMARY KEY,
first_name TEXT NOT NULL,
last_name TEXT NOT NULL,
email TEXT UNIQUE,
hire_date DATE,
salary DECIMAL(10, 2)
);
```
### Insert Sample Data
```sql
INSERT INTO employees (employee_id, first_name, last_name, email, hire_date, salary)
VALUES
(1, 'Alice', 'Johnson', 'alice@company.com', '2020-01-15', 75000.00),
(2, 'Bob', 'Smith', 'bob@company.com', '2021-03-22', 65000.00),
(3, 'Carol', 'Williams', 'carol@company.com', '2019-07-10', 80000.00);
```
## SQL Syntax Basics
### Case Sensitivity
- SQL keywords are case-insensitive
- Table and column names vary by database
- Best practice: uppercase keywords, lowercase names
```sql
-- All equivalent
SELECT * FROM employees;
select * from employees;
SeLeCt * FrOm employees;
```
### Comments
```sql
-- Single line comment
/*
Multi-line
comment
*/
```
### Semicolons
```sql
-- Most databases require semicolons
SELECT * FROM employees;
SELECT * FROM departments;
```
## SQL Clauses
| Clause | Purpose |
|--------|---------|
| SELECT | Columns to retrieve |
| FROM | Table to query |
| WHERE | Filter conditions |
| ORDER BY | Sort results |
| GROUP BY | Group rows |
| HAVING | Filter groups |
## Running SQL
### Command Line (SQLite)
```bash
sqlite3 company.db < schema.sql
sqlite3 company.db -header -column < query.sql
```
### Python
```python
import sqlite3
conn = sqlite3.connect('company.db')
cursor = conn.cursor()
cursor.execute("SELECT * FROM employees")
results = cursor.fetchall()
for row in results:
print(row)
conn.close()
```
### DBeaver (GUI)
Free cross-platform database tool.
## Summary
- SQL is the standard language for relational databases
- Data is organized in tables with rows and columns
- Primary keys uniquely identify rows
- SQL syntax: SELECT columns FROM table WHERE conditions
- SQL is case-insensitive for keywords
- Semicolons terminate statements
Comments
Comments powered by Giscus
To enable comments, add your Giscus embed code here.
Learn more about Giscus →