MariaDB [(none)]> exit
MariaDB [(none)]> use toyu;
Database changed
MariaDB [toyu]> SELECT DISTINCT s.stuId,
    -> CONCAT(st.fname, ' ', s.lname) AS student,
    -> s.major,
    -> s.advisor AS advisorId,
    -> -- output
    -> FROM student AS s;
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 AS s' at line 6
MariaDB [toyu]> SELECT DISTINCT s.stuId,
    -> CONCAT(st.fname, ' ', s.lname) AS student,
    -> s.major,
    -> s.advisor AS advisorId
    -> -- output
    -> FROM student AS s;
ERROR 1054 (42S22): Unknown column 'st.fname' in 'field list'
MariaDB [toyu]> SELECT DISTINCT s.stuId,
    -> CONCAT(s.fname, ' ', s.lname) AS student,
    -> s.major,
    -> s.advisor AS advisorId
    -> -- output
    -> FROM student AS s;
+--------+-----------------+-------+-----------+
| stuId  | student         | major | advisorId |
+--------+-----------------+-------+-----------+
| 100000 | Tony Hawk       | CSCI  |      1011 |
| 100001 | Mary Hawk       | CSCI  |      1011 |
| 100002 | David Hawk      | CSCI  |      1012 |
| 100003 | Catherine Lim   | ITEC  |      NULL |
| 100004 | Larry Johnson   | ITEC  |      1017 |
| 100005 | Linda Johnson   | CINF  |      1015 |
| 100006 | Lillian Johnson | CINF  |      1016 |
| 100007 | Ben Zico        | NULL  |      NULL |
| 100008 | Bill Ching      | ARTS  |      NULL |
| 100009 | Linda King      | ARTS  |      1018 |
| 100111 | Cathy Johanson  | NULL  |      1018 |
+--------+-----------------+-------+-----------+
11 rows in set (0.001 sec)

MariaDB [toyu]> SELECT DISTINCT s.stuId,
    -> CONCAT(s.fname, ' ', s.lname) AS student,
    -> IFNULL(s.major, 'No major'),
    -> s.advisor AS advisorId
    -> -- output
    -> FROM student AS s;
+--------+-----------------+-----------------------------+-----------+
| stuId  | student         | IFNULL(s.major, 'No major') | advisorId |
+--------+-----------------+-----------------------------+-----------+
| 100000 | Tony Hawk       | CSCI                        |      1011 |
| 100001 | Mary Hawk       | CSCI                        |      1011 |
| 100002 | David Hawk      | CSCI                        |      1012 |
| 100003 | Catherine Lim   | ITEC                        |      NULL |
| 100004 | Larry Johnson   | ITEC                        |      1017 |
| 100005 | Linda Johnson   | CINF                        |      1015 |
| 100006 | Lillian Johnson | CINF                        |      1016 |
| 100007 | Ben Zico        | No major                    |      NULL |
| 100008 | Bill Ching      | ARTS                        |      NULL |
| 100009 | Linda King      | ARTS                        |      1018 |
| 100111 | Cathy Johanson  | No major                    |      1018 |
+--------+-----------------+-----------------------------+-----------+
11 rows in set (0.001 sec)

MariaDB [toyu]> SELECT DISTINCT CONCAT(f.fname, ' ', f.lname) AS faculty ,
    ->    f.rank,
    ->    d.deptName AS department
    ->    -- output
    -> FROM faculty AS f,
    ->    department AS d,
    ->    school AS s
    ->    -- sources
    -> WHERE -- conditions
    -> f.deptCode = d.deptCode 
    -> AND d.schoolCode = s.schoolCode
    -> AND s.schoolName = Science and Engineering' ;
    '> 
    '> ';
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 '' ;

'' at line 12
MariaDB [toyu]> SELECT DISTINCT CONCAT(f.fname, ' ', f.lname) AS faculty ,
    ->    f.rank,
    ->    d.deptName AS department
    ->    -- output
    -> FROM faculty AS f,
    ->    department AS d,
    ->    school AS s
    ->    -- sources
    -> WHERE -- conditions
    -> f.deptCode = d.deptCode 
    -> AND d.schoolCode = s.schoolCode
    -> AND s.schoolName = 'Science and Engineering' ;
+---------------+---------------------+------------------------------+
| faculty       | rank                | department                   |
+---------------+---------------------+------------------------------+
| Daniel Kim    | Professor           | Computer Information Systems |
| Andrew Byre   | Associate Professor | Computer Information Systems |
| Paul Smith    | Professor           | Computer Science             |
| Mary Tran     | Associate Professor | Computer Science             |
| David Love    | NULL                | Computer Science             |
| Sharon Mannes | Assistant Professor | Computer Science             |
| Deborah Gump  | Professor           | Information Technology       |
| Benjamin Yu   | Lecturer            | Information Technology       |
+---------------+---------------------+------------------------------+
8 rows in set (0.004 sec)

MariaDB [toyu]> 
MariaDB [toyu]> SELECT DISTINCT CONCAT(f.fname, ' ', f.lname) AS faculty ,
    ->    f.rank,
    ->    d.deptName AS department
    ->    -- output
    -> FROM faculty AS f INNER JOIN department AS d ON (f.deptCode = d.deptCode )
    -> INNER JOIN school AS s ON (d.schoolCode = s.schoolCode)
    ->    -- sources
    -> WHERE  s.schoolName = 'Science and Engineering' ;
+---------------+---------------------+------------------------------+
| faculty       | rank                | department                   |
+---------------+---------------------+------------------------------+
| Daniel Kim    | Professor           | Computer Information Systems |
| Andrew Byre   | Associate Professor | Computer Information Systems |
| Paul Smith    | Professor           | Computer Science             |
| Mary Tran     | Associate Professor | Computer Science             |
| David Love    | NULL                | Computer Science             |
| Sharon Mannes | Assistant Professor | Computer Science             |
| Deborah Gump  | Professor           | Information Technology       |
| Benjamin Yu   | Lecturer            | Information Technology       |
+---------------+---------------------+------------------------------+
8 rows in set (0.001 sec)

MariaDB [toyu]> SELECT DISTINCT d.deptCode, d.deptName,
    -> CONCAT(f.fname, ' ', f.lname) AS faculty 
    -> FROM department AS d INNER JOIN faculty AS f ON (d.deptCode = f.deptCode);
+----------+------------------------------+-----------------+
| deptCode | deptName                     | faculty         |
+----------+------------------------------+-----------------+
| CSCI     | Computer Science             | Paul Smith      |
| CSCI     | Computer Science             | Mary Tran       |
| CSCI     | Computer Science             | David Love      |
| CSCI     | Computer Science             | Sharon Mannes   |
| CINF     | Computer Information Systems | Daniel Kim      |
| CINF     | Computer Information Systems | Andrew Byre     |
| ITEC     | Information Technology       | Deborah Gump    |
| ARTS     | Arts                         | Art Allister    |
| ITEC     | Information Technology       | Benjamin Yu     |
| ENGL     | English                      | Katrina Bajaj   |
| ACCT     | Accounting                   | Jorginlo Neymar |
+----------+------------------------------+-----------------+
11 rows in set (0.001 sec)

MariaDB [toyu]> SELECT DISTINCT d.deptCode, d.deptName,
    -> CONCAT(f.fname, ' ', f.lname) AS faculty 
    -> FROM department AS d INNER JOIN faculty AS f ON (d.deptCode = f.deptCode)
    -> ORDER BY d.deptCode ASC;
+----------+------------------------------+-----------------+
| deptCode | deptName                     | faculty         |
+----------+------------------------------+-----------------+
| ACCT     | Accounting                   | Jorginlo Neymar |
| ARTS     | Arts                         | Art Allister    |
| CINF     | Computer Information Systems | Daniel Kim      |
| CINF     | Computer Information Systems | Andrew Byre     |
| CSCI     | Computer Science             | Paul Smith      |
| CSCI     | Computer Science             | Mary Tran       |
| CSCI     | Computer Science             | David Love      |
| CSCI     | Computer Science             | Sharon Mannes   |
| ENGL     | English                      | Katrina Bajaj   |
| ITEC     | Information Technology       | Deborah Gump    |
| ITEC     | Information Technology       | Benjamin Yu     |
+----------+------------------------------+-----------------+
11 rows in set (0.001 sec)

MariaDB [toyu]> 
MariaDB [toyu]> SELECT DISTINCT d.deptCode, d.deptName,
    -> CONCAT(f.fname, ' ', f.lname) AS faculty 
    -> FROM department AS d LEFT JOIN faculty AS f ON (d.deptCode = f.deptCode)
    -> ORDER BY d.deptCode ASC;
+----------+------------------------------+-----------------+
| deptCode | deptName                     | faculty         |
+----------+------------------------------+-----------------+
| ACCT     | Accounting                   | Jorginlo Neymar |
| ARTS     | Arts                         | Art Allister    |
| CINF     | Computer Information Systems | Daniel Kim      |
| CINF     | Computer Information Systems | Andrew Byre     |
| CSCI     | Computer Science             | Paul Smith      |
| CSCI     | Computer Science             | Mary Tran       |
| CSCI     | Computer Science             | David Love      |
| CSCI     | Computer Science             | Sharon Mannes   |
| ENGL     | English                      | Katrina Bajaj   |
| ITEC     | Information Technology       | Deborah Gump    |
| ITEC     | Information Technology       | Benjamin Yu     |
| MATH     | Mathematics                  | NULL            |
+----------+------------------------------+-----------------+
12 rows in set (0.001 sec)

MariaDB [toyu]> SELECT DISTINCT d.deptCode, d.deptName,
    -> IFNULL(CONCAT(f.fname, ' ', f.lname), 'Santa Claus') AS faculty 
    -> FROM department AS d LEFT JOIN faculty AS f ON (d.deptCode = f.deptCode)
    -> ORDER BY d.deptCode ASC;
+----------+------------------------------+-----------------+
| deptCode | deptName                     | faculty         |
+----------+------------------------------+-----------------+
| ACCT     | Accounting                   | Jorginlo Neymar |
| ARTS     | Arts                         | Art Allister    |
| CINF     | Computer Information Systems | Daniel Kim      |
| CINF     | Computer Information Systems | Andrew Byre     |
| CSCI     | Computer Science             | Paul Smith      |
| CSCI     | Computer Science             | Mary Tran       |
| CSCI     | Computer Science             | David Love      |
| CSCI     | Computer Science             | Sharon Mannes   |
| ENGL     | English                      | Katrina Bajaj   |
| ITEC     | Information Technology       | Deborah Gump    |
| ITEC     | Information Technology       | Benjamin Yu     |
| MATH     | Mathematics                  | Santa Claus     |
+----------+------------------------------+-----------------+
12 rows in set (0.001 sec)

MariaDB [toyu]> notee