Basic SQL Syntax

Before you start writing queries, it is important to understand the writing rules and basic SQL syntax. This knowledge helps you write correct, easy-to-read code.

SQL Is Case Insensitive

SQL is a case-insensitive language, meaning that uppercase and lowercase letters do not affect the execution result. However, by convention, SQL keywords are written in uppercase.

-- Both of these queries produce the same result
SELECT * FROM student;
select * from student;
Select * From Student;

Even though SQL itself does not care about capitalization, writing it consistently makes your code easier to read and maintain.

The Role of the Semicolon

The semicolon (;) is used as a separator between SQL statements. Although the semicolon is optional in some RDBMS like MySQL, it is strongly recommended to avoid conflicts between queries.

SELECT * FROM student;
SELECT * FROM grades;

Without a semicolon, MySQL may execute both statements as one unit, which will cause an error.

Comments in SQL

SQL supports two types of comments:

Single-Line Comments

Use -- for single-line comments. The rest of the text after -- is ignored by the database.

-- This is a single-line comment
SELECT * FROM student; -- Fetch all student data

Multi-Line Comments

Use /* ... */ for comments that span multiple lines.

/*
  This query fetches all student data
  from the student table
  sorted by name
*/
SELECT * FROM student
ORDER BY name;

Comments are very useful for explaining the purpose of complex queries or for temporarily disabling part of the code without deleting it.

SQL Statement Overview

SQL has six main categories of statements based on their function:

Statement	Function	Example
SELECT	Retrieves data from a table	`SELECT * FROM student;`
INSERT	Adds new data to a table	`INSERT INTO student VALUES (1, 'John');`
UPDATE	Modifies existing data	`UPDATE student SET name = 'John';`
DELETE	Deletes data from a table	`DELETE FROM student WHERE id = 1;`
CREATE	Creates a new database or table	`CREATE TABLE student (id INT);`
ALTER	Modifies a table's structure	`ALTER TABLE student ADD email VARCHAR(100);`
DROP	Deletes a database or table	`DROP TABLE student;`

DML (Data Manipulation Language)

DML focuses on manipulating data within tables. The main DML statements are SELECT, INSERT, UPDATE, and DELETE.

-- SELECT: retrieve data
SELECT name, major FROM student WHERE enrollment_year = 2023;

-- INSERT: add data
INSERT INTO student (id, name, major) VALUES (4, 'Dana', 'Information Technology');

-- UPDATE: modify data
UPDATE student SET enrollment_year = 2024 WHERE id = 1;

-- DELETE: remove data
DELETE FROM student WHERE id = 4;

DDL (Data Definition Language)

DDL focuses on database structure. The main DDL statements include CREATE, ALTER, and DROP.

-- CREATE: create a new table
CREATE TABLE lecturer (
    id INT PRIMARY KEY,
    name VARCHAR(100)
);

-- ALTER: add a column
ALTER TABLE lecturer ADD email VARCHAR(100);

-- DROP: delete a table
DROP TABLE lecturer;

DCL (Data Control Language)

DCL focuses on access rights and data control.

-- Grant access rights
GRANT SELECT, INSERT ON student TO 'user1'@'localhost';

-- Revoke access rights
REVOKE INSERT ON student FROM 'user1'@'localhost';

Summary

Understanding SQL writing rules — such as case insensitivity, semicolon usage, comments, and statement categories — is a strong foundation for starting your SQL learning journey. With this base, we are ready to learn the data types available in SQL.

In the next chapter, we will learn about the various data types supported by SQL and how to choose the right data type for each column.