Saturday, January 25, 2025

SQL Basics


 ebook - Mastering SQL: A Comprehensive Guide to Database Mastery

What is SQL?

Before we go deep into SQL, let's quickly define what a database is.
A database is an organized collection of data that allows for easy access, management, and updating. It serves as the backbone for storing information in countless applications, ranging from small-scale systems like a personal contact list to large-scale systems like an e-commerce website. Databases are managed by specialized software called Database Management Systems (DBMS), which provide tools to efficiently store, retrieve, and manipulate data.

SQL, or Structured Query Language, is the standard language used to interact with these databases. Whether you're inserting new data, retrieving specific information, or updating existing records, SQL serves as the bridge between users and the underlying database. By understanding how databases work and how SQL leverages their structure, you'll gain the ability to manage and analyze data effectively.

SQL is a cornerstone of data management, essential for anyone working with data-driven applications.

Why Learn SQL?

  • Universal Demand: SQL is widely used across industries such as finance, healthcare, e-commerce, and technology.

  • Foundational Skill: It’s a gateway to learning more advanced database and data analysis concepts.

  • High Earning Potential: Proficiency in SQL can lead to lucrative career opportunities.

SQL and Databases

A relational database organizes data into tables with rows and columns. Each table represents an entity, and the relationships between tables are established using keys.

SQL works with various relational databases such as:

  • MySQL: Open-source and widely used for web applications.

  • PostgreSQL: Known for its reliability and advanced features.

  • Microsoft SQL Server: Enterprise-grade database with seamless integration into Microsoft’s ecosystem.

  • Oracle Database: Popular in large-scale enterprise systems.

  • SQLite: Lightweight database used in mobile and small-scale applications.

Career Opportunities with SQL

  • Data Analyst

  • Database Administrator

  • Data Scientist

  • Software Developer

  • Business Intelligence Developer


SQL Basics

Case Sensitivity

SQL keywords are not case-sensitive, which means SELECT and select are treated the same. However, it is a common convention to write SQL keywords in uppercase to improve code readability. For example:

SELECT * FROM employees;
select * from employees;

Both queries return the same result. Table names and column names, on the other hand, may be case-sensitive depending on the database system.

Basic Query Structure

A basic SQL query follows this structure:

SELECT column1, column2, ...
FROM table_name
WHERE condition;

  • SELECT: Specifies the columns to retrieve. You can use * to retrieve all columns.

  • FROM: Specifies the table from which to retrieve data.

  • WHERE: Filters the results based on a condition. Conditions can include comparisons like =, <, >, and logical operators like AND, OR.

Example

Imagine a table named employees with the following columns: id, name, department, and salary. To retrieve the names of all employees who work in the "Sales" department, you would write:

SELECT name
FROM employees
WHERE department = 'Sales';

This query returns only the name column for employees where the department is "Sales".


Common SQL Commands

SQL commands are broadly categorized into four types based on their purpose:

  1. DDL (Data Definition Language):
    DDL commands define and modify the structure of a database. Examples include CREATE, ALTER, DROP, and TRUNCATE. These commands directly affect the schema of the database.

  2. DML (Data Manipulation Language):
    DML commands handle data within tables. Common examples are INSERT, UPDATE, DELETE, and SELECT. These commands focus on manipulating data without altering the database structure.

  3. DCL (Data Control Language):
    DCL commands manage access and permissions. Commands like GRANT and REVOKE control user privileges, ensuring secure database operations.

  4. TCL (Transaction Control Language):
    TCL commands handle transactions in a database. Examples include COMMIT, ROLLBACK, and SAVEPOINT, which ensure data integrity during operations.

Now, let us look at some commonly used SQL commands.

1. SELECT

The SELECT statement is the most commonly used SQL command and is used to retrieve data from a table.

Detailed Explanation:

  • You can specify one or more columns to retrieve by separating them with commas.

  • Use * to retrieve all columns.

  • You can add sorting to the results using ORDER BY.

Syntax:

SELECT column1, column2, ...
FROM table_name;

Example: Retrieve all columns from the employees table:

SELECT *
FROM employees;

Retrieve only the names and departments of employees:

SELECT name, department
FROM employees;

2. INSERT

The INSERT statement is used to add new rows to a table. Each column specified in the INSERT INTO clause must have a corresponding value.

Detailed Explanation:

  • If you omit a column, it must have a default value or allow NULL.

  • You can insert multiple rows in a single query.

Syntax:

INSERT INTO table_name (column1, column2, ...)
VALUES (value1, value2, ...);

Example: Add a new employee to the employees table:

INSERT INTO employees (name, department, salary)
VALUES ('John Doe', 'HR', 50000);

You can also insert multiple rows:

INSERT INTO employees (name, department, salary)
VALUES
('Alice Smith', 'Sales', 60000),
('Bob Johnson', 'Engineering', 70000);

3. UPDATE

The UPDATE statement modifies existing data in a table. Be careful when using UPDATE without a WHERE clause, as it updates all rows.

Detailed Explanation:

  • Use SET to specify the column-value pairs to update.

  • Use the WHERE clause to specify which rows to update.

Syntax:

UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition;

Example: Increase the salary of "John Doe" by 5000:

UPDATE employees
SET salary = salary + 5000
WHERE name = 'John Doe';

4. DELETE

The DELETE statement removes rows from a table. Similar to UPDATE, always use a WHERE clause unless you intend to delete all rows.

Detailed Explanation:

  • Without a WHERE clause, all rows are deleted.

  • Be cautious when deleting data as it is often irreversible.

Syntax:

DELETE FROM table_name
WHERE condition;

Example: Remove employees in the "HR" department:

DELETE FROM employees
WHERE department = 'HR';


SQL Data Types

SQL tables store data in columns, and each column has a specific data type that determines what kind of data it can hold. Here are some commonly used SQL data types:

  • INTEGER: Used for whole numbers. Example: 42.

  • VARCHAR(n): Stores variable-length strings up to a maximum of n characters. Example: 'John'.

  • TEXT: Stores large text fields. Example: 'This is a long description.'.

  • DATE: Stores date values in the format YYYY-MM-DD. Example: 2025-01-14.

  • DECIMAL(p, s): Stores fixed-point numbers. p is the total number of digits, and s is the number of digits after the decimal point. Example: 1234.56 with DECIMAL(6, 2).

Example Table Definition

Here is an example of creating a table with various data types:

CREATE TABLE employees (
    id INTEGER PRIMARY KEY,
    name VARCHAR(100),
    department VARCHAR(50),
    salary DECIMAL(10, 2),
    hire_date DATE
);

This table defines an employees table with:

  • An id column for unique identifiers.

  • A name column for employee names.

  • A department column for the department name.

  • A salary column with a maximum of 10 digits, 2 of which are after the decimal.

  • A hire_date column to store the hiring date.

ALTER TABLE

The ALTER command modifies the structure of an existing table. You can add, delete, or change columns in a table.

Example: Adding a new column to the employees table:

ALTER TABLE employees
ADD phone_number VARCHAR(15);


This adds a phone_number column to store contact numbers for employees.

Example: Modifying the data type of an existing column:

ALTER TABLE employees
MODIFY salary DECIMAL(12, 2);

This changes the salary column to accommodate larger values.


DROP TABLE

The DROP command deletes an entire table, including its structure and data.

Example: Dropping the employees table:

DROP TABLE employees;

This permanently removes the employees table. Use this with caution as the data cannot be recovered.


TRUNCATE TABLE

The TRUNCATE command removes all rows from a table but retains its structure for future use.

Example: Truncating the employees table:

TRUNCATE TABLE employees;

This deletes all records in the table, resetting it for fresh data insertion. Unlike DELETE, it cannot be rolled back and is faster for large datasets.

Constraints in SQL

Constraints are rules applied to columns in a table to enforce data integrity and define how data is stored and managed. Here are some commonly used constraints:

PRIMARY KEY

The PRIMARY KEY uniquely identifies each record in a table. It ensures that no duplicate or NULL values exist in the column.

Example:

CREATE TABLE employees (
    id INTEGER PRIMARY KEY,
    name VARCHAR(100)
);


Here, the id column is the primary key, ensuring unique identifiers for employees.


FOREIGN KEY

The FOREIGN KEY establishes a relationship between two tables by linking a column in one table to the primary key of another table.

Example:

CREATE TABLE departments (
    department_id INTEGER PRIMARY KEY,
    department_name VARCHAR(50)
);

CREATE TABLE employees (
    id INTEGER PRIMARY KEY,
    name VARCHAR(100),
    department_id INTEGER,
    FOREIGN KEY (department_id) REFERENCES departments(department_id)
);


This links employees.department_id to departments.department_id, enforcing referential integrity.


NOT NULL

The NOT NULL constraint ensures that a column cannot have NULL values, making it mandatory to provide a value for that column.

Example:

CREATE TABLE employees (
    id INTEGER PRIMARY KEY,
    name VARCHAR(100) NOT NULL
);

Here, the name column must always have a value.


UNIQUE

The UNIQUE constraint ensures all values in a column are distinct, similar to PRIMARY KEY but allows one NULL.

Example:

CREATE TABLE employees (
    email VARCHAR(100) UNIQUE
);

This ensures that no two employees can have the same email address.


DEFAULT

The DEFAULT constraint sets a default value for a column if no value is provided during insertion.

Example:

CREATE TABLE employees (
    id INTEGER PRIMARY KEY,
    salary DECIMAL(10, 2) DEFAULT 50000.00
);


Here, the salary column defaults to 50,000 if not explicitly provided.


CHECK

The CHECK constraint enforces a condition on the values in a column.

Example:

CREATE TABLE employees (
    id INTEGER PRIMARY KEY,
    salary DECIMAL(10, 2),
    CHECK (salary > 0)
);

This ensures the salary value must be greater than zero.

These constraints help maintain data accuracy, consistency, and reliability within the database.



Hands-On Exercises

Exercise 1: Create a Table

Create a table named products with the following columns:

  • id (INTEGER, Primary Key)

  • name (VARCHAR(100))

  • price (DECIMAL(10, 2))

  • category (VARCHAR(50))

Solution:

CREATE TABLE products (
    id INTEGER PRIMARY KEY,
    name VARCHAR(100),
    price DECIMAL(10, 2),
    category VARCHAR(50)
);

Exercise 2: Insert Data

Insert the following product into the products table:

  • id: 1

  • name: "Laptop"

  • price: 1200.50

  • category: "Electronics"

Solution:

INSERT INTO products (id, name, price, category)
VALUES (1, 'Laptop', 1200.50, 'Electronics');


In this chapter, you learned the basics of SQL, including its syntax and essential commands like SELECT, INSERT, UPDATE, and DELETE. You also explored how to define tables and work with data types. In the next chapter, we will delve into SQL Joins, a powerful feature for combining data from multiple tables.

No comments:

Search This Blog