Page 295 - PYTHON-12

22. Write SQL commands for (i) to (vii) on the basis of table SPORTS:

Table: SPORTS
StudentNO Class Name Game1 Grade1 Game2 Grade2
10 7 Sammer Cricket B Swimming A
11 8 Sujit Tennis A Skating C
12 7 Kamal Swimming B Football B
13 7 Venna 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 ‘C’ in either Game1 or Game2 or both.
(b) Display the number of students getting grade ‘A’ in Cricket.
(c) Display the names of the students who have the same game for both Game1 and Game2.
(d) Display the game taken up by the students, whose name starts with ‘A’.
(e) Add a new column named ‘Marks’.
(f) Assign a value 200 for marks for all those who are getting grade ‘B’ or grade ‘A’ in both Game1 and
Game2.
Ans. (a) SELECT Name for SPORTS where grade=‘C’ or Grade2=‘C’;
(b) SELECT Count(*) from SPORTS where grade=‘A’;
(c) SELECT name from SPORTS where game1 = game2;
(d) SELECT game,game2 from SPORTS where name like ‘A%’;
(e) ALTER TABLE SPORTS add (marks int(4));
(f) UPDATE SPORTS set marks=200 where grade=‘A’;
23. (a) Study the following table and write SQL queries for questions (i) to (iv) and output for (v) and (vi).
Table: Orders
Orderid Pname Quantity Rate Sale_date Discount
1001 Pen 10 20 2019-10-05
1002 Pencil 20 10 2019-10-21
1003 Book 10 100 2019-11-02 50
1004 Eraser 100 5 2019-12-05 25
1005 Copy 50 20 2019-12-10
(i) Write SQL query to display Pname, Quantity and Rate for all the orders that are either Pencil or
Pen.
(ii) Write SQL query to display the orders which are not getting any Discount.
(iii) Write SQL query to display the Pname, Quantity and Sale_date for all the orders whose total cost
(Quantity * Rate) is greater than 500.
(iv) Write SQL query to display the orders whose Rate is in the range 20 to 100.
(v) SELECT Pname, Quantity from Orders WHERE Pname LIKE(‘_e%’);
(vi) SELECT Pname, Quantity, Rate FROM Orders Order BY Quantity DESC;
Ans. (i) SELECT Pname, Quantity, Rate FROM Orders WHERE Pname IN(‘Pencil’,’Pen’);
(ii) SELECT * FROM Orders WHERE Discount is NULL;
(iii) SELECT Pname, Quantity, Sale_date FROM Orders WHERE Quantity * Cost > 500;
(iv) SELECT * FROM Orders WHERE Rate BETWEEN 20 AND 100;
(v)
Pname Quantity Relational Database and SQL
Pen 10
Pencil 20

12.65