?? Day 32: Exploring Practical SQL Implementation - Part 4??

A. Modifying SQL Commands used in Database:

When working with databases, it is essential to have a good understanding of modifying SQL commands to efficiently manage data. Here are some key commands for modifying data in a database:

1. Insert Data:???- The INSERT INTO command is used to add new records to a table.???

2. Insert from a Table:???- You can insert data into a table from another table using the INSERT INTO SELECT statement.???

3. Insert Multiple Rows:???- To insert multiple rows at once, you can use the INSERT INTO command with multiple value sets.??

4. Update:???- The UPDATE command is used to modify existing records in a table.??

5. Update with Join:???- You can update records in one table based on values from another table using the UPDATE JOIN statement.???

6. Delete:???- The DELETE FROM command is used to remove records from a table.??

7. Alter Table: The ALTER TABLE statement in SQL is a powerful command used to modify the structure of an existing table. It allows you to add, modify, or drop columns, as well as perform other structural changes.

By mastering these SQL commands for modifying data in a database, you can effectively manage and manipulate the information stored in your database tables.

Real Life-Example

1. Insert Data:

-- Scenario: Adding a new book to the catalog
INSERT INTO books (title, author, price)
VALUES ('The Art of SQL', 'Stephane Faroult', 984);        

• Description: This command adds a new book titled "The Art of SQL" by Stephane Faroult with a price of Rs 984 to the 'books' table.
• Output: The 'books' table will have a new entry for "The Art of SQL" with the provided details.

2. Insert from a Table:

-- Scenario: Adding new books from a supplier's catalog
INSERT INTO books (title, author, price)
SELECT title, author, price
FROM supplier_catalog
WHERE category = 'Technology';        

• Description: This command inserts books from the 'supplier_catalog' table into the 'books' table, but only those categorized as 'Technology.'
• Output: The 'books' table will have additional entries for books meeting the specified condition in the 'supplier_catalog' table.

3. Insert Multiple Rows:

-- Scenario: Introducing a book bundle promotion
INSERT INTO books (title, author, price)
VALUES 
  ('Data Science Essentials', 'Jake VanderPlas', 941),
  ('Machine Learning Mastery', 'Jason Brownlee', 732),
  ('Python Crash Course', 'Eric Matthes', 704);        

• Description: This command adds multiple books to the 'books' table as part of a new book bundle promotion.
• Output: The 'books' table will have entries for the new books introduced in the promotion.

4. Update:

-- Scenario: Adjusting the price of a specific book
UPDATE books
SET price = 785
WHERE title = 'Python Crash Course';        

• Description: This command modifies the price of the book "Python Crash Course" to Rs 785.
• Output: The 'books' table will reflect the updated price for the specified book.

5. Update with Join:

-- Scenario: Updating book ratings based on customer reviews
UPDATE books b
SET b.rating = r.average_rating
FROM reviews r
WHERE b.book_id = r.book_id;        

• Description: This command updates the 'rating' column in the 'books' table based on the average ratings from the 'reviews' table.
• Output: The 'books' table will have updated ratings based on the average ratings from the 'reviews' table.

6. Delete:

-- Scenario: Removing a book that is no longer available
DELETE FROM books
WHERE title = 'Outdated Technology Trends';        

• Description: This command deletes the book titled "Outdated Technology Trends" from the 'books' table as it is no longer available.
• Output: The 'books' table will no longer have an entry for the deleted book.

7. Alter Table:

-- Adding a new column 'registration_date' to the 'customers' table
ALTER TABLE customers
ADD COLUMN registration_date DATE;        

• Description: Imagine you run an e-commerce platform, and you decide to capture the registration date of your customers to analyze user growth over time.
• Output: Adds a new column "registration date" to the employees table.

B. Indexing SQL Commands used in Database:

Indexes in a database are crucial for optimizing query performance by providing faster access to data. Here are some key SQL commands related to indexes:

1. Create Index:???- The CREATE INDEX command is used to create an index on one or more columns in a table.???

2. Unique Index:???- You can create a unique index to enforce uniqueness on one or more columns in a table.???

3. Drop Index:???- The DROP INDEX command is used to remove an existing index from a table.??

4. Clustered Index:???- A clustered index determines the physical order of data rows in a table. You can create a clustered index using the CLUSTERED keyword.???

5. Non-Clustered Index:???- A non-clustered index is a separate structure from the table data and can be created using the NONCLUSTERED keyword.??

6. Composite Index:???- You can create an index on multiple columns to improve query performance for specific queries that involve those columns.?

By utilizing these SQL commands for indexes, you can enhance the efficiency of database queries and improve overall performance when working with large datasets.

Real Life-Example

1. Create Index:

-- Scenario: Improving search performance for book titles
CREATE INDEX idx_title ON books (title);        

• Description: This command creates an index named 'idx_title' on the 'title' column of the 'books' table. It improves search performance when querying based on book titles.
• Output: The database creates an index structure that allows faster retrieval of data based on the 'title' column.

2. Unique Index:

-- Scenario: Ensuring uniqueness in employee IDs
CREATE UNIQUE INDEX idx_employee_id ON employees (employee_id);        

• Description: This command creates a unique index named 'idx_employee_id' on the 'employee_id' column of the 'employees' table. It ensures each employee ID is unique.
• Output: The database enforces the uniqueness constraint on the 'employee_id' column.

3. Drop Index:

-- Scenario: Removing an unnecessary index
DROP INDEX idx_unused_index;        

• Description: This command removes the index named 'idx_unused_index' from the database.
• Output: The specified index is removed, and the associated space is freed.

4. Clustered Index:

-- Scenario: Clustering books based on publication year
CREATE CLUSTERED INDEX idx_year_cluster ON books (publication_year);        

• Description: This command creates a clustered index named 'idx_year_cluster' on the 'publication_year' column of the 'books' table. It physically organizes the data in the order of publication years.
• Output: The data in the 'books' table is organized based on the 'publication_year.'

5. Non-Clustered Index:

-- Scenario: Improving search performance for author names
CREATE NONCLUSTERED INDEX idx_author_search ON books (author);        

• Description: This command creates a non-clustered index named 'idx_author_search' on the 'author' column of the 'books' table. It improves search performance without affecting the physical order of data.
• Output: The database creates a separate structure for faster searches based on the 'author' column.

6. Composite Index:

-- Scenario: Enhancing search speed for combined criteria
CREATE INDEX idx_combined_search ON orders (customer_id, order_date);        

• Description: This command creates a composite index named 'idx_combined_search' on the 'customer_id' and 'order_date' columns of the 'orders' table. It optimizes search speed for queries combining these criteria.
• Output: The database creates an index structure to accelerate searches using both 'customer_id' and 'order_date.'