Embedded Files
PROGRAMMING FOR THE WEB: SQLITE
Introduction to SQL
Introduction to SQL
In this activity, we'll dive deep into SQL (Structured Query Language), the standard language for interacting with relational databases. We'll explore various SQL commands, their structure, and how they're used to manipulate data.
SQL is a powerful language that allows you to communicate with databases. It's used to create, read, update, and delete data in a database.
Basic Database Concepts
Basic Database Concepts
Before we begin, let's make sure we understand some key database basics:
Database: A collection of organised data stored and accessed electronically.
Table: In a database, data is organised into tables. Think of a table like a spreadsheet.
Row: A single record in a table. For example, all information about one specific movie.
Column: A field in a table. For example, 'title' or 'year' in a movies table.
Primary Key: A unique identifier for each row in a table, usually an ID number.
SQL Syntax
SQL Syntax
SQL keywords are typically written in ALL CAPS.
Each statement ends with a semicolon (;).
Strings (text) are enclosed in single quotes ('').
Basic SQL Commands
Basic SQL Commands
1. Creating Tables
1. Creating Tables
Tables are the foundation of a relational database. We use the CREATE TABLE statement to define a new table:
CREATE TABLE books (
id INTEGER PRIMARY KEY,
title TEXT NOT NULL,
author TEXT,
year INTEGER,
genre TEXT
);
❓Explanation:
CREATE TABLE is followed by the table name. It tells SQL to make a new table.
In parentheses, we list each column with its name and data type (e.g. TEXT, INTEGER).
PRIMARY KEY is a unique identifier for each row.
NOT NULL means this field must always have a value.
2. Inserting Data
2. Inserting Data
To add new records to a table, we use the INSERT INTO statement:
INSERT INTO books (title, author, year, genre)
VALUES ('To Kill a Mockingbird', 'Harper Lee', 1960, 'Fiction');
INSERT INTO books (title, author, year, genre)
VALUES ('1984', 'George Orwell', 1949, 'Science Fiction');
❓Explanation:
INSERT INTO tells SQL we're adding data.
We list the columns we're filling.
VALUES contains the data we're inserting, in the same order.
3. Selecting Data
3. Selecting Data
The SELECT statement is used to retrieve data from the database:
-- Select all columns and rows
SELECT * FROM books;
-- Select specific columns
SELECT title, author FROM books;
-- Select with a condition
SELECT title, year FROM books WHERE year > 1950;
-- Order the results
SELECT title, year FROM books ORDER BY year DESC;
-- Limit the number of results
SELECT title FROM books LIMIT 5;
❓Explanation:
SELECT chooses what data we want.
* means "all columns".
FROM specifies which table to use.
WHERE filters the results based on a condition.
ORDER BY sorts the results. DESC means descending order.
LIMIT restricts the number of rows returned.
4. Updating Data
4. Updating Data
To modify existing data, we use the UPDATE statement:
UPDATE books
SET genre = 'Dystopian Fiction'
WHERE title = '1984';
❓Explanation:
UPDATE tells SQL we're changing existing data.
SET clause specifies which columns to update and their new values.
WHERE determines which rows to update.
5. Deleting Data
5. Deleting Data
To remove data from a table, we use the DELETE statement:
DELETE FROM books
WHERE year < 1900;
❓Explanation:
DELETE FROM removes rows from a table. It is followed by the table name.
WHERE clause specifies which rows to delete. Be careful! Without a WHERE clause, all rows will be deleted.
6. More Advanced Queries
6. More Advanced Queries
SQL allows for more complex operations:
-- Count the number of books
SELECT COUNT(*) FROM books;
-- Group books by genre and count them
SELECT genre, COUNT(*) AS book_count
FROM books
GROUP BY genre;
-- Find the average year of books for each author
SELECT author, AVG(year) AS avg_year
FROM books
GROUP BY author;
-- Join data from multiple tables
SELECT books.title, authors.name
FROM books
JOIN authors ON books.author_id = authors.id;
❓Explanation:
COUNT(), AVG() are aggregate functions that perform calculations on data.
GROUP BY groups rows that have the same values in specified columns.
JOIN combines rows from two or more tables based on a related column between them.
Activity - Movie Database
Activity - Movie Database
Head over to an SQL Playground. E.g. sqlfiddle.com
Create a table called movies
Add columns for id, title, director, year, and rating.
Insert at least 5 of your favorite movies into the table.
Write a query to select all movies released after 2000.
Update the rating of one of your movies.
Write a query to find the average rating of movies for each director.
Delete any movies that have a rating below 3.
Some sample movies to help you out:
Page updated
Report abuse