.mode column
.header on
.width 30 30 40
-------SELECT examples
select name from course;
select distinct name from course;
select (fname ||' ' || lname) fullname from student;

select (fname ||' ' || lname) 'student name', 
CASE country WHEN 'USA' THEN 'domestic'
ELSE 'international' END category
FROM student; 

select (fname ||' ' || lname) 'student name', 
CASE  WHEN country='USA' OR country='Canada' OR country='England' THEN 'English'
WHEN country='France' THEN 'French'
ELSE 'Unknown' END 'Native language'
FROM student;

-------FROM examples
select (s.fname ||' ' || s.lname) name1, 
(p.fname || ' ' || p.lname) name2 from student s, professor p;

select name as department, (fname ||' ' || lname) head
FROM department d, professor p
WHERE d.head_id = p.id;

select name as course, (fname ||' ' || lname) as student
FROM course c, student s, takes t
WHERE t.course_id = c.id AND t.student_id = s.id;

select 
	(fname ||' ' || lname) as student,
	name as course,	
	CASE 
	WHEN grade>= 80 THEN 'A'
	WHEN grade>= 65 THEN 'B'
	WHEN grade>= 50 THEN 'C'
	ELSE 'Sorry, failed'
	END lettergrade
FROM course c, student s, takes t
WHERE t.course_id = c.id AND t.student_id = s.id
ORDER BY student;

SELECT p.lname as professor, 'supervised by', s.lname as supervisor
FROM professor p, professor s, mentors m
WHERE m.mentee_id = p.id AND m.mentor_id = s.id;

-----WHERE examples
SELECT s.lname as student, grade 
FROM student s, course c, takes t 
WHERE s.id = t.student_id AND c.id = t.course_id
AND c.name LIKE 'Algo%';

SELECT s.lname as student, c.name AS course, grade 
FROM student s, course c, takes t 
WHERE s.id = t.student_id AND c.id = t.course_id
AND c.name IN ('Algorithms', 'Databases', 'Python');

SELECT DISTINCT(s.fname) as student
FROM student s, course c, takes t 
WHERE s.id = t.student_id AND c.id = t.course_id
AND c.name LIKE 'Algo%' OR c.name LIKE 'Data%';

SELECT DISTINCT(s.fname) as student
FROM student s, course c, takes t 
WHERE s.id = t.student_id AND c.id = t.course_id
AND c.name LIKE 'Algo%'
INTERSECT
SELECT DISTINCT(s.fname) as student
FROM student s, course c, takes t 
WHERE s.id = t.student_id AND c.id = t.course_id
AND c.name LIKE 'D%';

SELECT lname as student, c.name as course
FROM student s, course c, takes t
WHERE t.student_id =s.id AND t.course_id = c.id
AND t.grade IS NULL;

----SUBQUERY in FROM example
SELECT name FROM
(	SELECT (s.fname ||' ' || s.lname) as name 
	FROM student s 
	WHERE s.country='USA') 'domestic students';
	
----SUBQUERY in WHERE examples
SELECT s.lname as student, t.grade as grade
FROM student s, takes t 
WHERE t.student_id = s.id
AND t.grade > 
(	SELECT grade FROM student s1, takes t1, course c1
	WHERE s1.id = t1.student_id AND t1.course_id = c1.id
	AND s1.lname = 'Lopez' AND c1.name='Python'); 

---should be >= ALL in all other DBMSs	
SELECT s.lname as student, t.grade as grade
FROM student s, takes t 
WHERE t.student_id = s.id
AND t.grade > 
(	SELECT grade FROM student s1, takes t1, course c1
	WHERE s1.id = t1.student_id AND t1.course_id = c1.id
	AND s1.lname = 'Lopez'); 
	
SELECT s.lname as student, t.grade as grade
FROM student s, takes t 
WHERE t.student_id = s.id
AND t.grade = 
(	SELECT grade FROM student s1, takes t1, course c1
	WHERE s1.id = t1.student_id AND t1.course_id = c1.id
	AND s1.lname = 'Lennon' AND c1.name = 'Databases'); 
	
SELECT s.lname as student, t.grade as grade
FROM student s, takes t 
WHERE t.student_id = s.id
AND t.grade IN
(	SELECT grade FROM student s1, takes t1, course c1
	WHERE s1.id = t1.student_id AND t1.course_id = c1.id
	AND s1.lname = 'Lennon');
	
---correlated subqueries
SELECT DISTINCT lname 
FROM student souter
WHERE EXISTS 
(	SELECT 1 FROM 
	student sinner, takes t
	WHERE t.student_id = sinner.id
	AND souter.id = sinner.id);
	
SELECT DISTINCT lname 
FROM student souter
WHERE NOT EXISTS 
(	SELECT 1 FROM 
	student sinner, takes t
	WHERE t.student_id = sinner.id
	AND souter.id = sinner.id);
	
---GROUPING and AGGREGATION examples
SELECT c.name AS course, AVG(grade)
FROM course c, takes t
WHERE t.course_id = c.id
GROUP BY c.name;

SELECT c.name AS course, AVG(grade)
FROM course c, takes t
WHERE t.course_id = c.id
GROUP BY c.name
HAVING count(c.name)>2;

SELECT 'Marks distribution' AS ' ' , 
SUM (CASE WHEN grade BETWEEN 0 AND 50 THEN 1 ELSE 0 END) AS 'FAILS', 
SUM (CASE WHEN grade BETWEEN 51 AND 65 THEN 1 ELSE 0 END) AS 'C', 
SUM (CASE WHEN grade BETWEEN 66 AND 80 THEN 1 ELSE 0 END) AS 'B', 
SUM (CASE WHEN grade >80 THEN 1 ELSE 0 END) AS 'A'
FROM student s, takes t
WHERE s.id = t.student_id;




