-- logging.

mysql -u yue -p

use toyu;

SELECT * FROM student;

tee 2015_1_14_sql_log.txt

show databases;

DROP SCHEMA IF EXISTS toyu;

source createtoyu.sql

[1] List the stuId, names, major departments and minor code of all students
minoring in CSCI, CINF or ITEC in the following manner.
+--------+-----------------+------------------------------+-------+
| stuId | student | major | minor |
+--------+-----------------+------------------------------+-------+
| 100000 | Tony Hawk | Computer Science | CINF |
| 100001 | Mary Hawk | Computer Science | CINF |
| 100002 | David Hawk | Computer Science | ITEC |
| 100003 | Catherine Lim | Information Technology | CINF |
| 100006 | Lillian Johnson | Computer Information Systems | ITEC |
| 100009 | Linda King | Arts | CSCI |
+--------+-----------------+------------------------------+-------+
6 rows in set)


SELECT DISTINCT stuId, fname, lname, major, minor
FROM student;

-- 1c.
SELECT DISTINCT stuId, 
    CONCAT(fname, ' ', lname), 
    major, minor
FROM student;

SELECT DISTINCT stuId, 
    CONCAT(fname, ' ', lname) AS student, -- alias (column label)
    major, minor
FROM student;

[1] Output columns: label: value
1.	stuId
2.	student: fname concatenate with ‘space’ and lname, CONCAT(fname, ' ', lname), e.g: Tony Hawk: 
3.	major: department.deptName
4.	minor
[2] Sources: 
1.	student
2.	department
[3] Conditions: 
•	Join condition: student.major = department.deptCode

SELECT DISTINCT stuId, 
    CONCAT(fname, ' ', lname) AS student, -- alias (column label)
    department.deptName AS major, minor
FROM student, department
WHERE student.major = department.deptCode;