🔳 `A` ${\color{Red}2}$ `Z` SQL Solutions ${\color{Green}(using\ SQL\ SERVER)}$

©️ ${\color{grey}Developed\ and\ Maintained\ by:}$ ${\color{blue}Md\ Imtiyaz\ Alam}$

👇 ${\color{red}NAVIGATION}$

Categories	Sub_Categories
DDL ▶️	SCHEMA	CREATE	ALTER	DROP	TRUNCATE
DML ▶️	INSERT	UPDATE	DELETE	STORED_PROCEDURE
DQL ▶️	SELECT	WILDCARDS	CLAUSES	JOINS
DCL ▶️	GRANT	REVOKE
TCL ▶️	COMMIT	ROLLBACK	SAVEPOINT	TRANSACTION
SFL ▶️	STRING_FUNC	MATH_FUNC	DATE_FUNC	LOGIC_FUNC	CONVERT_FUNC
WFL ▶️	AGGREGATE_FUNC	RANK_FUNC	ANALY_FUNC
OTHERS ▶️	Use	Constraints	View	Index	Normalization
	Rename	Set	Data_Warehouse	Trigger	CTE
	Arith_Operator	Comp_Operator	Log_Operator	Union	Intersect

◻️ Objective

The objective of the SQL report is to understand all the Basic SQL-Server Queries/Scripts as well as all kind of String Functions.

◻️ Tech Stack

SQL Server

◻️ Steps includes

Creating a Database FSA
Creating two Tables Student and Exams
Applying all categories of SQL Commands
- ${\color{blue}DDL}$: Defines Database structures.
- ${\color{blue}DML}$: Manipulates Data.
- ${\color{blue}DQL}$: Queries and Retrieves data.
- ${\color{blue}DCL}$: Manages access Permissions.
- ${\color{blue}TCL}$: Controls Transactions.
- ${\color{blue}SFL}$: Returns a single value.
- ${\color{blue}WFL}$: Returns a value based on a set of rows.

◻️ Categories of applied SQL Commands

${\color{red}DDL}$ (Data Definition Language): It changes the structure of the table like creating a table, deleting a table, altering a table, etc. All the command of DDL are auto-committed that means it permanently save all the changes in the database.
- SCHEMA
- CREATE [Define DATA TYPES, CONSTRAINTS here]
- ALTER (Rename/Add/Drop)
- DROP
- TRUNCATE
${\color{red}DML}$ (Data Manipulation Language): DML commands are used to modify the database. The command of DML is not auto-committed that means it can't permanently save all the changes in the database. They can be rollback.
- INSERT
- UPDATE
- DELETE
- STORED PROCEDURE
${\color{red}DQL}$ (Data Query Language): DQL is used to fetch the data from the database.
- SELECT [mostly OPERATORS used here]
- CLAUSES [WHERE, GROUP BY, HAVING, ORDER BY, LIMIT/TOP]
- JOINS [JOIN, LEFT JOIN, RIGHT JOIN, FULL JOIN, SELF JOIN, CROSS JOIN, UNION, UNION ALL, INTERSECT]
${\color{red}DCL}$ (Data Control Language): DCL commands are used to Grant and Revoke (take back) authority from any database user.
- GRANT
- REVOKE
${\color{red}TCL}$ (Transaction Control Language): TCL commands can only use with DML commands like INSERT, DELETE and UPDATE only.
- BEGIN TRANSACTION
- COMMIT
- ROLLBACK
- SAVEPOINT
${\color{red}SFL}$ (Scalar Functions Library): SFL commands are used to return a single value based on the input provided.
- STRING FUNCTIONS [CONCAT, SUBSTRING, CHARINDEX, PATINDEX, CHAR LENGTH, LETTER CASE, TRIM, REPLACE, LEFT/RIGHT, REVERSE, REPLICATE, FORMAT]
- MATHEMATICAL FUNCTIONS [ABS, ROUND, SQRT, CEILING, FLOOR, POWER, EXP, LOG]
- DATE FUNCTIONS [GETDATE, YEAR, MONTH, DAY, DATEADD, DATEDIFF]
- CONVERSION FUNCTIONS [CAST, CONVERT, TRY_CAST, TRY_CONVERT]
- LOGICAL FUNCTIONS [COALESCE, ISNULL]
${\color{red}WFL}$ (Windows Functions Library): WFL commands are used to return a value based on a set of rows related to the current row.
- AGGREGATE FUNCTIONS [COUNT, AVG, SUM, MIN, MAX]
- RANKING FUNCTIONS [ROW_NUMBER, DENSE_RANK, RANK, NTILE]
- ANALYTIC FUNCTIONS [CUME_DIST, PERCENT_RANK, FIRST_VALUE, LAST_VALUE, LAG, LEAD]

◻️ Detailed view of all SQL-Server Commands with `QUERIES` and `SCRIPTS`

Table: STUDENT
+-------------+-----------+-----------------+--------+-----------------+---------+----------------+-------+-----+------------+
|   Adm_No    | DOJ       | Stud_Name       | Gender | Guardian_Name   | Address | Contact_Number | Class | Fee | Monitor_ID |
+-------------+-----------+-----------------+--------+-----------------+---------+----------------+-------+-----+------------+
|  ROSE00023  | 10/1/2021 | Abu Talha       |   M    | Md Fareed       | Delhi   |   7903077297   |   10  | 400 | NULL       |
|  ROSE00024  | 10/1/2021 | Abu Salesh      |   M    | Md Fareed       | Delhi   |   7903077297   |   8   | 450 | ROSE00023  |
|  ROSE00040  | 10/1/2021 | Md Neyamul      |   M    | Md Shamsuddin   | Chennai |   9661194838   |   7   | 350 | ROSE00023  |
|  ROSE00041  | 6/8/2021  | Ruba Parveen    |   F    | Md Parwez       | Delhi   |   9693461570   |   5   | 275 | ROSE00023  |
|  ROSE00058  | 10/2/2021 | Md Muntazeem    |   M    | Md Naimuddin    | Pune    |   8292149189   |   10  | 325 | ROSE00023  |
|  ROSE00102  | 10/29/2021| Mantasha Khatoon|   F    | Hasnain         | Noida   |   9709148101   |   6   | 250 | ROSE00144  |
|  ROSE00144  | 12/1/2021 | Arju Kumar      |   M    | Ranjit Kumar Sah| Mumbai  |   6206863026   |   8   | 300 | ROSE00058  |
|  ROSE00145  | 12/1/2021 | Roji Kumari     |   F    | Ranjit Kumar Sah| Mumbai  |   6206863026   |   7   | 300 | ROSE00144  |
|  ROSE00172  | 12/4/2021 | Md Azfar        |   M    | Md Mushtaque    | Sikkim  |   7631041561   |   10  | 300 | ROSE00058  |
|  ROSE00331  | 2/3/2023  | Juveria Khatoon |   F    | Saud Alam       | Chennai |   7330859950   |   8   | 300 | ROSE00058  |
|  ROSE00335  | 9/4/2023  | Manish Kumar    |   M    | Ranjan Kumar    | Noida   |   9060609777   |   9   | 350 | ROSE00058  |
|  ROSE00041  | 6/8/2021  | Ruba Parveen    |   F    | Md Parwez       | Delhi   |   9693461570   |   5   | 275 | ROSE00144  |
+-------------+-----------+-----------------+--------+-----------------+---------+----------------+-------+-----+------------+

Table: EXAMS
+------------+--------------+--------------+----------------+------------+
|   Adm_No   | Subject_Code | Subject_Name | Marks_Obtained | Exam_Date  |
+------------+--------------+--------------+----------------+------------+
|  ROSE00001 |    SUB004    | S.St.        |      73        | 12/11/2021 |
|  ROSE00015 |    SUB002    | Science      |      66        | 12/13/2021 |
|  ROSE00023 |    SUB003    | English      |      95        | 12/09/2021 |
|  ROSE00024 |    SUB001    | Mathematics  |      86        | 12/10/2021 |
|  ROSE00040 |    SUB002    | Science      |      80        | 12/08/2021 |
|  ROSE00041 |    SUB002    | Science      |      78        | 12/02/2021 |
|  ROSE00050 |    SUB003    | English      |      56        | 12/14/2021 |
|  ROSE00051 |    SUB001    | Mathematics  |      67        | 12/15/2021 |
|  ROSE00058 |    SUB001    | Mathematics  |      90        | 12/07/2021 |
|  ROSE00065 |    SUB002    | Science      |      81        | 12/16/2021 |
|  ROSE00102 |    SUB001    | Mathematics  |      85        | 12/01/2021 |
|  ROSE00108 |    SUB005    | Computer     |         NULL   |   NULL     |
|  ROSE00144 |    SUB002    | Science      |      74        | 12/05/2021 |
|  ROSE00145 |    SUB003    | English      |      89        | 12/06/2021 |
|  ROSE00172 |    SUB003    | English      |      92        | 12/03/2021 |
|  ROSE00331 |    SUB001    | Mathematics  |      88        | 12/04/2021 |
+------------+--------------+--------------+----------------+------------+

📗 DDL (`Data Definition Language`)

🔘 ${\color{blue}USE}$ (SCL)

🏠 Home

+---------------------------------------------------------------------------------------------------------------------------------+
| Specifically it comes under SCL (Session Control Language). It is used to select a specific Database to work with in a session. |
+---------------------------------------------------------------------------------------------------------------------------------+

🔸 Using an Existing `DATABASE` named 'FSA'

      USE FSA;

🔘 ${\color{blue}SCHEMA}$

🏠 Home

+----------------------------------------------------------------------------------------------------------------------------------+
| A schema is like a container for Database objects (tables) that can contain multiple relational tables for a project/department. |
| A database might have multiple schemas                                                                                           |
+----------------------------------------------------------------------------------------------------------------------------------+

🔹 Create a new `SCHEMA` named 'INSTITUTE'

      CREATE SCHEMA INSTITUTE;

🔹 Create a Table `within Schema` INSTITUTE

      CREATE TABLE INSTITUTE.RESULT (
                                      Adm_No VARCHAR(20) PRIMARY KEY,
                                      Tot_Marks INT,
                                      Grade VARCHAR(30)
                                    );

🔹 Transfer Table `Student` into Schema `INSTITUTE`

      ALTER SCHEMA INSTITUTE TRANSFER dbo.Student;     -- dbo.student means student table in current database (dbo) transferring to schema 'Institute'

      ALTER SCHEMA INSTITUTE TRANSFER HR.Student;     -- HR.student means student table in Old schema (HR) transferring to New schema 'Institute' in current database

🔘 ${\color{blue}CREATE}$

🏠 Home

+--------------------------------------------------------------------------+
| It is used to Create new Databases, Tables, Constraints, Views, Indexes. |
+--------------------------------------------------------------------------+

🔸 Create a new `DATABASE` named 'FSA'

      CREATE DATABASE FSA;

🔸 Create a new `TABLE` named 'STUDENT'

      CREATE TABLE STUDENT (
      Adm_No VARCHAR(20) PRIMARY KEY,
      DOJ DATE,
      Stud_Name VARCHAR(50),
      Gender CHAR(10),
      Guardian_Name VARCHAR(50),
      Address VARCHAR(150),
      Contact_Number BIGINT,
      Class INT,
      Fee DECIMAL(10, 2)
      );

🔸 Create the Table Student with `CONSTRAINTS` (inline)

SQL CONSTRAINTS are used to specify rules for the data in a table. Constraints are used to limit the type of data that can go into a table.

      CREATE TABLE STUDENT (
      Adm_No VARCHAR(10) PRIMARY KEY,                    -- PRIMARY KEY constraint (takes by default NOT NULL) constraint on Admission number
      DOJ DATE NOT NULL,                                 -- Date of Joining, NOT NULL constraint
      Stud_Name VARCHAR(50) NOT NULL,                    -- Student Name, NOT NULL constraint
      Gender CHAR(1) CHECK (Gender IN ('M', 'F')),       -- CHECK constraint ensuring Gender is either 'M' or 'F'
      Guardian_Name VARCHAR(50) NOT NULL,                -- Guardian Name, NOT NULL constraint
      Address VARCHAR(100),                              -- Address
      Contact_Number VARCHAR(15),                        -- Contact Number
      Class INT CHECK (Class BETWEEN 1 AND 12),          -- CHECK constraint ensuring Class is between 1 and 12
      Fee DECIMAL(10, 2) CHECK (Fee > 0)                 -- CHECK constraint ensuring Fee is positive
      Class_Time time(10) DEFAULT '07:05:00' NOT NULL    -- DEFAULT with NOT NULL, 2 constraint, IF USER DOESN'T INSERT ANY VALUE IN Class_Time, WILL AUTOMATICALLY TAKE Default Time '07:05:00'
      );

🔸 Create a `VIEW` named `Class10_Students`

SQL VIEWS are simplified data access, minimize the Query. It is also known as Virtual Table or Query Table because it does not store the rows and columns on the disk. It can lead to performance issues because it is not actual table

      CREATE VIEW Class10_Students AS
      SELECT Adm_No, Stud_Name, Gender, Guardian_Name, Contact_Number, Fee
      FROM STUDENT
      WHERE Class = 10;

Query to check the VIEWS in a Table

      SELECT * FROM Class10_Students;

🔸 Create an `INDEX` `idx_StudName`

An Index in SQL is like a table of contents in a book. It helps SQL Server quickly locate and retrieve the data from a table without having to scan the entire table.

      CREATE INDEX idx_StudName
      ON STUDENT (Stud_Name);

Query to check the INDEXES in a Table

      EXEC sp_helpindex 'STUDENT';

Without an Index (Left side): SQL Server searches the whole table (slow).
With an Index (Right side): SQL Server jumps directly to the rows you're looking for (fast).

🔸 Create Tables under `NORMALIZATION`

NORMALIZATION: The process of organizing data to reduce complexity and improve data integrity.
1st Normal Form (1NF): Eliminate repeating groups.
Before 1NF

      					-- Original table with multiple phone numbers in one row
	CREATE TABLE StudentInfo (
				   Adm_No VARCHAR(10),
    				   Stud_Name VARCHAR(50),
    				   Phone_Number VARCHAR(100)      -- Comma-separated values
				 );

					-- Inserting a record with multiple phone numbers
	INSERT INTO StudentInfo (Adm_No, Stud_Name, Phone_Number)
	                 VALUES ('ROSE00023', 'Abu Talha', '7903077297, 9823099999');

After 1NF

      					-- Creating a normalized table
	CREATE TABLE StudentInfo_1NF (
                                        Adm_No VARCHAR(10),
    					Stud_Name VARCHAR(50),
    					Phone_Number VARCHAR(15)
				     );

					-- Insert each phone number as a separate row
	INSERT INTO StudentInfo_1NF (Adm_No, Stud_Name, Phone_Number)
	VALUES ('ROSE00023', 'Abu Talha', '7903077297'),
	       ('ROSE00023', 'Abu Talha', '9823099999');

2nd Normal Form (2NF): Remove partial dependencies (Non-prime attributes depend on the whole primary key).
Before 2NF

				-- Assume a student’s address and class information depend on both the Adm_No and the Class, which violates 2NF
      						-- Original table with partial dependency
	CREATE TABLE StudentInfo_2NF (
    					Adm_No VARCHAR(10),
    					Class VARCHAR(10),
    					Stud_Name VARCHAR(50),
    					Address VARCHAR(50)
				     );

						-- Inserting data
	INSERT INTO StudentInfo_2NF (Adm_No, Class, Stud_Name, Address)
	                     VALUES ('ROSE00023', '10', 'Abu Talha', 'Delhi');

After 2NF

			-- Create separate tables for Student and Class
      						-- Student table (Adm_No is the primary key)
	CREATE TABLE Student (
    				Adm_No VARCHAR(10),
    				Stud_Name VARCHAR(50),
    				Address VARCHAR(50)
			     );

						-- Class table (Separate the class info)
	CREATE TABLE ClassInfo (
    				Adm_No VARCHAR(10),
    				Class VARCHAR(10)
			       );

						-- Insert into the Student table
	INSERT INTO Student (Adm_No, Stud_Name, Address)
	             VALUES ('ROSE00023', 'Abu Talha', 'Delhi');

						-- Insert into the ClassInfo table
	INSERT INTO ClassInfo (Adm_No, Class)
	               VALUES ('ROSE00023', '10');

3rd Normal Form (3NF): Remove transitive dependencies (Non-prime attributes depend on non-prime attributes).
Before 3NF

		-- Assume the Guardian_Name and Contact_Number depend on the student’s Adm_No, but the Contact_Number is dependent on the Guardian_Name, violating 3NF
      						-- Original table with transitive dependency
	CREATE TABLE StudentGuardianInfo (
					   Adm_No VARCHAR(10),
    					   Stud_Name VARCHAR(50),
    					   Guardian_Name VARCHAR(50),
    					   Contact_Number VARCHAR(15)
					 );

						-- Inserting data
	INSERT INTO StudentGuardianInfo (Adm_No, Stud_Name, Guardian_Name, Contact_Number)
	                         VALUES ('ROSE00023', 'Abu Talha', 'Md Fareed', '7903077297');

After 3NF

			-- Create a separate table for Guardian to remove the transitive dependency
      					-- Student table (Adm_No is the primary key)
	CREATE TABLE Student (
    				Adm_No VARCHAR(10),
    				Stud_Name VARCHAR(50)
			     );

					-- Guardian table (Guardian_Name is the primary key)
	CREATE TABLE Guardian (
    				Guardian_Name VARCHAR(50),
    				Contact_Number VARCHAR(15)
			      );

					-- StudentGuardianInfo table to link students to their guardians
	CREATE TABLE StudentGuardianInfo (
					   Adm_No VARCHAR(10),
    					   Guardian_Name VARCHAR(50)
					 );

					-- Insert into the Student table
	INSERT INTO Student (Adm_No, Stud_Name)
	             VALUES ('ROSE00023', 'Abu Talha');

					-- Insert into the Guardian table
	INSERT INTO Guardian (Guardian_Name, Contact_Number)
	              VALUES ('Md Fareed', '7903077297');

					-- Insert into the StudentGuardianInfo table
	INSERT INTO StudentGuardianInfo (Adm_No, Guardian_Name)
	                         VALUES ('ROSE00023', 'Md Fareed');

🔘 ${\color{blue}ALTER}$

🏠 Home

+---------------------------------------------------------------------------------------+
| It is used to Alter (change) the structure of the Table and the name of the Database. |
+---------------------------------------------------------------------------------------+

🔹 Alter a `DATABASE` FSA to 'FSA_new'

      ALTER DATABASE FSA
      Modify Name = FSA_new;

🔹 Rename a `Table` STUDENTS to 'STUDENT'

      EXEC sp_rename 'Students', 'Student';

🔹 Rename a Table `Column` Contact_No to 'Contact_Number'

      EXEC sp_rename 'student.Contact_No', 'Contact_Number';

🔹 Add a new `column` 'Email' to table Student

      ALTER TABLE STUDENT
      ADD Email VARCHAR(100);

🔹 Modify a `column (change data type)` BIGINT to 'VARCHAR' for `Contact_Number` column

      ALTER TABLE STUDENT
      ALTER COLUMN Contact_Number VARCHAR(20);

🔹 Modify a `column (change length of data type)` VARCHAR(50) to 'VARCHAR(100)' for `Stud_Name` column

      ALTER TABLE STUDENT
      ALTER COLUMN Stud_Name VARCHAR(100);

🔹 Drop a column 'Email' from table Student

      ALTER TABLE STUDENT
      DROP COLUMN Email;

🔹 Add a default value of `300` to 'Fee' column by adding CONSTRAINT

      ALTER TABLE STUDENT
      ADD CONSTRAINT DF_Fee DEFAULT 300 FOR Fee;

🔘 ${\color{blue}DROP}$

🏠 Home

+-----------------------------------------------------------------------+
| It is used to Delete/Remove the objects from the Database completely. |
+-----------------------------------------------------------------------+

🔸 Drop the `Database` 'FSA'

      DROP DATABASE FSA;

🔸 Drop the `Schema` 'HR'

      DROP SCHEMA HR;

🔸 Drop the `Table` 'Student'

      DROP TABLE STUDENT;

🔸 Drop a column 'Email' from table Student

      ALTER TABLE STUDENT
      DROP COLUMN Email;

🔸 Drop the `View` 'Class10_Students'

      DROP VIEW Class10_Students;

🔸 Drop an `Index` 'idx_StudName'

      DROP INDEX idx_StudName ON STUDENT;          -- Syntax: Index_Name ON Table_Name

      Drop Index Student.idx_StudName;             -- Syntax: Table_Name.Index_Name

🔘 ${\color{blue}TRUNCATE}$

🏠 Home

+-----------------------------------------------------------------------------------------------------------------------+
| It is used to Remove/Delete all records (rows) from a table, but the table structure (Column names/headings) remains. |
+-----------------------------------------------------------------------------------------------------------------------+

🔹 Truncate the `Table` 'Student'

      TRUNCATE TABLE STUDENT;

📗 DML (`Data Manipulation Language`)

🔘 ${\color{blue}INSERT}$

🏠 Home

+-------------------------------------------------+
| It is used to Add new data/values into a table. |
+-------------------------------------------------+

'Column names' must match the order of the values.
'Dates' should be provided in YYYY-MM-DD format.
The 'Adm_No' is the primary key and must be unique for each row.

🔹 Insert Data/Values (single record) into a Table `Student`

      INSERT INTO STUDENT (Adm_No, DOJ, Stud_Name, Gender, Guardian_Name, Address, Contact_Number, Class, Fee)
      VALUES ('ROSE00023', '2021-10-01', 'Abu Talha', 'M', 'Md Fareed', 'Khiripaghar', '7903077297', 10, 400);

🔹 Insert Data/Values (Multiple records) into a Table `Student`

      INSERT INTO STUDENT (Adm_No, DOJ, Stud_Name, Gender, Guardian_Name, Address, Contact_Number, Class, Fee)
      VALUES 
      ('ROSE00023', '2021-10-01', 'Abu Talha', 'M', 'Md Fareed', 'Khiripaghar', '7903077297', 10, 400),
      ('ROSE00024', '2021-10-01', 'Abu Salesh', 'M', 'Md Fareed', 'Khiripaghar', '7903077297', 8, 450),
      ('ROSE00040', '2021-10-01', 'Md Neyamul', 'M', 'Md Shamsuddin', 'Gauripur', '9661194838', 7, 350),
      ('ROSE00041', '2021-06-08', 'Ruba Parveen', 'F', 'Md Parwez', 'Khiripaghar', '9693461570', 5, 275),
      ('ROSE00058', '2021-10-02', 'Md Muntazeem', 'M', 'Md Naimuddin', 'Rajapur', '8292149189', 10, 325),
      ('ROSE00102', '2021-10-29', 'Mantasha Khatoon', 'F', 'Hasnain', 'Nayadih', '9709148101', 6, 250),
      ('ROSE00144', '2021-12-01', 'Arju Kumar', 'M', 'Ranjit Kumar Sah', 'Chilmil', '6206863026', 8, 300),
      ('ROSE00145', '2021-12-01', 'Roji Kumari', 'F', 'Ranjit Kumar Sah', 'Chilmil', '6206863026', 7, 300),
      ('ROSE00172', '2021-12-04', 'Md Azfar', 'M', 'Md Mushtaque', 'Maghota', '7631041561', 10, 300),
      ('ROSE00331', '2023-02-03', 'Juveria Khatoon', 'F', 'Saud Alam', 'Chihar', '7330859950', 8, 300);

🔹 Insert and COPY complete Data/Values from Old Source Table `Student_Old` into a New Destination Table `Student` with `Same Columns`

Data Warehousing: It is the process of storing and managing large volumes of data from multiple sources for analysis and reporting.
ETL: The process of combining data from multiple sources into a data warehouse is called extract, transform, and load (ETL).
Data Mining: Discovering patterns, trends, and useful information from large datasets stored in a database like Aggregation, or other calculations.

      INSERT INTO STUDENT (Adm_No, DOJ, Stud_Name, Gender, Guardian_Name, Address, Contact_Number, Class, Fee)
		   SELECT Adm_No, DOJ, Stud_Name, Gender, Guardian_Name, Address, Contact_Number, Class, Fee
      FROM Student_Old;

🔘 ${\color{blue}UPDATE}$

🏠 Home

+-----------------------------------------------------------+
| It is used to Modify existing data/values within a table. |
+-----------------------------------------------------------+

The WHERE clause ensures that only the specified row (e.g., Adm_No = 'ROSE00023') is updated.
Without WHERE, all rows in the table would be updated!

🔸 Update (SET) `Single value/row` (Class) in a Table 'Student'

     UPDATE STUDENT
     SET Class = 9
     WHERE Adm_No = 'ROSE00040';

🔸 Update (SET) `Multiple (same) values/same Column` (Gender) in a Table 'Student' if Column has NULL values

     UPDATE STUDENT
     SET Gender = 'M'
     Where Gender IS NULL;

🔸 Update (SET) `Multiple (different) values/same row` (Stud_Name, Fee and DOJ) in a Table 'Student'

     UPDATE STUDENT
     SET Stud_Name = 'Abu Talha Khan', Fee = 450, DOJ = '2021-10-04'
     WHERE Adm_No = 'ROSE00023';

🔸 Update (SET) `Multiple (same) values/same Column` (Gender) in a Table 'Student'

     UPDATE STUDENT
     SET Gender = 'Fem'
     WHERE Adm_No IN ('ROSE00145', 'ROSE00331', 'ROSE00041');

🔸 Update (SET with CASE, ELSE, END) `Multiple (different) values/different Column` (FEE) in a Table 'Student'

for ELSE statement when we keep the same column name (i.e. "Fee") as SET statement, then Rest columns value remain same

        UPDATE STUDENT
           SET Fee = CASE
               WHEN Adm_No='ROSE00024' THEN 450
               WHEN Adm_No='ROSE00041' THEN 275
               WHEN Adm_No='ROSE00058' THEN 325
               WHEN Adm_No='ROSE00102' THEN 250
               WHEN Adm_No='ROSE00023' THEN 400
               WHEN Adm_No='ROSE00040' THEN 350
           ELSE Fee                                       -- for ELSE statement when we keep the same column name (i.e. "Fee") as SET statement, then Rest columns value remain same
        END;

🔸 Update (SET with CASE, ELSE, END) `Multiple (different) values/different Column` (FEE) in a Table 'Student'

for ELSE statement when we keep any value (i.e. "199") different from SET statement, then Rest columns take the default value "199"

        UPDATE STUDENT
           SET Fee = CASE
               WHEN Adm_No='ROSE00024' THEN 450
               WHEN Adm_No='ROSE00041' THEN 275
               WHEN Adm_No='ROSE00058' THEN 325
               WHEN Adm_No='ROSE00102' THEN 250
               WHEN Adm_No='ROSE00023' THEN 400
               WHEN Adm_No='ROSE00040' THEN 350
           ELSE 199                                       -- for ELSE statement when we keep any value (i.e. "199") different from SET statement, then Rest columns take the default value "199"
        END;

🔘 ${\color{blue}DELETE}$

🏠 Home

+-----------------------------------------+
| It is used to Remove data from a table. |
+-----------------------------------------+

WHERE clause specifies which rows to delete (e.g., rows with Adm_No = 'ROSE00023').
If you omit the WHERE clause, all rows in the table will be deleted!

🔹 Delete single Row/Record from a Table `Student` by the reference of one `Primary-Key` Value

      DELETE FROM STUDENT
      WHERE Adm_No = 'ROSE00023';                         -- One Primary-Key Value

🔹 Delete Multiple Rows/Records from a Table `Student` by the reference of many `Primary-Key` Values

The IN operator allows you to match multiple values in a column.
This query will delete Both rows where Adm_No is either 'ROSE00023' or 'ROSE00024'.

      DELETE FROM STUDENT
      WHERE Adm_No IN ('ROSE00023', 'ROSE00024');         -- Multiple Primary-Key Values by using "IN" operator

🔹 Delete Multiple Rows/Records from a Table `Student` by the reference of one `Non-Key` Value

      DELETE FROM STUDENT
      WHERE Class = 10;                                   -- One Non-Key Value

🔘 ${\color{blue}STORED\ PROCEDURE}$

🏠 Home

+---------------------------------------------------------------------------------------------+
| It is a precompiled SQL code stored in the database, that can be executed as a single unit. |
| If any SQL query need to write again and again then same can be saved as Stored Procedure.  |
+---------------------------------------------------------------------------------------------+

🔹 CREATE a Table 'EmpInfo' `Outside Stored Procedure`

      CREATE TABLE EmpInfo (
                      Emp_ID INT PRIMARY KEY,                    -- Constraint PRIMARY KEY (by default contains NOT NULL)
                      Emp_Name VARCHAR(50) NOT NULL,             -- Constraint NOT NULL
                      Department VARCHAR(50),
                      Salary INT NOT NULL CHECK (Salary > 10000) -- Constraints NOT NULL & Checks Salary more than 10,000
                     );

🔹 `Stored Procedure` to `INSERT` Records into 'EmpInfo' Table

      CREATE PROCEDURE InsertEmpInfo
				     @Emp_ID INT,               -- '@' symbol is used to declare variables or parameters in SQL Server
                                     @Emp_Name VARCHAR(50),
                                     @Department VARCHAR(50),
                                     @Salary INT
      AS
      BEGIN
	 INSERT INTO EmpInfo (Emp_ID, Emp_Name, Department, Salary)
	              VALUES (@Emp_ID, @Emp_Name, @Department, @Salary);
      END;

                    -- SP can be called by EXEC command and pass the required parameters
                    -- Inserting a new employee into the EmpInfo table, Can be inserted one or multi values/informations using EXEC command

      EXEC InsertEmpInfo @Emp_ID = 1, @Emp_Name = 'John Doe', @Department = 'HR', @Salary = 10500;

      EXEC InsertEmpInfo @Emp_ID = 2, @Emp_Name = 'Jane Smith', @Department = 'Finance', @Salary = 12000;

🔹 `Stored Procedure` to `UPDATE` Records into 'EmpInfo' Table

      CREATE PROCEDURE UpdateEmpInfo
				     @Emp_ID INT,
                                     @Emp_Name VARCHAR(50),
                                     @Department VARCHAR(50),
                                     @Salary INT
      AS
      BEGIN
	 UPDATE EmpInfo
                     SET Emp_Name    =  @Emp_Name, 
                         Department  =  @Department, 
                         Salary      =  @Salary
         WHERE Emp_ID = @Emp_ID;
      END;

      EXEC UpdateEmpInfo                                  -- SP can be called by EXEC command and pass the required parameters
                         @Emp_ID = 1, 
                         @Emp_Name = 'Alice Brown', 
                         @Department = 'IT', 
                         @Salary = 12000;

🔹 `Stored Procedure` to `DELETE` Records from 'EmpInfo' Table

      CREATE PROCEDURE DeleteEmpInfo
                                    @Emp_ID INT           -- Parameter to identify the employee
      AS
      BEGIN
         DELETE FROM EmpInfo
         WHERE Emp_ID = @Emp_ID;
     END;

      EXEC DeleteEmpInfo @Emp_ID = 1;                     -- Delete the employee with Emp_ID = 1

🔹 `Stored Procedure` to `SELECT` Records from 'EmpInfo' Table

      CREATE PROCEDURE EmployeeInfo
      AS                                  -- BEGIN and END are used to define the start and end of the executable block within a stored procedure,
      BEGIN                               -- allowing multiple SQL statements to be grouped together. 
           SELECT * FROM EmpInfo
           WHERE Department = 'HR';      
      END;

🔹 Check Stored Procedure named `EmployeeInfo` from 'EmpInfo' Table

      EXEC EmployeeInfo;

🔹 `Stored Procedure` using a `TRIGGER`

TRIGGER: It is a special kind of stored procedure that automatically executes (or "fires") in response to specific events on a table, such as INSERT, UPDATE, or DELETE operations which helps in automate the tasks.

        CREATE TRIGGER trg_AfterInsert
	AFTER INSERT ON Employee
	FOR EACH ROW
	BEGIN
    		INSERT INTO Employee_Audit (Emp_ID, Change_Type, Change_Date)
    		                    VALUES (NEW.Emp_ID, 'INSERT', NOW());
	END;

📗 DQL (`Data Query Language`)

🔘 ${\color{blue}SELEC T}$

🏠 Home

+------------------------------------------------------------+
| It is used to Retrieve/Fetch data from one or more tables. |
+------------------------------------------------------------+

🔸 Select Current `Date and Time`

      SELECT Getdate() AS [Current Date and Time];             -- To check Current DATE and TIME

      SELECT Sysdatetime() AS [Current Date and Time];         -- To check Current DATE and TIME

      SELECT Cast(Getdate() AS Date) AS [Current Date];        -- To check Current DATE only

      SELECT Cast(Sysdatetime() AS Date) AS [Current Date];    -- To check Current DATE only

🔸 Select `All Databases` of SQL-Server

      SELECT * FROM sys.databases;

🔸 Copy a Table from Old Database to New Database

      USE NewDB;
      SELECT * INTO NewDB.dbo.Student FROM OldDB.dbo.Student;

🔸 Select `All Tables` from a current session Database

      SELECT * FROM sys.tables;

🔸 Select `All Columns` from a Table 'Student'

      SELECT * FROM STUDENT;

🔸 Select `All Columns` from a Table 'Exams'

      SELECT * FROM EXAMS;

🔸 Select `Table` 'Exams' Columns from `Schema` 'Institute'

      SELECT * FROM INSTITUTE.EXAMS;         -- Select Records from Schema (Institute), Table (Exams) = Schema.Table

🔸 Select `Table` 'Exams' Columns from `Schema` 'Institute' in `Database` 'FSA'

      SELECT * FROM FSA.INSTITUTE.EXAMS;         -- Select Records from Database (FSA), Schema (Institute), Table (Exams) = Database.Schema.Table

🔸 Select `Specific Columns` from a Table 'Student'

      SELECT Adm_No, Stud_Name, Class, Fee 
      FROM EXAMS;

🔸 Select Records with a Arithmetic Operators` (+, -, *, /, %)

      SELECT Stud_Name, Fee, Fee + 50 AS Increased_Fee
      FROM STUDENT;                                            -- Addition (+): Increase the FEE by 50 for each student

      SELECT Stud_Name, Fee, Fee - 100 AS Reduced_Fee
      FROM STUDENT;                                            -- Subtraction (-): Decrease 100 from the Fee for each student

      SELECT Stud_Name, Fee, Fee * 2 AS Doubled_Fee
      FROM STUDENT;                                            -- Multiplication (*): Multiply the Fee by 2 for each student

      SELECT Stud_Name, Fee, Fee / 2 AS Halved_Fee
      FROM STUDENT;                                            -- Division (/): Divide the Fee by 2 for each student

      SELECT Stud_Name, Fee, Fee % 100 AS Remainder_Fee
      FROM STUDENT;                                            -- Modulus (%): Find the remainder when Fee is divided by 100

🔸 Select Records with a `Condition` (`Comparison Operators`: =, >, <, >=, <=, !=, <>, !<, !>)

      SELECT * FROM STUDENT
      WHERE Class = 10;                     -- Return all records from STUDENT Table for 10th Class

      SELECT * FROM STUDENT
      WHERE Class <> 5;                     -- Return all records from STUDENT Table for all classes EXCEPT 5th Class [Class not equal to (<> or !=) 5]

      SELECT * FROM STUDENT
      WHERE Fee >= 350;                     -- Return all records from STUDENT Table for those whose Fee more than or equal to 350

      SELECT * FROM STUDENT
      WHERE Fee !> 350;                     -- Return all records from STUDENT Table for those whose Fee not more than 350

🔸 Select Records with Conditions by `Logical Operators`: AND, OR, NOT, IN, BETWEEN, LIKE, ANY/SOME, ALL, IS NULL

      SELECT * FROM STUDENT
      WHERE Fee > 300 AND Class = 8;        -- Return all records from STUDENT Table for 8th Class whose Fee more than 300

      SELECT * FROM STUDENT
      WHERE Class = 10 OR Class = 9;         -- Return all records from STUDENT Table for 8th Class whose Fee more than 300

      SELECT * FROM STUDENT
      WHERE NOT Fee > 350 AND Gender = 'F';   -- Return all records from STUDENT Table whose fee not more than 350 for Females

      SELECT * FROM STUDENT
      WHERE DOJ BETWEEN '2021-10-01' AND '2021-12-04';                 -- Return all records from STUDENT Table who joined between date range

      SELECT * FROM STUDENT
      WHERE Stud_Name LIKE 'Ru%' OR Stud_Name LIKE '%oon';             -- Used 'WILDCARD %' here

      SELECT * FROM EXAMS
      WHERE Subject_Name IN('Science', 'English', 'Computer') AND Marks_Obtained > 90;    -- Return all records from EXAMS Table who get marks above 90 IN Science, English and Computer

      SELECT * FROM STUDENT
      WHERE Fee > ANY (SELECT Fee FROM STUDENT WHERE Class = 8);        -- Find students whose Fee is greater than ANY student in Class 8

      SELECT * FROM STUDENT                                              -- ANY=SOME: SOME is functionally equivalent to ANY in SQL
      WHERE Fee = SOME (SELECT Fee FROM STUDENT WHERE Class = 8);        -- Find students whose Fee is equal to SOME student in Class 8

      SELECT * FROM STUDENT
      WHERE Fee > ALL (SELECT Fee FROM STUDENT WHERE Class = 8);         -- Find students whose Fee is greater than ALL students in Class 8

      SELECT Stud_Name FROM STUDENT                                      -- Find students whose Fee is greater than ALL students in Class 8
      WHERE EXISTS (SELECT * FROM STUDENT WHERE Class = 10);             -- If an entry EXISTS, it returns the student's name

      SELECT * FROM EXAMS
      WHERE Marks_Obtained IS NULL;                                     -- Find students whose Marks_Obtained IS NULL (if any such values exist)

🔸 Select Records with `NESTED Queries/SUBqueries`

      SELECT * FROM EXAMS
      WHERE Marks_Obtained > (SELECT AVG(Marks_Obtained) FROM EXAMS);        -- Return all records greater than average marks

🔸 Select Records with `Order/Sorting` (ASC or DESC)

      SELECT * FROM STUDENT
      ORDER BY Stud_Name ASC;

      SELECT * FROM STUDENT
      ORDER BY Class DESC;

🔘 ${\color{blue}WILDCARDS}$

🏠 Home

+------------------------------------------------------------------------+
| It is used to search for patterns with LIKE clause within string data. |
+------------------------------------------------------------------------+

🔹 Select Records with `Wildcard %`

      SELECT * FROM STUDENT
      WHERE Guardian_Name LIKE 'M%';        -- Return all records from STUDENT Table where Guardian Name STARTS with letter 'M'

      SELECT * FROM EXAMS
      WHERE Subject_Name LIKE '%e';         -- Return all records from EXAMS Table where Subject Name ENDS with letter 'e'

      SELECT * FROM EXAMS
      WHERE Subject_Name LIKE 'M%s';        -- Return all records from EXAMS Table where Subject name STARTS with letter 'M' and ENDS with letter 's'

      SELECT * FROM EXAMS
      WHERE Addres LIKE '%ur%';             -- Return all records from EXAMS Table where Addres CONTAINS phrase 'ur'

🔹 Select Records with `Wildcard _`

      SELECT * FROM STUDENT
      WHERE Addres LIKE '_hilmil';           -- Return all records from STUDENT Table where Addres STARTS with ANY ONE character, FOLLOWED "hilmil"

      SELECT * FROM EXAMS
      WHERE Subject_Name LIKE 'Englis_';     -- Return all records from EXAMS Table where subject name STARTS with "Englis", ENDS with ANY ONE character

      SELECT * FROM EXAMS                    -- Return all records from EXAMS Table where subject name STARTS with ANY 2 characters....
      WHERE Subject_Name LIKE '__gl___';     -- FOLLOWED by "gl" and ENDS with ANY 3 characters

      SELECT * FROM EXAMS                    -- Return all records from EXAMS Table where subject code starts with "S"....
      WHERE Subject_Code LIKE 'S__002';      -- followed by any 2 characters and ends with '002'

      SELECT * FROM STUDENT
      WHERE Addres LIKE '_a%';               -- Return all records from STUDENT Table where addres starts with any one character, 'a' at 2nd position

🔹 Select Records with `Wildcard []`

      SELECT * FROM STUDENT
      WHERE Addres LIKE '[a-g]%';            -- Return all records from STUDENT Table where Addres starts with any one letter "from 'a' to 'g'" (a,b,c,d,e,f OR g)

      SELECT * FROM STUDENT
      WHERE Stud_Name LIKE 'A[nr]%'          -- Matches names starting with "An" or "Ar"

      SELECT * FROM STUDENT
      WHERE Guardian_Name LIKE '[rhs]%';    -- Return all records from STUDENT Table where Guardian Name STARTS with letter 'r' or 'h' or 's'

      SELECT * FROM STUDENT
      WHERE Guardian_Name LIKE '[^rhs]%';    -- Return all records from STUDENT Table where Guardian Name NOT STARTS with letter 'r' or 'h' or 's'

🔘 ${\color{blue}CLAUSES}$

🏠 Home

+------------------------------------------------------------------------+
| It is used to specify conditions or actions to be applied to the data. |
| Clauses help to filter, group, sort, or limit the results of a query.  |
+------------------------------------------------------------------------+

🔹 Select Records using `WHERE`

WHERE clause: Filter rows based on a condition before grouping.

       SELECT * FROM STUDENT
       WHERE Class = 8;                                      -- Retrieve all students in Class 8

🔹 Select Records using `ORDER BY`

ORDER BY clause: Sort the result set.

       SELECT * FROM STUDENT
       ORDER BY Stud_Name ASC;                               -- Retrieve all students sorted by Stud_Name in ascending order

       SELECT * FROM STUDENT
       WHERE Gender = 'M'
       ORDER BY Stud_Name ASC;                               -- Retrieve all male students sorted by Stud_Name in ascending order

🔹 Select Records using `GROUP BY`

GROUP BY clause: Group rows based on one or more columns.

       SELECT Class, SUM(Fee) AS [Total Fee by Class]
       FROM STUDENT
       GROUP BY Class;                                       -- Sum the fee of students in each class

       SELECT Class, SUM(Fee) AS [Total Fee by Class]
       FROM STUDENT
       WHERE Fee > 300                                       -- Filters rows where Fee is greater than 300
       GROUP BY Class
       ORDER BY Class DESC;                                  -- Sum the fee of students in each class whose fee more than 300 sorted by Class in Descending order

🔹 Select Records using `HAVING`

HAVING clause: Filter groups after Grouping to filter results on Aggregation.

       SELECT Class, COUNT(*) AS NumberOfStudents
       FROM STUDENT
       GROUP BY Class
       HAVING COUNT(*) > 2;                                  -- Find classes with more than 2 students

       SELECT Class, SUM(Fee) AS [Total Fee by Class]
       FROM STUDENT
       GROUP BY Class
       HAVING SUM(Fee) > 500                                 -- Filters groups where the total Fee is greater than 500
       ORDER BY Class DESC;

🔹 Select Records using `TOP/OFFSET/FETCH`

TOP clause: Limit the number of rows returned.

       SELECT TOP 5 * FROM STUDENT
       ORDER BY Fee DESC;                                    -- Retrieve the top 5 students by Fee in descending order

       SELECT TOP 5 Adm_No, Stud_Name FROM STUDENT
       ORDER BY Fee DESC;                                    -- Retrieve the top 5 students table Adm_No and Stud_Name columns by Fee in descending order

       SELECT Adm_No, Marks_Obtained FROM EXAMS
       ORDER BY Marks_Obtained DESC
       OFFSET 3 ROWS                                         -- Skip (Offset) 1st 3 rows and then return next 5 rows
       FETCH NEXT 5 ROWS ONLY;                               -- Retrieve the top 5 EXAMS table Adm_No and Marks_Obtained columns by Marks_Obtained in descending order

🔹 Select Records using `DISTINCT`

DISTINCT clause: Select unique values only.

       SELECT DISTINCT Class FROM STUDENT;                   -- Retrieve the distinct/unique classes of students

       SELECT DISTINCT Class FROM STUDENT
       WHERE Fee > 300;                                      -- Retrieve the distinct classes/unique of students where student fee more than 300

🔹 Select Records using `JOIN`

JOIN clause: Combines rows from two or more tables.

       SELECT S.*, E.Subject_Name, E.Marks_Obtained          -- Fetch all columns (*) from Student Table and Subject & Marks obtained from Exams Table
       FROM STUDENT S
       INNER JOIN EXAMS E
       ON S.Adm_No = E.Adm_No;

🔘 ${\color{blue}JOIN_S}$

🏠 Home

+---------------------------------------------------------------------------------------------+
| It is used to combine rows from two or more tables, based on a related column between them. |
+---------------------------------------------------------------------------------------------+

🔸 Fetch Student Records using `(INNER) JOIN`

(INNER) JOIN: Returns records that have only MATCHING (Common) VALUES IN BOTH TABLES.

      SELECT S.Adm_No, S.Stud_Name, E.Subject_Name, E.Marks_Obtained
      FROM STUDENT S
      INNER JOIN EXAMS E
      ON S.Adm_No = E.Adm_No;                             -- Returns the Admission No., student names along with the subjects and marks they obtained in the exams

      SELECT S.Adm_No, S.Stud_Name, E.Subject_Name, E.Marks_Obtained
      FROM STUDENT S
      JOIN EXAMS E                                        -- JOIN or Inner JOIN works similarly
      ON S.Adm_No = E.Adm_No
      WHERE S.Class = 10;                                 -- Returns the students of class 10 and their respective exam marks

      SELECT S.Adm_No, S.Stud_Name, E.Subject_Name, E.Marks_Obtained
      FROM STUDENT S
      INNER JOIN EXAMS E
      ON S.Adm_No = E.Adm_No
      WHERE E.Marks_Obtained > 85;                        -- Fetches students who scored more than 85 marks in any exam

🔸 Fetch Student Records using `LEFT (OUTER) JOIN`

LEFT (OUTER) JOIN: Returns all records from the LEFT TABLE, and the matched records from the RIGHT TABLE.

      SELECT S.Adm_No, S.Stud_Name, E.Subject_Name, E.Marks_Obtained
      FROM STUDENT S
      LEFT JOIN EXAMS E
      ON S.Adm_No = E.Adm_No;           -- Fetches all students, including those who may not have appeared in any exams

      SELECT S.Adm_No, S.Stud_Name, E.Subject_Name, E.Marks_Obtained
      FROM STUDENT S
      LEFT OUTER JOIN EXAMS E           -- LEFT OUTER JOIN acts as similar to LEFT JOIN
      ON S.Adm_No = E.Adm_No;

🔸 Fetch Student Records using `RIGHT (OUTER) JOIN`

RIGHT (OUTER) JOIN: Returns all records from the RIGHT TABLE, and the matched records from the LEFT TABLE.

      SELECT S.Adm_No, S.Stud_Name, E.Subject_Name, E.Marks_Obtained
      FROM STUDENT S
      RIGHT JOIN EXAMS E
      ON S.Adm_No = E.Adm_No;           -- Fetches all exam records, even if some students may not exist in the STUDENT table (Null Values for Marks_Obtained)

      SELECT S.Adm_No, S.Stud_Name, E.Subject_Name, E.Marks_Obtained
      FROM STUDENT S
      RIGHT OUTER JOIN EXAMS E
      ON S.Adm_No = E.Adm_No;           -- RIGHT OUTER JOIN acts as similar to RIGHT JOIN

🔸 Fetch Student Records using `FULL (OUTER) JOIN`

FULL (OUTER) JOIN: Returns all records when there is a match in either LEFT or RIGHT TABLE.

      SELECT S.Adm_No, S.Stud_Name, E.Subject_Name, E.Marks_Obtained
      FROM STUDENT S
      FULL OUTER JOIN EXAMS E
      ON S.Adm_No = E.Adm_No;           -- Fetches all exam records, even if some students may not exist in the STUDENT table (Null Values)

      SELECT S.Adm_No, S.Stud_Name, E.Subject_Name, E.Marks_Obtained
      FROM STUDENT S
      FULL JOIN EXAMS E
      ON S.Adm_No = E.Adm_No;           --  FULL JOIN acts as similar to FULL OUTER JOIN

🔸 Fetch Student Records using `CROSS JOIN`

CROSS JOIN: It combines every row of the first table with every row of the second table. Example: Cross join students with their subjects.

      SELECT S.Stud_Name, E.Subject_Name
      FROM STUDENT S
      CROSS JOIN EXAMS E;              -- Pairs every student with every subject, producing a large set of combinations

🔸 Fetch Student Records using `SELF JOIN`

SELF JOIN: A self join is when a table is joined with itself. It is useful for hierarchical or recursive data structures.

				    -- Find Students with the Same Guardian
      SELECT 
	    A.Stud_Name AS Student1, 
	    B.Stud_Name AS Student2, 
    	    A.Guardian_Name
      FROM 
	    STUDENT A
      JOIN 
            STUDENT B 
      ON 
    	    A.Guardian_Name = B.Guardian_Name 
    	    AND A.Stud_Name < B.Stud_Name;

				    -- Find Students and Their Monitor
      SELECT 
	    A.Stud_Name AS Student, 
    	    B.Stud_Name AS Monitor
      FROM 
	    STUDENT A
      JOIN 
            STUDENT B 
      ON 
    	    A.Monitor_ID = B.Adm_No;

				    -- Find Students in the Same Class
      SELECT 
	    A.Stud_Name as Student1, 
    	    B.Stud_Name as Student2,
	    A.Class
      FROM 
	    STUDENT A
      JOIN 
            STUDENT B 
      ON 
    	    A.Class = B.Class
	    AND A.Stud_Name < B.Stud_Name;

				    -- Find Students with the Same Address
      SELECT 
	    A.Stud_Name as Student1, 
    	    B.Stud_Name as Student2,
	    A.Addres
      FROM 
	    STUDENT A
      JOIN 
            STUDENT B 
      ON 
    	    A.Addres = B.Addres
	    AND A.Stud_Name < B.Stud_Name;

🔸 Fetch Student Records using `UNION`

UNION: It Combines the results of two or more SELECT statements. The UNION operator only returns DISTINCT records from one or more tables. It is useful when both the select statement have same number of columns and same kind of data types, either from one table or from two tables

      SELECT Adm_No FROM STUDENT
      UNION
      SELECT Monitor_ID FROM STUDENT;   	-- Return the DISTINCT values combination from 'Adm_No' and 'Monitor_ID' from same table

      SELECT Adm_No FROM STUDENT
      UNION
      SELECT Adm_No FROM EXAMS;   		-- Return the DISTINCT values combination from 'Adm_No' from two tables

🔸 Fetch Student Records using `UNION ALL`

UNION ALL: It Combines the results of two or more SELECT statements. The UNION ALL operator returns Selected values including DUPLICATES from one or more tables. It is useful when both the select statement have same number of columns and same kind of data types, either from one table or from two tables

      SELECT Adm_No FROM STUDENT
      UNION ALL
      SELECT Monitor_ID FROM STUDENT;   	-- Return ALL values including DUPLICATES combination from 'Adm_No' and 'Monitor_ID' from same table

      SELECT Adm_No FROM STUDENT
      UNION ALL
      SELECT Adm_No FROM EXAMS;   		-- Return ALL values including DUPLICATES combination from 'Adm_No' from two tables

🔸 Fetch Student Records using `INTERSECT`

INTERSECT: It Combines the results of two or more SELECT statements. The INTERSECT operator returns only COMMON values from one or more tables. It is useful when both the select statement have same number of columns and same kind of data types, either from one table or from two tables

      SELECT Adm_No FROM STUDENT
      INTERSECT
      SELECT Monitor_ID FROM STUDENT;   	-- Return COMMON values from 'Adm_No' and 'Monitor_ID' from same table

      SELECT Adm_No FROM STUDENT
      INTERSECT
      SELECT Adm_No FROM EXAMS;   		-- Return COMMON values from 'Adm_No' from two tables

📗 DCL (`Data Control Language`)

🔘 ${\color{blue}GRANT}$

🏠 Home

+----------------------------------------------------------+
| It is used to give a user access rights to the database. |
+----------------------------------------------------------+

🔹 Grant `SELECT` Permission on a Table

      GRANT SELECT ON STUDENT TO UserA;                      -- Gives the user 'UserA' the ability to perform SELECT queries on the STUDENT table

🔹 Grant `INSERT` and `UPDATE` Permissions on a Table

      GRANT INSERT, UPDATE ON STUDENT TO UserA;              -- Allows UserA to insert new records and update existing ones in the STUDENT table

🔘 ${\color{blue}REVOKE}$

🏠 Home

+----------------------------------------------------------------------------------+
| It is used to remove access rights from the database which is granted to a user. |
+----------------------------------------------------------------------------------+

🔸 Revoke `SELECT` Permission on a Table

      REVOKE SELECT ON STUDENT TO UserA;                      -- Removes the SELECT permission, so UserA can no longer query data from the STUDENT table

🔸 Revoke `INSERT` and `UPDATE` Permissions on a Table

      REVOKE INSERT, UPDATE ON STUDENT TO UserA;              -- Removes the ability for UserA to insert new records or update existing ones in the STUDENT table

📗 TCL (`Transaction Control Language`)

🔘 ${\color{blue}COMMIT}$

🏠 Home

+--------------------------------------------------------------------------------------+
| It is used to save the current transaction permanently in the database.              |
| Once a COMMIT is executed, the changes are made permanent and cannot be rolled back. |
+--------------------------------------------------------------------------------------+

🔹 Insert a Record and `Commit` the Transaction

      BEGIN TRANSACTION;

      INSERT INTO STUDENT (Adm_No, DOJ, Stud_Name, Gender, Guardian_Name, Address, Contact_Number, Class, Fee)
      VALUES ('ROSE00332', '2023-09-15', 'John Doe', 'M', 'Richard Doe', 'Greenfield', '1234567890', 8, 350);

      COMMIT;

🔘 ${\color{blue}ROLLBACK}$

🏠 Home

+-------------------------------------------------------------+
| It is used to Undo changes made in the current transaction. |
+-------------------------------------------------------------+

🔸 Insert a Record, but `Rollback` the Transaction

      BEGIN TRANSACTION;

      INSERT INTO STUDENT (Adm_No, DOJ, Stud_Name, Gender, Guardian_Name, Address, Contact_Number, Class, Fee)
      VALUES ('ROSE00333', '2023-09-15', 'Jane Smith', 'F', 'John Smith', 'Blueville', '9876543210', 7, 300);

      ROLLBACK;                                                                      -- If something goes wrong, rollback the transaction

🔘 ${\color{blue}SAVEPOINT}$

🏠 Home

+----------------------------------------------------------------------------------+
| It is used to set a point within a transaction to which you can roll back later. |
+----------------------------------------------------------------------------------+

🔹 Using `SAVEPOINT` in a Transaction

      BEGIN TRANSACTION;

      INSERT INTO STUDENT (Adm_No, DOJ, Stud_Name, Gender, Guardian_Name, Address, Contact_Number, Class, Fee)
      VALUES ('ROSE00334', '2023-09-15', 'Alice Brown', 'F', 'Sam Brown', 'Redtown', '1122334455', 6, 250);

      SAVEPOINT Save1;

      INSERT INTO STUDENT (Adm_No, DOJ, Stud_Name, Gender, Guardian_Name, Address, Contact_Number, Class, Fee)
      VALUES ('ROSE00335', '2023-09-15', 'Bob White', 'M', 'Jim White', 'Greenville', '5566778899', 5, 275);

      ROLLBACK TRANSACTION Save1;                                                  -- Something goes wrong, rollback to the savepoint

      COMMIT;                                                                      -- Now commit the first insert, but not the second

🔘 ${\color{blue}SET\ TRANSACTION}$

🏠 Home

+------------------------------------------------------------------------------------+
| It is used to specify characteristics for the transaction (e.g., isolation level). |
+------------------------------------------------------------------------------------+

🔸 Using `SET TRANSACTION ISOLATION LEVEL`

      SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

      BEGIN TRANSACTION;
                                                                                           -- Query or Insert/Update/Delete operations                                                      

      INSERT INTO STUDENT (Adm_No, DOJ, Stud_Name, Gender, Guardian_Name, Address, Contact_Number, Class, Fee)
      VALUES ('ROSE00336', '2023-09-15', 'Charlie Green', 'M', 'Paul Green', 'Bluefield', '9988776655', 9, 400);

      COMMIT;

📗 SFL (`Scalar Functions Library`)

🔘 ${\color{blue}STRING\ FUNCTIONS}$

🏠 Home

+------------------------------------------------------------------------------------------------+
| It is used to manipulate string data for tasks like concatenation, extraction, and formatting. |
+------------------------------------------------------------------------------------------------+

🔹 Concatenate `Adm_No` and `Stud_Name`

      SELECT CONCAT(Adm_No, ' - ', Stud_Name) AS StudentDetails
      FROM STUDENT;

🔹 Concatenate `Stud_Name` and `Guardian_Name`

      SELECT CONCAT('Name: ', Stud_Name, ', Guardian: ', Guardian_Name) AS [Full Details]
      FROM STUDENT;

🔸 Extract `first 3 characters` of Stud_Name

      SELECT SUBSTRING(Stud_Name, 1, 3) AS ShortName
      FROM STUDENT;

🔸 Extract `4 characters` from Adm_No starting at `position 5`

      SELECT SUBSTRING(Adm_No, 5, 4) AS PartAdmNo
      FROM STUDENT;

🔹 Find the length of `Stud_Name` (LEN)

      SELECT Stud_Name, LEN(Stud_Name) AS NameLength
      FROM STUDENT;

🔹 Find the length of `Address` (LEN)

      SELECT Address, LEN(Address) AS AddressLength
      FROM STUDENT;

🔹 Find the position of `Md` in Stud_Name (CHARINDEX)

      SELECT CHARINDEX('Md', Stud_Name) AS Md_Position
      FROM STUDENT;

🔹 Find the `starting position` of pattern `R` in Stud_Name (PATINDEX)

      SELECT PATINDEX('%R%', Stud_Name) AS R_Position
      FROM STUDENT;

🔸 Convert Stud_Name to `UPPERCASE`

      SELECT UPPER(Stud_Name) AS UpperCaseName
      FROM STUDENT;

🔸 Convert Address to `lowercase`

      SELECT LOWER(Address) AS LowerCaseAddress
      FROM STUDENT;

🔹 Remove `spaces` from Contact_Number

      SELECT TRIM(Contact_Number) AS TrimmedContact
      FROM STUDENT;

🔹 Remove `leading spaces` from Stud_Name

      SELECT LTRIM(Stud_Name) AS Trimmed_Left_Name
      FROM STUDENT;

🔹 Remove `trailing spaces` from Stud_Name

      SELECT RTRIM(Stud_Name) AS Trimmed_Right_Name
      FROM STUDENT;

🔸 Replace 'Md' with 'Mr.' in Stud_Name

      SELECT REPLACE(Stud_Name, 'Md', 'Mr.') AS Updated_Name
      FROM STUDENT;

🔸 Replace hyphen `-` with `space` in Adm_No

      SELECT REPLACE(Adm_No, '-', ' ') AS ModifiedAdmNo
      FROM STUDENT;

🔸 Insert `Dr.` at the beginning of Stud_Name

      SELECT STUFF(Stud_Name, 1, 0, 'Dr. ') AS Stuffed_Name
      FROM STUDENT;

🔹 Extract `first 5` characters of Adm_No

      SELECT LEFT(Adm_No, 5) AS FirstFive
      FROM STUDENT;

🔹 Extract `last 4` characters of Contact_Number

      SELECT RIGHT(Contact_Number, 4) AS LastFourDigits
      FROM STUDENT;

🔸 Reverse `Adm_No`

      SELECT REVERSE(Adm_No) AS ReversedAdmNo               -- reverse the order of characters in a string
      FROM STUDENT;

🔸 Reverse `Stud_Name`

      SELECT REVERSE(Stud_Name) AS ReversedName             -- reverse the order of characters in a string
      FROM STUDENT;

🔹 Repeat '*' symbol 5 times

      SELECT REPLICATE('*', 5) AS Stars                      -- Return star (*) 5 times
      FROM STUDENT;

🔹 Repeat `Stud_Name` twice

      SELECT REPLICATE(Stud_Name, 2) AS DoubledName          -- Return the same Student Name 2 times
      FROM STUDENT;

🔹 Repeat String like a `Loop`

      PRINT 'This is a loop';
      GO 5                           -- 'GO' is not an actual SQL command, It is a part of SSMS which indicates end of Batch (group of commands) in SSMS
/*
OUTPUT:
=======
Beginning execution loop
This is a loop
This is a loop
This is a loop
This is a loop
This is a loop
Batch execution completed 4 times.
*/

🔸 Format Contact_Number with `dashes`

      SELECT FORMAT(Contact_Number, '###-###-####') AS FormattedContact   -- format a value according to a format
      FROM STUDENT;

🔸 Format DOJ in `MM/dd/yyyy` format

      SELECT FORMAT(DOJ, 'MM/dd/yyyy') AS FormattedDOJ
      FROM STUDENT;

🔘 ${\color{blue}MATHEMATICAL\ FUNCTIONS}$

🏠 Home

+---------------------------------------------------------------------------------------------------------------------+
| It is used to perform basic mathematical operations like Square root, Round, Power, Floor, Ceiling on numeric data. |
+---------------------------------------------------------------------------------------------------------------------+

🔹 `ABS()` : Returns the absolute (`Positive`) value of a number

	     						-- Inside the (), Can be used COLUMN Name of a Table
      SELECT ABS(-10) AS AbsoluteValue;             	-- Returns the absolute value of -10

🔹 `ROUND()` : Rounds a number to a specified number of decimal places

	      						-- Inside the (), Can be used COLUMN Name of a Table
      SELECT ROUND(12.3456, 2) AS RoundedValue;      	-- Rounds 12.3456 to 2 decimal places

🔹 `SQRT()` : Returns the square root of a number

	      						-- Inside the (), Can be used COLUMN Name of a Table
      SELECT SQRT(25) AS SquareRoot;      		-- Returns the square root of 25

🔹 `CEILING()` : Returns the smallest integer greater than or equal to a number

	      						-- Inside the (), Can be used COLUMN Name of a Table
      SELECT CEILING(4.3) AS CeilingValue;      	-- Returns the ceiling of 4.3 (next whole number, round up forwards)

🔹 `FLOOR()` : Returns the largest integer less than or equal to a number

	      						-- Inside the (), Can be used COLUMN Name of a Table
      SELECT FLOOR(4.7) AS FloorValue;      		-- Returns the floor of 4.7 (previous whole number, round up backwards)

🔹 `POWER()` : Raises a number to the power of another number

	      						-- Inside the (), Can be used COLUMN Name of a Table
      SELECT POWER(2, 3) AS PowerValue;      		-- Raises 2 to the power of 3 (2^3)

🔹 `EXP()` : Returns e raised to the power of a specified number

	      						-- Inside the (), Can be used COLUMN Name of a Table
      SELECT EXP(1) AS ExponentialValue;      		-- Returns e^1 (where e is Euler's number, approximately 2.718)

🔹 LOG() : Returns the natural logarithm (base e) of a number

      							-- Inside the (), Can be used COLUMN Name of a Table
      SELECT LOG(10) AS LogarithmValue;      		-- Returns the natural logarithm of 10

🔘 ${\color{blue}DATE\ FUNCTIONS}$

🏠 Home

+-----------------------------------------------------------+
| It is used to handle and manipulate date and time values. |
+-----------------------------------------------------------+

🔹 `GETDATE()` : Returns the current date and time

      SELECT GETDATE() AS CurrentDateTime;

🔹 `YEAR()` : Extracts the year from a date

        SELECT YEAR(DOJ) AS YearOfJoining
	FROM STUDENT;

🔹 `MONTH()` : Extracts the month from a date

        SELECT MONTH(DOJ) AS MonthOfJoining
	FROM STUDENT;

🔹 `DAY()` : Extracts the day from a date

        SELECT DAY(DOJ) AS DayOfJoining
	FROM STUDENT;

🔹 `DATEADD()` : Adds a specified time interval to a date

        SELECT Adm_No, DOJ, DATEADD(DAY, 5, DOJ) AS DOJPlus5Days     -- Adds 5 days to the 'DOJ' (Date of Joining) column
	FROM STUDENT;

🔹 `DATEDIFF()` : Returns the difference between two dates in terms of a specified time interval

        SELECT Adm_No, DOJ, DATEDIFF(DAY, DOJ, GETDATE()) AS DaysSinceJoining     -- Returns the difference in days between 'DOJ' and the current date
	FROM STUDENT;

🔘 ${\color{blue}LOGICAL\ FUNCTIONS}$

🏠 Home

+------------------------------------------+
| It is used to handle the missing values. |
+------------------------------------------+

🔹 `ISNULL()` : Replaces NULL with a specified value `(with one Argument)`

      SELECT Adm_No, ISNULL(Monitor_ID, 'No Monitor') AS MonitorStatus          -- Replaces NULL in Monitor_ID with 'No Monitor'
      FROM STUDENT;

🔹 `COALESCE()` : Returns the first non-null value from a list `(with multiple Arguments)`

      SELECT Adm_No, COALESCE(Guardian_Name, Monitor_ID, 'Not Available') AS FirstNonNullValue  
      FROM STUDENT;                         -- Returns the first non-null value from Guardian_Name, Monitor_ID, or 'Not Available'

🔘 ${\color{blue}CONVERSION\ FUNCTIONS}$

🏠 Home

+-------------------------------------------------+
| It is used to change data types of expressions. |
+-------------------------------------------------+

🔹 `CAST()` : Converts one data type to another

      SELECT CAST('2024-09-24' AS DATE) AS ConvertedDate;                  -- This converts a string to a date type

🔹 `CONVERT()` : Similar to CAST(), but allows formatting options

      SELECT CONVERT(VARCHAR(10), GETDATE(), 101) AS FormattedDate;        -- This converts the current date to a string formatted as MM/DD/YYYY

🔹 `TRY_CAST()` : Attempts conversion; returns `NULL` if it fails

      SELECT TRY_CAST('123.45' AS INT) AS ConvertedValue;            -- This tries to convert a string to an integer. Since it can't be converted, it returns NULL

🔹 `TRY_CONVERT()` : Similar to TRY_CAST(), with formatting options

      SELECT TRY_CONVERT(VARCHAR(10), 'NotANumber') AS SafeConvert;
					-- This attempts to convert a non-numeric string to a varchar. It returns NULL due to the failure in conversion

📗 WFL (`Windows Functions Library`)

🔘 ${\color{blue}AGGREGATE\ FUNCTIONS}$

🏠 Home

+----------------------------------------------------------------------------------+
| It is used to perform a calculation on Set of Values and return a single value. |
| It is commonly used to summarize or analyze data.                                |
+----------------------------------------------------------------------------------+

🔸 Select Records using `COUNT()`

      SELECT COUNT(*) AS TotalStudents
      FROM STUDENT;                          -- Count the total number of records/rows from student table

🔸 Select Records using `SUM()`

      SELECT SUM(Fee) AS TotalFees
      FROM STUDENT;                          -- Calculate the total fees of all students from student table

🔸 Select Records using `AVG()`

      SELECT AVG(Fee) AS AverageFee
      FROM STUDENT;                          -- Calculate the average fee of students from student table

🔸 Select Records using `MIN()`

      SELECT MIN(Fee) AS MinimumFee
      FROM STUDENT;                          -- Find the minimum fee paid by a student from student table

🔸 Select Records using `MAX()`

      SELECT MAX(Fee) AS MinimumFee
      FROM STUDENT;                          -- Find the maximum fee paid by a student from student table

🔸 Select Records using `COUNT()` with GROUP BY and HAVING clauses

      SELECT Class, COUNT(*) AS NumberOfStudents
      FROM STUDENT
      GROUP BY Class
      HAVING COUNT(*) > 2;                   -- Find classes with more than 2 students

🔸 Check DUPLICATE Records in a Table using `COUNT()`

      SELECT Adm_No, COUNT(*) as DuplicateCount
      FROM STUDENT
      GROUP BY Adm_No
      HAVING COUNT(*) >= 2;                   -- Find classes with more than 2 students

🔘 ${\color{blue}RANKING\ FUNCTIONS}$

🏠 Home

+--------------------------------------------------------------------------------------+
| It’s commonly used for ranking results, Assigns a unique sequential integer to rows, |
| such as determining the top or bottom records in a dataset. 			       |                                
+--------------------------------------------------------------------------------------+

🔸 Assign a Rank to rows using `ROW_NUMBER()`

       	SELECT Adm_No, Marks_Obtained,
        ROW_NUMBER() OVER (ORDER BY Marks_Obtained DESC) AS RowNum
	FROM EXAMS;

🔸 Assign a Rank to rows using `DENSE_RANK()` with no gaps even if any `TIES`

       	SELECT Adm_No, Marks_Obtained,
        DENSE_RANK() OVER (ORDER BY Marks_Obtained DESC) AS DenseRank
	FROM EXAMS;

🔸 Assign a Rank to rows using `RANK()` with gaps if any `TIES`

       	SELECT Adm_No, Marks_Obtained,
        RANK() OVER (ORDER BY Marks_Obtained DESC) AS Rank
	FROM EXAMS;

🔸 Divide rows using `NTILE()` into bucket of Grouped Sequence

       	SELECT Adm_No, Marks_Obtained,
        NTILE(4) OVER (ORDER BY Marks_Obtained DESC) AS NTileGroup      -- If total 16 rows, NTILE(4) divides into groups of 4-4
	FROM EXAMS;                                                     -- with rank 1-1 for 1st four, 2-2 for next four, 3-3 for again next four, and 4-4 for last four

🔸 Display the `N-th Rank` marks obtained using `DENSE_RANK()` with `CTE`

CTE: A CTE (Common Table Expression) is a temporary table you create in a SQL query to make it easier to write and understand complex queries.

       	With CTE_RankNo AS (
		             SELECT Adm_No, Marks_Obtained,
			     DENSE_RANK() OVER (ORDER BY Marks_Obtained DESC) AS [Rank Position]
			     FROM EXAMS
		           )
	Select *
	From RankNo
	Where [Rank Position] = 2;                 -- Similarly can display 3rd, 4th,......nth RANK by DESC from TOP & ASC from BOTTOM

🔸 Delete DUPLICATE Records using `ROW_NUMBER()` with `CTE`

       With CTE_RemovDup AS (
			     SELECT *,
        		     ROW_NUMBER() OVER (PARTITION BY Adm_No, DOJ, Stud_Name 
                              			ORDER BY Adm_No) AS DupRecord
			     FROM STUDENT;
		            )
	DELETE FROM CTE_RemovDup
	WHERE DupRecord > 1;

🔘 ${\color{blue}ANALYTIC\ FUNCTIONS}$

🏠 Home

+-------------------------------------------------------------------------------+
| It is used to compute values across a set of rows related to the current row, |
| enabling analysis without grouping the data. 			       		|                                
+-------------------------------------------------------------------------------+

Table: EXAMS1
+----------+--------------+--------------+----------------+------------+
|  Adm_No  | Subject_Code | Subject_Name | Marks_Obtained | Exam_Date  |
+----------+--------------+--------------+----------------+------------+
| ROSE0001 |	SUB001	  |    Math	 |	85	  | 2023-01-10 |
| ROSE0001 |	SUB002	  |   Science	 |	90	  | 2023-01-20 |
| ROSE0001 |	SUB003	  |   English	 |	88	  | 2023-01-30 |
| ROSE0002 |	SUB001	  |    Math	 |	78	  | 2023-01-10 |
| ROSE0002 |	SUB002	  |   Science	 |	82	  | 2023-01-20 |
| ROSE0002 |	SUB003	  |   English	 |	75	  | 2023-01-30 |
| ROSE0003 |	SUB001	  |    Math	 |	92	  | 2023-01-10 |
| ROSE0003 |	SUB002	  |   Science	 |	85	  | 2023-01-20 |
+----------+--------------+--------------+----------------+------------+

🔸 Using `CUME_DIST()` Calculate the cumulative distribution of marks obtained

       	SELECT Adm_No, Subject_Name, Marks_Obtained,
        CUME_DIST() OVER (ORDER BY Marks_Obtained) AS CumulativeDistribution
	FROM EXAMS1;

🔸 Using `PERCENT_RANK()` Calculate the percent rank of each student's marks

       	SELECT Adm_No, Subject_Name, Marks_Obtained,
        PERCENT_RANK() OVER (ORDER BY Marks_Obtained) AS PercentRank
	FROM EXAMS1;

🔸 Using `FIRST_VALUE()` Get the first mark obtained for each student

       	SELECT Adm_No, Subject_Name, Marks_Obtained, Exam_Date,
        FIRST_VALUE(Marks_Obtained) OVER (PARTITION BY Adm_No ORDER BY Exam_Date) AS FirstMark
	FROM EXAMS1;

🔸 Using `LAST_VALUE()` Get the last mark obtained for each student

       	SELECT Adm_No, Subject_Name, Marks_Obtained, Exam_Date,
        LAST_VALUE(Marks_Obtained) OVER (PARTITION BY Adm_No ORDER BY Exam_Date 
        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS LastMark
	FROM EXAMS1;

🔸 Using `LAG()` Get the previous mark obtained by each student

       	SELECT Adm_No, Subject_Name, Marks_Obtained, Exam_Date,
        LAG(Marks_Obtained) OVER (PARTITION BY Adm_No ORDER BY Exam_Date) AS PreviousMark
	FROM EXAMS1;

🔸 Using `LEAD()` Get the next mark obtained by each student

       	SELECT Adm_No, Subject_Name, Marks_Obtained, Exam_Date,
        LEAD(Marks_Obtained) OVER (PARTITION BY Adm_No ORDER BY Exam_Date) AS NextMark
	FROM EXAMS;

Name		Name	Last commit message	Last commit date
Latest commit History 142 Commits
README.md		README.md
SQL_A2Z_Solutions.sql		SQL_A2Z_Solutions.sql

ialam085/A2Z_SQL_Solutions

Folders and files

Latest commit

History

Repository files navigation

🔳 A ${\color{Red}2}$ Z SQL Solutions ${\color{Green}(using\ SQL\ SERVER)}$

©️ ${\color{grey}Developed\ and\ Maintained\ by:}$ ${\color{blue}Md\ Imtiyaz\ Alam}$

👇 ${\color{red}NAVIGATION}$

◻️ Objective

◻️ Tech Stack

◻️ Steps includes

◻️ Categories of applied SQL Commands

◻️ Detailed view of all SQL-Server Commands with QUERIES and SCRIPTS

📗 DDL (Data Definition Language)

🔘 ${\color{blue}USE}$ (SCL)

🔸 Using an Existing DATABASE named 'FSA'

🔘 ${\color{blue}SCHEMA}$

🔹 Create a new SCHEMA named 'INSTITUTE'

🔹 Create a Table within Schema INSTITUTE

🔹 Transfer Table Student into Schema INSTITUTE

🔘 ${\color{blue}CREATE}$

🔸 Create a new DATABASE named 'FSA'

🔸 Create a new TABLE named 'STUDENT'

🔸 Create the Table Student with CONSTRAINTS (inline)

🔸 Create a VIEW named Class10_Students

🔸 Create an INDEX idx_StudName

🔸 Create Tables under NORMALIZATION

🔘 ${\color{blue}ALTER}$

🔹 Alter a DATABASE FSA to 'FSA_new'

🔹 Rename a Table STUDENTS to 'STUDENT'

🔹 Rename a Table Column Contact_No to 'Contact_Number'

🔹 Add a new column 'Email' to table Student

🔹 Modify a column (change data type) BIGINT to 'VARCHAR' for Contact_Number column

🔹 Modify a column (change length of data type) VARCHAR(50) to 'VARCHAR(100)' for Stud_Name column

🔹 Drop a column 'Email' from table Student

🔹 Add a default value of 300 to 'Fee' column by adding CONSTRAINT

🔘 ${\color{blue}DROP}$

🔸 Drop the Database 'FSA'

🔸 Drop the Schema 'HR'

🔸 Drop the Table 'Student'

🔸 Drop a column 'Email' from table Student

🔸 Drop the View 'Class10_Students'

🔸 Drop an Index 'idx_StudName'

🔘 ${\color{blue}TRUNCATE}$

🔹 Truncate the Table 'Student'

📗 DML (Data Manipulation Language)

🔘 ${\color{blue}INSERT}$

🔹 Insert Data/Values (single record) into a Table Student

🔹 Insert Data/Values (Multiple records) into a Table Student

🔹 Insert and COPY complete Data/Values from Old Source Table Student_Old into a New Destination Table Student with Same Columns

🔘 ${\color{blue}UPDATE}$

🔸 Update (SET) Single value/row (Class) in a Table 'Student'

🔸 Update (SET) Multiple (same) values/same Column (Gender) in a Table 'Student' if Column has NULL values

🔸 Update (SET) Multiple (different) values/same row (Stud_Name, Fee and DOJ) in a Table 'Student'

🔸 Update (SET) Multiple (same) values/same Column (Gender) in a Table 'Student'

🔸 Update (SET with CASE, ELSE, END) Multiple (different) values/different Column (FEE) in a Table 'Student'

🔸 Update (SET with CASE, ELSE, END) Multiple (different) values/different Column (FEE) in a Table 'Student'

🔘 ${\color{blue}DELETE}$

🔹 Delete single Row/Record from a Table Student by the reference of one Primary-Key Value

🔹 Delete Multiple Rows/Records from a Table Student by the reference of many Primary-Key Values

🔹 Delete Multiple Rows/Records from a Table Student by the reference of one Non-Key Value

🔘 ${\color{blue}STORED\ PROCEDURE}$

🔹 CREATE a Table 'EmpInfo' Outside Stored Procedure

🔹 Stored Procedure to INSERT Records into 'EmpInfo' Table

🔹 Stored Procedure to UPDATE Records into 'EmpInfo' Table

🔹 Stored Procedure to DELETE Records from 'EmpInfo' Table

🔹 Stored Procedure to SELECT Records from 'EmpInfo' Table

🔹 Check Stored Procedure named EmployeeInfo from 'EmpInfo' Table

🔹 Stored Procedure using a TRIGGER

📗 DQL (Data Query Language)

🔘 ${\color{blue}SELEC T}$

🔸 Select Current Date and Time

🔸 Select All Databases of SQL-Server

🔸 Copy a Table from Old Database to New Database

🔸 Select All Tables from a current session Database

🔸 Select All Columns from a Table 'Student'

🔸 Select All Columns from a Table 'Exams'

🔸 Select Table 'Exams' Columns from Schema 'Institute'

🔸 Select Table 'Exams' Columns from Schema 'Institute' in Database 'FSA'

🔸 Select Specific Columns from a Table 'Student'

🔳 `A` ${\color{Red}2}$ `Z` SQL Solutions ${\color{Green}(using\ SQL\ SERVER)}$

◻️ Detailed view of all SQL-Server Commands with `QUERIES` and `SCRIPTS`

📗 DDL (`Data Definition Language`)

🔸 Using an Existing `DATABASE` named 'FSA'

🔹 Create a new `SCHEMA` named 'INSTITUTE'

🔹 Create a Table `within Schema` INSTITUTE

🔹 Transfer Table `Student` into Schema `INSTITUTE`

🔸 Create a new `DATABASE` named 'FSA'

🔸 Create a new `TABLE` named 'STUDENT'

🔸 Create the Table Student with `CONSTRAINTS` (inline)

🔸 Create a `VIEW` named `Class10_Students`

🔸 Create an `INDEX` `idx_StudName`

🔸 Create Tables under `NORMALIZATION`

🔹 Alter a `DATABASE` FSA to 'FSA_new'

🔹 Rename a `Table` STUDENTS to 'STUDENT'

🔹 Rename a Table `Column` Contact_No to 'Contact_Number'

🔹 Add a new `column` 'Email' to table Student

🔹 Modify a `column (change data type)` BIGINT to 'VARCHAR' for `Contact_Number` column

🔹 Modify a `column (change length of data type)` VARCHAR(50) to 'VARCHAR(100)' for `Stud_Name` column

🔹 Add a default value of `300` to 'Fee' column by adding CONSTRAINT

🔸 Drop the `Database` 'FSA'

🔸 Drop the `Schema` 'HR'

🔸 Drop the `Table` 'Student'

🔸 Drop the `View` 'Class10_Students'

🔸 Drop an `Index` 'idx_StudName'

🔹 Truncate the `Table` 'Student'

📗 DML (`Data Manipulation Language`)

🔹 Insert Data/Values (single record) into a Table `Student`

🔹 Insert Data/Values (Multiple records) into a Table `Student`

🔹 Insert and COPY complete Data/Values from Old Source Table `Student_Old` into a New Destination Table `Student` with `Same Columns`

🔸 Update (SET) `Single value/row` (Class) in a Table 'Student'

🔸 Update (SET) `Multiple (same) values/same Column` (Gender) in a Table 'Student' if Column has NULL values

🔸 Update (SET) `Multiple (different) values/same row` (Stud_Name, Fee and DOJ) in a Table 'Student'

🔸 Update (SET) `Multiple (same) values/same Column` (Gender) in a Table 'Student'

🔸 Update (SET with CASE, ELSE, END) `Multiple (different) values/different Column` (FEE) in a Table 'Student'

🔸 Update (SET with CASE, ELSE, END) `Multiple (different) values/different Column` (FEE) in a Table 'Student'

🔹 Delete single Row/Record from a Table `Student` by the reference of one `Primary-Key` Value

🔹 Delete Multiple Rows/Records from a Table `Student` by the reference of many `Primary-Key` Values

🔹 Delete Multiple Rows/Records from a Table `Student` by the reference of one `Non-Key` Value

🔹 CREATE a Table 'EmpInfo' `Outside Stored Procedure`

🔹 `Stored Procedure` to `INSERT` Records into 'EmpInfo' Table

🔹 `Stored Procedure` to `UPDATE` Records into 'EmpInfo' Table

🔹 `Stored Procedure` to `DELETE` Records from 'EmpInfo' Table

🔹 `Stored Procedure` to `SELECT` Records from 'EmpInfo' Table

🔹 Check Stored Procedure named `EmployeeInfo` from 'EmpInfo' Table

🔹 `Stored Procedure` using a `TRIGGER`

📗 DQL (`Data Query Language`)

🔸 Select Current `Date and Time`

🔸 Select `All Databases` of SQL-Server

🔸 Select `All Tables` from a current session Database

🔸 Select `All Columns` from a Table 'Student'

🔸 Select `All Columns` from a Table 'Exams'

🔸 Select `Table` 'Exams' Columns from `Schema` 'Institute'

🔸 Select `Table` 'Exams' Columns from `Schema` 'Institute' in `Database` 'FSA'

🔸 Select `Specific Columns` from a Table 'Student'

🔸 Select Records with a `Condition` (`Comparison Operators`: =, >, <, >=, <=, !=, <>, !<, !>)

🔸 Select Records with Conditions by `Logical Operators`: AND, OR, NOT, IN, BETWEEN, LIKE, ANY/SOME, ALL, IS NULL

🔸 Select Records with `NESTED Queries/SUBqueries`

🔸 Select Records with `Order/Sorting` (ASC or DESC)

🔹 Select Records with `Wildcard %`

🔹 Select Records with `Wildcard _`

🔹 Select Records with `Wildcard []`

🔹 Select Records using `WHERE`

🔹 Select Records using `ORDER BY`

🔹 Select Records using `GROUP BY`

🔹 Select Records using `HAVING`

🔹 Select Records using `TOP/OFFSET/FETCH`

🔹 Select Records using `DISTINCT`

🔹 Select Records using `JOIN`

🔸 Fetch Student Records using `(INNER) JOIN`

🔸 Fetch Student Records using `LEFT (OUTER) JOIN`

🔸 Fetch Student Records using `RIGHT (OUTER) JOIN`

🔸 Fetch Student Records using `FULL (OUTER) JOIN`

🔸 Fetch Student Records using `CROSS JOIN`

🔸 Fetch Student Records using `SELF JOIN`

🔸 Fetch Student Records using `UNION`

🔸 Fetch Student Records using `UNION ALL`

🔸 Fetch Student Records using `INTERSECT`

📗 DCL (`Data Control Language`)

🔹 Grant `SELECT` Permission on a Table