Write SQL queries for (a) to (d) and find outputs for SQL queries… - Vidyadip

Question

Write SQL queries for (a) to (d) and find outputs for SQL queries (e) to (h), which are based on the tables. [CBSE Sample Paper 2017]

TABLE: WATCHES

Watchid	Watch_name	Price	Type	Qty_store
W001	High time	10000	Unisex	100
W002	Life time	15000	Ladies	150
W003	Wave	20000	Gents	200
W004	High fashion	7000	Unisex	250
W005	Golden time	25000	Gents	100

TABLE: SALE

Watchid	Qty_Sold	Quarter
W001	10	1
W003	5	1
W002	20	2
W003	10	2
W001	15	3
W002	20	3
W005	10	3
W003	15	4

(a) To display all the details of those watches whose name ends with ‘time’

(b) To display watch’s name and price of those watches which have price range between 5000-15000

(c) To display total quantity in store of unisex type watches

(d) To display watch name and their quantity sold in first quarter

(e) Select max(price),min(qty_store) from watches;

(f) Select quarter, sum(qty_sold) from sale group by quarter;

(g) Select watch_name,price,type from watches w, sale s where w.watchid=s.watchid;

(h) Select watch_name, qty_store, sum(qty_sold),qty_store

✓

Answer

Get the step-by-step solution for this question inside the Vidyadip app.

Get the answer in the app

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 Database Query Using–SQL→Database Query Using–SQL — all question types→Computer Science STD 12 Science question papers→CBSE Board STD 12 Science question papers→CBSE Board question paper generator→

Similar questions

Consider the table LOANS given below:

Table: LOANS

AccNo	Name	Loan Amt	EMI	IntRate	Start Date	Interest
1001	R.K. Gupta	300000	36	12.00	19-07-2009	1200
1002	S. P. Shanna	500000	48	10.00	22-03-2008	1800
1003	K.P. Jain	300000	36	NULL	08-03-2007	1600
1004	M.P. Yadav	800000	60	10.00	06-12-2008	2250
1005	S.P. Sinlia	200000	36	12.50	03-01-2010	4500
1006	P. Shanna	700000	60	12.50	05-06-2008	3500
1007	K.S. Dhall	500000	48	NULL	05-03-2008	3800

(a) State the command that will give the output as:

(i) Select name from LOANS where Name >‘D ’;

(ii) Select name from LOANS where AccNo >1001;

(iii) Select name from LOANS where Loan_Amt >= 500000;

(iv) Select name from LOANS where Loan_Amt >500000 order Loan_AmtDESC;

(b) What will be the output of the following command:

Select name, EMI from LOANS where Loan_Amt >500000 and Int_Rate is NULL;

(c) John has given the following command to display the count of all loan holders whose name ends with “Sharma ”:

Select count() from LOANS where Name like ‘Sharma%’;

But he is not getting the desired result. Help him by finding out the correct command fromthe followings:

(i) Select count(*) from LOANS where name Like ‘Sharma%’;

(ii) Select count(*) from LOANS where name Like ‘%Sharma ’;

(iii) Select count( ) from LOANS where name like ‘%sharma ’;

(iv) Select count(Name) from LOANS where name ‘Sharma%’;

(d) State the command to display the maximum Loan_Amt and Cust_Name :-

(i) Select Max(Cust_Name, Loan_Amt) from LOANS;

(ii) Select Cust_Name, max(LoanAmt) from LOANS;

(iii) Select Cust_Name, max(Loan_Amount) from LOANS;

(iv) Select Cust_name, max(Loan_Amt) from LOANS;

(e) Help Ramesh to display the count of all loan holders whose interest is NULL.

(i) Select Count(*) from LOANS where Int_Rate is NULL.

(ii) Select Count() from LOANS where Interest is NULL.

(iii) Select Count(*) from LOANS where Int_rate = NULL;

(iv) Select Count( ) from LOANS where Interest = NULL;

Choose the correct option.

(a) What is String data-type in Python?

(i)It is written in double quotes only.

(ii)Its value can be changed.

(iii)It is enclosed in square brackets.

(iv)It is immutable data-type.

(b) Ms. Ragini is confused on the way to compare the strings. Help her in choosing the correct statement.

(i)equal() (ii)equals() (iii) == (iv)compare()

(c) Tejas is confused in the output of the following code. Help him to choose the correct option.

age = 12

txt = "My name is Tejas, I am " , age

print(txt)

(i)('My name is Tejas, I am ', 12)

(ii)My name is Tejas, and I am 12

(iii)(My name is Tejas, I am , 12)

(iv)[My name is Tejas, I am , 12]

(d) Help mini to choose the correct output of the following Python code.

rollno = 3

name = "Mini"

Average = 88.76

Txt = "My name is {1}, having roll no {0} and scored {2} percent."

print(Txt.format(rollno, name, Average))

(i)My name is ‘Mini’, having roll no 3 and scored 88.76 percent.

(ii)My name is Mini, having roll no 3 and scored 88.76 percent.

(iii)‘My name is Mini, having roll no 3 and scored 88.76 percent.’

(iv)(My name is Mini, having roll no 3 and scored 88.76 percent.)

(e) Help Gurnika to choose the correct option for the following Python code.

b = "Hello, World!"

print(b[-5:2])

(i)Hello, World! (ii)orl (iii) Error (iv) No Output

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?

Krishna of class 12 is writing a program to read the details of Sports performance and store in the csv file “games.csv” delimited with a tab character. As a programmer, help him to achieve the task.

(a)Line 1 : Name the module he should import.

(b)Line 2 : To create an object to enable to write in the csv file.

(c)Line 3 : To store the data in list

(d)Line 4 : To write a record.

(e)Line 5 : To close the file.

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’;

Mr. Iqbal is a programmer, who has recently joined the company and given a task to write a python code to perform the following binary file operations with the help of two user defined functions/modules:

Answer the following question on the basis of given dataframe:

(i) To print the maximum salary of the Total Salary column.

(ii) To print the data in ascending order of Total Salary.

(iii) To print the data in descending order of Total Salary.

(iv) To print 'sum','mean','max','min','count','median','var'fro the columns 'SALARY','tax','Total Salary'.

Answer the following questions on the basis of given data set:

	index	date	duration	item	month	network	network_type
0	0	15/10/14 06:58	34.429	data	2014-11	data	data
1	1	15/10/14 06:58	13.000	call	2014-11	Vodafone	mobile
2	2	15/10/14 14:46	23.000	call	2014-11	Airtel	mobile
3	3	15/10/14 14:48	4.000	call	2014-11	data	mobile
4	4	15/10/14 17:27	4.000	call	2014-11	Airtel	mobile
5	5	15/10/14 18:55	4.000	call	2014-11	Airtel	mobile
6	6	16/10/14 06:58	34.429	call	2014-11	data	data
7	7	16/10/14 15:01	602.000	call	2014-11	Vodafone	mobile
8	8	16/10/14 15:12	1050.000	call	2014-11	Airtel	mobile
9	9	16/10/14 15:30	19.000	call	2014-11	voicemail	voicemail
10	10	16/10/14 16:21	1183.000	call	2014-11	Vodafone	mobile
11	11	16/10/14 22:18	1.000	sms	2014-11	Airtel	mobile
12	12	16/10/14 22:21	1.000	sms	2014-11	Vodafone	mobile
13	13	17/10/14 06:58	34.429	data	2014-11	data	data

(i) To count the rows in the dataset

(ii) What was the longest phone call / data entry?

(iii) How many seconds of phone calls are recorded in total?

(iv) How many entries are there for each month?

(v) To print the group key

(vi) To count the group keys

(vii) Get the first entry for each month

(viii) Get the sum of the durations per month

(ix) Get the number of dates / entries in each month

(x) What is the sum of durations, for calls only, to each network

(xi) How many calls, sms, and data entries are in each month?

(xii) How many calls, texts, and data are sent per month, split by network_type?

(xiii) Group the data frame by month and item and extract a number of stats from each group

(xiv) Group the data frame by month and item and extract a number of stats from each group

Oberoi Industries has set up its new center at Mangalore for its office and web-based activities. It has 4 blocks of buildings as shown in the diagram below:

Center to center distances between various blocks :

Block A to Block B	50 m
Block B to Block C	150 m
Block C to Block D	25 m
Block A to Block D	170 m
Block B to Block D	125 ill
Block A to Block C	90 m

Number of Computer:

Block A	25
Block B	50
Block C	125
Block D	10

(a) Suggest the device which is used to connect all the nodes in particular block.

(i) Repeater (ii) Gateway

(iii) Bridge (iv) Switch

(b) Suggest the most suitable wire to connect all the blocks with high-speed data transfer.

(i) Ethernet Cable (ii) Twisted Pair

(iii) Optical Fibre (iv) Co-axial Cable

(c) The organization is planning to link its front office situated in the city in a hilly region where cable connection is not feasible, suggest an economic way to connect way to connect it with reasonably high speed.

(i) Satellite (ii) Radio Wave

(iii) Microwave (iv) Bluetooth

(d) Suggest the appropriate protocol/way to do the audio-video communication between the different parts of the world.

(i) E-Mail (ii) Blog

(iii) Chat (iv) VOIP

(e) What type of network would be formed if the Mangalore office is connected to their New York office?

(i) LAN (ii) PAN

(iii) MAN (iv) WAN

“IT Tech ”Company conducts workshops for employees of organizations. The company requires data of workshops that are organize(d) As a database administrator you have decided that:

Name of the Database: IT

Name of the Table: WORKSHOP

Attributes of Table ‘Workshop ’are as follows.

Field	Type	Constraint
Workshopld	integer	Primary Key
Title	Varchar(50)
DateWorkshop	Date
NumSpeakers	Integer

Table : WORKSHOP

WorkshopId	Title	Date Workshop	NumSpeakers
1001	Robotics	2020-02-21	3
1002	AI	2020-05-24	4
1003	Humanoids	2020-07-15	3
1004	Block Chain	2020-04-25	5
1005	Cloud	2020-03-03	4

(a) Which attribute can be the primary key.

(i) Title (ii) WorkshopID

(iii) NumSpeaker (iv) DateWorkshop

(b) Which command will display the structure of the table:

(i) Use workshop (ii) Desc

(iii) Desc workshop (iv) Show tables

(c) Command to display all the record in descending order of number of speakers.

(i) Select * from Workshop reverse by NumSpeakers;

(ii) Select * from workshop order by descNumSpeakers;

(iii) Select * from workshop order by NumSpeakersdesc;

(iv) Select * from workshop group by NumSpeakersdesc;

(d) What will be the cardinality and degree of the table: WORKSHOP

(i) Cardinality = 5, Degree =4

(ii) Cardinality = 4, Degree =5

(iii) Cardinality = 6, Degree =4

(iv) Cardinality = 5, Degree =5

(e) Choose the right command which will delete the record of 1004 WorkshopId

(i) Delete * from workshop where workshopid==1004;

(ii) Delete from workshop where workshopid=1004;

(iii) Drop from workshop where workshopid=1004;

(iv) Delete table from workshop where workshopid=1004;