CBSE Guess > Papers > Important Questions > Class XII > 2012 > Computer Science > Computer Science Vinay Kumar Srivastava

Computer Science- CBSE CLASS XII

c) Consider the table given below, write command in SQL for (1) to (4) and output for (5) to (8).

     For - 1 Marks

Table : STUDENT

( 1 ) To display the name and stream of all students who are in class 12.
( 2 ) To display the different Streams available for students.
( 3 ) To display name, avgmarks and grade in ascending order of grade.
( 4 ) To display names of those students whose grade and section are same.
( 5 ) SELECT Stipend+500 FROM Student  WHERE stream LIKE  ‘%ma%’;
( 6 ) SELECT COUNT(*) FROM Student WHERE grade=’C’ OR stipend =800;
( 7 ) SELECT ClassSec FROM Student WHERE  Avgmark>68 && stream=’Medical’;
( 8 ) SELECT AVG(stipend) FROM Student WHERE name=’Karan’;

Ans: (1) SELECT name,stream
              FROM student
              WHERE classSec LIKE ‘12%’;
                               Or
             SELECT name,stream
             FROM student
             WHERE LEFT(classSec,2)=‘12’;

       (2)  SELECT DISTINCT stream
              FROM student;

       (3)  SELECT name, AvgMark, Grade
              FROM student
              ORDER BY Grade ASC;

       (4) SELECT name
              FROM student
              WHERE RIGHT(classSec,1)=Grade;

       (5)  2500

       (6)  2

       (7)  11C
              12J

      (8)  1000          

Q . 6     Answer the following question.      For - 2 Marks

a) Write an SQL query to create the  table  “Club“ with the following structure-����      For - 2 Marks

Ans:
CREATE TABLE club (CoachId INT(6) PRIMARY KEY, CoachName Varchar(25)  NOT NULL, Age INT(3) CHECK Age>35, Sports Varchar(20), Pay INT(8), Sex  Char(1));

b) In a database there are two tables ‘Doctors’ and  ‘Patients’ shown below-����          For - 2 Marks

Table: Doctors

Table: Patients���

i) Name the column(s) that can be used to retrieve data from both the tables.
�ii) Identify the Primary key and Foreign Key attributes from both the tables�

Ans : i) DId ( from Doctors and Patients table)
ii) PId – Primary Key ( Patient Table)
iii)DId-  Foreign Key (Patient Table)
iv)DId-  Primary Key ( Doctors Table)

c) Consider the tables given below-              For - 6 Marks

Table : Staff

Table : Salary

With reference to above tables, write commands in SQL for (i) and (ii) and output for (iii)-