SQL Understanding tables, rows, and columns
Understanding tables, rows, and columns is fundamental to working with relational databases in SQL.
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.