EXP4_CSE2004Lab_20BRS1064

--EXP4_CSE2004Lab_20BRS1064
CREATE TABLE employee_20BRS1064(
empid varchar2(4) CHECK (empid LIKE 'E%') PRIMARY KEY,
empname varchar2(20),
jobname varchar2(20),
hiredate DATE,
salary INT,
commission INT,
depid INT
);
INSERT INTO employee_20BRS1064 VALUES('E101','KAYLING','PRESIDENT',
to_date('10-01-2020','dd-mm-yyyy'),'6000.00','400.00','1001');
INSERT INTO employee_20BRS1064 VALUES('E102','BLAZE JONAS','MANAGER',
to_date('14-03-2019','dd-mm-yyyy'),'2750.00','600.00','3001');
INSERT INTO employee_20BRS1064 VALUES('E103','CLARE JONAS','MANAGER',
to_date('15-04-2018','dd-mm-yyyy'),'2550.00','400.00','1001');
INSERT INTO employee_20BRS1064 VALUES('E104','JONAS','MANAGER',
to_date('10-05-2020','dd-mm-yyyy'),'2957.00','600.00','2001');
INSERT INTO employee_20BRS1064 VALUES('E105','SCARLET','ANALYST',
to_date('10-01-2020','dd-mm-yyyy'),'3100.00','600.00','2001');
INSERT INTO employee_20BRS1064 VALUES('E106','FRANK','ANALYST',
to_date('12-01-2020','dd-mm-yyyy'),'3100.00','600.00','2001');
INSERT INTO employee_20BRS1064 VALUES('E107','SANDRINE','CLERK',
to_date('15-03-2019','dd-mm-yyyy'),'900.00','600.00','2001');
INSERT INTO employee_20BRS1064 VALUES('E108','ADELYN','SALESMAN',
to_date('19-01-2020','dd-mm-yyyy'),'1700.00','400.00','3001');
INSERT INTO employee_20BRS1064 VALUES('E109','WADE JONAS','SALESMAN',
to_date('10-08-2019','dd-mm-yyyy'),'1350.00','600.00','3001');
INSERT INTO employee_20BRS1064 VALUES('E110','MADDEN','SALESMAN',
to_date('20-09-2019','dd-mm-yyyy'),'1350.00','1500.00','3001');
INSERT INTO employee_20BRS1064 VALUES('E111','TUCKER JONAS','SALESMAN',
to_date('22-10-2015','dd-mm-yyyy'),'1600.00','0.00','3001');
INSERT INTO employee_20BRS1064 VALUES('E112','ADNRES','CLERK',
to_date('15-08-2019','dd-mm-yyyy'),'1200.00','600.00','2001');
INSERT INTO employee_20BRS1064 VALUES('E113','JULIUS','CLERK',
to_date('28-08-2020','dd-mm-yyyy'),'1050.00','1500.00','3001');
INSERT INTO employee_20BRS1064 VALUES('E114','MARKER','CLERK',
to_date('10-08-2019','dd-mm-yyyy'),'1500.00','1500.00','1001');

--1
SELECT round(SQRT(salary),2) FROM employee_20BRS1064;
--2
SELECT SUM(salary) FROM employee_20BRS1064;
SELECT MIN(salary) FROM employee_20BRS1064;
SELECT MAX(salary) FROM employee_20BRS1064;
SELECT round(avg(salary),2) FROM employee_20BRS1064;
SELECT stats_mode(salary) FROM employee_20BRS1064;
--3
SELECT substr(empname,1,3) AS abc FROM employee_20BRS1064;
--4
SELECT REPLACE(empname,'JONAS','SMITH') FROM employee_20BRS1064;
--5
SELECT MIN(salary) AS min_salary FROM employee_20BRS1064;
SELECT MAX(salary) AS max_salary FROM employee_20BRS1064;
--6
SELECT EXTRACT(MONTH FROM hiredate) AS MONTH FROM employee_20BRS1064;
--7
SELECT to_char(hiredate,'dd-mm-yy') AS HireDate FROM employee_20BRS1064;
--8
SELECT add_months(hiredate,2) AS HireDate FROM employee_20BRS1064;
--9
SELECT last_day('05-OCT-15') AS HireDate FROM dual;
--10
SELECT round(avg(commission),2) FROM employee_20BRS1064;
--11
SELECT sysdate - 60 FROM dual;
--12
SELECT empname,hiredate FROM employee_20BRS1064 WHERE to_char(hiredate,'mm')=08;
--13
13. List OUT the employee names who will celebrate their WORK anniversary during CURRENT MONTH.
SELECT empname,hiredate FROM employee_20BRS1064
WHERE to_char(hiredate,'mm')=08 AND
to_char(hiredate,'yyyy')=2020;
--14
SELECT empname,hiredate FROM employee_20BRS1064 WHERE hiredate=sysdate;
--15
SELECT MAX(salary)-MIN(salary) AS diif_Salary FROM employee_20BRS1064;
--16
SELECT empname,salary+commission AS TotalSalary FROM employee_20BRS1064;
--17
SELECT empname,salary+commission AS totalSalary FROM employee_20BRS1064;
--18
SELECT SUM(salary+commission) AS TotalSalary,jobname FROM employee_20BRS1064 GROUP BY jobname;
--19
SELECT initcap(jobname) FROM employee_20BRS1064;
--20
SELECT COUNT(DISTINCT(jobname)) FROM employee_20BRS1064;
--21
SELECT empname FROM employee_20BRS1064 WHERE LENGTH(empname)=7;
--22
SELECT COUNT(empname),jobname FROM employee_20BRS1064 GROUP BY jobname;


DROP TABLE employee_20BRS1064;