thevipulvats

-- Self JOIN in SQL Demonstration
-- This script demonstrates how to use Self JOINs to query hierarchical and relational data
-- within the same table, using an employee management system example.

-- Create and use database
CREATE DATABASE self_join_tutorial;
USE self_join_tutorial;

-- Create employees table with manager_id referencing the same table
CREATE TABLE employees (

thevipulvats

-- REPLACE INTO SQL Demonstration
-- This script demonstrates how REPLACE INTO combines INSERT and DELETE operations
-- to simplify the process of updating or inserting records based on primary or unique key values.

-- Create and use database
CREATE DATABASE replace_demo;
USE replace_demo;

-- Create products table
CREATE TABLE products (

thevipulvats

-- SQL DELETE Tutorial
-- Demonstrates how to remove records from a database table
-- DELETE FROM table_name WHERE condition;

-- Create a database for our examples
CREATE DATABASE delete_tutorial;

-- Use the database
USE delete_tutorial;

thevipulvats

-- Create the database
CREATE DATABASE store_inventory;

-- Switch to the new database
USE store_inventory;

-- Create products table
CREATE TABLE products (
    product_id INT PRIMARY KEY,
    product_name VARCHAR(50) NOT NULL,

thevipulvats

-- =============================================
-- MySQL Subqueries Demonstration 
-- Online Store Database Example
-- =============================================

-- Create database and set it as the active database
CREATE DATABASE online_store;
USE online_store;

-- =============================================

thevipulvats

-- CROSS JOIN
-- Cartesian product of two tables
-- It combines each row from the first table with every row from the second table
-- resulting in all possible combinations of rows
CREATE DATABASE cross_join_tutorial;
USE cross_join_tutorial;
CREATE TABLE products (
    product_id INT PRIMARY KEY,
    product_name VARCHAR(50) NOT NULL
);

thevipulvats

-- ====================================================================
-- MySQL FULL JOIN Lecture - Complete SQL Script
-- ====================================================================

-- Introduction to FULL JOIN
-- ====================================================================
-- FULL JOIN
-- - It returns all matching rows from both tables where the join condition is met
-- - It also returns all non-matching rows from the left table (with NULL values for columns from the right table)
-- - It also returns all non-matching rows from the right table (with NULL values for columns from the left table)

thevipulvats

-- ====================================================================
-- MySQL UNION Lecture - Complete SQL Script
-- ====================================================================

-- Introduction to UNION and UNION ALL
-- ====================================================================
-- UNION allows us to combine result sets from multiple SELECT queries into a single result set
-- Key points:
-- - Combines rows from multiple queries into a single result set
-- - Appends rows vertically (stacks them on top of each other)

thevipulvats

-- LEFT JOIN (LEFT OUTER JOIN) Tutorial
-- It returns ALL records from the left table and only the matching records from the right table.
-- If no match exists in the right table, NULL values will be returned for the right table's columns.

-- Basic LEFT JOIN syntax
SELECT columns
FROM table1
LEFT JOIN table2
ON table1.column = table2.column;

thevipulvats

-- Create and use the Gokuldham Society database
CREATE DATABASE gokuldham_society;
USE gokuldham_society;

-- Create apartments table to store apartment information
CREATE TABLE apartments (
    apartment_id INT PRIMARY KEY,
    apartment_number VARCHAR(10) NOT NULL,
    floor_number INT NOT NULL,
    wing_name CHAR(1) NOT NULL