SQL Basic Operations: A Detailed Guide

Understanding the fundamentals of SQL for database interaction.

Fundamental SQL Operations

This section covers the essential SQL commands for interacting with a database, from creation to basic data manipulation.

1. CREATE DATABASE

The CREATE DATABASE statement is used to create a new database in your database management system.

CREATE DATABASE database_name;

• CREATE DATABASE: Keyword to initiate database creation.
• database_name: The name you want to assign to your new database.

Example:

CREATE DATABASE company_data;

This command will create a new database named company_data.

2. USE DATABASE

The USE statement selects and sets a specific database as the current working database.

USE database_name;

• USE: Keyword to select a database.
• database_name: The name of the database to use.

Example:

USE company_data;

After this, all subsequent commands will operate on the company_data database.

3. CREATE TABLE

The CREATE TABLE statement defines a new table within the currently selected database, specifying columns, data types, and constraints.

CREATE TABLE table_name (
    column1_name data_type1 constraints,
    column2_name data_type2 constraints,
    ...,
    PRIMARY KEY (one_or_more_columns),
    FOREIGN KEY (column_name) REFERENCES other_table(other_column)
);

Key Components:

• table_name: Name of the new table.
• column_name: Name for each column.
• data_type: Type of data the column will store (e.g., INT, VARCHAR(length), DECIMAL(precision, scale), DATE).
• constraints: Rules to enforce data integrity:
  • PRIMARY KEY: Uniquely identifies each row, cannot be NULL.
  • AUTO_INCREMENT: Automatically assigns sequential integers (often with PRIMARY KEY).
  • NOT NULL: Column cannot contain NULL values.
  • UNIQUE: All values in the column must be distinct.
  • DEFAULT value: Specifies a default value if none is provided.
  • FOREIGN KEY: Establishes a link to another table, enforcing referential integrity.
  • CHECK (condition): Defines a condition values must satisfy.

Example (from prompt):

CREATE TABLE employees (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100),
    department VARCHAR(50),
    salary DECIMAL(10, 2)
);

Data Manipulation Language (DML)

These commands are used for manipulating the data within your tables.

4.1. INSERT

The INSERT INTO statement adds new rows (records) into a table.

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

Example (from prompt):

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

More Examples:

INSERT INTO employees VALUES (NULL, 'Jane Smith', 'HR', 60000);

4.2. SELECT

The SELECT statement retrieves data from one or more tables.

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

Example (from prompt):

SELECT * FROM employees;

More Examples:

-- Select only the name and salary of all employees
SELECT name, salary FROM employees;

-- Select employees in the 'IT' department
SELECT name, salary FROM employees WHERE department = 'IT';

4.3. UPDATE

The UPDATE statement modifies existing data in a table.

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

Caution: Omitting the WHERE clause will update all rows!

Example (from prompt):

UPDATE employees SET salary = 55000 WHERE name = 'John Doe';

More Examples:

-- Increase the salary of all employees in the 'HR' department by 10%
UPDATE employees SET salary = salary * 1.10 WHERE department = 'HR';

4.4. DELETE

The DELETE FROM statement removes one or more rows from a table.

DELETE FROM table_name
WHERE condition;

Caution: Omitting the WHERE clause will delete all rows! This is permanent.

Example (from prompt):

DELETE FROM employees WHERE name = 'John Doe';

More Examples:

-- Delete all employees in the 'Finance' department
DELETE FROM employees WHERE department = 'Finance';

-- Delete all rows from the employees table (use with extreme caution!)
DELETE FROM employees;

Practice Ideas (Detailed)

Further practice to solidify your understanding and explore more advanced concepts.

5.1. Create a table for projects, departments, or students

Apply the CREATE TABLE statement with different data types and constraints.

Example: Creating a projects table:

CREATE TABLE projects (
    project_id INT PRIMARY KEY AUTO_INCREMENT,
    project_name VARCHAR(255) NOT NULL UNIQUE,
    start_date DATE,
    end_date DATE,
    budget DECIMAL(15, 2) CHECK (budget > 0),
    department_id INT,
    FOREIGN KEY (department_id) REFERENCES departments(department_id)
);

Example: Creating a departments table:

CREATE TABLE departments (
    department_id INT PRIMARY KEY AUTO_INCREMENT,
    department_name VARCHAR(50) NOT NULL UNIQUE,
    location VARCHAR(100)
);

5.2. Practice JOINs: INNER JOIN, LEFT JOIN

Combine rows from two or more tables based on related columns.

INNER JOIN:

Returns only rows where there’s a match in both tables.

SELECT employees.name, departments.department_name
FROM employees
INNER JOIN departments ON employees.department_id = departments.department_id;

LEFT JOIN (or LEFT OUTER JOIN):

Returns all rows from the left table, and matching rows from the right. NULL for unmatched right rows.

SELECT employees.name, departments.department_name
FROM employees
LEFT JOIN departments ON employees.department_id = departments.department_id;

5.3. Try GROUP BY, HAVING, and ORDER BY

Summarize, filter, and sort data.

GROUP BY:

Groups rows with same values, often with aggregate functions.

SELECT department_id, COUNT(*) AS number_of_employees
FROM employees
GROUP BY department_id;

HAVING:

Filters the results of a GROUP BY clause.

SELECT department_id, AVG(salary) AS average_salary
FROM employees
GROUP BY department_id
HAVING AVG(salary) > 52000;

ORDER BY:

Sorts the result set (ASC for ascending, DESC for descending).

SELECT name, salary
FROM employees
ORDER BY salary DESC, name ASC;

5.4. Learn to use constraints: UNIQUE, NOT NULL, FOREIGN KEY

Crucial for maintaining data integrity.

Example: Adding a Foreign Key to employees:

-- Create the departments table (if not already done)
CREATE TABLE departments (
    department_id INT PRIMARY KEY AUTO_INCREMENT,
    department_name VARCHAR(50) NOT NULL UNIQUE
);

-- Add a department_id column to the employees table
ALTER TABLE employees
ADD COLUMN department_id INT;

-- Create a foreign key constraint
ALTER TABLE employees
ADD CONSTRAINT fk_department
FOREIGN KEY (department_id) REFERENCES departments(department_id);

-- Update existing employees to assign them to departments
-- (Example: Assuming department_id 1 is 'IT', 2 is 'HR')
UPDATE employees SET department_id = 1 WHERE department = 'IT';
UPDATE employees SET department_id = 2 WHERE department = 'HR';

-- You can then drop the old 'department' column if you wish
-- ALTER TABLE employees
-- DROP COLUMN department;

5.5. Try writing some stored procedures or views

Advanced database objects for organizing and simplifying complex SQL operations.

Stored Procedures:

A set of SQL statements stored in the database, executable by name.

-- Example (MySQL syntax):
DELIMITER //
CREATE PROCEDURE GetEmployeesByDepartment (IN dept_name VARCHAR(50))
BEGIN
    SELECT name, salary
    FROM employees
    WHERE department = dept_name;
END //
DELIMITER ;

-- Call the stored procedure
CALL GetEmployeesByDepartment('IT');

Views:

A virtual table based on the result of a SELECT statement. They simplify complex queries and can restrict data access.

CREATE VIEW IT_Employees AS
SELECT id, name, salary
FROM employees
WHERE department = 'IT';

-- Query the view like a regular table
SELECT * FROM IT_Employees;