SQL Examples

108pg

 

- Find the number of tuples of "student" relation.

Select count(*)
from student;

 

- Find the average salary, maximum salary, and minimum salary of professors in CS department.

Select avg(salary), max(salary), min(salary)
from professor
where deptName='CS';

- Find the number of distinct professors who teach a course in the Spring 2010 semester.

Select count(distinct pID)
from teaches
where semester='Spring' and year=2010;

 

109pg

- Find the number of professors in each department.

Select deptName, count(*)
from professor
group by deptName;

- Find the average salary of professors in each department.

Select deptName, avg(salary)
from professors
group by deptName;

110pg

Select deptName, pID, avg(salary) // pID가 올 수 없음. 'group by'의 속성이나 집계함수만 가능함!
from professor
group by deptName;

Select avg(salary) // 이렇게 'group by'의 속성이 없어도 괜찮음.
from professor
group by deptName;

- Find the names and average salaries of all departments whose average salary is greater than 6900.

Select deptName, avg(salary)
from professor
group by deptName
having avg(salary)>6900;

111pg

- employee(name, salary, dno) -name이 PK
- department(dnumber, dname, location) -dnumber가 PK

- For each department having more than five employees,
retrieve the department name and the number of employees making more than $40,000

Select dname, count(*) //잘못된 쿼리문
from employee, department
where dno=dnumber and salary>40000 //조인하고 $40,000보다 많이 버는 사람들만 남긴 후
group by dname //dname으로 그룹핑
having count(*)>5; //$40,000보다 많이 버는 사람들이 5명보다 많은 그룹을 추출

Select dname, count(*) //올바른 쿼리문
from employee, department
where dno=dnumber and //조인하고
dno in (Select dno from employee group by dno having count(*)>5) and //dno가 5개보다 많은 tuple들에 대해서 추출하고
salary>40000 //$40,000보다 많이 버는 사람들 tuple을 추출한 후 
group by dname //dname으로 그룹핑

117pg

- Get the name of professor who has the same salary as the professor with pID 10.

Select name
from professor
where salary = 
	(Select salary
	from professor
	where pID=10)
and pID <> 10;

- Get the name of professor who earns the maximum salary of the CS department.

Select name
from professor
where salary=
    (Select max(salary)
    from professor
    where deptName='CS');

118pg

- Get names and salaries of professor who has ID with 10 or 21 or 22.

Select name, salary
from professor
where pID in (10,21,22);

또는

Select name, salary
from professor
where pID=10 or pID=21 or pID=22;

- Find course numbers offered in Fall 2009 and in Spring 2010.

Select distinct cID //2009Fall과 2010Spring에 둘 다 개설 될 시 중복이 생기니 distinct함.
from teaches
where semester='Fall' and year=2009
and cID in
(Select cID
from teaches
where semester='Spring' and year=2010);

또는

(Select cID from teaches where semester='Fall' and year=2009)
intersect
(Select cID from teaches where semester='Spring' and year=2010);

- Find course numbers offered in Fall 2009 but not in Spring 2010.

Select distinct cID
from teaches
where semester='Fall' and year=2009 and
cID not in
(Select cID
from teaches
where semester='Spring' and year=2010);

혹은

(Select cID from teaches semester='Fall' and year=2009)
except
(Select cID from teaches semester='Spring' and year=2010);

119pg

- Find the total number of distinct students who have taken any courses 
taught by the professor with pID 10.

Select count(distinct sID)
from takes
where (cID, semester, year) in //takes랑 teaches가 가진 동일한 속성들은 모두 join해야 함.
(Select cID, semester, year
from teaches
where pID=10);

또는

Select count(distinct sID)
from takes,teaches
where takes.cID=teaches.cID and
takes.semester=teaches.semester and
takes.year=teaches.year and
pID=10;

121pg

- Find professor names with salary greater than that of some (at least one) professor
in CS department.

Select distinct T.name
from professor as T, professor as S
where T.salary > S.salary and S.deptName='CS';

또는

Select name
from professor
where salary > some(Select salary from professor where deptName='CS');

- Find the professor names whose salary is greater than the salary of all professors
in CS department

Select name
from professor
where salary > all(Select salary from professor where deptName='CS');

122pg

- Find all course numbers taught in both the Fall 2009 semester 
and the Fall 2010 semester.

Select S.cID
from teaches as S
where S.semester='Fall' and S.year=2009 and
exists 
(Select *
from teaches as T
where T.semester='Fall' and T.year=2010 and S.cID=T.cID);

123pg

- Find all student IDs and names who have taken all courses offered in 'CS' department.

Select S.sID, S.name
from student as S
where not exists(
    (Select cID
    from course
    where deptName='CS')
	except
    (Select T.cID
    from takes as T
    where S.sID=T.sID)
)

124pg

- Find all courses that were offered at most once in 2009.

Select C.cID
from course as C
where unique (
    Select T.cID
    from teaches as T
	where C.cID=T.cID and T.year=2009
)

Select C.cID
from course as C
where 1>= 
(Select count(T.cID)
from teaches as T
where S.cID=T.cID and T.year=2009
);

125pg

- Find department name and the average salary of the professors of the department
where the average salary is greater than 6900.

Select deptName, avgSalary
from 
(Select deptName, avg(salary) as avgSalary
from professor
group by deptName
)
where avgSalary>6900;

또는 

Select deptName, avgSalary
from
        (Select deptName, avg(salary)
        from professor
        group by deptName) 
    as deptAvg(deptName, avgSalary)
where avgSalary>6900;

- Find the maximum total salary of department across all departments.

Select max(sumSalary)
from
(Select deptName, sum(salary)
from department
group by deptName) 
as deptTotal(deptName, sumSalary);

또는 

Select sum(salary)
from professor
group by deptName
having sum(salary) > all(Select sum(salary) from professor group by deptName);

126pg

- Find professor names, their salaries, the average salary of their departments.

Select P1.name, P1.salary, avgSalary
from professor P1,
lateral 
(Select avg(P2.salary) as avgSalary
from professor as P2
where P1.deptName=P2.deptName);

Select name, salary, avg(salary) //Syntax Error → name, salary, avg(salary) 개수가 다름
from professor
group by deptName;

- Find the department name with the maximum budget along with its budget.

With
maxBudget(value) as
(Select max(budget)
from department)
Select deptName, budget
from department, maxBudget
where department.budget=maxBudget.value;

또는

Select deptName, budget
from department
where budget = (Select max(budget) from department);

127pg

- Find all departments where the total salary is greater than
the average of the total salary at all departments

With 
	Total(deptName,value) as 
        (Select deptName,sum(salary)
        from professor
        group by deptName),
    avgTotal(value) as
        (Select avg(value)
        from Total)
    Select deptName
    from Total,avgTotal
    where Total.value>avgTotal.valuel;

- Find department names along with the number of professors.

Select deptName,(Select count(*) 
				from professor p1 
                where p1.deptName = d1.deptName)
from department d1;

128pg

- Find the rank of each student.
studentGrades(ID, deptName, GPA)

Select ID, rank() over (order by GPA desc) as sRank from studentGrades
order by sRank;

또는

Select ID, (1+ (Select count(*) from studentGrades B where B.GPA>A.GPA)) as sRank
from studentGrades A
order by sRank;

129pg

- Find the rank of students within each department

Select ID, deptName, rank() over(partition by deptName order by GPA desc) as sRank
from studentGrades
order by deptName, sRank;