Learn SQL Syntax, CRUD Operations, Data Types & Commands for Beginners - Textnotes

Learn SQL Syntax, CRUD Operations, Data Types & Commands for Beginners

Master the fundamentals of SQL with this beginner-friendly guide. Learn SQL syntax, CRUD operations (Create, Read, Update, Delete), essential data types like INT, VARCHAR, DATE, DECIMAL, BOOLEAN, and key commands such as CREATE, SELECT, INSERT, UPDATE, and DELETE. Practice by creating an Employees table and performing real SQL operations.

1. What is SQL?

SQL (Structured Query Language) is the standard language used to interact with relational databases. It allows you to create, read, update, and delete data efficiently.

Key Points:

  1. SQL is used in databases like MySQL, SQL Server, PostgreSQL, and Oracle.
  2. It organizes data in tables, rows, and columns.
  3. Supports querying, data manipulation, and management.

2. SQL Syntax

  1. SQL statements are usually case-insensitive, but keywords are written in uppercase for clarity.
  2. General structure of a query:

SELECT column1, column2
FROM table_name
WHERE condition
ORDER BY column1;

Example:


SELECT Name, Salary
FROM Employees
WHERE Department = 'IT'
ORDER BY Salary DESC;

3. CRUD Operations

CRUD stands for the four basic operations in SQL:

OperationSQL CommandPurpose
CreateINSERTAdd new records
ReadSELECTRetrieve records
UpdateUPDATEModify existing records
DeleteDELETERemove records

4. Common SQL Data Types

Data TypeDescriptionExample
INTInteger numbers1, 100, 5000
VARCHARVariable-length text'Alice', 'HR'
DATEDate values'2025-12-05'
DECIMALDecimal/floating numbers123.45
BOOLEANTrue/False valuesTRUE, FALSE

5. Essential SQL Commands

5.1 Create and Drop Database


-- Create a new database
CREATE DATABASE CompanyDB;

-- Drop an existing database
DROP DATABASE CompanyDB;

5.2 Create, Alter, and Drop Table


-- Create Employees table
CREATE TABLE Employees (
ID INT PRIMARY KEY AUTO_INCREMENT,
Name VARCHAR(50),
Department VARCHAR(50),
Salary DECIMAL(10,2)
);

-- Add a new column
ALTER TABLE Employees
ADD JoinDate DATE;

-- Drop the table
DROP TABLE Employees;

5.3 Insert Records


INSERT INTO Employees (Name, Department, Salary)
VALUES ('Alice', 'HR', 50000),
('Bob', 'IT', 60000),
('Charlie', 'Finance', 55000);

5.4 Retrieve Records


-- Retrieve all records
SELECT * FROM Employees;

-- Retrieve specific columns
SELECT Name, Salary FROM Employees;

-- Retrieve records with a condition
SELECT * FROM Employees
WHERE Salary > 55000;

5.5 Update Records


UPDATE Employees
SET Salary = 65000
WHERE Name = 'Bob';

5.6 Delete Records


DELETE FROM Employees
WHERE Name = 'Charlie';

6. Practical Exercises

  1. Create a table Employees with columns: ID, Name, Department, Salary.
  2. Insert 3–5 employee records.
  3. Update the salary of an employee.
  4. Delete an employee record.
  5. Retrieve all records from the table.
  6. Try selecting specific columns and filtering records using WHERE.

7. Tips for Beginners

  1. Always use primary keys for unique identification.
  2. Use meaningful column names for clarity.
  3. Keep practicing CRUD operations with different datasets.
  4. Use comments in SQL for better readability:

-- This is a comment