Welcome to the MariaDB monitor. Commands end with ; or \g. Your MariaDB connection id is 124 Server version: 11.8.6-MariaDB-0+deb13u1 from Debian -- Please help get to 10k stars at https://github.com/MariaDB/Server Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. MariaDB [sks]> show tables; +------------------+ | Tables_in_sks | +------------------+ | advisor | | classroom | | course | | department | | instructor | | instructor_title | | prereq | | section | | student | | takes | | teaches | | time_slot | +------------------+ 12 rows in set (0.001 sec) MariaDB [sks]> describe instructor; +-----------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-----------+--------------+------+-----+---------+-------+ | ID | varchar(5) | NO | PRI | NULL | | | name | varchar(20) | NO | | NULL | | | dept_name | varchar(20) | YES | MUL | NULL | | | salary | decimal(8,2) | YES | | NULL | | +-----------+--------------+------+-----+---------+-------+ 4 rows in set (0.007 sec) MariaDB [sks]> describe department; +-----------+---------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-----------+---------------+------+-----+---------+-------+ | dept_name | varchar(20) | NO | PRI | NULL | | | building | varchar(15) | YES | | NULL | | | budget | decimal(12,2) | YES | | NULL | | +-----------+---------------+------+-----+---------+-------+ 3 rows in set (0.001 sec) MariaDB [sks]> select ID,name from instructor where dept_name = 'Physics' and salary > 90000; +-------+----------+ | ID | name | +-------+----------+ | 22222 | Einstein | +-------+----------+ 1 row in set (0.012 sec) MariaDB [sks]> select ID,name from instructor where dept_name = 'Physics' and salary > '90000'; +-------+----------+ | ID | name | +-------+----------+ | 22222 | Einstein | +-------+----------+ 1 row in set (0.003 sec) MariaDB [sks]> select ID,name from instructor where dept_name = Physics and salary > 90000; ERROR 1054 (42S22): Unknown column 'Physics' in 'WHERE' MariaDB [sks]> select dept_name from instructor; +------------+ | dept_name | +------------+ | Biology | | Comp. Sci. | | Comp. Sci. | | Comp. Sci. | | Elec. Eng. | | Finance | | Finance | | History | | History | | Music | | Physics | | Physics | +------------+ 12 rows in set (0.063 sec) MariaDB [sks]> select dept_name,salary from instructor; +------------+----------+ | dept_name | salary | +------------+----------+ | Comp. Sci. | 65000.00 | | Finance | 90000.00 | | Music | 40000.00 | | Physics | 95000.00 | | History | 60000.00 | | Physics | 87000.00 | | Comp. Sci. | 75000.00 | | History | 62000.00 | | Finance | 80000.00 | | Biology | 72000.00 | | Comp. Sci. | 92000.00 | | Elec. Eng. | 80000.00 | +------------+----------+ 12 rows in set (0.003 sec) MariaDB [sks]> select distinct dept_name from instructor; +------------+ | dept_name | +------------+ | Biology | | Comp. Sci. | | Elec. Eng. | | Finance | | History | | Music | | Physics | +------------+ 7 rows in set (0.064 sec) MariaDB [sks]> select salary from instructor; +----------+ | salary | +----------+ | 65000.00 | | 90000.00 | | 40000.00 | | 95000.00 | | 60000.00 | | 87000.00 | | 75000.00 | | 62000.00 | | 80000.00 | | 72000.00 | | 92000.00 | | 80000.00 | +----------+ 12 rows in set (0.003 sec) MariaDB [sks]> select distinct salary from instructor; +----------+ | salary | +----------+ | 65000.00 | | 90000.00 | | 40000.00 | | 95000.00 | | 60000.00 | | 87000.00 | | 75000.00 | | 62000.00 | | 80000.00 | | 72000.00 | | 92000.00 | +----------+ 11 rows in set (0.003 sec) MariaDB [sks]> explain select distinct salary from instructor; +------+-------------+------------+------+---------------+------+---------+------+------+-----------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +------+-------------+------------+------+---------------+------+---------+------+------+-----------------+ | 1 | SIMPLE | instructor | ALL | NULL | NULL | NULL | NULL | 12 | Using temporary | +------+-------------+------------+------+---------------+------+---------+------+------+-----------------+ 1 row in set (0.003 sec) MariaDB [sks]> explain select distinct dept_name from instructor; +------+-------------+------------+-------+---------------+-----------+---------+------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +------+-------------+------------+-------+---------------+-----------+---------+------+------+-------------+ | 1 | SIMPLE | instructor | index | NULL | dept_name | 83 | NULL | 12 | Using index | +------+-------------+------------+-------+---------------+-----------+---------+------+------+-------------+ 1 row in set (0.003 sec) MariaDB [sks]> select ID,name,salary from instructor; +-------+------------+----------+ | ID | name | salary | +-------+------------+----------+ | 10101 | Srinivasan | 65000.00 | | 12121 | Wu | 90000.00 | | 15151 | Mozart | 40000.00 | | 22222 | Einstein | 95000.00 | | 32343 | El Said | 60000.00 | | 33456 | Gold | 87000.00 | | 45565 | Katz | 75000.00 | | 58583 | Califieri | 62000.00 | | 76543 | Singh | 80000.00 | | 76766 | Crick | 72000.00 | | 83821 | Brandt | 92000.00 | | 98345 | Kim | 80000.00 | +-------+------------+----------+ 12 rows in set (0.001 sec) MariaDB [sks]> select ID,name,salary/12 from instructor; +-------+------------+-------------+ | ID | name | salary/12 | +-------+------------+-------------+ | 10101 | Srinivasan | 5416.666667 | | 12121 | Wu | 7500.000000 | | 15151 | Mozart | 3333.333333 | | 22222 | Einstein | 7916.666667 | | 32343 | El Said | 5000.000000 | | 33456 | Gold | 7250.000000 | | 45565 | Katz | 6250.000000 | | 58583 | Califieri | 5166.666667 | | 76543 | Singh | 6666.666667 | | 76766 | Crick | 6000.000000 | | 83821 | Brandt | 7666.666667 | | 98345 | Kim | 6666.666667 | +-------+------------+-------------+ 12 rows in set (0.003 sec) MariaDB [sks]> select * from instructor, teaches; +-------+------------+------------+----------+-------+-----------+--------+----------+------+ | ID | name | dept_name | salary | ID | course_id | sec_id | semester | year | +-------+------------+------------+----------+-------+-----------+--------+----------+------+ | 10101 | Srinivasan | Comp. Sci. | 65000.00 | 76766 | BIO-101 | 1 | Summer | 2017 | | 12121 | Wu | Finance | 90000.00 | 76766 | BIO-101 | 1 | Summer | 2017 | | 15151 | Mozart | Music | 40000.00 | 76766 | BIO-101 | 1 | Summer | 2017 | | 22222 | Einstein | Physics | 95000.00 | 76766 | BIO-101 | 1 | Summer | 2017 | | 32343 | El Said | History | 60000.00 | 76766 | BIO-101 | 1 | Summer | 2017 | | 33456 | Gold | Physics | 87000.00 | 76766 | BIO-101 | 1 | Summer | 2017 | | 45565 | Katz | Comp. Sci. | 75000.00 | 76766 | BIO-101 | 1 | Summer | 2017 | | 58583 | Califieri | History | 62000.00 | 76766 | BIO-101 | 1 | Summer | 2017 | | 76543 | Singh | Finance | 80000.00 | 76766 | BIO-101 | 1 | Summer | 2017 | | 76766 | Crick | Biology | 72000.00 | 76766 | BIO-101 | 1 | Summer | 2017 | | 83821 | Brandt | Comp. Sci. | 92000.00 | 76766 | BIO-101 | 1 | Summer | 2017 | | 98345 | Kim | Elec. Eng. | 80000.00 | 76766 | BIO-101 | 1 | Summer | 2017 | | 10101 | Srinivasan | Comp. Sci. | 65000.00 | 76766 | BIO-301 | 1 | Summer | 2018 | | 12121 | Wu | Finance | 90000.00 | 76766 | BIO-301 | 1 | Summer | 2018 | | 15151 | Mozart | Music | 40000.00 | 76766 | BIO-301 | 1 | Summer | 2018 | | 22222 | Einstein | Physics | 95000.00 | 76766 | BIO-301 | 1 | Summer | 2018 | | 32343 | El Said | History | 60000.00 | 76766 | BIO-301 | 1 | Summer | 2018 | | 33456 | Gold | Physics | 87000.00 | 76766 | BIO-301 | 1 | Summer | 2018 | | 45565 | Katz | Comp. Sci. | 75000.00 | 76766 | BIO-301 | 1 | Summer | 2018 | | 58583 | Califieri | History | 62000.00 | 76766 | BIO-301 | 1 | Summer | 2018 | | 76543 | Singh | Finance | 80000.00 | 76766 | BIO-301 | 1 | Summer | 2018 | | 76766 | Crick | Biology | 72000.00 | 76766 | BIO-301 | 1 | Summer | 2018 | | 83821 | Brandt | Comp. Sci. | 92000.00 | 76766 | BIO-301 | 1 | Summer | 2018 | | 98345 | Kim | Elec. Eng. | 80000.00 | 76766 | BIO-301 | 1 | Summer | 2018 | | 10101 | Srinivasan | Comp. Sci. | 65000.00 | 10101 | CS-101 | 1 | Fall | 2017 | | 12121 | Wu | Finance | 90000.00 | 10101 | CS-101 | 1 | Fall | 2017 | | 15151 | Mozart | Music | 40000.00 | 10101 | CS-101 | 1 | Fall | 2017 | | 22222 | Einstein | Physics | 95000.00 | 10101 | CS-101 | 1 | Fall | 2017 | | 32343 | El Said | History | 60000.00 | 10101 | CS-101 | 1 | Fall | 2017 | | 33456 | Gold | Physics | 87000.00 | 10101 | CS-101 | 1 | Fall | 2017 | | 45565 | Katz | Comp. Sci. | 75000.00 | 10101 | CS-101 | 1 | Fall | 2017 | | 58583 | Califieri | History | 62000.00 | 10101 | CS-101 | 1 | Fall | 2017 | | 76543 | Singh | Finance | 80000.00 | 10101 | CS-101 | 1 | Fall | 2017 | | 76766 | Crick | Biology | 72000.00 | 10101 | CS-101 | 1 | Fall | 2017 | | 83821 | Brandt | Comp. Sci. | 92000.00 | 10101 | CS-101 | 1 | Fall | 2017 | | 98345 | Kim | Elec. Eng. | 80000.00 | 10101 | CS-101 | 1 | Fall | 2017 | | 10101 | Srinivasan | Comp. Sci. | 65000.00 | 45565 | CS-101 | 1 | Spring | 2018 | | 12121 | Wu | Finance | 90000.00 | 45565 | CS-101 | 1 | Spring | 2018 | | 15151 | Mozart | Music | 40000.00 | 45565 | CS-101 | 1 | Spring | 2018 | | 22222 | Einstein | Physics | 95000.00 | 45565 | CS-101 | 1 | Spring | 2018 | | 32343 | El Said | History | 60000.00 | 45565 | CS-101 | 1 | Spring | 2018 | | 33456 | Gold | Physics | 87000.00 | 45565 | CS-101 | 1 | Spring | 2018 | | 45565 | Katz | Comp. Sci. | 75000.00 | 45565 | CS-101 | 1 | Spring | 2018 | | 58583 | Califieri | History | 62000.00 | 45565 | CS-101 | 1 | Spring | 2018 | | 76543 | Singh | Finance | 80000.00 | 45565 | CS-101 | 1 | Spring | 2018 | | 76766 | Crick | Biology | 72000.00 | 45565 | CS-101 | 1 | Spring | 2018 | | 83821 | Brandt | Comp. Sci. | 92000.00 | 45565 | CS-101 | 1 | Spring | 2018 | | 98345 | Kim | Elec. Eng. | 80000.00 | 45565 | CS-101 | 1 | Spring | 2018 | | 10101 | Srinivasan | Comp. Sci. | 65000.00 | 83821 | CS-190 | 1 | Spring | 2017 | | 12121 | Wu | Finance | 90000.00 | 83821 | CS-190 | 1 | Spring | 2017 | | 15151 | Mozart | Music | 40000.00 | 83821 | CS-190 | 1 | Spring | 2017 | | 22222 | Einstein | Physics | 95000.00 | 83821 | CS-190 | 1 | Spring | 2017 | | 32343 | El Said | History | 60000.00 | 83821 | CS-190 | 1 | Spring | 2017 | | 33456 | Gold | Physics | 87000.00 | 83821 | CS-190 | 1 | Spring | 2017 | | 45565 | Katz | Comp. Sci. | 75000.00 | 83821 | CS-190 | 1 | Spring | 2017 | | 58583 | Califieri | History | 62000.00 | 83821 | CS-190 | 1 | Spring | 2017 | | 76543 | Singh | Finance | 80000.00 | 83821 | CS-190 | 1 | Spring | 2017 | | 76766 | Crick | Biology | 72000.00 | 83821 | CS-190 | 1 | Spring | 2017 | | 83821 | Brandt | Comp. Sci. | 92000.00 | 83821 | CS-190 | 1 | Spring | 2017 | | 98345 | Kim | Elec. Eng. | 80000.00 | 83821 | CS-190 | 1 | Spring | 2017 | | 10101 | Srinivasan | Comp. Sci. | 65000.00 | 83821 | CS-190 | 2 | Spring | 2017 | | 12121 | Wu | Finance | 90000.00 | 83821 | CS-190 | 2 | Spring | 2017 | | 15151 | Mozart | Music | 40000.00 | 83821 | CS-190 | 2 | Spring | 2017 | | 22222 | Einstein | Physics | 95000.00 | 83821 | CS-190 | 2 | Spring | 2017 | | 32343 | El Said | History | 60000.00 | 83821 | CS-190 | 2 | Spring | 2017 | | 33456 | Gold | Physics | 87000.00 | 83821 | CS-190 | 2 | Spring | 2017 | | 45565 | Katz | Comp. Sci. | 75000.00 | 83821 | CS-190 | 2 | Spring | 2017 | | 58583 | Califieri | History | 62000.00 | 83821 | CS-190 | 2 | Spring | 2017 | | 76543 | Singh | Finance | 80000.00 | 83821 | CS-190 | 2 | Spring | 2017 | | 76766 | Crick | Biology | 72000.00 | 83821 | CS-190 | 2 | Spring | 2017 | | 83821 | Brandt | Comp. Sci. | 92000.00 | 83821 | CS-190 | 2 | Spring | 2017 | | 98345 | Kim | Elec. Eng. | 80000.00 | 83821 | CS-190 | 2 | Spring | 2017 | | 10101 | Srinivasan | Comp. Sci. | 65000.00 | 10101 | CS-315 | 1 | Spring | 2018 | | 12121 | Wu | Finance | 90000.00 | 10101 | CS-315 | 1 | Spring | 2018 | | 15151 | Mozart | Music | 40000.00 | 10101 | CS-315 | 1 | Spring | 2018 | | 22222 | Einstein | Physics | 95000.00 | 10101 | CS-315 | 1 | Spring | 2018 | | 32343 | El Said | History | 60000.00 | 10101 | CS-315 | 1 | Spring | 2018 | | 33456 | Gold | Physics | 87000.00 | 10101 | CS-315 | 1 | Spring | 2018 | | 45565 | Katz | Comp. Sci. | 75000.00 | 10101 | CS-315 | 1 | Spring | 2018 | | 58583 | Califieri | History | 62000.00 | 10101 | CS-315 | 1 | Spring | 2018 | | 76543 | Singh | Finance | 80000.00 | 10101 | CS-315 | 1 | Spring | 2018 | | 76766 | Crick | Biology | 72000.00 | 10101 | CS-315 | 1 | Spring | 2018 | | 83821 | Brandt | Comp. Sci. | 92000.00 | 10101 | CS-315 | 1 | Spring | 2018 | | 98345 | Kim | Elec. Eng. | 80000.00 | 10101 | CS-315 | 1 | Spring | 2018 | | 10101 | Srinivasan | Comp. Sci. | 65000.00 | 45565 | CS-319 | 1 | Spring | 2018 | | 12121 | Wu | Finance | 90000.00 | 45565 | CS-319 | 1 | Spring | 2018 | | 15151 | Mozart | Music | 40000.00 | 45565 | CS-319 | 1 | Spring | 2018 | | 22222 | Einstein | Physics | 95000.00 | 45565 | CS-319 | 1 | Spring | 2018 | | 32343 | El Said | History | 60000.00 | 45565 | CS-319 | 1 | Spring | 2018 | | 33456 | Gold | Physics | 87000.00 | 45565 | CS-319 | 1 | Spring | 2018 | | 45565 | Katz | Comp. Sci. | 75000.00 | 45565 | CS-319 | 1 | Spring | 2018 | | 58583 | Califieri | History | 62000.00 | 45565 | CS-319 | 1 | Spring | 2018 | | 76543 | Singh | Finance | 80000.00 | 45565 | CS-319 | 1 | Spring | 2018 | | 76766 | Crick | Biology | 72000.00 | 45565 | CS-319 | 1 | Spring | 2018 | | 83821 | Brandt | Comp. Sci. | 92000.00 | 45565 | CS-319 | 1 | Spring | 2018 | | 98345 | Kim | Elec. Eng. | 80000.00 | 45565 | CS-319 | 1 | Spring | 2018 | | 10101 | Srinivasan | Comp. Sci. | 65000.00 | 83821 | CS-319 | 2 | Spring | 2018 | | 12121 | Wu | Finance | 90000.00 | 83821 | CS-319 | 2 | Spring | 2018 | | 15151 | Mozart | Music | 40000.00 | 83821 | CS-319 | 2 | Spring | 2018 | | 22222 | Einstein | Physics | 95000.00 | 83821 | CS-319 | 2 | Spring | 2018 | | 32343 | El Said | History | 60000.00 | 83821 | CS-319 | 2 | Spring | 2018 | | 33456 | Gold | Physics | 87000.00 | 83821 | CS-319 | 2 | Spring | 2018 | | 45565 | Katz | Comp. Sci. | 75000.00 | 83821 | CS-319 | 2 | Spring | 2018 | | 58583 | Califieri | History | 62000.00 | 83821 | CS-319 | 2 | Spring | 2018 | | 76543 | Singh | Finance | 80000.00 | 83821 | CS-319 | 2 | Spring | 2018 | | 76766 | Crick | Biology | 72000.00 | 83821 | CS-319 | 2 | Spring | 2018 | | 83821 | Brandt | Comp. Sci. | 92000.00 | 83821 | CS-319 | 2 | Spring | 2018 | | 98345 | Kim | Elec. Eng. | 80000.00 | 83821 | CS-319 | 2 | Spring | 2018 | | 10101 | Srinivasan | Comp. Sci. | 65000.00 | 10101 | CS-347 | 1 | Fall | 2017 | | 12121 | Wu | Finance | 90000.00 | 10101 | CS-347 | 1 | Fall | 2017 | | 15151 | Mozart | Music | 40000.00 | 10101 | CS-347 | 1 | Fall | 2017 | | 22222 | Einstein | Physics | 95000.00 | 10101 | CS-347 | 1 | Fall | 2017 | | 32343 | El Said | History | 60000.00 | 10101 | CS-347 | 1 | Fall | 2017 | | 33456 | Gold | Physics | 87000.00 | 10101 | CS-347 | 1 | Fall | 2017 | | 45565 | Katz | Comp. Sci. | 75000.00 | 10101 | CS-347 | 1 | Fall | 2017 | | 58583 | Califieri | History | 62000.00 | 10101 | CS-347 | 1 | Fall | 2017 | | 76543 | Singh | Finance | 80000.00 | 10101 | CS-347 | 1 | Fall | 2017 | | 76766 | Crick | Biology | 72000.00 | 10101 | CS-347 | 1 | Fall | 2017 | | 83821 | Brandt | Comp. Sci. | 92000.00 | 10101 | CS-347 | 1 | Fall | 2017 | | 98345 | Kim | Elec. Eng. | 80000.00 | 10101 | CS-347 | 1 | Fall | 2017 | | 10101 | Srinivasan | Comp. Sci. | 65000.00 | 98345 | EE-181 | 1 | Spring | 2017 | | 12121 | Wu | Finance | 90000.00 | 98345 | EE-181 | 1 | Spring | 2017 | | 15151 | Mozart | Music | 40000.00 | 98345 | EE-181 | 1 | Spring | 2017 | | 22222 | Einstein | Physics | 95000.00 | 98345 | EE-181 | 1 | Spring | 2017 | | 32343 | El Said | History | 60000.00 | 98345 | EE-181 | 1 | Spring | 2017 | | 33456 | Gold | Physics | 87000.00 | 98345 | EE-181 | 1 | Spring | 2017 | | 45565 | Katz | Comp. Sci. | 75000.00 | 98345 | EE-181 | 1 | Spring | 2017 | | 58583 | Califieri | History | 62000.00 | 98345 | EE-181 | 1 | Spring | 2017 | | 76543 | Singh | Finance | 80000.00 | 98345 | EE-181 | 1 | Spring | 2017 | | 76766 | Crick | Biology | 72000.00 | 98345 | EE-181 | 1 | Spring | 2017 | | 83821 | Brandt | Comp. Sci. | 92000.00 | 98345 | EE-181 | 1 | Spring | 2017 | | 98345 | Kim | Elec. Eng. | 80000.00 | 98345 | EE-181 | 1 | Spring | 2017 | | 10101 | Srinivasan | Comp. Sci. | 65000.00 | 12121 | FIN-201 | 1 | Spring | 2018 | | 12121 | Wu | Finance | 90000.00 | 12121 | FIN-201 | 1 | Spring | 2018 | | 15151 | Mozart | Music | 40000.00 | 12121 | FIN-201 | 1 | Spring | 2018 | | 22222 | Einstein | Physics | 95000.00 | 12121 | FIN-201 | 1 | Spring | 2018 | | 32343 | El Said | History | 60000.00 | 12121 | FIN-201 | 1 | Spring | 2018 | | 33456 | Gold | Physics | 87000.00 | 12121 | FIN-201 | 1 | Spring | 2018 | | 45565 | Katz | Comp. Sci. | 75000.00 | 12121 | FIN-201 | 1 | Spring | 2018 | | 58583 | Califieri | History | 62000.00 | 12121 | FIN-201 | 1 | Spring | 2018 | | 76543 | Singh | Finance | 80000.00 | 12121 | FIN-201 | 1 | Spring | 2018 | | 76766 | Crick | Biology | 72000.00 | 12121 | FIN-201 | 1 | Spring | 2018 | | 83821 | Brandt | Comp. Sci. | 92000.00 | 12121 | FIN-201 | 1 | Spring | 2018 | | 98345 | Kim | Elec. Eng. | 80000.00 | 12121 | FIN-201 | 1 | Spring | 2018 | | 10101 | Srinivasan | Comp. Sci. | 65000.00 | 32343 | HIS-351 | 1 | Spring | 2018 | | 12121 | Wu | Finance | 90000.00 | 32343 | HIS-351 | 1 | Spring | 2018 | | 15151 | Mozart | Music | 40000.00 | 32343 | HIS-351 | 1 | Spring | 2018 | | 22222 | Einstein | Physics | 95000.00 | 32343 | HIS-351 | 1 | Spring | 2018 | | 32343 | El Said | History | 60000.00 | 32343 | HIS-351 | 1 | Spring | 2018 | | 33456 | Gold | Physics | 87000.00 | 32343 | HIS-351 | 1 | Spring | 2018 | | 45565 | Katz | Comp. Sci. | 75000.00 | 32343 | HIS-351 | 1 | Spring | 2018 | | 58583 | Califieri | History | 62000.00 | 32343 | HIS-351 | 1 | Spring | 2018 | | 76543 | Singh | Finance | 80000.00 | 32343 | HIS-351 | 1 | Spring | 2018 | | 76766 | Crick | Biology | 72000.00 | 32343 | HIS-351 | 1 | Spring | 2018 | | 83821 | Brandt | Comp. Sci. | 92000.00 | 32343 | HIS-351 | 1 | Spring | 2018 | | 98345 | Kim | Elec. Eng. | 80000.00 | 32343 | HIS-351 | 1 | Spring | 2018 | | 10101 | Srinivasan | Comp. Sci. | 65000.00 | 15151 | MU-199 | 1 | Spring | 2018 | | 12121 | Wu | Finance | 90000.00 | 15151 | MU-199 | 1 | Spring | 2018 | | 15151 | Mozart | Music | 40000.00 | 15151 | MU-199 | 1 | Spring | 2018 | | 22222 | Einstein | Physics | 95000.00 | 15151 | MU-199 | 1 | Spring | 2018 | | 32343 | El Said | History | 60000.00 | 15151 | MU-199 | 1 | Spring | 2018 | | 33456 | Gold | Physics | 87000.00 | 15151 | MU-199 | 1 | Spring | 2018 | | 45565 | Katz | Comp. Sci. | 75000.00 | 15151 | MU-199 | 1 | Spring | 2018 | | 58583 | Califieri | History | 62000.00 | 15151 | MU-199 | 1 | Spring | 2018 | | 76543 | Singh | Finance | 80000.00 | 15151 | MU-199 | 1 | Spring | 2018 | | 76766 | Crick | Biology | 72000.00 | 15151 | MU-199 | 1 | Spring | 2018 | | 83821 | Brandt | Comp. Sci. | 92000.00 | 15151 | MU-199 | 1 | Spring | 2018 | | 98345 | Kim | Elec. Eng. | 80000.00 | 15151 | MU-199 | 1 | Spring | 2018 | | 10101 | Srinivasan | Comp. Sci. | 65000.00 | 22222 | PHY-101 | 1 | Fall | 2017 | | 12121 | Wu | Finance | 90000.00 | 22222 | PHY-101 | 1 | Fall | 2017 | | 15151 | Mozart | Music | 40000.00 | 22222 | PHY-101 | 1 | Fall | 2017 | | 22222 | Einstein | Physics | 95000.00 | 22222 | PHY-101 | 1 | Fall | 2017 | | 32343 | El Said | History | 60000.00 | 22222 | PHY-101 | 1 | Fall | 2017 | | 33456 | Gold | Physics | 87000.00 | 22222 | PHY-101 | 1 | Fall | 2017 | | 45565 | Katz | Comp. Sci. | 75000.00 | 22222 | PHY-101 | 1 | Fall | 2017 | | 58583 | Califieri | History | 62000.00 | 22222 | PHY-101 | 1 | Fall | 2017 | | 76543 | Singh | Finance | 80000.00 | 22222 | PHY-101 | 1 | Fall | 2017 | | 76766 | Crick | Biology | 72000.00 | 22222 | PHY-101 | 1 | Fall | 2017 | | 83821 | Brandt | Comp. Sci. | 92000.00 | 22222 | PHY-101 | 1 | Fall | 2017 | | 98345 | Kim | Elec. Eng. | 80000.00 | 22222 | PHY-101 | 1 | Fall | 2017 | +-------+------------+------------+----------+-------+-----------+--------+----------+------+ 180 rows in set (0.005 sec) MariaDB [sks]> select * from instructor, teaches where instructor.ID = teaches.ID; +-------+------------+------------+----------+-------+-----------+--------+----------+------+ | ID | name | dept_name | salary | ID | course_id | sec_id | semester | year | +-------+------------+------------+----------+-------+-----------+--------+----------+------+ | 76766 | Crick | Biology | 72000.00 | 76766 | BIO-101 | 1 | Summer | 2017 | | 76766 | Crick | Biology | 72000.00 | 76766 | BIO-301 | 1 | Summer | 2018 | | 10101 | Srinivasan | Comp. Sci. | 65000.00 | 10101 | CS-101 | 1 | Fall | 2017 | | 45565 | Katz | Comp. Sci. | 75000.00 | 45565 | CS-101 | 1 | Spring | 2018 | | 83821 | Brandt | Comp. Sci. | 92000.00 | 83821 | CS-190 | 1 | Spring | 2017 | | 83821 | Brandt | Comp. Sci. | 92000.00 | 83821 | CS-190 | 2 | Spring | 2017 | | 10101 | Srinivasan | Comp. Sci. | 65000.00 | 10101 | CS-315 | 1 | Spring | 2018 | | 45565 | Katz | Comp. Sci. | 75000.00 | 45565 | CS-319 | 1 | Spring | 2018 | | 83821 | Brandt | Comp. Sci. | 92000.00 | 83821 | CS-319 | 2 | Spring | 2018 | | 10101 | Srinivasan | Comp. Sci. | 65000.00 | 10101 | CS-347 | 1 | Fall | 2017 | | 98345 | Kim | Elec. Eng. | 80000.00 | 98345 | EE-181 | 1 | Spring | 2017 | | 12121 | Wu | Finance | 90000.00 | 12121 | FIN-201 | 1 | Spring | 2018 | | 32343 | El Said | History | 60000.00 | 32343 | HIS-351 | 1 | Spring | 2018 | | 15151 | Mozart | Music | 40000.00 | 15151 | MU-199 | 1 | Spring | 2018 | | 22222 | Einstein | Physics | 95000.00 | 22222 | PHY-101 | 1 | Fall | 2017 | +-------+------------+------------+----------+-------+-----------+--------+----------+------+ 15 rows in set (0.004 sec) MariaDB [sks]> select * from instructor, teaches where ID = teaches.ID; ERROR 1052 (23000): Column 'ID' in WHERE is ambiguous MariaDB [sks]> select name,course_id from instructor, teaches where instructor.ID = teaches.ID; +------------+-----------+ | name | course_id | +------------+-----------+ | Crick | BIO-101 | | Crick | BIO-301 | | Srinivasan | CS-101 | | Katz | CS-101 | | Brandt | CS-190 | | Brandt | CS-190 | | Srinivasan | CS-315 | | Katz | CS-319 | | Brandt | CS-319 | | Srinivasan | CS-347 | | Kim | EE-181 | | Wu | FIN-201 | | El Said | HIS-351 | | Mozart | MU-199 | | Einstein | PHY-101 | +------------+-----------+ 15 rows in set (0.001 sec) MariaDB [sks]> select * from teaches as T1, teaches as T2 where T1.ID = T2.ID and T1.course_id != T2.course_id; +-------+-----------+--------+----------+------+-------+-----------+--------+----------+------+ | ID | course_id | sec_id | semester | year | ID | course_id | sec_id | semester | year | +-------+-----------+--------+----------+------+-------+-----------+--------+----------+------+ | 76766 | BIO-101 | 1 | Summer | 2017 | 76766 | BIO-301 | 1 | Summer | 2018 | | 76766 | BIO-301 | 1 | Summer | 2018 | 76766 | BIO-101 | 1 | Summer | 2017 | | 10101 | CS-101 | 1 | Fall | 2017 | 10101 | CS-315 | 1 | Spring | 2018 | | 10101 | CS-101 | 1 | Fall | 2017 | 10101 | CS-347 | 1 | Fall | 2017 | | 45565 | CS-101 | 1 | Spring | 2018 | 45565 | CS-319 | 1 | Spring | 2018 | | 83821 | CS-190 | 1 | Spring | 2017 | 83821 | CS-319 | 2 | Spring | 2018 | | 83821 | CS-190 | 2 | Spring | 2017 | 83821 | CS-319 | 2 | Spring | 2018 | | 10101 | CS-315 | 1 | Spring | 2018 | 10101 | CS-101 | 1 | Fall | 2017 | | 10101 | CS-315 | 1 | Spring | 2018 | 10101 | CS-347 | 1 | Fall | 2017 | | 45565 | CS-319 | 1 | Spring | 2018 | 45565 | CS-101 | 1 | Spring | 2018 | | 83821 | CS-319 | 2 | Spring | 2018 | 83821 | CS-190 | 1 | Spring | 2017 | | 83821 | CS-319 | 2 | Spring | 2018 | 83821 | CS-190 | 2 | Spring | 2017 | | 10101 | CS-347 | 1 | Fall | 2017 | 10101 | CS-101 | 1 | Fall | 2017 | | 10101 | CS-347 | 1 | Fall | 2017 | 10101 | CS-315 | 1 | Spring | 2018 | +-------+-----------+--------+----------+------+-------+-----------+--------+----------+------+ 14 rows in set (0.005 sec) MariaDB [sks]> select * from teaches as T1, teaches as T2 where T1.ID = T2.ID and T1.course_id <> T2.course_id; +-------+-----------+--------+----------+------+-------+-----------+--------+----------+------+ | ID | course_id | sec_id | semester | year | ID | course_id | sec_id | semester | year | +-------+-----------+--------+----------+------+-------+-----------+--------+----------+------+ | 76766 | BIO-101 | 1 | Summer | 2017 | 76766 | BIO-301 | 1 | Summer | 2018 | | 76766 | BIO-301 | 1 | Summer | 2018 | 76766 | BIO-101 | 1 | Summer | 2017 | | 10101 | CS-101 | 1 | Fall | 2017 | 10101 | CS-315 | 1 | Spring | 2018 | | 10101 | CS-101 | 1 | Fall | 2017 | 10101 | CS-347 | 1 | Fall | 2017 | | 45565 | CS-101 | 1 | Spring | 2018 | 45565 | CS-319 | 1 | Spring | 2018 | | 83821 | CS-190 | 1 | Spring | 2017 | 83821 | CS-319 | 2 | Spring | 2018 | | 83821 | CS-190 | 2 | Spring | 2017 | 83821 | CS-319 | 2 | Spring | 2018 | | 10101 | CS-315 | 1 | Spring | 2018 | 10101 | CS-101 | 1 | Fall | 2017 | | 10101 | CS-315 | 1 | Spring | 2018 | 10101 | CS-347 | 1 | Fall | 2017 | | 45565 | CS-319 | 1 | Spring | 2018 | 45565 | CS-101 | 1 | Spring | 2018 | | 83821 | CS-319 | 2 | Spring | 2018 | 83821 | CS-190 | 1 | Spring | 2017 | | 83821 | CS-319 | 2 | Spring | 2018 | 83821 | CS-190 | 2 | Spring | 2017 | | 10101 | CS-347 | 1 | Fall | 2017 | 10101 | CS-101 | 1 | Fall | 2017 | | 10101 | CS-347 | 1 | Fall | 2017 | 10101 | CS-315 | 1 | Spring | 2018 | +-------+-----------+--------+----------+------+-------+-----------+--------+----------+------+ 14 rows in set (0.001 sec) MariaDB [sks]> select ID from teaches as T1, teaches as T2 where T1.ID = T2.ID and T1.course_id <> T2.course_id; ERROR 1052 (23000): Column 'ID' in SELECT is ambiguous MariaDB [sks]> select T1.ID from teaches as T1, teaches as T2 where T1.ID = T2.ID and T1.course_id <> T2.course_id; +-------+ | ID | +-------+ | 76766 | | 76766 | | 10101 | | 10101 | | 45565 | | 83821 | | 83821 | | 10101 | | 10101 | | 45565 | | 83821 | | 83821 | | 10101 | | 10101 | +-------+ 14 rows in set (0.001 sec) MariaDB [sks]> select distinct T1.ID from teaches as T1, teaches as T2 where T1.ID = T2.ID and T1.course_id <> T2.course_id; +-------+ | ID | +-------+ | 76766 | | 10101 | | 45565 | | 83821 | +-------+ 4 rows in set (0.002 sec) MariaDB [sks]> select distinct T1.ID as busy_ID from teaches as T1, teaches as T2 where T1.ID = T2.ID and T1.course_id <> T2.course_id; +---------+ | busy_ID | +---------+ | 76766 | | 10101 | | 45565 | | 83821 | +---------+ 4 rows in set (0.001 sec) MariaDB [sks]> (select course_id from section where semester = 'Fall' and year = '2017'); +-----------+ | course_id | +-----------+ | CS-101 | | CS-347 | | PHY-101 | +-----------+ 3 rows in set (0.004 sec) MariaDB [sks]> (select course_id from section where semester = 'Spring' and year = '2018'); +-----------+ | course_id | +-----------+ | CS-101 | | FIN-201 | | MU-199 | | HIS-351 | | CS-319 | | CS-319 | | CS-315 | +-----------+ 7 rows in set (0.001 sec) MariaDB [sks]> (select course_id from section where semester = 'Fall' and year = '2017') union (select course_id from section where semester = 'Spring' and year = -> '2018'); +-----------+ | course_id | +-----------+ | CS-101 | | CS-347 | | PHY-101 | | FIN-201 | | MU-199 | | HIS-351 | | CS-319 | | CS-315 | +-----------+ 8 rows in set (0.005 sec) MariaDB [sks]> (select course_id from section where semester = 'Fall' and year = '2017') intersect (select course_id from section where semester = 'Spring' and year = '2018'); +-----------+ | course_id | +-----------+ | CS-101 | +-----------+ 1 row in set (0.002 sec) MariaDB [sks]> select course_id from section where (semester = 'Fall' and year = '2017') and (semester = 'Spring' and year = '2018'); Empty set (0.003 sec) MariaDB [sks]> (select course_id from section where semester = 'Fall' and year = '2017'); +-----------+ | course_id | +-----------+ | CS-101 | | CS-347 | | PHY-101 | +-----------+ 3 rows in set (0.001 sec) MariaDB [sks]> (select course_id from section where semester = 'Spring' and year = '2018'); +-----------+ | course_id | +-----------+ | CS-101 | | FIN-201 | | MU-199 | | HIS-351 | | CS-319 | | CS-319 | | CS-315 | +-----------+ 7 rows in set (0.001 sec) MariaDB [sks]> (select course_id from section where semester = 'Fall' and year = '2017') except (select course_id from section where semester = 'Spring' and year = '2018'); +-----------+ | course_id | +-----------+ | CS-347 | | PHY-101 | +-----------+ 2 rows in set (0.001 sec) MariaDB [sks]> (select course_id from section) except (select course_id from section where semester = 'Spring' and year = '2018'); +-----------+ | course_id | +-----------+ | BIO-101 | | BIO-301 | | CS-190 | | CS-347 | | EE-181 | | PHY-101 | +-----------+ 6 rows in set (0.003 sec) MariaDB [sks]> (select distinct dept_name from instructor); +------------+ | dept_name | +------------+ | Biology | | Comp. Sci. | | Elec. Eng. | | Finance | | History | | Music | | Physics | +------------+ 7 rows in set (0.001 sec) MariaDB [sks]> (select dept_name from instructor); +------------+ | dept_name | +------------+ | Biology | | Comp. Sci. | | Comp. Sci. | | Comp. Sci. | | Elec. Eng. | | Finance | | Finance | | History | | History | | Music | | Physics | | Physics | +------------+ 12 rows in set (0.001 sec) MariaDB [sks]> (select distinct dept_name from instructor) union (select dept_name from instructor); +------------+ | dept_name | +------------+ | Biology | | Comp. Sci. | | Elec. Eng. | | Finance | | History | | Music | | Physics | +------------+ 7 rows in set (0.003 sec) MariaDB [sks]> (select dept_name from instructor) union (select dept_name from instructor); +------------+ | dept_name | +------------+ | Biology | | Comp. Sci. | | Elec. Eng. | | Finance | | History | | Music | | Physics | +------------+ 7 rows in set (0.001 sec) MariaDB [sks]> (select dept_name from instructor) union (select ID from instructor); +------------+ | dept_name | +------------+ | Biology | | Comp. Sci. | | Elec. Eng. | | Finance | | History | | Music | | Physics | | 76766 | | 10101 | | 45565 | | 83821 | | 98345 | | 12121 | | 76543 | | 32343 | | 58583 | | 15151 | | 22222 | | 33456 | +------------+ 19 rows in set (0.001 sec) MariaDB [sks]> (select ID from instructor) union (select dept_name from instructor); +------------+ | ID | +------------+ | 76766 | | 10101 | | 45565 | | 83821 | | 98345 | | 12121 | | 76543 | | 32343 | | 58583 | | 15151 | | 22222 | | 33456 | | Biology | | Comp. Sci. | | Elec. Eng. | | Finance | | History | | Music | | Physics | +------------+ 19 rows in set (0.001 sec) MariaDB [sks]> (select ID,dept_name from instructor) union (select ID,dept_name from instructor); +-------+------------+ | ID | dept_name | +-------+------------+ | 76766 | Biology | | 10101 | Comp. Sci. | | 45565 | Comp. Sci. | | 83821 | Comp. Sci. | | 98345 | Elec. Eng. | | 12121 | Finance | | 76543 | Finance | | 32343 | History | | 58583 | History | | 15151 | Music | | 22222 | Physics | | 33456 | Physics | +-------+------------+ 12 rows in set (0.001 sec) MariaDB [sks]> explain (select dept_name from instructor) union (select dept_name from instructor); +------+--------------+------------+-------+---------------+-----------+---------+------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +------+--------------+------------+-------+---------------+-----------+---------+------+------+-------------+ | 1 | PRIMARY | instructor | index | NULL | dept_name | 83 | NULL | 12 | Using index | | 2 | UNION | instructor | index | NULL | dept_name | 83 | NULL | 12 | Using index | | NULL | UNION RESULT | | ALL | NULL | NULL | NULL | NULL | NULL | | +------+--------------+------------+-------+---------------+-----------+---------+------+------+-------------+ 3 rows in set (0.001 sec) MariaDB [sks]> (select salary from instructor) union (select salary from instructor); +----------+ | salary | +----------+ | 65000.00 | | 90000.00 | | 40000.00 | | 95000.00 | | 60000.00 | | 87000.00 | | 75000.00 | | 62000.00 | | 80000.00 | | 72000.00 | | 92000.00 | +----------+ 11 rows in set (0.001 sec) MariaDB [sks]> explain (select salary from instructor) union (select salary from instructor); +------+--------------+------------+------+---------------+------+---------+------+------+-------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +------+--------------+------------+------+---------------+------+---------+------+------+-------+ | 1 | PRIMARY | instructor | ALL | NULL | NULL | NULL | NULL | 12 | | | 2 | UNION | instructor | ALL | NULL | NULL | NULL | NULL | 12 | | | NULL | UNION RESULT | | ALL | NULL | NULL | NULL | NULL | NULL | | +------+--------------+------------+------+---------------+------+---------+------+------+-------+ 3 rows in set (0.001 sec) MariaDB [sks]> select avg(salary) from instructor; +--------------+ | avg(salary) | +--------------+ | 74833.333333 | +--------------+ 1 row in set (0.004 sec) MariaDB [sks]> select avg(salary) as avg_salary from instructor; +--------------+ | avg_salary | +--------------+ | 74833.333333 | +--------------+ 1 row in set (0.001 sec) MariaDB [sks]> select avg(salary) as avg_salary from -> (select * from instructor where salary > 70000); ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '' at line 2 MariaDB [sks]> select avg(salary) as avg_salary from (select * from instructor where salary > 70000) as newtable; +--------------+ | avg_salary | +--------------+ | 83875.000000 | +--------------+ 1 row in set (0.003 sec) MariaDB [sks]> select avg(salary) as avg_salary from (select * from instructor where salary > 70000) as newtable; +--------------+ | avg_salary | +--------------+ | 83875.000000 | +--------------+ 1 row in set (0.001 sec) MariaDB [sks]> select * from newtable; ERROR 1146 (42S02): Table 'sks.newtable' doesn't exist MariaDB [sks]> select avg(salary) as avg_salary from (select * from instructor where salary > 70000) as newtable; +--------------+ | avg_salary | +--------------+ | 83875.000000 | +--------------+ 1 row in set (0.001 sec) MariaDB [sks]> select avg(salary) from instructor where salary > 70000; +--------------+ | avg(salary) | +--------------+ | 83875.000000 | +--------------+ 1 row in set (0.001 sec) MariaDB [sks]> with avg_budget(value) as -> (select avg(budget) from department) -> select dept_name from department, avg_budget where department.budget > avg_budget.value; +------------+ | dept_name | +------------+ | Biology | | Comp. Sci. | | Finance | +------------+ 3 rows in set (0.004 sec) MariaDB [sks]> select avg(budget) from department; +--------------+ | avg(budget) | +--------------+ | 85000.000000 | +--------------+ 1 row in set (0.001 sec) MariaDB [sks]> select * from department; +------------+----------+-----------+ | dept_name | building | budget | +------------+----------+-----------+ | Biology | Watson | 90000.00 | | Comp. Sci. | Taylor | 100000.00 | | Elec. Eng. | Taylor | 85000.00 | | Finance | Painter | 120000.00 | | History | Painter | 50000.00 | | Music | Packard | 80000.00 | | Physics | Watson | 70000.00 | +------------+----------+-----------+ 7 rows in set (0.001 sec) MariaDB [sks]> select avg(budget) from department; +--------------+ | avg(budget) | +--------------+ | 85000.000000 | +--------------+ 1 row in set (0.001 sec) MariaDB [sks]> select * from department where budget > avg(budget); ERROR 1111 (HY000): Invalid use of group function MariaDB [sks]> select * from department where budget > (select avg(budget) from department); +------------+----------+-----------+ | dept_name | building | budget | +------------+----------+-----------+ | Biology | Watson | 90000.00 | | Comp. Sci. | Taylor | 100000.00 | | Finance | Painter | 120000.00 | +------------+----------+-----------+ 3 rows in set (0.002 sec)