Have you tried the query on any database?

|
Sandip Londhe 2022-06-30 11:47:35
pro_name | com_name | price |
————- ———– ——-
| Motherboard | Asus | 3200 |
| Keyboard | Frontech | 550 |
| ZIP | Zebronics | 250 |
| Monitor | Samsung | 5000 |
| DVD | iBall | 900 |
| Printer | Epison | 2600
joyi Paul 2022-06-30 11:47:53
😃hi, anyone needs free study material for web development. Message me outside. I am ready to help you 🥰 Message me outside ☺️
Swapnil 2022-06-30 12:37:00
Sandip Londhe 2022-06-30 11:45:53
select i.pro_name,c.com_name, max(i.pro_price) price from company_mast c inner join item_mast i on c.com_id = i.pro_com group by com_name;

First of all this query will give syntax error as i.pro_name is not provided in group by clause and
Secondly this will bring 9 rows in output as you have mentioned i.pro_name hence it will consider each product and there own price as max price

SS 2022-06-30 12:38:17
. Write a stored procedure that accepts the month and year as inputs and prints the ordernumber, orderdate and status of the orders placed in that month.

Example: call order_status(2005, 11);

SS 2022-06-30 12:38:54
can someone help me with?
Sandip Londhe 2022-06-30 12:43:49
Swapnil 2022-06-30 12:37:00
First of all this query will give syntax error as i.pro_name is not provided in group by clause and
Secondly this will bring 9 rows in output as you have mentioned i.pro_name hence it will consider each product and there own price as max price

Copy paste the query , it gives the same result as you , only order by needs to be add and sequence of columns has to be adjusted

Swapnil 2022-06-30 12:45:41
Sandip Londhe 2022-06-30 12:43:49
Copy paste the query , it gives the same result as you , only order by needs to be add and sequence of columns has to be adjusted

mysql_en-22258.jpg

Swapnil 2022-06-30 12:46:16
Have you tried the query on any database?
Sandip Londhe 2022-06-30 12:46:49
Yes
Sandip Londhe 2022-06-30 12:48:01
mysql_en-22261.jpg

Sandip Londhe 2022-06-30 12:49:30
mysql_en-22262.jpg

Swapnil 2022-06-30 12:50:22
Can you do select * from item_mast and send us the data
Sandip Londhe 2022-06-30 12:51:16
mysql_en-22264.jpg

Swapnil 2022-06-30 12:52:37
Sandip Londhe 2022-06-30 12:48:01

How mysql is allowing you to execute without mentioning i.pro_name in group by clause ?

Sandip Londhe 2022-06-30 12:54:25
if column names were duplicate then probably it will not allow
Swapnil 2022-06-30 12:54:52
No as per SQL standard ANSI , group by should contain all non aggregrate columns
Swapnil 2022-06-30 12:55:30
https://www.simplilearn.com/tutorials/sql-tutorial/group-by-in-sql#:~:text=The Group By statement is,the SELECT command in SQL.

How to Aggregate Data Using Group By in SQL [Updated]Simplilearn.com
What is group by in SQL? Learn ✅SQL Group By statement ✅Syntax for group by in SQL ✅Aggregate functions ✅Using group by on single & multiple columns, & more.
Sandip Londhe 2022-06-30 12:58:31
as of now many times i used the same logic , it works every single time ,
Swapnil 2022-06-30 12:59:04
Thats very weird and have not heard of this before
Swapnil 2022-06-30 12:59:15
Sandip Londhe 2022-06-30 12:58:31
as of now many times i used the same logic , it works every single time ,

https://onecompiler.com/mysql/3y8khada4 try same thing here

Swapnil 2022-06-30 12:59:28
select MySQL as database
Sandip Londhe 2022-06-30 12:59:52
only limitations to where clause to use aggregate func
Swapnil 2022-06-30 13:00:46
Which version of MySQL are you using ?
Swapnil 2022-06-30 13:01:32
First statement on google regarding group by
If you specify the GROUP BY clause, columns referenced must be all the columns in the SELECT clause that do not contain an aggregate function.
Sandip Londhe 2022-06-30 13:01:35
Server version: 8.0.28 MySQL Community Server – GPL
SS 2022-06-30 13:03:43
SS 2022-06-30 12:38:17
. Write a stored procedure that accepts the month and year as inputs and prints the ordernumber, orderdate and status of the orders placed in that month.

Example: call order_status(2005, 11);

CREATE DEFINER=`root`@`localhost` PROCEDURE `order_status`(year1 int,month1 varchar(3))
BEGIN
select orderNumber, orderDate, status from orders where year(orderdate)= year1

SS 2022-06-30 13:04:32
I have done till here can someone help me with the date part as how can i get 2 character date like ’01’
suricruise1 2022-06-30 13:05:23
Sandip Londhe 2022-06-30 11:45:53
select i.pro_name,c.com_name, max(i.pro_price) price from company_mast c inner join item_mast i on c.com_id = i.pro_com group by com_name;

It’s showing 9 records bro

suricruise1 2022-06-30 13:05:33
I checked now
suricruise1 2022-06-30 13:05:58
Sandip Londhe 2022-06-30 13:01:35
Server version: 8.0.28 MySQL Community Server – GPL

Same version

Swapnil 2022-06-30 13:06:14
SS 2022-06-30 13:04:32
I have done till here can someone help me with the date part as how can i get 2 character date like ’01’

month(orderdate) = 01

Swapnil 2022-06-30 13:06:55
Swapnil 2022-06-30 10:53:22
create table company_mast (com_id int, com_name text);

create table item_mast (pro_id int, pro_name text, pro_price int, pro_com int);

insert into company_mast values (11,’Samsung’);
insert into company_mast values (12,’iBall’);
insert into company_mast values (13,’Epison’);
insert into company_mast values (14,’Zebronics’);
insert into company_mast values (15,’Asus’);
insert into company_mast values (16,’Frontech’);

insert into item_mast values (101,’Motherboard’,3200,15);
insert into item_mast values (102,’Keyboard’,450,16);
insert into item_mast values (103,’ZIP’,250,14);
insert into item_mast values (104,’Speaker’,550,16);
insert into item_mast values (105,’Monitor’,5000,11);
insert into item_mast values (106,’DVD’,900,12);
insert into item_mast values (107,’CD Drive’,800,12);
insert into item_mast values (108,’Printer’,2600,13);
insert into item_mast values (109,’Refill’,350,13);

with max_price as
(
select pro_com, max(pro_price) as price
from item_mast
group by pro_com
order by pro_com
)
select b.pro_name,a.price,c.com_name
from max_price a
join item_mast b on a.pro_com = b.pro_com and a.price = b.pro_price
join company_mast c on b.pro_com = c.com_id
order by a.price desc

Output:
pro_name | price | com_name
————- ——- ———–
Monitor | 5000 | Samsung
Motherboard | 3200 | Asus
Printer | 2600 | Epison
DVD | 900 | iBall
Speaker | 550 | Frontech
ZIP | 250 | Zebronics

@suricruise1 Can you check this now?

suricruise1 2022-06-30 13:07:21
Swapnil 2022-06-30 10:53:22
create table company_mast (com_id int, com_name text);

create table item_mast (pro_id int, pro_name text, pro_price int, pro_com int);

insert into company_mast values (11,’Samsung’);
insert into company_mast values (12,’iBall’);
insert into company_mast values (13,’Epison’);
insert into company_mast values (14,’Zebronics’);
insert into company_mast values (15,’Asus’);
insert into company_mast values (16,’Frontech’);

insert into item_mast values (101,’Motherboard’,3200,15);
insert into item_mast values (102,’Keyboard’,450,16);
insert into item_mast values (103,’ZIP’,250,14);
insert into item_mast values (104,’Speaker’,550,16);
insert into item_mast values (105,’Monitor’,5000,11);
insert into item_mast values (106,’DVD’,900,12);
insert into item_mast values (107,’CD Drive’,800,12);
insert into item_mast values (108,’Printer’,2600,13);
insert into item_mast values (109,’Refill’,350,13);

with max_price as
(
select pro_com, max(pro_price) as price
from item_mast
group by pro_com
order by pro_com
)
select b.pro_name,a.price,c.com_name
from max_price a
join item_mast b on a.pro_com = b.pro_com and a.price = b.pro_price
join company_mast c on b.pro_com = c.com_id
order by a.price desc

Output:
pro_name | price | com_name
————- ——- ———–
Monitor | 5000 | Samsung
Motherboard | 3200 | Asus
Printer | 2600 | Epison
DVD | 900 | iBall
Speaker | 550 | Frontech
ZIP | 250 | Zebronics

This okay bro

suricruise1 2022-06-30 13:08:23
suricruise1 2022-06-30 13:05:23
It’s showing 9 records bro

This one also correct.

suricruise1 2022-06-30 13:08:58
In group by clause mention one column name it working
|