HTML
JAVASCRIPT
CSS
PYTHON
JAVA
SQL

SQL DataBase

Introduction to SQL Database

A SQL (Structured Query Language) database is a structured data storage system that enables efficient querying, retrieval, and manipulation of records. These databases follow a structured schema, using tables, rows, and columns to organize and manage data.

Key Concepts in SQL

  • Tables: Data is stored in rows and columns within tables. The primary storage units containing structured data.
  • Schema: Defines the structure of the database, including tables, columns, and data types.
  • Columns: Define the type and nature of the stored data.
  • Rows: Represent individual data entries.
  • Primary Key: A unique identifier for each row in a table.
  • Foreign Key: A field in one table that refers to the primary key in another table.
  • Constraints: Rules like PRIMARY KEY, FOREIGN KEY, UNIQUE, NOT NULL, etc.
  • Indexes: Improve data retrieval efficiency.
  • Views: Virtual tables representing query results.
  • Stored Procedures: Predefined SQL statements to automate tasks.
  • Queries: Commands written in SQL to interact with the database.

1. Creating a Database

Before working with data, a database must be created.

Syntax:

CREATE DATABASE database_name;

Example:

CREATE DATABASE CompanyDB;

Explanation: This command initializes a new database named CompanyDB.

2. Using a Database

To execute SQL commands inside a specific database, select it using:

Syntax:

USE database_name;

Example:

USE CompanyDB;

Explanation: This sets CompanyDB as the active database.

3. Creating a Table

A table defines the structure of stored data.

Syntax:

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

Example:

CREATE TABLE Employees (
    ID INT PRIMARY KEY,
    Name VARCHAR(50) NOT NULL,
    Age INT CHECK (Age > 18),
    Department VARCHAR(100),
    Salary DECIMAL(10,2)
);

Explanation: This creates an Employees table with different column types and constraints.

4. Inserting Data

To add records into a table, use the INSERT statement.

Syntax:

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

Example:

INSERT INTO Employees (ID, Name, Age, Department, Salary)
VALUES (101, 'Alice Johnson', 29, 'IT', 70000.50);

Explanation: This inserts a new employee’s details into the Employees table.

5. Retrieving Data

The SELECT command fetches records from a table.

Syntax:

SELECT column1, column2 FROM table_name WHERE condition;

Example:

SELECT Name, Salary FROM Employees WHERE Age > 25;

Explanation: This retrieves names and salaries of employees older than 25.

6. Updating Records

To modify existing data, use the UPDATE command.

Syntax:

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

Example:

UPDATE Employees
SET Salary = 75000
WHERE Name = 'Alice Johnson';

Explanation: This updates Alice’s salary in the Employees table.

7. Deleting Data

To remove records, use the DELETE statement.

Syntax:

DELETE FROM table_name WHERE condition;

Example:

DELETE FROM Employees WHERE Age < 21;

Explanation: This removes employees younger than 21.

8. Filtering Data

The WHERE clause applies conditions to queries.

Syntax:

SELECT column1, column2 FROM table_name WHERE condition;

Example:

SELECT * FROM Employees WHERE Department = 'IT';

Explanation: This fetches all employees in the IT department.

9. Sorting Data

The ORDER BY clause arranges data in ascending or descending order.

Syntax:

SELECT column1, column2 FROM table_name ORDER BY column1 ASC|DESC;

Example:

SELECT Name, Salary FROM Employees ORDER BY Salary DESC;

Explanation: This lists employees by salary in descending order.

10. Joining Tables

The JOIN operation combines data from multiple tables based on related columns.

Syntax:

SELECT table1.column, table2.column FROM table1 JOIN table2 ON table1.common_column = table2.common_column;

Example:

CREATE TABLE Departments (
    DeptID INT PRIMARY KEY,
    DeptName VARCHAR(50)
);

INSERT INTO Departments VALUES (1, 'IT'), (2, 'HR');

SELECT Employees.Name, Departments.DeptName
FROM Employees
JOIN Departments ON Employees.Department = Departments.DeptName;

Explanation: This fetches employees along with their department names.

Conclusion

SQL databases provide a structured, efficient method for storing, managing, and retrieving data. With a robust set of commands like CREATE, INSERT, SELECT, UPDATE, and DELETE, SQL empowers developers to work with large datasets seamlessly. Whether managing a small application or enterprise-level data, SQL remains an essential tool in database management.

Previous Next