An Introduction to
the Relational Model and SQL

by K. Yue

1. Introduction

• There are many data models employed by database systems.
• (Logical) Data models defines how data is organized, connected, processed and queried conceptually.
• It is important to recognize the basic data structures used by these models.
• Examples:
  1. Hierarchical model: tree
  2. Network model: graph
  3. Object-Oriented model: directed graph
  4. XML: tree with many different types of nodes, plus sets of attributes.
  5. Google's Big Table: sparse, distributed multi-dimensional sorted maps.
  6. mongoDB: document
  7. Excel: table, and to be more exact, a two dimensional array.
• Some advantages of the relational model and relational DBMS:
  1. Strong mathematical foundation
  2. Simple
  3. Data independence: separation of the logical and physical databases.
  4. Strong design theory
  5. Strong practical support and community
  6. High popularity
• Some disadvantages of the relational model and relational DBMS:
  1. The data model may not match the problem requirements well.
  2. May not scale well.
  3. May be too restrictive.
• The basic relational data model in layman terms:
• A database is composed of a collection of tables (relations).
• A table contains many rows (tuples) and columns (attributes)
• Each row contains many column values.
• Every row of a table has the same columns.
• Values of the same column have the same data type.
• A candidate key of a table is a minimal identifier of a row in the table.
• A primary key is a selected candidate key (for storing the table).
• A foreign key of a relation refers to a primary key of another relation (known as the parent table).

Example:

Toyu: A drastically simplified university: toyu

Relationship Diagram (in MS Access):

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

MS Access: toyu.accdb

MySQL creation script: Createtoyu.sql.txt.

• A simple SELECT statement in SQL an be used to retrieve data from a table.

mysql> SELECT * FROM grade;
+-------+------------+
| grade | gradePoint |
+-------+------------+
| A     |     4.0000 |
| A-    |     3.6667 |
| B     |     3.0000 |
| B+    |     3.3333 |
| B-    |     2.6667 |
| C     |     2.0000 |
| C+    |     2.3333 |
| C-    |     1.6667 |
| D     |     1.0000 |
| D+    |     1.3333 |
| D-    |     0.6667 |
| F     |     0.0000 |
| WX    |       NULL |
+-------+------------+
13 rows in set (0.00 sec)

mysql> SELECT * FROM school;
+------------+-------------------------------+
| schoolCode | schoolName                    |
+------------+-------------------------------+
| BUS        | Business                      |
| EDU        | Education                     |
| HSH        | Human Sciences and Humanities |
| CSE        | Science and Engineering       |
+------------+-------------------------------+
4 rows in set (0.00 sec)

mysql> SELECT * FROM department;
+----------+------------------------------+------------+------------+
| deptCode | deptName                     | schoolCode | numFaculty |
+----------+------------------------------+------------+------------+
| ACCT     | Accounting                   | BUS        |         10 |
| ARTS     | Arts                         | HSH        |          5 |
| CINF     | Computer Information Systems | CSE        |          5 |
| CSCI     | Computer Science             | CSE        |         12 |
| ENGL     | English                      | HSH        |         12 |
| ITEC     | Information Technology       | CSE        |          4 |
| MATH     | Mathematics                  | CSE        |          7 |
+----------+------------------------------+------------+------------+
7 rows in set (0.00 sec)

mysql> SELECT * FROM faculty;
+-------+----------+----------+----------+---------------------+
| facId | fname    | lname    | deptCode | rank                |
+-------+----------+----------+----------+---------------------+
|  1011 | Paul     | Smith    | CSCI     | Professor           |
|  1012 | Mary     | Tran     | CSCI     | Associate Professor |
|  1013 | David    | Love     | CSCI     |                     |
|  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 |
+-------+----------+----------+----------+---------------------+
11 rows in set (0.00 sec)

mysql> SELECT * FROM course;
+----------+--------+--------+-------------------------------------+---------+
| courseId | rubric | number | name                                | credits |
+----------+--------+--------+-------------------------------------+---------+
|     2000 | CSCI   | 3333   | Data Structures                     |       3 |
|     2001 | CSCI   | 4333   | Design of Database Systems          |       3 |
|     2002 | CSCI   | 5333   | DBMS                                |       3 |
|     2020 | CINF   | 3321   | Introduction to Information Systems |       3 |
|     2021 | CINF   | 4320   | Web Application Development         |       3 |
|     2040 | ITEC   | 3335   | Database Development                |       3 |
|     2041 | ITEC   | 3312   | Introduction to Scripting           |       3 |
|     2060 | ENGL   | 1410   | English I                           |       4 |
|     2061 | ENGL   | 1311   | English II                          |       3 |
|     2080 | ARTS   | 3311   | Hindu Arts                          |       3 |
|     2090 | ACCT   | 3333   | Managerial Accounting               |       3 |
+----------+--------+--------+-------------------------------------+---------+
11 rows in set (0.00 sec)

mysql> SELECT * FROM `class`;
+---------+----------+----------+------+-------+------+
| classId | courseId | semester | year | facId | room |
+---------+----------+----------+------+-------+------+
|   10000 |     2000 | Fall     | 2019 |  1011 | D241 |
|   10001 |     2001 | Fall     | 2019 |  1011 | D242 |
|   10002 |     2002 | Fall     | 2019 |  1012 | D136 |
|   10003 |     2020 | Fall     | 2019 |  1014 | D241 |
|   10004 |     2021 | Fall     | 2019 |  1014 | D241 |
|   10005 |     2040 | Fall     | 2019 |  1015 | D237 |
|   10006 |     2041 | Fall     | 2019 |  1019 | D217 |
|   10007 |     2060 | Fall     | 2019 |  1020 | B101 |
|   10008 |     2080 | Fall     | 2019 |  1018 | D241 |
|   11000 |     2000 | Spring   | 2020 |  1011 | D241 |
|   11001 |     2001 | Spring   | 2020 |  1013 | D242 |
|   11002 |     2002 | Spring   | 2020 |  1013 | D136 |
|   11003 |     2020 | Spring   | 2020 |  1016 | D217 |
|   11004 |     2061 | Spring   | 2020 |  1018 | B101 |
+---------+----------+----------+------+-------+------+
14 rows in set (0.00 sec)

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  |       |      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> SELECT * FROM enroll;
+--------+---------+-------+----------+
| stuId  | classId | grade | n_alerts |
+--------+---------+-------+----------+
| 100000 |   10000 | A     |        0 |
| 100001 |   10000 | NULL  |     NULL |
| 100002 |   10000 | B-    |        3 |
| 100000 |   10001 | A     |        2 |
| 100001 |   10001 | A-    |        0 |
| 100000 |   10002 | B+    |        1 |
| 100002 |   10002 | B+    |        2 |
| 100000 |   10003 | C     |        0 |
| 100002 |   10003 | D     |        4 |
| 100004 |   10003 | A     |        0 |
| 100005 |   10003 | NULL  |     NULL |
| 100000 |   10004 | A-    |        1 |
| 100004 |   10004 | B+    |     NULL |
| 100005 |   10004 | A-    |        0 |
| 100006 |   10004 | C+    |     NULL |
| 100005 |   10005 | A-    |        0 |
| 100006 |   10005 | A     |     NULL |
| 100005 |   10006 | B+    |     NULL |
| 100007 |   10007 | F     |        4 |
| 100008 |   10007 | C-    |        0 |
| 100007 |   10008 | A-    |        0 |
| 100000 |   11001 | D     |        4 |
+--------+---------+-------+----------+
22 rows in set (0.00 sec)
 

Example:

Consider the following instance of the table department:

+----------+------------------------------+------------+------------+
| deptCode | deptName                     | schoolCode | numFaculty |
+----------+------------------------------+------------+------------+
| ACCT     | Accounting                   | BUS        |         10 |
| ARTS     | Arts                         | HSH        |          5 |
| CINF     | Computer Information Systems | CSE        |          5 |
| CSCI     | Computer Science             | CSE        |         12 |
| ENGL     | English                      | HSH        |         12 |
| ITEC     | Information Technology       | CSE        |          4 |
| MATH     | Mathematics                  | CSE        |          7 |
+----------+------------------------------+------------+------------+
7 rows in set (0.00 sec)

• The name of the table is 'department'.
• There are currently six rows in the relation instance.
• Each row in the table 'department' has the same columns: deptCode, deptName, schoolCode and numFaculty. This is the structure of the relation.
• The relation schema for class: department(deptCode, deptName, schoolCode, numFaculty).
• Each column has a specific data type. Examples:
  1. deptCode: VARCHAR(4)
  2. deptName: VARCHAR(30)
  3. schoolCode: VARCHAR(3)
  4. numFaculty: TINYINT
• We said the domain of the column deptCode is varchar(4).
• Domain may be understood as the values allowed by the data type.
• Thus, you may consider the relation schema and column domains the 'structure' of the database.
• The structures usually do not change much, just like the structure of a building.
• However, the structure can be changed (just like the structure of a building: remodeling).
• On the other hand, the content of a table (called a relation instance) can be changed from time to time.

mysql> desc department;
+------------+-------------+------+-----+---------+-------+
| Field      | Type        | Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+-------+
| deptCode   | varchar(4)  | NO   | PRI | NULL    |       |
| deptName   | varchar(30) | YES  | UNI | NULL    |       |
| schoolCode | varchar(3)  | YES  | MUL | NULL    |       |
| numFaculty | tinyint(4)  | YES  |     | NULL    |       |
+------------+-------------+------+-----+---------+-------+
4 rows in set (0.01 sec)
 

Example:

After inserting a new row:

INSERT INTO department VALUES ('PHYS', 'Physics','CSE',3); 

The new relation instance of the table department:

mysql> select * from department;
+----------+------------------------------+------------+------------+
| deptCode | deptName                     | schoolCode | numFaculty |
+----------+------------------------------+------------+------------+
| ACCT     | Accounting                   | BUS        |         10 |
| ARTS     | Arts                         | HSH        |          5 |
| CINF     | Computer Information Systems | CSE        |          5 |
| CSCI     | Computer Science             | CSE        |         12 |
| ENGL     | English                      | HSH        |         12 |
| ITEC     | Information Technology       | CSE        |          4 |
| MATH     | Mathematics                  | CSE        |          7 |
| PHYS     | Physics                      | CSE        |          3 |
+----------+------------------------------+------------+------------+
8 rows in set (0.00 sec)

After:

DELETE FROM department WHERE deptCode = 'PHYS';

the relation instance reverts back.

mysql> select * from department;
+----------+------------------------------+------------+------------+
| deptCode | deptName                     | schoolCode | numFaculty |
+----------+------------------------------+------------+------------+
| ACCT     | Accounting                   | BUS        |         10 |
| ARTS     | Arts                         | HSH        |          5 |
| CINF     | Computer Information Systems | CSE        |          5 |
| CSCI     | Computer Science             | CSE        |         12 |
| ENGL     | English                      | HSH        |         12 |
| ITEC     | Information Technology       | CSE        |          4 |
| MATH     | Mathematics                  | CSE        |          7 |
+----------+------------------------------+------------+------------+
7 rows in set (0.00 sec)
 

• Note the terms table and relation can be ambiguous. They may mean:
  1. the relation schema: the 'structure' of the table.
  2. the relation instance: actual data in the relation at a specific time.
• Note that relations are a logical concept to reason with.
• Relations must eventually stored in the file systems to ensure persistence.
• A row may be stored as a record, in which a column is a field of the record.
• Conceptually, a record may be used to represent (or store information about) an entity, a relationship, or a concept
  • A student record may represent a student (entity)
  • An enrollment record may represent a relationship: a student taking a class.
• Two tables may be 'connected' through unique identifiers, known as foreign keys. Example:
  • The enrollment table is related to the student table through stuId:

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  |       |      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> SELECT * FROM enroll;
+--------+---------+-------+----------+
| stuId  | classId | grade | n_alerts |
+--------+---------+-------+----------+
| 100000 |   10000 | A     |        0 |
| 100001 |   10000 | NULL  |     NULL |
| 100002 |   10000 | B-    |        3 |
| 100000 |   10001 | A     |        2 |
| 100001 |   10001 | A-    |        0 |
| 100000 |   10002 | B+    |        1 |
| 100002 |   10002 | B+    |        2 |
| 100000 |   10003 | C     |        0 |
| 100002 |   10003 | D     |        4 |
| 100004 |   10003 | A     |        0 |
| 100005 |   10003 | NULL  |     NULL |
| 100000 |   10004 | A-    |        1 |
| 100004 |   10004 | B+    |     NULL |
| 100005 |   10004 | A-    |        0 |
| 100006 |   10004 | C+    |     NULL |
| 100005 |   10005 | A-    |        0 |
| 100006 |   10005 | A     |     NULL |
| 100005 |   10006 | B+    |     NULL |
| 100007 |   10007 | F     |        4 |
| 100008 |   10007 | C-    |        0 |
| 100007 |   10008 | A-    |        0 |
| 100000 |   11001 | D     |        4 |
+--------+---------+-------+----------+
22 rows in set (0.00 sec)

2.SQL and MySQL

• The standard query language for RDBMS is Structured Query Language (SQL).
• There are many RDBMS
• We use MySQL (or MariaDB) in this class.

DBMS uses a client-server architecture.

MySQL Server Setup

We will use MySQL/MariaDB.

[1] Install XAMPP, which contains many server software configured to work together for development purpose. For XAMPP, we will use MySQL/Maria DB and Apache (Web server).

1. Recommended to install XAMPP in the top level: c:\xampp (likely the default).
2. Set up development accounts immediately using phpMyAdmin after intallation.
3. Change the root password (optional): a secure step that requires tinkering.
   1. Use PHPMyAdmin to change the password, say to 'a_new_frog' for both hostname '%' and 'localhost'
   2. PhpMyAdmin use the default root account (with no initial password) via localhost. Thus, you will need to supply the new password to start up PhpMyAdmin by editing the file c:\xampp\phpMyAdmin\config.inc.php, search change the line to, for example: $cfg['Servers'][$i]['password'] = 'a_new_frog';

MySQL Clients Setup:

It is common to use multiple clients to connect to the backend database server. In this course, we will use three clients in our classes. You may use your own favorite clients. However, I may not be as helpful in these clients.

[1] MySQL Command-Line Prompt

1. Come with (1) XAMPP/MariaDB or (2) MySQL 8.0. (Note that the two versions of mysql prompt are different.)
   1. MariaDB mysql: https://mariadb.com/kb/en/mysql-command-line-client/
   2. MySQL 8.x mysql: https://dev.mysql.com/doc/refman/8.0/en/mysql.html
2. A command line text-based MySQL-specific client.
3. You may set the PATH variable so you can call mysql prompt anywhere, such as by adding "c:\xampp\mysql\bin" in the PATH system environment variable.

Example:

mysql –h host -u user -p

or

mysql –h host -u user -p -P port_number

[2] PhPMyAdmin

1. A Web-based GUI client focused on DB administration.
2. After starting both MySQL and Apache in XAMPP, go to localhost in your browser.
3. MySQL specific.

[3] HeidiSQL

1. A GUI SQL client

Notes:

• 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.
• SQL is easy to start with. Not so easy to become an expert.
• Essentially declarative but with some procedural constructs.

SQL

• SQL is a rich declarative high level language.
• For the time being, we only need to know the basic form of the SELECT statement to get some taste of SQL and RDBMS.

select distinct <<result_columns>>
from <<source_tables>> -- conceptually join to form a large table
where <<conditions_for_inclusion>>

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.

Examples:

Using toyu:

mysql> -- Department codes and their names
mysql> SELECT DISTINCT deptCode, deptName
    -> FROM department;
+----------+------------------------------+
| deptCode | deptName                     |
+----------+------------------------------+
| ACCT     | Accounting                   |
| ARTS     | Arts                         |
| CINF     | Computer Information Systems |
| CSCI     | Computer Science             |
| ENGL     | English                      |
| ITEC     | Information Technology       |
| MATH     | Mathematics                  |
+----------+------------------------------+
7 rows in set (0.00 sec)

mysql>
mysql> -- Faculty information from the department 'CSCI'
mysql> SELECT DISTINCT *
    -> FROM faculty
    -> WHERE deptCode = 'CSCI';
+-------+--------+--------+----------+---------------------+
| facId | fname  | lname  | deptCode | rank                |
+-------+--------+--------+----------+---------------------+
|  1011 | Paul   | Smith  | CSCI     | Professor           |
|  1012 | Mary   | Tran   | CSCI     | Associate Professor |
|  1013 | David  | Love   | CSCI     |                     |
|  1014 | Sharon | Mannes | CSCI     | Assistant Professor |
+-------+--------+--------+----------+---------------------+
4 rows in set (0.00 sec)

mysql>
mysql> -- Faculty names from the department 'CSCI'
mysql> SELECT DISTINCT fname, lname
    -> FROM faculty
    -> WHERE deptCode = 'CSCI';
+--------+--------+
| fname  | lname  |
+--------+--------+
| Paul   | Smith  |
| Mary   | Tran   |
| David  | Love   |
| Sharon | Mannes |
+--------+--------+
4 rows in set (0.00 sec)

mysql>
mysql> -- Associate professor names from the department 'CSCI'
mysql> SELECT DISTINCT fname, lname
    -> FROM faculty
    -> WHERE deptCode = 'CSCI'
    -> AND `rank` = 'Associate Professor';
+-------+-------+
| fname | lname |
+-------+-------+
| Mary  | Tran  |
+-------+-------+
1 row in set (0.00 sec)

mysql>
mysql> -- Department names and numbers of faculty with the numbers
mysql> SELECT DISTINCT deptName, numFaculty
    -> FROM department
    -> WHERE numFaculty >= 10;
+------------------+------------+
| deptName         | numFaculty |
+------------------+------------+
| Accounting       |         10 |
| Computer Science |         12 |
| English          |         12 |
+------------------+------------+
3 rows in set (0.00 sec)

mysql>
mysql> -- Names of all faculty members and their
mysql> -- department names and ranks.
mysql> SELECT DISTINCT faculty.fName, faculty.lname,
    ->     department.deptName, faculty.`rank`
    -> FROM department, faculty
    -> WHERE faculty.deptCode = department.deptCode;
+----------+----------+------------------------------+---------------------+
| fName    | lname    | deptName                     | rank                |
+----------+----------+------------------------------+---------------------+
| Jorginlo | Neymar   | Accounting                   | Assistant Professor |
| Art      | Allister | Arts                         | Assistant Professor |
| Daniel   | Kim      | Computer Information Systems | Professor           |
| Andrew   | Byre     | Computer Information Systems | Associate Professor |
| Paul     | Smith    | Computer Science             | Professor           |
| Mary     | Tran     | Computer Science             | Associate Professor |
| David    | Love     | Computer Science             |                     |
| Sharon   | Mannes   | Computer Science             | Assistant Professor |
| Katrina  | Bajaj    | English                      | Lecturer            |
| Deborah  | Gump     | Information Technology       | Professor           |
| Benjamin | Yu       | Information Technology       | Lecturer            |
+----------+----------+------------------------------+---------------------+
11 rows in set (0.00 sec)

mysql>
mysql> -- Names of all faculty members and their
mysql> -- department names and ranks in the school CSE
mysql> SELECT DISTINCT faculty.fName, faculty.lname,
    ->     department.deptName, faculty.`rank`
    -> FROM department, faculty
    -> WHERE faculty.deptCode = department.deptCode
    -> AND department.schoolCode = 'CSE';
+----------+--------+------------------------------+---------------------+
| fName    | lname  | deptName                     | rank                |
+----------+--------+------------------------------+---------------------+
| Daniel   | Kim    | Computer Information Systems | Professor           |
| Andrew   | Byre   | Computer Information Systems | Associate Professor |
| Paul     | Smith  | Computer Science             | Professor           |
| Mary     | Tran   | Computer Science             | Associate Professor |
| David    | Love   | Computer Science             |                     |
| Sharon   | Mannes | Computer Science             | Assistant Professor |
| Deborah  | Gump   | Information Technology       | Professor           |
| Benjamin | Yu     | Information Technology       | Lecturer            |
+----------+--------+------------------------------+---------------------+
8 rows in set (0.00 sec)

Classroom Demonstration and Exercises:

Example. Show all student names.

Expected Result:

+---------+-----------+
| lname   | fname     |
+---------+-----------+
| Hawk    | Tony      |
| Hawk    | Mary      |
| Hawk    | David     |
| Lim     | Catherine |
| Johnson | Larry     |
| Johnson | Linda     |
| Johnson | Lillian   |
| Zico    | Ben       |
| Ching   | Bill      |
| King    | Linda     |
+---------+-----------+
10 rows in set (0.00 sec)

Analysis:

[1] Sources: student
[2] Conditions: none
[3] Output fields: lname, fname

SELECT DISTINCT s.lname, s.fname -- [3]
FROM student s; -- [1]

Example. List the last names and first names of students minoring in CINF and having 1011 as faculty advisor.

+-------+-------+
| lname | fname |
+-------+-------+
| Hawk  | Tony  |
| Hawk  | Mary  |
+-------+-------+
2 rows in set (0.01 sec)

[Analysis]

[1] Source tables: student

[2] Conditions:

• minor = 'CINF'
• advisor = 1011

[3] Output columns:

• lname
• fname

[suggested solution of sample question]

SELECT DISTINCT s.lname, s.fname
FROM student s
WHERE minor = 'CINF'
AND advisor = 1011;

1. All student names and the major codes.
2. All student names and the major department names.
3. All student names enrolled in the class with id 10003.
4. Show all information of students majoring in ‘MATH’.
5. Show the names and credits of students majoring in 'MATH'.
6. Show the names and credits of students majoring in 'MATH' and having 40 or more credits.
7. Show the id of students enrolled in 'CSC201A'.
8. Show the code of departments with faculty in the rank of 'Professor'.
9. Show the names of departments with faculty in the rank of 'Professor'.
10. Show the names of students who have enrolled in 'CSC201A'.
11. Show the names and major names of every student.
12. Show the names, major names, and advisor names of every student.
11. Show the student names and their major names for all students who have received a grade A in a class offered by a faculty from the MATH department.