MariaDB [(none)]> use toyu; Database changed MariaDB [toyu]> SELECT s.major, d.deptName, -> COUNT(s.stuId) AS `# major` -> FROM student AS s LEFT JOIN department AS d ON (s.major = d.deptCode) -> WHERE s.ach >= 10 -> GROUP BY s.major, d.deptName -- form group according to distinct values in s.major -> HAVING `# major` > 1 -> ORDER BY s.major; +-------+------------------------------+---------+ | major | deptName | # major | +-------+------------------------------+---------+ | ARTS | Arts | 2 | | CINF | Computer Information Systems | 2 | | CSCI | Computer Science | 3 | | ITEC | Information Technology | 2 | +-------+------------------------------+---------+ 4 rows in set (0.039 sec) MariaDB [toyu]> SELECT s*, d.* -> FROM student AS s LEFT JOIN department AS d ON (s.major = d.deptCode) -> WHERE s.ach >= 10; 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.* FROM student AS s LEFT JOIN department AS d ON (s.major = d.deptCode) WH...' at line 1 MariaDB [toyu]> SELECT s.*, d.* -> FROM student AS s LEFT JOIN department AS d ON (s.major = d.deptCode) -> WHERE s.ach >= 10; +--------+-----------+---------+-------+-------+------+---------+----------+------------------------------+------------+----------+ | stuId | fname | lname | major | minor | ach | advisor | deptCode | deptName | schoolCode | numStaff | +--------+-----------+---------+-------+-------+------+---------+----------+------------------------------+------------+----------+ | 100000 | Tony | Hawk | CSCI | CINF | 40 | 1011 | CSCI | Computer Science | CSE | 12 | | 100001 | Mary | Hawk | CSCI | CINF | 35 | 1011 | CSCI | Computer Science | CSE | 12 | | 100002 | David | Hawk | CSCI | ITEC | 66 | 1012 | CSCI | Computer Science | CSE | 12 | | 100003 | Catherine | Lim | ITEC | CINF | 20 | NULL | ITEC | Information Technology | CSE | 4 | | 100004 | Larry | Johnson | ITEC | NULL | 66 | 1017 | ITEC | Information Technology | CSE | 4 | | 100005 | Linda | Johnson | CINF | ENGL | 13 | 1015 | CINF | Computer Information Systems | CSE | 5 | | 100006 | Lillian | Johnson | CINF | ITEC | 18 | 1016 | CINF | Computer Information Systems | CSE | 5 | | 100007 | Ben | Zico | NULL | NULL | 16 | NULL | NULL | NULL | NULL | NULL | | 100008 | Bill | Ching | ARTS | NULL | 90 | NULL | ARTS | Arts | HSH | 5 | | 100009 | Linda | King | ARTS | CSCI | 125 | 1018 | ARTS | Arts | HSH | 5 | +--------+-----------+---------+-------+-------+------+---------+----------+------------------------------+------------+----------+ 10 rows in set (0.002 sec) MariaDB [toyu]> SELECT s.major, d.deptName, -> COUNT(s.stuId) AS `# major` -> FROM student AS s LEFT JOIN department AS d ON (s.major = d.deptCode) -> WHERE s.ach >= 10 -> GROUP BY s.major, d.deptName -- form group according to distinct values in s.major -> ; +-------+------------------------------+---------+ | major | deptName | # major | +-------+------------------------------+---------+ | NULL | NULL | 1 | | ARTS | Arts | 2 | | CINF | Computer Information Systems | 2 | | CSCI | Computer Science | 3 | | ITEC | Information Technology | 2 | +-------+------------------------------+---------+ 5 rows in set (0.001 sec) MariaDB [toyu]> SELECT s.major, d.deptName, -> COUNT(s.stuId) AS `# major` -> FROM student AS s LEFT JOIN department AS d ON (s.major = d.deptCode) -> WHERE s.ach >= 10 -> GROUP BY s.major, d.deptName -- form group according to distinct values in s.major -> HAVING `# major` > 1 -> ORDER BY s.major; +-------+------------------------------+---------+ | major | deptName | # major | +-------+------------------------------+---------+ | ARTS | Arts | 2 | | CINF | Computer Information Systems | 2 | | CSCI | Computer Science | 3 | | ITEC | Information Technology | 2 | +-------+------------------------------+---------+ 4 rows in set (0.001 sec) MariaDB [toyu]> MariaDB [toyu]> SELECT s.major, COUNT(s.stuId) AS `# major` -> FROM student AS s -> GROUP BY s.major -> WHERE s.ach >= 10; 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 'WHERE s.ach >= 10' at line 4 MariaDB [toyu]> SELECT s.major, COUNT(s.stuId) AS `# major` -> FROM student AS s -> GROUP BY s.major -> HAVING s.ach >= 10; ERROR 1054 (42S22): Unknown column 's.ach' in 'having clause' MariaDB [toyu]> SELECT s.major, COUNT(s.stuId) AS `# major` -> FROM student AS s -> WHERE s.ach >= 10 AND `# major` > 1 -> GROUP BY s.major; ERROR 1054 (42S22): Unknown column '# major' in 'where clause' MariaDB [toyu]> SELECT s.major, COUNT(s.stuId) AS `# major` -> FROM student AS s -> WHERE s.ach >= 10 AND COUNT(s.stuId) > 1 -> GROUP BY s.major -> ; ERROR 1111 (HY000): Invalid use of group function MariaDB [toyu]> SELECT s.major, d.deptName, -- group by columns -> COUNT(s.stuId) AS `# major`, -- group functions -> 'Hello' -> FROM student AS s LEFT JOIN department AS d ON (s.major = d.deptCode) -> WHERE s.ach >= 10 -> GROUP BY s.major, d.deptName -- form group according to distinct values in s.major -> HAVING `# major` > 1 -> ORDER BY s.major; +-------+------------------------------+---------+-------+ | major | deptName | # major | Hello | +-------+------------------------------+---------+-------+ | ARTS | Arts | 2 | Hello | | CINF | Computer Information Systems | 2 | Hello | | CSCI | Computer Science | 3 | Hello | | ITEC | Information Technology | 2 | Hello | +-------+------------------------------+---------+-------+ 4 rows in set (0.001 sec) MariaDB [toyu]> MariaDB [toyu]> SELECT s.major, d.deptName, -- group by columns -> COUNT(s.stuId) AS `# major`, -- group functions -> 'Hello' -> , s.sid, CONCAT(s.fname, '', f.lname) -> FROM student AS s LEFT JOIN department AS d ON (s.major = d.deptCode) -> WHERE s.ach >= 10 -> GROUP BY s.major, d.deptName -- form group according to distinct values in s.major -> HAVING `# major` > 1 -> ORDER BY s.major; ERROR 1054 (42S22): Unknown column 's.sid' in 'field list' MariaDB [toyu]> SELECT CONCAT(s.fname, ' ', s.lname) AS student, -> COUNT(e.classId) AS `Enrolled classes` -> FROM student AS s LEFT JOIN enroll e ON (s.stuId = e.stuId) -> GROUP BY student -> ORDER BY `Enrolled classes` DESC; +-----------------+------------------+ | student | Enrolled classes | +-----------------+------------------+ | Tony Hawk | 6 | | Linda Johnson | 4 | | David Hawk | 3 | | Mary Hawk | 2 | | Larry Johnson | 2 | | Lillian Johnson | 2 | | Ben Zico | 2 | | Bill Ching | 1 | | Catherine Lim | 0 | | Linda King | 0 | | Cathy Johanson | 0 | +-----------------+------------------+ 11 rows in set (0.006 sec) MariaDB [toyu]> SELECT f.deptCode, COUNT(f.facId) AS numFaculty -> FROM faculty AS f -> GROUP BY f.deptCode; +----------+------------+ | deptCode | numFaculty | +----------+------------+ | ACCT | 1 | | ARTS | 1 | | CINF | 2 | | CSCI | 4 | | ENGL | 1 | | ITEC | 2 | +----------+------------+ 6 rows in set (0.003 sec) MariaDB [toyu]> SELECT s.major AS deptCode, COUNT(s.stuId) AS numMajor -> FROM student AS s -> GROUP BY s.major; +----------+----------+ | deptCode | numMajor | +----------+----------+ | NULL | 2 | | ARTS | 2 | | CINF | 2 | | CSCI | 3 | | ITEC | 2 | +----------+----------+ 5 rows in set (0.002 sec) MariaDB [toyu]> SELECT s.minor AS deptCode, COUNT(s.stuId) AS numMinor -> FROM student AS s -> GROUP BY s.minor; +----------+----------+ | deptCode | numMinor | +----------+----------+ | NULL | 4 | | CINF | 3 | | CSCI | 1 | | ENGL | 1 | | ITEC | 2 | +----------+----------+ 5 rows in set (0.002 sec) MariaDB [toyu]> WITH ma AS -> (SELECT s.major AS deptCode, COUNT(s.stuId) AS numMajor -> FROM student AS s -> GROUP BY s.major), -> mi AS -> (SELECT s.minor AS deptCode, COUNT(s.stuId) AS numMinor -> FROM student AS s -> GROUP BY s.minor), -> f AS -> (SELECT f.deptCode, COUNT(f.facId) AS numFaculty -> FROM faculty AS f -> GROUP BY f.deptCode) -> SELECT d.deptCode, -> d.deptName, -> IFNULL(f.numFaculty, 0) AS numFaculty, -> IFNULL(ma.numMajor, 0) AS numMajor, -> IFNULL(mi.numMinor, 0) AS numMinor -> FROM department AS d LEFT JOIN ma USING (deptCode) -> LEFT JOIN mi USING (deptCode) -> LEFT JOIN f USING (deptCode); +----------+------------------------------+------------+----------+----------+ | 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 | 1 | | ITEC | Information Technology | 2 | 2 | 2 | | MATH | Mathematics | 0 | 0 | 0 | +----------+------------------------------+------------+----------+----------+ 7 rows in set (0.013 sec) MariaDB [toyu]> Adding row number and rank: -> WITH ma AS -> (SELECT s.major AS deptCode, COUNT(s.stuId) AS numMajor -> FROM student AS s -> GROUP BY s.major), -> mi AS -> (SELECT s.minor AS deptCode, COUNT(s.stuId) AS numMinor -> FROM student AS s -> GROUP BY s.minor), -> f AS -> (SELECT f.deptCode, COUNT(f.facId) AS numFaculty -> FROM faculty AS f -> GROUP BY f.deptCode) -> SELECT ROW_NUMBER() OVER () AS `#`, -> RANK() OVER (ORDER BY f.numFaculty DESC) AS `# in descending number of faculty`, -> d.deptCode, -> d.deptName, -> IFNULL(f.numFaculty, 0) AS numFaculty, -> IFNULL(ma.numMajor, 0) AS numMajor, -> IFNULL(mi.numMinor, 0) AS numMinor -> FROM department AS d LEFT JOIN ma USING (deptCode) -> LEFT JOIN mi USING (deptCode) -> LEFT JOIN f USING (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 'Adding row number and rank: WITH ma AS (SELECT s.major AS deptCode, COUNT(s.s...' at line 1 MariaDB [toyu]> WITH ma AS -> (SELECT s.major AS deptCode, COUNT(s.stuId) AS numMajor -> FROM student AS s -> GROUP BY s.major), -> mi AS -> (SELECT s.minor AS deptCode, COUNT(s.stuId) AS numMinor -> FROM student AS s -> GROUP BY s.minor), -> f AS -> (SELECT f.deptCode, COUNT(f.facId) AS numFaculty -> FROM faculty AS f -> GROUP BY f.deptCode) -> SELECT ROW_NUMBER() OVER () AS `#`, -> RANK() OVER (ORDER BY f.numFaculty DESC) AS `# in descending number of faculty`, -> d.deptCode, -> d.deptName, -> IFNULL(f.numFaculty, 0) AS numFaculty, -> IFNULL(ma.numMajor, 0) AS numMajor, -> IFNULL(mi.numMinor, 0) AS numMinor -> FROM department AS d LEFT JOIN ma USING (deptCode) -> LEFT JOIN mi USING (deptCode) -> LEFT JOIN f USING (deptCode); +---+-----------------------------------+----------+------------------------------+------------+----------+----------+ | # | # in descending number of faculty | deptCode | deptName | numFaculty | numMajor | numMinor | +---+-----------------------------------+----------+------------------------------+------------+----------+----------+ | 1 | 1 | CSCI | Computer Science | 4 | 3 | 1 | | 2 | 2 | CINF | Computer Information Systems | 2 | 2 | 3 | | 3 | 2 | ITEC | Information Technology | 2 | 2 | 2 | | 4 | 4 | ARTS | Arts | 1 | 2 | 0 | | 5 | 4 | ACCT | Accounting | 1 | 0 | 0 | | 6 | 4 | ENGL | English | 1 | 0 | 1 | | 7 | 7 | MATH | Mathematics | 0 | 0 | 0 | +---+-----------------------------------+----------+------------------------------+------------+----------+----------+ 7 rows in set (0.005 sec) MariaDB [toyu]> WITH temp AS -> (SELECT DISTINCT sc.schoolName AS college, d.deptName AS department, -> COUNT(s.stuId) As deptMajor -> FROM school AS sc INNER JOIN department AS d ON (sc.schoolCode = d.schoolCode) -> LEFT JOIN student AS s ON (s.major = d.deptCode) -> GROUP BY college, department) -> SELECT temp.college, temp.department, -> temp.deptMajor AS `major in department`, -> SUM(deptMajor) OVER(PARTITION BY college) AS `major in college`, -> SUM(deptMajor) OVER() AS `major in university` -> FROM temp; +-------------------------------+------------------------------+---------------------+------------------+---------------------+ | college | department | major in department | major in college | major in university | +-------------------------------+------------------------------+---------------------+------------------+---------------------+ | Business | Accounting | 0 | 0 | 9 | | Human Sciences and Humanities | Arts | 2 | 2 | 9 | | Human Sciences and Humanities | English | 0 | 2 | 9 | | Science and Engineering | Computer Science | 3 | 7 | 9 | | Science and Engineering | Information Technology | 2 | 7 | 9 | | Science and Engineering | Mathematics | 0 | 7 | 9 | | Science and Engineering | Computer Information Systems | 2 | 7 | 9 | +-------------------------------+------------------------------+---------------------+------------------+---------------------+ 7 rows in set (0.003 sec) MariaDB [toyu]> -- Prepared statements. MariaDB [toyu]> SET @sql = "SELECT * FROM toyu.student"; Query OK, 0 rows affected (0.002 sec) MariaDB [toyu]> PREPARE stmt FROM @sql; Query OK, 0 rows affected (0.003 sec) Statement prepared MariaDB [toyu]> EXECUTE stmt; +--------+-----------+----------+-------+-------+------+---------+ | stuId | fname | lname | major | minor | ach | advisor | +--------+-----------+----------+-------+-------+------+---------+ | 100000 | Tony | Hawk | CSCI | CINF | 40 | 1011 | | 100001 | Mary | Hawk | CSCI | CINF | 35 | 1011 | | 100002 | David | Hawk | CSCI | ITEC | 66 | 1012 | | 100003 | Catherine | Lim | ITEC | CINF | 20 | NULL | | 100004 | Larry | Johnson | ITEC | NULL | 66 | 1017 | | 100005 | Linda | Johnson | CINF | ENGL | 13 | 1015 | | 100006 | Lillian | Johnson | CINF | ITEC | 18 | 1016 | | 100007 | Ben | Zico | NULL | NULL | 16 | NULL | | 100008 | Bill | Ching | ARTS | NULL | 90 | NULL | | 100009 | Linda | King | ARTS | CSCI | 125 | 1018 | | 100111 | Cathy | Johanson | NULL | NULL | 0 | 1018 | +--------+-----------+----------+-------+-------+------+---------+ 11 rows in set (0.001 sec) MariaDB [toyu]> DEALLOCATE PREPARE stmt; Query OK, 0 rows affected (0.000 sec) MariaDB [toyu]> SELECT * FROM toyu.studen; ERROR 1146 (42S02): Table 'toyu.studen' doesn't exist MariaDB [toyu]> SELECT * FROM toyu.student; +--------+-----------+----------+-------+-------+------+---------+ | stuId | fname | lname | major | minor | ach | advisor | +--------+-----------+----------+-------+-------+------+---------+ | 100000 | Tony | Hawk | CSCI | CINF | 40 | 1011 | | 100001 | Mary | Hawk | CSCI | CINF | 35 | 1011 | | 100002 | David | Hawk | CSCI | ITEC | 66 | 1012 | | 100003 | Catherine | Lim | ITEC | CINF | 20 | NULL | | 100004 | Larry | Johnson | ITEC | NULL | 66 | 1017 | | 100005 | Linda | Johnson | CINF | ENGL | 13 | 1015 | | 100006 | Lillian | Johnson | CINF | ITEC | 18 | 1016 | | 100007 | Ben | Zico | NULL | NULL | 16 | NULL | | 100008 | Bill | Ching | ARTS | NULL | 90 | NULL | | 100009 | Linda | King | ARTS | CSCI | 125 | 1018 | | 100111 | Cathy | Johanson | NULL | NULL | 0 | 1018 | +--------+-----------+----------+-------+-------+------+---------+ 11 rows in set (0.001 sec) MariaDB [toyu]> MariaDB [toyu]> SET @sql = "SELECT * FROM toyu.student"; Query OK, 0 rows affected (0.000 sec) MariaDB [toyu]> PREPARE stmt FROM @sql; Query OK, 0 rows affected (0.000 sec) Statement prepared MariaDB [toyu]> EXECUTE stmt; +--------+-----------+----------+-------+-------+------+---------+ | stuId | fname | lname | major | minor | ach | advisor | +--------+-----------+----------+-------+-------+------+---------+ | 100000 | Tony | Hawk | CSCI | CINF | 40 | 1011 | | 100001 | Mary | Hawk | CSCI | CINF | 35 | 1011 | | 100002 | David | Hawk | CSCI | ITEC | 66 | 1012 | | 100003 | Catherine | Lim | ITEC | CINF | 20 | NULL | | 100004 | Larry | Johnson | ITEC | NULL | 66 | 1017 | | 100005 | Linda | Johnson | CINF | ENGL | 13 | 1015 | | 100006 | Lillian | Johnson | CINF | ITEC | 18 | 1016 | | 100007 | Ben | Zico | NULL | NULL | 16 | NULL | | 100008 | Bill | Ching | ARTS | NULL | 90 | NULL | | 100009 | Linda | King | ARTS | CSCI | 125 | 1018 | | 100111 | Cathy | Johanson | NULL | NULL | 0 | 1018 | +--------+-----------+----------+-------+-------+------+---------+ 11 rows in set (0.001 sec) MariaDB [toyu]> EXECUTE stmt; +--------+-----------+----------+-------+-------+------+---------+ | stuId | fname | lname | major | minor | ach | advisor | +--------+-----------+----------+-------+-------+------+---------+ | 100000 | Tony | Hawk | CSCI | CINF | 40 | 1011 | | 100001 | Mary | Hawk | CSCI | CINF | 35 | 1011 | | 100002 | David | Hawk | CSCI | ITEC | 66 | 1012 | | 100003 | Catherine | Lim | ITEC | CINF | 20 | NULL | | 100004 | Larry | Johnson | ITEC | NULL | 66 | 1017 | | 100005 | Linda | Johnson | CINF | ENGL | 13 | 1015 | | 100006 | Lillian | Johnson | CINF | ITEC | 18 | 1016 | | 100007 | Ben | Zico | NULL | NULL | 16 | NULL | | 100008 | Bill | Ching | ARTS | NULL | 90 | NULL | | 100009 | Linda | King | ARTS | CSCI | 125 | 1018 | | 100111 | Cathy | Johanson | NULL | NULL | 0 | 1018 | +--------+-----------+----------+-------+-------+------+---------+ 11 rows in set (0.001 sec) MariaDB [toyu]> EXECUTE stmt; +--------+-----------+----------+-------+-------+------+---------+ | stuId | fname | lname | major | minor | ach | advisor | +--------+-----------+----------+-------+-------+------+---------+ | 100000 | Tony | Hawk | CSCI | CINF | 40 | 1011 | | 100001 | Mary | Hawk | CSCI | CINF | 35 | 1011 | | 100002 | David | Hawk | CSCI | ITEC | 66 | 1012 | | 100003 | Catherine | Lim | ITEC | CINF | 20 | NULL | | 100004 | Larry | Johnson | ITEC | NULL | 66 | 1017 | | 100005 | Linda | Johnson | CINF | ENGL | 13 | 1015 | | 100006 | Lillian | Johnson | CINF | ITEC | 18 | 1016 | | 100007 | Ben | Zico | NULL | NULL | 16 | NULL | | 100008 | Bill | Ching | ARTS | NULL | 90 | NULL | | 100009 | Linda | King | ARTS | CSCI | 125 | 1018 | | 100111 | Cathy | Johanson | NULL | NULL | 0 | 1018 | +--------+-----------+----------+-------+-------+------+---------+ 11 rows in set (0.001 sec) MariaDB [toyu]> DEALLOCATE PREPARE stmt; Query OK, 0 rows affected (0.000 sec) MariaDB [toyu]> EXPLAIN SELECT * FROM toyu.student; +------+-------------+---------+------+---------------+------+---------+------+------+-------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +------+-------------+---------+------+---------------+------+---------+------+------+-------+ | 1 | SIMPLE | student | ALL | NULL | NULL | NULL | NULL | 11 | | +------+-------------+---------+------+---------------+------+---------+------+------+-------+ 1 row in set (0.002 sec) MariaDB [toyu]> -- with placeholders. MariaDB [toyu]> SET @sql = "SELECT * FROM toyu.student WHERE major = ? AND ach >= ?"; Query OK, 0 rows affected (0.000 sec) MariaDB [toyu]> PREPARE stmt FROM @sql; Query OK, 0 rows affected (0.001 sec) Statement prepared MariaDB [toyu]> SET @major = 'CSCI'; Query OK, 0 rows affected (0.001 sec) MariaDB [toyu]> SET @ach = 38; Query OK, 0 rows affected (0.000 sec) MariaDB [toyu]> EXECUTE stmt USING @major, @ach; +--------+-------+-------+-------+-------+------+---------+ | stuId | fname | lname | major | minor | ach | advisor | +--------+-------+-------+-------+-------+------+---------+ | 100000 | Tony | Hawk | CSCI | CINF | 40 | 1011 | | 100002 | David | Hawk | CSCI | ITEC | 66 | 1012 | +--------+-------+-------+-------+-------+------+---------+ 2 rows in set (0.005 sec) MariaDB [toyu]> MariaDB [toyu]> EXECUTE stmt USING 'CSCI', 38; +--------+-------+-------+-------+-------+------+---------+ | stuId | fname | lname | major | minor | ach | advisor | +--------+-------+-------+-------+-------+------+---------+ | 100000 | Tony | Hawk | CSCI | CINF | 40 | 1011 | | 100002 | David | Hawk | CSCI | ITEC | 66 | 1012 | +--------+-------+-------+-------+-------+------+---------+ 2 rows in set (0.002 sec) MariaDB [toyu]> MariaDB [toyu]> SET @major = 'CINF'; Query OK, 0 rows affected (0.001 sec) MariaDB [toyu]> SET @ach = 15; Query OK, 0 rows affected (0.001 sec) MariaDB [toyu]> EXECUTE stmt USING @major, @ach; +--------+---------+---------+-------+-------+------+---------+ | stuId | fname | lname | major | minor | ach | advisor | +--------+---------+---------+-------+-------+------+---------+ | 100006 | Lillian | Johnson | CINF | ITEC | 18 | 1016 | +--------+---------+---------+-------+-------+------+---------+ 1 row in set (0.000 sec) MariaDB [toyu]> MariaDB [toyu]> SET @major = 'ITEC'; Query OK, 0 rows affected (0.000 sec) MariaDB [toyu]> SET @ach = 25; Query OK, 0 rows affected (0.000 sec) MariaDB [toyu]> EXECUTE stmt USING @major, @ach; +--------+-------+---------+-------+-------+------+---------+ | stuId | fname | lname | major | minor | ach | advisor | +--------+-------+---------+-------+-------+------+---------+ | 100004 | Larry | Johnson | ITEC | NULL | 66 | 1017 | +--------+-------+---------+-------+-------+------+---------+ 1 row in set (0.001 sec) MariaDB [toyu]> MariaDB [toyu]> -- with placeholders. MariaDB [toyu]> SET @sql = "SELECT * FROM toyu.student WHERE major = ? AND ach >= ?"; Query OK, 0 rows affected (0.000 sec) MariaDB [toyu]> PREPARE stmt FROM @sql; Query OK, 0 rows affected (0.001 sec) Statement prepared MariaDB [toyu]> SET @major = 'CSCI'; Query OK, 0 rows affected (0.001 sec) MariaDB [toyu]> SET @ach = 38; Query OK, 0 rows affected (0.001 sec) MariaDB [toyu]> EXECUTE stmt USING @major, @ach; +--------+-------+-------+-------+-------+------+---------+ | stuId | fname | lname | major | minor | ach | advisor | +--------+-------+-------+-------+-------+------+---------+ | 100000 | Tony | Hawk | CSCI | CINF | 40 | 1011 | | 100002 | David | Hawk | CSCI | ITEC | 66 | 1012 | +--------+-------+-------+-------+-------+------+---------+ 2 rows in set (0.001 sec) MariaDB [toyu]> MariaDB [toyu]> EXECUTE stmt USING 'CSCI', 38; +--------+-------+-------+-------+-------+------+---------+ | stuId | fname | lname | major | minor | ach | advisor | +--------+-------+-------+-------+-------+------+---------+ | 100000 | Tony | Hawk | CSCI | CINF | 40 | 1011 | | 100002 | David | Hawk | CSCI | ITEC | 66 | 1012 | +--------+-------+-------+-------+-------+------+---------+ 2 rows in set (0.001 sec) MariaDB [toyu]> MariaDB [toyu]> SET @major = 'CINF'; Query OK, 0 rows affected (0.001 sec) MariaDB [toyu]> SET @ach = 15; Query OK, 0 rows affected (0.000 sec) MariaDB [toyu]> EXECUTE stmt USING @major, @ach; +--------+---------+---------+-------+-------+------+---------+ | stuId | fname | lname | major | minor | ach | advisor | +--------+---------+---------+-------+-------+------+---------+ | 100006 | Lillian | Johnson | CINF | ITEC | 18 | 1016 | +--------+---------+---------+-------+-------+------+---------+ 1 row in set (0.001 sec) MariaDB [toyu]> MariaDB [toyu]> SET @major = 'ITEC'; Query OK, 0 rows affected (0.000 sec) MariaDB [toyu]> SET @ach = 25; Query OK, 0 rows affected (0.000 sec) MariaDB [toyu]> EXECUTE stmt USING @major, @ach; +--------+-------+---------+-------+-------+------+---------+ | stuId | fname | lname | major | minor | ach | advisor | +--------+-------+---------+-------+-------+------+---------+ | 100004 | Larry | Johnson | ITEC | NULL | 66 | 1017 | +--------+-------+---------+-------+-------+------+---------+ 1 row in set (0.001 sec) MariaDB [toyu]> notee