Download App

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

CBSE BoardEnglish MediumSTD 12 ScienceComputer ScienceDatabase Management – SQL3 Marks
Question
Write SQL queries for (i) to (iv) and find outputs for SQL queries (v) to (vii), which are based on the tables.

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

✓

Answer

(a) select tname, city, salary from trainer order by hiredate desc;

(b) Select tname, city from trainer where hiredate between ‘2001-12-01’ and ‘2001-12-31’;

OR

Select tname, city from trainer where hiredate >= ‘2001-12-01’ and hiredate<=‘2001-12-31’;

OR

Select tname, city from trainer where hiredate like ‘2001-12%’;

(c) Select tname, hiredate, cname, startdate from trainer, course where trainer.tid=course.tid and fees<=10000;

(d) Select city, count(*) from trainer group by city;

(e) Select tid, tname, from trainer where city not in(‘delhi’, ‘mumbai’);

(f) Distinct tid

101

103

102

104

105

(g)

(h)

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 "3 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

How to get the items of series A not present in series B?
→
What is the basic difference between Trojan Horse and Computer Worm?
→
Write SQL queries for (i) to (iv) and find outputs for SQL queries (v) to (viii), which are based on the tables:

TABLE: SALESPERSON

CODE NAME SALARY ITCODE
1001 TANDEEP JHA 60000 I2
1002 YOGRAJ SINHA 70000 I5
1003 TENZIN JACK 45000 I2
1005 ANOKHI RAJ 50000 I7
1004 TARANA SEN 55000 I7

TABLE: ITEM

ITCODE ITEMTYPE TURNOVER
I5 STATIONARY 3400000
I7 HOISTERY 6500000
I2 BAKERY 10090000

(a) To display the CODE and NAME of all salesperson having “I7” item Type Code from the table SALESPERSON.
(b) To display all details from table SALESPERSON in descending order of SALARY.
(c) To display the number of SALESPERSON dealing in each TYPE of ITEM.(Use ITCODE for the same)
(d) To display NAME of all the salesperson table along with their corresponding ITEMTYPE from the ITEM table.
(e) Select max(salary) from salesperson;
(f) Select distinct itcode from salesperson;
(g) select code,name,i.itcode from sales persons, item i where s.itcode=i.itcode and turnover>=7000000;
(h) Select sum (salary)from salesperson where itcode=”i2”;

→
What are candidate keys in a table? Give a suitable example of candidate keys in a table.
→
Understand the code, answer the following questions:

(a) What is the run-time efficiency of the following code segment?
(b) Write the output.
(c) What will be the runtime efficiency if $n=2$?
→
Explain the concept of split-apply-combin in group by.
→
 Write a python script to read the numbers from the user until user presses zero and print sum , average and maximum number entered by the user.
→
Python program to get the difference between the two lists.
→
Write function insert() to insert the new row in table emp. The function will take the values as parameter.
→

Consider the following table Bank.

Table: Bank

AccNo Cust_name FD_Amount Months Int_Rate FD_Date
1001 Arti Gupta 30000 36 6.00 2018-07-01
1002 Dilip Lal 50000 48 6.75 2018-03-22
1003 Navin Gupta 30000 36 NULL 2018-03-01
1004 D.P. Yadav 80000 60 8.25 2017-06-12
1005 Jyoti Sharma 20000 36 6.50 2017-01-31
1006 Rakesh Kumar 70000 60 8.25 2018-06-15
1007 K.D. Singh 50000 48 NULL 2018-07-05
1008 Anjali Sharma 60000 48 6.75 2017-04-02
1009 Swati Garg 40000 42 6.50 2018-06-15
1010 Rupinder Kaur 25000 36 6.50 2018-09-27

Write SQL commands for the statements (a) to (h) on the table Bank
(a) Display the details of all FD whose rate of interest is in the range 6% to 7%.
(b) Display the Customer Name and FD Amount for all the loans for which the number of Months is 24, 36, or 48(using IN operator).
(c) Display the Account Number, Customer Name and FD Amount for all the FD for which the Customer Name
ends with “Sharma”.
(d) Delete the records of “Rupinder Kaur”.
(e) Add another column Maturity_Amt of type Integer in the Bank table.
(f) To find the average FD amount. Label the column as “Average FD Amount”.
(g) To find the total FD amount which started in the year 2018?
(h) Update Maturity Amount of all bank customers.
a. Maturity Amount = (FD_Amount*Months* Int_rate)/(12*100)

→