--Creating Employee Table
CREATE TABLE Employee
(
	 Employee_ID int NOT NULL PRIMARY KEY,
  	 First_Name varchar(20),
  	 Last_Name varchar(25),
  	 Emai_Id varchar(14),
  	 Phone_Number varchar(20),
  	 Hire_Date Date,
  	 Job_Id varchar(10),
  	 Salary int,
  	 Commission_Percent int,
  	 Manager_Id int,
  	 Department_Id int
); 

CREATE TABLE Department
(
  	Department_Id int,
  	Department_Name varchar(30),
  	Manager_Id int,
  	Location_Id int
);

CREATE TABLE Salgrade
(
  	Grade_Level varchar(3),
  	Lowest_Sal int,
  	Highest_Sal int
);

INSERT INTO Employee
VALUES
(100,"Sailaja","Balgeri","sb","5151231421","06/17/87","AD_PRES",24000,0.9,null,90),
(101,"Neelim","Atmuri","na","5151241431","09/21/89","AD_VP",17000,0.9,100,90),
(102,"Nitin","Agarwal","nag","5141231543","07/13/93","AD_VP",17000,0.9,100,90),
(103,"Prasanth","Rathore","pr","5151241421","01/03/90","IT_PROG",9000,0.6,102,60),
(104,"Santosh","Devunuri","sd","5141521278","21/05/91","IT_PROG",6000,0.6,103,60),
(107,"Ravi",null,"ra","5121231234","07/02/99","IT_PROG",4200,0.6,103,60),
(124,"Vijay",null,"vi","5132351246","16/11/99","ST_MAN",5800,0.5,100,50),
(141,"Naveen",null,"nav","5121241234","17/10/95","ST_CLERK",3500,0.5,124,50),
(142,"Amrith",null,"am","5121242345","29/01/97","ST_CLERK",3100,0.5,124,50),
(143,"Srikanth","G","sg","5121231245","15/03/98","ST_CLERK",2600,0.5,124,50);

INSERT INTO Salgrade
VALUES("A",1000,2999),
("B",3000,5999),
("C",6000,9999),
("D",10000,14999),
("E",15000,24999),
("F",25000,40000);

INSERT INTO Department 
VALUES
(10,"Administration",200,1700),
(20,"Marketing",201,1800),
(30,"Shipping",124,1500),
(60,"IT",103,1400),
(80,"Sales",149,2500),
(90,"Executive",100,1700),
(110,"Accounting",205,1700),
(190,"Contracting",null,1700);

--2)
CREATE TABLE Emp
(
  Eno int(6),
  Ename varchar(30),
  Sal decimal(8,2)
 );

 --3)
 ALTER TABLE Emp
 ADD deptno int(4);
 
 --4)
 ALTER TABLE Emp
 RENAME Column Sal to Salary;

 --5)
 
 --6)
 CREATE TABLE Dept
 (
   Dno int(6),
   Dname varchar(40),
   loc_id int(4)
 );
 
 --7)
 ALTER TABLE Dept
 DROP Column loc_id;

 --8)
ALTER TABLE Dept
RENAME to Departments;

--9)
INSERT INTO Emp
VALUES
(1,"ANIL",20000,1001),
(2,"ATUL",25000,2002),
(3,"ARUL",30000,1001),
(4,"AMIT",27000,3003),
(5,"ANUP",35000,2002),
(6,"ANIR",30000,1001);

--10)
INSERT INTO Departments
VALUES
(1001,"MARKETING"),
(2002,"SALES"),
(3003,"EXECUTIVE"),
(4004,"IT"),
(5005,"PRODUCTION");

--11)
UPDATE Emp
SET Salary = 40000
WHERE deptno = 1001;

--12)
UPDATE Emp
SET deptno = 2002
WHERE Ename = "ARUL";

--13)
DELETE FROM Emp
WHERE Salary = 27000;

--14)
DELETE FROM Departments

--15)
DROP TABLE Departments;

--16)
DROP TABLE Emp;

--17)

--18)
PRAGMA table_info(Salgrade);

--19)
SELECT * FROM Department;

--20)
PRAGMA table_info(Employee);

--21)
SELECT Employee_Id,Last_Name,Job_Id,Hire_Date as STARTDATE
FROM Employee;

--22)
SELECT DISTINCT Job_Id FROM Employee;

--23)
SELECT Last_Name || ", " || Job_Id as Concatenated_names
FROM Employee;

--24)
SELECT Last_Name,Salary 
FROM Employee
WHERE Salary > 12000;

--25)
SELECT Last_Name, Department_Id 
FROM Employee
WHERE Employee_Id = 107;

--26)
SELECT Last_Name,Salary 
FROM Employee
WHERE Salary NOT BETWEEN 5000 AND 12000;

--27)
SELECT Last_Name,Job_Id,Hire_Date
FROM Employee
WHERE Hire_Date BETWEEN 02/20/98 to 02/05/98
ORDER BY Hire_Date;

--28)
SELECT Last_Name,Department_Id
FROM Employee
WHERE Department_ID = 20 OR Department_Id = 50
ORDER BY Last_Name;

--29)
SELECT Last_Name as Employees,Salary as Monthly_Salary
FROM Employee
WHERE Salary Between 5000 AND 12000 AND Department_Id in (20,50)

--30)
SELECT Last_Name,Hire_Date
FROM Employee
WHERE Hire_Date Like '%99';

--31)
SELECT Last_Name,Salary,Commission_Percent
FROM Employee
ORDER BY Hire_Date,Salary,Commission_Percent DESC

--32)
SELECT Last_Name 
FROM Employee
WHERE Last_Name LIKE "__A%";

--33)
SELECT Last_Name
FROM Employee
WHERE Last_Name LIKE "%A%" OR "%E%";

--34)
SELECT e.Last_Name,e.Job_Id,e.Salary
FROM Employee as e
LEFT JOIN Department as d ON e.Department_Id = d.Department_Id
WHERE e.Salary NOT IN (2500,3500,7000) AND d.Department_Name NOT IN ("Sales","Executive");

--35)
SELECT DATE('now');
SELECT CURRENT_DATE;

--36)
SELECT Employee_ID,Last_Name,Salary,(Salary+(Salary*0.15)) as New_Salary
FROM Employee;

--37)
SELECT Employee_ID,Last_Name,Salary,(Salary+(Salary*0.15)) as New_Salary,((Salary+(Salary*0.15)) - Salary) as Increase
FROM Employee;

--38)
SELECT Last_Name || " earns " || Salary || " monthly but wants " || (Salary*3) || "." as Dream_Salary
FROM Employee

--39)
--40)
SELECT Last_Name,"$" || SUBSTR('               ' || CAST(Salary AS TEXT), +15) as Salary
FROM Employee

--42)
SELECT Last_Name,Hire_Date,DAYNAME(Hire_Date) as DAY
FROM Employee;

--43)
SELECT Last_Name || +", " || (Salary*12) as EMPLOYEES_AND_THEIR_SALARIES
FROM Employee;
*/
by