14. Laboratoriya ishi Mavzu : Agregat funksiyalardan foydalanib so’rovlar yaratish

7 rows in set (0.00 sec)  

  

mysql> SELECT AVG(Price) AS Average_Price FROM Products;  

+---------------+  

| Average_Price |  

+---------------+  

|    46714.2857 |  

+---------------+  

1 row in set (0.10 sec)  

  

mysql> SELECT AVG(Price) FROM Products  

     WHERE Manufacturer='Apple';  

+------------+  

| AVG(Price) |  

+------------+  

| 53000.0000 |  

+------------+  

1 row in set (0.00 sec)  

  

mysql> SELECT AVG(Price * ProductCount) FROM Products  

---

     ;  

+---------------------------+  

| AVG(Price * ProductCount) |  

+---------------------------+  

|               145142.8571 |  

+---------------------------+  

1 row in set (0.00 sec)  

  

mysql> SELECT AVG(Price * ProductCount)  AS JAMI FROM Products  

     ;  

+-------------+  

| JAMI        |  

+-------------+  

| 145142.8571 |  

+-------------+  

1 row in set (0.00 sec)  

  

mysql>  

mysql> SELECT COUNT(*) FROM Products;  

+----------+  

| COUNT(*) |  

+----------+  

|        7     |  

+----------+  

1 row in set (0.13 sec)  

---

  

mysql>  

mysql> SELECT COUNT(Manufacturer) FROM Products;  

+---------------------+  

| COUNT(Manufacturer) |  

+---------------------+  

|                   7         |  

+---------------------+  

1 row in set (0.00 sec)  

  

mysql> SELECT MIN(Price), MAX(Price) FROM Products;  

+------------+------------+  

| MIN(Price) | MAX(Price) |  

+------------+------------+  

|      28000 |      76000 |  

+------------+------------+  

1 row in set (0.05 sec)  

  

mysql> SELECT SUM(ProductCount) FROM Products;  

+-------------------+  

| SUM(ProductCount) |  

+-------------------+  

|                24       |  

+-------------------+  

1 row in set (0.00 sec)  

---

  

mysql> SELECT SUM(ProductCount * Price) FROM Products      ;  

+---------------------------+  

| SUM(ProductCount * Price) |  

+---------------------------+  

|                   1016000 |  

+---------------------------+  

1 row in set (0.00 sec)  

  

mysql> SELECT COUNT(DISTINCT Manufacturer) FROM Products;  

+------------------------------+  

| COUNT(DISTINCT Manufacturer) |  

+------------------------------+  

|                            4            |  

+------------------------------+  

1 row in set (0.05 sec)  

  

mysql> SELECT COUNT(ALL Manufacturer) FROM Products;  

+-------------------------+  

| COUNT(ALL Manufacturer) |  

+-------------------------+  

|                       7           |  

+-------------------------+  

1 row in set (0.00 sec)  

  

---

mysql> SELECT COUNT(*) AS ProdCount,  

            SUM(ProductCount) AS TotalCount,  

            MIN(Price) AS MinPrice,  

            MAX(Price) AS MaxPrice,  

            AVG(Price) AS AvgPrice  

     FROM Products;  

+-----------+------------+----------+----------+------------+  

| ProdCount | TotalCount | MinPrice | MaxPrice | AvgPrice   |  

+-----------+------------+----------+----------+------------+  

|         7 |         24 |    28000 |    76000 | 46714.2857 |  

+-----------+------------+----------+----------+------------+  

1 row in set (0.00 sec)  

  

mysql> SELECT GROUP_CONCAT(Price) FROM Products;  

+-------------------------------------------+  

| GROUP_CONCAT(Price)                       |  

+-------------------------------------------+  

| 76000,51000,32000,56000,46000,28000,38000 |  

+-------------------------------------------+  

1 row in set (0.00 sec)  

  

mysql> SELECT GROUP_CONCAT(PRODUCTNAME) FROM 

Products;  

+-----------------------------------------------------------------+  

| GROUP_CONCAT(PRODUCTNAME)                                       |  

+-----------------------------------------------------------------+  

---

| iPhone X,iPhone 8,iPhone 7,Galaxy S9,Galaxy S8,Honor 10,Nokia 8 | +--

---------------------------------------------------------------+  

1 row in set (0.00 sec)  

  

Topshiriq: Har bir talaba o’ziga berilgan predmet soha mavzusi bo’yicha 

AVG,SUM,  MIN,  MAX,  GROUP_CONCAT  operatoridan  foydalanib  so’rovlar 

yaratishi va natijasi bilan ko’rsatib o’tishi  kerak.