Download App

Database Management – SQL — Computer Science STD 12 Science — Question

CBSE BoardEnglish MediumSTD 12 ScienceComputer ScienceDatabase Management – SQL5 Marks
Question

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:

Name
S. P. Shanna
K.S. Dhall
M.P. Yadav
P. Shanna
K.S. Dhall

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

✓

Answer

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

(b) (iv) None of the above

(c) (ii) Select Count(*) from LOANS Where Name like ‘%Sharma ’

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

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

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 Management – SQL→Database Management – 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 following Data Frame ‘‘emp’’ and answer any four questions from (i) – (v).

Ecode Name Age Fav_Color Salary
101 Rohit 20 Blue 45000
102 Mohanti 24 Red 36000
103 Tushar Koul 23 Green 42000
104 Rupali 22 Yellow 38000
105 Gurpreet 21 Pink 40000

(a) Select the command from the given options that will give the following output:-

(i) print(emp.max)

(ii) print(emp.max(axis=1))

(iii) print(emp.max,axis=1)

(iv) print(emp.max())

(b) A manager wants to know the Favourite colour of the employee with the employee code 103. Help him to identify the correct set of statements from the given options:

(i) df1=emp[emp[‘Ecode’]==103]

print(df1)

(ii) df1=emp['Ecode'==103]

print(df1)

(iii) df1=emp[emp.Ecode=103]

print(df1)

(iv) df1=emp[emp.Ecode==103]

print(df1)

(c) Which of the following statement will give the names of the employees whose salary is more than 40000.

(i) print(emp.max())

(ii) print(emp[emp["Salary"]>40000])

(iii) print(emp["Salary"]>40000)

(iv) print(emp.max()>40000)

(d) Which of the following command will list only the columns Ename and Salary using loc:

(i) print(emp.loc[:,[0,2]]

(ii) print(emp.loc[:,["Ename","Salary"]])

(iii) print(emp.loc(:["Ename","Salary"]))

(iv) print(emp.loc[["Ename","Salary"]])

(e) Mr. Singh, the manager wants to add a new column, the Rank with the values ‘IV’, ‘II’, ‘III’, ‘IV’, ‘I’, to the data frame Help him to identify the right command from the followings to do so :

(i) emp.column=[‘IV’, ‘II’, ‘III’, ‘IV’, ‘I’ ]

(ii) emp.iloc["Rank"] =[‘IV’, ‘II’, ‘III’, ‘IV’, ‘I’]

(iii) emp["Rank"] =[‘IV’, ‘II’, ‘III’, ‘IV’, ‘I’ ]

(iv) None of the above

→
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.

→
An email is a service of sending or receiving emails or messages in the form of text, audio, video, etc over the internet. Various service providers are providing email services to users. The most popular service providers in India are Gmail, Yahoo, Hotmail, Rediff, etc.

An email address for an email account is a unique ID. This email ID is used to send and receive mails over the Internet. Each email address has two primary components: username and domain name The username comes first, followed by the @) symbol and then the domain name

(a) The first email service was:

(i)Gmail(ii)Rediff

(iii)Hotmail(iv)Yahoo

(b) Unsolicited e-mail advertising is known as _______

(i)newsgroup

(ii)junk ads

(iii)spam

(iv)none of the above

(c) Which of the following is the correct format of email address?

(i)name@website@info

(ii)name@websiteinfo

(iii)www.nameofwebsitecom

(iv)namewebsitecom

(d) Mail access starts with client when user needs to download e-mail from the______

(i)mail box(ii)mail server

(iii)IP server(iv)internet

(e) When sender and receiver of an email are on same system, we need only two_____

(i)IP (ii)domain

(iii)servers(iv)user agents

→
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

→

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

→
(a) Create the Dictionary Student with the following key and add the 5 values. Dictionary will be created from 4 different list:

Column Name Key
Student Number sno
Student Name sname
E-mail mail
Marks mark

(b) Create the data frame for the above specification containing index parameter along with column.

(c) Add a new column grade into above Data Frame with at least 3 methods.

(d) Insert the 2 rows.

(e) Create another Data Frame with two rows named DF2.

(f) Add this data frame to your existing data frame.

(g) Display the alternate rows.

(h) Display the name and marks of the students whose marks is greater than equal to 90.

(i) Fill all NaN in the data.

(j) Increase the marks by 10.

→

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 the following questions:

id Feature1 Feature2
0 1 A B
1 2 C D
2 3 E F
3 4 G H
4 5 I J


id Feature1 Feature2
0 1 K L
1 2 M N
2 6 O P
3 7 Q R
4 8 S T

(i) To create the data frame for the above dataset.

(ii) To join the data frames.

(iii) To count the the rows in new data frame.

(iv) To reset the index.

→

Pawan is confused in understanding Dictionary concept. Help him to understand the concept by choosing the appropriate answer.

(a) Which statement will display Not Available?

(i) Statement 9 (ii) Statement 10

(iii) Statement 11 (iv) Statement 13

(b) Choose the key from Dictionary.

(i) ADMNO (ii) ‘admno ’

(iii) 101 (iv) “Not Available ”

(c) Which statement will display None?

(i) Statement 9 (ii) Statement 10

(iii) Statement 11 (iv) Statement 13

(d) Which Statement will display Tejas?

(i) Statement 8 (ii) Statement 9

(iii) Statement 10 (iv) Statement 11

(e) Choose the identifier.

(i) ADMNO (ii) ‘admno ’

(iii) setdefault (iv) Not Available

→

Consider the table "Grocer "and write SQL queries for the tasks that follow:

Table: Charity

Item_Id ItemName UnitPrice Quantity (kg) Date_Purchase
1 Rice 52.50 80 2010-02-01
2 Wheat 25.40 50 2010-03-09
3 Corn 50.80 100 2010-03-11
4 Semolina 28.90 50 2010-01-15

(Unit Price is per kg. price)

(i) Display Item name, unit price along with Date of purchase for all the Items.

(ii) Display Item name along with Month (in number) when it was purchased for all the items.

(iii) Display Item name along with year in which it was purchased for all the items.

(iv) Display Item Id, Date of Purchase and day name of week (e.g. Monday) on which it was purchased for all the items.

(v) Display names of all the items that were purchased on Mondays or Tuesdays.

(vi) Display the day name of the week on which Rice was purchased.

(vii) Display the Item name and unit price truncated to integer value (no decimal digits) of all the items.

(viii) Display current date.

→