PRACTICE JOINS.sql

select * from STUDENT;
SELECT * FROM COLLEGE; 
SELECT * FROM APPLY,STUDENT WHERE STUDENT.SID = APPLY.SID; 
SELECT * FROM APPLY;
SELECT DISTINCT(MAJOR),STUDENT.SNAME FROM APPLY,STUDENT WHERE STUDENT.SID = APPLY.SID; 

--bY RAJ kUM

insert into College values ('step','KA',10000);
SELECT APPLY.MAJOR,STUDENT.SNAME,APPLY.CNAME,COLLEGE.ENROLLMENT FROM APPLY,STUDENT,COLLEGE WHERE STUDENT.SID = APPLY.SID AND APPLY.CNAME = COLLEGE.CNAME; 
select S.SNAME,S.GPA from STUDENT S WHERE S.SIZEHS < 1000;

select S.SNAME,S.GPA from STUDENT S,APPLY A WHERE S.SIZEHS < 1000 AND S.SID = A.SID AND A.MAJOR='CS' AND A.CNAME='Stanford';

SELECT DISTINCT S1.SNAME,S2.SNAME FROM STUDENT S1,STUDENT S2 WHERE S1.GPA=S2.GPA AND S1.SID <> S2.SID;

SELECT S1.SID,S1.SNAME,S2.SID,S2.SNAME FROM STUDENT S1 INNER JOIN STUDENT S2 ON S1.SID < S2.SID AND S1.GPA=S2.GPA;

SELECT S1.SID,S1.SNAME,A.CNAME,A.MAJOR,S1.GPA FROM STUDENT S1 INNER JOIN APPLY A ON S1.SID = A.SID;

SELECT S1.SID,S1.SNAME,A.CNAME,A.MAJOR FROM STUDENT S1 LEFT OUTER JOIN APPLY A ON S1.SID = A.SID;

SELECT C.CNAME FROM COLLEGE C WHERE C.CNAME NOT IN (SELECT CNAME FROM APPLY);

(SELECT DISTINCT A.CNAME FROM APPLY A);

SELECT * FROM COLLEGE;

select STUDENT.sname, STUDENT.sid, APPLY.major,APPLY.cname from STUDENT full outer join APPLY on STUDENT.SID = APPLY.SID;

SELECT DISTINCT(MAJOR) FROM APPLY;


SELECT * FROM STUDENT S WHERE S.SID IN (SELECT SID FROM APPLY WHERE APPLY.MAJOR='CS');

SELECT S.SID,S.SNAME,A.CNAME FROM STUDENT S INNER JOIN APPLY A ON S.SID = A.SID AND A.MAJOR='CS';

--nAMES OF STUDENTS WHO APPLIED TO CS
SELECT SNAME FROM STUDENT S WHERE S.SID IN (SELECT SID FROM APPLY WHERE APPLY.MAJOR='CS');

SELECT * FROM STUDENT



select * from APPLY;
select * from COLLEGE;
select * from STUDENT;


//College with fewer than 5 applicants// 
SELECT CNAME 
FROM APPLY
GROUP BY CNAME
HAVING COUNT(*) < 5;

//Number of colleges who have fewer than 5 applicants//
SELECT CNAME,COUNT(CNAME) 
FROM APPLY
GROUP BY CNAME
HAVING COUNT(*) < 5;

--//mAJORS WHOSE APPLICANTS MAXIMUM GPA IS BELOW AVERAGE GPA

SELECT APPLY.MAJOR FROM APPLY,STUDENT
WHERE APPLY.SID = STUDENT.SID
GROUP BY APPLY.MAJOR
HAVING MAX(GPA) < (SELECT AVG(STUDENT.GPA) FROM STUDENT);

-- tRY TO PUT AVERAGE WITH ROWS ALSO

select student.sid,sname,cname,count(distinct cname) over (partition by student.sid) as count from student,apply 
where student.sid = apply.sid order by sid;

select student.sid,sname,cname,count(cname) over () from student,apply 
where student.sid = apply.sid group by student.sid;