tee 2025_10_15_sql_log.txt

-- Student names and number of classes enrolled.
-- More than 2 classes to be included in the result.
SELECT CONCAT(s.fname, ' ', s.lname) AS student,
   COUNT(e.classId) AS `Enrolled classes`
FROM student AS s INNER JOIN enroll e ON (s.stuId = e.stuId)
GROUP BY student
HAVING `Enrolled classes` > 2
ORDER BY `Enrolled classes` DESC; 


SELECT *
FROM student AS s INNER JOIN enroll e ON (s.stuId = e.stuId);

SELECT *
FROM student AS s INNER JOIN enroll e ON (s.stuId = e.stuId)
WHERE s.major IN ('CSCI', 'CINF');

SELECT CONCAT(s.fname, ' ', s.lname) AS student, 
FROM student AS s INNER JOIN enroll e ON (s.stuId = e.stuId)
WHERE s.major IN ('CSCI', 'CINF')
GROUP BY student;

SELECT CONCAT(s.fname, ' ', s.lname) AS student, s.*, e.*
FROM student AS s INNER JOIN enroll e ON (s.stuId = e.stuId)
WHERE s.major IN ('CSCI', 'CINF')
GROUP BY student
HAVING `Enrolled classes` > 2
ORDER BY `Enrolled classes` DESC; 


SELECT CONCAT(s.fname, ' ', s.lname) AS student,
   COUNT(e.classId) AS `Enrolled classes`
FROM student AS s INNER JOIN enroll e ON (s.stuId = e.stuId)
GROUP BY student;


SELECT CONCAT(s.fname, ' ', s.lname) AS student,
   COUNT(e.classId) AS `Enrolled classes`
FROM student AS s INNER JOIN enroll e ON (s.stuId = e.stuId)
WHERE s.ach > 20
GROUP BY student
HAVING `Enrolled classes` > 2
ORDER BY `Enrolled classes` DESC; 

SELECT CONCAT(s.fname, ' ', s.lname) AS student,
   COUNT(e.classId) AS `Enrolled classes`
FROM student AS s INNER JOIN enroll e ON (s.stuId = e.stuId)
WHERE s.ach > 20
AND `Enrolled classes` > 2  -- no group yet
GROUP BY student
ORDER BY `Enrolled classes` DESC; 

SELECT CONCAT(s.fname, ' ', s.lname) AS student,
   COUNT(e.classId) AS `Enrolled classes`
FROM student AS s INNER JOIN enroll e ON (s.stuId = e.stuId)
WHERE s.ach > 20
AND COUNT(e.classId) > 2   -- ERROR 1111 (HY000): Invalid use of group function
GROUP BY student
ORDER BY `Enrolled classes` DESC; 


SELECT CONCAT(s.fname, ' ', s.lname) AS student,
   COUNT(e.classId) AS `Enrolled classes`,
   'hope'
FROM student AS s INNER JOIN enroll e ON (s.stuId = e.stuId)
WHERE s.ach > 20
GROUP BY student
HAVING `Enrolled classes` > 2
ORDER BY `Enrolled classes` DESC; 

[2] Can you write a query to generate the following output?
+----------+------------------------------+------------+----------+----------+
| deptCode | deptName                     | numFaculty | numMajor | numMinor |
+----------+------------------------------+------------+----------+----------+
| ACCT     | Accounting                   |          1 |        0 |        0 |
| ARTS     | Arts                         |          1 |        2 |        0 |
| CINF     | Computer Information Systems |          2 |        2 |        3 |
| CSCI     | Computer Science             |          4 |        3 |        1 |
| ENGL     | English                      |          1 |        0 |        2 |
| ITEC     | Information Technology       |          2 |        2 |        2 |
| MATH     | Mathematics                  |          0 |        0 |        0 |
+----------+------------------------------+------------+----------+----------+
7 rows in set (0.00 sec)


[1] COlumns 1 and 3
SELECT DISTINCT f.deptCode, COUNT(f.facId) as numFaculty
FROM faculty AS f
GROUP BY f.deptCode;

[2] columns 1 and 4 (major):
SELECT DISTINCT s.major AS deptCode, COUNT(s.stuId) as numMajor
FROM student AS s
GROUP BY s.major;

[3] columns 1 and 5 (minor):
SELECT DISTINCT s.minor AS deptCode, COUNT(s.stuId) as numMinor
FROM student AS s
GROUP BY s.minor;

[4] columns 1 and 2
SELECT d.deptCode, d.deptName
FROM department AS d;

WITH t1 AS
(SELECT DISTINCT f.deptCode, COUNT(f.facId) as numFaculty
FROM faculty AS f
GROUP BY f.deptCode),
t2 AS
(SELECT DISTINCT s.major AS deptCode, COUNT(s.stuId) as numMajor
FROM student AS s
GROUP BY s.major),
t3 AS
(SELECT DISTINCT s.minor AS deptCode, COUNT(s.stuId) as numMinor
FROM student AS s
GROUP BY s.minor)
SELECT d.deptCode, d.deptName, t1.numFaculty, t2.numMajor, t3.numMinor
FROM department AS d 
	INNER JOIN t1 USING (deptCode)
	INNER JOIN t2 USING (deptCode)
	INNER JOIN t3 USING (deptCode);

WITH t1 AS
(SELECT DISTINCT f.deptCode, COUNT(f.facId) as numFaculty
FROM faculty AS f
GROUP BY f.deptCode),
t2 AS
(SELECT DISTINCT s.major AS deptCode, COUNT(s.stuId) as numMajor
FROM student AS s
GROUP BY s.major),
t3 AS
(SELECT DISTINCT s.minor AS deptCode, COUNT(s.stuId) as numMinor
FROM student AS s
GROUP BY s.minor)
SELECT d.deptCode, d.deptName, 
	IFNULL(t1.numFaculty, 0) AS numFaculty, 
	IFNULL(t2.numMajor, 0) AS numMajor, 
	IFNULL(t3.numMinor, 0) AS numMinor
FROM department AS d 
	LEFT JOIN t1 USING (deptCode)
	LEFT JOIN t2 USING (deptCode)
	LEFT JOIN t3 USING (deptCode);