Introduction to SQL

by K. Yue

1. Introduction

• SQL (Structured Query Language): defacto standard for relational databases.
• SQL-like languages are also used in non-relational DBMS.
• Contains core specifications and extensions. Latest SQL standard: 2016.
• Not pure relational model: e.g.
  1. Use the terms row, column, and table instead of tuple, attribute and relation.
  2. The results may not be a set.
• Mostly based on Tuple Relational Calculus (TRC) and a little on Relational Algebra.
• SQL is mostly declarative.
• DBMS vendor-specific extensions are common.
• SQL Contains:
  • Data Definition Language (DDL): define the relation schema (structure)
  • Data Manipulation Language (DML): manipulate data; CRUD:
    1. Create: Insert
    2. Read
    3. Update
    4. Delete
  • Data Administration Language: for DB administration such as user and security management.

2. MySQL

Toyu: A drastically simplified university.

ER Diagram (created by using MySQL Workbench): Entity-Relationship Diagram to be elaborated elsewhere.

MS Access: toyu.accdb

MySQL creation script: Createtoyu.sql.txt.

DDL:

• Make sure that you are familiar with the core SQL Data Definition Language (DDL) commands. Refer to, for example: http://www.w3schools.com/sql/default.asp.
• MariaDB DDL: https://mariadb.com/kb/en/sql-statements/
• MySQL DDL manual: https://dev.mysql.com/doc/refman/8.0/en/sql-statements.html.
• Basic DDL: examples
  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
• Constraints: 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 condition 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:

CREATE TABLE s2
SELECT * FROM student;

SELECT *
FROM s2;

-- Note that keys and constraints of student are missing in s2.
desc s2;
desc student;

DROP TABLE s2;

show tables; 

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:

1. In MySQL, use back-quote: `first name`
2. 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. Induces: 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>>

• 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.

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, which is a key word in SQL.

Example:

mysql> 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 |    NULL |
| 100008 | Bill      | Ching   | ARTS  | ENGL  |      90 |    1018 |
| 100009 | Linda     | King    | ARTS  | CSCI  |     125 |    1018 |
+--------+-----------+---------+-------+-------+---------+---------+
10 rows in set (0.00 sec)

mysql>
mysql> INSERT INTO student VALUES
    -> (100010,'Bun','Yue',null,null,50,null),
    -> (100011,'Paul','Harris','CSCI','ITEC',23,1015);
Query OK, 2 rows affected (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql>
mysql> -- New content of the table student.
mysql> 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 |    NULL |
| 100008 | Bill      | Ching   | ARTS  | ENGL  |      90 |    1018 |
| 100009 | Linda     | King    | ARTS  | CSCI  |     125 |    1018 |
| 100010 | Bun       | Yue     | NULL  | NULL  |      50 |    NULL |
| 100011 | Paul      | Harris  | CSCI  | ITEC  |      23 |    1015 |
+--------+-----------+---------+-------+-------+---------+---------+
12 rows in set (0.00 sec)

mysql>
mysql> -- Insert Bun Yue and another row again.
mysql> INSERT INTO student VALUES
    -> (100010,'Bun','Yue',null,null,50,null);
ERROR 1062 (23000): Duplicate entry '100010' for key 'PRIMARY'
mysql> INSERT INTO student VALUES
    -> (100015,'Sam','Harrison','CSCI','ITEC',23,2015);
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`toyu`.`student`, CONSTRAINT `Student_advisor_fk` FOREIGN KEY (`advisor`) REFERENCES `faculty` (`facid`))
mysql>
mysql> -- Remove the two new rows.
mysql> DELETE FROM Student
    -> WHERE stuId = 100010 OR stuId = 100011;
Query OK, 2 rows affected (0.01 sec)

mysql>
mysql> -- New content of the table student.
mysql> 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 |    NULL |
| 100008 | Bill      | Ching   | ARTS  | ENGL  |      90 |    1018 |
| 100009 | Linda     | King    | ARTS  | CSCI  |     125 |    1018 |
+--------+-----------+---------+-------+-------+---------+---------+
10 rows in set (0.00 sec)

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>> -- [3] construct result columns
FROM <<source_tables>> -- [1] conceptually join sources to form a large table
WHERE <<conditions_for_inclusion>> -- [2] Filter rows

1. <<source_tables>>: the source tables to gather the result data
2. <<conditions_for_inclusion>>: the conditions to be satisfied for results to be included and the conditions the tables should be connected together.
3. <<result_columns>>: the result columns or expressions desired to be displayed.

• 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.
  • wild cards:
    • % match any and all following characters.
    • _: match any one character.

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 AS s
WHERE EXISTS
(SELECT *
FROM Enroll e
WHERE e.stuId = s.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:

-MariaDB [toyu]> select * from student;
+--------+-----------+---------+-------+-------+---------+---------+
| stuId  | fname     | lname   | major | minor | credits | advisor |
+--------+-----------+---------+-------+-------+---------+---------+
| 100000 | Tony      | Hawk    | ITEC  | 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]> select distinct *
    -> from Student
    -> where lname like '%k%';
+--------+-------+-------+-------+-------+---------+---------+
| stuId  | fname | lname | major | minor | credits | advisor |
+--------+-------+-------+-------+-------+---------+---------+
| 100000 | Tony  | Hawk  | ITEC  | CINF  |      40 |    1011 |
| 100001 | Mary  | Hawk  | CSCI  | CINF  |      35 |    1011 |
| 100002 | David | Hawk  | CSCI  | ITEC  |      66 |    1011 |
| 100009 | Linda | King  | ARTS  | CSCI  |     125 |    1018 |
+--------+-------+-------+-------+-------+---------+---------+
4 rows in set (0.00 sec)

MariaDB [toyu]> select distinct *
    -> from Student
    -> where lname collate latin1_general_cs like '%k%';
+--------+-------+-------+-------+-------+---------+---------+
| stuId  | fname | lname | major | minor | credits | advisor |
+--------+-------+-------+-------+-------+---------+---------+
| 100000 | Tony  | Hawk  | ITEC  | CINF  |      40 |    1011 |
| 100001 | Mary  | Hawk  | CSCI  | CINF  |      35 |    1011 |
| 100002 | David | Hawk  | CSCI  | ITEC  |      66 |    1011 |
+--------+-------+-------+-------+-------+---------+---------+
3 rows in set (0.00 sec)

MariaDB [toyu]>
MariaDB [toyu]> select distinct *
    -> from Student
    -> where lname like '%ng';
+--------+-------+-------+-------+-------+---------+---------+
| stuId  | fname | lname | major | minor | credits | advisor |
+--------+-------+-------+-------+-------+---------+---------+
| 100008 | Bill  | Ching | ARTS  | ENGL  |      90 |    1018 |
| 100009 | Linda | King  | ARTS  | CSCI  |     125 |    1018 |
+--------+-------+-------+-------+-------+---------+---------+
2 rows in set (0.00 sec)

MariaDB [toyu]>
MariaDB [toyu]> select distinct *
    -> from Student
    -> where lname like 'ng';
Empty set (0.00 sec)

MariaDB [toyu]>
MariaDB [toyu]> select distinct *
    -> from Student
    -> where lname like '__ng';
+--------+-------+-------+-------+-------+---------+---------+
| stuId  | fname | lname | major | minor | credits | advisor |
+--------+-------+-------+-------+-------+---------+---------+
| 100009 | Linda | King  | ARTS  | CSCI  |     125 |    1018 |
+--------+-------+-------+-------+-------+---------+---------+
1 row in set (0.00 sec)

MariaDB [toyu]>
MariaDB [toyu]> select distinct *
    -> from Student
    -> where lname like '___ng';
+--------+-------+-------+-------+-------+---------+---------+
| stuId  | fname | lname | major | minor | credits | advisor |
+--------+-------+-------+-------+-------+---------+---------+
| 100008 | Bill  | Ching | ARTS  | ENGL  |      90 |    1018 |
+--------+-------+-------+-------+-------+---------+---------+
1 row in set (0.00 sec)

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:

mysql> SELECT DISTINCT s.fname, s.lname, c.classId, e.grade
    -> FROM student s, enroll e, class c
    -> WHERE s.stuId = e.stuId   -- Join condition
    -> AND e.classId = c.classId -- Join condition
    -> AND c.semester = 'Fall'   -- problem condition
    -> AND c.year = 2019;        -- problem condition
+---------+---------+---------+-------+
| 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 | C     |
| David   | Hawk    |   10003 | D     |
| 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 | F     |
| Bill    | Ching   |   10007 | C-    |
| Ben     | Zico    |   10008 | A-    |
+---------+---------+---------+-------+
21 rows in set (0.00 sec)

Join

• In the Select Statement, the from clause allows the results of join statement in the table references.
• Using the join clauses are the preferred techniques:
  • Potential faster performance: better optimization by DB engine, especially when using index.
  • Better style: separation of join conditions and query logic condition
  • Easier changes among different joins.
• There are many kind of joins. Experiment with them.
• You may use the Explain statement in MySQL to find out the execution plan.

Example:

-- Student names, classes (id) enrolled and their grades in Fall 2019
SELECT DISTINCT s.fname, s.lname, c.classId, e.grade
FROM student s, enroll e, class c
WHERE s.stuId = e.stuId    -- Join condition
AND e.classId = c.classId  -- Join condition
AND c.semester = 'Fall'    -- Problem condition
AND c.year = 2019;         -- Problem condition

-- Student names, classes (id) enrolled and their grades in Fall 2019
SELECT DISTINCT s.fname, s.lname, c.classId, e.grade
FROM student s INNER JOIN enroll e ON (s.stuId = e.stuId)    -- Join condition
    INNER JOIN class c ON (e.classId = c.classId)            -- Join condition
WHERE c.semester = 'Fall'    -- Problem condition
AND c.year = 2019;           -- Problem condition

Left Join

• Left joins are the most popular besides (inner) joins.
• Left Join: same as Inner Join, except that for R1 left join R2 and a tuple t1 in R1 without matching tuples in R2, t1 will be kept in the result with attributes from R2 being null.

Example

-- List the names of the students with their minors (in full name).
-- Student with no department not listed.
SELECT CONCAT(s.fname, ' ', s.lname) AS student,
    d.deptName AS `minor department`
FROM student s INNER JOIN department d ON (s.minor = d.deptCode);

-- List the names of the students with their minors (in full name).
SELECT CONCAT(s.fname, ' ', s.lname) AS student,
    d.deptName AS `minor department`
FROM student s LEFT JOIN department d ON (s.minor = d.deptCode);

-- List the names of the students with their minors (in full name).
-- more readable form.
SELECT CONCAT(s.fname, ' ', s.lname) AS student,
    IF (d.deptName IS NULL, 'N/A', d.deptName) AS `minor department`
FROM student s LEFT JOIN department d ON (s.minor = d.deptCode);

Exercise

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

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

Common Table Expressions (CTE)

• Supported by MySQL 8.0.
• Allow the definition of temporary tables in sequence before the body of a SELECT statement.
• A table defined in CTE can be used immediately until the end of the SELECT statement.
• Support a more natural way to implement algorithm solutions.
• Allow recursion.

Example:

-- CTE
WITH   
    t1 AS (SELECT MAX(credits) AS max FROM student)
SELECT s.stuId,
       s.credits,
       t1.max - s.credits AS `diff from max credits of all`
FROM student AS s, t1
ORDER BY credits DESC;

-- multiple common tables (not efficient; used as demonstration.)
WITH   
    t1 AS (SELECT MAX(credits) AS max FROM student),
    t2 AS
        (SELECT s.stuId,
                s.credits,
                t1.max - s.credits AS diff,
                s.major
        FROM student AS s, t1
        )
SELECT t2.stuId, t2.credits,
    t2.diff AS `diff from max credits of all`,
    d.deptName AS department
FROM t2 LEFT JOIN department d ON (t2.major = d.deptCode)
ORDER BY t2.credits DESC;

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.

Thus, the conceptual steps and framework for SELECT statements:

SELECT DISTINCT <<result_columns>> -- [5] construct result columns
FROM <<source_tables>> -- [1] conceptually join sources to form a large table to produce initial rows
WHERE <<conditions_for_inclusion>> -- [2] Filter initial rows
GROUP BY <<group_by_columns>>
         --[3] group initial rows into groups by values of the group_by_column. A group becomes a new row.
HAVING <<conditions for filtering group>> -- [4] filter groups
ORDER BY <<columns>>; -- [6] Order the result of [5].

Example:

-- Student names and number of courses enrolled. SELECT CONCAT(s.fname, ' ', s.lname) AS student, COUNT(e.classId) AS `Enrolled classes` FROM student AS s JOIN enroll e ON (s.stuId = e.stuId) GROUP BY student HAVING `Enrolled classes` > 2 ORDER BY `Enrolled classes` DESC; 

Exercises:

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

+-----------------+------------------+
| name            | Enrolled classes |
+-----------------+------------------+
| Tony Hawk       |                6 |
| 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)
 

[2] Can you write a query to generate the following output?

+----------+------------------------------+------------+----------+----------+
| 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 |        2 |
| ITEC     | Information Technology       |          2 |        2 |        2 |
| MATH     | Mathematics                  |          0 |        0 |        0 |
+----------+------------------------------+------------+----------+----------+
7 rows in set (0.00 sec)