Comprehensive Guide to Relational Database Concepts and SQL Syntax

This tutorial introduces relational database terminology such as database, table, schema, column, row, and primary key, and explains their basic definitions.

It outlines the structure of SQL statements, describing clauses, expressions, predicates, queries, and statements, and notes that SQL keywords are case‑insensitive while identifiers may be case‑sensitive depending on the DBMS.

The Data Definition Language (DDL) section covers CREATE, ALTER, and DROP commands for databases, tables, and views, with examples like CREATE DATABASE test; and CREATE TABLE user (id INT UNSIGNED NOT NULL, username VARCHAR(64) NOT NULL DEFAULT 'default'); .

The Data Manipulation Language (DML) part explains INSERT, UPDATE, DELETE, and SELECT operations, showing syntax such as INSERT INTO user (username, password) VALUES ('admin','admin'); , UPDATE user SET username='robot' WHERE username='root'; , and DELETE FROM user WHERE username='robot'; .

Query filtering is demonstrated with WHERE, IN, BETWEEN, AND/OR/NOT, and LIKE clauses, including examples like SELECT * FROM products WHERE vend_id IN ('DLL01','BRS01'); and SELECT prod_name FROM products WHERE prod_name LIKE '%bean bag%'; .

Join operations are described, covering INNER JOIN, LEFT/RIGHT JOIN, NATURAL JOIN, and self‑joins, with sample code such as SELECT vend_name, prod_name FROM vendors INNER JOIN products ON vendors.vend_id = products.vend_id; . The article also compares joins with UNION set operations.

Function usage is illustrated for text processing (e.g., SELECT * FROM mytable WHERE SOUNDEX(col1) = SOUNDEX('apple'); ), date/time handling ( SELECT NOW(); ), and numeric aggregation ( SELECT AVG(DISTINCT col1) AS avg_col FROM mytable; ).

Aggregation and sorting are covered with GROUP BY, HAVING, and ORDER BY clauses, showing how to group rows and filter grouped results, for example SELECT cust_name, COUNT(*) AS addr_num FROM Customers GROUP BY cust_name HAVING COUNT(*) >= 1 ORDER BY cust_name DESC; .

Database constraints (NOT NULL, UNIQUE, PRIMARY KEY, FOREIGN KEY, CHECK, DEFAULT) and index creation ( CREATE INDEX user_index ON user(id); ) are explained, along with their impact on performance.

Transaction control commands (START TRANSACTION, SAVEPOINT, ROLLBACK TO, COMMIT) are presented with a multi‑step example, and permission management using GRANT and REVOKE is demonstrated. Finally, the guide covers stored procedures, cursors, and triggers, providing sample definitions such as CREATE PROCEDURE proc_adder(IN a INT, IN b INT, OUT sum INT) BEGIN SET sum = a + b; END; and CREATE TRIGGER trigger_insert_user AFTER INSERT ON user FOR EACH ROW BEGIN INSERT INTO user_history(user_id, operate_type, operate_time) VALUES (NEW.id, 'add a user', NOW()); END; .