Query_with_comments.rtf

{\rtf1\ansi\ansicpg1252\cocoartf2638
\cocoatextscaling0\cocoaplatform0{\fonttbl\f0\fswiss\fcharset0 Helvetica;}
{\colortbl;\red255\green255\blue255;}
{\*\expandedcolortbl;;}
\paperw11900\paperh16840\margl1440\margr1440\vieww28300\viewh14720\viewkind0
\pard\tx566\tx1133\tx1700\tx2267\tx2834\tx3401\tx3968\tx4535\tx5102\tx5669\tx6236\tx6803\pardirnatural\partightenfactor0

\f0\fs24 \cf0 -----------------Query1--------------------------\
/*Query to find total number of people in college*/\
select count(*) as Population\
from(\
    select inst_id from instructor\
	\
	union all\
	\
	select std_id from student\
) Population;\
\
------------------Query2---------------------------\
/*Query to find the number of students per advisor */\
\
select count(std_id) as "Number of Students",ad_name\
from (select s.std_id,aa.ad_name as ad_name\
	  from student s,advisor aa\
	 where s.ad_id=aa.ad_id) sub\
	 group by ad_name;\
	 \
	 \
	 \
-------------------Query3-------------------------\
/*Query to find the professors who are not advisors*/\
\
Select u.name as Name\
from "User" u,Instructor i\
where u.ID=i.ID\
and i.inst_id not in (select inst_id from advisor);\
\
\
-------------------Query4----------------------------\
/*Query to find users whose name start with A and S where age is less than 20 and belongs to Computer Science Department*/\
\
Select * from (\
select * from "User" u\
where u.name like 'A%'\
	) sub\
where dept_id in (select dept_id from department where dept_name='Computer Science\'92);\
and age<20;\
\
\
\
\
\
-------------------Query5----------------------------\
/*Query to get number of people in each building*/\
\
select count(*),building\
from (select u.id,d.building from "User" u, department d\
	 where u.dept_id=d.dept_id) B\
	 group by building;\
	 \
	 \
--------------------Query6----------------------------\
/*Query to know which instructor handles classes in which building*/\
\
Select name as "Instructor_Name",building as "Building" \
from (select name,building \
	  from "User" u, department d\
	  where u.dept_id=d.dept_id\
	  and u.id in (select id from instructor ))Details;\
						  \
						  \
-------------------Query7-----------------------------\
/*Query to find the Students with GPA less than 3*/\
\
select Name from "User"\
where id in(select id from student where gpa<3);\
\
\
\
------------------Query8-------------------------------\
/* To find the details of the Professors who are advisors*/\
\
Select name,age,address,email_id from "User"\
where name in (select ad_name from advisor);\
\
\
------------------Query9-------------------------------\
/*Students who are below age of 23*/\
Select u.* from \
"User" u\
where u.ID in (select id from Student)\
and u.age<m;\
\
\
------------------Query10-------------------------------\
/*To find the average age of the students who are having GPA of 3*/\
\
Select avg(u.age) from \
"User" u\
where u.ID in (select id from student where gpa=3);\
\
\
\
\
\
\
\
\
\
\
\
\
\
\
\
\
\
\
}