Skip to content

Instantly share code, notes, and snippets.

View thevipulvats's full-sized avatar
💭
I may be slow to respond.

Vipul thevipulvats

💭
I may be slow to respond.
View GitHub Profile
-- 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 (
-- 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 (
-- 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;
-- 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,
-- =============================================
-- MySQL Subqueries Demonstration
-- Online Store Database Example
-- =============================================
-- Create database and set it as the active database
CREATE DATABASE online_store;
USE online_store;
-- =============================================
-- 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
);
-- ====================================================================
-- 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)
-- ====================================================================
-- 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)
-- 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;
-- 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