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

SQL Language

Next ►
►

Structured Query Language (SQL) is the standard language for interacting with relational databases. It allows users to create, read, update, and delete data within a database. This section will cover the basics of SQL, including its syntax, commands, and practical examples.

Key Concepts of SQL

  1. Data Definition Language (DDL): Commands that define the structure of the database.

    • CREATE: Creates a new table or database.
    • ALTER: Modifies an existing database object, such as a table.
    • DROP: Deletes tables or databases.

  2. Data Manipulation Language (DML): Commands that manipulate data within the database.

    • SELECT: Retrieves data from the database.
    • INSERT: Adds new data to the database.
    • UPDATE: Modifies existing data.
    • DELETE: Removes data from the database.

  3. Data Control Language (DCL): Commands that control access to data.

    • GRANT: Gives user access privileges to the database.
    • REVOKE: Removes user access privileges.

  4. Transaction Control Language (TCL): Commands that manage transactions within the database.

    • COMMIT: Saves all changes made during the transaction.
    • ROLLBACK: Undoes changes made during the transaction.

Basic SQL Syntax

Creating a Table

CREATE TABLE Employees (
    EmployeeID INT PRIMARY KEY,
    FirstName VARCHAR(50),
    LastName VARCHAR(50),
    BirthDate DATE,
    Position VARCHAR(50)
);

Explanation:

  • CREATE TABLE Employees: Creates a new table named Employees.
  • EmployeeID INT PRIMARY KEY: Defines a column EmployeeID of type integer and sets it as the primary key.
  • FirstName VARCHAR(50): Defines a column FirstName of type variable character with a maximum length of 50.
  • LastName VARCHAR(50): Similar to FirstName.
  • BirthDate DATE: Defines a column BirthDate of type date.
  • Position VARCHAR(50): Defines a column Position of type variable character with a maximum length of 50.

Inserting Data

INSERT INTO Employees (EmployeeID, FirstName, LastName, BirthDate, Position)
VALUES (1, 'John', 'Doe', '1980-01-15', 'Manager');

Explanation:

  • INSERT INTO Employees: Specifies the table where data will be inserted.
  • (EmployeeID, FirstName, LastName, BirthDate, Position): Lists the columns to insert data into.
  • VALUES (1, 'John', 'Doe', '1980-01-15', 'Manager'): Provides the values for each column.

Selecting Data

SELECT FirstName, LastName, Position
FROM Employees
WHERE Position = 'Manager';

Explanation:

  • SELECT FirstName, LastName, Position: Specifies the columns to retrieve.
  • FROM Employees: Specifies the table to retrieve data from.
  • WHERE Position = 'Manager': Filters the results to include only rows where the Position is 'Manager'.

Updating Data

UPDATE Employees
SET Position = 'Senior Manager'
WHERE EmployeeID = 1;

Explanation:

  • UPDATE Employees: Specifies the table to update.
  • SET Position = 'Senior Manager': Sets the new value for the Position column.
  • WHERE EmployeeID = 1: Filters the rows to update based on the EmployeeID.

Deleting Data

DELETE FROM Employees
WHERE EmployeeID = 1;

Explanation:

  • DELETE FROM Employees: Specifies the table to delete data from.
  • WHERE EmployeeID = 1: Filters the rows to delete based on the EmployeeID.

Practical Exercises

Exercise 1: Creating and Populating a Table

  1. Create a table named Departments with the following columns:

    • DepartmentID (integer, primary key)
    • DepartmentName (variable character, max length 50)
    • Location (variable character, max length 50)

  2. Insert the following data into the Departments table:

    • (1, 'Human Resources', 'New York')
    • (2, 'Finance', 'Chicago')
    • (3, 'IT', 'San Francisco')

Solution:

CREATE TABLE Departments (
    DepartmentID INT PRIMARY KEY,
    DepartmentName VARCHAR(50),
    Location VARCHAR(50)
);

INSERT INTO Departments (DepartmentID, DepartmentName, Location)
VALUES (1, 'Human Resources', 'New York'),
       (2, 'Finance', 'Chicago'),
       (3, 'IT', 'San Francisco');

Exercise 2: Querying Data

  1. Select the DepartmentName and Location for all departments located in 'Chicago'.

Solution:

SELECT DepartmentName, Location
FROM Departments
WHERE Location = 'Chicago';

Exercise 3: Updating Data

  1. Update the Location of the 'IT' department to 'Los Angeles'.

Solution:

UPDATE Departments
SET Location = 'Los Angeles'
WHERE DepartmentName = 'IT';

Exercise 4: Deleting Data

  1. Delete the department with DepartmentID 2.

Solution:

DELETE FROM Departments
WHERE DepartmentID = 2;

Common Mistakes and Tips

  • Forgetting the WHERE clause in UPDATE or DELETE statements: This can lead to updating or deleting all rows in the table.
  • Mismatched data types: Ensure that the data types of the values match the column definitions.
  • Syntax errors: SQL syntax can be strict, so pay attention to commas, parentheses, and quotation marks.

Conclusion

In this section, we covered the basics of SQL, including its key concepts, basic syntax, and practical examples. Understanding SQL is fundamental for interacting with relational databases, and mastering these basics will prepare you for more advanced database operations. In the next section, we will delve into basic operations in SQL, where you will learn more about querying and manipulating data.

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