Introduction to SQL

by K. Yue

1. Introduction

SQL (Structured Query Language): defacto standard for relational databases.
Contains core specifications and extensions.
Not pure relational model: e.g.
- row, column and table instead of tuple, attribute and relation.
- results may not be a set.
Mostly based on Tuple Relational Calculus and some Relational Algebra.
DBMS specific extensions are common.
Contains both DDL (Data Definition Language) and DML (Data Manipulation Language)
Useful resources:
- MySQL 5.7 Manual.
- w3schools: SQL.

2. MySQL and DDL

We will use MySQL. Either download your own version of MySQL (XAMPP or standalone MySQL) or connect to the MySQL server at dcm.uhcl.edu (or dcmweb) at port 3306.

You may use the command line program 'mysql' as MySQL client.

mysql –h host -u user -p

to start your mysql session.

You may also use your favorite SQL clients, such as MySQL Workbench, HeidiSQL, etc.: http://www.slant.co/topics/53/~mysql-client-applications-for-windows.

Note that different DBMS support different versions of SQL. They may not fully support the standards and may include extensions.
For example, MySQL does not support EXCEPT, which is in ANSI SQL 92.
When developing databases in a given DBMS, portability issue is a consideration for your choice of SQL statements.
MySQL is not based on a pure relational model. For example:
- Query results may contain duplicate rows.
- Order may be important.
Easy to start. Not so easy to become an expert.
Essentially declarative and non-procedural.

Exercise on the toy university database, toyu:

Toyu: A drastically simplified university.

ER Diagram:

MS Access: toyu.accdb

MySQL creation script: Createtoyu.sql.txt.

DDL:

Make sure that you are familiar with the core SQL DDL commands. Refer to, for example: http://www.w3schools.com/sql/default.asp.
MySQL DDL manual: https://dev.mysql.com/doc/refman/5.0/en/sql-syntax-data-definition.html.
Basic DDL:
1. CREATE TABLE
2. CREATE DATABASE: a database contains a collection of related tables for an application.
3. CREATE VIEW
4. CREATE INDEX
5. CREATE PROCEDURE
6. CREATE FUNCTION
7. CREATE TRIGGER
8. ALTER (No ALTER TRIGGER and ALTER INDEX)
9. DROP
Constraint: to implement certain constraints in your data model.
1. NOT NULL: attributes cannot have an null value.
2. UNIQUE: KEY; the set of attributes must be unique for each row:
3. PRIMARY KEY: unique, not null, and used for the physical structure of the relation.
4. FOREIGN KEY
5. CHECK: for a Boolean conditions on the columns.
6. DEFAULT: insert a default.
Some other options:
- AUTO INCREMENT
- Limit: number of results returned.

Examples:

DROP TABLE IF EXISTS Enroll;
DROP TABLE IF EXISTS Student;
DROP TABLE IF EXISTS Class;
DROP TABLE IF EXISTS Course;
DROP TABLE IF EXISTS Faculty;
DROP TABLE IF EXISTS Department;

--
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)
);

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));

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,
   CONSTRAINT Course_courseId_pk PRIMARY KEY (courseId));

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
);

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)
);


CREATE TABLE IF NOT EXISTS Enroll(
   stuId      INT NOT NULL,
   classId      INT NOT NULL,
   grade      VARCHAR(2),
   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
);

Notes:

There are restrictions on the syntax (such as the length of table names) which can be implementation specific.

Example:

Columns may include special characters but you will need special syntax. You can not use the name 'first name' directly as column name as spaces are not allowed. Instead:

In MySQL, use backquote: `first name`
In MS SQL Server, use []: [first name]

For each column, there is a data type and optional specifiers (such as NULL, NOT NULL, default values, etc.)
Additional constraints and indexes can be defined.
In general, some important considerations in creating tables:
1. What are the columns?
2. What are the data types of the columns?
  - The right domain.
  - Performance.
3. Nullability of columns
4. the primary key
5. Candidate keys
6. Foreign keys: can they be enforced for the particular database engine?
7. Indexes: performance tuning.
8. Additional constraints: check whether they are enforced by the DBMS.

Example:

MySQL only supports foreign key constraint in the InnoDB database engine.
MySQL ignore the 'check' clause.

3. DML

Basically declarative.
Basic update commands (write):
1. INSERT
2. UPDATE
3. DELETE

INSERT INTO <<table>> [<<columns>>]
VALUES <<expression>>

If column names are missing, the proper column order during table creation will be used.
Column names for default values or auto-increment values should not be included in the INSERT statement.
NULL and DEFAULT can be used as values in INSERT.
One may also insert values from a select statement:

INSERT INTO <<table>> [<<columns>>]
<<select statement>>

Delete statement includes a condition for deletion.

DELETE FROM <<table>>
WHERE <<condition>>

Example:

Note the order of the insertion below.

INSERT INTO DEPARTMENT VALUES
   ('CSCI','Computer Science','CSE',12),
   ('CINF','Computer Information Systems','CSE',5),
   ('ITEC','Information Technology','CSE',4),
   ('ARTS','Arts','HSH',5),
   ('ENGL','English','HSH',12),
   ('ACCT','Accounting','BUS',10);

INSERT INTO FACULTY VALUES('1011','Paul','Smith','CSCI','Professor'),
   ('1012','Mary','Tran','CSCI','Associate Professor'),
   ('1013','David','Love','CSCI','Associate Professor'),
   ('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');

INSERT INTO COURSE VALUES
   (2000, 'CSCI', '3333', 'Data Structures'),
   (2001, 'CSCI', '4333', 'Design of Database Systems'),
   (2002, 'CSCI', '5333', 'DBMS'),
   (2020, 'CINF', '3321', 'Introduction to Information Systems'),
   (2021, 'CINF', '4320', 'Web Application Development'),
   (2040, 'ITEC', '3335', 'Database Development'),
   (2041, 'ITEC', '3312', 'Introduction to Scripting'),
   (2060, 'ENGL', '1310', 'English I'),
   (2061, 'ENGL', '1311', 'English II'),
   (2080, 'ARTS', '3311', 'Hindu Arts'),
   (2090, 'ACCT', '3311', 'Managerial Accounting');


INSERT INTO CLASS VALUES
   (10000,2000,'Fall',2017,1011,'D241'),
   (10001,2001,'Fall',2017,1011,'D242'),
   (10002,2002,'Fall',2017,1012,'D136'),
   (10003,2020,'Fall',2017,1014,'D241'),
   (10004,2021,'Fall',2017,1014,'D241'),
   (10005,2040,'Fall',2017,1015,'D237'),
   (10006,2041,'Fall',2017,1019,'D217'),
   (10007,2060,'Fall',2017,1020,'B101'),
   (10008,2080,'Fall',2017,1018,'D241'),
   (11000,2000,'Spring',2018,1011,'D241'),
   (11001,2001,'Spring',2018,1013,'D242'),
   (11002,2002,'Spring',2018,1013,'D136'),
   (11003,2020,'Spring',2018,1016,'D217'),
   (11004,2061,'Spring',2018,1020,'B101')
;

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,1014),
   (100008,'Bill','Ching','ARTS','ENGL',90,1018),
   (100009,'Linda','King','ARTS','CSCI',125,1018)
;

INSERT INTO ENROLL VALUES
   (100000,10000,'A'),
   (100001,10000,null),
   (100002,10000,'B-'),
   (100000,10001,'A'),
   (100001,10001,'A-'),
   (100000,10002,'B+'),
   (100002,10002,'B+'),
   (100000,10003,'A'),
   (100002,10003,'B-'),
   (100004,10003,'A'),
   (100005,10003,null),
   (100000,10004,'A-'),
   (100004,10004,'B+'),
   (100005,10004,'A-'),
   (100006,10004,'C+'),
   (100005,10005,'A-'),
   (100006,10005,'A'),
   (100005,10006,'B+'),
   (100007,10007,'B+'),
   (100008,10007,'C-'),
   (100007,10008,'A')
;

Note the explicit use of null:

MariaDB [toyu]> select * from student;
+--------+-----------+---------+-------+-------+---------+---------+
| stuId | fname     | lname   | major | minor | credits | advisor |
+--------+-----------+---------+-------+-------+---------+---------+
| 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 |    1014 |
| 100008 | Bill      | Ching   | ARTS | ENGL |      90 |    1018 |
| 100009 | Linda     | King    | ARTS | CSCI |     125 |    1018 |
| 100010 | Bun       | Yue     | NULL | NULL |      50 |    NULL |
| 100011 | Paul      | Harris | NULL | NULL |      23 |    NULL |
+--------+-----------+---------+-------+-------+---------+---------+
12 rows in set (0.00 sec)

MariaDB [toyu]> insert into STUDENT VALUES
    -> (100010,'Bun','Yue',null,null,50,null);
ERROR 1062 (23000): Duplicate entry '100010' for key 'PRIMARY'
MariaDB [toyu]> insert into STUDENT(StuId, fname, lname, credits) VALUES
    -> (100010,'Paul','Harris',23);
ERROR 1062 (23000): Duplicate entry '100010' for key 'PRIMARY'
MariaDB [toyu]> insert into STUDENT(StuId, fname, lname, credits) VALUES
    -> (100011,'Paul','Harris',23);
ERROR 1062 (23000): Duplicate entry '100011' for key 'PRIMARY'
MariaDB [toyu]> select * from student;
+--------+-----------+---------+-------+-------+---------+---------+
| stuId | fname     | lname   | major | minor | credits | advisor |
+--------+-----------+---------+-------+-------+---------+---------+
| 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 |    1014 |
| 100008 | Bill      | Ching   | ARTS | ENGL |      90 |    1018 |
| 100009 | Linda     | King    | ARTS | CSCI |     125 |    1018 |
| 100010 | Bun       | Yue     | NULL | NULL |      50 |    NULL |
| 100011 | Paul      | Harris | NULL | NULL |      23 |    NULL |
+--------+-----------+---------+-------+-------+---------+---------+
12 rows in set (0.00 sec)

Example: Entity integrity.

MariaDB [toyu]> select * from student;
+--------+-----------+---------+-------+-------+---------+---------+
| stuId | fname     | lname   | major | minor | credits | advisor |
+--------+-----------+---------+-------+-------+---------+---------+
| 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 |    1014 |
| 100008 | Bill      | Ching   | ARTS | ENGL |      90 |    1018 |
| 100009 | Linda     | King    | ARTS | CSCI |     125 |    1018 |
| 100010 | Bun       | Yue     | NULL | NULL |      50 |    NULL |
| 100011 | Paul      | Harris | NULL | NULL |      23 |    NULL |
+--------+-----------+---------+-------+-------+---------+---------+
12 rows in set (0.00 sec)

MariaDB [toyu]> insert into STUDENT VALUES
    -> (100010,'Bun','Yue',null,null,50,null);
ERROR 1062 (23000): Duplicate entry '100010' for key 'PRIMARY'
MariaDB [toyu]> insert into STUDENT(StuId, fname, lname, credits) VALUES
    -> (100010,'Paul','Harris',23);
ERROR 1062 (23000): Duplicate entry '100010' for key 'PRIMARY'
MariaDB [toyu]> insert into STUDENT(StuId, fname, lname, credits) VALUES
    -> (100011,'Paul','Harris',23);
ERROR 1062 (23000): Duplicate entry '100011' for key 'PRIMARY'
MariaDB [toyu]> select * from student;
+--------+-----------+---------+-------+-------+---------+---------+
| stuId | fname     | lname   | major | minor | credits | advisor |
+--------+-----------+---------+-------+-------+---------+---------+
| 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 |    1014 |
| 100008 | Bill      | Ching   | ARTS | ENGL |      90 |    1018 |
| 100009 | Linda     | King    | ARTS | CSCI |     125 |    1018 |
| 100010 | Bun       | Yue     | NULL | NULL |      50 |    NULL |
| 100011 | Paul      | Harris | NULL | NULL |      23 |    NULL |
+--------+-----------+---------+-------+-------+---------+---------+
12 rows in set (0.00 sec)

Try:

create or replace table activeStudent(
   stuId      INT NOT NULL,
   lname       VARCHAR(20) NOT NULL,
   fname       VARCHAR(20) NOT NULL,
   numCourses INT DEFAULT 0
);

select * from activeStudent;

insert into activeStudent(stuId, lname, fname, numCourses)
select s.stuId, s.lname, s.fName, count(e.classId) as numCourses
from Student s natural join Enroll e
group by s.stuId, s.lname, s.fname;

select * from activeStudent;

drop table activeStudent;

The update statement is used to update rows and can have an update condition.

UPDATE <<table>>
SET <<update assignments>>
[WHERE <<update condition>>]

Example:

update Student
SET major = 'ITEC'
where StuId = 100000;

The update statements can be used to update multiple rows so be careful.

Example:

-- All students will be majoring in CSCI
update Student
set major = 'CSCI';

Once changed, the effect is permanent. There is no 'undo' command.

The Select Statement

Basic data retrieval statement in SQL
Not to be confused with the select statement in Relational Algebra.
Basic format:

select distinct <result columns>
from <sources>
where <conditions>;

Some special operations:
1. BETWEEN lower_range AND upper_range
2. IN: membership test for a set/table (binary operation)
3. EXISTS: not an empty set (unary operation)
4. LIKE:inexact string matching.

Example:

-- student with credits in a range.
select *
from Student
where credits between 30 and 70;

-- student in selected majors
select *
from Student
where major in ('CSCI', 'CINF', 'ITEC');

-- student enrolled in some classes.
select *
from Student
where exists
   (select *
    from Enroll
    where enroll.stuId = Student.stuId);

Exercise:

[1] Provide another version for the query: student enrolled in some classes.

[2] Give a SQL statement for students not enrolled in any class.

Example:

Notes:

The AS clause can be used to define alias, which can then be referred to.
Backtick (`) can be used to refer to columns or names with spaces or special characters.

Multiple Tables

When multiple tables are needed for a query, it is common that foreign keys are used to connect the tables.
It is thus necessary to ensure that the equality of the foreign key with the key of the referenced table.

Example:

MariaDB [toyu]> -- Multiple tables, alias, expressions, and order by
MariaDB [toyu]>
MariaDB [toyu]> -- Student names and grades of the class with id 1004.
MariaDB [toyu]> select distinct s.fname, s.lname, e.grade
    -> from student s, enroll e
    -> where s.stuId = e.stuId
    -> and classId = 10004;
+---------+---------+-------+
| fname   | lname   | grade |
+---------+---------+-------+
| Tony    | Hawk    | A-    |
| Larry   | Johnson | B+    |
| Linda   | Johnson | A-    |
| Lillian | Johnson | C+    |
+---------+---------+-------+
4 rows in set (0.00 sec)

MariaDB [toyu]>
MariaDB [toyu]> -- Student names and grades of the class with id 1004.
MariaDB [toyu]> -- order by grade
MariaDB [toyu]> select distinct s.fname, s.lname, e.grade
    -> from student s, enroll e
    -> where s.stuId = e.stuId
    -> and classId = 10004
    -> order by e.grade;
+---------+---------+-------+
| fname   | lname   | grade |
+---------+---------+-------+
| Tony    | Hawk    | A-    |
| Linda   | Johnson | A-    |
| Larry   | Johnson | B+    |
| Lillian | Johnson | C+    |
+---------+---------+-------+
4 rows in set (0.00 sec)

MariaDB [toyu]>
MariaDB [toyu]> -- Student names and grades of the class with id 1004.
MariaDB [toyu]> -- order by grade
MariaDB [toyu]> select distinct concat(s.fname, ' ', s.lname),
    -> e.grade
    -> from student s, enroll e
    -> where s.stuId = e.stuId
    -> and classId = 10004
    -> order by e.grade;
+-------------------------------+-------+
| concat(s.fname, ' ', s.lname) | grade |
+-------------------------------+-------+
| Tony Hawk                     | A-    |
| Linda Johnson                 | A-    |
| Larry Johnson                 | B+    |
| Lillian Johnson               | C+    |
+-------------------------------+-------+
4 rows in set (0.00 sec)

MariaDB [toyu]>
MariaDB [toyu]> -- Student names and grades of the class with id 1004.
MariaDB [toyu]> -- order by grade
MariaDB [toyu]> select distinct concat(s.fname, ' ', s.lname) as name,
    -> e.grade
    -> from student s, enroll e
    -> where s.stuId = e.stuId
    -> and classId = 10004
    -> order by e.grade;
+-----------------+-------+
| name            | grade |
+-----------------+-------+
| Tony Hawk       | A-    |
| Linda Johnson   | A-    |
| Larry Johnson   | B+    |
| Lillian Johnson | C+    |
+-----------------+-------+
4 rows in set (0.00 sec)

MariaDB [toyu]>
MariaDB [toyu]> -- Student names, classes (id) enrolled and their grades
MariaDB [toyu]> -- in Fall 2017
MariaDB [toyu]> select distinct s.fname, s.lname, c.classId, e.grade
    -> from student s, enroll e, class c
    -> where s.stuId = e.stuId
    -> and e.classId = c.classId
    -> and c.semester = 'Fall'
    -> and c.year = 2017;
+---------+---------+---------+-------+
| fname   | lname   | classId | grade |
+---------+---------+---------+-------+
| Tony    | Hawk    |   10000 | A     |
| Mary    | Hawk    |   10000 | NULL |
| David   | Hawk    |   10000 | B-    |
| Tony    | Hawk    |   10001 | A     |
| Mary    | Hawk    |   10001 | A-    |
| Tony    | Hawk    |   10002 | B+    |
| David   | Hawk    |   10002 | B+    |
| Tony    | Hawk    |   10003 | A     |
| David   | Hawk    |   10003 | B-    |
| Larry   | Johnson |   10003 | A     |
| Linda   | Johnson |   10003 | NULL |
| Tony    | Hawk    |   10004 | A-    |
| Larry   | Johnson |   10004 | B+    |
| Linda   | Johnson |   10004 | A-    |
| Lillian | Johnson |   10004 | C+    |
| Linda   | Johnson |   10005 | A-    |
| Lillian | Johnson |   10005 | A     |
| Linda   | Johnson |   10006 | B+    |
| Ben     | Zico    |   10007 | B+    |
| Bill    | Ching   |   10007 | C-    |
| Ben     | Zico    |   10008 | A     |
+---------+---------+---------+-------+
21 rows in set (0.00 sec)

MariaDB [toyu]>
MariaDB [toyu]> -- Student names and grades of the class ITEC 3335 in
MariaDB [toyu]> -- Fall 2017.
MariaDB [toyu]> select distinct s.fname, s.lname, e.grade
    -> from student s, enroll e, class c, course co
    -> where s.stuId = e.stuId
    -> and e.classId = c.classId
    -> and c.courseId = co.courseId
    -> and co.rubric = 'ITEC'
    -> and co.number = '3335'
    -> and c.semester = 'Fall'
    -> and c.year = 2017;
+---------+---------+-------+
| fname   | lname   | grade |
+---------+---------+-------+
| Linda   | Johnson | A-    |
| Lillian | Johnson | A     |
+---------+---------+-------+
2 rows in set (0.00 sec)

Subqueries and join

In the Select Statement, the from clause allows the results of join statements in the table references.
Using join clauses can sometimes be faster as it provides more information for the DB engines to optimize.
There are many kind of joins: inner join, outer joint, etc. Experiment with them.
You may use the Explain statement in MySQL to find out the execution plan.

Example:

-- Student names and grades of the class ITEC 3335 in
-- Fall 2017.
select distinct s.fname, s.lname, e.grade
from student s join enroll e on (s.stuId = e.stuId)
   join class c on (e.classId = c.classId)
   join course co on (c.courseId = co.courseId)
where co.rubric = 'ITEC'
and co.number = '3335'
and c.semester = 'Fall'
and c.year = 2017;

-- Student names and major department name (not code)
select concat(s.fname, ' ', s.lname) as name,
   d.deptName as `department name`
from student s, department d
where s.major = d.deptCode
order by `department name`;

-- Student names and major department name (not code)
-- using join.
select concat(s.fname, ' ', s.lname) as name,
   d.deptName as `department name`
from student s join department d on (s.major = d.deptCode)
order by `department name`;

Exercise

[1] Student names and major department name, included those with unknown majors.

[2] Repeat one, for those unknown majors, lists them as 'Undeclared.'

Group by and having:

Useful for group reports: one result row per group, not per row as in the regular select statement.
Allow aggregate functions (group functions, column functions) to be performed by the groups defined.
Output one tuple per group.
A group is defined by an unique value of the columns in the group by clause.
Example aggregate functions: MAX, MIN, AVG, COUNT, SUM, GROUP_CONCAT, etc.
The HAVING clause allows using aggregate functions in the condition. WHERE clause does not.
Using GROUP BY, the columns of the SELECT clause can only have:
1. Columns named in GROUP BY.
2. Aggregate functions on other columns in the tables.
3. Constant expressions.
A number can be used in the group by and order by clauses to refer to the result columns in the select clauses.

Example:

-- Student id and number of courses enrolled.
select e.stuId, count(e.classId) as `Enrolled classes`
from Enroll e
group by e.stuId;

-- Student names and number of courses enrolled.
select concat(s.fname, ' ', s.lname) as name,
count(e.classId) as `Enrolled classes`
from Student s join Enroll e on (s.stuId = e.stuId)
group by name
order by `Enrolled classes` desc;

Exercises:

Can you write a query to generate the Student names and number of courses enrolled, including those not enrolled?

+-----------------+------------------+
| name            | Enrolled classes |
+-----------------+------------------+
| Tony Hawk       |                5 |
| Linda Johnson   |                4 |
| David Hawk      |                3 |
| Ben Zico        |                2 |
| Larry Johnson   |                2 |
| Mary Hawk       |                2 |
| Lillian Johnson |                2 |
| Bill Ching      |                1 |
| Catherine Lim   |                0 |
| Linda King      |                0 |
+-----------------+------------------+
10 rows in set (0.00 sec)