SQLASSIGNMENT5.sql

use sql_anjali_session
CREATE TABLE Employeedetails (
    EID INT IDENTITY(1,1) PRIMARY KEY,
    Name VARCHAR(50),
    Age INT,
    Salary INT,
    Department VARCHAR(20),
    City VARCHAR(30)
);

INSERT INTO Employeedetails (Name, Age, Salary, Department, City) VALUES
('Suresh', 25, 30000, 'QA', 'New York'),
('Ramesh', 28, 45000, 'DA', 'Mumbai'),
('Amit', 22, 18000, 'FS', 'Delhi'),
('Anita', 24, 22000, 'DE', 'New York'),
('Arjun', 26, 50000, NULL, 'Pune'),
('Bina', 21, 0, 'QA', 'New York'),
('Chirag', 23, 35000, 'FS', 'Ahmedabad'),
('Deepa', 27, 60000, 'DE', 'Bangalore'),
('Ajay', 29, 48000, 'DA', 'New York'),
('Neha', 24, 20000, 'QA', 'Mumbai');

---Name = "Suresh"
SELECT Name
FROM Employeedetails
WHERE Name = 'Suresh';

---Name starts with a letter less than 'D'
SELECT TOP 100 *
FROM Employeedetails
WHERE Salary <> 0;

---City = "New York"
SELECT *
FROM Employeedetails
WHERE Name < 'D';

---City = "New York"
SELECT *
FROM Employeedetails
WHERE City = 'New York';

---Name = "Ramesh" with alias
SELECT Name AS "Employee Name"
FROM Employeedetails
WHERE Name = 'Ramesh';

---Department = 'QA
SELECT *
FROM Employeedetails
WHERE Department = 'QA'
OR (Age > 23 AND Salary < 50000);

---Department IN ('DA','FS','DE')
SELECT *
FROM Employeedetails
WHERE Department IN ('DA', 'FS', 'DE');

SELECT *
FROM Employeedetails
WHERE Department = 'DA'
OR Department = 'FS'
OR Department = 'DE';

---Name starts with 'A'
SELECT *
FROM Employeedetails
WHERE Name LIKE 'A%';

---Name ends with 'a'
SELECT *
FROM Employeedetails
WHERE Name LIKE '%a';

---Department is NOT NULL
SELECT *
FROM Employeedetails
WHERE Department IS NOT NULL;

---EID & Salary where Department exists AND Salary > 20000
SELECT EID, Salary
FROM Employeedetails
WHERE Department IS NOT NULL
AND Salary > 20000;