/**************************************************************
  BASIC ONE-TABLE QUERY
  IDs and names of students with GPA > 3.5
**************************************************************/

select ID, name
from Student
where GPA > 3.5;

/**************************************************************
  BASIC MULTI-TABLE QUERY
  Names and GPAs of students with sizeHS < 1000 applying to
  CS at Berkeley
**************************************************************/

select name, GPA
from Student, Apply
where sizeHS < 1000 and major = 'CS' and location = 'Berkeley'
  and Student.ID = Apply.ID;

/**************************************************************
  BASIC MULTI-TABLE QUERY
  Locations and ranks of all large campuses with CS applicants
  [[ Error then fix ]]
**************************************************************/


select location, rank
from Campus, Apply
where Campus.location = Apply.location
  and enrollment > 30000 and major = 'CS';



/**************************************************************
  REMOVING DUPLICATES
  [[ Do same query without duplicates ]]
  Note difference with relational algebra
**************************************************************/




/**************************************************************
  SORTED RESULT
  Application information sorted by campus rank, then by
  descending GPA
  [[ First unsorted, then add "order by" ]]
**************************************************************/

select Student.ID, name, GPA, Apply.location, rank
from Student, Apply, Campus
where Student.ID = Apply.ID and Apply.location = Campus.location;




/**************************************************************
  LIKE PREDICATE
  Applicants to bio majors
**************************************************************/

select ID, major
from Apply
where major like '%bio%';

/**************************************************************
  SELECT *
  [[ Do same query with "select *" ]]
  [[ Also do "select * from Student, Campus;" ]]
**************************************************************/





/**************************************************************
  SELECT WITH ARITHMETIC
  MISSING WHERE CLAUSE
  Compute scaled GPA based on sizeHS
**************************************************************/

select ID, name, GPA, GPA * (sizeHS/1000), sizeHS
from Student;

/**************************************************************
  RESULT ATTRIBUTE RENAMING
  [[ Add "as scaledGPA" ]]
**************************************************************/



/**************************************************************
  TABLE VARIABLES, SELF-JOIN
  Pairs of students with same GPA
**************************************************************/

select  S1.ID, S1.name, S2.ID, S2.name
from  Student S1, Student S2
where S1.GPA = S2.GPA;

/**************************************************************
  [[ How to get rid of self-pairings? Reverse-pairings? ]]
**************************************************************/





/**************************************************************
  UNION
  [[ Also show "union all" ]]
  [[ Also show "order by" ]]
**************************************************************/

select name from Student
union
select major from Apply;






/**************************************************************
  INTERSECT
  IDs of students who applied to both CS and EE
**************************************************************/

select ID from Apply where major = 'CS'
intersect
select ID from Apply where major = 'EE';

/**************************************************************
  [[ MySQL doesn't support "intersect"; alternative? ]]
**************************************************************/






/**************************************************************
  EXCEPT (difference)
  IDs of students who applied to CS but not EE
**************************************************************/

select ID from Apply where major = 'CS'
except
select ID from Apply where major = 'EE';

/**************************************************************
  [[ MySQL doesn't support difference; alternative? ]]
**************************************************************/





/**************************************************************
  DIGRESSION
  [[ How does what we've covered so far compare with
     relational algebra? ]]
**************************************************************/





/**************************************************************
  SUBQUERY WITH IN
  IDs and names of students applying to CS
**************************************************************/

select ID, name
from Student
where ID in (select ID from Apply where major = 'CS');

/**************************************************************
  [[ Can we write it without the subquery? ]]
  [[ What about duplicates? ]]
  [[ Does adding "distinct" always work? (select just name)]]
**************************************************************/








/**************************************************************
  DIGRESSION
  Why do we care so much about duplicates?
  [[ Select just GPA in previous examples ]]
**************************************************************/




/**************************************************************
  SUBQUERY WITH NOT IN
  IDs and names of students who applied to CS but not EE
  [[ Also change to "not ID in" ]]
**************************************************************/

select ID, name
from Student
where ID in (select ID from Apply where major = 'CS')
  and ID not in (select ID from Apply where major = 'EE');



/**************************************************************
  SUBQUERY WITH EXISTS
  Students such that some other student has the same GPA
  and sizeHS
  [[ Error then fix ]]
**************************************************************/

select ID, name, GPA, sizeHS
from Student S1
where exists (
         select * from Student S2
         where S1.GPA = S2.GPA and S1.sizeHS = s2.sizeHS );




/**************************************************************
  MULTI-ATTRIBUTE IN
  [[ Do same query with multi-attribute "in" ]]
**************************************************************/






/**************************************************************
  SUBQUERY WITH NOT EXISTS
  Location and rank of the biggest campus
**************************************************************/

select location, rank
from Campus C1
where not exists (
         select * from Campus C2
         where C1.enrollment < C2.enrollment );

/**************************************************************
  [[ Can we write it without a subquery? ]]
**************************************************************/



/**************************************************************
  SUBQUERY WITH ALL
  Same query using "> all"
  [[ Error then fix ]]
**************************************************************/

select location, rank
from Campus
where enrollment > all (select enrollment from Campus);



/**************************************************************
  SUBQUERY WITH ANY
  Same query using "not < any"
**************************************************************/

select location, rank
from Campus
where not enrollment < any (select enrollment from Campus);

/**************************************************************
  SUBQUERY WITH ANY
  Students not from the smallest HS
**************************************************************/

select ID, name, sizeHS
from Student
where sizeHS > any (select sizeHS from Student);

/**************************************************************
  [[ Do same query using "exists" ]]
**************************************************************/






/**************************************************************
  SUBQUERY IN FROM
  Students whose scaled GPA increases GPA by more than 1
  [[ First remind of scaledGPA query ]]
  [[ Remove table alias to show error ]]
**************************************************************/

select ID, name, GPA, GPA * (sizeHS/1000) as scaledGPA
from Student;

select ID, name, GPA, scaledGPA
from ( select ID, name, GPA, GPA * (sizeHS/1000) as scaledGPA
       from Student ) S
where scaledGPA - GPA > 1;

/**************************************************************
  BASIC AGGREGATION (AVG)
  Average GPA of all students
**************************************************************/

select avg(GPA)
from Student;

/**************************************************************
  AGGREGATION OVER JOIN
  Average GPA of students applying to CS
**************************************************************/

select avg(GPA)
from Student, Apply
where Student.ID = Apply.ID and major = 'CS';

/**************************************************************
  [[ Is above query correct? ]]
**************************************************************/




/**************************************************************
  AGGREGATION SUBQUERIES IN FROM
  Amount by which average GPA of students applying to CS
  exceeds average of students not applying to CS
**************************************************************/

select CS.avgGPA - NonCS.AvgGPA
from ( select avg(GPA) as avgGPA from Student
       where ID in (
          select ID from Apply where major = 'CS' )) as CS,
     ( select avg(GPA) as avgGPA from Student
       where ID not in (
          select ID from Apply where major = 'CS' )) as nonCS;

/**************************************************************
  BASIC AGGREGATION (COUNT)
  Number of campuses bigger than 30,000
**************************************************************/

select count(*)
from Campus
where enrollment > 30000;

/**************************************************************
  COUNT DISTINCT
  Number of students applying to Santa Cruz
  [[ Incorrect result; how to fix? ]]
**************************************************************/


select count(*)
from Apply
where location = 'Santa Cruz';



/**************************************************************
  COUNT IN SUBQUERY SINGLE-VALUE COMPARISON
  Students such that number of other students with same GPA is
  equal to number of other students with same sizeHS
**************************************************************/

select *
from Student S1
where (select count(*) from Student S2
       where S2.ID <> S1.ID and S2.GPA = S1.GPA) =
      (select count(*) from Student S2
       where S2.ID <> S1.ID and S2.sizeHS = S1.sizeHS);

/**************************************************************
  GROUP BY
  Number of applications to each campus
**************************************************************/

select location, count(*)
from Apply
group by location;

/**************************************************************
  MULTIPLE AGGREGATIONS
  Include minimum and maximum applicant GPA for each
  campus and major
**************************************************************/

select location, major, min(GPA), max(GPA)
from Student, Apply
where Student.ID = Apply.ID
group by location, major;

/**************************************************************
  GROUP BY AND COUNT DISTINCT
  Number of campuses applied to by each student
  [[ Discuss 'name' in select list ]]
**************************************************************/


select Student.ID, name, count(distinct location)
from Student, Apply
where Student.ID = Apply.ID
group by Student.ID;



/**************************************************************
  DOCUMENTED FLAW
  Add location, major to select list
  [[ Show same query without group by and count ]]
**************************************************************/

select Student.ID, name, location, major, count(distinct location)
from Student, Apply
where Student.ID = Apply.ID
group by Student.ID;




/**************************************************************
  [[ Do original query but include students who applied to no
     campuses ]]
**************************************************************/






/**************************************************************
  AGGREGATION SUBQUERY IN SELECT
  Campus ranks versus average GPA of applicants
**************************************************************/

select rank,
       (select avg(GPA) from Student
        where ID in (select ID from Apply
                     where location = Campus.location)) as avgGPA
from Campus;

/**************************************************************
  HAVING
  Campuses with fewer than 4 applications
**************************************************************/

select location
from Apply
group by location
having count(*) < 4;

/**************************************************************
  [[ Do same query without "group by" or "having" ]]
**************************************************************/






/**************************************************************
  [[ Modify for fewer than 4 appplicants, not applications ]]
**************************************************************/






/**************************************************************
  NULL VALUES, INSERT
  Add some students with null sizeHS
**************************************************************/

insert into Student values ('432', 'Kevin', 3.2, null);
insert into Student values ('321', 'Lori', 3.8, null);

/**************************************************************
  QUERIES OVER NULL VALUES
  All students from small HS, then from large HS
**************************************************************/

select ID, name
from Student
where sizeHS < 1000;

select ID, name
from Student
where not sizeHS < 1000;

select ID, name
from Student
where sizeHS < 1000 or not sizeHS < 1000;

select ID, name from Student;

/**************************************************************
  [[ Add "or GPA < 3.3" ]]
  [[ Add "or GPA < 3.3 or not GPA < 3.3" ]]
  [[ Add "or sizeHS is null" ]]
**************************************************************/






/**************************************************************
  INSERT WITH SUBQUERY
  Make all students who didn't apply anywhere apply to
  basket-weaving major at Santa Cruz
**************************************************************/

insert into Apply
  select ID, 'Santa Cruz', 'basket-weaving', null
  from Student
  where ID not in (select ID from Apply);

/**************************************************************
  DELETE
  Delete all students who applied to more than two different
  majors
  [[ Start with query below; add "having" then "delete" ]]
**************************************************************/

select ID, count(distinct major)
from Apply
group by ID;






/**************************************************************
  UNNECESSARY RESTRICTION
  [[ Try same query but delete from Apply instead ]]
**************************************************************/






/**************************************************************
  UPDATE
  Accept applicants to Los Angeles with GPA > 3.6 and turn
  them into urban-studies majors
**************************************************************/

update Apply
set decision = 'Y', major = 'urban-studies'
where location = 'Los Angeles'
  and ID in (select ID from student where GPA > 3.6);