The Project | About Us | Contribute | Donations | License
HOME My courses Completed
◄ Previous
◄

Basic Operations in SQL

Next ►
►

In this section, we will cover the fundamental operations in SQL (Structured Query Language) that are essential for interacting with relational databases. These operations include creating, reading, updating, and deleting data, commonly referred to as CRUD operations.

Key Concepts

  1. CRUD Operations:

    • Create: Inserting new data into a table.
    • Read: Querying data from a table.
    • Update: Modifying existing data in a table.
    • Delete: Removing data from a table.

  2. SQL Syntax:

    • SQL statements are used to perform tasks such as updating data on a database or retrieving data from a database.
    • SQL syntax is case-insensitive, but it is a common convention to write SQL keywords in uppercase.

Basic SQL Operations

  1. Creating Data (INSERT)

The INSERT INTO statement is used to add new rows of data to a table.

Syntax:

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

Example:

INSERT INTO Employees (EmployeeID, FirstName, LastName, Age, Department)
VALUES (1, 'John', 'Doe', 30, 'HR');

  1. Reading Data (SELECT)

The SELECT statement is used to query data from a database. The data returned is stored in a result table, sometimes called the result set.

Syntax:

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

Example:

SELECT FirstName, LastName, Department
FROM Employees
WHERE Age > 25;

  1. Updating Data (UPDATE)

The UPDATE statement is used to modify existing records in a table.

Syntax:

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

Example:

UPDATE Employees
SET Age = 31
WHERE EmployeeID = 1;

  1. Deleting Data (DELETE)

The DELETE statement is used to remove existing records from a table.

Syntax:

DELETE FROM table_name
WHERE condition;

Example:

DELETE FROM Employees
WHERE EmployeeID = 1;

Practical Exercises

Exercise 1: Insert Data

Task: Insert a new employee into the Employees table with the following details:

  • EmployeeID: 2
  • FirstName: Jane
  • LastName: Smith
  • Age: 28
  • Department: IT

Solution:

INSERT INTO Employees (EmployeeID, FirstName, LastName, Age, Department)
VALUES (2, 'Jane', 'Smith', 28, 'IT');

Exercise 2: Select Data

Task: Retrieve the first name, last name, and department of all employees who are older than 25.

Solution:

SELECT FirstName, LastName, Department
FROM Employees
WHERE Age > 25;

Exercise 3: Update Data

Task: Update the age of the employee with EmployeeID 2 to 29.

Solution:

UPDATE Employees
SET Age = 29
WHERE EmployeeID = 2;

Exercise 4: Delete Data

Task: Delete the employee with EmployeeID 2 from the Employees table.

Solution:

DELETE FROM Employees
WHERE EmployeeID = 2;

Common Mistakes and Tips

  1. Omitting the WHERE Clause in UPDATE/DELETE:

    • Always ensure you include a WHERE clause in your UPDATE and DELETE statements to avoid modifying or deleting all records in the table.

  2. Case Sensitivity:

    • While SQL keywords are case-insensitive, table names and column names can be case-sensitive depending on the database system.

  3. Syntax Errors:

    • Pay attention to commas, parentheses, and semicolons to avoid syntax errors.

  4. Data Types:

    • Ensure that the values you insert or update match the data types defined for the columns.

Conclusion

In this section, we covered the basic operations in SQL, including how to insert, select, update, and delete data. These operations form the foundation of interacting with relational databases. In the next module, we will delve deeper into referential integrity and how to maintain consistency in your database.

◄ Previous
◄
Next ►
►
The Project | About Us | Contribute | Donations | License
© Copyright 2024. All rights reserved