To create a student database and execute DDL queries using SQL. To create a manager database and execute DML queries using SQL.
The SQL commands that deal with the manipulation of data present in the database belong to DML or Data Manipulation Language and this includes most of the SQL statements. It is the component of the SQL statement that controls access to data and to the database. Basically, DCL statements are grouped with DML statements.
INSERT: It is used to insert data into a table. 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');
UPDATE manager
SET salary = salary + (salary * 0.10),
annualsalary = annualsalary + (annualsalary * 0.10);
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)
SELECT ename AS "Name", salary * 12 AS "Annual Salary"
FROM manager;
SELECT ename from manager where designation='clerk';
SELECT ename from manager where designation!='manager';
SELECT ename from manager where commission=0;
SELECT ename
FROM manager
WHERE ename LIKE 'S%' OR ename LIKE '%s';
select ename,designation,deptno,Hiredate from manager
order by Hiredate asc;
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.
select ename,deptno,salary from manager order by deptno asc;
select ename,deptno,salary from manager order by salary desc;
select ename from manager where deptno not in (10,30,40);
#3# QUERY:
select count(*) from manager;
select ename ,salary,annualsalary from manager
where salary = (select max(salary) from manager);
select ename ,salary,annualsalary from manager
where salary = (select min(salary) from manager);
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.
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.