SQL10feb.sql

--- EMPLOYEE & PROJECT TABLES

--- EMPLOYEE TABLE
CREATE TABLE Employee
(
    EID INT PRIMARY KEY,
    Ename VARCHAR(50),
    Department VARCHAR(50),
    Age INT,
    Salary INT,
    MID INT,      --- Manager ID (self join reference)
    DID INT       --- Department ID (department table reference)
);

--- INSERT EMPLOYEE DATA
INSERT INTO Employee VALUES
(101, 'Suresh', 'IT', 28, 50000, 10, 1),
(102, 'Ramesh', 'HR', 35, 60000, 11, 2),
(103, 'Anita', 'IT', 30, 55000, 10, 1),
(104, 'Pooja', 'Finance', 26, 45000, 12, 3),
(105, 'Rahul', 'HR', 40, 70000, 11, 2),
(106, 'Neha', 'Sales', 32, 48000, 13, 4),
(107, 'Amit', 'IT', 45, 80000, 10, 1);

--- VIEW BASE TABLE DATA
SELECT * FROM Employee;

--- PROJECT TABLE

CREATE TABLE Project
(
    PID INT PRIMARY KEY,
    Pname VARCHAR(50),
    EID INT
);

--- INSERT PROJECT DATA
INSERT INTO Project VALUES
(1, 'Data Migration', 101),
(2, 'HR Automation', 102),
(3, 'Cloud Setup', 103),
(4, 'Finance Audit', 104),
(5, 'Sales Dashboard', 106),
(6, 'AI Reporting', 107);

--- VIEWS (VIRTUAL TABLES)

--- SIMPLE VIEW : EMPLOYEE DETAILS
CREATE VIEW vw_EmployeeDetails
AS
SELECT EID, Ename, Department
FROM Employee;

--- FETCH DATA FROM VIEW
SELECT * FROM vw_EmployeeDetails;

--- DROP VIEW
DROP VIEW vw_EmployeeDetails;
drop view vw_employeeprojectdetails;

--- RECREATE / ALTER VIEW
CREATE OR ALTER VIEW vw_EmployeeDetails
AS
SELECT EID, Ename, Department
FROM Employee;

--- VIEW WITH JOIN (EMPLOYEE + PROJECT)

CREATE VIEW vw_EmployeeProjectDetails
AS
SELECT E.EID, E.Ename, E.Department, P.Pname
FROM Employee E
JOIN Project P
ON E.EID = P.EID;

--- FETCH DATA FROM JOIN VIEW
SELECT * FROM vw_EmployeeProjectDetails;

--- ADVANTAGE OF VIEW
--- NO NEED TO WRITE LENGTHY JOIN QUERIES
--- BASE TABLES ARE NOT DIRECTLY EXPOSED
--- USED FOR SECURITY AND SIMPLICITY
--- PERFORMANCE WISE VIEWS ARE NOT FASTER THAN JOINS
--- VIEW IS JUST A STORED QUERY
--- ONLY INDEXED VIEWS CAN IMPROVE PERFORMANCE


--- VIEW UPDATE (ALLOWED – SINGLE BASE TABLE VIEW)

UPDATE vw_EmployeeDetails
SET Department = 'AI'
WHERE EID = 101;

--- VERIFY UPDATE
SELECT * FROM Employee;
SELECT * FROM vw_EmployeeDetails;

--- INVALID UPDATE EXAMPLE (JOIN VIEW)

--- THIS UPDATE IS NOT ALLOWED
--- BECAUSE JOIN VIEW USES MULTIPLE BASE TABLES
--- SQL SERVER WILL THROW ERROR

UPDATE vw_EmployeeProjectDetails
SET Pname = 'Cloud Engineering'
WHERE EID = 101;

--- MODIFICATION AFFECTS MULTIPLE BASE TABLES
--- SO UPDATE IS NOT PERMITTED

--- HOW TO VIEW VIEW STRUCTURE

--- USE THIS TO SEE VIEW DEFINITION / SYNTAX
sp_helptext 'vw_EmployeeDetails';

--- USE THIS TO SEE VIEW METADATA (COLUMNS, DATATYPES)
sp_help 'vw_EmployeeDetails';

---====================================================
--- FETCH EMPLOYEE NAME, MANAGER NAME, DEPARTMENT NAME, PROJECT NAME
---====================================================
--- (ASSUMPTION: MANAGER IS SELF JOIN USING MID)

CREATE VIEW vw_Details
AS
SELECT 
    E.Ename        AS EmployeeName,
    M.Ename        AS ManagerName,
    E.Department  AS DepartmentName,
    P.Pname       AS ProjectName
FROM Employee E
LEFT JOIN Employee M ON E.MID = M.EID
LEFT JOIN Project P ON E.EID = P.EID;


---====================================================
--- CREATE A VIEW ON A SINGLE TABLE AND UPDATE DATA USING VIEW
---====================================================

SELECT * FROM Employee;
SELECT * FROM vw_EmployeeDetails;

--- UPDATE USING SINGLE BASE TABLE VIEW (ALLOWED)
UPDATE vw_EmployeeDetails
SET Department = 'AI'
WHERE EID = 101;

--- VERIFY UPDATE IN BASE TABLE
SELECT * FROM Employee;


--- EMPLOYEE & DEPARTMENT TABLES (FOR JOIN VIEW)

CREATE TABLE Employeee
(
    EID INT,
    Ename VARCHAR(50),
    DID INT
);

--- INSERT EMPLOYEE DATA
INSERT INTO Employeee VALUES
(1, 'Suresh', 10),
(2, 'Jayesh', 10),
(3, 'Mahesh', 30);


CREATE TABLE Department
(
    DID INT,
    Dname VARCHAR(50)
);

--- INSERT DEPARTMENT DATA
INSERT INTO Department VALUES
(10, 'IT'),
(20, 'Cloud'),
(30, 'AI');


---====================================================
--- VIEW WITH JOIN (EMPLOYEE + DEPARTMENT)
---====================================================

CREATE VIEW vw_EmpDepartmentDetails
AS
SELECT 
    E.EID,
    E.Ename,
    D.Dname
FROM Employeee E
JOIN Department D
ON E.DID = D.DID;


--- FETCH DATA FROM VIEW
SELECT * FROM vw_EmpDepartmentDetails;

update vw_empdepartmentdetails 
set dname='data'
where eid=1 

select* from employee 
select * from vw

---====================================================
--- CREATE TABLE
---====================================================

CREATE TABLE test
(
    tid INT,
    tname VARCHAR(50),
    city VARCHAR(50)
);

--- INSERT DATA
INSERT INTO test VALUES
(1, 'QA', 'Vadodara'),
(2, 'Cloud', 'Anand'),
(3, 'AI', 'Ahmedabad');


---====================================================
--- CREATE SIMPLE VIEW
---====================================================

CREATE VIEW vw_testdetails
AS
SELECT tid, tname
FROM test;

--- FETCH DATA
SELECT * FROM vw_testdetails
select * from test 

---====================================================
--- ALTER TABLE (WITHOUT SCHEMABINDING)
---====================================================
--- THIS IS ALLOWED BECAUSE VIEW IS NOT SCHEMABOUND

ALTER TABLE test
DROP COLUMN city;



ALTER TABLE test
DROP COLUMN tid;

SELECT * FROM vw_testdetails

---====================================================
--- DROP VIEW
---====================================================

DROP VIEW vw_testdetails;


---====================================================
--- CREATE VIEW WITH SCHEMABINDING
---====================================================
--- RULES:
--- 1. MUST USE dbo.TABLE_NAME
--- 2. CANNOT USE SELECT *
--- 3. BASE TABLE STRUCTURE CANNOT BE CHANGED

CREATE VIEW vw_testdetails
WITH SCHEMABINDING
AS
SELECT tid, tname
FROM dbo.test;

Alter VIEW vw_testdetails
WITH ENCRYPTION
AS
SELECT tname
FROM dbo.test;


Alter VIEW vw_testdetails
WITH SCHEMABINDING
AS
SELECT tname
FROM dbo.test;
----advantage of schmabinding 


---====================================================
--- TRY ALTER TABLE (NOT ALLOWED)
---====================================================
--- THIS WILL FAIL BECAUSE VIEW IS SCHEMABOUND

ALTER TABLE test
DROP COLUMN tid;

drop table test
---====================================================
--- SOLUTION
---====================================================
--- FIRST DROP VIEW, THEN ALTER TABLE

DROP VIEW vw_testdetails;

ALTER TABLE test
DROP COLUMN tid;


---====================================================
--- DROP TABLE
---====================================================

DROP TABLE test;

select name
from sys.tables 


select name,create_date

---it will give table which is their in my database


---====================================================
--- INDEX (CLUSTERED & NON-CLUSTERED)
---====================================================

--- INDEX DOES NOT SCAN ENTIRE TABLE (LIKE AN ENTIRE BOOK)
--- IT WORKS LIKE AN INDEX PAGE
--- SAVES TIME
--- FASTER DATA RETRIEVAL AND PROCESSING
--- ONE CLUSTERED INDEX PER TABLE
--- MULTIPLE NON-CLUSTERED INDEXES PER TABLE
--- CLUSTERED INDEX DEFINES PHYSICAL ORDER OF DATA


---====================================================
--- BASIC INDEX SYNTAX
---====================================================

--- CREATE INDEX index_name
--- ON table_name (column_name)

SELECT * FROM Employee;


---====================================================
--- NON-CLUSTERED INDEX
---====================================================

CREATE NONCLUSTERED INDEX IX_EmpSalary
ON Employee (Salary ASC);

--- QUERY USING INDEX
SELECT Salary FROM Employee;

--- DROP INDEX
DROP INDEX Employee.IX_EmpSalary;


---====================================================
--- COMPOSITE INDEX (MULTIPLE COLUMNS)
---====================================================

CREATE NONCLUSTERED INDEX IX_EmpSalaryWithDepartment
ON Employee (Department DESC, Salary ASC);

--- QUERY USING COMPOSITE INDEX
SELECT Department, Salary FROM Employee;

--- DROP COMPOSITE INDEX
DROP INDEX Employee.IX_EmpSalaryWithDepartment;


---====================================================
--- CLUSTERED INDEX
---====================================================
--- IF PRIMARY KEY EXISTS, CLUSTERED INDEX IS ALREADY CREATED

CREATE CLUSTERED INDEX IX_EmpEID
ON Employee (EID);

SELECT * FROM Employee;


---====================================================
--- INSERT DATA (CLUSTERED INDEX EFFECT)
---====================================================
--- DATA WILL BE STORED IN ORDER OF EID

INSERT INTO Employee VALUES
(5, 'Naresh', 'IT', 24, 30000, 11, 2),
(6, 'Kamesh', 'QA', 24, 20000, 12, 3);


---====================================================
--- SAMPLE TABLE (PRIMARY KEY = CLUSTERED INDEX)
---====================================================

CREATE TABLE Sample
(
    SID INT PRIMARY KEY
);

--- CHECK INDEX DETAILS
sp_helpindex 'Sample';

DROP TABLE Sample;


---====================================================
--- UNIQUE INDEX
---====================================================

CREATE UNIQUE INDEX IX_EID
ON Employee (EID);

SELECT * FROM Employee;

--- THIS WILL FAIL (DUPLICATE EID)
INSERT INTO Employee VALUES
(6, 'Kamlesh', 'Sales', 30, 2000, 13, 4);


---====================================================
--- DIFFERENCE BETWEEN UNIQUE KEY AND UNIQUE INDEX
---====================================================
--- UNIQUE KEY IS A CONSTRAINT
--- UNIQUE INDEX IS A PERFORMANCE OBJECT
--- UNIQUE INDEX IS FASTER PERFORMANCE-WISE


---====================================================
--- FILTERED NON-CLUSTERED INDEX
---====================================================
--- INDEX CREATED ONLY FOR NON-NULL VALUES

CREATE NONCLUSTERED INDEX IX_EmpTermination
ON Employee (TerminationDate)
WHERE TerminationDate IS NOT NULL;


---====================================================
--- IMPORTANT NOTE
---====================================================
--- INDEX ON SALARY WILL NOT SCAN ENTIRE TABLE
--- IT WILL DIRECTLY SEARCH INDEX PAGES
--- AFTER CREATING INDEX, EXECUTION PLAN SHOWS INDEX SEEK

---convert 
---functions 
---1. cast (convert datatype )

Select Ename,'$' + CAST(Esalary as varchar)
from employee
Select * from employee

---coalesce(to find first not null value )
Select EID,Ename,COALESCE(officeemail,personalemail,'NO EMAIL FOUND')
FROM Employee


---for how long the employee is working in our organization 
---find out employee age

---datediff(Interval,start_date,end_date)
Select Ename, DateofJoining ,datediff(Year,DOJ,'2026-01-31' as year of experience)
from employee

Select Ename, DOB ,datediff(Year,DOJ,getdata())
from employee

---replace(String,string_to_replace,newString)
Select Replace('Suresh  patel','  ',' ')
Select Replace(Phone,'+91','0')


---trim(use for removing whitespaces )
select trim(Ename) from employee

---you can also write FOR LEFT  
select Ltrim(Ename) from employee

---you can also write FOR RIGHT
select Rtrim(Ename) from employee

---LEN
select len(Ename) from employee
'Suresh'

---upper case 
select upper(Ename) from employee
'Suresh'


---getdate()

---concate

Select'Suresh' +''+ 'Patel'
Select'Suresh' +''+ NULL

Select'Suresh' +''+ 'Patel'
Select Concat('Suresh' ,NULL)