Basic SQL Commands

Basic SQL Commands

Structured Query Language (SQL) is the standard language used to communicate with relational database management systems. This topic will focus on the basic SQL commands that allow you to create, read, update, and delete data in a database. These operations are often referred to as CRUD operations.

Data Definition Language (DDL)

Data Definition Language (DDL) commands are used to define and manage all the structures in a database. The most common DDL commands are:

• CREATE: Used to create new tables or databases.
• ALTER: Used to modify existing database structures.
• DROP: Used to delete tables or databases.

CREATE Command

The CREATE command is used to create a new table in the database. The syntax for creating a table is as follows:

CREATE TABLE table_name (column1 datatype, column2 datatype, ...);

Here is an example of creating a table named Employees:

CREATE TABLE Employees (ID INT PRIMARY KEY, Name VARCHAR(100), Age INT, Salary DECIMAL(10, 2));

This command creates a table called Employees with four columns: ID, Name, Age, and Salary. The ID column is defined as the primary key, which uniquely identifies each record.

ALTER Command

The ALTER command is used to modify an existing table structure. You can add, modify, or delete columns. The syntax for altering a table is as follows:

ALTER TABLE table_name ADD column_name datatype;

For example, to add a column for Email to the Employees table, you would write:

ALTER TABLE Employees ADD Email VARCHAR(100);

To modify an existing column, the syntax is:

ALTER TABLE table_name MODIFY column_name new_datatype;

For example, if you want to change the Salary column to allow for larger values:

ALTER TABLE Employees MODIFY Salary DECIMAL(15, 2);

To delete a column, use:

ALTER TABLE table_name DROP COLUMN column_name;

For example, to remove the Email column:

ALTER TABLE Employees DROP COLUMN Email;

DROP Command

The DROP command is used to delete an entire table or database. The syntax for dropping a table is:

DROP TABLE table_name;

For example, to drop the Employees table:

DROP TABLE Employees;

This command will remove the table and all its data permanently.

Data Manipulation Language (DML)

Data Manipulation Language (DML) commands are used to manipulate data within tables. The most common DML commands are:

• INSERT: Used to add new records.
• UPDATE: Used to modify existing records.
• DELETE: Used to remove records.

INSERT Command

The INSERT command is used to add new rows to a table. The syntax for inserting data is:

INSERT INTO table_name (column1, column2, ...) VALUES (value1, value2, ...);

For example, to insert a new employee into the Employees table:

INSERT INTO Employees (ID, Name, Age, Salary) VALUES (1, 'John Doe', 30, 50000.00);

This command adds a new record with the specified values.

UPDATE Command

The UPDATE command is used to modify existing records in a table. The syntax for updating data is:

UPDATE table_name SET column1 = value1, column2 = value2 WHERE condition;

For example, to update the salary of the employee with ID 1:

UPDATE Employees SET Salary = 55000.00 WHERE ID = 1;

This command changes the salary of the employee to 55,000 rand.

DELETE Command

The DELETE command is used to remove records from a table. The syntax for deleting data is:

DELETE FROM table_name WHERE condition;

For example, to delete the employee with ID 1:

DELETE FROM Employees WHERE ID = 1;

This command will remove the specified record from the table.

Data Query Language (DQL)

Data Query Language (DQL) commands are used to query data from a database. The most common DQL command is:

• SELECT: Used to retrieve data from one or more tables.

SELECT Command

The SELECT command is used to query data from a table. The syntax for selecting data is:

SELECT column1, column2, ... FROM table_name WHERE condition;

For example, to select all columns from the Employees table:

SELECT * FROM Employees;

This command retrieves all records and all columns from the Employees table.

If you want to select specific columns, you can specify them:

SELECT Name, Salary FROM Employees;

This command retrieves only the Name and Salary columns for all records.

Filtering Results

You can filter results using the WHERE clause. For example, to find employees with a salary greater than 40,000 rand:

SELECT * FROM Employees WHERE Salary > 40000;

This command retrieves only the records where the salary is greater than 40,000 rand.

SELECT * FROM Employees WHERE Age > 30 AND Salary < 60000;

Summary

• DDL commands define the database structure (CREATE, ALTER, DROP).
• DML commands manipulate data (INSERT, UPDATE, DELETE).
• DQL commands query data (SELECT).

Check your understanding

1. What command would you use to create a new table in SQL?
2. How would you update a record in a table?
3. What is the purpose of the WHERE clause in SQL commands?
4. Explain the difference between DDL and DML commands.