Friday, 1 October 2021

SQL group by min and max sql videos in telugu 40

 https://youtu.be/y2GKwl_2diI

------------------------------------------

Setting environment for using XAMPP for Windows.
# ram.log
'ram.log' is not recognized as an internal or external command,
operable program or batch file.

# ram.cmd
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 8
Server version: 10.4.18-MariaDB mariadb.org binary distribution

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 classicmodels;
Database changed
MariaDB [classicmodels]> select * from tab;
ERROR 1146 (42S02): Table 'classicmodels.tab' doesn't exist
MariaDB [classicmodels]> show tables;
+-------------------------+
| Tables_in_classicmodels |
+-------------------------+
| customers               |
| employees               |
| offices                 |
| orderdetails            |
| orders                  |
| payments                |
| productlines            |
| products                |
+-------------------------+
8 rows in set (0.002 sec)

MariaDB [classicmodels]> select * from employess limit 1;
ERROR 1146 (42S02): Table 'classicmodels.employess' doesn't exist
MariaDB [classicmodels]> select * from employess limit 1;
ERROR 1146 (42S02): Table 'classicmodels.employess' doesn't exist
MariaDB [classicmodels]> select * from employees limit 1;
+----------------+----------+-----------+-----------+------------------------------+------------+-----------+-----------+
| employeeNumber | lastName | firstName | extension | email                        | officeCode | reportsTo | jobTitle  |
+----------------+----------+-----------+-----------+------------------------------+------------+-----------+-----------+
|           1002 | Murphy   | Diane     | x5800     | [email protected]classicmodelcars.com | 1          |      NULL | President |
+----------------+----------+-----------+-----------+------------------------------+------------+-----------+-----------+
1 row in set (0.112 sec)

MariaDB [classicmodels]> select * from orders limit 1;
+-------------+------------+--------------+-------------+---------+----------+----------------+
| orderNumber | orderDate  | requiredDate | shippedDate | status  | comments | customerNumber |
+-------------+------------+--------------+-------------+---------+----------+----------------+
|       10100 | 2003-01-06 | 2003-01-13   | 2003-01-10  | Shipped | NULL     |            363 |
+-------------+------------+--------------+-------------+---------+----------+----------------+
1 row in set (0.067 sec)

MariaDB [classicmodels]> select * from ordersdetails limit 1;
ERROR 1146 (42S02): Table 'classicmodels.ordersdetails' doesn't exist
MariaDB [classicmodels]> select * from orderdetails limit 1;
+-------------+-------------+-----------------+-----------+-----------------+
| orderNumber | productCode | quantityOrdered | priceEach | orderLineNumber |
+-------------+-------------+-----------------+-----------+-----------------+
|       10100 | S18_1749    |              30 |    136.00 |               3 |
+-------------+-------------+-----------------+-----------+-----------------+
1 row in set (0.024 sec)

MariaDB [classicmodels]> select * from orderdetails limit 2;
+-------------+-------------+-----------------+-----------+-----------------+
| orderNumber | productCode | quantityOrdered | priceEach | orderLineNumber |
+-------------+-------------+-----------------+-----------+-----------------+
|       10100 | S18_1749    |              30 |    136.00 |               3 |
|       10100 | S18_2248    |              50 |     55.09 |               2 |
+-------------+-------------+-----------------+-----------+-----------------+
2 rows in set (0.001 sec)

MariaDB [classicmodels]> select sum(quantityordered) from orderdetails;
+----------------------+
| sum(quantityordered) |
+----------------------+
|               105516 |
+----------------------+
1 row in set (0.071 sec)

MariaDB [classicmodels]> select sum(quantityordered),ordernumber from orderdetails
    -> group by ordernumber limit 10;
+----------------------+-------------+
| sum(quantityordered) | ordernumber |
+----------------------+-------------+
|                  151 |       10100 |
|                  142 |       10101 |
|                   80 |       10102 |
|                  541 |       10103 |
|                  443 |       10104 |
|                  545 |       10105 |
|                  675 |       10106 |
|                  229 |       10107 |
|                  561 |       10108 |
|                  212 |       10109 |
+----------------------+-------------+
10 rows in set (0.034 sec)

MariaDB [classicmodels]> select sum(quantityordered),ordernumber from orderdetails
    -> select sum(quantityordered) from orderdetails;
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 'select sum(quantityordered) from
orderdetails' at line 2
MariaDB [classicmodels]> select avg(quantityordered),ordernumber from orderdetails
    -> group by ordernumber limit 10;
+----------------------+-------------+
| avg(quantityordered) | ordernumber |
+----------------------+-------------+
|              37.7500 |       10100 |
|              35.5000 |       10101 |
|              40.0000 |       10102 |
|              33.8125 |       10103 |
|              34.0769 |       10104 |
|              36.3333 |       10105 |
|              37.5000 |       10106 |
|              28.6250 |       10107 |
|              35.0625 |       10108 |
|              35.3333 |       10109 |
+----------------------+-------------+
10 rows in set (0.001 sec)

MariaDB [classicmodels]> select sum(quantityordered),ordernumber from orderdetails
    -> group by ordernumber limit 10;
+----------------------+-------------+
| sum(quantityordered) | ordernumber |
+----------------------+-------------+
|                  151 |       10100 |
|                  142 |       10101 |
|                   80 |       10102 |
|                  541 |       10103 |
|                  443 |       10104 |
|                  545 |       10105 |
|                  675 |       10106 |
|                  229 |       10107 |
|                  561 |       10108 |
|                  212 |       10109 |
+----------------------+-------------+
10 rows in set (0.002 sec)

MariaDB [classicmodels]> select sum(quantityordered),ordernumber from orderdetails
    -> group by ordernumber order by sum(quantityordered) limit 1;
+----------------------+-------------+
| sum(quantityordered) | ordernumber |
+----------------------+-------------+
|                   15 |       10408 |
+----------------------+-------------+
1 row in set (0.034 sec)

MariaDB [classicmodels]> select sum(quantityordered),ordernumber from orderdetails
    -> group by ordernumber order by sum(quantityordered) desc limit 1;
+----------------------+-------------+
| sum(quantityordered) | ordernumber |
+----------------------+-------------+
|                  717 |       10222 |
+----------------------+-------------+
1 row in set (0.008 sec)

MariaDB [classicmodels]>

No comments:

Post a Comment

Note: only a member of this blog may post a comment.