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

thevipulvats

/*
 * PRIMARY KEYS IN SQL
 * This script demonstrates the concepts and best practices for using primary keys in database design
 */

-- Primary Keys - Key Benefits:
-- They uniquely identify each record in a table
-- They ensure no duplicate records exist
-- They provide a reference point for relationships between tables
-- They optimize database performance for record retrieval

thevipulvats

-- Database Normalization: 1NF, 2NF, and 3NF Demonstration
-- This SQL script demonstrates the process of normalizing a database through 
-- First, Second, and Third Normal Forms using a bookstore database example.

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

-- Original denormalized table
CREATE TABLE book_orders (

thevipulvats

-- Foreign Keys in SQL: Database Relationships
-- =============================================

-- INTRODUCTION TO FOREIGN KEYS
-- A foreign key is a column or set of columns in one table that refers to the primary key in another table. 
-- It creates a link between the two tables, establishing a parent-child relationship.
-- Parent table: Contains the primary key that is referenced
-- Child table: Contains the foreign key that references the primary key of the parent table
-- Purpose of Foreign Keys: Referential Integrity, Data Validation, Structured Relationships

thevipulvats

-- SQL INNER JOIN Lecture
-- An INNER JOIN returns only the rows where there is a match in both tables based on the specified join condition.
-- If there's no match, the rows from both tables are excluded from the result set.

-- Create database
CREATE DATABASE db_inner_join;
USE db_inner_join;

-- Create authors table
CREATE TABLE authors (