2002:
5. Given the following Teacher Relation. Write SQL Commands fro (b) to (g)
| No |
Name | Department | DateofJoining | Salary | Sex |
|---|---|---|---|---|---|
| 1 | Raja | Computer | 21/5/98 | 8000 | M |
| 2 | Sangita | History | 21/5/97 | 9000 | F |
| 3 | Ritu | Sociology | 29/8/98 | 8000 | F |
| 4 |
Kumar | Linguistics | 13/6/96 | 10000 | M |
| 5 |
Venka traman | History | 31/10/99 | 8000 | M |
| 6 | Sindhu | Computer | 21/5/86 | 14000 | M |
| 7 |
Aishwarya | Sociology | 11/1/1998 | 12000 | F |
(b) To select all the information of teacher in computer department.
Ans: Select * from Teacher where Department=”Computer”
(c ) To list the name of female teachers in History Department.
Ans: Select Name from Teacher Where Sex=”F” And Department=”History”.
(d) To list all names of teachers with date of admission in ascending order.
Ans: Select Name from Teacher Order By Dateofjoining Asc
(e) To display Teacher's Name, Department, and Salary of female teachers
Ans: Select Name,Department,Salary from Teacher Where Sex=”F”
(f)To count the number of items whose salary is less than 10000
Ans: Select Count(*) from Teacher Where Salary<10000.
(g) To insert a new record in the Teacher table with the following data: 8,”Mersha”,”Computer”,
(1/1/2000),12000,”M”.Ans: Insert into Teacher values ,”Mersha”, ”Computer”,{1/1/2000),12000,”M”);
2001:
5.b) Write the SQL commands for (i) to (vii) on the basis of the table SPORTS
TABLE: SPORTS
| Studno |
Class | Name | Game1 | Grade1 | Game2 | Grade2 |
|---|---|---|---|---|---|---|
| 10 |
7 | Smeer | Criket | B | Swimming | A |
| 11 | 8 | Sujit | Tennis | A | Skating | C |
| 12 |
7 | Kamala | 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 |
(i) Display the names of the students who have grade ‘C’ in either Game1 or Game2 or both.
Ans: Select Name From Sports Where Grade1=”C” OR Grade2=”C”
(ii) Display the number of students getting grade ‘A’ in Cricket.
Ans: Select Count(*) from Sports Where (Game1=”Cricket” and Grade1=”A”) or (Game2=”Cricket” and Grade2=”A”)
(iii) Display the names of the students who have same game for both game1 and game2
Ans: Select Name From Sports Where Game1=Game2
(iv) Display the games taken up by the students, whose name starts with ‘A’.
Ans: Select Game1,Game2 From Sports Where Name Like “A%”
(v) Add a new column named ‘marks’.
Ans: Alter Table Sports Add Marks Number(5);
(vi) Assign a value 200 for marks for all those who are getting grade ‘B’ or ‘A’ in both Game1 and Game2.
Ans: (Children, Try This Answer as an assignment)
(vii) Arrange the whole table in the alphabetical order of name.
Ans: Select * from Sports Order By Name
CBSE Computer Science Solved Revision Tour By Mr. Ravi Kiran ( mrkdata@yahoo.com )
