( SQL  6 Marks )

Q1.

Table : SchoolBus

(b)    To show all information of students where capacity is more than the no of student in order of rtno.

(c)     To show area_covered for buses covering more than 20 km., but charges less then 80000.

(d)    To show transporter wise total no. of students traveling.

(e)     To show rtno, area_covered and average cost per student for all routes where average cost per student is - charges/noofstudents.

(f)      Add a new record with following data:

            (11, “ Moti bagh”,35,32,10,” kisan tours “, 35000)   

(g)     Give the output considering the original relation as given:

       (i)  select sum(distance) from schoolbus where transporter= “ Yadav travels”;

       (ii) select min(noofstudents) from schoolbus;

       (iii) select avg(charges) from schoolbus where transporter= “ Anand  travels”;

       (iv)  select distinct transporter from schoolbus;

Q2.  

TABLE : GRADUATE

(a)         List  the names of those students who have obtained DIV 1 sorted by NAME.

(b)         Display  a report, listing NAME, STIPEND, SUBJECT  and amount of stipend received in a year assuming that the STIPEND  is paid every month.

(c)          To count the number of students who are either PHYSICS or COMPUTER SC graduates.

(d)         To insert a new row in the GRADUATE table:

 11,”KAJOL”, 300, “computer sc”, 75, 1

(e)     Give the output of following sql statement based on table GRADUATE:

(i)                  Select MIN(AVERAGE) from GRADUATE where   SUBJECT=”PHYSICS”;

(ii)                      Select SUM(STIPEND) from GRADUATE WHERE div=2;

(iii)               Select AVG(STIPEND) from GRADUATE where AVERAGE>=65;

(iv)                    Select COUNT(distinct SUBDJECT)  from GRADUATE;

(f)    Assume that there is one more table GUIDE in the database as shown below:


Table: GUIDE

 g) What will be the output of the following query:

SELECT NAME,  ADVISOR FROM  GRADUATE, GUIDE WHERE SUBJECT= MAINAREA ;

Q3. Write SQL command for (i)  to (vii) on the basis of the table SPORTS

Table: SPORTS

(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 same game for both Game1 and Game2.

(d)    Display the games taken up by the students, whose name starts with ‘A’.

(e)     Assign a value 200 for Marks for all those who are getting grade ‘B’ or  grade ‘A’ in both Game1 and Game2.

(f)     Arrange the whole table in the alphabetical order of Name.

(g)     Add a new column named ‘Marks’. 

Q4.

Employees

EmpSalary

Write the SQL commands for the following :

(i)                  To show firstname,lastname,address and city of all employees living in paris                            

(ii)                To display the content of Employees table in descending order of Firstname.                        

(iii)               To display the firstname,lastname and total salary of all managers from the tables Employee and empsalary , where total salary is calculated as salary+benefits.                                              

(iv)              To display the maximum salary among managers and clerks from the table Empsalary.

Give the Output of following SQL commands:                                                      

(i)                  Select firstname,salary from employees ,empsalary where designation = ‘Salesman’ and Employees.empid=Empsalary.empid;

(ii)                Select count(distinct designation) from empsalary;

(iii)               Select designation, sum(salary) from empsalary group by designation having count(*) >2;

(iv)              Select sum(benefits) from empsalary where designation =’Clerk’;

Theory file Handling , SQL , Networking

1. Difference between
1. text file and binary file.
2. ios::app and ios::out
3. ios::ate and ios::app
4. ifstream and ofstream
5. get and getline function
6. getc and getline function
7. read and write
   
   
   
2. Write two member functions belonging to fstream , ifstream and ofstream class.
   
   
   
3. Write two functions belonging to I/O error handling in files.
   
   
   
4. Explain tellg and tellp , seekg and seekp functions
   
   
   
5. Define in 20 words only : alternate key ,attribute , candidate keys, cardinality and  degree of a table ,foreign key ,data independence, primary key, relation , tuple, Views ,Normalization , Un-normalized form (UNF)1NF, 2NF,3NF and 4NF
   
   
   
6. What is DDL and DML
   
   
   
7. Define in 20 words only : transmission media , hackers , crackers ,Web browser, cookies, network security,firewall ,telnet, repeater ,bridge,router ,gateway,modem,cyber law,authorization and authentication, hub, , switches
   
   
   
8. Define the term topology . Defina star , bus , ring , tree , graph , mesh topology .
   
   
   
9. Define all the guided transmission media with their advantages and disadvantages.
   
   
   
10. Define all the un - guided transmission media with their advantages and disadvantages.
    
    
    
11. Expand the following terms :GSM , TCP/IP , TDMA , CDMA , WAN , LAN , MAN , SMS , E-MAIL , URL , WLL , SMS,XML,HTML,WWW