# Understanding SQL Fundamentals: A Beginner's Handbook:- ### **Check Out My Portfolio Now:** [πŸ”— Click Here](https://ankits-web.vercel.app/) ### **What is a Database?** * A place to store and organize information so you can find it easily. πŸ“‚βœ… * Example: Like a digital notebook to save and find information quickly. πŸ“πŸ’» * Example: A phone contact list is a simple database. πŸ“± ### What is DBMS? * DBMS means Database Management System. * It’s a software that helps to store and manage data. * Makes it easy to add, update, delete, and find data. * Example\*\*:\*\* MySQL, Oracle, Microsoft SQL Server. | **DBMS** | **RDBMS** | | --- | --- | | Database Management System | Relational Database Management System | | Stores data as files or documents | Stores data in tables (rows & columns) | | MongoDB | MySQL, Oracle, PostgreSQL | | Unstructured | Structured | | JSON-like documents | Tables with columns and data types | | DBMS might not always do that perfectly. | RDBMS keeps your data safe during updates. | ### What is a Query? * A query is a question or request sent to a database. It asks the database to find, add, update, or delete data.
πŸ’‘
Example: You want to see all names in a friends list.
> **This query asks the database to show all friend names.** ```plaintext SELECT name FROM friends; ``` ### What is MySQL Workbench? * It’s a tool to work with MySQL databases easily. * You can write and run SQL commands. * You can design tables and see data in a nice way. ### **What is a Primary Key in a database?** * It’s a special ID for each row in a table. * No two rows have the same primary key. * Example: Your student ID in a school. ### **What is a Secondary Key in a database?** * A key used to find or group data, but it’s not unique. * Example:-Class name in a student list β€” many students share the same class. * It’s like a helper key in the database. * ### **What is a Foreign Key in a database?** * A foreign key is like a bridge between two tables. * It links one table’s data to another table’s unique ID (primary key). * Helps keep data organized and connected. * A foreign key is a link from one table to another .It helps tables talk to each other. * Example:-In an orders list, Customer ID tells who placed the order by linking to the customers list. --- ```sql -- Create a new database named startersql CREATE DATABASE startersql; -- Use the startersql database USE startersql; -- Delete the users table if it exists DROP TABLE IF EXISTS users; -- (You can create tables or insert data here) -- (You can create tables or insert data here) -- Rename the table 'users' to 'customers' RENAME TABLE users TO customers; -- Get all columns and rows from the 'users' table SELECT * FROM users; -- It displays all the columns (fields) in the table named pre. SHOW COLUMNS FROM pre; -- Delete the startersql database completely DROP DATABASE startersql; ``` --- ### **Step 1: Create a Database:-** ```sql CREATE DATABASE startersql; USE startersql; ``` ### **Step 2: Create a Table:-** ```sql CREATE TABLE users ( id INT AUTO_INCREMENT PRIMARY KEY, -- Makes a user ID that goes up automatically (1, 2, 3...) name VARCHAR(100) NOT NULL, -- Name of the user (must be filled in) Variable-length string, up to 100 characters. email VARCHAR(100) UNIQUE NOT NULL, -- Email (must be filled in and cannot be the same as anyone else's) gender ENUM('Male', 'Female', 'Other'), -- Gender (choose one from Male, Female, Other) date_of_birth DATE, -- Birth date (example: 2000-05-01) salary DECIMAL(10, 2), -- Salary with 2 decimal points (example: 50000.50) created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP -- Adds the date & time automatically when the user is added ); ``` #### **Data Types Explained:-** * `INT`: Integer type, used for whole numbers. * `VARCHAR(100)`: Variable-length string, up to 100 characters. * `ENUM`: A string object with a value chosen from a list of permitted values. * `DATE`: Stores date values. eg -`date_of_birth DATE`. * `TIMESTAMP`: Stores date and time, automatically set to the current timestamp when a row is created. * `BOOLEAN`: Stores TRUE or FALSE values, often used for flags like `is_active`. * `DECIMAL(10, 2)`: Stores exact numeric data values, useful for financial data. #### **Constraints Explained:-** * `AUTO_INCREMENT`: Automatically generates a unique number for each row. * `PRIMARY KEY`: Uniquely identifies each row in the table. * `NOT NULL`: Ensures a column cannot have a NULL value. * `UNIQUE`: Ensures all values in a column are different. * `DEFAULT`: Sets a default value for a column if no value is provided. eg-`created_at TIMESTAMP DEFAULT` * `CURRENT_TIMESTAMP`, `is_active BOOLEAN DEFAULT TRUE` ### **Step 3-Insert Multiple Rows at Once:-** ```sql INSERT INTO users (name, email, gender, date_of_birth, salary) VALUES ('Aarav', 'aarav@example.com', 'Male', '1995-05-14', 65000.00), ('Ananya', 'ananya@example.com', 'Female', '1990-11-23', 72000.00), ('Raj', 'raj@example.com', 'Male', '1988-02-17', 58000.00), ('Sneha', 'sneha@example.com', 'Female', '2000-08-09', 50000.00), ('Farhan', 'farhan@example.com', 'Male', '1993-12-30', 61000.00), ('Priyanka', 'priyanka@example.com', 'Female', '1985-07-12', 84000.00), ('Aisha', 'aisha@example.com', 'Female', '1997-03-25', 56000.00), ('Aditya', 'aditya@example.com', 'Male', '1992-06-17', 69000.00), ('Meera', 'meera@example.com', 'Female', '1989-09-05', 77000.00), ('Ishaan', 'ishaan@example.com', 'Male', '2001-10-02', 45000.00), ('Tanvi', 'tanvi@example.com', 'Female', '1994-04-18', 62000.00), ('Rohan', 'rohan@example.com', 'Male', '1986-12-01', 75000.00), ``` ### \*\*Step 4-\*\*Basic SQL Operations:- ```sql -- Show all columns and rows from the users table SELECT * FROM users; -- Show only the name and email of each user SELECT name, email FROM users; -- Show all users where gender is Female SELECT * FROM users WHERE gender = 'Female'; -- Show all users where gender is NOT Female SELECT * FROM users WHERE gender <> 'Female'; -- (<> means Not Equal To) -- Select only column1 and column2 from the table called table_name SELECT column1, column2 FROM table_name; -- Show users whose ID is greater than 10 SELECT * FROM users WHERE id > 10; -- Show users who have a date of birth (not empty) SELECT * FROM users WHERE date_of_birth IS NOT NULL; -- Show users born between 1990 and 2000 SELECT * FROM users WHERE date_of_birth BETWEEN '1990-01-01' AND '2000-12-31'; -- Show users whose gender is either Male or Female SELECT * FROM users WHERE gender IN ('Male', 'Female'); -- Show Male users who earn more than 70,000, sorted by birth date (oldest first) SELECT * FROM users WHERE gender = 'Male' AND salary > '70000' ORDER BY date_of_birth ASC; -- Show users whose name starts with the letter A SELECT * FROM users WHERE name LIKE 'A%'; -- % means any characters after A ``` --- ### Altering a Table in SQL(Add a Column):- ```sql -- Add a new column named 'phone' to the 'users' table to store phone numbers ALTER TABLE users ADD phone VARCHAR(15); -- Remove the 'is_active' column from the 'users' table ALTER TABLE users DROP COLUMN is_active; -- Change the size of the 'name' column to allow up to 150 characters ALTER TABLE users MODIFY COLUMN name VARCHAR(150); ``` **Move a column to the first position:-** ```sql --ALTER TABLE table_name --MODIFY COLUMN column_name data_type --FIRST; --ALTER TABLE table_name --MODIFY COLUMN column_name data_type --AFTER another_column; -- Move the 'email' column to be the first column in the 'users' table ALTER TABLE users MODIFY COLUMN email VARCHAR(100) FIRST; -- Move the 'gender' column to appear right after the 'name' column in the 'users' table ALTER TABLE users MODIFY COLUMN gender ENUM('Male', 'Female', 'Other') AFTER name; ``` ### **LIKE (Pattern Matching):-** ```sql SELECT * FROM users WHERE name LIKE 'A%'; -- Starts with A SELECT * FROM users WHERE name LIKE '%a'; -- Ends with a SELECT * FROM users WHERE name LIKE '%li%'; -- Contains 'li' ``` ### **ORDER BY:-** * **ASC:** Sorts from smallest to largest (e.g., A to Z, oldest to newest). * **DESC:** Sorts from largest to smallest (e.g., Z to A, newest to oldest). ```sql SELECT * FROM users ORDER BY date_of_birth ASC; SELECT * FROM users ORDER BY name DESC; ``` ### **LIMIT:-** ```sql SELECT * FROM users LIMIT 5; -- Top 5 rows SELECT * FROM users LIMIT 10 OFFSET 5; -- Skip first 5 rows, then get next 10 SELECT * FROM users LIMIT 5, 10; -- Get 10 rows starting from the 6th row (Same as above) SELECT * FROM users ORDER BY created_at DESC LIMIT 10; ``` ## UPDATE - Modifying Existing Data: ```sql CREATE DATABASE IF NOT EXISTS startersql; USE startersql; CREATE TABLE users ( id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(100) NOT NULL, email VARCHAR(100) UNIQUE NOT NULL, gender ENUM('Male', 'Female', 'Other'), date_of_birth DATE, salary DECIMAL(10, 2), created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ); INSERT INTO users (name, email, gender, date_of_birth, salary) VALUES ('Aarav', 'aarav@example.com', 'Male', '1995-05-14', 65000.00), ('Ananya', 'ananya@example.com', 'Female', '1990-11-23', 72000.00), ('Raj', 'raj@example.com', 'Male', '1988-02-17', 58000.00), ('Sneha', 'sneha@example.com', 'Female', '2000-08-09', 50000.00), ('Farhan', 'farhan@example.com', 'Male', '1993-12-30', 61000.00), ('Priyanka', 'priyanka@example.com', 'Female', '1985-07-12', 84000.00), ('Aisha', 'aisha@example.com', 'Female', '1997-03-25', 56000.00), ('Aditya', 'aditya@example.com', 'Male', '1992-06-17', 69000.00), ('Meera', 'meera@example.com', 'Female', '1989-09-05', 77000.00), ('Ishaan', 'ishaan@example.com', 'Male', '2001-10-02', 45000.00), ('Tanvi', 'tanvi@example.com', 'Female', '1994-04-18', 62000.00), ('Rohan', 'rohan@example.com', 'Male', '1986-12-01', 75000.00), ('Zoya', 'zoya@example.com', 'Female', '1998-01-15', 54000.00), ('Karan', 'karan@example.com', 'Male', '1990-08-22', 68000.00), ('Nikita', 'nikita@example.com', 'Female', '1987-03-10', 71000.00), ('Manav', 'manav@example.com', 'Male', '1996-11-29', 61000.00), ('Divya', 'divya@example.com', 'Female', '1991-02-28', 57000.00), ('Harshit', 'harshit@example.com', 'Male', '1993-09-09', 65000.00), ('Ritika', 'ritika@example.com', 'Female', '1999-05-05', 52000.00), ('Imran', 'imran@example.com', 'Male', '1995-07-30', 63000.00), ('Juhi', 'juhi@example.com', 'Female', '1992-10-14', 59000.00), ('Tushar', 'tushar@example.com', 'Male', '1990-01-08', 73000.00), ('Lata', 'lata@example.com', 'Female', '1984-11-11', 78000.00), ('Yash', 'yash@example.com', 'Male', '1997-06-06', 64000.00), ('Fatima', 'fatima@example.com', 'Female', '1993-03-03', 55000.00); -- UPDATE users SET name = 'Ankit', email = 'Ankit@gmail.com' WHERE id = 1; DELETE FROM users WHERE id = 3; SELECT * FROM users; ``` > #### **Increase salary by β‚Ή10,000 for all users whose salary is less than β‚Ή60,000.** ```sql UPDATE users SET salary = salary + 10000 WHERE salary < 60000; ``` > #### **Set the gender of user** Ommm to Other. --- ```sql UPDATE users SET gender = 'Other' WHERE name = 'Ommm'; ``` > #### **Reset salary of all users to β‚Ή50,000 (Careful - affects all rows).** ```sql UPDATE users SET salary = 50000; ``` ```sql -- Update the user with ID 1, set their name and email to new values UPDATE users SET name = 'Ankit', email = 'Ankit@gmail.com' WHERE id = 1; -- Delete the user who has ID 3 DELETE FROM users WHERE id = 3; -- Check if the user with ID 3 still exists (should return nothing if deleted) SELECT * FROM users WHERE id = 3; -- Delete all users where salary is empty (NULL) DELETE FROM users WHERE salary IS NULL; -- Delete all users whose salary is less than 50000 DELETE FROM users WHERE salary < 50000; ``` ## **MySQL Constraints:-** ```sql CREATE TABLE users ( -- id will auto-increase for each new user id INT AUTO_INCREMENT PRIMARY KEY, -- name cannot be empty (NOT NULL) name VARCHAR(100) NOT NULL, -- email must be unique for each user email VARCHAR(100) UNIQUE, -- user must be born after the year 2000 (real check) date_of_birth DATE, CONSTRAINT chk_dob CHECK (date_of_birth > '2000-01-01'), -- gender has default value 'Other' if not given gender ENUM('Male', 'Female', 'Other') DEFAULT 'Other', -- user is active by default is_active BOOLEAN DEFAULT TRUE, -- salary can be any number, but we will use this later in queries salary DECIMAL(10,2) ); ``` | **Constraint** | **Purpose** | | --- | --- | | `UNIQUE` | Prevents duplicate values | | `NOT NULL` | Ensures value is not NULL | | `CHECK` | Restricts values using a condition | | `DEFAULT` | Sets a default value | | `PRIMARY KEY` | Uniquely identifies each row | | `AUTO_INCREMENT` | Automatically generates unique numbers | ```sql CREATE DATABASE IF NOT EXISTS db; USE db; DROP TABLE IF EXISTS users; CREATE TABLE users ( id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(100), gender ENUM('Male', 'Female', 'Other'), email VARCHAR(100), salary DECIMAL(10, 2), date_of_birth DATE ); INSERT INTO users (name, gender, email, salary, date_of_birth) VALUES ('Ankit', 'Male', 'ankit@gmail.com', 60000, '1996-08-10'), ('Priya', 'Female', 'priya@example.com', 55000, '1998-04-22'), ('Ravi', 'Male', 'ravi@company.com', 72000, '1995-01-15'), ('Neha', 'Female', 'neha@work.com', 50000, '1997-12-05'), ('Rahul', 'Male', 'rahul@webmail.com', 65000, '1994-07-30'), ('Aditi', 'Female', 'aditi@domain.com', 48000, '1999-06-18'), ('Kiran', 'Other', 'kiran@misc.com', 53000, '2000-03-10'); -- -------------------------------------------- -- 5. Aggregate Functions -- -------------------------------------------- SELECT COUNT(*) AS total_users FROM users; -- Output: 7 users total SELECT COUNT(*) AS female_users FROM users WHERE gender = 'Female'; -- Output: 3 female users SELECT MIN(salary) AS min_salary, MAX(salary) AS max_salary FROM users; -- Output: Min = 48000.00, Max = 72000.00 SELECT SUM(salary) AS total_payroll FROM users; -- Output: 403000.00 (total of all salaries) SELECT AVG(salary) AS avg_salary FROM users; -- Output: 57642.86 (average salary) SELECT gender, AVG(salary) AS avg_salary FROM users GROUP BY gender; -- Output: -- Male β†’ 65666.67 -- Female β†’ 51000.00 -- Other β†’ 53000.00 -- -------------------------------------------- -- 6. String Functions -- -------------------------------------------- SELECT name, LENGTH(name) AS name_length FROM users; -- Output: Ankit β†’ 5, Priya β†’ 5, etc. SELECT name, LOWER(name) AS lowercase_name FROM users; -- Output: Ankit β†’ ankit, Priya β†’ priya, etc. SELECT name, UPPER(name) AS uppercase_name FROM users; -- Output: Ankit β†’ ANKIT, etc. SELECT CONCAT(name, ' <', email, '>') AS user_contact FROM users; -- Output: Ankit , etc. SELECT name, LEFT(name, 3) AS first_3_letters FROM users; -- Output: Ankit β†’ Ank, Priya β†’ Pri, etc. SELECT email, RIGHT(email, 4) AS email_end FROM users; -- Output: .com SELECT name, SUBSTRING(name, 2, 3) AS mid_part FROM users; -- Output: Ankit β†’ nki, Priya β†’ riy, etc. SELECT email, REPLACE(email, '@', '[at]') AS safe_email FROM users; -- Output: ankit[at]gmail.com, etc. SELECT email, INSTR(email, '@') AS at_position FROM users; -- Output: Position of '@' in email, e.g., 6 SELECT name, REVERSE(name) AS reversed_name FROM users; -- Output: Ankit β†’ tiknA, Priya β†’ ayirP, etc. -- -------------------------------------------- -- 7. Date Functions -- -------------------------------------------- SELECT NOW() AS current_time; -- Output: Current system date and time SELECT name, YEAR(date_of_birth) AS birth_year FROM users; -- Output: Ankit β†’ 1996, Priya β†’ 1998, etc. SELECT name, DATEDIFF(CURDATE(), date_of_birth) AS days_lived FROM users; -- Output: Ankit β†’ approx 10500+ days, etc. SELECT name, TIMESTAMPDIFF(YEAR, date_of_birth, CURDATE()) AS age FROM users; -- Output: Ankit β†’ 28 (as of 2025), etc. -- -------------------------------------------- -- 8. Mathematical Functions -- -------------------------------------------- SELECT salary, ROUND(salary) AS rounded, FLOOR(salary) AS floored, CEIL(salary) AS ceiled FROM users; -- Output: Rounded, floored, ceiled values of each salary (usually same here) SELECT id, MOD(id, 2) AS remainder FROM users; -- Output: 0 for even id, 1 for odd id -- -------------------------------------------- -- 9. Conditional Functions -- -------------------------------------------- SELECT name, gender, IF(gender = 'Female', 'Yes', 'No') AS is_female FROM users; -- Output: Priya β†’ Yes, Ankit β†’ No, etc. ``` --- --- ## **MySQL Transactions and AutoCommit:-** * Transaction means running a group of SQL commands together, and we can save or cancel them using `COMMIT` or `ROLLBACK`. * AutoCommit means MySQL automatically saves your changes after each SQL command. ```sql -- Step 1: Create the 'users' table CREATE TABLE users ( id INT PRIMARY KEY, name VARCHAR(100), salary DECIMAL(10,2) ); -- Step 2: Insert sample data INSERT INTO users (id, name, salary) VALUES (1, 'Alice', 60000.00), (2, 'Bob', 55000.00), (3, 'Charlie', 70000.00), (4, 'Diana', 75000.00), (5, 'Evan', 50000.00); -- Step 3: View initial data SELECT * FROM users; -- Output: /* +----+---------+----------+ | id | name | salary | +----+---------+----------+ | 1 | Alice | 60000.00 | | 2 | Bob | 55000.00 | | 3 | Charlie | 70000.00 | | 4 | Diana | 75000.00 | | 5 | Evan | 50000.00 | +----+---------+----------+ */ -- Step 4: Turn off AutoCommit SET autocommit = 0; -- Step 5: Start a transaction (optional but recommended) START TRANSACTION; -- Step 6: Update Evan’s salary to 80000 (temporary change) UPDATE users SET salary = 80000 WHERE id = 5; -- Step 7: Check Evan’s salary before deciding SELECT * FROM users WHERE id = 5; -- Output: /* +----+-------+----------+ | id | name | salary | +----+-------+----------+ | 5 | Evan | 80000.00 | <-- Updated but NOT saved yet +----+-------+----------+ */ -- Step 8: Something is wrong β€” rollback the change ROLLBACK; -- Step 9: Check Evan’s salary again after rollback SELECT * FROM users WHERE id = 5; -- Output: /* +----+-------+----------+ | id | name | salary | +----+-------+----------+ | 5 | Evan | 50000.00 | <-- Back to original value +----+-------+----------+ */ -- Step 10: Turn AutoCommit back on (optional) SET autocommit = 1; -- Step 11: Final check – View all data SELECT * FROM users; -- Output: /* +----+---------+----------+ | id | name | salary | +----+---------+----------+ | 1 | Alice | 60000.00 | | 2 | Bob | 55000.00 | | 3 | Charlie | 70000.00 | | 4 | Diana | 75000.00 | | 5 | Evan | 50000.00 | <-- No change saved +----+---------+----------+ */ ``` * **SET autocommit = 0;** β†’ Turn off auto-save, you control when to save. * **UPDATE** β†’ Make a change in the data. * **COMMIT;** β†’ Save your changes permanently. * **ROLLBACK;** β†’ Undo your changes, don’t save. * **SET autocommit = 1;** β†’ Turn auto-save back on. > Can I Drop a PRIMARY KEY? > > Yes, you **can drop a primary key** in MySQL using: > > ```sql > ALTER TABLE users DROP PRIMARY KEY; > ``` ### **Foreign key:-** | customer\_id (PK) | name | email | | --- | --- | --- | | 1 | Alice | [alice@example.com](mailto:alice@example.com) | | [2](mailto:alice@example.com) | [Bob](mailto:alice@example.com) | [bob@example.com](mailto:bob@example.com) | | order\_id (PK) | order\_date | customer\_id (FK) | amount | | --- | --- | --- | --- | | 101 | 2025-07-27 | 1 | 150.00 | | 102 | 2025-07-28 | 2 | 200.00 | * In Orders table**,** `customer_id` is the foreign key β€” it points to the customer in the Customers table. ```sql -- Create Customers table (Parent table) CREATE TABLE customers ( customer_id INT PRIMARY KEY, name VARCHAR(50) ); -- Create Orders table (Child table) with foreign key to Customers CREATE TABLE orders ( order_id INT PRIMARY KEY, order_date DATE, customer_id INT, FOREIGN KEY (customer_id) REFERENCES customers(customer_id) ); -- Insert data into Customers INSERT INTO customers VALUES (1, 'Alice'), (2, 'Bob'); -- Insert data into Orders linked to Customers INSERT INTO orders VALUES (101, '2025-07-27', 1), -- Alice's order (102, '2025-07-28', 2); -- Bob's order -- Check Customers table SELECT * FROM customers; -- Output: -- +-------------+-------+ -- | customer_id | name | -- +-------------+-------+ -- | 1 | Alice | -- | 2 | Bob | -- +-------------+-------+ -- Check Orders table SELECT * FROM orders; -- Output: -- +----------+------------+-------------+ -- | order_id | order_date | customer_id | -- +----------+------------+-------------+ -- | 101 | 2025-07-27 | 1 | -- | 102 | 2025-07-28 | 2 | -- +----------+------------+-------------+ -- Trying to insert an order with a customer_id that doesn't exist will fail -- INSERT INTO orders VALUES (103, '2025-07-29', 3); -- Error: Cannot add or update a child row: a foreign key constraint fails ``` ## **SQL JOINs in MySQL:-** * JOIN = connect two tables using a common column to get related data together. ```sql -- ---------------------------------------- -- 1. Create Sample Tables -- ---------------------------------------- -- Drop tables if they already exist DROP TABLE IF EXISTS Employees; DROP TABLE IF EXISTS Departments; -- Employees Table CREATE TABLE Employees ( EmpID INT PRIMARY KEY, EmpName VARCHAR(100), DeptID INT ); -- Departments Table CREATE TABLE Departments ( DeptID INT PRIMARY KEY, DeptName VARCHAR(100) ); -- ---------------------------------------- -- 2. Insert Sample Data -- ---------------------------------------- -- Insert Employees INSERT INTO Employees (EmpID, EmpName, DeptID) VALUES (1, 'Alice', 10), (2, 'Bob', 20), (3, 'Charlie', 30), (4, 'Diana', NULL), (5, 'Eve', 40); -- No matching department -- Insert Departments INSERT INTO Departments (DeptID, DeptName) VALUES (10, 'HR'), (20, 'Engineering'), (30, 'Marketing'), (50, 'Sales'); -- No matching employee -- ---------------------------------------- -- 3. INNER JOIN: Only matching rows -- ---------------------------------------- SELECT E.EmpID, E.EmpName, D.DeptName FROM Employees E INNER JOIN Departments D ON E.DeptID = D.DeptID; -- βœ… Shows only employees that belong to existing departments -- ---------------------------------------- -- 4. LEFT JOIN: All employees, matched or not -- ---------------------------------------- SELECT E.EmpID, E.EmpName, D.DeptName FROM Employees E LEFT JOIN Departments D ON E.DeptID = D.DeptID; -- βœ… Shows all employees, including those without a department (NULLs for Dept) -- ---------------------------------------- -- 5. RIGHT JOIN: All departments, matched or not -- ---------------------------------------- SELECT E.EmpName, D.DeptName FROM Employees E RIGHT JOIN Departments D ON E.DeptID = D.DeptID; -- βœ… Shows all departments, including those with no employees -- ---------------------------------------- -- 6. FULL OUTER JOIN: All rows from both sides -- ---------------------------------------- -- Note: Not all SQL databases (like MySQL) support FULL OUTER JOIN natively. -- You can simulate it using UNION SELECT E.EmpName, D.DeptName FROM Employees E LEFT JOIN Departments D ON E.DeptID = D.DeptID UNION SELECT E.EmpName, D.DeptName FROM Employees E RIGHT JOIN Departments D ON E.DeptID = D.DeptID; -- βœ… Shows all combinations: matched and unmatched from both tables -- ---------------------------------------- -- 7. CROSS JOIN: Cartesian product (every row with every other) -- ---------------------------------------- SELECT E.EmpName, D.DeptName FROM Employees E CROSS JOIN Departments D; ``` **Employees Table** | EmpID | EmpName | DeptID | | --- | --- | --- | | 1 | Alice | 10 | | 2 | Bob | 20 | | 3 | Charlie | 30 | | 4 | Diana | NULL | | 5 | Eve | 40 | **Departments Table** | DeptID | DeptName | | --- | --- | | 10 | HR | | 20 | Engineering | | 30 | Marketing | | 50 | Sales | --- ### INNER JOIN:- ```sql SELECT E.EmpID, E.EmpName, D.DeptName FROM Employees E INNER JOIN Departments D ON E.DeptID = D.DeptID; ``` | EmpID | EmpName | DeptName | | --- | --- | --- | | 1 | Alice | HR | | 2 | Bob | Engineering | | 3 | Charlie | Marketing | ### **LEFT JOIN:-** ```sql SELECT E.EmpID, E.EmpName, D.DeptName FROM Employees E LEFT JOIN Departments D ON E.DeptID = D.DeptID; ``` | EmpID | EmpName | DeptName | | --- | --- | --- | | 1 | Alice | HR | | 2 | Bob | Engineering | | 3 | Charlie | Marketing | | 4 | Diana | NULL | | 5 | Eve | NULL | ### RIGHT JOIN:- ```sql SELECT E.EmpName, D.DeptName FROM Employees E RIGHT JOIN Departments D ON E.DeptID = D.DeptID; ``` | EmpName | DeptName | | --- | --- | | Alice | HR | | Bob | Engineering | | Charlie | Marketing | | NULL | Sales | ### FULL OUTER JOIN (Simulated with UNION):- ```sql SELECT E.EmpName, D.DeptName FROM Employees E LEFT JOIN Departments D ON E.DeptID = D.DeptID UNION SELECT E.EmpName, D.DeptName FROM Employees E RIGHT JOIN Departments D ON E.DeptID = D.DeptID; ``` | EmpName | DeptName | | --- | --- | | Alice | HR | | Bob | Engineering | | Charlie | Marketing | | Diana | NULL | | Eve | NULL | | NULL | Sales | ### CROSS JOIN:- ```sql SELECT E.EmpName, D.DeptName FROM Employees E CROSS JOIN Departments D; ``` | EmpName | DeptName | | --- | --- | | Alice | HR | | Alice | Engineering | | Alice | Marketing | | Alice | Sales | | Bob | HR | | Bob | Engineering | | ... | ... | | Eve | Sales | --- ### **Summary Table** | **JOIN Type** | **Description** | | --- | --- | | `INNER JOIN` | Only matching rows from both tables | | `LEFT JOIN` | All rows from left table + matching from right | | `RIGHT JOIN` | All rows from right table + matching from left | ### UNION ALL:- ```sql SELECT E.EmpName, D.DeptName FROM Employees E LEFT JOIN Departments D ON E.DeptID = D.DeptID UNION ALL SELECT E.EmpName, D.DeptName FROM Employees E RIGHT JOIN Departments D ON E.DeptID = D.DeptID WHERE E.DeptID IS NULL; -- Avoid double-counting matches ``` | EmpName | DeptName | | --- | --- | | Alice | HR | | Bob | Engineering | | Charlie | Marketing | | Diana | NULL | | Eve | NULL | | NULL | Sales | > * `UNION`: Removes duplicates between the two queries. > > * `UNION ALL`: Includes all rows, including duplicates. > ### **Self JOIN:-** ```sql -- Create the table CREATE TABLE employees ( id INT PRIMARY KEY, name VARCHAR(100), manager_id INT ); -- Insert sample data INSERT INTO employees (id, name, manager_id) VALUES (1, 'Alice', NULL), (2, 'Bob', 1), (3, 'Charlie', 1), (4, 'David', 2), (5, 'Eve', 2); -- Self join: get employees and their manager's name SELECT e.id AS employee_id, e.name AS employee_name, m.name AS manager_name FROM employees e LEFT JOIN employees m ON e.manager_id = m.id; ``` | employee\_id | employee\_name | manager\_name | | --- | --- | --- | | 1 | Alice | NULL | | 2 | Bob | Alice | | 3 | Charlie | Alice | | 4 | David | Bob | | 5 | Eve | Bob | > Project1:- ```sql SELECT DISTINCT name_hos -- Get different hospital names FROM pre -- From the table called 'pre' WHERE name_hos IS NOT NULL -- Only names that are not empty AND TRIM(name_hos) != '' -- And not just spaces ORDER BY name_hos; -- Put the names in order ----------------------------------------------------------------------------------------------------- --SELECT p51.basic_diagnosis, COUNT(DISTINCT pre.pat_id) AS patient_count --FROM pre --JOIN demohealth.p51 AS p51 ON pre.preid = p51.preid --WHERE pre.name_hos = ? --AND ( -- DATE(pre.entrydate) BETWEEN ? AND ? --OR DATE(pre.apptdate) BETWEEN ? AND ? -- ) --GROUP BY p51.basic_diagnosis --ORDER BY patient_count DESC ---------------------------------------- SELECT p51.basic_diagnosis, -- Get the diagnosis name COUNT(DISTINCT pre.pat_id) AS patient_count -- Count unique patients for each diagnosis FROM pre -- From the table named 'pre' JOIN demohealth.p51 AS p51 ON pre.preid = p51.preid -- Join with 'p51' table where preid matches WHERE pre.name_hos = 'VBCR019' -- Only for hospital named 'VBCR019' AND ( DATE(pre.entrydate) BETWEEN '2013-01-01' AND '2024-12-31' -- Dates between 2013 and 2024 for entry date OR DATE(pre.apptdate) BETWEEN '2013-01-01' AND '2024-12-31' -- Or dates between 2013 and 2024 for appointment date ) GROUP BY p51.basic_diagnosis -- Group results by diagnosis ORDER BY patient_count DESC; -- Show diagnoses with most patients first ----------------------------------------------------------------------------------------------------- --SELECT pre.pat_id, p51.basic_diagnosis, p51.value, MIN(pre.preid) AS preid --FROM pre -- JOIN demohealth.p51 AS p51 ON pre.preid = p51.preid -- WHERE pre.name_hos = ? -- AND p51.basic_diagnosis = ? -- AND ( -- DATE(pre.entrydate) BETWEEN ? AND ? -- OR DATE(pre.apptdate) BETWEEN ? AND ? --) -- GROUP BY pre.pat_id, p51.basic_diagnosis, p51.value -- ORDER BY MIN(pre.entrydate) DESC ---------------------------------------- SELECT pre.pat_id, -- Patient ID p51.basic_diagnosis, -- Diagnosis name p51.value, -- Related value for diagnosis MIN(pre.preid) AS preid -- Smallest preid for this patient and diagnosis FROM pre JOIN demohealth.p51 AS p51 ON pre.preid = p51.preid -- Join on preid WHERE pre.name_hos = 'VBCR019' -- Hospital name to filter AND p51.basic_diagnosis = 'DM' -- Diagnosis to filter AND ( DATE(pre.entrydate) BETWEEN '2020-01-01' AND '2024-12-31' -- Entry dates between Jan 1, 2020 and Dec 31, 2024 OR DATE(pre.apptdate) BETWEEN '2020-01-01' AND '2024-12-31' -- Or appointment dates in the same range ) GROUP BY pre.pat_id, p51.basic_diagnosis, p51.value -- Group by patient, diagnosis, value ORDER BY MIN(pre.entrydate) DESC; -- Sort by earliest entry date, newest first ----------------------------------------------------------------------------------------------------- ``` > Project2:- ```sql SELECT DISTINCT name_hos -- Get only different (unique) hospital names FROM healthcaredb.pre -- From the 'pre' table in the 'healthcaredb' database ORDER BY name_hos; -- Sort the hospital names in alphabetical order ----------------------------------------------------------------------------------------------------------------------- SELECT DISTINCT basic_diagnosis -- Get only unique diagnosis names FROM demohealth.p51 -- From the 'p51' table in the 'demohealth' database ORDER BY basic_diagnosis; -- Sort the diagnosis names alphabetically ---------------------------------------------------------------------------------------------------------------------- ----------------------------------------------------------------------------------------------------------------------------------------------------- --SELECT DISTINCT pr.preid -- FROM healthcaredb.pre pr --JOIN demohealth.p51 p ON pr.preid = p.preid --WHERE TRIM(UPPER(p.basic_diagnosis)) = TRIM(UPPER(?)) --AND TRIM(UPPER(pr.name_hos)) = TRIM(UPPER(?)) --AND pr.entrydate BETWEEN ? AND ? ----------------------------------------------- SELECT DISTINCT pr.preid -- Get unique preid values FROM healthcaredb.pre pr -- From the 'pre' table JOIN demohealth.p51 p ON pr.preid = p.preid -- Join with 'p51' table on preid WHERE TRIM(UPPER(p.basic_diagnosis)) = TRIM(UPPER('DM')) -- Filter diagnosis (e.g., 'Diabetes') AND TRIM(UPPER(pr.name_hos)) = TRIM(UPPER('VBCR019')) -- Filter hospital name (e.g., 'City Hospital') AND pr.entrydate BETWEEN '2016-03-15' AND '2024-11-30'; -- Random date range between 2013 and 2024 ---------------------------------------------------------------------------------------------------------------------- ----------------------------------------------------------------------------------------------------------------------------------------------------- --SELECT DISTINCT basic_diagnosis AS disease --FROM demohealth.p51 --WHERE preid = ? SELECT DISTINCT basic_diagnosis AS disease FROM demohealth.p51 WHERE preid = 1713347150718; ----------------------------------------------------------------------------------------------------------------------------------------------------- ----------------------------------------------------------------------------------------------------------------------------------------------------- --SELECT DISTINCT pr.preid --FROM healthcaredb.pre pr --JOIN demohealth.p51 p ON pr.preid = p.preid --WHERE TRIM(UPPER(p.basic_diagnosis)) = TRIM(UPPER(?)) -- AND TRIM(UPPER(pr.name_hos)) = TRIM(UPPER(?)) -- AND pr.entrydate BETWEEN ? AND ? ---------------------------------------------------- SELECT DISTINCT pr.preid -- Select unique preid values (no duplicates) FROM healthcaredb.pre pr -- From the 'pre' table in 'healthcaredb' (alias pr) JOIN demohealth.p51 p ON pr.preid = p.preid -- Join with 'p51' table on matching preid WHERE TRIM(UPPER(p.basic_diagnosis)) = TRIM(UPPER('CMD')) -- Filter by diagnosis 'Diabetes' (case-insensitive, trimmed) AND TRIM(UPPER(pr.name_hos)) = TRIM(UPPER('VBCR019')) -- Filter by hospital name 'City Hospital' (case-insensitive, trimmed) AND pr.entrydate BETWEEN '2015-01-01' AND '2024-12-31'; -- Filter records with entrydate between Jan 1, 2015 and Dec 31, 2023 ---------------------------------------------------------------------------------------------------------------------------------------------------- ----------------------------------------------------------------------------------------------------------------------------------------------------- --SELECT basic_diagnosis AS disease, COUNT(DISTINCT preid) AS count --FROM demohealth.p51 --WHERE preid IN (${placeholders}) --AND TRIM(UPPER(basic_diagnosis)) != TRIM(UPPER(?)) --AND TRIM(UPPER(basic_diagnosis)) != 'OTHERS' --GROUP BY basic_diagnosis --ORDER BY count DESC --LIMIT ?- ---------------------------------------- SELECT basic_diagnosis AS disease, -- Rename 'basic_diagnosis' column as 'disease' COUNT(DISTINCT preid) AS count -- Count how many unique 'preid' values for each diagnosis FROM demohealth.p51 -- Use the 'p51' table from 'demohealth' database WHERE preid IN (1001, 1002, 1003, 1004) -- Filter rows where 'preid' is in this list (replace with real IDs) AND TRIM(UPPER(basic_diagnosis)) != TRIM(UPPER('Flu')) -- Exclude the diagnosis 'Flu' (case-insensitive) AND TRIM(UPPER(basic_diagnosis)) != 'OTHERS' -- Also exclude the diagnosis labeled as 'OTHERS' GROUP BY basic_diagnosis -- Group results by diagnosis ORDER BY count DESC -- Show diagnoses with the most patients first LIMIT 5; -- Show only the top 5 results ---------------------------------------------------------------------------------------------------------------------------------------------------- ----------------------------------------------------------------------------------------------------------------------------------------------------- ```