Computer Science
Write SQL queries based on the following tables:
Table: PRODUCT
| P_ID | ProductName | Manufacturer | Price | Discount |
|---|---|---|---|---|
| TP01 | Talcum Powder | LAK | 40 | NULL |
| FW05 | Face Wash | ABC | 45 | 5 |
| BS01 | Bath Soap | ABC | 55 | NULL |
| 5H06 | Shampoo | XYZ | 120 | 10 |
| FW12 | Face Wash | XYZ | 95 | NULL |
Table: CLIENT
| C_ID | ClientName | City | P_ID |
|---|---|---|---|
| 01 | Cosmetic Shop | Delhi | TP01 |
| 02 | Total Health | Mumbai | FW05 |
| 03 | Live Life | Delhi | BS01 |
| 04 | Pretty Woman | Delhi | 5H06 |
| 05 | Dreams | Delhi | FW12 |
(i) Write SQL Query to display ProductName and Price for all products whose Price is in the range 50 to 150.
(ii) Write SQL Query to display details of products whose manufacturer is either XYZ or ABC.
(iii) Write SQL query to display ProductName, Manufacturer and Price for all products that are not giving any discount.
(iv) Write SQL query to display ProductName and price for all products whose ProductName ends with 'h'.
(v) Write SQL query to display ClientName, City, P_ID and ProductName for all clients whose city is Delhi.
(vi) Which column is used as Foreign Key and name the table where it has been used as Foreign key?
Python
SQL Queries
6 Likes
Answer
(i)
SELECT ProductName, Price
FROM PRODUCT
WHERE Price BETWEEN 50 AND 150;
+-------------+-------+
| ProductName | Price |
+-------------+-------+
| Bath Soap | 55 |
| Face Wash | 95 |
| Shampoo | 120 |
+-------------+-------+
(ii)
SELECT * FROM PRODUCT
WHERE Manufacturer = 'XYZ' OR Manufacturer = 'ABC';
+------+-------------+--------------+-------+----------+
| P_ID | ProductName | Manufacturer | Price | Discount |
+------+-------------+--------------+-------+----------+
| BS01 | Bath Soap | ABC | 55 | NULL |
| FW05 | Face Wash | ABC | 45 | 5 |
| FW12 | Face Wash | XYZ | 95 | NULL |
| SH06 | Shampoo | XYZ | 120 | 10 |
+------+-------------+--------------+-------+----------+
(iii)
SELECT ProductName, Manufacturer, Price
FROM PRODUCT
WHERE Discount IS NULL;
+---------------+--------------+-------+
| ProductName | Manufacturer | Price |
+---------------+--------------+-------+
| Bath Soap | ABC | 55 |
| Face Wash | XYZ | 95 |
| Talcum Powder | LAK | 40 |
+---------------+--------------+-------+
(iv)
SELECT ProductName, Price
FROM PRODUCT
WHERE ProductName LIKE '%h';
+-------------+-------+
| ProductName | Price |
+-------------+-------+
| Face Wash | 45 |
| Face Wash | 95 |
+-------------+-------+
(v)
SELECT C.ClientName, C.City, P.P_ID, P.ProductName
FROM PRODUCT P, CLIENT C
WHERE P.P_ID = C.P_ID AND C.CITY = 'DELHI';
+---------------+-------+------+---------------+
| ClientName | City | P_ID | ProductName |
+---------------+-------+------+---------------+
| Cosmetic Shop | Delhi | TP01 | Talcum Powder |
| Live Life | Delhi | BS01 | Bath Soap |
| Pretty Woman | Delhi | SH06 | Shampoo |
| Dreams | Delhi | FW12 | Face Wash |
+---------------+-------+------+---------------+
(vi) The column used as a Foreign Key is P_ID in the CLIENT table, and it references the P_ID column in the PRODUCT table.
Answered By
2 Likes
Related Questions
What is the degree and cardinality of a table having 10 rows and 5 columns?
For the given table, do as directed:
Table: STUDENT
ColumnName Data type size Constraint ROLLNO Integer 4 Primary Key SNAME Varchar 25 Not Null GENDER Char 1 Not Null DOB Date Not Null FEES Integer 4 Not Null HOBBY Varchar 15 Null (i) Write SQL query to create the table.
(ii) Write SQL query to increase the size of SNAME to hold 30 characters.
(iii) Write SQL query to remove the column HOBBY.
(iv) Write SQL query to insert a row in the table with any values of your choice that can be accommodated there.
Answer the questions based on the table given below:
Table: HOSPITAL
S.No. Name Age Department Dateofadm Charges Sex 1 Arpit 62 Surgery 1998-01-21 300 M 2 Zareena 22 ENT 1997-12-12 250 F 3 Kareem 32 Orthopaedic 1998-02-19 200 M 4 Arun 12 Surgery 1998-01-11 300 M 5 Zubin 30 ENT 1998-01-12 250 M 6 Ketaki 16 ENT 1998-02-24 250 F 7 Ankit 29 Cardiology 1998-02-20 800 F 8 Zareen 45 Gynaecology 1998-02-22 300 F 9 Kush 19 Cardiology 1998-01-13 800 M 10 Shilpa 23 Nuclear Medicine 1998-02-21 400 F (a) To list the names of all the patients admitted after 1998-01-15.
(b) To list the names of female patients who are in ENT department.
(c) To list the names of all patients with their date of admission in ascending order.
(d) To display Patient's Name, Charges, Age for only female patients.
(e) Find out the output of the following SQL commands:
- SELECT COUNT(DISTINCT Charges) FROM HOSPITAL;
- SELECT MIN(Age) FROM HOSPITAL WHERE Sex = "F";
A department store MyStore is considering to maintain their inventory using SQL to store the data. As a database administrator, Abhay has decided that:
• Name of the database — mystore
• Name of the table — STOREThe attributes of STORE are as follows:
ItemNo — numeric
ItemName — character of size 20
Scode — numeric
Quantity — numericTable: STORE
ItemNo ItemName Scode Quantity 2005 Sharpener Classic 23 60 2003 Ball Pen 0.25 22 50 2002 Gel Pen Premium 21 150 2006 Gel Pen Classic 21 250 2001 Eraser Small 22 220 2004 Eraser Big 22 110 2009 Ball Pen 0.5 21 180 (a) Identify the attribute best suitable to be declared as a primary key.
(b) Write the degree and cardinality of the table STORE.
(c) Insert the following data into the attributes ItemNo, ItemName and SCode respectively in the given table
STORE.ItemNo = 2010, ItemName = "Note Book" and Scode = 25.
(d) Abhay wants to remove the table STORE from the database MyStore. Which command will he use from the following?
- DELETE FROM STORE;
- DROP TABLE STORE;
- DROP DATABASE MYSTORE;
- DELETE STORE FROM MYSTORE;
(e) Now Abhay wants to display the structure of the table STORE, i.e., name of the attributes and their respective data types that he has used in the table. Write the query to display the same.