Download App

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

CBSE BoardEnglish MediumSTD 12 ScienceComputer ScienceDatabase Management – SQL4 Marks
Question
Write SQL queries for (a) to (f) and write the outputs for the SQL queries mentioned shown in (g1) to (g4) parts on the basis of tables ITEMS and TRADERS:

Table: ITEMS

CODE INAME QTY PRICE COMPANY TCODE
1001 DIGITAL
PAD 12i 		120 11000 XENITA T01
1006 LED
SCREEN 40 		70 38000 SANTORA T02
1004 CAR GPS
SYSTEM 		50 21500 GEOKNOW T01
1003 DIGITAL
CAMERA
12X 		160 8000 DIGICLICK T02
1005 PEN DRIVE
32GB 		600 1200 STOREHOME T03

Table: TRADERS

TCode TName CITY
T01 ELECTRONIC SALES MUMBAI
T03 BUSY STORE CORP DELHI
T02 DISP HOUSE INC CHENNAI

(a) To display the details of all the items in the ascending order of item names (i.e. INAME).

(b) To display item name and price of all those items, whose price is in range of 10000 and 22000 (both values inclusive).

(c) To display the number of items, which are traded by each trader. The expected output of this query should be:

T01 2

T02 2

T03 1

(d) To display the price, item name and quantity (i.e. qty) of those items which have quantity more than 150.

(e) To display the names of those traders, who are either from DELHI or from MUMBAI.

(f) To display the names of the companies and the names of the items in descending order of company names.

(g1) Select max(price), min(price) from items;

(g2) Select price*qty amount from items where code=1004;

(g3) Select distinct tcode from items;

(g4) Select iname, tname from items i, traders t where i.tcode=t.tcode and qty <100;

✓

Answer

(a) SELECT * FROM ITEMS ORDER BY INAME;

(b) SELECT INAME, PRICE FROM ITEMS WHERE PRICE BETWEEN 10000 AND 22000;

(c) SELECT TCODE, COUNT(*) FROM ITEMS GROUP BY TCODE;

(d) SELECT PRICE, INAME, QTY FROM ITEMS WHERE QTY >150;

(e) SELECT INAME FROM TRADERS WHERE CITY IN (‘DELHI ’, ‘MUMBAI ’);

(f) SELECT COMPANY, INAME FROM ITEMS ORDER BY COMPANY DESC;

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

Write the SQL query questions from (i) to (iv) and write the output of SQL command for questions from (v) to (vii) given below:

Table: EMPLOYEES

EMPID FIRST NAME LAST NAME ADDRESS CITY
010 George Smith 83 First
Street 		Howard
105 Mary Jones 842 Vine
Ave. 		Losantiville
152 Sam Tones 33 Elm St. Paris
215 Sarah Ackerman 440 U.S. 110 Upton
244 Manila Sengupta 24 Friends
Street 		New Delhi
300 Robert Samuel 9 Fifth Cross Washington
335 Henry Williams 12 Moore
Street 		Boston
400 Rachel Lee 121 Harrison
St. 		New York
441 Peter Thompson 11 Red Road Paris

Table: EMPSALARY

EMPID SALARY BENEFITS DESIGNATION
010 75000 15000 Manager
105 65000 15000 Manager
152 80000 25000 Director
215 75000 12500 Manager
244 50000 12000 Clerk
300 45000 10000 Clerk
355 40000 10000 Clerk
4000 32000 7500 Salesman
441 28000 7500 Salesman

Write the SQL commands for the following :

(i) To show firstname, lastname, address and city of all employees living in paris.

(ii) To display the content of Employees table in ascending order of Firstname.

(iii) To display the firstname,lastname and total salary of all managers from the tables employee and empsalary, where total salary is calculated as salary+benefits.

(iv) To display the maximum salary among managers and clerks from the table Empsalary.

Give the Output of following SQL commands:

(v) Select firstname, salary from employees, empsalary where designation = ‘Salesman ’and Employees.

empid=Empsalary.empid;

(vi) Select count(distinct designation) from empsalary;

(vii) Select designation, sum(salary) from empsalary group by designation having count(*) >2;

→
Consider the following DEPT and EMPLOYEE tables. Write SQL queries for (i) to (iv) and find outputs for SQL queries
(v) to (viii).

Table: DEPT

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

Table: EMPLOYEE

ENO 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 employees.

(i) To display Eno, Name, Gender from the table EMPLOYEE in ascending order of Eno.

(ii) To display the Name of all the MALE employees from the table EMPLOYEE.

(iii) To display the Eno and Name of those employees from the table EMPLOYEE who a born between ‘1987‐01‐01 ’and ‘1991‐12‐01 ’.

(iv) To count and display FEMALE employees who have joined after ‘1986‐01‐01 ’.

(v) Select count(*),dcode from employee group by dcode having count(*)>1;

(vi) Select distinct department from dept;

(vii) Select name, department from employee e, dept d where e.dcode=d.dcode and en0 <1003;

(viii) select max(doj), min(dob) from employee;

→

Consider the following tables CARHUB and CUSTOMER and answer (a) and (b) parts of this question:

Table: CARHUB

Vcode VehicleName 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;

→
WAP to generate 2n+1 lines of the following pattern on the computer screen:
→
Write a python program using function to create the list of numeric values and search the number in the list using Linear Search Technique. The function will return –1 if element not found otherwise show the position.
→
Perfect Edu Services Ltd. is an educational organization. It is planning to setup its India campus at Chennai with its head office at Delhi. The Chennai campus has 4 main buildings – ADMIN, ENGINEERING, BUSINESS and MEDIA

Shortest distances between various buildings:

ADMIN to ENGINEERING 55 m
ADMIN to BUSINESS 90 m
ADMIN to MEDIA 50 m
ENGINEERING to BUSINESS 55 m
ENGINEERING to MEDIA 50 m
BUSINESS to MEDIA 45 m
DELHI Head Office to CHENNAICampus 2175 km

Number of Computers installed at various buildings are as follows:

ADMIN 110
ENGINEERING 75
BUSINESS 40
MEDIA 12
DELHI Head Office 20

(a) Suggest the most appropriate location of the server inside the CHENNAI campus (out of the 4 buildings), to get the best connectivity for maximum no. of computers. Justify your answer.

Which hardware device will you suggest to be procured by the company to be installed to protect and control the internet uses within the campus?

(d) Which of the following will you suggest to establish the online face-to-face communication between the people in the Admin Office of CHENNAI campus and DELHI Head Office?

(a) Cable TV

(b) Email

(c) Video Conferencing

(d) Text Chat

→
Write a python program to accept the list as parameter and print the prime numbers of the list.
→

Write a program to create the list and ask user whether you want to delete an element if say yes then delete the element and print the new list else print the entered list.

→
Given a list of numbers (integers), write a function to return the second maximum and second minimum in this list.

→
Write algorithm for Delete operation in Queue.
→