mysql> use student1;

Database changed
mysql> CREATE TABLE Student ( snum INTEGER PRIMARY KEY, sname VARCHAR(100)
NOT NULL, major VARCHAR(50), level VARCHAR(2) CHECK (level IN ('FR', 'SO',
'JR', 'SR')), age INTEGER CHECK (age > 0));
Query OK, 0 rows affected (0.10 sec)

mysql> INSERT INTO Student VALUES(1, 'annapurna', 'CS', 'FR', 18),(2, 'ramesh',
'Math', 'SO', 19),(3, 'bhavana', 'CS', 'JR', 20),(4, 'Deva', 'Physics', 'SR', 21),
(5, 'umesh', 'Math', 'FR', 18);
Query OK, 5 rows affected (0.07 sec)
Records: 5 Duplicates: 0 Warnings: 0

mysql> select *from student;

+------+-----------+---------+-------+------+
| snum | sname | major | level | age |
+------+-----------+---------+-------+------+
| 1 | annapurna | CS | FR | 18 |
| 2 | ramesh | Math | SO | 19 |
| 3 | bhavana | CS | JR | 20 |
| 4 | Deva | Physics | SR | 21 |
| 5 | umesh | Math | FR | 18 |
+------+-----------+---------+-------+------+
5 rows in set (0.00 sec)

mysql> CREATE TABLE Faculty ( fid INTEGER PRIMARY KEY, fname VARCHAR(100) NOT
NULL, deptid INTEGER);
Query OK, 0 rows affected (0.09 sec)

mysql> INSERT INTO Faculty (fid, fname, deptid) VALUES(1, 'Dr. Smith', 101),(2,
'Dr. Jones', 102);
Query OK, 2 rows affected (0.07 sec)
Records: 2 Duplicates: 0 Warnings: 0

mysql> select *from faculty;

+-----+-----------+--------+
| fid | fname | deptid |
+-----+-----------+--------+
| 1 | Dr. Smith | 101 |
| 2 | Dr. Jones | 102 |
+-----+-----------+--------+
2 rows in set (0.00 sec)

mysql> CREATE TABLE Class ( name VARCHAR(100) PRIMARY KEY, meets_at

VARCHAR(50), room VARCHAR(10), fid INTEGER, FOREIGN KEY (fid) REFERENCES
Faculty(fid) ON DELETE NO ACTION);
Query OK, 0 rows affected (0.13 sec)

mysql> INSERT INTO Class (name, meets_at, room, fid) VALUES('CS101', 'MWF 10-11',
'R128', 1),('Math200', 'TTh 1-2:30', 'R128', 2),('Physics300', 'MWF 2-3', 'R128',
1),('CS201', 'TTh 3-4:30', 'R128', 1);
Query OK, 4 rows affected (0.07 sec)
Records: 4 Duplicates: 0 Warnings: 0

mysql> selct *from class;

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use near 'selct
*from class' at line 1
mysql> selct *from Class;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use near 'selct
*from Class' at line 1
mysql> select *from Class;
+------------+------------+------+------+
| name | meets_at | room | fid |
+------------+------------+------+------+
| CS101 | MWF 10-11 | R128 | 1 |
| CS201 | TTh 3-4:30 | R128 | 1 |
| Math200 | TTh 1-2:30 | R128 | 2 |
| Physics300 | MWF 2-3 | R128 | 1 |
+------------+------------+------+------+
4 rows in set (0.00 sec)

mysql> CREATE TABLE Enrolled ( snum INTEGER, cname VARCHAR(100), PRIMARY

KEY (snum, cname), FOREIGN KEY (snum) REFERENCES Student(snum) ON DELETE NO
ACTION, FOREIGN KEY (cname) REFERENCES Class(name) ON DELETE NO ACTION);
Query OK, 0 rows affected (0.13 sec)

mysql> INSERT INTO Enrolled (snum, cname) VALUES(1, 'CS101'),(2, 'Math200'),(3,

'CS101'),(3, 'Physics300'),(4, 'CS201');
Query OK, 5 rows affected (0.07 sec)
Records: 5 Duplicates: 0 Warnings: 0

mysql> select *from enrolled;

+------+------------+
| snum | cname |
+------+------------+
| 1 | CS101 |
| 3 | CS101 |
| 4 | CS201 |
| 2 | Math200 |
| 3 | Physics300 |
+------+------------+
5 rows in set (0.00 sec)

mysql> CREATE VIEW AvgAgePerLevel AS

-> SELECT level, AVG(age) AS average_age
-> FROM Student
-> GROUP BY level;
Query OK, 0 rows affected (0.07 sec)

mysql> SELECT level, AVG(age) AS average_age

-> FROM Student
-> GROUP BY level;
+-------+-------------+
| level | average_age |
+-------+-------------+
| FR | 18.0000 |
| SO | 19.0000 |
| JR | 20.0000 |
| SR | 21.0000 |
+-------+-------------+
4 rows in set (0.00 sec)

mysql> SELECT level, AVG(age) AS average_age

-> FROM Student
-> WHERE level <> 'JR'
 -> GROUP BY level;
+-------+-------------+
| level | average_age |
+-------+-------------+
| FR | 18.0000 |
| SO | 19.0000 |
| SR | 21.0000 |
+-------+-------------+
3 rows in set (0.00 sec)

mysql> SELECT f.fname, COUNT(c.name) AS total_classes

-> FROM Faculty f
-> JOIN Class c ON f.fid = c.fid
-> WHERE c.room = 'R128'
-> AND f.fid NOT IN (
-> SELECT fid
-> FROM Class
-> WHERE room <> 'R128'
-> )
-> GROUP BY f.fname;
+-----------+---------------+
| fname | total_classes |
+-----------+---------------+
| Dr. Smith | 3 |
| Dr. Jones | 1 |
+-----------+---------------+
2 rows in set (0.07 sec)

mysql> SELECT s.sname

-> FROM Student s
-> JOIN Enrolled e ON s.snum = e.snum
-> GROUP BY s.sname
-> HAVING COUNT(e.cname) = (
-> SELECT MAX(count)
-> FROM (
-> SELECT COUNT(e1.cname) AS count
-> FROM Enrolled e1
-> GROUP BY e1.snum
-> ) AS max_count
-> );
+---------+
| sname |
+---------+
| bhavana |
+---------+
1 row in set (0.06 sec)

mysql> SELECT s.sname

-> FROM Student s
-> LEFT JOIN Enrolled e ON s.snum = e.snum
-> WHERE e.snum IS NULL;
+-------+
| sname |
+-------+
| umesh |
+-------+
1 row in set (0.00 sec)
mysql> SELECT T1.age, T1.level
-> FROM Student T1
 -> JOIN (
-> SELECT age, level, COUNT(*) AS cnt
-> FROM Student
-> GROUP BY age, level
-> ) T2 ON T1.age = T2.age AND T1.level = T2.level
-> JOIN (
-> SELECT age, MAX(cnt) AS max_cnt
-> FROM (
-> SELECT age, level, COUNT(*) AS cnt
-> FROM Student
-> GROUP BY age, level
-> ) AS subquery
-> GROUP BY age
-> ) T3 ON T2.age = T3.age AND T2.cnt = T3.max_cnt;
+------+-------+
| age | level |
+------+-------+
| 18 | FR |
| 19 | SO |
| 20 | JR |
| 21 | SR |
| 18 | FR |
+------+-------+
5 rows in set (0.00 sec)
mysql> SELECT * FROM AvgAgePerLevel;
+-------+-------------+
| level | average_age |
+-------+-------------+
| FR | 18.0000 |
| SO | 19.0000 |
| JR | 20.0000 |
| SR | 21.0000 |
+-------+-------------+
4 rows in set (0.00 sec)

mysql>