-- 9/30

tee 2024_9_30_sql_log.txt







-- 9/25

tee 2024_9_25_sql_log.txt

use toyu;

SELECT stuId
FROM enroll;

SELECT DISTINCT stuId
FROM enroll;

SELECT DISTINCT s.stuId, s.fname
FROM student AS s
WHERE s.lname = 'Hawk';

SELECT DISTINCT s.stuId, s.fname
WHERE s.lname = 'Hawk'
FROM student AS s; -- error


DROP SCHEMA IF EXISTS tinker;
CREATE SCHEMA tinker;
USE tinker;

CREATE TABLE s2 -- creation and population
SELECT * FROM toyu.student;

SELECT *
FROM s2;

CREATE TEMPORARY TABLE s3
SELECT * FROM toyu.student;

SELECT *
FROM s3;

CREATE TABLE s4 LIKE toyu.student; -- creation

SELECT *
FROM s4;

INSERT INTO s4           -- population
SELECT * FROM toyu.student;

SELECT *
FROM s4;

SHOW TABLES;

-- Note that keys and constraints of student are missing in s2 and S3.
DESC toyu.student;
DESC s2;
DESC s4;
DESC s3;
DESC s4;

DROP TABLE s2;
DROP TABLE s3;
DROP TABLE s4;

SHOW TABLES;

DROP SCHEMA IF EXISTS tinker;

UPDATE Student
SET major = 'ITEC'
WHERE StuId = 100000;

UPDATE Student
SET major = 'CSCI';


-- operators:
-- student with credits in a range.
SELECT DISTINCT *
FROM Student
WHERE ach BETWEEN 30 AND 70;

SELECT DISTINCT *
FROM Student
WHERE ach >=30 AND ach <=70;


SELECT DISTINCT S.StuId, IF(s.ach <= 60, 'lower', 'upper') AS level
FROM student AS s;


SELECT DISTINCT s.fname, s.lname, c.classId, e.grade
FROM student AS s, enroll AS e, class AS c
WHERE s.stuId = e.stuId -- Join condition
AND e.classId = c.classId -- Join condition
AND c.semester = 'Fall' -- problem condition
AND c.year = 2019; -- problem condition

SELECT DISTINCT s.fname, s.lname, c.classId, e.grade
FROM student AS s INNER JOIN enroll e ON (s.stuId = e.stuId) -- Join condition
    INNER JOIN class AS c ON (e.classId = c.classId) -- Join condition
WHERE c.semester = 'Fall' -- Problem condition
AND c.year = 2019; -- Problem condition


-- student in selected majors
SELECT DISTINCT *
FROM Student
WHERE major IN ('CSCI', 'CINF', 'ITEC');

-- List the names of the students with their minors (in full name).
-- Student with no department not listed.
SELECT DISTINCT CONCAT(s.fname, ' ', s.lname) AS student,
    d.deptName AS `minor department`
FROM student AS s INNER JOIN department AS d ON (s.minor = d.deptCode);

-- List the names of the students with their minors (in full name).
SELECT DISTINCT CONCAT(s.fname, ' ', s.lname) AS student,
    d.deptName AS `minor department`
FROM student AS s LEFT JOIN department AS d ON (s.minor = d.deptCode);

-- subqueries in the WHERE course
-- students not enrolled in any class.
SELECT DISTINCT *
FROM student AS s
WHERE s.stuId NOT IN (SELECT DISTINCT e.stuID FROM enroll AS e);

-- student with the maximum number of ach.
SELECT DISTINCT MAX(ach)
FROM student;

-- student within 60 credits of the maximum number of ach any student may have.
SELECT DISTINCT s.stuId,
   CONCAT(s.fname, ' ', s.lname) AS student,
   s.ach AS credits
FROM student AS s
WHERE s.ach + 60 >=
   (SELECT DISTINCT MAX(ach) FROM student);

-- subqueries as derived tables.
SELECT DISTINCT s.stuId,
   CONCAT(s.fname, ' ', s.lname) AS student,
   s.ach AS credits
FROM student AS s INNER JOIN
   (SELECT DISTINCT MAX(ach) AS max FROM student) AS m -- an alias is required.
WHERE s.ach + 60 >= m.max;