Step 3: Basic SQL Commands
SQL commands are basic instructions used to create, manage, and manipulate data in a database.
They allow you to add, modify, retrieve, or delete information and also define the structure of tables and databases.
1. CREATE
The CREATE command is used to create new databases, tables, views, or other database objects.
It defines the structure and columns of a table or schema.
CREATE TABLE table_name (
column1 datatype [constraints],
column2 datatype [constraints],
...
);Example:
CREATE TABLE students (
id INT PRIMARY KEY,
name VARCHAR(100),
age INT
);This creates a table named students with three columns: id, name, and age.
2. INSERT
The INSERT command is used to add new data (rows) into a table.
You must specify the column names and values to insert.
INSERT INTO table_name (column1, column2, ...)
VALUES (value1, value2, ...);Example:
INSERT INTO students (id, name, age)
VALUES (1, 'Alice', 20);This adds a student named Alice, aged 20, with ID 1 into the table.
3. SELECT
The SELECT command retrieves data from one or more tables.
It can fetch specific columns, filter rows, and sort or group results.
SELECT column1, column2, ...
FROM table_name;
-- OR to select all:
SELECT * FROM table_name;Example:
SELECT * FROM students;This displays all rows and columns from the students table.
4. UPDATE
The UPDATE command is used to change existing data in a table.
You can modify one or more columns based on a condition using WHERE.
UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition;Example:
UPDATE students
SET age = 21
WHERE id = 1;This updates the age of the student with ID 1 to 21.
5. DELETE
The DELETE command removes data from a table.
Use it with WHERE to delete specific rows or without it to remove all records.
DELETE FROM table_name
WHERE condition;Example:
DELETE FROM students
WHERE id = 1;This deletes the student whose ID is 1 from the table.
6. ALTER
The ALTER command modifies an existing table’s structure.
You can add, delete, or modify columns without affecting the stored data.
-- To add a column
ALTER TABLE table_name
ADD column_name datatype;
-- To drop a column
ALTER TABLE table_name
DROP COLUMN column_name;
-- To modify a column
ALTER TABLE table_name
MODIFY column_name new_datatype;Example:
ALTER TABLE students
ADD grade VARCHAR(2);This adds a new column grade to the students table.
7. DROP
The DROP command permanently deletes a database or table along with all its data and structure.
This action cannot be undone.
DROP TABLE table_name;
DROP DATABASE database_name;Example:
DROP TABLE students;This removes the entire students table from the database.
8. WHERE
The WHERE clause filters records that meet specific conditions.
It is commonly used with SELECT, UPDATE, and DELETE.
SELECT * FROM table_name
WHERE condition;Example:
SELECT * FROM students
WHERE age = 18;This retrieves only the students whose age is 18.
9. ORDER BY
The ORDER BY clause sorts query results by one or more columns,
either in ascending (ASC) or descending (DESC) order.
SELECT column1, column2
FROM table_name
ORDER BY column_name ASC|DESC;Example:
SELECT * FROM students
ORDER BY age ASC;This lists students from youngest to oldest based on their age.
10. DISTINCT
The DISTINCT keyword in a SELECT query returns only unique (non-duplicate) values from a column.
SELECT DISTINCT column_name
FROM table_name;Example:
SELECT DISTINCT grade FROM students;This displays only the different grade values without repetition.