Page 433 - TP_IT_V1.0_C10_flipbpookl
P. 433

2.  Create a Database-SCHOOL with the given tables:                     21 st   #Productivity & Accountability
                                                                                          Century
                        STUDENT                                                             Skills  #Information Literacy
                        Admno varchar(5)   [primary key]
                        Name varchar(20)

                        Address varchar(30)
                        City varchar(10)

                        Phoneno number(10)

                        DOB date
                        MARKS

                        Rollno number(3)  [primary key]
                        Admno varchar(5)

                        Tcode varchar(5)
                        Test1 number(6,2)

                        Test2 number(6,2)

                        HALFYEARLY number(6,2)
                        TEACHER

                        Tcode varchar(5)  [primary key]
                        Tname varchar(20)

                        Subject varchar(10)
                        Class number(3)

                          After creating the above tables insert 10 records in each table. Give the command to insert a record in the
                        student table.

                        Write the SQL query for the above table:
                        a.  Display the structure of the table STUDENT, MARKS and TEACHER.

                        b.  Display all records from all three tables.
                        c.  Display only those students who live in ‘Delhi’.

                        d.  Display the details of the students whose DOB is in the year 2007.
                        e.  Display the total of Test1 and Test2 from MARKS table.

                        f.   Display the Admno, name and city in the descending order of the name from the STUDENT table.

                        g.  Display all details of Rollno from 10 to 30 from the MARKS table.
                        h.  Display only those teachers’ details from the TEACHER table where the class taught is either 9 or 10.

                        i.   Display only those teachers’ details from TEACHER table where subject taught is ‘Computer’.
                        j.   Display all teachers details in the ascending order of the class.



                                                                                                           Projects  431
   428   429   430   431   432   433   434   435   436   437   438