CREATE TABLE emp73
(
empno NUMBER,
ename VARCHAR2(255),
jobtitle VARCHAR(20),
managername VARCHAR2(200),
joiningdate DATE,
salary NUMBER,
deptno NUMBER
);
INSERT INTO emp73(empno,ename,jobtitle,managername,joiningdate,salary)
VALUES(101,'PARTHA','MANAGER','SAM','23-JAN-2023',27000);
INSERT INTO emp73(empno,ename,jobtitle,managername,joiningdate,salary)
VALUES(102,'KARTHA','CLERK','TAM','23-MAY-2015',37000);
INSERT INTO emp73(empno,ename,jobtitle,managername,joiningdate,salary)
VALUES(101,'DARTHA','MANAGER','SAM','15-SEP-1996',20000);
INSERT INTO emp73(empno,ename,jobtitle,managername,joiningdate,salary)
VALUES(101,'MARTHA','STAFF','TAM','01-FEB-2001',17000);
INSERT INTO emp73(empno,ename,jobtitle,managername,joiningdate,salary)
VALUES(101,'SARTHA','EMPLOYEE','SAM','20-JAN-1995',19000);
ALTER TABLE emp73 ADD experience INTEGER;
UPDATE emp73 SET experience=(months_between(SYSDATE,joiningdate))/12;
SELECT*from emp73;
UPDATE emp73 SET salary=(salary+(salary*10/100)) WHERE experience>10;
DELETE from emp73 WHERE experience>=25;
SELECT managername, COUNT(managername)
FROM emp73
GROUP BY managername
HAVING COUNT(managername)>1;
CREATE VIEW nameview3
as select ename, managername
FROM emp73;
CREATE TABLE stu12
(
stud_id NUMBER PRIMARY KEY,
student_name VARCHAR2(40),
mark1 NUMBER,
mark2 NUMBER,
mark3 NUMBER,
vtotal NUMBER,
percentage NUMBER,
grade VARCHAR2(2)
);
INSERT INTO stu12 (stud_id,student_name,mark1,mark2,mark3)
VALUES (1,'RAHMAN',100,99,98);
INSERT INTO stu12 (stud_id,student_name,mark1,mark2,mark3)
VALUES (2,'SIDDHU',80,90,89);
INSERT INTO stu12 (stud_id,student_name,mark1,mark2,mark3)
VALUES (3,'VIJAY',100,100,100);
INSERT INTO stu12 (stud_id,student_name,mark1,mark2,mark3)
VALUES (4,'DEVARAKONDA',67,45,75);
INSERT INTO stu12 (stud_id,student_name,mark1,mark2,mark3)
VALUES (5,'ANAND',74,09,36);
INSERT INTO stu12 (stud_id,student_name,mark1,mark2,mark3)
VALUES (6,'NIVINPAULY',38,92,59);
UPDATE stu12 SET vtotal=mark1+mark2+mark3;
ALTER TABLE stu12 ADD average INTEGER;
UPDATE stu12 SET average=vtotal/3;
UPDATE stu12 SET percentage=vtotal*300/100;
UPDATE stu12 SET grade='A' WHERE average>90;
UPDATE stu12 SET grade='B' WHERE average<=90 AND average>80;
UPDATE stu12 SET grade='C' WHERE average<=80 AND average>70;
UPDATE stu12 SET grade='D' WHERE average<=70 AND average>60;
UPDATE stu12 SET grade='E' WHERE average<=60 AND average>50;
UPDATE stu12 SET grade='F' WHERE average<=50 AND average>40;
UPDATE stu12 SET grade='U' WHERE average<40;
CREATE VIEW stuview3 AS SELECT stud_id,student_name,percentage FROM stu12 WHERE
grade!='U';
CREATE VIEW student002 AS
SELECT stud_id, student_name, percentage
FROM stu12;
CREATE VIEW MarksView AS
SELECT stu12.stud_id, stu12.student_name, [Link]
FROM stu12
WHERE [Link]!='U';
