MariaDB [sks]> 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.001 sec) MariaDB [sks]> select avg(budget) from department; +--------------+ | avg(budget) | +--------------+ | 85000.000000 | +--------------+ 1 row in set (0.001 sec) MariaDB [sks]> select dept_name from department where budget > (select avg(budget) from department); +------------+ | dept_name | +------------+ | Biology | | Comp. Sci. | | Finance | +------------+ 3 rows in set (0.001 sec) MariaDB [sks]> explain select dept_name from department where budget > (select avg(budget) from department); +------+-------------+------------+------+---------------+------+---------+------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +------+-------------+------------+------+---------------+------+---------+------+------+-------------+ | 1 | PRIMARY | department | ALL | NULL | NULL | NULL | NULL | 7 | Using where | | 2 | SUBQUERY | department | ALL | NULL | NULL | NULL | NULL | 7 | | +------+-------------+------------+------+---------------+------+---------+------+------+-------------+ 2 rows in set (0.001 sec) MariaDB [sks]> explain with avg_budget(value) as (select avg(budget) from department) select dept_name from department, avg_budget where department.budget > avg_budget.value; +------+-------------+------------+------+---------------+------+---------+------+------+-------------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +------+-------------+------------+------+---------------+------+---------+------+------+-------------------------------------------------+ | 1 | PRIMARY | department | ALL | NULL | NULL | NULL | NULL | 7 | | | 1 | PRIMARY | | ALL | NULL | NULL | NULL | NULL | 7 | Using where; Using join buffer (flat, BNL join) | | 2 | DERIVED | department | ALL | NULL | NULL | NULL | NULL | 7 | | +------+-------------+------------+------+---------------+------+---------+------+------+-------------------------------------------------+ 3 rows in set (0.001 sec) MariaDB [sks]> select * from instructor where name like "%ri%"; +-------+------------+------------+----------+ | ID | name | dept_name | salary | +-------+------------+------------+----------+ | 10101 | Srinivasan | Comp. Sci. | 65000.00 | | 58583 | Califieri | History | 62000.00 | | 76766 | Crick | Biology | 72000.00 | +-------+------------+------------+----------+ 3 rows in set (0.001 sec) MariaDB [sks]> select * from instructor where name like "%ri_%"; +-------+------------+------------+----------+ | ID | name | dept_name | salary | +-------+------------+------------+----------+ | 10101 | Srinivasan | Comp. Sci. | 65000.00 | | 76766 | Crick | Biology | 72000.00 | +-------+------------+------------+----------+ 2 rows in set (0.001 sec) MariaDB [sks]> select name from instructor order by name; +------------+ | name | +------------+ | Brandt | | Califieri | | Crick | | Einstein | | El Said | | Gold | | Katz | | Kim | | Mozart | | Singh | | Srinivasan | | Wu | +------------+ 12 rows in set (0.001 sec) MariaDB [sks]> select name from instructor order by name desc; +------------+ | name | +------------+ | Wu | | Srinivasan | | Singh | | Mozart | | Kim | | Katz | | Gold | | El Said | | Einstein | | Crick | | Califieri | | Brandt | +------------+ 12 rows in set (0.001 sec) MariaDB [sks]> select name, dept_name from instructor order by dept_name,name asc; +------------+------------+ | name | dept_name | +------------+------------+ | Crick | Biology | | Brandt | Comp. Sci. | | Katz | Comp. Sci. | | Srinivasan | Comp. Sci. | | Kim | Elec. Eng. | | Singh | Finance | | Wu | Finance | | Califieri | History | | El Said | History | | Mozart | Music | | Einstein | Physics | | Gold | Physics | +------------+------------+ 12 rows in set (0.001 sec) MariaDB [sks]> select name, dept_name from instructor order by name,dept_name asc; +------------+------------+ | name | dept_name | +------------+------------+ | Brandt | Comp. Sci. | | Califieri | History | | Crick | Biology | | Einstein | Physics | | El Said | History | | Gold | Physics | | Katz | Comp. Sci. | | Kim | Elec. Eng. | | Mozart | Music | | Singh | Finance | | Srinivasan | Comp. Sci. | | Wu | Finance | +------------+------------+ 12 rows in set (0.001 sec) MariaDB [sks]> select count(name) from instructor where salary > 80000; +-------------+ | count(name) | +-------------+ | 4 | +-------------+ 1 row in set (0.001 sec) MariaDB [sks]> select count(salary) from instructor where salary > 80000; +---------------+ | count(salary) | +---------------+ | 4 | +---------------+ 1 row in set (0.001 sec) MariaDB [sks]> select count(*) from instructor where salary > 80000; +----------+ | count(*) | +----------+ | 4 | +----------+ 1 row in set (0.001 sec) MariaDB [sks]> select count(*) from instructor; +----------+ | count(*) | +----------+ | 12 | +----------+ 1 row in set (0.001 sec) MariaDB [sks]> select count(dept_name) from instructor; +------------------+ | count(dept_name) | +------------------+ | 12 | +------------------+ 1 row in set (0.001 sec) MariaDB [sks]> select count(distinct dept_name) from instructor; +---------------------------+ | count(distinct dept_name) | +---------------------------+ | 7 | +---------------------------+ 1 row in set (0.001 sec) MariaDB [sks]> select dept_name,avg(salary) from instructor group by dept_name; +------------+--------------+ | dept_name | avg(salary) | +------------+--------------+ | Biology | 72000.000000 | | Comp. Sci. | 77333.333333 | | Elec. Eng. | 80000.000000 | | Finance | 85000.000000 | | History | 61000.000000 | | Music | 40000.000000 | | Physics | 91000.000000 | +------------+--------------+ 7 rows in set (0.001 sec) MariaDB [sks]> select name,avg(salary) from instructor group by dept_name; +------------+--------------+ | name | avg(salary) | +------------+--------------+ | Crick | 72000.000000 | | Srinivasan | 77333.333333 | | Kim | 80000.000000 | | Wu | 85000.000000 | | El Said | 61000.000000 | | Mozart | 40000.000000 | | Einstein | 91000.000000 | +------------+--------------+ 7 rows in set (0.001 sec) MariaDB [sks]> select dept_name,avg(salary) from instructor group by dept_name having max(salary) > 80000; +------------+--------------+ | dept_name | avg(salary) | +------------+--------------+ | Comp. Sci. | 77333.333333 | | Finance | 85000.000000 | | Physics | 91000.000000 | +------------+--------------+ 3 rows in set (0.001 sec) MariaDB [sks]> select name from instructor group by dept_name having salary > avg(salary); ERROR 1054 (42S22): Unknown column 'salary' in 'HAVING' MariaDB [sks]> select name from instructor group by dept_name having instructor.salary > avg(salary); ERROR 1054 (42S22): Unknown column 'instructor.salary' in 'HAVING' 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.001 sec) MariaDB [sks]> select * from information_schema.check_constraints; +--------------------+-------------------+-------------+-----------------+--------+---------------------------------------------------+ | CONSTRAINT_CATALOG | CONSTRAINT_SCHEMA | TABLE_NAME | CONSTRAINT_NAME | LEVEL | CHECK_CLAUSE | +--------------------+-------------------+-------------+-----------------+--------+---------------------------------------------------+ | def | sks | department | budget | Column | `budget` > 0 | | def | sks | time_slot | start_hr | Column | `start_hr` >= 0 and `start_hr` < 24 | | def | sks | time_slot | start_min | Column | `start_min` >= 0 and `start_min` < 60 | | def | sks | time_slot | end_hr | Column | `end_hr` >= 0 and `end_hr` < 24 | | def | sks | time_slot | end_min | Column | `end_min` >= 0 and `end_min` < 60 | | def | sks | student | tot_cred | Column | `tot_cred` >= 0 | | def | sks | section | semester | Column | `semester` in ('Fall','Winter','Spring','Summer') | | def | sks | section | year | Column | `year` > 1701 and `year` < 2100 | | def | sks | instructor | salary | Column | `salary` > 29000 | | def | sks | course | credits | Column | `credits` > 0 | | def | mysql | global_priv | Priv | Column | json_valid(`Priv`) | | def | mysql | servers | Options | Column | json_valid(`Options`) | +--------------------+-------------------+-------------+-----------------+--------+---------------------------------------------------+ 12 rows in set (0.010 sec) MariaDB [sks]> select table_name,constraint_name,check_clause from information_schema.check_constraints; +-------------+-----------------+---------------------------------------------------+ | table_name | constraint_name | check_clause | +-------------+-----------------+---------------------------------------------------+ | department | budget | `budget` > 0 | | time_slot | start_hr | `start_hr` >= 0 and `start_hr` < 24 | | time_slot | start_min | `start_min` >= 0 and `start_min` < 60 | | time_slot | end_hr | `end_hr` >= 0 and `end_hr` < 24 | | time_slot | end_min | `end_min` >= 0 and `end_min` < 60 | | student | tot_cred | `tot_cred` >= 0 | | section | semester | `semester` in ('Fall','Winter','Spring','Summer') | | section | year | `year` > 1701 and `year` < 2100 | | instructor | salary | `salary` > 29000 | | course | credits | `credits` > 0 | | global_priv | Priv | json_valid(`Priv`) | | servers | Options | json_valid(`Options`) | +-------------+-----------------+---------------------------------------------------+ 12 rows in set (0.008 sec) MariaDB [sks]> select * from instructor; +-------+------------+------------+----------+ | ID | name | dept_name | salary | +-------+------------+------------+----------+ | 10101 | Srinivasan | Comp. Sci. | 65000.00 | | 12121 | Wu | Finance | 90000.00 | | 15151 | Mozart | Music | 40000.00 | | 22222 | Einstein | Physics | 95000.00 | | 32343 | El Said | History | 60000.00 | | 33456 | Gold | Physics | 87000.00 | | 45565 | Katz | Comp. Sci. | 75000.00 | | 58583 | Califieri | History | 62000.00 | | 76543 | Singh | Finance | 80000.00 | | 76766 | Crick | Biology | 72000.00 | | 83821 | Brandt | Comp. Sci. | 92000.00 | | 98345 | Kim | Elec. Eng. | 80000.00 | +-------+------------+------------+----------+ 12 rows in set (0.001 sec) MariaDB [sks]> update instructor set salary = 1.1*salary; Query OK, 12 rows affected (0.004 sec) Rows matched: 12 Changed: 12 Warnings: 0 MariaDB [sks]> select * from instructor; +-------+------------+------------+-----------+ | ID | name | dept_name | salary | +-------+------------+------------+-----------+ | 10101 | Srinivasan | Comp. Sci. | 71500.00 | | 12121 | Wu | Finance | 99000.00 | | 15151 | Mozart | Music | 44000.00 | | 22222 | Einstein | Physics | 104500.00 | | 32343 | El Said | History | 66000.00 | | 33456 | Gold | Physics | 95700.00 | | 45565 | Katz | Comp. Sci. | 82500.00 | | 58583 | Califieri | History | 68200.00 | | 76543 | Singh | Finance | 88000.00 | | 76766 | Crick | Biology | 79200.00 | | 83821 | Brandt | Comp. Sci. | 101200.00 | | 98345 | Kim | Elec. Eng. | 88000.00 | +-------+------------+------------+-----------+ 12 rows in set (0.001 sec) MariaDB [sks]> update instructor set salary = 1.1*salary where dept_name = "Physics"; Query OK, 2 rows affected (0.004 sec) Rows matched: 2 Changed: 2 Warnings: 0 MariaDB [sks]> select * from instructor; +-------+------------+------------+-----------+ | ID | name | dept_name | salary | +-------+------------+------------+-----------+ | 10101 | Srinivasan | Comp. Sci. | 71500.00 | | 12121 | Wu | Finance | 99000.00 | | 15151 | Mozart | Music | 44000.00 | | 22222 | Einstein | Physics | 114950.00 | | 32343 | El Said | History | 66000.00 | | 33456 | Gold | Physics | 105270.00 | | 45565 | Katz | Comp. Sci. | 82500.00 | | 58583 | Califieri | History | 68200.00 | | 76543 | Singh | Finance | 88000.00 | | 76766 | Crick | Biology | 79200.00 | | 83821 | Brandt | Comp. Sci. | 101200.00 | | 98345 | Kim | Elec. Eng. | 88000.00 | +-------+------------+------------+-----------+ 12 rows in set (0.001 sec)