?? Day 35: Exploring Practical SQL Implementation - Part 7??

A. Create Table based Queries in SQL:-

1. Create Table:

CREATE TABLE employees (
   employee_id INT,
   first_name VARCHAR(50),
   last_name VARCHAR(50),
   birthdate DATE
);        

Explanation:

• This query creates a table named employees with columns for employee_id, first_name, last_name, and birthdate.

Real-Life Example:

• Use Case: Employee Database
• Query: Creating a table to store employee information.
• Output: A table structure ready to store details like employee ID, names, and birthdate.

2. Create Table with Constraints:

CREATE TABLE orders (
   order_id INT PRIMARY KEY,
   product_id INT,
   quantity INT,
   CONSTRAINT fk_product FOREIGN KEY (product_id) REFERENCES products(product_id),
   CONSTRAINT chk_quantity CHECK (quantity > 0)
);        

Explanation:

• This query creates an orders table with constraints, ensuring a unique order_id, a valid product_id referencing another table, and a check on the quantity.

Real-Life Example:

• Use Case: Sales Management
• Query: Establishing a table for order details with constraints.
• Output: A structured table enforcing data integrity rules for orders.

3. Create Temporary Table:

CREATE TEMPORARY TABLE temp_sales AS
SELECT product_name, sales_quantity
FROM daily_sales
WHERE sales_date = CURRENT_DATE;        

Explanation:

• This query creates a temporary table named temp_sales by selecting specific columns from an existing table (daily_sales) based on today's sales date.

Real-Life Example:

• Use Case: Daily Sales Report
• Query: Creating a temporary table to store today's sales data.
• Output: A temporary table with a snapshot of sales data for the current date.

4. Drop Table:

DROP TABLE outdated_data;        

Explanation:

• This query removes the table named outdated_data along with all its data and structure.

Real-Life Example:

• Use Case: Data Cleanup
• Query: Dropping a table that contains outdated or irrelevant information.
• Output: The outdated_data table is deleted, freeing up database resources.

B. Alter Table based Queries in SQL:

1. Add Column:

ALTER TABLE employees
ADD department VARCHAR(30);        

Explanation:

• This query adds a new column named department to the existing employees table.

Real-Life Example:

• Use Case: Employee Management
• Query: Adding a column to store the department information.
• Output: The employees table is modified with a new column for department details.

2. Drop Column:

ALTER TABLE orders
DROP COLUMN discount;        

Explanation:

• This query removes the discount column from the orders table.

Real-Life Example:

• Use Case: Order Processing
• Query: Dropping a column that is no longer needed in order processing.
• Output: The orders table no longer contains the discount column.

3. Modify Column:

ALTER TABLE products
MODIFY COLUMN price DECIMAL(10, 2);        

Explanation:

• This query modifies the data type of the price column in the products table.

Real-Life Example:

• Use Case: Inventory Management
• Query: Changing the data type of the price column for more accurate calculations.
• Output: The products table now has the price column with a decimal data type.

4. Rename Column:

ALTER TABLE customers
CHANGE COLUMN email_address contact_email VARCHAR(100);        

Explanation:

• This query renames the email_address column to contact_email in the customers table.

Real-Life Example:

• Use Case: Customer Relationship Management (CRM)
• Query: Renaming a column for clarity and consistency in CRM records.
• Output: The customers table reflects the change from email_address to contact_email.

5. Add Constraint:

ALTER TABLE products
ADD CONSTRAINT chk_price CHECK (price > 0);        

Explanation:

• This query adds a check constraint to ensure that the price column in the products table is always greater than zero.

Real-Life Example:

• Use Case: Pricing Validation
• Query: Adding a constraint to validate product prices.
• Output: The products table now enforces the check constraint on the price column.

6. Drop Constraint:

ALTER TABLE employees
DROP CONSTRAINT fk_department;        

Explanation:

• This query removes the foreign key constraint named fk_department from the employees table.

Real-Life Example:

• Use Case: Foreign Key Management
• Query: Dropping a foreign key constraint when it's no longer necessary.
• Output: The employees table no longer has the foreign key constraint fk_department.

7. Rename Table:

ALTER TABLE old_table
RENAME TO new_table;        

Explanation:

• This query renames the old_table to new_table.

Real-Life Example:

• Use Case: Table Renaming
• Query: Renaming a table for better organization or clarity.
• Output: The table is now referred to as new_table.