Download App

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

CBSE BoardEnglish MediumSTD 12 ScienceComputer ScienceDatabase Management – SQL4 Marks
Question

Consider the following tables Product and Client. Write SQL commands for the statements (i) to (iv) and give outputs for SQL queries (v) to (viii)

TABLE: PRODUCT

P_ID Product Name Manufacturer Price
TP01 Talcom Powder LAK 40
FW05 FaceWash ABC 45
BS01 Bath Soap ABC 55
SH06 Shampoo XYZ 120
FW12 Face Wash XYZ 95

TABLE: CLIENT

C_ID ClientName City P_ID
01 Cosmetic Shop Delhi FW05
06 Total Health Mumbai BS01
12 Live Life Delhi SH06
15 Pretty Woman Delhi FW12
16 Dreams Bangalore TP01

(i) To display the details of those Clients whose City is Delhi

(ii) To display the details of Products whose Price is in the range of 50 to 100 (Both values included)

(iii) To display the ClientName, City from Table Client, and ProductName and Price from table Product, with their corresponding matching P_ID

(iv) To increase the Price of all Products by 10

(v) SELECT DISTINCT CITY FROM Client;

(vi) SELECT Manufacturer, MAX(Price), Min(Price), Count(*) FROM Product GROUP BY Manufacturer;

(vii) SELECT ClientName, ManufacturerName FROM Product, Client WHERE Client.Prod_Id = Product.P_Id;

✓

Answer

(i) Select * from client where city=”Delhi ”;

(ii) Select * from product where price between 50 and 100;

(iii) Select a.clientname,a.city ,b.productname, b.price from client a, product b where a.P_ID and b.P_ID;

(iv) Update product set price = price +10;

(v) City

Banglore

Delhi

Mumbai

(vi)

(vii)

Client Name Manufacturer
Total Health ABC
Cosmetic Shop ABC
Pretty Woman XYZ
Live Life XYZ
Dreams LAK

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 a python program using function to demonstrate random() along with list, tuple and dictionary. The program should do 3 things: (i) Should create the list of 5 random numbers between 10 to 99. (ii) Should create the tuple of 5 random numbers between 10 to 99. (iii) Should create the dictionary of 5 keys:values random characters as key and ASCII as its value.
→
Consider the following table HOSPITAL.

Table: HOSPITAL

PNo Name Age Department DateofAdm Charges Sex
1 Mayank 65 Surgery 23/02/2018 600 M
2 Babita 24 ENT 01/01/2019 400 F
3 Kashish 45 Orthopaedic 19/12/2018 400 M
4 Tarun 12 Surgery 01/10/2018 600 M
5 Manisha 36 ENT 12/01/2018 400 F
6 Imran 16 ENT 24/02/2018 400 M
7 Ankita NULL Cardiology 20/08/2018 800 F
8 Zoya 45 Gynecology 22/02/2018 500 F
9 Kush 19 Cardiology 13/01/2019 800 M
10 Shalini 31 Medicine 19/02/2018 300 F

Note: PNo is the primary key in the above table.

Write SQL commands for the statements (a) to (g) on the table HOSPITAL.

(a) To show all the information about the patients of the cardiology department.

(b) To list the names of female patients who are either in the orthopaedic or surgery department.

(c) To list the name of all the patients with their date of admission in ascending order.

(d) To display the patient’s name, charges, the age for female patients only.

(e) To count the number of patients with age > 30.

(f) To display various departments.

(g) To display the number of patients in each department.

→

Answer the (a) and (b) on the basis of the following tables STORE and ITEM:

Table: STORE

SNo SName AREA
S01 ABC Computronics GK II
S02 All Infotech Media CP
S03 Tech Shoppe Nehru Place
S05 Hitech Tech Store CP

Table: ITEM

INo IName Price SNo
T01 Mother Board 12000 S01
T02 Hard Disk 5000 S01
T03 Keyboard 500 S02
T04 Mouse 300 S01
T05 Mother Board 13000 S02
T06 Key Board 400 S03
T07 LCD 6000 S04
T08 LCD 5500 S05
T09 Mouse 350 S05
T10 Hard disk 4500 S03

(a) Write the SQL queries (1 to 4):

(i) To display IName and Price of all the items in the ascending order of their Price.

(ii) To display the SNo and SName or all stores located in CP.

(iii) To display the minimum and maximum price of each IName from the table Item.

(iv) To display the IName, price of all items and their respective SName where they are available.

(b) Write the output of the following SQL commands (i) to (iv):

(i) Select distinct iname from item where price >= 5000;

(ii) Select area, count(*) from store group by area;

(iii) Select count(distinct area) from store;

(iv) Select iname, price*0.05 discount from item where sno in (‘s02 ’, ‘s03 ’);

→
Write a function to read the content from the file “India.txt”, and store the frequency of each character in dictionary and display the dictionary in the screen. Content of the file is: ‘‘India is the fastest growing economy. India is looking for more investments around the globe. The whole world is looking at India as a great market. Most of the Indians can foresee the heights that India is capable of reaching.’’ Output is: {'I': 5, 'n': 15, 'd': 7, 'i': 15, 'a': 17, ' ': 39, 's': 13, 't': 15, 'h': 10, 'e': 20, 'f': 5, 'g': 8, 'r': 9, 'o': 17, 'w': 3, 'c': 4, 'm': 4, 'y': 1, '.': 4, 'n': 4, 'l': 6, 'k': 3, 'v': 1, 'u': 1, 'b': 2, 'T': 1, 'M': 1, 'p': 1}
→
Write the function Bubble() to sort the list using Bubble sort technique. The function will take the list as parameter.
→
WAP to find the sum of first n terms of the following series:
$1+\frac{x^3}{3 !}+\frac{x^5}{5 !}+\frac{x^7}{7 !} \ldots$
→

Answer the following question the basis of given dataframe:

Itemno ItemName Color Price
0 1 Ball Pen Black 15.0
1 2 Pencil Blue 5.5
2 3 Ball Pen Green 10.5
3 4 Gel Pen Green 11.0
4 5 Notenook Red 15.5
5 6 Ball Pen Green 11.5
6 7 Highlighter Blue 8.5
7 8 Gel Pen Red 12.5
8 9 P Marker Blue 5.6
9 10 Ball Pen Green 11.5

(i) To set the index to column Item Name.

(ii) To print the pivoting the data based on Item name as index, column as color and values as price.

(iii) To print the data as row index Itemname and columns index as color. Fill NaN as blank

(iv) To create another alias df3 and store the result of question 2.

(v) To add a new column QTY.

(vi) To print the mean priceod of all Item name and color wise.

(vii) To print the mean of the price, Item name and color margin wise.

(viii) To print the mean of the price, Item name and color wise with margins as Total.

(ix) To display the sorted data in ascending order according to the price

(x) To display the sorted data in descending order according to the price

(xi) To sort the data index wise.

(xii) To print the median.

(xiii) To print the maximum values column wise.

(xiv) To print the sum of the price.

(xv) To print the maximum of each column

(xvi) To print the first quantile of the price.

(xvii) To print the first quantile of Item no

(xviii) To print the first quartile, second quartile and third quartile. of Item no.

(xix) To print all the descriptive statistics.

→
Write a script to create a list of 5 integers and then
(a) Create a copy of this list using copy() method
(b) Display both the lists
(c) Input an integer from the user and insert it in the first list at a specific location. The location should also be input from the user. Use insert() method for insertion.
(d) Display both the lists
→
Write a menu driven program to do the following option on binary file “Student”. The program should implement through list which contains student number, student name and marks. Student Menu ------------------ 1) Add New Student 2) Display student details 3) Search student 4) Update a Record 5) Delete a Record 6) Save & Quit
→
Write a menu driven python program using function Push(), Pop() and Display() to implement the stack. The program will store the name of the books.
→