[Link] the following table “Garment”.
Write command for SQL for (i) to (iv).
Table : GARMENT
GCODE GNAME SIZE COLOUR PRICE
111 Tshirts XL Red 1400.00
112 Jeans L Blue 1600.00
113 Skirt M Black 1100.00
114 Ladies Jacket XL Blue 4000.00
115 Trousers L Brown 1500.00
116 Ladies Top L pink 1200.00
(i)To display names od those garments that are
available in ‘XL’ size.
- SELECT GNAME
FROM GARMENT
WHERE SIZE = ‘XL’;
(ii)To display codes and names of those garments
that have their names starting with ‘Ladies’.
-SELECT GCODE, NAME
FROM GARMENT
WHERE GNAME LIKE ‘Ladies%’:
(iii) To display garment names, codes and prices of
those garments that have price in the range 1000.00
to 1500.00 (both 1000.00 and 1500.00 included).
-SELECT GNAME, GCODE, PRICE
FROM GARMENT
WHERE PRICE BETWEEN 1000.00 AND 1500.00;
(iv) To change the colour of garment of garment
with code as 116 to “Orange”.
-UPDATE garment
SET COLOUR = ‘orange’
WHERE GCODE = 116;
[Link] the following table named
“SOFTDRINK”. Write commands of SQL
for (i) to (iv).
Table : SOFTDRINK
DRINKCODE DNAME PRICE CALORIES
101 Lime and Lemon 20.00 120
102 Apple Drink 18.00 120
103 Nature Nectar 15.00 115
104 Green Mango 15.00 140
105 Aam Panna 20.00 135
106 Mango Juice 12.00 150
(i)To display names and drink codes of those drinks
that have more than 120 calories.
-SELECT DNAME, DRINKCODE
FROM SOFTDRINK
WHERE CALORIES > 120;
(ii) To display drink codes, names and calories of all
drinks, in descending order of calories.
-SELECT DRINKCODE, DNAME, CALORIES
FROM SOFTDRINK
ORDER BY CALORIES DESC;
(iii) To display names and price of drinks that have
price in the range 12 to 18 (both 12 and 18 included).
-SELECT DNAME, PRICE
FROM SOFTDRINK
WHERE PRICE BETWEEN 12 AND 18;
(iv) Increase the price of all drinks in the given table
by 10%.
-UPDATE SOFTDRINK
SET PRICE = PRICE + 0.10*PRICE;
[Link] the following table CABHUB and
CUSTOMER. Write SQL commands for the
following statements.
Table : CABHUB
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
(i)To Display the names of all white colored vehicles.
-SELECT VehicleName
FROM CABHUB
WHERE Color = “WHITE”;
(iii) To display name of vehicle, make and capacity of
vehicles in ascending order of their seating capacity.
-SELECT VehicleName, Make, Capacity
FROM CABHUB
ORDER BY Capacity;
(iii) To display the highest charges at which a vehicle
can be hired from CABHUB.
-SELECT Max(Charges)
FROM CABHUB;
(iv) To display the customer name and the
corresponding name of the vehicle hired by them.
-SELECT CName, VehicleName
FROM CUSTOMER, CABHUB
WHERE [Link] = [Link];
[Link] a Database Company, there are two
tables given below:
Table : SALES
SALESMANID NAME SALES LOCATIONID
S1 ANITA SINGH AR. 250000 102
S2 Y.P SINGH 1300000 101
S3 TINA JAISWAL 1400000 103
S4 GURDEEP 1250000 102
SINGH
S5 SIMI FAIZAL 1450000 103
Table : LOCATION
LOCATIONID LOCATIONNAME
101 Delhi
102 Mumbai
103 Kolkata
104 Chennai
(i)To display SalesmanID, name of salesman,
LocationID with corresponding location names.
-SELECT SALESMANID, NAME, LOCATIONID, LOCATIONANME
FROM SALES S, LOCATION L
WHERE [Link] = [Link];
(ii) To display names of salesman, sales and
corresponding location names who have achieved
Sales more than 1300000.
-SELECT NAME, SALES, LOCATIONNAME
FROM SALES S, LOCATION L
WHERE [Link] = [Link] AND SALES > 1300000;
(iii) To display names of those salesman who have
‘SINGH’ in their names.
-SELECT NAME
FROM SALES
WHERE NAME LIKE ‘%SINGH%’;
(iv) Identify Primary key in the table SALES. Give
reason for your choice.
-Primary Key : SALESMANID
Reason:- It uniquely identifies all ROWS in the table and does not
contain empty/zero or null values.
5. In a Database Multiplexes, there are two
tables with the following data. Write
MySQL queries for (i) and (iii), which are
based on TicketDetails and AgentDetails.
Table : TicketDetail
Tcode NAME Tickets A_code
S001 Meena 7 A01
S002 Vani 5 A02
S003 Meena 9 A01
S004 Karish 2 A03
S005 Suraj 1 A02
Table : AgentDetails
ACode AName
A01 Mr. Robin
A02 Mr. Ayush
A03 Mr. Trilok
A04 Mr. John
(i)To display Trade, Name and Aname of all the
records where the number of tickets sold is more
than 5.
-Select Tcode, Name, AName
From TicketDetails TD, AgentDetails AD
Where TD.A_Code=[Link]
AND Tickets >5;
(ii) To display total number of tickets booked by
agent "Mr. Ayush”.
-Select Count(*)
From TicketDetails TD, AgentDetails AD
Where TD.A_Cod = [Link]
AND AName = “[Link]”;
(iii) To display Acode, Aname and corresponding
Tcode where Aname ends with ‘k’.
-Select Acode, Aname, Tcode
From TicketDetails TD, AgentDetails AD
Where TD.A_Code = [Link]
AND AName Like ‘%k’;