Write the SQL and give output in from ORACLE for the following queries, (Q 15 –
ID: 3821853 • Letter: W
Question
Write the SQL and give output in from ORACLE for the following queries, (Q 15 – q 21)
Q 15. Give the count of students who got better grade than Apple.
Q16. Give the name of faculty that have exactly one E in their name
Q 17. Give the salaries of faculty who are teaching student DRUM
Q18 Give the count of grades for INSS421. (i.e, How many A;s, how many B’s etc..)
Q 19. Assume a total of 12 classrooms. How many empty classes are available each day on listed class times? Ex: If there are 4 classes scheduled on W5:30 then there are 12 - 4 = 8 empty classes available on W5:30
Q 20 Give the names of students who are taking at least two classes in RM 219BC.
Q 21. Give the names of students and their grades who got better grades than the highest grade by GRAY in classes taught by KEEN
Sample data FACULTY OF Num, F Name, Rank, D Name, Salary) F NUM F NAME. SALARY RANK D NAME. LI2 DWIGHT PROFESSOR MISS 93500 LEE ASSISTANT MIS 60500 PROFESSOR MI1 88000 CORY ASSOCIATE 82500 MIS PROFESSOR ASSOCIATE 71500 MK1 JACKET MMK REFER PROFESSOR 49500 IMK3 GARY Professor MKT 891000 55000 DARWIN PROFESSOR MGTExplanation / Answer
Q 15.
Select count(*) from GRADE1 where g > (select g from GRADE1 where S_NAME = 'APPLE');
Q16.
Select F_NAME from FACULTY where F_Name like '%E%';
Q 17. Give the salaries of faculty who are teaching student DRUM
Select SALARY from FACULTY f inner join CLASS1 c on f.F_NAME = c.F_NAME inner join GRADE1 g1 on c.C_NAME = g1.C_NAME where g1.S_NAME = 'DRUM';
Q18
Select count(g) from GRADE1 group by g where C_NAME = 'INSS421';
Q 19.
Select COUNT(C_ROOM) from CLASS1 MINUS Select COUNT(C_ROOM) from CLASS1 group by C_TIME ;
Q 20
Select S_NAME from GRADE1 g1 inner join CLASS1 c on g1.C_NAME = c.C_NAME where c.C_ROOM = '219BC' HAVING COUNT(C_NAME) >=2;
Q 21.
Select S_NAME ,g from GRADE1 where g>(Select g from GRADE1 g1 inner join CLASS1 c on g1.C_NAME = c.C_NAME inner join FACULTY f on f.F_NAME = c.F_NAME where g1.S_NAME = 'GRAY' and f.F_NAME = 'KEEN');
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.