Rem see all rows and all columns
select * from students;
Rem simple project
select studentid, last, first from students;
Rem simple project but not consistent with Relational Algebra gives duplicate rows in results
select major from students;
Rem simple project avoid duplicate rows
select distinct major from students;
Rem simple restrict see all info about freshman students
select * from students where year = 'Fr';
Rem slightly more complicated restrict
select * from students where year = 'Fr' and gpa < 2.5;
Rem project and restrict combined into one command
select studentid, last, first, gpa from students where year = 'Fr' and gpa < 2.5;
Rem project sorted
select studentid, last, first, major from students order by major;
Rem project sorted descending ( Z A for character data)
select studentid, last, first, major from students order by major desc;
Rem project sorted by major with ties broken by last name
select studentid, last, first, major from students order by major desc, last;
Rem intentional error incorrect join studentid is ambiguous
select studentid, last, first, sectionid from students, enrollments;
Rem intentional error incorrect join gives Cartesian Product
select students.studentid, last, first, sectionid from students, enrollments;
Rem fixed - adding join condition - FK = PK
select students.studentid, last, first, sectionid from students, enrollments where students.studentid = enrollments.studentid;
Rem demonstrate that the FK = PK
select students.studentid, enrollments.studentid, last, first, sectionid from students, enrollments where students.studentid = enrollments.studentid;
Rem joining more than two tables - need condition - FK = PK for each join (typically #tables 1 join conditions)
select students.studentid, last, first, dept, course
from students, enrollments, sections
where enrollments.studentid = students.studentid and
enrollments.sectionid = sections.sectionid;
REM list for particular section
select students.studentid, last, first, dept, course
from students, enrollments, sections
where enrollments.studentid = students.studentid and
enrollments.sectionid = sections.sectionid
and sections.sectionid = 66419;
REM can use not equal
Select time, room, stoppoint from sections
Where room <> O 129;
REM show that = and <> add up to all sections
Select time, room, stoppoint from sections
Where room = O 129;
REM In Oracle not equal can use C-like syntax along with traditional Pascal-like syntax above
Select time, room, stoppoint from sections
Where room != O 129;
REM can use between for ranges
Select * from students
Where gpa between 2.0 and 2.2;
REM can use between for ranges
Select * from students
Where gpa between 2.0 and 2.4;
REM traditional CSC ranges
Select * from students
Where gpa >= 2.0 and gpa <= 2.4;
REM error what people new to CSC frequently think should work for ranges
Select * from students
Where 2.4 >= gpa >= 2.0;
REM Can use wildcards but must use like instead of =
Select * from catalogcourses
Where title like %Lang%;
REM comes up empty must use like instead of =
Select * from catalogcourses
Where title = %Lang%;
REM comes up empty inside quotes case matters (it doesnt matter anywhere else when using SQL)
Select * from catalogcourses
Where title like %lang%;
REM set operation is it a member of the set? (any number of members, separated by commas)
Select * from enrollments where sectionid IN (66415, 66419);
REM can do calculations but this is an error because some rows end up dividing by zero (no student work stations)
Select rooms.*, capacity / numbstudentworkstations as ratio
From rooms;
REM calculation protected from dividing by zero (if no student work stations, wont be in the result)
Select rooms.*, capacity / numbstudentworkstations as ratio
From rooms
Where numbstudentworkstations > 0;
REM Start of third class aggregate functions provided
Select avg(gpa)
From students;
REM more aggregate functions provided
Select max(gpa)
From students;
REM more aggregate functions provided
Select min(gpa)
From students;
REM more aggregate functions provided count counts rows, so doesnt need attribute name
Select count(*)
From students;
REM but can count unique values if provide distinct and attribute name
Select count(distinct major)
From students;
REM without distinct it just counts values which is (almost the same as counting rows (nulls wouldnt be counted))
Select count(major)
From students;
REM can restrict rows before aggregating
Select avg(gpa)
From students
Where major = CSC;
REM just testing see what their gpas are
Select last, gpa
From students
Where major = CSC;
REM intentional error trying to get sub-totals no group
Select major, avg(gpa)
From students;
REM sub-totals group
Select major, avg(gpa)
From students
Group by major;
REM can see more than one sub-total
Select major, avg(gpa), min(gpa), max(gpa)
From students
Group by major;
REM sub-totals group by year major combination
Select year, major, avg(gpa)
From students
Group by year, major;
REM sub-totals sorted
Select year, major, avg(gpa)
From students
Group by year, major
Order by major, year;
REM intentional error group by must come before order by
Select year, major, avg(gpa)
From students
Order by major, year
Group by year, major;
REM where restricts rows before grouping end up with fewer groups in this case
Select major, avg(gpa)
From students
Where year = Sr
Group by major;
REM intentional error Where must come before group by
Select major, avg(gpa)
From students
Group by major
Where year = Sr;
REM just testing I wanted to know how many students there were in each major so I could choose an interesting cut-off for the next query
Select major, count(*)
From students
Group by major;
REM sub-totals with GROUPS limited
Select major, avg(gpa)
From students
Group by major
Having count(*) > 3;
REM intentional error where concerns individual rows so cant use group function with it
Select major, avg(gpa)
From students
where count(*) > 3
Group by major;
REM having can use any test involving groups here were just seeing ave gpas for majors with high ave gpas
Select major, avg(gpa)
From students
Group by major
Having avg(gpa) > 3.0;
REM intentional error Having concerns groups so MUST use test involving groups NOT individual rows
Select major, avg(gpa)
From students
Group by major
Having gpa > 3.0;
REM test query for use inside another query
Select avg(gpa) from students;
REM nested queries finds students with above average GPAs
Select * from students
Where gpa >
(Select avg(gpa) from students)
Order by gpa desc;
REM test query for use inside another query
Select studentid from students where year = Fr;
REM nested queries finds section ids for sections with freshmen enrolled
Select distinct sectionid from enrollments
Where studentid IN (
Select studentid from students where year = Fr )
Order by sectionid;