MariaDB [toyu]> show tables; +----------------+ | Tables_in_toyu | +----------------+ | class | | course | | department | | enroll | | faculty | | grade | | school | | student | +----------------+ 8 rows in set (0.001 sec) MariaDB [toyu]> CREATE TEMPORARY TABLE IF NOT EXISTS School_2 ( -> schoolCode VARCHAR(3), -> schoolName VARCHAR(30), -> CONSTRAINT School_schoolCode_pk PRIMARY KEY (schoolCode), -> CONSTRAINT School_name_ck UNIQUE (schoolName) -> ); Query OK, 0 rows affected, 1 warning (0.017 sec) MariaDB [toyu]> show tables; +----------------+ | Tables_in_toyu | +----------------+ | class | | course | | department | | enroll | | faculty | | grade | | school | | student | +----------------+ 8 rows in set (0.001 sec) MariaDB [toyu]> MariaDB [toyu]> SELECT * -> FROM School_2; Empty set (0.000 sec) MariaDB [toyu]> SELECT * FROM School; +------------+-------------------------------+ | schoolCode | schoolName | +------------+-------------------------------+ | BUS | Business | | EDU | Education | | HSH | Human Sciences and Humanities | | CSE | Science and Engineering | +------------+-------------------------------+ 4 rows in set (0.001 sec) MariaDB [toyu]> CREATE TABLE IF NOT EXISTS School_3 -> SELECT * FROM School; Query OK, 4 rows affected (0.026 sec) Records: 4 Duplicates: 0 Warnings: 0 MariaDB [toyu]> show tables; +----------------+ | Tables_in_toyu | +----------------+ | class | | course | | department | | enroll | | faculty | | grade | | school | | school_3 | | student | +----------------+ 9 rows in set (0.001 sec) MariaDB [toyu]> ALTER TABLE School_3 -> ADD COLUMN building VARCHAR(30) AFTER schoolCode; Query OK, 0 rows affected (0.012 sec) Records: 0 Duplicates: 0 Warnings: 0 MariaDB [toyu]> SELECT g.*, 0 AS passing FROM Grade AS g; +-------+------------+---------+ | grade | gradePoint | passing | +-------+------------+---------+ | A | 4.0000 | 0 | | A- | 3.6667 | 0 | | B | 3.0000 | 0 | | B+ | 3.3333 | 0 | | B- | 2.6667 | 0 | | C | 2.0000 | 0 | | C+ | 2.3333 | 0 | | C- | 1.6667 | 0 | | D | 1.0000 | 0 | | D+ | 1.3333 | 0 | | D- | 0.6667 | 0 | | F | 0.0000 | 0 | | WX | NULL | 0 | +-------+------------+---------+ 13 rows in set (0.001 sec) MariaDB [toyu]> MariaDB [toyu]> CREATE TABLE IF NOT EXISTS Grade_2 -> SELECT g.*, 0 AS passing FROM Grade AS g; Query OK, 13 rows affected (0.016 sec) Records: 13 Duplicates: 0 Warnings: 0 MariaDB [toyu]> UPDATE Grade_2 -> SET passing = IF(gradePoint >= 2.5, 1, 0); Query OK, 5 rows affected (0.007 sec) Rows matched: 13 Changed: 5 Warnings: 0 MariaDB [toyu]> UPDATE student -> SET major = 'CINF' -> WHERE major IN ('CSCI', 'ITEC'); Query OK, 5 rows affected (0.009 sec) Rows matched: 5 Changed: 5 Warnings: 0 MariaDB [toyu]> -- MariaDB [toyu]> -- MariaDB [toyu]> -- Create a very simplified university database MariaDB [toyu]> -- MariaDB [toyu]> -- MariaDB [toyu]> -- Drop tables for house-keeping if necessary MariaDB [toyu]> -- MariaDB [toyu]> MariaDB [toyu]> SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0; Query OK, 0 rows affected (0.002 sec) MariaDB [toyu]> SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0; Query OK, 0 rows affected (0.000 sec) MariaDB [toyu]> SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL'; Query OK, 0 rows affected (0.002 sec) MariaDB [toyu]> MariaDB [toyu]> DROP SCHEMA IF EXISTS toyu; Query OK, 10 rows affected (0.075 sec) MariaDB [(none)]> CREATE SCHEMA toyu; Query OK, 1 row affected (0.001 sec) MariaDB [(none)]> USE toyu; Database changed MariaDB [toyu]> MariaDB [toyu]> DROP TABLE IF EXISTS Enroll; Query OK, 0 rows affected, 1 warning (0.000 sec) MariaDB [toyu]> DROP TABLE IF EXISTS Student; Query OK, 0 rows affected, 1 warning (0.001 sec) MariaDB [toyu]> DROP TABLE IF EXISTS Class; Query OK, 0 rows affected, 1 warning (0.000 sec) MariaDB [toyu]> DROP TABLE IF EXISTS Course; Query OK, 0 rows affected, 1 warning (0.000 sec) MariaDB [toyu]> DROP TABLE IF EXISTS Faculty; Query OK, 0 rows affected, 1 warning (0.000 sec) MariaDB [toyu]> DROP TABLE IF EXISTS Department; Query OK, 0 rows affected, 1 warning (0.000 sec) MariaDB [toyu]> DROP TABLE IF EXISTS school; Query OK, 0 rows affected, 1 warning (0.000 sec) MariaDB [toyu]> DROP TABLE IF EXISTS Grade; Query OK, 0 rows affected, 1 warning (0.000 sec) MariaDB [toyu]> MariaDB [toyu]> MariaDB [toyu]> -- MariaDB [toyu]> MariaDB [toyu]> CREATE TABLE IF NOT EXISTS Grade ( -> grade VARCHAR(2), -> gradePoint DECIMAL(5,4), -> CONSTRAINT Grade_grade_pk PRIMARY KEY (grade) -> ); Query OK, 0 rows affected, 1 warning (0.013 sec) MariaDB [toyu]> MariaDB [toyu]> CREATE TABLE IF NOT EXISTS School ( -> schoolCode VARCHAR(3), -> schoolName VARCHAR(30), -> CONSTRAINT School_schoolCode_pk PRIMARY KEY (schoolCode), -> CONSTRAINT School_name_ck UNIQUE (schoolName) -> ); Query OK, 0 rows affected, 1 warning (0.017 sec) MariaDB [toyu]> MariaDB [toyu]> CREATE TABLE IF NOT EXISTS Department ( -> deptCode VARCHAR(4), -> deptName VARCHAR(30), -> schoolCode VARCHAR(3), -> numFaculty TINYINT, -> CONSTRAINT Department_deptCode_pk PRIMARY KEY (deptCode), -> CONSTRAINT Department_name_ck UNIQUE (deptName), -> CONSTRAINT Department_schoolCode_fk FOREIGN KEY (schoolCode) -> REFERENCES School(schoolCode) -> ); Query OK, 0 rows affected, 1 warning (0.018 sec) MariaDB [toyu]> MariaDB [toyu]> CREATE TABLE IF NOT EXISTS Faculty ( -> facId INT NOT NULL, -> fname VARCHAR(20) NOT NULL, -> lname VARCHAR(20) NOT NULL, -> deptCode VARCHAR(4) NOT NULL, -> `rank` VARCHAR(25), -> CONSTRAINT Faculty_facId_pk PRIMARY KEY (facId), -> CONSTRAINT Faculty_deptCode_fk FOREIGN KEY (deptCode) -> REFERENCES Department(deptCode)); Query OK, 0 rows affected, 1 warning (0.020 sec) MariaDB [toyu]> MariaDB [toyu]> CREATE TABLE IF NOT EXISTS Course ( -> courseId INT NOT NULL, -> rubric char(4) NOT NULL, -> number char(4) NOT NULL, -> name VARCHAR(80) NOT NULL, -> credits TINYINT, -> CONSTRAINT Course_courseId_pk PRIMARY KEY (courseId), -> CONSTRAINT Course_deptCode_fk FOREIGN KEY (rubric) -> REFERENCES Department(deptCode)); Query OK, 0 rows affected, 1 warning (0.021 sec) MariaDB [toyu]> MariaDB [toyu]> CREATE TABLE IF NOT EXISTS Class ( -> classId INT NOT NULL AUTO_INCREMENT, -> courseId INT NOT NULL, -> semester VARCHAR(10) NOT NULL, -> year DECIMAL(4,0) NOT NULL, -> facId INT NOT NULL, -> room VARCHAR(6), -> CONSTRAINT Class_classId_pk PRIMARY KEY (classId), -> CONSTRAINT Class_courseId_fk FOREIGN KEY (courseId) -> REFERENCES Course(courseId) ON DELETE CASCADE, -> CONSTRAINT Class_facId_fk FOREIGN KEY (facId) -> REFERENCES Faculty (facId) ON DELETE CASCADE -> ); Query OK, 0 rows affected, 1 warning (0.023 sec) MariaDB [toyu]> MariaDB [toyu]> CREATE TABLE IF NOT EXISTS Student ( -> stuId INT NOT NULL, -> fname VARCHAR(20) NOT NULL, -> lname VARCHAR(20) NOT NULL, -> major VARCHAR(4) NULL, -> minor VARCHAR(4) NULL, -> credits integer(3) DEFAULT 0, -> advisor INT NULL, -> CONSTRAINT Student_stuId_pk PRIMARY KEY(stuId), -> CONSTRAINT Student_credits_cc CHECK ((credits>=0) AND (credits < 250)), -> CONSTRAINT Student_major_fk FOREIGN KEY (major) -> REFERENCES Department(deptCode) ON DELETE CASCADE, -> CONSTRAINT Student_minor_fk FOREIGN KEY (minor) -> REFERENCES Department(deptCode) ON DELETE CASCADE, -> CONSTRAINT Student_advisor_fk FOREIGN KEY (advisor) -> REFERENCES Faculty(facId) -> ); Query OK, 0 rows affected, 1 warning (0.016 sec) MariaDB [toyu]> MariaDB [toyu]> CREATE TABLE IF NOT EXISTS Enroll( -> stuId INT NOT NULL, -> classId INT NOT NULL, -> grade VARCHAR(2), -> n_alerts INT, -> CONSTRAINT Enroll_classId_stuId_pk PRIMARY KEY (classId, stuId), -> CONSTRAINT Enroll_classNumber_fk FOREIGN KEY (classId) -> REFERENCES Class(classId) ON DELETE CASCADE, -> CONSTRAINT Enroll_stuId_fk FOREIGN KEY (stuId) -> REFERENCES Student (stuId) ON DELETE CASCADE, -> CONSTRAINT Enroll_grade_fk FOREIGN KEY (grade) -> REFERENCES Grade (grade) ON DELETE CASCADE -> ); Query OK, 0 rows affected, 1 warning (0.027 sec) MariaDB [toyu]> MariaDB [toyu]> INSERT INTO Grade VALUES -> ('A',4),('A-',3.6667),('B+',3.3333),('B',3),('B-',2.6667), -> ('C+',2.3333),('C',2),('C-',1.6667), -> ('D+',1.3333),('D',1),('D-',0.6667),('F',0), -> ('WX', NULL); Query OK, 13 rows affected (0.010 sec) Records: 13 Duplicates: 0 Warnings: 0 MariaDB [toyu]> MariaDB [toyu]> INSERT INTO School VALUES -> ('BUS','Business'),('EDU','Education'), -> ('HSH','Human Sciences and Humanities'), -> ('CSE','Science and Engineering'); Query OK, 4 rows affected (0.008 sec) Records: 4 Duplicates: 0 Warnings: 0 MariaDB [toyu]> MariaDB [toyu]> INSERT INTO Department VALUES -> ('ACCT','Accounting','BUS',10), -> ('ARTS','Arts','HSH',5), -> ('CINF','Computer Information Systems','CSE',5), -> ('CSCI','Computer Science','CSE',12), -> ('ENGL','English','HSH',12), -> ('ITEC','Information Technology','CSE',4), -> ('MATH','Mathematics','CSE',7); Query OK, 7 rows affected (0.008 sec) Records: 7 Duplicates: 0 Warnings: 0 MariaDB [toyu]> MariaDB [toyu]> INSERT INTO Faculty VALUES -> (1011,'Paul','Smith','CSCI','Professor'), -> (1012,'Mary','Tran','CSCI','Associate Professor'), -> (1013,'David','Love','CSCI',NULL), -> (1014,'Sharon','Mannes','CSCI','Assistant Professor'), -> (1015,'Daniel','Kim','CINF','Professor'), -> (1016,'Andrew','Byre','CINF','Associate Professor'), -> (1017,'Deborah','Gump','ITEC','Professor'), -> (1018,'Art','Allister','ARTS','Assistant Professor'), -> (1019,'Benjamin','Yu','ITEC','Lecturer'), -> (1020,'Katrina','Bajaj','ENGL','Lecturer'), -> (1021,'Jorginlo','Neymar','ACCT','Assistant Professor'); Query OK, 11 rows affected (0.004 sec) Records: 11 Duplicates: 0 Warnings: 0 MariaDB [toyu]> MariaDB [toyu]> INSERT INTO Course VALUES -> (2000,'CSCI',3333,'Data Structures',3), -> (2001,'CSCI',4333,'Design of Database Systems',3), -> (2002,'CSCI',5333,'DBMS',3), -> (2020,'CINF',3321,'Introduction to Information Systems',3), -> (2021,'CINF',4320,'Web Application Development',3), -> (2040,'ITEC',3335,'Database Development',3), -> (2041,'ITEC',3312,'Introduction to Scripting',3), -> (2060,'ENGL',1410,'English I',4), -> (2061,'ENGL',1311,'English II',3), -> (2080,'ARTS',3311,'Hindu Arts',3), -> (2090,'ACCT',3333,'Managerial Accounting',3); Query OK, 11 rows affected (0.004 sec) Records: 11 Duplicates: 0 Warnings: 0 MariaDB [toyu]> MariaDB [toyu]> INSERT INTO Class VALUES -> (10000,2000,'Fall',2019,1011,'D241'), -> (10001,2001,'Fall',2019,1011,'D242'), -> (10002,2002,'Fall',2019,1012,'D136'), -> (10003,2020,'Fall',2019,1014,'D241'), -> (10004,2021,'Fall',2019,1014,'D241'), -> (10005,2040,'Fall',2019,1015,'D237'), -> (10006,2041,'Fall',2019,1019,'D217'), -> (10007,2060,'Fall',2019,1020,'B101'), -> (10008,2080,'Fall',2019,1018,'D241'), -> (11000,2000,'Spring',2020,1011,'D241'), -> (11001,2001,'Spring',2020,1013,'D242'), -> (11002,2002,'Spring',2020,1013,'D136'), -> (11003,2020,'Spring',2020,1016,'D217'), -> (11004,2061,'Spring',2020,1018,'B101'); Query OK, 14 rows affected (0.005 sec) Records: 14 Duplicates: 0 Warnings: 0 MariaDB [toyu]> MariaDB [toyu]> INSERT INTO Student VALUES -> (100000,'Tony','Hawk','CSCI','CINF',40,1011), -> (100001,'Mary','Hawk','CSCI','CINF',35,1011), -> (100002,'David','Hawk','CSCI','ITEC',66,1011), -> (100003,'Catherine','Lim','ITEC','CINF',20,1017), -> (100004,'Larry','Johnson','ITEC',NULL,66,1017), -> (100005,'Linda','Johnson','CINF','ENGL',13,1015), -> (100006,'Lillian','Johnson','CINF','ITEC',18,1015), -> (100007,'Ben','Zico',NULL,NULL,16,NULL), -> (100008,'Bill','Ching','ARTS','ENGL',90,1018), -> (100009,'Linda','King','ARTS','CSCI',125,1018); Query OK, 10 rows affected (0.003 sec) Records: 10 Duplicates: 0 Warnings: 0 MariaDB [toyu]> MariaDB [toyu]> INSERT INTO Enroll VALUES -> (100000,10000,'A',0), -> (100001,10000,NULL,NULL), -> (100002,10000,'B-',3), -> (100000,10001,'A',2), -> (100001,10001,'A-',0), -> (100000,10002,'B+',1), -> (100002,10002,'B+',2), -> (100000,10003,'C',0), -> (100002,10003,'D',4), -> (100004,10003,'A',0), -> (100005,10003,NULL,NULL), -> (100000,10004,'A-',1), -> (100004,10004,'B+',NULL), -> (100005,10004,'A-',0), -> (100006,10004,'C+',NULL), -> (100005,10005,'A-',0), -> (100006,10005,'A',NULL), -> (100005,10006,'B+',NULL), -> (100007,10007,'F',4), -> (100008,10007,'C-',0), -> (100007,10008,'A-',0), -> (100000,11001,'D',4); Query OK, 22 rows affected (0.009 sec) Records: 22 Duplicates: 0 Warnings: 0 MariaDB [toyu]> MariaDB [toyu]> SET SQL_MODE=@OLD_SQL_MODE; Query OK, 0 rows affected (0.001 sec) MariaDB [toyu]> SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS; Query OK, 0 rows affected (0.000 sec) MariaDB [toyu]> SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS; Query OK, 0 rows affected (0.000 sec) MariaDB [toyu]> MariaDB [toyu]> MariaDB [toyu]> MariaDB [toyu]> MariaDB [toyu]> EXPLAIN SELECT * FROM Student; +------+-------------+---------+------+---------------+------+---------+------+------+-------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +------+-------------+---------+------+---------------+------+---------+------+------+-------+ | 1 | SIMPLE | Student | ALL | NULL | NULL | NULL | NULL | 10 | | +------+-------------+---------+------+---------------+------+---------+------+------+-------+ 1 row in set (0.002 sec) MariaDB [toyu]> SELECT 1; +---+ | 1 | +---+ | 1 | +---+ 1 row in set (0.000 sec) MariaDB [toyu]> MariaDB [toyu]> SELECT COUMT(*) -> FROM student; ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '*) FROM student' at line 1 MariaDB [toyu]> SELECT COUNT(*) -> FROM student; +----------+ | COUNT(*) | +----------+ | 10 | +----------+ 1 row in set (0.005 sec) MariaDB [toyu]> SET countStudents = 0; ERROR 1193 (HY000): Unknown system variable 'countStudents' MariaDB [toyu]> SET %countStudents = 0; ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '%countStudents = 0' at line 1 MariaDB [toyu]> SET &countStudents = 0; ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '&countStudents = 0' at line 1 MariaDB [toyu]> SET &countStudents = 0;SET @countStudents = 0; ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '&countStudents = 0' at line 1 Query OK, 0 rows affected (0.000 sec) MariaDB [toyu]> SET @countStudents = 0; Query OK, 0 rows affected (0.000 sec) MariaDB [toyu]> SELECT COUNT(*) INTO @countStudents -> FROM student; Query OK, 1 row affected (0.004 sec) MariaDB [toyu]> SELECT @countStudents; +----------------+ | @countStudents | +----------------+ | 10 | +----------------+ 1 row in set (0.000 sec) MariaDB [toyu]> SELECT @countStudents = @countStudents + 1; +-------------------------------------+ | @countStudents = @countStudents + 1 | +-------------------------------------+ | 0 | +-------------------------------------+ 1 row in set (0.003 sec) MariaDB [toyu]> SELECT @countStudents; +----------------+ | @countStudents | +----------------+ | 10 | +----------------+ 1 row in set (0.000 sec) MariaDB [toyu]> SELECT @countStudents := @countStudents + 1; +--------------------------------------+ | @countStudents := @countStudents + 1 | +--------------------------------------+ | 11 | +--------------------------------------+ 1 row in set (0.000 sec) MariaDB [toyu]> SELECT @countStudents := @countStudents + 1; +--------------------------------------+ | @countStudents := @countStudents + 1 | +--------------------------------------+ | 12 | +--------------------------------------+ 1 row in set (0.000 sec) MariaDB [toyu]> SET @row = 0; Query OK, 0 rows affected (0.000 sec) MariaDB [toyu]> MariaDB [toyu]> SELECT $row := @row + 1 AS `row`, * -> FROM student; ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ':= @row + 1 AS `row`, * FROM student' at line 1 MariaDB [toyu]> MariaDB [toyu]> SELECT @row := @row + 1 AS `row`, * -> FROM student; ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '* FROM student' at line 1 MariaDB [toyu]> SELECT @row := @row + 1 AS `row`, s.* -> FROM student AS s; +------+--------+-----------+---------+-------+-------+---------+---------+ | row | stuId | fname | lname | major | minor | credits | advisor | +------+--------+-----------+---------+-------+-------+---------+---------+ | 1 | 100000 | Tony | Hawk | CSCI | CINF | 40 | 1011 | | 2 | 100001 | Mary | Hawk | CSCI | CINF | 35 | 1011 | | 3 | 100002 | David | Hawk | CSCI | ITEC | 66 | 1011 | | 4 | 100003 | Catherine | Lim | ITEC | CINF | 20 | 1017 | | 5 | 100004 | Larry | Johnson | ITEC | NULL | 66 | 1017 | | 6 | 100005 | Linda | Johnson | CINF | ENGL | 13 | 1015 | | 7 | 100006 | Lillian | Johnson | CINF | ITEC | 18 | 1015 | | 8 | 100007 | Ben | Zico | NULL | NULL | 16 | NULL | | 9 | 100008 | Bill | Ching | ARTS | ENGL | 90 | 1018 | | 10 | 100009 | Linda | King | ARTS | CSCI | 125 | 1018 | +------+--------+-----------+---------+-------+-------+---------+---------+ 10 rows in set (0.000 sec) MariaDB [toyu]> SELECT * -> FROM student -> WHERE major IS NULL; +--------+-------+-------+-------+-------+---------+---------+ | stuId | fname | lname | major | minor | credits | advisor | +--------+-------+-------+-------+-------+---------+---------+ | 100007 | Ben | Zico | NULL | NULL | 16 | NULL | +--------+-------+-------+-------+-------+---------+---------+ 1 row in set (0.002 sec) MariaDB [toyu]> SELECT s.stuId, s.major -- columns. -> FROM student AS s -- [1] 'universal bit table' -> WHERE credits > 10 -- [2] row -> ; +--------+-------+ | stuId | major | +--------+-------+ | 100000 | CSCI | | 100001 | CSCI | | 100002 | CSCI | | 100003 | ITEC | | 100004 | ITEC | | 100005 | CINF | | 100006 | CINF | | 100007 | NULL | | 100008 | ARTS | | 100009 | ARTS | +--------+-------+ 10 rows in set (0.001 sec) MariaDB [toyu]> MariaDB [toyu]> SELECT s.major, s.stuId -- [4] columns. -> FROM student AS s -- [1] 'universal bit table' -> WHERE credits > 10 -- [2] row -> GROUP BY s.major --= [3] one group for each s.major value. -> ; ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '= [3] one group for each s.major value.' at line 4 MariaDB [toyu]> MariaDB [toyu]> SELECT s.major, s.stuId -- [4] columns. -> FROM student AS s -- [1] 'universal bit table' -> WHERE credits > 10 -- [2] row -> GROUP BY s.major -- [3] one group for each s.major value. -> ; +-------+--------+ | major | stuId | +-------+--------+ | NULL | 100007 | | ARTS | 100008 | | CINF | 100005 | | CSCI | 100000 | | ITEC | 100003 | +-------+--------+ 5 rows in set (0.001 sec) MariaDB [toyu]> SELECT s.stuId, s.major -- [3] columns. -> FROM student AS s -- [1] 'universal bit table' -> WHERE credits > 10 -- [2] row -> ; +--------+-------+ | stuId | major | +--------+-------+ | 100000 | CSCI | | 100001 | CSCI | | 100002 | CSCI | | 100003 | ITEC | | 100004 | ITEC | | 100005 | CINF | | 100006 | CINF | | 100007 | NULL | | 100008 | ARTS | | 100009 | ARTS | +--------+-------+ 10 rows in set (0.001 sec) MariaDB [toyu]> MariaDB [toyu]> MariaDB [toyu]> SELECT s.major, s.stuId -- [4] columns. -> FROM student AS s -- [1] 'universal bit table' -> WHERE credits > 10 -- [2] row -> GROUP BY s.major -- [3] one group for each s.major value. -> ; +-------+--------+ | major | stuId | +-------+--------+ | NULL | 100007 | | ARTS | 100008 | | CINF | 100005 | | CSCI | 100000 | | ITEC | 100003 | +-------+--------+ 5 rows in set (0.001 sec) MariaDB [toyu]> SELECT s.major, COUNT(s.stuId) AS `NUmber of students`, -- [4] columns. COUNT: aggregate function -> MAX(credits) AS `Max # of credits` -> FROM student AS s -- [1] 'universal bit table' -> WHERE credits > 10 -- [2] row -> GROUP BY s.major -- [3] one group for each s.major value. -> ; +-------+--------------------+------------------+ | major | NUmber of students | Max # of credits | +-------+--------------------+------------------+ | NULL | 1 | 16 | | ARTS | 2 | 125 | | CINF | 2 | 18 | | CSCI | 3 | 66 | | ITEC | 2 | 66 | +-------+--------------------+------------------+ 5 rows in set (0.002 sec) MariaDB [toyu]> -- show only those with 2 or more majors. MariaDB [toyu]> SELECT s.major, COUNT(s.stuId) AS `Number of students`, -- [4] columns. COUNT: aggregate function -> MAX(credits) AS `Max # of credits` -> FROM student AS s -- [1] 'universal bit table' -> WHERE credits > 10 -- [2] row -> `Number of students` >= 2 -> GROUP BY s.major -- [3] one group for each s.major value. -> ; ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '`Number of students` >= 2 GROUP BY s.major' at line 5 MariaDB [toyu]> -- show only those with 2 or more majors. MariaDB [toyu]> SELECT s.major, COUNT(s.stuId) AS `Number of students`, -- [4] columns. COUNT: aggregate function -> MAX(credits) AS `Max # of credits` -> FROM student AS s -- [1] 'universal bit table' -> WHERE credits > 10, -- [2] row -> `Number of students` >= 2 -- error: no group yet. -> GROUP BY s.major -- [3] one group for each s.major value. -> ; ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ' `Number of students` >= 2 GROUP BY s.major' at line 4 MariaDB [toyu]> -- show only those with 2 or more majors. MariaDB [toyu]> SELECT s.major, COUNT(s.stuId) AS `Number of students`, -- [4] columns. COUNT: aggregate function -> MAX(credits) AS `Max # of credits` -> FROM student AS s -- [1] 'universal bit table' -> WHERE credits > 10 -- [2] row -> GROUP BY s.major -- [3] one group for each s.major value. -> HAVING `Number of students` >= 2 -- error: no group yet. -> ; +-------+--------------------+------------------+ | major | Number of students | Max # of credits | +-------+--------------------+------------------+ | ARTS | 2 | 125 | | CINF | 2 | 18 | | CSCI | 3 | 66 | | ITEC | 2 | 66 | +-------+--------------------+------------------+ 4 rows in set (0.003 sec) MariaDB [toyu]> MariaDB [toyu]> SELECT DISTINCT s.stuId, CONCCAT(s.fname, ' ', s.lname) AS student, -> d.deptName AS major -> FROM Student AS s INNER JOIN department AS d ON (s.major = d.deptCode); ERROR 1305 (42000): FUNCTION toyu.CONCCAT does not exist MariaDB [toyu]> SELECT DISTINCT s.stuId, CONCAT(s.fname, ' ', s.lname) AS student, -> d.deptName AS major -> FROM Student AS s INNER JOIN department AS d ON (s.major = d.deptCode); +--------+-----------------+------------------------------+ | stuId | student | major | +--------+-----------------+------------------------------+ | 100000 | Tony Hawk | Computer Science | | 100001 | Mary Hawk | Computer Science | | 100002 | David Hawk | Computer Science | | 100003 | Catherine Lim | Information Technology | | 100004 | Larry Johnson | Information Technology | | 100005 | Linda Johnson | Computer Information Systems | | 100006 | Lillian Johnson | Computer Information Systems | | 100008 | Bill Ching | Arts | | 100009 | Linda King | Arts | +--------+-----------------+------------------------------+ 9 rows in set (0.003 sec) MariaDB [toyu]> MariaDB [toyu]> SELECT DISTINCT s.stuId, CONCAT(s.fname, ' ', s.lname) AS student, -> d.deptName AS major -> FROM Student AS s LEFT JOIN department AS d ON (s.major = d.deptCode); +--------+-----------------+------------------------------+ | stuId | student | major | +--------+-----------------+------------------------------+ | 100000 | Tony Hawk | Computer Science | | 100001 | Mary Hawk | Computer Science | | 100002 | David Hawk | Computer Science | | 100003 | Catherine Lim | Information Technology | | 100004 | Larry Johnson | Information Technology | | 100005 | Linda Johnson | Computer Information Systems | | 100006 | Lillian Johnson | Computer Information Systems | | 100007 | Ben Zico | NULL | | 100008 | Bill Ching | Arts | | 100009 | Linda King | Arts | +--------+-----------------+------------------------------+ 10 rows in set (0.003 sec) MariaDB [toyu]> SELECT DISTINCT s.stuId, CONCAT(s.fname, ' ', s.lname) AS student, -> ISNULL(d.deptName, 'Undeclared') AS major -> FROM Student AS s LEFT JOIN department AS d ON (s.major = d.deptCode); ERROR 1582 (42000): Incorrect parameter count in the call to native function 'ISNULL' MariaDB [toyu]> SELECT DISTINCT s.stuId, CONCAT(s.fname, ' ', s.lname) AS student, -> IF(ISNULL(d.deptName), 'Undeclared'), d.deptName) AS major -> FROM Student AS s LEFT JOIN department AS d ON (s.major = d.deptCode); ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '), d.deptName) AS major FROM Student AS s LEFT JOIN department AS d ON (s.major ' at line 2 MariaDB [toyu]> SELECT DISTINCT s.stuId, CONCAT(s.fname, ' ', s.lname) AS student, -> IF(ISNULL(d.deptName), 'Undeclared', d.deptName) AS major -> FROM Student AS s LEFT JOIN department AS d ON (s.major = d.deptCode); +--------+-----------------+------------------------------+ | stuId | student | major | +--------+-----------------+------------------------------+ | 100000 | Tony Hawk | Computer Science | | 100001 | Mary Hawk | Computer Science | | 100002 | David Hawk | Computer Science | | 100003 | Catherine Lim | Information Technology | | 100004 | Larry Johnson | Information Technology | | 100005 | Linda Johnson | Computer Information Systems | | 100006 | Lillian Johnson | Computer Information Systems | | 100007 | Ben Zico | Undeclared | | 100008 | Bill Ching | Arts | | 100009 | Linda King | Arts | +--------+-----------------+------------------------------+ 10 rows in set (0.001 sec) MariaDB [toyu]> SELECT s.major, COUNT(s.stuId) AS `Number of students`, -- [4] columns. COUNT: aggregate function -> MAX(credits) AS `Max # of credits` -> FROM student AS s -- [1] 'universal bit table' -> WHERE credits > 10 -- [2] row -> GROUP BY s.major; +-------+--------------------+------------------+ | major | Number of students | Max # of credits | +-------+--------------------+------------------+ | NULL | 1 | 16 | | ARTS | 2 | 125 | | CINF | 2 | 18 | | CSCI | 3 | 66 | | ITEC | 2 | 66 | +-------+--------------------+------------------+ 5 rows in set (0.001 sec) MariaDB [toyu]> SELECT s.major, COUNT(s.stuId) AS `Number of students`, -- [4] columns. COUNT: aggregate function -> MAX(credits) AS `Max # of credits` -> FROM student AS s -- [1] 'universal bit table' -> WHERE credits > 10 -- [2] row -> AND s.major IS NOT NULL -> GROUP BY s.major; +-------+--------------------+------------------+ | major | Number of students | Max # of credits | +-------+--------------------+------------------+ | ARTS | 2 | 125 | | CINF | 2 | 18 | | CSCI | 3 | 66 | | ITEC | 2 | 66 | +-------+--------------------+------------------+ 4 rows in set (0.000 sec) MariaDB [toyu]> SELECT s.major AS deptCode, COUNT(s.stuId) AS `Number of majors`, -- [4] columns. COUNT: aggregate function -> MAX(credits) AS `Max # of credits` -> FROM student AS s -- [1] 'universal bit table' -> WHERE credits > 10 -- [2] row -> AND s.major IS NOT NULL -> GROUP BY s.major; +----------+------------------+------------------+ | deptCode | Number of majors | Max # of credits | +----------+------------------+------------------+ | ARTS | 2 | 125 | | CINF | 2 | 18 | | CSCI | 3 | 66 | | ITEC | 2 | 66 | +----------+------------------+------------------+ 4 rows in set (0.000 sec) MariaDB [toyu]> SELECT d1.deptCode, d1.deptName, temp.`Number of majors`, temp.`Max # of credits` -> FROM (SELECT s.major AS deptCode, COUNT(s.stuId) AS `Number of majors`, -- [4] columns. COUNT: aggregate function -> MAX(credits) AS `Max # of credits` -> FROM student AS s -- [1] 'universal bit table' -> WHERE credits > 10 -- [2] row -> AND s.major IS NOT NULL -> GROUP BY s.major) AS temp, -- Step [1]: subquery -> department AS d1 -> WHERE d1.deptCode - temp.deptCode; Empty set, 56 warnings (0.005 sec) MariaDB [toyu]> MariaDB [toyu]> SELECT d1.deptCode, d1.deptName, temp.`Number of majors`, temp.`Max # of credits` -> FROM (SELECT s.major AS deptCode, COUNT(s.stuId) AS `Number of majors`, -- [4] columns. COUNT: aggregate function -> MAX(credits) AS `Max # of credits` -> FROM student AS s -- [1] 'universal bit table' -> WHERE credits > 10 -- [2] row -> AND s.major IS NOT NULL -> GROUP BY s.major) AS temp, -- Step [1]: subquery -> department AS d1 -> WHERE d1.deptCode = temp.deptCode; +----------+------------------------------+------------------+------------------+ | deptCode | deptName | Number of majors | Max # of credits | +----------+------------------------------+------------------+------------------+ | ARTS | Arts | 2 | 125 | | CINF | Computer Information Systems | 2 | 18 | | CSCI | Computer Science | 3 | 66 | | ITEC | Information Technology | 2 | 66 | +----------+------------------------------+------------------+------------------+ 4 rows in set (0.001 sec) MariaDB [toyu]> EXPLAIN SELECT s.major AS deptCode, COUNT(s.stuId) AS `Number of majors`, -- [4] columns. COUNT: aggregate function -> MAX(credits) AS `Max # of credits` -> FROM student AS s -- [1] 'universal bit table' -> WHERE credits > 10 -- [2] row -> AND s.major IS NOT NULL -> GROUP BY s.major; +------+-------------+-------+-------+------------------+------------------+---------+------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +------+-------------+-------+-------+------------------+------------------+---------+------+------+-------------+ | 1 | SIMPLE | s | range | Student_major_fk | Student_major_fk | 19 | NULL | 9 | Using where | +------+-------------+-------+-------+------------------+------------------+---------+------+------+-------------+ 1 row in set (0.000 sec) MariaDB [toyu]> EXPLAIN SELECT d1.deptCode, d1.deptName, temp.`Number of majors`, temp.`Max # of credits` -> FROM (SELECT s.major AS deptCode, COUNT(s.stuId) AS `Number of majors`, -- [4] columns. COUNT: aggregate function -> MAX(credits) AS `Max # of credits` -> FROM student AS s -- [1] 'universal bit table' -> WHERE credits > 10 -- [2] row -> AND s.major IS NOT NULL -> GROUP BY s.major) AS temp, -- Step [1]: subquery -> department AS d1 -> WHERE d1.deptCode = temp.deptCode; +------+-------------+------------+-------+------------------+--------------------+---------+------------------+------+----------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +------+-------------+------------+-------+------------------+--------------------+---------+------------------+------+----------------------------------------------+ | 1 | PRIMARY | d1 | index | PRIMARY | Department_name_ck | 123 | NULL | 7 | Using index | | 1 | PRIMARY | | ref | key0 | key0 | 19 | toyu.d1.deptCode | 2 | | | 2 | DERIVED | s | ALL | Student_major_fk | NULL | NULL | NULL | 10 | Using where; Using temporary; Using filesort | +------+-------------+------------+-------+------------------+--------------------+---------+------------------+------+----------------------------------------------+ 3 rows in set (0.002 sec) MariaDB [toyu]> WITH temp AS -> (SELECT s.major AS deptCode, COUNT(s.stuId) AS `Number of majors`, -- [4] columns. COUNT: aggregate function -> MAX(credits) AS `Max # of credits` -> FROM student AS s -- [1] 'universal bit table' -> WHERE credits > 10 -- [2] row -> AND s.major IS NOT NULL -> GROUP BY s.major) -- step [1] -> SELECT d1.deptCode, d1.deptName, temp.`Number of majors`, temp.`Max # of credits` -> FROM temp INNER JOIN department AS d1 ON (d1.deptCode = temp.deptCode); +----------+------------------------------+------------------+------------------+ | deptCode | deptName | Number of majors | Max # of credits | +----------+------------------------------+------------------+------------------+ | ARTS | Arts | 2 | 125 | | CINF | Computer Information Systems | 2 | 18 | | CSCI | Computer Science | 3 | 66 | | ITEC | Information Technology | 2 | 66 | +----------+------------------------------+------------------+------------------+ 4 rows in set (0.003 sec) MariaDB [toyu]> WITH RECURSIVE cte (n) AS -> ( -> SELECT 1 -> UNION ALL -> SELECT n + 1 FROM cte WHERE n < 5 -> ) -> SELECT * FROM cte; +------+ | n | +------+ | 1 | | 2 | | 3 | | 4 | | 5 | +------+ 5 rows in set (0.002 sec) MariaDB [toyu]> notee