Page 195 - Informatics_Practices_Fliipbook_Class12
P. 195
9. Consider the table Patient given below and write SQL commands. [2023]
Table: Patient
Patientid Name City Phone Dateofadm Department
1000001 Ritvik Garg Delhi 68476213 2021-12-10 Surgery
1000002 Rahil Arora Mumbai 36546321 2022-01-08 Medicine
1000003 Mahek Bhatt Delhi 68421879 2022-02-02 Cardiology
1000004 Soumik Rao Delhi 26543266 2022-01-11 Medicine
1000005 Suresh Sood Bangalore 65432442 2021-03-09 Surgery
(i) Display the details of all patients who were admitted in January.
(ii) Count the total number of patients from Delhi.
(iii) Display the last 2 digits of the Patientid of all patients from Surgery Department.
Ans. (i) SELECT * FROM patient WHERE MONTHNAME(dateofadm) = 'January';
(ii) SELECT COUNT(*) FROM patient WHERE city = 'Delhi'; or SELECT city, count(*) FROM
patient
GROUP BY city HAVING city='Delhi';
(iii) SELECT RIGHT(patientid,2) FROM patient WHERE department = 'Surgery'; or SELECT
MID(patientid,-2) FROM patient WHERE department = 'Surgery';
10. Write the output (i-iii) for the following SQL commands. [2023]
Table: FASHION
ID Product Price Qty
F01 Kajal_ 970 10
F02 Foundation 2100 15
F03 Night Cream 1700 20
F04 Day Cream 1400 10
F05 Shampoo 1200 25
F06 Lipstick 850 32
(i) SELECT COUNT(Product) FROM FASHION;
(ii) SELECT SUM (Price*Qty) FROM FASHION WHERE Product="Night Cream";
(iii) SELECT LEFT (Product, 4) FROM FASHION WHERE Price>1500;
Ans. (i) COUNT(Product)
6
(ii) SUM(price*qty)
34000
(iii) LEFT(PRODUCT,4)
Foun
Nigh
OR
(i) SUBSTR("CLIMATE CHANGE", 4,4)
MATE
(ii) UCASE(RIGHT("Pollution" , 3))
ION
(iii) SELECT LENGTH ("HAPPY")+3
8
11. Write the SQL queries which will perform the following operations: [2023]
(i) To display the year from your Date of Admission which is '2023-05-15'.
Database Query using SQL 181

