Q. Write SQL commands for the following on the basis of given table SPORTS:



 
StudentNo Class Name Game1 Grade1 Game2 Grade2
10 7 Sameer Cricket B Swimming A
11 8 Sujit Tennis A Skating C
12 7 Kamal Swimming B Football B
13 7 Veena Tennis C Tennis A
14 9 Archana Basketball A Cricket A
15 10 Arpit Cricket A Athletics C



(a) Display the names of the students who have grade 'A' in either Game1 or Game2 or both.
(b) Display the number of students having game 'Cricket'.
(c) Display the names of students who have the same game for both Game1 and Game2.
(d) Display the games taken by the students whose name starts with 'A'.
(e) Give the output of the following sql statements:
(i) select count(*) from SPORTS;
(ii) select distinct Class from SPORTS;
(iii) select max(Class) from STUDENT;
(iv) select count(*) from SPORTS group by Game1;


Answer :-

(a) Select name from sportsWhere grade1 = "c" or grade2 = "c" ;
(b) Select count(*) from student where game = 'Cricket' or game2 = 'Cricket' ;
(c) Select name from sports Where game1 = game2 ;
(d) Select game1 , game2  from sports Where name like “A%” ;

(e)

(i)

 
count(*)
6


(ii)

 
distinct Class
7
8
9
10


(iii)

 
max(Class)
10


(iv)

 
count(*)
2
2
1
1



5 Comments

You can help us by Clicking on ads. ^_^
Please do not send spam comment : )

  1. it is wrong it should be like Where grades = 'c' or Grades2 = 'c'

    ReplyDelete
  2. In grade 1 is tennis while in grade 2 are skating and athletics....

    On game 1 and game 2 they have both swimming....


    In game 1 choose swimming and I'm game 2 i choose athletics

    ReplyDelete
    Replies
    1. Do you want query of "In game 1 choose swimming and I'm game 2 i choose athletics" then answer is

      Select name from sports
      Where game1 = "swimming"game2="athletics" ;

      Delete

Post a Comment

You can help us by Clicking on ads. ^_^
Please do not send spam comment : )

Previous Post Next Post