Working with SQL Queries

Creating and Modifying a Database

Before we can store any data, we first need to create the database structure itself. This is what Data Definition Language (DDL) is used for. Its purpose is to create individual tables with their columns (attributes), data types, and relationships.

Commands

CREATE - creates a table or database
DROP - deletes a table or database (including its data)
ALTER - modifies an existing structure (e.g. adding a column)
TRUNCATE - deletes all records from a table

Basic Data Types and Keys

INT – a whole number (often used for IDs, ages, and counts).
VARCHAR(n) – text with a variable length of up to n characters (e.g. a name or email).
DATE – a calendar date (year, month, day).
DECIMAL / FLOAT – a decimal number (e.g. an average grade or price).
PRIMARY KEY (PK) – the primary key; a unique identifier for each row in a table.
FOREIGN KEY (FK) – the foreign key; refers to the primary key in another table and creates a relationship.

-- 1. CREATE: Creating tables with their columns
CREATE TABLE students (
    id INT PRIMARY KEY,
    name VARCHAR(50) NOT NULL
);

CREATE TABLE subjects (
    id INT PRIMARY KEY,
    name VARCHAR(50) NOT NULL
);

CREATE TABLE grades (
    id INT PRIMARY KEY,
    student_id INT,
    subject_id INT,
    value INT NOT NULL,
    FOREIGN KEY (student_id) REFERENCES students(id),
    FOREIGN KEY (subject_id) REFERENCES subjects(id)
);

CREATE TABLE grade_notes (
    id INT PRIMARY KEY,
    grade_id INT,
    text VARCHAR(255),
    FOREIGN KEY (grade_id) REFERENCES grades(id)
);

-- 2. DROP: Deleting an unnecessary table
DROP TABLE grade_notes;

-- 3. ALTER: Moving the note column directly into the grades table
ALTER TABLE grades ADD COLUMN note VARCHAR(255);

Records in a Database

Once the tables exist, we can insert specific content into them and then work with it. Individual records form the rows of a table.

Writing and Modifying Data

Data Manipulation Language (DML) is responsible for inserting, modifying, and deleting rows in existing tables.

Commands

INSERT INTO - inserts a row
UPDATE - modifies a row
DELETE - deletes a row

Modifying and deleting data is usually done together with the WHERE clause. Without it, all rows in the table will be affected.

-- 1. INSERT: Inserting data into tables
INSERT INTO students (id, name) VALUES (1, 'John Smith');
INSERT INTO students (id, name) VALUES (2, 'Emily Johnson');

INSERT INTO subjects (id, name) VALUES (101, 'Computer Science');

INSERT INTO grades (id, student_id, subject_id, value, note)
VALUES (1, 1, 101, 1, 'Excellent project');

-- 2. UPDATE: Correcting a typo or changing a value
UPDATE grades
SET value = 2, note = 'Correction after the test'
WHERE id = 1;

-- 3. DELETE: Deleting a specific record
DELETE FROM grades WHERE id = 1;

Reading and Searching Data

By far the most common activity when working with databases is searching for and reading information. This is what Data Query Language (DQL) is used for, with the SELECT command at its core. We can display all columns using the asterisk *, or only specific columns by listing their names. Results can also be filtered using conditions, grouped into summaries, or combined from several related tables.

SELECT Statement Clauses

When writing a query, it is important to follow the correct order of clauses:

SELECT – specifies the output columns or calculations.
- Aggregate functions: Used to process a whole set of rows into a single resulting value. The most common ones include COUNT() (counts the number of records), AVG() (calculates the arithmetic average), SUM() (adds values together), MIN() and MAX() (finds the smallest or largest value). To learn how to work with these functions, see: GeeksforGeeks – Aggregate Functions in SQL
FROM – specifies the source table from which we retrieve the data.
- JOIN: Allows us to combine multiple tables into a single result based on a relationship between a primary key and a foreign key. The most commonly used types of joins are INNER, RIGHT, and LEFT. You can learn how to use them in the article: W3Schools – SQL JOIN Guide
WHERE – a basic filter that selects only rows matching the specified conditions before any grouping takes place.
GROUP BY – groups rows with the same value into logical groups (e.g. grouping grades by student or subject so that we can use aggregate functions on them).
HAVING – works as a filter for the groups created after using GROUP BY (e.g. show only students whose average grade is better than 1.5).
ORDER BY – sorts the results in ascending order (ASC, the default) or descending order (DESC).
LIMIT – determines the maximum number of records displayed.

SELECT
    COUNT(*) AS total_grades,
    AVG(value) AS overall_average
FROM grades;

SELECT
    students.name,
    grades.value AS grade
FROM students
LEFT JOIN grades ON students.id = grades.student_id;

SELECT
    students.name,
    AVG(grades.value) AS average_grade,
    COUNT(grades.id) AS number_of_grades
FROM students
INNER JOIN grades ON students.id = grades.student_id
WHERE grades.value IS NOT NULL
GROUP BY students.name
HAVING COUNT(grades.id) >= 2
ORDER BY average_grade ASC
LIMIT 3;

Detailed information can always be found in the documentation or in clear tutorials such as the ones provided by W3Schools.

Advanced Database Operations

Transaction Control Language - TCL

For more complex changes where no error can occur, we can use transactions. They allow us to return the data to its original state if something goes wrong, while a successful change is permanently saved to the database.

COMMIT – permanently saves all changes made within a transaction.
ROLLBACK – returns the database to the state it was in before the transaction started and cancels unsaved changes.
SAVEPOINT – creates a checkpoint inside a transaction that we can return to without cancelling the entire process.

For a deeper understanding of transactions, I recommend the article GeeksforGeeks – SQL Commands (DDL, DQL, DML, DCL, TCL).

Data Control Language - DCL

This is used to manage security and access rights. It determines who is allowed to view data and who has permission to modify or delete it.

GRANT – gives a user or role a specific permission (e.g. permission to use SELECT or UPDATE).
REVOKE – removes or cancels a previously granted permission.

The basics of managing users and roles are clearly explained in W3Schools – SQL Hosting & Database Privileges.