Page 22 - IPP-12-2024

Table: FRUITS

Import Origin_ Port_of Price_Per_ Total_Value
Date Fruit_Code Fruit_Name Country _Discharge Quantity Unit (USD) (USD)
2016-11-22 08100 Fresh Durian Thailand Banglore 110 2.11 231
2016-11-22 08050 Fresh Kiwi United States Kolkata 18637 1.90 35433
2016-11-22 08110 Java Apple Thailand Kolkata 75 3.70 279
2016-11-22 08509 Mongosteen Thailand Kolkata 695 3.70 2572

Help him by writing SQL queries given below to perform the following tasks:
(i) Insert a new record in the table having the following values: ["2016-12-22", 08000,
"CHERRIES", "CHINA", "MUMBAI", 200, 1.0, 200]
(ii) To change the name of fruit "MONGOSTEEN" to "MELON".
(iii) To remove the records of those fruits whose total value is less than 300 USD.
(iv) To add a new column QUALITY_ CHECK of CHAR type having size 1 byte with a default value "P".
(v) To display the records of the imported fruits that have been imported in the month of December.
Ans. (i) SELECT MOD(53,4)as "Modulus_Result";(can give any column name)
(ii) SELECT DAYNAME("1998-12-15");(enter any DOB)
(iii) SELECT POW(8,-3);OR SELECT POWER(8,-3);
(iv) SELECT LCASE("I M POSSIBLE");OR SELECT LOWER("I M POSSIBLE");

(v) SELECT SQRT(125);
OR
(i) INSERT INTO FRUITS VALUES

("2016-12-22", 08000, "CHERRIES", "CHINA", "MUMBAI", 200, 1.0 , 200);
(ii) UPDATE FRUITS

SET FRUIT_NAME = "MELON"
WHERE FRUIT_NAME = "MONGOSTEEN";
(iii) DELETE FROM FRUITS
WHERE TOTAL_VALUE<300;
(iv) ALTER TABLE FRUITS ADD QUALITY_CHECK CHAR(1) DEFAULT "P";
(v) SELECT * FROM FRUITS WHERE MONTH(IMPORTDATE)=12;
OR
SELECT * FROM FRUITS WHERE
MONTHNAME(IMPORTDATE)="DECEMBER";
34. A Delhi-based media house is setting up its new office in Patna. It has five different blocks: General
Administration, Sales, News, Advertisement and Editorial. The tables given below show the distance between
different blocks and the number of computers in each block. (5)

From To Distance

General Administration Sales 100 m
General Administration Editorial 65 m
General Administration News 60 m
Editorial Sales 55 m
Editorial News 45 m
Editorial Advertisement 40 Km
Delhi Head Office Patna Branch 2228 Km

MODEL TEST PAPER 9