MySQL Functions Type B Sumita arora IP Solution

MySQL Functions Sumita arora IP Solution || MySQL Functions Sumita arora IP Class 12 Solution || MySQL Functions IP || MySQL Functions Information Practices || MySQL Functions Sumita arora Information Practices Solution || MySQL Functions Sumita arora Information Practices Class 12 Solution || MySQL Functions Sumita arora Solution

Q1. Given the following table:

Table: CLUB

COACH_ID    COACHNAME    AGE    SPORTS    DATOFAPP    PAY    SEX
1.    KUKREJA    35    KARATE    1996-03-27    1000    M
2.    RAVINA    34    KARATE    1998-01-20    1200    F
3.    KARAN    34    SQUASH    1998-02-19    2000    M
4.    TARUN    33    BASKETBALL    1998-01-01    1500    M
5.    ZUBIN    36    SWIMMING    1998-01-12    750    M
6.    KETAKI    36    SWIMMING    1998-02-24    800    F
7.    ANKITA    39    SQUASH    1998-02-20    2200    F
8.    ZAREEN    37    KARATE    1998-02-22    1100    F
9.    KUSH    41    SWIMMING    1998-01-13    900    M
10    SHAILYA    37    BASKETBALL    1998-02-19    1700    M

Give the output of following SQL statements:

(i) SELECT LCASE (SPORTS) FROM Club;
(ii) SELECT MOD (Age, 5) FROM CLUB WHERE Sex = ‘F’;
(iii) SELECT POWER(3, 2) FROM CLUB WHERE Sports = ‘KARATE’;
(iv) SELECT SubStr (CoachName, 1, 2) FROM CLUB WHERE Datofapp > ‘1998-01-31’;





Q2 Given the following table:

Table: STUDENT1

No.    Name    Stipend    Stream    Avg Mark    Grade    Class
1    Karan    400.00    Medical    78.5    B    12B
2    Divakar    450.00    Commerce    89.2    A    11C
3    Divya    300.00    Commerce    68.6    C    12C
4    Arun    350.00    Humanities    73.1    B    12C
5    Sabina    500.00    Nonmedical    90.6    A    11A
6    John    400.00    Medical    75.4    B    12B
7    Robert    250.00    Humanities    61.4    C    11A
8    Rubina    450.00    Nonmedical    88.5    A    12A
9    Vikas    500.00    Nonmedical    92.0    A    12A
10    Mohan    300.00    Commerce    67.5    C    12C

Give the output of following SQL statement:

(i) SELECT TRUNCATE(AvgMark) FROM Student1 WHERE AvgMark < 75;
(ii) SELECT ROUND(AvgMark) FROM Student1 WHERE Grade = 'B';
(iii) SELECT CONCAT (Name, Stream) FROM Student1 WHERE Class = '12A';
(iv) SELECT RIGHT (Stream, 2) FROM Student1;




Q3. Given the following table:

TABLE: LIBRARY


No    Title    Author    Type    Pub    Qty    Price
1.    Data Structure    Lipschutz    DS    McGraw    4    217
2.    Computer Studies    French    FND    Galgotia    2    75
3.    Advanced Pascal    Schildt    PROG    McGraw    4    350
4.    Dbase dummies    Palmer    DBMS    PustakM    5    130
5.    Mastering C++    Gurewich    PROG    BPB    3    295
6.    Guide Network    Freed    NET    ZPress    3    200
7.    Mastering Foxpro    Seigal    DBMS    BPB    2    135
8.    DOS guide    Norton    OS    PHI    3    175
9.    Basic for beginners    Morton    PROG    BPB    3    40
10.    Mastering Window    Cowart    OS    BPB    1    225

Give the output of following SQL commands on the basis of table Library.

(i) SELECT UPPER (Title) FROM Library WHERE Price < 150;
(ii) SELECT CONCAT (Author, Type) FROM Library WHERE Qty <3;
(iii) SELECT MOD (Qty, 4) FROM Library;

Perform following questions based on these tables:

Table PAYDAY (contains one column only)

CycleDate    DATE

Table ADDRESS (contains following eight columns)

Last Name    VARCHAR (25)

FirstName    VARCHAR (25)

Street    VARCHAR (50)

City    VARCHAR (25)

State    CHAR (2)

Zip    NUMBER

Phone    VARCHAR(12)

Ext    VARCHAR(5)

Q4. Write a query to show the current date and time.



Q5. Write a query to show the city of user with first name as 'MARK'.



Q6. Show via query how many days remain until Christmas. Round fractional days up using the numeric function ROUND.



Q7. Write a query against table PAYDAY that will show the number of days between the payday (CYCLEDATE column) and the last day of the month (LAST_DAY function).



Q8. Write a query against the EMPL table to show the names of all employees concatenated with their jobtypes.



Q9. Write a query against the ADDRESS table to show the names (first name, last name) and phones of all persons concatenated in following form:



Q10. Write a query against the ADDRESS table to select a list of names and phone numbers. The output should match these requirements:

• The name column should contain both the first and last names with a blank space between them.
Use the string concatenation.
• The second column will contain the phone number.
• Phone number should be in the format (999) 999-9999. Use the SUBSTR function and CONCAT.
• Order the query by last name then first name.