To create a manager database and execute DML queries using SQL.
UPDATE: It is used to update existing data within a table.
DELETE: It is used to delete records from a database table.
create table manager(enumber number(6),ename char(15),salary number(5),commission number(4),annualsalary number(7),Hiredate date,designation char(10),deptno number(2),reporting char(10));
insert into manager values(7369,'Dharsan',2500,500,30000,'30-June-81','clerk',10,'John');
insert into manager values(7839,'Subu',3000,400,36000,'1-Jul-82','manager',null,'James');
insert into manager values(7934,'Aadhi',3500,300,42000,'1-May-82','manager',30,NULL);
insert into manager values(7788,'Vikash',4000,0,48000,'12-Aug-82','clerk',50,'Bond');
SQL> UPDATE manager
2 SET salary = salary + (salary * 0.10),
3 annualsalary = annualsalary + (annualsalary * 0.10);
SQL> DELETE FROM manager WHERE salary < 2750;
Q3) Display each name of the employee as “Name” and annual salary as “Annual Salary” (Note: Salary in emp table is the monthly salary)
SQL> SELECT ename AS "Name", salary * 12 AS "Annual Salary" FROM manager;
SQL> SELECT ename FROM manager WHERE designation = 'clerk';
SQL> SELECT ename FROM manager WHERE designation != 'manager';
SQL> SELECT ename FROM manager WHERE commission = 0;
SQL> SELECT ename FROM manager WHERE ename LIKE 'S%' OR ename LIKE '%s';
SQL> SELECT ename, designation, deptno, Hiredate FROM manager ORDER BY Hiredate ASC;
SQL> SELECT * FROM manager WHERE Hiredate < TO_DATE('1981-09-30', 'YYYY-MM-DD');
Q11) List ename, deptno and sal after sorting emp table in ascending order by deptno and then descending order by sal.
SQL> SELECT ename, deptno, salary FROM manager ORDER BY deptno ASC;
SQL> SELECT ename, deptno, salary FROM manager ORDER BY deptno DESC;
SQL> SELECT ename FROM manager WHERE deptno NOT IN (10,30,40);
SQL> SELECT count(*) FROM manager;
SQL> SELECT ename, salary, annualsalary FROM manager WHERE salary = (SELECT max(salary) FROM manager);
SQL> SELECT ename, salary, annualsalary FROM manager WHERE salary = (SELECT min(salary) FROM manager);
SQL> SELECT avg(salary) FROM manager;
Q15) List the jobs and number of employees in each job. The result should be in the descending order of the number of employees.
SQL> SELECT designation, count(*) AS "Number of Employees" FROM manager GROUP BY designation ORDER BY count(*) DESC;
Thus, Manager database is created and DML queries such as insertion, updation, deletion are executed using SQL.