scott
lion
cscdart

describe students

select * 
from students;

SELECT *
FROM STUDENTS;

select studentid, first, last
from students;

select major from students; 

select distinct major from students; 

select *
from students
where year = 'Fr';

select *
from students
where year = 'fr';

select *
from students
where year = 'Fr' 
and gpa < 3.0;

select studentid, last, first, year, gpa
from students
where year = 'Fr' and gpa < 3.0;

select * from students
order by gpa;

select * from students
order by gpa desc;

select studentid, last, 
first, sectionid
from students, 
enrollments;

select students.studentid, last, 
first, sectionid
from students, 
enrollments;

select students.studentid,  
last, first, sections.sectionid, dept, course
from students, enrollments, sections
where enrollments.studentid
= students.studentid
and 
enrollments.sectionid 
= sections.sectionid;

select students.studentid, last, first,
year, major
from students, enrollments
where enrollments.studentid
= students.studentid
and sectionid = '66419';

select room from sections;

select * from sections
where room = 'O 129';

select * from sections
where room != 'O 129';

select * from students
where gpa < 3 and gpa >= 2.5;

select * from students
where gpa between 2.5 and 3;

select * from sections
where room is null;

select dept, course, title, credits 
from catalogcourses
where title like '%lang%';

select * from enrollments
where sectionid = '66415' 
or sectionid = '66419';

select * from enrollments
where sectionid in ( '66415','66419');

select rooms.*, 
capacity / numbstudentworkstations
as ratio
from rooms
where numbstudentworkstations > 0;

select avg(gpa) from students;

select max(gpa) from students;

select min(gpa) from students;

select count(*) from students;

select count(*) from students 
where major = 'CSC';

select count(distinct major)
from students;

select major, avg(gpa)
from students;

select major, avg(gpa)
from students
group by major;

select time, sum(stoppoint) from 
sections
group by time;

select time, 
sum(stoppoint) as total from 
sections
group by time
order by total desc;

select major, avg(gpa), 
max(gpa), min(gpa)
from students
group by major;

select time, 
sum(stoppoint) as total from 
sections
where stoppoint > 100
group by time
order by total desc;

select time, 
sum(stoppoint) as total from 
sections
group by time
having sum(stoppoint) > 100
order by total desc;

select time, count(*) as numsect,
sum(stoppoint) as total from 
sections
group by time
having count(*) > 2
order by total desc;

select avg(gpa) from students;

select studentid, last, first, gpa
from students
where gpa > 
(select avg(gpa) from students)
order by gpa desc;

select studentid from students
where year = 'Fr';

select distinct sectionid 
from enrollments
where studentid in 
(select studentid from students
where year = 'Fr');

select distinct dept, course 
from sections,enrollments
where enrollments.sectionid 
= sections.sectionid
and studentid in 
(select studentid from students
where year = 'Fr');