Explore

Database and Table Management

Summary - Database and Table Management

Understanding Data Types

Definition

Data types in SQL are the classifications that specify the kind of data that can be stored in a database column. They play a crucial role in how the database manages data, influencing both the operations that can be performed on the data and how much storage space the data will consume. Choosing the correct data type for each column is essential for ensuring data integrity and optimizing performance.

Common Data Types

Numeric Data Types:

INT: This data type is used for storing whole numbers without decimals. For example, you might use it for employee IDs or counts of items.

FLOAT: This type is used for storing numbers that require decimals. It’s useful for storing values like prices or measurements where precision is important.

Character Data Types:

CHAR(n): This is a fixed-length string type. If you define a column as CHAR(5), it will always store five characters, padding with spaces if necessary. It is useful for storing fixed-length codes, like state abbreviations.

VARCHAR(n): This type is variable-length and can hold up to n characters. It is more flexible than CHAR and is commonly used for names or addresses where the length may vary.

Date and Time Data Types:

DATE: This type stores dates in a year-month-day format. It is perfect for fields that track dates, such as a customer's registration date.

TIMESTAMP: This type stores both date and time, providing a complete picture of when an event occurred. It’s useful for tracking when records are created or modified.

Understanding these data types helps in designing the database effectively, allowing you to store and retrieve data accurately and efficiently.

⁠

Create, Update & Delete Operations

Definition

Create, Update, and Delete operations, often abbreviated as CRUD, are fundamental actions that allow users to interact with and manage data stored in a database. Each of these operations performs a specific function related to data management.

Create Operation

The Create operation is used to add new records to a table. This is done with the INSERT INTO statement, which specifies the table you want to add data to and the values for each column.

Example of Create

To add a new customer to a customers table, you would write:

INSERT INTO customers (customer_name, contact_email)

VALUES ('John Doe', 'johndoe@example.com');

In this example, a new row is created in the customers table with the name "John Doe" and the email "

johndoe@example.com⁠

Update Operation

The Update operation allows you to modify existing records. It is performed using the UPDATE statement, which specifies the table to be updated and the new values for one or more columns, along with a condition to identify which records to update.

Example of Update

If you want to change John Doe's email address to a new one, you would write:

UPDATE customers

SET contact_email = 'john.doe@newdomain.com'

WHERE customer_name = 'John Doe';

This command searches for the record where the customer's name is "John Doe" and updates the email address for that specific record.

Delete Operation

The Delete operation is used to remove records from a table. The DELETE FROM statement specifies which table to delete from, and it often includes a WHERE clause to indicate which records should be deleted.

Example of Delete

To remove the customer record for John Doe, you would use:

DELETE FROM customers

WHERE customer_name = 'John Doe';

This command deletes the row from the customers table where the customer's name matches "John Doe." It’s essential to be careful with the DELETE command, especially if you omit the WHERE clause, as it can remove all records from the table.

⁠

Primary and Foreign Key

Keys in SQL are essential for maintaining relationships between tables and ensuring data integrity. They serve as unique identifiers for records and help to enforce rules on the data entered into a database.

Primary Key

A Primary Key is a column (or a combination of columns) that uniquely identifies each row in a table. A primary key must contain unique values and cannot have NULL values. This means every record can be distinguished from others without ambiguity.

Example

To create a customers table with a primary key on the customer_id column, you would write:

CREATE TABLE customers (

customer_id INT PRIMARY KEY,

customer_name VARCHAR(50)

);

In this example, the customer_id acts as the primary key, ensuring that every customer has a unique identifier.

Foreign Key

A Foreign Key is a column or a group of columns in one table that links to the primary key in another table. This creates a relationship between the two tables and helps maintain referential integrity.

Example

To create a orders table where each order is linked to a customer in the customers table, you would write:

CREATE TABLE orders (

order_id INT PRIMARY KEY,

customer_id INT,

FOREIGN KEY (customer_id) REFERENCES customers(customer_id)

);

In this example, the customer_id in the orders table is a foreign key that references the customer_id in the customerstable. This relationship ensures that every order is associated with a valid customer.

⁠

Constraints Functions

Definition

Constraints in SQL are rules applied to table columns to enforce data integrity. They ensure that the data entered into the database adheres to certain conditions, preventing invalid data from being stored.

Types of Constraints

NOT NULL: This constraint ensures that a column cannot have a NULL value. It is important for fields that must always have a value, such as names or IDs.

Example:

CREATE TABLE employees (

employee_id INT PRIMARY KEY,

employee_name VARCHAR(50) NOT NULL

);

In this example, the employee_name column cannot be left empty.

UNIQUE: This constraint ensures that all values in a column are unique across the table. It prevents duplicate entries, which is critical for fields like email addresses or usernames.

Example:

CREATE TABLE products (

product_id INT PRIMARY KEY,

product_code VARCHAR(10) UNIQUE

);

Here, product_code must be unique for each product, preventing duplicates.

CHECK: This constraint ensures that all values in a column meet a specific condition. For instance, you can use it to enforce that a salary must be greater than zero.

Example:

CREATE TABLE salaries (

employee_id INT,

salary DECIMAL(10, 2),

CHECK (salary > 0)

);

This ensures that no employee can have a salary of zero or less.

Using constraints helps maintain the accuracy and reliability of the data within the database, preventing common data entry errors.

⁠

Alter & Truncate

ALTER Command

The ALTER command is used to make changes to an existing database object, such as a table. You can add new columns, modify existing columns, or even drop columns that are no longer needed.

Example of ALTER

To add a new column to the employees table for storing the date of birth, you would write:

ALTER TABLE employees

ADD date_of_birth DATE;

This command modifies the employees table by adding a new column called date_of_birth.

TRUNCATE Command

The TRUNCATE command is used to quickly remove all records from a table, while keeping the structure of the table intact. Unlike the DELETE command, which removes rows one at a time and logs each deletion, TRUNCATE operates on the entire table and is much faster.

Example of TRUNCATE

To remove all records from the employees table without dropping the table itself, you would use:

TRUNCATE TABLE employees;

This command clears all data from the employees table but keeps the table structure for future use. It’s a useful command when you want to reset a table quickly without affecting its definition.

Understanding Database and Table Management is crucial for organizing and maintaining structured data efficiently. For more detailed information, refer to the Notion link:

Database and Table Management⁠

⁠

Want to print your doc?
This is not the way.

Try clicking the ⋯ next to your doc name or using a keyboard shortcut (

CtrlP

) instead.