[go: up one dir, main page]
Scribd
Upload
1K views4 pages

Write SQL Commands Based On The SOFT DRINK Table: Query 2. Display Drink Code With Calories of SOFTDRINK Table

The document provides 22 SQL queries to retrieve information from a SOFT DRINK table with columns DRINKCODE, DNAME, PRICE, CALORIES, and DOE. The queries select, insert, update, and aggregate data from the table to return drink information, counts, sums, averages, maximums, and minimums. Sample data with 4 rows is provided to populate the table.

Uploaded by

Suyash Mishra
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
1K views4 pages

Write SQL Commands Based On The SOFT DRINK Table: Query 2. Display Drink Code With Calories of SOFTDRINK Table

The document provides 22 SQL queries to retrieve information from a SOFT DRINK table with columns DRINKCODE, DNAME, PRICE, CALORIES, and DOE. The queries select, insert, update, and aggregate data from the table to return drink information, counts, sums, averages, maximums, and minimums. Sample data with 4 rows is provided to populate the table.

Uploaded by

Suyash Mishra
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
You are on page 1/ 4

write SQL Commands based on the SOFT DRINK table

DRINKCODE DNAME PRICE CALORIES DOE

101 LIME AND LEMON 30.00 120 03/02/15

102 APPLE DRINK 28.00 12 03/04/15

103 NATURE NECTAR 25.00 100 03/04/16

104 Green Mango Drink 25.00 78 03/04/16

i.Create the above SOFT DRINK table using Create Command

ii.Insert the above 4 rows in the SOFT DRINK table using INSERT Command.
Query 1. Display all information from SOFTDRINK table.

SELECT *
FROM SOFTDRINK;

Query 2. Display Drink code with Calories of SOFTDRINK table.

SELECT DRINKCODE, CALORIES


FROM SOFTDRINK;

Query 3. Display Drink Code, DName with DOE giving alias name to DOE as Date of
expiry of SOFTDRINK table.

SELECT DRINKCODE, DNAME, DOE “DATE OF EXPIRY”


FROM SOFTDRINK;

Query 4. Display all information for 103 drink code of SOFTDRINK table.

SELECT *
FROM SOFTDRINK
WHERE DRINKCODE=’103’;

Query 5. Display Drink Code, DName with DOE for calories less than 100 of SOFTDRINK
table.

SELECT DRINKCODE, DNAME, DOE


FROM SOFTDRINK
WHERE CALORIES<100;

Query 6. Display Drink Code, DName with DOE for calories greater than 100 and price of
less than 50 of SOFTDRINK table.

SELECT DRINKCODE, DNAME, DOE


FROM SOFTDRINK
WHERE CALORIES>100 and Price <50;

Query 7. Display all information were price is NULL in SOFTDRINK table.

SELECT *
FROM SOFTDRINK
WHERE PRICE is NULL;

Query 8. Display all information were Date of expiry after 2014 from SOFTDRINK table.

SELECT *
FROM SOFTDRINK
WHERE DOE>’2014-12-31’;

Query 9 : Display information of Green Mango.

SELECT *
FROM SOFTDRINK
WHERE lcase(DNAME)='green mango';

Query 10 : Display information of drinks start with the letter N.

Wild card character ( _ , %), a clause LIKE


_ = exactly one character
% = one OR multiple character

SELECT *
FROM SOFTDRINK
WHERE DNAME LIKE 'N%';

Query 11 : Display information of drinks ending with the letter K.

SELECT *
FROM SOFTDRINK
WHERE DNAME like '%K' OR DNAME like '%k';
Query 12: Display information of drinks with the letter O anywhere in the drink name.

SELECT *
FROM SOFTDRINK
WHERE DNAME like '%O%' OR DNAME like '%o%';

Query 13: Display information of drinks with the letter E as a third letter in the drink
name.

SELECT *
FROM SOFTDRINK
WHERE DNAME like '__E%' OR DNAME like '__e%';

Query 14: Display the information of drinks with length exactly 5 characters.

SELECT *
FROM SOFTDRINK
WHERE DNAME like '_____';

Query 15: Increase the price by 10 of apple drink.

UPDATE SOFTDRINK
SET PRICE=PRICE+10
WHERE DNAME=’APPLE DRINK’;

Query 16: Display DrinkCode, DName along with price incremented by 10 for apple drink.

SELECT DRINKCODE, DNAME, PRICE+10


FROM SOFTDRINK
WHERE DNAME=’APPLE DRINK’;

Query 17: Display the total amount spend on drinks.

SELECT SUM(PRICE)
FROM SOFTDRINK;

Query 18: Display the Average amount spend on drinks.

SELECT AVG(PRICE)
FROM SOFTDRINK;

Query 19: Display the highest and lowest price amount.

SELECT MAX(PRICE), MIN(PRICE)


FROM SOFTDRINK;
Query 20: Display the no of records of SoftDrink.

SELECT COUNT(*)
FROM SOFTDRINK;

Query 21: Display the count of Drink Names present in SoftDrink Table .

SELECT COUNT(DNAME)
FROM SOFTDRINK;

Query 22: Display the count of different(non repeated) Drink Names present in SoftDrink
Table .

SELECT COUNT(Distinct DNAME)


FROM SOFTDRINK;

*****************************

You might also like