Scribd Logo
Upload

Welcome to Scribd!

  • 426 views

    Vel'S Public School, Sankarankovil Informatics Practices (065) Worksheet-2 Unit-2: Database Query Using SQL

    Uploaded by

    rizwana fathima
    The document contains questions and SQL queries related to various database tables including SchoolBus, Club, Student, Graduate, Teacher, Product, and Flight. It asks to write SQL queries to retrieve specific fields, perform aggregate functions, filter records, sort results, and modify the data. It also provides sample output for some given SQL statements on the tables.

    Copyright:

    © All Rights Reserved
    Download as PDF, TXT or read online from Scribd

    Vel'S Public School, Sankarankovil Informatics Practices (065) Worksheet-2 Unit-2: Database Query Using SQL

    Uploaded by

    rizwana fathima
    426 views6 pages
    The document contains questions and SQL queries related to various database tables including SchoolBus, Club, Student, Graduate, Teacher, Product, and Flight. It asks to write SQL queries to retrieve specific fields, perform aggregate functions, filter records, sort results, and modify the data. It also provides sample output for some given SQL statements on the tables.

    Original Description:

    CLASS 12 IP WORKSHEET SQL

    Original Title

    Worksheet 2 (SQL)

    Copyright

    © © All Rights Reserved

    Available Formats

    PDF, TXT or read online from Scribd

    Did you find this document useful?

    Is this content inappropriate?

    The document contains questions and SQL queries related to various database tables including SchoolBus, Club, Student, Graduate, Teacher, Product, and Flight. It asks to write SQL queries to retrieve specific fields, perform aggregate functions, filter records, sort results, and modify the data. It also provides sample output for some given SQL statements on the tables.

    Copyright:

    © All Rights Reserved
    Download as PDF, TXT or read online from Scribd
    Download as pdf or txt
    426 views6 pages

    Vel'S Public School, Sankarankovil Informatics Practices (065) Worksheet-2 Unit-2: Database Query Using SQL

    Uploaded by

    rizwana fathima
    The document contains questions and SQL queries related to various database tables including SchoolBus, Club, Student, Graduate, Teacher, Product, and Flight. It asks to write SQL queries to retrieve specific fields, perform aggregate functions, filter records, sort results, and modify the data. It also provides sample output for some given SQL statements on the tables.

    Copyright:

    © All Rights Reserved
    Download as PDF, TXT or read online from Scribd
    Download as pdf or txt
    You are on page 1of 6

    VEL’S PUBLIC SCHOOL,SANKARANKOVIL

    1. INFORMATICS PRACTICES (065)


    WORKSHEET-2
    UNIT-2 : DATABASE QUERY USING SQL

    Q1. Table : SchoolBus


    Rtno Area_covered Capacity Noofstudents Distance Transporter Charges
    1 Vasant kunj 100 120 10 Shivam travels 100000
    2 Hauz Khas 80 80 10 Anand travels 85000
    3 Pitampura 60 55 30 Anand travels 60000
    4 Rohini 100 90 35 Anand travels 100000
    5 Yamuna Vihar 50 60 20 Bhalla Co. 55000
    6 Krishna Nagar 70 80 30 Yadav Co. 80000
    7 Vasundhara 100 110 20 Yadav Co. 100000
    8 Paschim Vihar 40 40 20 Speed travels 55000
    9 Saket 120 120 10 Speed travels 100000
    10 Janak Puri 100 100 20 Kisan Tours 95000

    (a) To show all information of students where capacity is more than the no of student in order of
    rtno.
    (b) To show area_covered for buses more than 20 km., but charges less than 80000.
    (c) To show the area covered by Bhalla Co. and kisan tours
    (d) To show the names of all the transporters without repeating their names.
    (e) To show all the bus routes number where the distance is less than 30.
    (f) To show the area whose names end with „r‟ in decreasing order of the area covered.
    (g) To show rtno, area_covered and average cost per student for all routes where average cost per
    student is - charges/noofstudents
    (h)To show the number of areas covered by all the bus routes
    (i) To show the number of area covered by each transporter.
    (j) To show the maximum capacity of a bus
    (k) To show transporter wise total number of students traveling.
    (l) Add new record with data: (11, “ Moti bagh”,35,32,10,” kisan tours “, 35000)
    (m) Give the output considering the original relation as given :
    (n)select sum(distance) from schoolbus where transporter= “ Yadav Co.”;
    (o) select min (noofstudents) from schoolbus;
    (p)select avg(charges) from schoolbus where transporter= “Anand travels”;
    (q) select distinct transporter from schoolbus ;
    (r) select count(distinct transporter) from schoolbus ;
    (s) select count(*) from schoolbus ;
    (t) select transporter, count(*) from schoolbus group by transporter ;
    (u)select transporter, max(charges) from schoolbus group by transporter ;

    Table: CLUB
    C_Id CoachName Age Sports DateofApp Pay Sex No_Players
    1 KUKREJA 35 KARATE 2012-03-27 1000 M 20
    2 RAVINA 34 KARATE 2008-01-20 1200 F 15
    3 KARAN 34 SQUASH 2009-02-19 2000 M 2
    4 TARUN 33 BASKETBALL 2012-01-01 1500 M 16
    5 ZUBIN 36 SWIMMING 2008-01-12 700 M 10
    6 KETAKI 36 SWIMMING 2012-02-24 800 F 10
    7 ANKITA 39 SQUASH 2013-02-20 2200 F 5
    8 ZAREEN 37 KARATE 2010-02-22 1100 M 18
    9 KUSH 41 SWIMMING 2008-01-13 900 M 17
    10 SHAILYA 37 BASKETBALL 2008-02-19 1700 M 17
    2. Q2. Create the above table AND DO THE FOLLOWING
    (a) To show all information about the swimming coaches in the club.
    (b) To show all information of the CLUB which coach name start with “K”.
    (c) To show the names of coaches in sorted order of the name.
    (d) To list the names of all coaches with their date of appointment (DateofApp) in descending order.
    (e) To display coach name, age and sex from CLUB which sex is Male (M).
    (f) To show all information which age more than 35 and Pay between 1500 and 2000.
    (g) To display a report, showing coach name, pay, age and bonus (15% of Pay) for all the coaches.
    (h) To show distinct names of the games available in club.
    (i) To count the total number of coaches in the club
    (j) To count and display total coaches in each game
    (k) To display Sports wise sum of pay from club table.
    (l) To display highest age of coach, lowest pay of coach form table club.
    (m)Give the output of the following SQL command.
    (i) Select COUNT( Distinct SPORTS) from CLUB;
    (ii)Select MIN(Age) From CLUB Where Sex = “F”;
    (iii) Select AVG(Pay) From CLUB Where Sports = “KARATE”;
    (iv) Select SUM(Pay) From CLUB Where DateofApp > “2008-01-31”;
    (v) Select Sports From CLUB where Pay Between 700 and 1000 ;
    (vi) Select Sports From CLUB where Pay IN ( 700, 1000,2000) ;
    (vii) SELECT Sport, SUM(pay) FROM CLUB GROUP BY Sport Having Sex=‟M‟ ;
    (o) To delete record of coaches whose salary is less than 1000
    (p) To delete record of coaches whose name ends with „N‟ and has five characters
    (q) To increase the salary of all coaches by 20%
    Q3. Consider the table student given below. Find out the output of
    the following queries: Rno Fees
    A) SELECT COUNT(*) FROM STUDENT; 1 4000
    B) SELECT COUNT(FEES) FROM STUDENT; 2 4300
    C) SELECT AVG(FEES) FROM STUDENT; 3 5000
    D) SELECT FEES + 100 FROM STUDENT ; 4 Null
    Q.4

    a. Give the output of following queries based on schoolbus table:


    (i) select sum(distance) from schoolbus where transporter= “Yadavtravels”;
    (ii) select min(noofstudents) from schoolbus;
    (iii) select avg(charges) from schoolbus where transporter= “Anand travels”;
    (iv) select distinct transporter from schoolbus;
    3. b. Write sql query for following
    (i) Display area_covered in uppercase along with distance
    (ii) Display area_covered with largest length(number of characters)
    (iii) Display area_covered with largest distance
    (iv) Display the list of schoolbus where rightmost part of transporteris travels
    (v)Display the total distance covered by ‘Anand travels’
    (vi) Display the transporter which charges maximum with regards to all area covered taken together.

    Q.5

    a. Give the output of following sql statement based on tableGRADUATE:


    (i) Select MIN(AVERAGE) from GRADUATE whereSUBJECT=
    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;

    b. Write sql query for following


    (i) Display the name of the students in lower case
    (ii) Display the average stipend of COMP.SC. subject
    (iii) Display the name , stipend of student where subject is eitherphysics or maths
    (iv) Display the subject with maximum stipend
    (v)Display the subject with maximum stipend(sum of stipend of allstudent for each subject)

    Q.6

    a. Give the output of the following SQL statements.


    i. Select COUNT(distinct department) from TEACHER;
    ii. Select MAX(Age) from TEACHER where SEX=‘F‘;
    iii. Select AVG(Salary) from TEACHER where SEX=‘M‘;
    iv. Select SUM(Salary) from TEACHER whereDATOFJOIN<{12/07/96};
    b. Write SQL query for following
    4. i. Display the teacher name with maximum age
    ii. Display the teacher name with minimum salary
    iii. Display the department where maximum salary is being given
    iv. Display the name of the teacher where year of dateofadmis 98
    v. Display the name of the teacher whose day of dateofadm isMonday
    vi. Display how many males and females are there in teacher table

    Q.7

    a. Give the output of following statement.


    (i) Select COUNT(distinct company) from PRODUCT.
    (ii) Select MAX(price)from PRODUCT where WARRANTY<=3
    (iii) select avg(price) from PRODUCT
    (iv) SELECT MAX(STOCK),PNAME FROM PRODUCT

    b. Write SQL query for following


    i. Display the product with oldest manufacturing date
    ii. Display the product with maximum warranty
    iii. Display the recent product
    iv. Display the costliest TV company
    v. Display manufacturing year of all products
    vi. Display the product manufactured in MAR month
    vii. display the product name with maximum length.

    Q8.

    Write SQL queries w/r to FLIGHT table data

    i. Display flight codes, starting place, destination, number of flights in descending order of number
    of flights.
    ii. Count and display number of flights starting from each city(START).
    iii. Count and display number of flights starting from each city(START) where number of stops more
    than 2.
    iv. Display flight data in ascending order of no of stops
    5. v. Count and display number of flights reaching at each destination.
    vi. Count and display number of flights without stops

    Q9.
    Write SQL queries w/r to store table data

    i. To display name, location, city, SalesAmount of stores in descending order of SalesAmount.


    ii. To display total SalesAmount of each city along with city name.
    iii. To display total number of employees of each city along with city name.
    iv. To display total number of employees of each city along with city name where total employee of a
    city >15.
    v. To display total sales amount of each city along with city name.

    Q10.

    Write SQL queries w/r to Gym table data


    i.To display Mcode, Mname, Age of all female members of the Gym with age in descending order.
    ii. To count the number of members of the Gym of each type.
    iii. To display the type alongwith maximum and minimum fees of each type.
    iv. To display types of memberships available. Duplicate values should not bedisplayed.
    v. To display total fee given by each gender in Gym.
    vi. To display the average age of each gender in Gym
    vii. To display sum of fee given in each membership type
    6.
    Q11.

    Write SQL queries w/r to Salesperson table data


    i. To display Names and Salaries of Salespersons in descending order of salary.
    ii. To display areas in which Salespersons are working. Duplicate Areas should not be
    displayed.
    iii. To display Area along with number of Salespersons working in that area.
    iv. To display total salary paid in each area
    v. To display name of salesperson from the area where no of salesperson more than 1

    You might also like