CodeToLive

SQL Basics

SQL (Structured Query Language) is the standard language for managing and manipulating relational databases. It allows you to create, read, update, and delete data in a structured way.

What is a Relational Database?

A relational database organizes data into tables with rows and columns. Tables can be related to each other through common fields, enabling complex data relationships. 


-- Example of a simple table creation
CREATE TABLE customers (
    customer_id INT PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    email VARCHAR(100),
    registration_date DATE
);

Basic SQL Commands

SQL commands can be categorized into several types based on their functionality:

SQL Syntax Basics

SQL statements follow a specific syntax structure. Most statements start with a command keyword (like SELECT, INSERT, UPDATE) followed by clauses that specify what data to work with. 


-- Basic SELECT statement
SELECT column1, column2 
FROM table_name
WHERE condition
ORDER BY column1;

-- Basic INSERT statement
INSERT INTO table_name (column1, column2)
VALUES (value1, value2);

-- Basic UPDATE statement
UPDATE table_name
SET column1 = value1
WHERE condition;

-- Basic DELETE statement
DELETE FROM table_name
WHERE condition;

Data Types in SQL

SQL supports various data types to store different kinds of information:

Creating Your First Database

Let's walk through creating a simple database with two related tables: 


-- Create a database
CREATE DATABASE bookstore;

-- Use the database
USE bookstore;

-- Create authors table
CREATE TABLE authors (
    author_id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL,
    country VARCHAR(50),
    birth_date DATE
);

-- Create books table with foreign key to authors
CREATE TABLE books (
    book_id INT PRIMARY KEY AUTO_INCREMENT,
    title VARCHAR(200) NOT NULL,
    author_id INT,
    publication_date DATE,
    price DECIMAL(10, 2),
    FOREIGN KEY (author_id) REFERENCES authors(author_id)
);

Inserting Sample Data


-- Insert data into authors table
INSERT INTO authors (name, country, birth_date)
VALUES 
    ('J.K. Rowling', 'United Kingdom', '1965-07-31'),
    ('George R.R. Martin', 'United States', '1948-09-20'),
    ('Agatha Christie', 'United Kingdom', '1890-09-15');

-- Insert data into books table
INSERT INTO books (title, author_id, publication_date, price)
VALUES 
    ('Harry Potter and the Philosopher''s Stone', 1, '1997-06-26', 12.99),
    ('A Game of Thrones', 2, '1996-08-01', 14.99),
    ('Murder on the Orient Express', 3, '1934-01-01', 9.99);

Querying Data

The SELECT statement is used to retrieve data from one or more tables: 


-- Simple query to get all books
SELECT * FROM books;

-- Query with WHERE clause
SELECT title, price 
FROM books 
WHERE price > 10.00;

-- Query with JOIN
SELECT b.title, a.name AS author, b.price
FROM books b
JOIN authors a ON b.author_id = a.author_id;

Advantages of SQL

Common Database Systems

While SQL is the standard language, there are several popular database management systems:

Back to Tutorials