Notes

E41:
Create table subject(subjectcode varchar2(4) primary key,subjectname

varchar2(15),faculty varchar2(4));

add constraint : alter table subject add foreign key (faculty) references faculty (facultycode);

2select dept_code,count(*) as "no of student" from student group by dept_code;

3update faculty set salary=salary+500;

4select name from student where name like 'S%' UNION select facultyname from faculty where facultyname like 'S%';
5select name from student where address='Kaikhali';
6select facultyname from faculty where deptcode='IT';
8alter table subject add (department varchar2(4),year varchar2(1));

7. select faculty.FACULTYNAME from faculty ,department where faculty.deptcode=department.deptcode and department.hod ='F001';

9.update subject set department='IT', year='1' where SubjectCode='S003';

10. select FACULTYNAME from faculty WHERE salary > (select AVG(SALARY) from faculty)

11. select * from faculty WHERE salary > (select AVG(SALARY) from faculty) and deptcode='CSE'
12 select * from faculty WHERE salary = (select MAX(SALARY) from faculty)
select * from faculty WHERE salary = (select MIN(SALARY) from faculty)

13 select MAX(SALARY) from faculty WHERE salary NOT IN (select MAX(SALARY) from faculty)

14. SELECT * FROM FACULTY where FacultyCode not in (select HOD from department)

15. select SUBJECTNAME from subject WHERE department='CSE' AND year='3'

16. SELECT DEPTNAME FROM DEPARTMENT WHERE DEPTCODE IN (SELECT DEPTCODE FROM FACULTY GROUP BY DEPTCODE HAVING COUNT(DEPTCODE)=(SELECT MAX(COUNT(DEPTCODE)) FROM FACULTY GROUP BY DEPTCODE))
16. SELECT FACULTYNAME FROM FACULTY WHERE DeptCode IN (SELECT DEPTCODE FROM FACULTY GROUP BY DEPTCODE HAVING COUNT(DEPTCODE)=(SELECT MAX(COUNT(DEPTCODE)) FROM FACULTY GROUP BY DEPTCODE))