Download as DOCX, PDF, TXT or read online from Scribd
Download as docx, pdf, or txt
You are on page 1/ 3
AUTUMN BREAK REMEDIAL CLASS 2022-23
DAY-2 1. Find the output of the following SQL queries : (i) SELECT lower(SUBSTR(“FIT INDIA MOVEMENT”, -8)) (ii) SELECT INSTR(“ARTIFICIAL INTELLIGENCE”, “CI”) 2. Ms. Mohini is working in a school and stores the details of all students in a table SCHOOLDATA. TABLE : SCHOOLDATA Admno Name Class House Percent Gender Dob 20150001 Aditya Das 10 Green 86 Male 2006-02-20 20140212 Harsh Sharma 11 Red 75 Male 2004-10-05 20090234 Swapnil Pant 10 Yellow 84 Female 2005-11-21 20130216 Soumen Rao 9 Red 91 Male 2006-04-10 20190227 Rahil Arora 10 Blue 70 Male 2005-03-14 20120200 Akasha Singh 11 Red 64 Female 2004-12-16 Write SQL statements from the above given table to : (i) To remove leading spaces from the column Name. 3. Predict the output of SQL queries from the table SCHOOLDATA given in above question (i) SELECT LEFT(Gender,1), Name FROM SCHOOLDATA WHERE YEAR(Dob)=2005; 4. Predict the output of the following SQL queries : 3 (i) SELECT TRIM(" ALL THE BEST “); (ii) SELECT UPPER(Mid(“Startup india”,10)); 5. Consider a table "MYPET" with the following data and write SQL query for given statement- Table : MYPET Pet_id Pet_Name Breed LifeSpan Price Discount 101 Rocky Labrador Retriever 12 16000 5 202 Duke German Shepherd 13 22000 10 303 Oliver Bulldog 10 18000 7 404 Cooper Yorkshire Terrier 16 20000 12 505 Oscar Shih Tzu NULL 25000 8 (i) Display the Breed of all the pets in uppercase. 6. Write the names of SQL functions to perform the following operations : 3 (i) Convert email-id to lowercase. (ii) Count the number of characters in your name. 7. Consider table : PRODUCT and predict output 3 Table : PRODUCT PID PNAME PRICE QUANTITY PIOOI Eraser 10.50 5 P1002 Ball Pen 15.00 2 P1003 Gel Pen 25.10 3 P1004 Ruler 5.00 1 (i) SELECT UCASE(RIGHT(PNAME,2)) FROM PRODUCT; 8. Consider table : 4 Table : SALESMAN Scode Sname Area Qtysold Dateofjoin sool Ravi North 120 2015-10-01 S002 Sandeep South 105 2012-08-01 S003 Sunil NULL 68 2018-02-01 s004 Subh West 280 2010-04-01 s005 Ankit East 90 2018-10-01 S006 Raman North NULL 2019-12-01 Predict the output for the following SQL queries : (i) SELECT LENGTH (Sname) FROM SALESMAN WHER MONTH(Dateofjoin)=10; (ii) SELECT Sname FROM SALESMAN WHERE RIGHT(Scode,1)=5; 9. Predict the output of the following queries: i. select instr('exams@cbse.nic.in','.'); ii. select substr('exams@cbse.nic.in',7,4); iii. select left('exams@cbse.nic.in',5); 10. Ms.Saumya is working on a MySQL table named ‘Hotel’ having following structure:
She need to perform following task on the table:
i. To fetch last 2 characters from the user_id column. ii. To display the values of name column in lower case. iii. To display 3 characters from 3rd place from the column city. Suggest suitable SQL function for the same. Also write the query to achieve the desired task. 11. While dealing with string data type in MySQL, its observed that sometimes unnecessary space character comes in between which hampers the successful execution of a string manipulation module. Name the suitable MySQL function (s) to remove leading, trailing and both type of space characters from a string. Also give MySQL queries to depict the same. 12. Write outputs for SQL queries (i) to (iii) which are based on the given table PURCHASE: TABLE: PURCHASE CNO CNAME CITY QUANTITY DOP C01 GURPREET NEW DELHI 150 2022-06-11 C02 MALIKA HYDERABAD 10 2022-02-19 C03 NADAR DALHOUSIE 100 2021-12-04 C04 SAHIB CHANDIGARH 50 2021-10-10 C05 MEHAK CHANDIGARH 15 2021-10-20 i. SELECT LENGTH(CNAME) FROM PURCHASE WHERE QUANTITY>100; ii. SELECT CNAME FROM PURCHASE WHERE MONTH(DOP)=3; iii. SELECT MOD (QUANTITY, DAY(DOP)) FROM PURCHASE WHERE CITY= ‘CHANDIGARH’; 13. Write suitable SQL query for the following: i. Display 7 characters extracted from 7th left character onwards from the string ‘INDIA SHINING’. ii. Display the position of occurrence of string ‘COME’ in the string ‘WELCOME WORLD’. iii. Remove all the expected leading and trailing spaces from a column userid of the table ‘USERS’ 14. Explain the following SQL functions using suitable examples. i. UCASE() ii. TRIM() iii. MID() 15. Shreya, a database administrator has designed a database for a clothing shop. Help her by writing answers of the following questions based on the given table: TABLE: CLOTH
CCODE CNAME SIZE COLOR PRICE DOP
C001 JEANS XL BLUE 990 2022-01-21 C002 T SHIRT M RED 599 2021-12-12 C003 TROUSER M GREY 399 2021-11-10 C004 SAREE FREE GREEN 1299 2019-11-12 C005 KURTI L WHITE 399 2021-12-07 i. Write a query to display cloth names in lower case. ii. Write a query to display the lowest price of the cloths. iii. Write a query to count total number of cloths purchased of medium size. iv. Write a query to count year wise total number of cloths purchased.
