MariaDB [(none)]> use toyu
Database changed
MariaDB [toyu]> SELECT DISTINCT s.stuId,
    ->     CONCAT(s.fname, ' ', s.lname) AS student,
    ->     s.ach,
    ->     IFNULL(CONCAT(f.fname, ' ', f.lname), 'N/A') AS advisor
    -> FROM student AS s LEFT JOIN faculty AS f ON (s.advisor = f.facId)
    -> WHERE s.major = 'CSCI' ;
+--------+------------+------+------------+
| stuId  | student    | ach  | advisor    |
+--------+------------+------+------------+
| 100000 | Tony Hawk  |   40 | Paul Smith |
| 100001 | Mary Hawk  |   35 | Paul Smith |
| 100002 | David Hawk |   66 | Mary Tran  |
+--------+------------+------+------------+
3 rows in set (0.003 sec)

MariaDB [toyu]>