Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 131
Server version: 10.11.6-MariaDB-0+deb12u1 Debian 12

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 [(none)]> use sksunivdemo;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
MariaDB [sksunivdemo]> select name,dept_name from instructor order by dept_name,name;
+------------+------------+
| 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.018 sec)

MariaDB [sksunivdemo]> select dept_name,name from instructor order by dept_name,name;
+------------+------------+
| dept_name  | name       |
+------------+------------+
| Biology    | Crick      |
| Comp. Sci. | Brandt     |
| Comp. Sci. | Katz       |
| Comp. Sci. | Srinivasan |
| Elec. Eng. | Kim        |
| Finance    | Singh      |
| Finance    | Wu         |
| History    | Califieri  |
| History    | El Said    |
| Music      | Mozart     |
| Physics    | Einstein   |
| Physics    | Gold       |
+------------+------------+
12 rows in set (0.093 sec)

MariaDB [sksunivdemo]> select dept_name,avg(salary) from instructor group by dept_name,name;
+------------+--------------+
| dept_name  | avg(salary)  |
+------------+--------------+
| Biology    | 72000.000000 |
| Comp. Sci. | 92000.000000 |
| Comp. Sci. | 75000.000000 |
| Comp. Sci. | 65000.000000 |
| Elec. Eng. | 80000.000000 |
| Finance    | 80000.000000 |
| Finance    | 90000.000000 |
| History    | 62000.000000 |
| History    | 60000.000000 |
| Music      | 40000.000000 |
| Physics    | 95000.000000 |
| Physics    | 87000.000000 |
+------------+--------------+
12 rows in set (0.081 sec)

MariaDB [sksunivdemo]> 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.003 sec)

MariaDB [sksunivdemo]> select dept_name,avg(salary) from instructor group by dept_name order by dept_name desc;
+------------+--------------+
| dept_name  | avg(salary)  |
+------------+--------------+
| Physics    | 91000.000000 |
| Music      | 40000.000000 |
| History    | 61000.000000 |
| Finance    | 85000.000000 |
| Elec. Eng. | 80000.000000 |
| Comp. Sci. | 77333.333333 |
| Biology    | 72000.000000 |
+------------+--------------+
7 rows in set (0.032 sec)

MariaDB [sksunivdemo]> select name from instructor where name like "%ri%";
+------------+
| name       |
+------------+
| Srinivasan |
| Califieri  |
| Crick      |
+------------+
3 rows in set (0.081 sec)

MariaDB [sksunivdemo]> select name from instructor where name like "%ri_%";
+------------+
| name       |
+------------+
| Srinivasan |
| Crick      |
+------------+
2 rows in set (0.001 sec)

MariaDB [sksunivdemo]> select name from instructor where name like "%__ri_%";
Empty set (0.001 sec)

MariaDB [sksunivdemo]> select name from instructor where name like "%_ri_%";
+------------+
| name       |
+------------+
| Srinivasan |
| Crick      |
+------------+
2 rows in set (0.066 sec)

MariaDB [sksunivdemo]> select name from instructor where dept_name like "%Comp%Sci%";
+------------+
| name       |
+------------+
| Srinivasan |
| Katz       |
| Brandt     |
+------------+
3 rows in set (0.008 sec)

MariaDB [sksunivdemo]> select name from instructor where dept_name like "%Comp %Sci%";
Empty set (0.002 sec)

MariaDB [sksunivdemo]> select 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 1

MariaDB [sksunivdemo]> select avg(salary) from (select * from instructor where salary > 70000) as something;
+--------------+
| avg(salary)  |
+--------------+
| 83875.000000 |
+--------------+
1 row in set (0.088 sec)

MariaDB [sksunivdemo]> select something.name from (select * from instructor where salary > 70000) as something;
+----------+
| name     |
+----------+
| Wu       |
| Einstein |
| Gold     |
| Katz     |
| Singh    |
| Crick    |
| Brandt   |
| Kim      |
+----------+
8 rows in set (0.080 sec)

MariaDB [sksunivdemo]> select * from (select * from instructor where salary > 70000) as something;
+-------+----------+------------+----------+
| ID    | name     | dept_name  | salary   |
+-------+----------+------------+----------+
| 12121 | Wu       | Finance    | 90000.00 |
| 22222 | Einstein | Physics    | 95000.00 |
| 33456 | Gold     | Physics    | 87000.00 |
| 45565 | Katz     | Comp. Sci. | 75000.00 |
| 76543 | Singh    | Finance    | 80000.00 |
| 76766 | Crick    | Biology    | 72000.00 |
| 83821 | Brandt   | Comp. Sci. | 92000.00 |
| 98345 | Kim      | Elec. Eng. | 80000.00 |
+-------+----------+------------+----------+
8 rows in set (0.001 sec)

MariaDB [sksunivdemo]> select * from something;
ERROR 1146 (42S02): Table 'sksunivdemo.something' doesn't exist

MariaDB [sksunivdemo]> select upper.name,lower.name from (select * from instructor where salary > 70000) as upper, (select * from instructor where salary <= 70000) as lower;
+----------+------------+
| name     | name       |
+----------+------------+
| Wu       | Srinivasan |
| Wu       | Mozart     |
| Wu       | El Said    |
| Wu       | Califieri  |
| Einstein | Srinivasan |
| Einstein | Mozart     |
| Einstein | El Said    |
| Einstein | Califieri  |
| Gold     | Srinivasan |
| Gold     | Mozart     |
| Gold     | El Said    |
| Gold     | Califieri  |
| Katz     | Srinivasan |
| Katz     | Mozart     |
| Katz     | El Said    |
| Katz     | Califieri  |
| Singh    | Srinivasan |
| Singh    | Mozart     |
| Singh    | El Said    |
| Singh    | Califieri  |
| Crick    | Srinivasan |
| Crick    | Mozart     |
| Crick    | El Said    |
| Crick    | Califieri  |
| Brandt   | Srinivasan |
| Brandt   | Mozart     |
| Brandt   | El Said    |
| Brandt   | Califieri  |
| Kim      | Srinivasan |
| Kim      | Mozart     |
| Kim      | El Said    |
| Kim      | Califieri  |
+----------+------------+
32 rows in set (0.009 sec)

MariaDB [sksunivdemo]> select lower.name,upper.name from (select * from instructor where salary > 70000) as upper, (select * from instructor where salary <= 70000) as lower;
+------------+----------+
| name       | name     |
+------------+----------+
| Srinivasan | Wu       |
| Mozart     | Wu       |
| El Said    | Wu       |
| Califieri  | Wu       |
| Srinivasan | Einstein |
| Mozart     | Einstein |
| El Said    | Einstein |
| Califieri  | Einstein |
| Srinivasan | Gold     |
| Mozart     | Gold     |
| El Said    | Gold     |
| Califieri  | Gold     |
| Srinivasan | Katz     |
| Mozart     | Katz     |
| El Said    | Katz     |
| Califieri  | Katz     |
| Srinivasan | Singh    |
| Mozart     | Singh    |
| El Said    | Singh    |
| Califieri  | Singh    |
| Srinivasan | Crick    |
| Mozart     | Crick    |
| El Said    | Crick    |
| Califieri  | Crick    |
| Srinivasan | Brandt   |
| Mozart     | Brandt   |
| El Said    | Brandt   |
| Califieri  | Brandt   |
| Srinivasan | Kim      |
| Mozart     | Kim      |
| El Said    | Kim      |
| Califieri  | Kim      |
+------------+----------+
32 rows in set (0.001 sec)

MariaDB [sksunivdemo]> select max(lower.salary),min(upper.salary) from (select * from instructor where salary > 70000) as upper, (select * from instructor where salary <= 70000) as lower;
+-------------------+-------------------+
| max(lower.salary) | min(upper.salary) |
+-------------------+-------------------+
|          65000.00 |          72000.00 |
+-------------------+-------------------+
1 row in set (0.001 sec)

MariaDB [sksunivdemo]> with avg_budget(value) as (select avg(budget) from department)
    -> select dept_name from department,avg_budget where budget > value;
+------------+
| dept_name  |
+------------+
| Biology    |
| Comp. Sci. |
| Finance    |
+------------+
3 rows in set (0.071 sec)

MariaDB [sksunivdemo]> select dept_name from department, (select avg(budget) from department) as avg_budget(value) where budget > value;
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 '(value) where budget > value' at line 1

MariaDB [sksunivdemo]> select dept_name from department, (select avg(budget) as value from department) as avg_budget where budget > avg_budget.value;
+------------+
| dept_name  |
+------------+
| Biology    |
| Comp. Sci. |
| Finance    |
+------------+
3 rows in set (0.000 sec)