Download App

Structured Query Language (SQL) – II — Computer Science STD 12 Science — Question

CBSE BoardEnglish MediumSTD 12 ScienceComputer ScienceStructured Query Language (SQL) – II5 Marks
Question

Answer the following questions.

Vehicle

Challan

Offence

(a) Based on these tables write SQL statements for the following queries:

(i) Display the dates of first registration and last registration from the table Vehicle.

(ii) Display the number of challans issued on each date.

(iii) Display the total number of challans issued for each offence.

(iv) Display the total number of vehicles for which the 3rd and 4th characters of RegNo are '6C'.

(v) Display the total value of challans issued for which the Off_Desc is 'Driving without License'.

(vi) Display details of the challans issued on '2010-04-03' along with Off_Desc for each challan.

(vii) Display the RegNo of all vehicles which have been challaned more than once.

(viii) Display details of each challan alongwith vehicle details, Off_desc, and Challan_Amt.

(b) Identify the Foreign Keys (if any) of these tables. Justify your choices.

(c) Should any of these tables have some more column(s)? Think, discuss in peer groups, and discuss with your teacher.

✓

Answer

(a) (i)

(ii)

(iii)

(iv)

(v)

(vi)

(vii)

(viii)

(b) RegNo is the foreign key for the tables as it can be used to link each other.

(c) No, there is no need of more column(s) in any of the tables.

Need a full question paper?

Generate a complete, print-ready paper with questions like this in minutes — across 16+ boards, with answer keys.

Start Generating Free

Explore more

More "5 Marks Each" from Structured Query Language (SQL) – II→Structured Query Language (SQL) – II — all question types→Computer Science STD 12 Science question papers→CBSE Board STD 12 Science question papers→CBSE Board question paper generator→

Similar questions

On the basis of the given data set, answer the questions below:

ExpenditureData={'Country': ['Brazil', 'India', 'United Kingdom', 'Nigeria', 'China','Pakistan'],

'Expenditure': [785300000, 530780000, 694582000, 130581000, 407191800, 4754000],

'GrowthRate': [15, 18.6, 13.79,14.87,20.86, 12.54]

'UpdateDate': ['2016-03', '2016-08', '2016-02', '2016-01','2016-09','2016-05']}

Country Expenditure GrowthRate UpdateDate
0 Brazil 785300000 15.00 2016-03
1 India 530780000 18.60 2016-08
2 United Kingdom 694582000 13.79 2016-02
3 Nigeria 130581000 14.87 2016-01
4 China 407191800 20.86 2016-09
5 Pakistan 4754000 12.54 2016-05

(i) Write the command to create the data frame from the given lists:

(ii) To display the complete data frame.

(iii) To display the column country.

(iv) To display the following columns 'Country', 'Expenditure', 'UpdateDate'.

(v) To display the first and second record.

(vi) To change the index to the column 'country'.

(vii) To display the records of 'India','United Kingdom'

(viii) To display the records of the countries whose growth rate is greater than 18.

(ix) To display the Expenditure and update data whose growth rate is greater than 18.

(x) To display the Expenditure and update data whose expenditure is 694582000.

(xi) To add the new column GDP_Growth_rate.

(xii) Write the output:

(a) data1.iloc[2:5]

(b) data1.iloc[2:5,1:2]

→
Answer the (a) and (b) on the basis of the following tables STORE and ITEM: [Delhi 2014]

Table: STORE

SNo SName AREA
S01 ABC Computronics GK II
S02 All Infotech Media CP
S03 Tech Shoppe Nehru Place
S05 Hitech Tech Store CP

Table: ITEM

INo IName Price SNo
T01 Mother Board 12000 S01
T02 Hard Disk 5000 S01
T03 Keyboard 500 S02
T04 Mouse 300 S01
T05 Mother Board 13000 S02
T06 Key Board 400 S03
T07 LCD 6000 S04
T08 LCD 5500 S05
T09 Mouse 350 S05
T10 Hard disk 4500 S03

(a) Write the SQL queries (1 to 4):

(i) To display IName and Price of all the items in the ascending order of their Price.

(ii) To display the SNo and SName or all stores located in CP.

(iii) To display the minimum and maximum price of each IName from the table Item.

(iv) To display the IName, price of all items and their respective SName where they are available.

(b) Write the output of the following SQL commands (i) to (iv):

→
Write the few points for the healthy use of technology.
→
Write SQL queries for (i) to (iv) and find outputs for SQL queries (v) to (vii), which are based on the tables. [CBSE Sample Paper 2018]

Table: Trainer

TID Tname City HireDate Salary
101 Sunaina Mumbai 1998-10-15 90000
102 Anamika Delhi 1994-12-24 80000
103 Deepti Chandigarh 2001-12-21 82000
104 Meenakshi Delhi 2002-12-25 78000
105 Richa Mumbai 1996-01-12 95000
106 Maniprabha Chennai 2001-12-12 69000

Table: Course

CID CNAME FEES STARTDATE TID
C201 AGDCA 12000 2018-07-02 101
C202 ADCA 15000 2018-07-15 103
C203 DCA 10000 2018-10-01 102
C203 DDTP 9000 2018-09-15 104
C205 DHN 20000 2018-08-01 101
C206 O LEVEL 18000 2018-07-25 105

(a) Display the Trainer Name, City and Salary in descending order of their hire date.

(b) To display the TNAME and CITY of Trainer of joined the institute in the month of December 2001.

(c) To display TNAME, HIREDATE,CNAME, STARTDATE from tables TRAINER and COURSE whose FEES is less than or equal to 10000

(d) To display number of trainer from each city (e) select tid, tname,from trainer where city not in(‘delhi’,’mumbai’);

(f) select distinct tid from course;

(g) select tid , count (*), min(fees) from course group by tid having count(*)>1;

(h) Select count(*),sum(fees) from course where startdate <’2018-09-15’;

→
The following is the code related to the data structure QUEUE

Now answer the following based on the code given:

(a) What is the purpose of EnQueue function

(# Statement-1) defined above:

(i) It is a user defined function to scan / access QUEUE elements

(ii) It is a pre defined function to scan / access QUEUE elements

(iii) It is a user defined function working on double ended queue

(iv) It is a user defined function working on circular queue

(b) What is the line QUEUE.append(VALUE)

(# Statement-2) doing

(i) This will add the element at the end of the QUEUE

(ii) This will remove the element from the end of the QUEUE

(iii) This will add the element at the beginning of the QUEUE

(iv) This will remove the element from the beginning of the QUEUE

(c) Conceptually if the QUEUE is already full , what will happen

(i) PEEK() function will be executed

(ii) OVERFLOW occurs

(iii) UNDERFLOW occurs

(iv) We may still add more elements in it.

(d) What is the use of the variable VALUE used in argument (# Statement-1)

(i) This is the position of FRONT

(ii) This is the position of REAR

(iii) This is an element added in a QUEUE

(iv) This is the name of the list

(e) When the else part (# Statement-3) of the above code will be executed

(i) When the length of the QUEUE is zero

(ii) When the length of the QUEUE is one

(iii) When the length of the QUEUE is more than one

(iv) This part of the code will always executed

→
Uplifting Skills Hub India is a knowledge and skill community which has an aim to uplift the standard of knowledge and skills in the society. It is planning to setup its training centers in multiple towns and villages pan India with its head offices in the nearest cities. They have created a model of their network with a city, a town and 3 villages as follows.

As a network consultant, you have to suggest the best network related solutions for their issues/problems raised in (a) to (d) keeping in mind the distances between various locations and other given parameters.

Shortest distances between various locations:

VILLAGE 1 to B_TOWN 2 KM
VILLAGE 2 to B_TOWN 1.0 KM
VILLAGE 3 to B_TOWN 1.5 KM
VILLAGE 1 to VILLAGE 2 3.5 KM
VILLAGE 1 to VILLAGE 3 4.5 KM
VILLAGE 2 to VILLAGE 3 2.5 KM
A_CITY Head Office to B_HUB 25 KM

Number of Computers installed at various locations are as follows:

B_TOWN 120
VILLAGE 1 15
VILLAGE 2 10
VILLAGE 3 15
A_CITY OFFICE 6

Note:

• In Villages, there are community centers, in which one room has been given as training center to this organization to install computers.
• The organization has got financial support from the government and top IT companies.

(a) Suggest the most appropriate location of the SERVER in the B_HUB (out of the 4 locations), to get the best and effective connectivity. Justify your answer.

(b) Suggest the best wired medium and draw the cable layout (location to location) to efficiently connect various locations within the B_HUB.

(c) Which hardware device will you suggest to connect all the computers within each location of B_HUB?

(d) Which service/protocol will be most helpful to conduct live interactions of Experts from Head Office and people at all locations of B_HUB?

→
ABC School offered Stream to their students on the basis of following criteria. Programmer wrote the following code.

Maths Science Average Stream_Offered
>=80 >=90 >=90 Medical
>=75 >=85 >=85 Non-Medical
>=75 >=60 >=75 Commerce with Maths
>=60 >=60 >=60 Commerce without Maths
>=50 >=45 >=50 Humanities Stream
Otherwise Vocational Stream

Choose the correct possible option on the basis of following code.

(a) Which Statement will be executed, If a Student scored marks as Maths = 75, Science = 80 and Average = 89.

(i) Statement 1 (ii) Statement 2

(iii) Statement 3 (iv) Statement 4

(b) When a Student is Eligible for Humanities Stream?

(i) Student scored marks as Maths less than 65, Science = 80 and Average = 89.

(ii) Student scored marks as Maths = 75, Science = 80 and Average = 89.

(iii) If a Student scored marks as Maths = 55, Science = 47 and Average = 55.

(iv) If a Student scored marks as Maths = 75, Science = 80 and Average = 89.

(c) Which Statement will be executed, If a Student scored marks as Average = 65, Science = 78 and Maths = 80.

(i) Statement 3 (ii) Statement 4

(iii) Statement 5 (iv) Statement 6

(d) Which Statement will be executed, if a Student Scored marks in Science = 35, Maths = 37 and Average = 41.

(i) Statement 3 (ii) Statement 4

(iii) Statement 5 (iv) Statement 6

(e) Which Statement will be executed, If a Student Scored Science =66 , Maths =64 and Average = 65.

(i) Statement 4 (ii) Statement 5

(iii) Statement 3 (iv) Statement 6

→
Answer the question below based on given dataset:

DataFrame: dfzoo

animal uniq_id water_need
0 Elephant 1001 500
1 Elephant 1002 600
2 Elephant 1003 550
3 Tiger 1004 300
4 Tiger 1005 320
5 Tiger 1006 330
6 Tiger 1007 290
7 Tiger 1008 310
8 Zebra 1009 200
9 Zebra 1010 220
10 Zebra 1011 240
11 Zebra 1012 230
12 Zebra 1013 220
13 Zebra 1013 100
14 Zebra 1014 80
15 Lion 1015 420
16 Lion 1016 600
17 Lion 1017 500
18 Lion 1018 390
19 Kangaroo 1019 410
20 Kangaroo 1020 430
21 Kangaroo 1021 410

(i) Counting all the animals

(ii) Count the number of animals in zoo

(iii) To print the sum of the water need of an animals.

(iv) To print the sum of all values.

(v) To print the sum of only numeric values.

(vi) To print the minimum values of water need.

(vii) To print the mean values of water need.

(viii) To print the median values of water need.

(ix) To print the animal wise means value.

(x) To print the mean value water need of an each animal.

→
Intelligent Hub India is a knowledge community aimed to uplift the standard of skills and knowledge in the society. It is planning to setup its training centers in multiple towns and villages pan India with its head offices in the nearest cities. They have created a model of their network with a city, a town and 3 villages as follows.

As a network consultant, you have to suggest the best network related solutions for their issues/problems raised in (a) to (d), keeping in mind the distances between various locations and other given parameters.

Shortest distance between various locations:

VILLAGE 1 to YTOWN 2 KM
VILLAGE 2 to YTOWN 1.5 KM
VILLAGE 3 to YTOWN 3 KM
VILLAGE 1 to VILLAGE 2 3.5 KM
VILLAGE 1 to VILLAGE 3 4.5 KM
VILLAGE 2 to VILLAGE 3 3.5 KM
CITY Head Office to YHUB 30 KM

Number of computers installed at various locations are as follows:

YTOWN 100
VILLAGE 1 10
VILLAGE 2 15
VILLAGE 3 15
CITY OFFICE 5

Note:

• In Villages, there are community centers, in which one room has been given as training center to this organization to install computers.

• The organization has got financial support from the government and top IT companies.

(a) Suggest the most appropriate location of the SERVER in the YHUB (out of the 4 locations), to get the best and effective connectivity. Justify your answer.

(b) Suggest the best wired medium and draw the cable layout (location to location) to efficiently connect various locations within the YHUB.

(c) Which hardware device will you suggest to connect all the computers within each location of YHUB?

(d) Which service/protocol will be most helpful to conduct live interactions of Experts from Head Office and people at YHUB locations?

→

Write SQL commands for the following queries on the basis of Club relation given below:

Relation: Club

Coach-ID CoachName Age Sports date_of_app Pay Sex
1 Kukreja 35 Karate 27/03/1996 1000 M
2 Ravina 34 Karate 20/01/1998 1200 F
3 Karan 34 Squash 19/02/1998 2000 M
4 Tarun 33 Basketball 01/01/1998 1500 M
5 Zubin 36 Swimming 12/01/1998 750 M
6 Ketaki 36 Swimming 24/02/1998 800 F
7 Ankita 39 Squash 20/02/1998 2200 F
8 Zareen 37 Karate 22/02/1998 1100 F
9 Kush 41 Swimming 13/01/1998 900 M
10 Shailya 37 Basketball 19/02/1998 1700 F

(a) To show all information about the swimming coaches in the club.

(b) To list the names of all coaches with their date of appointment (date_of_app) in descending order.

(c) To display a report showing coach name, pay, age, and bonus (15% of pay) for all coaches.

(d) To insert a new row in the Club table with ANY relevant data:

(e) Give the output of the following SQL statements:

(i) Select COUNT(Distinct Sports) from Club;

(ii) Select Min(Age) from Club where SEX = “F”;

→