SQL Understanding tables, rows, and columns

Table:

Definition: A table is a collection of data organized into rows and columns. It's the basic structure in which data is stored in a relational database.

Example:

                                  
                                    CREATE TABLE students (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    age INT,
    grade CHAR(1)
);
                                  
                                

Columns:

Definition: Columns, also known as fields or attributes, define the different types of data that can be stored in a table. Each column has a specific data type (e.g., INT, VARCHAR, DATE).

Example:

Example:

                                  
                                    CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    hire_date DATE
);
                                  
                                

In the above example, employee_id, first_name, last_name, and hire_date are columns.


Rows:

Definition: Rows, also known as records or tuples, represent individual entries in a table. Each row contains data corresponding to each column in that table.

Example:

                                  
                                    INSERT INTO students (id, name, age, grade)
VALUES (1, 'John Doe', 20, 'A');
                                  
                                

In the above example, (1, 'John Doe', 20, 'A') represents a row of data in the students table.


Primary Key:

Definition: A primary key is a column or a set of columns that uniquely identifies each row in a table. It ensures the integrity of the data and allows for efficient retrieval of records.

Example:

                                  
                                    CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    product_name VARCHAR(100),
    quantity INT,
    customer_id INT
);
                                  
                                

Here, order_id is the primary key.


Foreign Key:

Definition: A foreign key is a column or a set of columns in a table that refers to the primary key of another table. It establishes a relationship between the two tables.

Example:

                                  
                                    CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    product_name VARCHAR(100),
    quantity INT,
    customer_id INT,
    FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);
                                  
                                

In this example, customer_id is a foreign key referencing the customer_id in the customers table.


Data Types:

Definition: Data types define the kind of data that can be stored in a column, such as integers, strings, dates, etc.

Example:

                                  
                                    CREATE TABLE products (
    product_id INT PRIMARY KEY,
    product_name VARCHAR(100),
    price DECIMAL(8, 2),
    release_date DATE
);
                                  
                                

In this example, product_id is of type INT, product_name is of type VARCHAR, price is of type DECIMAL, and release_date is of type DATE.