Basic SQL Concepts

1.Database

Definition: A database is a collection of organized data that can be easily accessed, managed, and updated. It stores data in a structured format, typically in tables.

Example :

CREATE DATABASE myDatabase;

Explanation: This command creates a new database named myDatabase. Creating a database is the first step in organizing and storing data which can then be managed using SQL.

2.Table

Definition: A table is a collection of related data entries consisting of rows and columns. Each table in a database holds data about a specific subject.

Example :

CREATE TABLE Customers (
    CustomerID int,
    Name varchar(255),
    Address varchar(255)
);

Explanation: This statement creates a table called Customers with columns for CustomerID, Name, and Address. Tables are fundamental for organizing data into specific formats and categories, making it easier to retrieve and manage.

3.Schema

Definition: A schema is a logical container for database objects like tables, views, and procedures.

Example :

CREATE SCHEMA Sales;

Explanation: This command creates a schema named Sales. Schemas help in organizing and securing database objects because they allow you to group related objects under a single name.

4.Row and Column

Definition: Rows represent individual records in a table, while columns represent the attributes of the records.

Example :

SELECT Name, Address FROM Customers;

Explanation: This query retrieves the Name and Address for each record in the Customers table. Columns help define the data structure of a table, and rows contain the actual data.

5.Primary Key

Definition: A primary key is a column (or combination of columns) that uniquely identifies each row in a table..

Example :

CREATE TABLE Orders (
    OrderID int PRIMARY KEY,
    OrderDate date,
    CustomerID int
);

Explanation: This creates an Orders table where OrderID serves as the primary key. Primary keys ensure each record within a table can be uniquely identified.

6.Foreign Key

Definition: A foreign key is a column that creates a relationship between two tables by referencing the primary key of another table.

Example :

CREATE TABLE Orders (
    OrderID int PRIMARY KEY,
    OrderDate date,
    CustomerID int,
    FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID)
);

Explanation: This statement sets CustomerID as a foreign key in the Orders table that references the CustomerID primary key in the Customers table. Foreign keys are crucial for maintaining referential integrity and representing relationships between data.