SQLQuery1FINAL.sql

CREATE DATABASE JAN2026;

-- Use the database
USE JAN2026;

-- TYPES OF SQL LANGUAGE
-- DDL : Data Definition Language (Defines structure)
-- DML : Data Manipulation Language (INSERT, UPDATE, DELETE)
-- DQL : Data Query Language (SELECT)
-- TCL : Transaction Control Language (COMMIT, ROLLBACK, SAVEPOINT)

-- Basic SELECT statements
SELECT 4 + 4 AS Addition;

SELECT 'Hello ' + ' Patel' AS FullName;

SELECT GETDATE() AS CurrentDate;

SELECT SYSTEM_USER AS SystemUser;

-- To drop a database
-- DROP DATABASE JAN2026;

-- Rename database
ALTER DATABASE JAN2026
MODIFY NAME = JANUARY2026;

-- To create a table in the database
-- CREATE TABLE <TableName>
-- (ColumnName DataType)
CREATE TABLE Employee
(
    EID INT,
    Ename VARCHAR(20),
    Eage INT,
    Esalary MONEY
);
-- Display all records from Employee table
SELECT * FROM Employee;

-- Rename table
EXEC sp_rename 'Employee', 'EMP';

-- Rename column
EXEC sp_rename 'EMP.EID', 'EMPID', 'COLUMN';

-- Display all records from EMP table
SELECT * FROM EMP;

-- Display all databases on the server
SELECT name, create_date
FROM sys.databases;

-- Display all tables in the current database
SELECT name
FROM sys.tables;

-- To drop a table
-- DROP TABLE Employee;

-- Rename database again
ALTER DATABASE JANUARY2222
MODIFY NAME = JANUARY2026;

USE JANUARY2026;

SELECT name FROM sys.tables;

ALTER TABLE EMP
ALTER COLUMN EMPID VARCHAR(10);

EXEC sp_rename 'EMP', 'Employee';

ALTER TABLE Employee
ADD Email VARCHAR(50);

INSERT INTO Employee (EMPID, Ename, Eage, Esalary, Email)
VALUES ('E201', 'Anjalii', 22, 30000, 'anjalii@gmail.com');

INSERT INTO Employee (EMPID, Ename, Eage, Esalary, Email)
VALUES
('E202', 'Riya', 23, 28000, 'riya@gmail.com'),
('E203', 'Neha', 21, 25000, 'neha@gmail.com');

SELECT * FROM Employee;

--used for the topmost column which will show a row 
Select top 0 * from Employee

---if u want to see the whole table 
sp_help Employee

---update first method 
Update Employee
Set ESalary = 35000
where EMPID = 'E201' 

Select * from Employee

--update second method -2 
--UPDATE <TABLENAME>
--SET COLUMNNAME = VALUE 
--where 
Update Employee
Set Esalary = 40000 
Where Esalary is NULL

---UPDATE THIRD METHOD 
UPDATE Employee
Set Esalary = 50000 

--delete method 1 
 Delete from Employee 
 Where Ename = 'Neha'

 --delete method 2 
INSERT INTO Employee (EMPID, Ename, Eage)
VALUES ('E204', 'Gopi',23);
 Select * from Employee
 Delete from Employee
 Where Esalary is NULL 

 ---IMPORTANT FOR INTERVIEW POINT OF VIEW
 
---DELETE METHOD 3
--IF WE USE DELETE ,IT WILL DELETE ALL THE VALUES NOT THE TABLE AND IF WE DROP THE TABLE , IT WILL DELETE WHOLE TABLE .

--USING DELETE FEATURE ,U CAN RECORD SPECIFIC RECORD BUT IN DROP, IT WILL OT ALLOW .

TRUNCATE Table Employee 
--similar to delete but u cannot use where .

---differnce between delete ,drop,truncate 
---1.can delete individual rows/records/tuples 1.no 1.no 2.entire rows/records/tuples are deleted but table structure remains(sche,a,columns)remains.
---2. table structure will be deleted 2.table structure will remain. delete can be undo but i truncate it can't .

Drop Table Employee

--identity Funcion ( Auto-Increment )
--identity is used only in INT datatype 

Create Table Employee
(EID INT Identity,Ename Varchar(255),Eage INT)
Insert into Employee values 
('Suresh',20)
Select * from Employee
Insert into Employee values 
('Ramesh',22)

--BY DEFAULT IT IS OFF , WE NEED TO ON IT TO MANUALLY insert the values in the table 
--Set Identity_Insert Employee ON

Set Identity_Insert Employee OFF


INSERT INTO Employee (EID, Ename, Eage)
VALUES (4,'Mahesh',20)

--- u cannot insert identity after creating the table or inbetween .it should be created initially only .

INSERT INTO Employee
VALUES ('Jayesh',24)

--u can give only one identity function per table 
--EID INT IDENTITY(100,1)
--100 → starting value
--1 → increment value
--So values will be:
--100, 101, 102, 103, ...
--u can only apply the identity initially after creating a table .