Data Analysis Week 4 Notes

Database:

• CREATE DATABASE new_db;
• SHOW DATABASES;
• USE new_db
• DROP DATABASE new_db;
• CREATE DATABASE IF NOT EXISTS new_db;
• DROP DATABASE IF EXISTS new_db mysql
• RENAME TABLE old_database_name.table1 TO new_database_name.table1;
• RENAME TABLE old_database_name.table2 TO new_database_name.table2; -- Repeat for all tables in the old database postgres ALTER DATABASE current_database_name RENAME TO new_database_name;

MSSQl
ALTER DATABASE Test MODIFY NAME = Example

Troubleshooting Database Rename Issues
Database is in Use: If you encounter an error saying that the database is in use, you may need to disconnect active users or close any applications connected to the database before renaming it.
Insufficient Privileges: If you receive a permission error, ensure you have administrative privileges or sufficient rights to modify the database. You might need to check your user role and permissions.
Database Name Constraints: Some DBMSs have restrictions on certain characters or reserved words in database names. Ensure your new database name adheres to the naming conventions for the specific SQL system you are using.

CREATE TABLE Customer(
CustomerID INT PRIMARY KEY,
CustomerName VARCHAR(50),
LastName VARCHAR(50),
Country VARCHAR(50),
Age INT CHECK (Age >= 0 AND Age <= 99),
Phone int(10)
);

CREATE TABLE SubTable AS
SELECT CustomerID, CustomerName
FROM customer;

CREATE TABLE IF NOT EXISTS Customer

DESC table_name;

DROP TABLE IF EXISTS categories;

SHOW TABLES; (mysql)

SELECT * FROM INFORMATION_SCHEMA.TABLES(other table)

ALTER TABLE Student RENAME Column name TO FIRST_NAME;

ALTER TABLE Student RENAME TO Student_Details;

ALTER TABLE Student ADD marks INT;

ALTER TABLE Student_Details
MODIFY COLUMN phone BIGINT;

ALTER TABLE Student_Details

DROP COLUMN marks;

ALTER TABLE Student_Details ALTER COLUMN age SET DEFAULT 18;

TRUNCATE TABLE EMPLOYEE;

SQL Cloning or Copying a Table

What is a Copying or Cloning Table in SQL
SQL Cloning is an operation that means making a copy of a table. It's like taking a photocopy of a document. This copy can include both the table’s structure (column names, data types, constraints) and optionally its data. The clone table is independent of the original and can be used for testing, backups, or analysis without affecting the original table.

Cloning a table in SQL means making a duplicate copy of an existing table. It's like making a backup so that we can experiment or work with the data without affecting the original table. This saves our the time and effort of creating a completely new table and re-entering all the same data. Cloning can be done with or without data:

With Data: The clone table includes the structure and rows of the original table.
Without Data: Only the structure of the original table is copied.

Methods for Cloning Tables in SQL
There are three different methods to create a clone table in SQL:

Simple Cloning
Deep Cloning
Shallow Cloning

1. Simple Cloning In this method, the clone table creates a copy of the original table’s structure and data, but constraints like primary keys, unique keys, and auto-increment properties are not preserved.

CREATE TABLE STUDENT_COPY AS SELECT * FROM STUDENT;

1. Shallow Cloning Shallow cloning is the method in which the clone table gets the same structure as the original table but it does not inherits or copy the data from the original table. In other words, we will have the empty table including indices such as primary key, unique key, and auto_increment. It also preserves constraints like primary keys and unique keys.

CREATE TABLE STUDENT_SHALLOW_CLONE LIKE STUDENT;
SELECT * FROM STUDENT_SHALLOW_CLONE;

1. Deep Cloning This method is widely used for creating the clone tables in SQL as it inherits all the properties of original table including indices such as primary key, unique, and auto_increment as well as inherits the existing data from the original table.

CREATE TABLE STUDENT_DEEP_CLONE LIKE STUDENT;
INSERT INTO STUDENT_DEEP_CLONE SELECT * FROM STUDENT;
SELECT * FROM STUDENT_DEEP_CLONE;

What is Temporary Table in SQL?

A temporary table in SQL is an important tool for maintaining intermediate results during query execution. They help store temporary data without affecting the underlying permanent tables.

CREATE TABLE #EmpDetails (id INT, name VARCHAR(25))

SELECT * FROM Emp1 WHERE Age=24;

SELECT * FROM Customer;

SELECT CustomerName
FROM Customer
where Age = '21';

SELECT Customer_id, COUNT(*) AS order_count
FROM Orders
GROUP BY Customer_id;

SELECT Department, sum(Salary) as Salary
FROM employee
GROUP BY department
HAVING SUM(Salary) >= 50000;

SELECT * FROM Customer ORDER BY Age DESC;

INSERT

INSERT INTO table_name
VALUES (value1, value2, value);

INSERT INTO Student (ROLL_NO, NAME, AGE, ADDRESS, PHONE)
VALUES
(6, 'Amit Kumar', 15, 'Delhi', 'XXXXXXXXXX'),
(7, 'Gauri Rao', 18, 'Bangalore', 'XXXXXXXXXX'),
(8, 'Manav Bhatt', 17, 'New Delhi', 'XXXXXXXXXX'),
(9, 'Riya Kapoor', 10, 'Udaipur', 'XXXXXXXXXX');

INSERT INTO target_table
SELECT * FROM source_table;

specific columns

INSERT INTO Students (Name, Age)
SELECT Name, Age
FROM OldStudents;

Inserting Data Using Transactions
When inserting large amounts of data, you can use SQL transactions to ensure that all rows are inserted correctly. A transaction groups multiple SQL operations into a single unit, so if one operation fails, the entire transaction is rolled back.

Query:

`BEGIN TRANSACTION;

INSERT INTO Customers (CustomerID, CustomerName, ContactName, Country)
VALUES (5, 'Sarah White', 'John White', 'Canada');

INSERT INTO Customers (CustomerID, CustomerName, ContactName, Country)
VALUES (6, 'Mohamed Ibrahim', 'Ahmed Ibrahim', 'UAE');

-- If any error occurs, the transaction will be rolled back

COMMIT;`

Update

UPDATE Customer
SET CustomerName = 'Nitin'
WHERE Age = 22;

UPDATE Customer
SET CustomerName = 'Satyam',
Country = 'USA'
WHERE CustomerID = 1;

If we accidentally omit the WHERE clause, all the rows in the table will be updated, which is a common mistake. Let’s update the CustomerName for every record in the table:

Query:

UPDATE Customer
SET CustomerName = 'Shubham';

Best Practices for Using SQL UPDATE Statement

1. Always use the WHERE clause:
   The most important point when using the UPDATE statement is to always include a WHERE clause unless you genuinely intend to update all rows.

2. Check your data before updating:

Run a SELECT query to view the data you want to update before executing the UPDATE statement. This helps avoid accidental data modifications.

SELECT * FROM Customer WHERE Age = 22;

1. Use transactions for critical updates:

When performing updates in a production environment, consider using transactions. Transactions allow you to commit or roll back changes as needed.

BEGIN TRANSACTION;
UPDATE Customer SET CustomerName = 'John' WHERE CustomerID = 3;
COMMIT; -- Use ROLLBACK to undo if necessary

1. Test on a small dataset first: If you are uncertain about the impact of your UPDATE, test it on a small subset of data to ensure the changes are as expected.

delete

DELETE FROM GFG_Employees
WHERE NAME = 'Rithvik';

delete All records from table

DELETE FROM GFG_Employees;
Or
DELETE * FROM GFG_Employees;

START TRANSACTION;
DELETE FROM GFG_Employees WHERE department = 'Development';
-- If needed, you can rollback the deletion
ROLLBACK;

What Are Duplicate Rows?
Duplicate rows are records in a database that have identical values in one or more columns. These rows often arise due to issues like multiple imports, user errors, or missing constraints like primary keys or unique indexes. SQL query to delete duplicate rows typically involves identifying duplicates using functions like ROW_NUMBER() or COUNT() and making sure that only one copy of each record is kept in the table. If not handled properly, duplicates can lead to:

Inaccurate Data Reporting: Reports may contain false information.
Storage Waste: Redundant records consume unnecessary space.
Decreased Query Performance: Queries on large tables with duplicates may perform poorly.
Why You Should Remove Duplicate Rows
Data Integrity: Duplicates can distort reports and analyses, leading to incorrect insights.
Optimal Performance: Redundant data can slow down queries, especially when dealing with large datasets.
Efficient Storage: Removing duplicates helps optimize storage usage, keeping your database lean.

Best Practices to Prevent Duplicates
While identifying and removing duplicates is essential, preventing them is even better. Here are some best practices to ensure that duplicates don’t enter your database in the first place:

Use Primary Keys or Unique Constraints: These ensure that each record is unique, preventing accidental duplication.
Data Validation: Implement validation rules in your application to prevent duplicate entries.
Indexing: Create unique indexes on columns that must remain unique, like contact numbers or email addresses.
Regular Data Cleaning: Periodically run data-cleaning queries to identify and remove any newly inserted duplicates.

SELECT DATE_FORMAT('2025-08-20 08:30:00', '%W, %M %D, %Y %r');
-- Output: Wednesday, August 20th, 2025 08:30:00 AM
Common format specifiers include:
%Y: Four-digit year
%y: Two-digit year
%M: Full month name (January-December)
%m: Numeric month (01-12)
%b: Abbreviated month name (Jan-Dec)
%D: Day of the month with English suffix (1st, 2nd, 3rd, etc.)
%d: Numeric day of the month (01-31)
%W: Full weekday name (Sunday-Saturday)
%w: Numeric weekday (0=Sunday, 6=Saturday)
%H: Hour (00-23)
%h: Hour (01-12)
%i: Minutes (00-59)
%s: Seconds (00-59)
%f: Microseconds (000000-999999)
%r: Time in 12-hour format with AM/PM (hh:mm:ss AM/PM)

SELECT CURTIME();

SELECT CURDATE()

Types of MySQL JOIN

1. INNER JOIN
2. LEFT JOIN
3. RIGHT JOIN
4. FULL JOIN
5. CROSS JOIN
6. Self Join
7. Update JOin

UPDATE students
JOIN courses ON students.course_id = courses.course_id
JOIN grades ON students.student_id = grades.student_id
SET grades.grade = grades.grade + 5
WHERE courses.course_name = 'Science';

1. Delete Join

DELETE emp FROM employees emp
JOIN salaries sal ON emp.employee_id = sal.employee_id
WHERE sal.salary < 50000;

Window Functions in SQL

SQL window functions are essential for advanced data analysis and database management. It is a type of function that allows us to perform calculations across a specific set of rows related to the current row. These calculations happen within a defined window of data and they are particularly useful for aggregates, rankings and cumulative totals without modifying the dataset.

Types of Window Functions in SQL

• aggregate window functions
• ranking window functions

1. Aggregate Window Function Aggregate window functions calculate aggregates over a window of rows while retaining individual rows. Common aggregate functions include:

SUM(): Sums values within a window.
AVG(): Calculates the average value within a window.
COUNT(): Counts the rows within a window.
MAX(): Returns the maximum value in the window.
MIN(): Returns the minimum value in the window.
LAG()/LEAD()

1. Ranking Window Functions These functions provide rankings of rows within a partition based on specific criteria. Common ranking functions include:

RANK(): Assigns ranks to rows, skipping ranks for duplicates.
DENSE_RANK(): Assigns ranks to rows without skipping rank numbers for duplicates.
ROW_NUMBER(): Assigns a unique number to each row in the result set.

1. CASE Statements in SQL

CASE lets you add conditional logic inside queries.

✅ Example 1: Simple CASE
SELECT emp_id,
first_name,
salary,
CASE
WHEN salary >= 100000 THEN 'High'
WHEN salary BETWEEN 60000 AND 99999 THEN 'Medium'
ELSE 'Low'
END AS salary_band
FROM Employees;

👉 Categorizes employees into salary bands.

✅ Example 2: CASE inside ORDER BY
SELECT emp_id, first_name, dept_id
FROM Employees
ORDER BY
CASE
WHEN dept_id = 1 THEN 1
WHEN dept_id = 2 THEN 2
ELSE 3
END;

👉 Custom sorting (Dept 1 first, then Dept 2, then others).

✅ Example 3: CASE inside WHERE
SELECT emp_id, first_name, salary, dept_id
FROM Employees
WHERE salary >
CASE
WHEN dept_id = 1 THEN 70000
WHEN dept_id = 2 THEN 50000
ELSE 40000
END;

Type Indexes:

• Clustered index
• Non-clustered index

Clustered Index
A clustered index is the type of indexing that establishes a physical sorting order of rows. The data rows are stored directly in the order of the indexed column(s). Each table can have only one clustered index because it dictates the data’s physical storage. A clustered index is like a Dictionary in which the sorting order is alphabetical and there is no separate index page.

Non-Clustered Index
Non-Clustered index is an index structure separate from the data stored in a table that reorders one or more selected columns. The non-clustered index is created to improve the performance of frequently used queries not covered by a clustered index. It's like a textbook, the index page is created separately at the beginning of that book.

Other Index types:

• Unique Index
• Composite (or Compound) Index
• Filtered Index(mysql wont support)
• Columnstore Index
• Hash Index

Data Normalization:

ACID Properties

Transactions are fundamental operations that allow us to modify and retrieve data. However, to ensure the integrity of a database, it is important that these transactions are executed in a way that maintains consistency, correctness, and reliability even in case of failures / errors. This is where the ACID properties come into play.

ACID stands for Atomicity, Consistency, Isolation, and Durability.