MariaDB [toyu]> desc student; +---------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +---------+-------------+------+-----+---------+-------+ | stuId | int(11) | NO | PRI | NULL | | | fname | varchar(20) | NO | | NULL | | | lname | varchar(20) | NO | | NULL | | | major | varchar(4) | YES | MUL | NULL | | | minor | varchar(4) | YES | MUL | NULL | | | credits | int(3) | YES | | 0 | | | advisor | int(11) | YES | MUL | NULL | | +---------+-------------+------+-----+---------+-------+ 7 rows in set (0.06 sec) primary key: stuId three indices: major, minor, advisor. (MUL: mutliple valued index) CREATE [UNIQUE | FULLTEXT | SPATIAL] INDEX index_name [index_type] ON tbl_name (key_part,...) create index lname_index on student(lname); create index fname_index on student(fname); drop index fname_index on student; -- select * from student; select s1.stuid from (select * from student) as s1 -- derived table: s1 ; select stuId from student; create index lfname_index on student(fname, lname); [4] name, number of major. select from (select ... of Q4) as q_major, -- deptName, numMajor (select ... of Q4 modified to minor) as q_minor, -- deptName, numMinor where q_major.deptName = q_minor.deptName; -- facId and the number of students he advises. [1] raw data select advisor as facId, count(stuId) as countAdvisees from student group by advisor order by advisor; select advise.* from (select f.lname as facId, count(stuId) as countAdvisees from student s, faculty f where s.advisor = f.facId group by s.advisor order by s.advisor) as advise, ...;