Creating tables in MySQL:

- Use your own database (e.g., c433323su01yangt2) to create tables. NOTE: The underlined should be replaced by your own account name.

- Once a table has been created, you may insert tuples into it.

Note: Data may be truncated depending on the data types and the value to be inserted.

Reference: Aldo Zelen. Understanding Numerical Data Types in SQL. Online: https://learnsql.com/blog/understanding-numerical-data-types-sql/#:~:text=There%20is%20a%20small%20difference,is%20specified%20by%20the%20coder.

- The problem is that there does not exist a Primary Key in the Account table.

- EX: Revise the Create Account statement above to add accountNo as the primary key.

Drop table account;

CREATE TABLE account (

accountNo integer,

balance numeric(8,2),

Constraint primary key (accountNo)

);

- Q: Can we change a table after it is created?

- Answer: Yes. Use the ALTER TABLE statement.

Source: https://dev.mysql.com/doc/refman/8.0/en/alter-table.html

EX: Revise the Account table by adding an attribute called accountType with data type CHAR.

Alter table account

Add accountType Char After accountNo;

EX: Use UPDATE to set the accountType of accountNo 1313 to ‘S’ (for Saving).

Update account

Set

accountType = ‘S’

Where

accountNo=1313;

EX: Use UPDATE to set the accountType of accountNo 2313 to ‘C’ (for Checking).

Switch to the toyu database.

Q1: What would be returned by the following query?

select stuid, concat('number of courses = ', round(ach/3)) from student;

Q2: Rewrite the following JOIN query as a Nested query.

Select distinct s.* from student s, enroll e where s.stuId = e.stuId;

+--------+---------+---------+-------+-------+-----+---------+

+--------+---------+---------+-------+-------+-----+---------+

| 100000 | Tony | Hawk | CSCI | CINF | 40 | 1011 |

| 100001 | Mary | Hawk | CSCI | CINF | 35 | 1011 |

| 100002 | David | Hawk | CSCI | ITEC | 66 | 1012 |

| 100004 | Larry | Johnson | ITEC | NULL | 66 | 1017 |

| 100005 | Linda | Johnson | CINF | ENGL | 13 | 1015 |

| 100006 | Lillian | Johnson | CINF | ITEC | 18 | 1016 |

| 100007 | Ben | Zico | NULL | NULL | 16 | NULL |

+--------+---------+---------+-------+-------+-----+---------+

Q3: Rewrite the following JOIN query using NATURAL JOIN. Hint: Review https://www.w3resource.com/mysql/advance-query-in-mysql/mysql-natural-join.php for NATURAL JOIN syntax and examples.

Select * from student s, enroll e where s.stuId = e.stuId;

Q4: Given the following nested query, first (a) explain what this query do, and then (b) rewrite the nested query as a NATURAL JOIN query.

Hint: Use GROUP BY and HAVING.

Select * from student where EXISTS (select * from enroll where enroll.stuid = student.stuid);

+--------+---------+---------+-------+-------+-----+---------+

+--------+---------+---------+-------+-------+-----+---------+

| 100000 | Tony | Hawk | CSCI | CINF | 40 | 1011 |

| 100001 | Mary | Hawk | CSCI | CINF | 35 | 1011 |

| 100002 | David | Hawk | CSCI | ITEC | 66 | 1012 |

| 100004 | Larry | Johnson | ITEC | NULL | 66 | 1017 |

| 100005 | Linda | Johnson | CINF | ENGL | 13 | 1015 |

| 100006 | Lillian | Johnson | CINF | ITEC | 18 | 1016 |

| 100007 | Ben | Zico | NULL | NULL | 16 | NULL |

+--------+---------+---------+-------+-------+-----+---------+

Q5: Are NATURAL JOIN the same as JOIN? Compare the results from the following two queries.

Select * from student NATURAL JOIN enroll);

Select * from student JOIN enroll);

· The ORDER BY keyword is used to sort the result-set in ascending or descending order.

Reference: https://www.w3schools.com/mysql/mysql_orderby.asp

Q6: Revise the query below such that the tuples are sorted on stuId.

Select * from student NATURAL JOIN enroll;

+--------+---------+---------+-------+-------+-----+---------+---------+-------+----------+

+--------+---------+---------+-------+-------+-----+---------+---------+-------+----------+

| 100000 | Tony | Hawk | CSCI | CINF | 40 | 1011 | 10001 | A | 2 |

| 100000 | Tony | Hawk | CSCI | CINF | 40 | 1011 | 10004 | A- | 1 |

| 100000 | Tony | Hawk | CSCI | CINF | 40 | 1011 | 10002 | B+ | 1 |

| 100000 | Tony | Hawk | CSCI | CINF | 40 | 1011 | 11001 | D | 4 |

| 100000 | Tony | Hawk | CSCI | CINF | 40 | 1011 | 10003 | C | 0 |

| 100000 | Tony | Hawk | CSCI | CINF | 40 | 1011 | 10000 | A | 0 |

| 100001 | Mary | Hawk | CSCI | CINF | 35 | 1011 | 10001 | A- | 0 |

| 100002 | David | Hawk | CSCI | ITEC | 66 | 1012 | 10000 | B- | 3 |

| 100002 | David | Hawk | CSCI | ITEC | 66 | 1012 | 10002 | B+ | 2 |

| 100002 | David | Hawk | CSCI | ITEC | 66 | 1012 | 10003 | D | 4 |

| 100004 | Larry | Johnson | ITEC | NULL | 66 | 1017 | 10004 | B+ | NULL |

| 100004 | Larry | Johnson | ITEC | NULL | 66 | 1017 | 10003 | A | 0 |

| 100005 | Linda | Johnson | CINF | ENGL | 13 | 1015 | 10004 | A- | 0 |

| 100005 | Linda | Johnson | CINF | ENGL | 13 | 1015 | 10005 | A- | 0 |

| 100005 | Linda | Johnson | CINF | ENGL | 13 | 1015 | 10006 | B+ | NULL |

| 100006 | Lillian | Johnson | CINF | ITEC | 18 | 1016 | 10004 | C+ | NULL |

| 100006 | Lillian | Johnson | CINF | ITEC | 18 | 1016 | 10005 | A | NULL |

| 100007 | Ben | Zico | NULL | NULL | 16 | NULL | 10007 | F | 4 |

| 100007 | Ben | Zico | NULL | NULL | 16 | NULL | 10008 | A- | 0 |

| 100008 | Bill | Ching | ARTS | NULL | 90 | NULL | 10007 | C- | 0 |

+--------+---------+---------+-------+-------+-----+---------+---------+-------+----------+

Q8: Revise the query below such that the tuples are sorted on stuId (major order) and classId (minor order).