MS SQL SERVER SOLUTION (Chapter 4 ) : Group By

 

For any type of query or doubt regarding any solution or if you are not able to understand the solution or if you have any other alternative solution that you want to show me, You can directly comment on the corresponding class video in youtube. So that everyone can participate in that discussion. But remember I will use the only those techniques that we have learned so far. Exercise has been built in that way. It does not involve any knowledge on  unknown topics. When I was a student I missed this sequential way of exercise. I tried to build the exercise on what you have learnt recently. So that you can focus on recently learnt topic only and a bit revise of earlier chapters. As an example, this is the solution of chapter 4 and I have used only the techniques learned till chapter 4. So if you are not able to crack any logic, that means you have to spend more time on "Group by" and earlier topics. 

So keep that in mind while commenting your doubts in the corresponding youtube class as well. suppose you are commenting in youtube class 4, then I will show the solutions using techniques learnt from class 1-4 only. Let's create a good automated way to learn this topic and create an intelligent self learning platform by which you can get maximum knowledge without paying anything. For the effective learning you can copy the 1st solution code to create the dataset quickly. solution 2 onwards , you can not copy anything. you have to write it by your own 😊. Happy learning.

 I am a teacher by passion not by profession. So I also want you to become passionate on databases.

For further help, 

debabrataguha20@gmail.com 

https://www.facebook.com/ddebb.guha/ 

https://www.linkedin.com/in/debabrata-guha-719008119/

 

 

 ------------------------------------ regarding chapter 4 ----------------------------------------

Group by Class: https://youtu.be/MXF8AuvxfOQ

You will get all the class video in youtube: https://www.youtube.com/playlist?list=PLtyCwTfL5QAzWl_mfVTjxHqGbwiyoUTnh

You will get all the exercise in the Facebook group  https://www.facebook.com/groups/331231161093613/ 

__________________________________________________________________________

 

1.    Q1.    Create a table with DDL Schema like below:

Student (Roll, SNAME, SUBJECTS, MARKS, GENDER, STATE) 

Assume each student is having only 3 subjects Phys, Chem, Math.

 

Soln. 

-- you can copy and paste the following query to create the dataset quickly

create table student
(
    roll        int,
    sname        varchar(100),
    subjects    varchar(10),
    marks        numeric(5,2),
    gender        varchar(1),
    state        varchar(2)
)

insert into student values
(1,'Ankan','phys',30.5,'M','WB'),
(1,'Ankan','chem',75.5,'M','WB'),
(1,'Ankan','math',87.0,'M','WB'),

(2,'Shreya','phys',82.5,'F','WB'),
(2,'Shreya','chem',77.5,'F','WB'),
(2,'Shreya','math',91.0,'F','WB'),

(3,'Radhika','phys',60.0,'F','WB'),
(3,'Radhika','chem',85.5,'F','WB'),
(3,'Radhika','math',89.0,'F','WB'),

(4,'Uday','phys',90.0,'M','WB'),
(4,'Uday','chem',71.5,'M','WB'),
(4,'Uday','math',92.0,'M','WB'),

(5,'Vijay','phys',90.0,'M','DL'),
(5,'Vijay','chem',91.5,'M','DL'),
(5,'Vijay','math',38.5,'M','DL'),

(6,'Sujay','phys',70.0,'M','DL'),
(6,'Sujay','chem',70.5,'M','DL'),
(6,'Sujay','math',78.5,'M','DL'),

(7,'Suman','phys',20.0,'M','DL'),
(7,'Suman','chem',82.0,'M','DL'),
(7,'Suman','math',50.5,'M','DL'),

(8,'Nidhi','phys',61.0,'F','DL'),
(8,'Nidhi','chem',63.0,'F','DL'),
(8,'Nidhi','math',69.0,'F','DL'),

(9,'Zoya','phys',21.0,'F','DL'),
(9,'Zoya','chem',71.0,'F','DL'),
(9,'Zoya','math',72.5,'F','DL'),

(10,'Olga','phys',11.0,'F','CA'),
(10,'Olga','chem',81.0,'F','CA'),
(10,'Olga','math',82.5,'F','CA'),

(11,'Georgy','phys',18.0,'F','CA'),
(11,'Georgy','chem',25.5,'F','CA'),
(11,'Georgy','math',85.0,'F','CA'),

(12,'Smith','phys',81.0,'M','CA'),
(12,'Smith','chem',82.5,'M','CA'),
(12,'Smith','math',77.0,'M','CA'),

(13,'Alan','phys',21.0,'M','WB'),
(13,'Alan','chem',22.5,'M','WB'),
(13,'Alan','math',37.0,'M','WB'),


(14,'George','phys',82.0,'M','LN'),
(14,'George','chem',62.5,'M','LN'),
(14,'George','math',55.0,'M','LN'),

(15,'Adams','phys',91.0,'M','LN'),
(15,'Adams','chem',92.0,'M','LN'),
(15,'Adams','math',87.0,'M','LN')