Download App

Database Query Using–SQL — Computer Science STD 12 Science — Question

CBSE BoardEnglish MediumSTD 12 ScienceComputer ScienceDatabase Query Using–SQL5 Marks
Question
Write SOL queries for (i) to (iv) and find outputs for SQL queries (v) to (viii) which are based on the tables. [Delhi 2019(C)]

TABLE : CUSTOMERS

NO CNAME ADDRESS
101 Richa jain Delhi
101 Surbhi Sinha Chennai
103 Lisa Thomas Bangalore
104 Imran Ali Delhi
105 Roshan Singh Chennai

TABLE : TRANSACTION

TRNO CNO AMOUNT TYPE DOT
T001 101 1500 Credit 2017-11-23
T002 103 2000 Debit 2017-05-12
T003 102 3000 Credit 2017-06-10
T004 103 12000 Credit 2017-09-12
T004 101 1000 Debit 2017-09-05

(a) To display details of all transactions of TYPE Credit from Table TRANSACTION.

(b) To display the CNO and AMOUNT of all transactions done in the month September 2017 from the table TRANSACTION.

(c) To display the last date of transaction (DOT) from the table TRANSACTION for the customer having CNO as 103.

(d) To display all CNO,CNAME and DOT (date of transaction) of those CUSTOMERS from tables CUSTOMERS and TRANSACTION who have done transactions more than or equal to 2000.

(e) Select count(*), avg(amount) from transaction where dot>= ‘2017-06-01’;

(f) Select cno, count(*), max (amount) from transaction group by cno having count(*)> 1;

(g) Select cno, cname from customer where address not in (‘Delhi, ‘Bangalore’);

(h) Select distinct cno from transaction;

✓

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

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

→

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;

→
Consider the SchoolBus table given below:
Table: School Bus
Rtno Area_covered Capacity Noofstudents Distance T ransporter Charges
1 Vasantkunj 100 120 10 Shivamtravels 100000
2 HauzKhas 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. 58000
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
(a) State the command that will give the output as:
Area_covered
Yamuna Vihar
Krishna Nagar
Vasundhara
(i) select area_covered from schoolbus where transporter Yadav Co.'and transporter='Bhalla Co.';
(ii) select area_covered from schoolbus where not transporter-Yadav Co.'and transporter=Bhaila Co.';
(iii) select area_covered from schoolbus where transporter - Yadav Co.'
OR transporter'Bhalla Co.';
(iv) select area_covered from schoolbus where transporter IN("Yadav co.", -Bhalla co."):
Choose the correct option:
(i) Both (i) and (ii). (ii) Both (iii) and (iv).
(iii) Any of the options (i), (ii) and (iv) (iv) Only (iii)
(b) What will be the output of the following command?
Select * from schoolbus where distance=20 order by charges;

(c) Ravi has given the following command to obtain the Longest distance Select max(distance) from schoolbus where group by transporter;
But he is not getting the desired result.
Help him by writing the correct command.
(i) select max(distance) from schoolbus where group by transporter;
(ii) select transporter, max(distance) from schoolbus group by distance;
(iii) select transporter, max(distance) group by transporter from schoolbus;
(iv) select transporter, max(distance) from schoolbus group by transporter;
(d) Help Saumya to write the command to display the name of the transporter who is having lowest capacity in his schoolbus?
(i) select transporter.min(capacity) from schoolbus;
(ii) select transporter.max(capacity) from schoolbus:
(iii) select transporter.min(capacity) from schoolbus group by transporter:
(iv) select transporter.maximum(capacity)
(e) Choose the appropriate primary key.
(i) Transporter (ii) Area_covered (iii) Rtno (iv) Charges
→
Answer the question given below:

Country Expenditure GrowthRate UpdataDate GDP_Growth_rate
0 Australia 1287600000 25.00 2016-07 21.0
1 United States 989300000 24.56 2016-09 19.6

(i) Create the above file in Excel and write the command to create the data frame.

(ii) To print the dataframe.

(iii) To set the index to the column country in the above data frame.

(iv) To add the new data frame in previous question data frame also sort the result.

(v) To change the first row date to 2016-01 for UpdateDate column.

→
Consider the following DEPT and WORKER tables. Write SQL queries for (i) to (iv) and find outputs for SQL queries (v) to (viii): [Delhi 2015]

Table: DEPT

DCODE DEPARTMENT CITY
D01 MEDIA DELHI
D02 MARKETING DELHI
D03 INFRASTRUCTURE MUMBAI
D05 FINANCE KOLKATA
D04 HUMAN RESOURCE MUMBAI

Table: WORKER

WNO NAME DOJ DOB GENDER DCODE
1001 George K 2013-09-02 1991-09-01 MALE D01
1002 Ryma Sen 2012-12-11 1990-12-15 FEMALE D03
1003 Mohitesh 2013-02-03 1987-09-04 MALE D05
1007 Anil Jha 2014-01-17 1984-10-19 MALE D04
1004 Manila Sahai 2012-12-09 1986-11-14 FEMALE D01
1005 R SAHAY 2013-11-18 1987-03-31 MALE D02
1006 Jaya Priya 2014-06-09 1985-06-23 FEMALE D05

Note: DOJ refers to date of joining and DOB refers to date of Birth of workers.

(i) To display Wno, Name, Gender from the table WORKER in descending order of Wno.

(ii) To display the Name of all the FEMALE workers from the table WORKER.

(iii) To display the Wno and Name of those workers from the table WORKER who are born between ‘1987-01-01’ and ‘1991-12-01’.

(iv) To count and display MALE workers who have joined after ‘1986-01-01’.

(v) SELECT COUNT(*), DCODE FROM WORKER GROUP BY DCODE HAVING COUNT(*)>1;

(vi) SELECT DISTINCT DEPARTMENT FROM DEPT;

(vii) SELECT NAME, DEPARTMENT, CITY FROM WORKER W,DEPT D WHERE W.DCODE=D.DCODE AND WNO<1003;

(viii) SELECT MAX(DOJ), MIN(DOB) FROM WORKER;

→
Search Engines allow us to filter the tons of information available on the internet and get the most accurate results. And while most people don’t pay too much attention to search engines, they immensely contribute to the accuracy of results and the experience you enjoy while scouring through the internet.

Besides being the most popular search engine covering over 90% of the worldwide market, Google boasts outstanding features that make it the best search engine in the market. It boasts cutting-edge algorithms, easy-to-use interface, and personalized user experience The platform is renowned for continually updating its search engine results and features to give users the best experience

(a) Search engines are:

(i)Software systems that are designed to search for information on the world wide web

(ii)Used to search documents.

(iii)Used to search videos.

(iv)All of the above

(b) We get a list of sites after typing a word in search bar called:

(i)Single word(ii)Key phrase

(iii)Site(iv)All of the above

(c) The search results are shown in a line of results. This is called:

(i)Search engine pages

(ii)Categories

(iii)Search engine result pages

(iv)Tag list

(d) Search engines are able to search _____ type of information.

(i)Videos(ii)Images

(iii)Documents(iv)All of the above

(e) Web search engines store information about web pages with the help of:

(i)Web router(ii)Web crawler

(iii)Web indexer(iv)Web organizer

→
Consider the following tables CARHUB and CUSTOMER and answer (a) and (b) parts of this question: [Delhi 2012]

Table: CARHUB

Vcode Vehicle Name Make Color Capacity Charges
100 Innova Toyota WHITE 7 15
102 SX4 Suzuki BLUE 4 14
104 C Class Mercedes RED 4 35
105 A-Star Suzuki WHITE 3 14
108 Indigo Tata SILVER 3 12

Table: CUSTOMER

CCode CName VCode
1 Hemant Sahu 101
2 Raj Lal 108
3 Feroza Shah 105
4 Ketan Dhal 104

(a) Write SQL commands for the following statements:

(i) To display the names of all white colored vehicles

(ii) To display name of vehicle, make and capacity of vehicles in ascending order of their sitting capacity

(iii) To display the highest charges at which a vehicle can be hired from CARHUB.

(iv) To display the customer name and the corresponding name of the vehicle hired by them.

(b) Give the output of the following SQL queries:

(i) Select count(distinct make) from cabhub;

(ii) Select max(charges), min(charges) from carhub;

(iii) Select count(*), make from carhub;

(iv) Select vehiclename from carhub where capacity = 4;

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

→
Mr. Sharma is working on pandas series and he is facing some problem while working on Series. Help him with few concepts by answering the given question:
He has created the series:

(a) He wanted the given output, which command will be appropriate to do the sam(e)

(b) Which command will give him the output as 5.
(i) s.index
(ii) s.size
(iii) s.values
(iv) None of the mentioned
(c) Assuming the changed structure, answer the question.

Which command will display the given output?
(i) s.head (2) (ii) s.top(2)
(iii) s.head() (iv) s.first(2)
(d) Choose the write output for the given command:
>>>print(stud1.count())
(i) 4 (ii) 3
(iii) 5 (iv) Error
(e) What is the name of the above Series?
(i) Student (ii) s
(iii) int64 (iv) float64
→
(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.

→