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 doesn’t 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, won’t 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 doesn’t 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 wouldn’t 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 can’t 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 we’re 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;