MySQL Function Queries

(Very important from exam point of view)

Q-1 Display last name, contribution and a third column which has contribution divided by 10. Round it to two
decimal places.
Ans- Select lastname, contribution Round (Contribution/10,2) FROM Charity;

Q-2 Display first 4 characters extracted from 3rd left letter from the string ABCDEFG.
Ans- SELECT SUBSTR(‘ABCDEFG’,3,4) ;
Since we are getting part of string, so we use substring function whereas ABCDEFG, 3 and 4 are parameters of
function. From 3rd letter till it counts fourth letter i.e F . Here ABCDEFG is a single string value to which we want to
apply function

Q-3 Display first three characters extracted from job column of employee id 8888 and 8900. [Table- emp, column
name-job, employee id=eid ]
Ans- SELECT SUBSTR(job,1,3) from emp where eid=8888 or eid=8900;
[Remember- Here job is the column name which will not come in inverted comma. This will work on all the values
of the job column.]

Q-4 Write a query to remove the leading and trailing spaces from string
‘ Punjab ’.
Ans- SELECT TRIM(‘ Punjab ’);

Q-5 Write a query to remove all the leading and trailing spaces from the column ename of table emp.
Ans- SELECT TRIM(ename) FROM emp;

Q-6 Write a query to remove the LEADING spaces from string ‘ Punjab’.
Ans- SELECT LTRIM(‘ Punjab’);

Q-7 Write a query to remove the Trailing spaces from string ‘Punjab ’.
Ans- SELECT RTRIM(‘ Punjab’);

Q-8 Display the position of PEN in INDEPENDENCE.

Ans- SELECT INSTR(‘INDEPENDENCE’,‘PEN’);

Q-9 Display the position of LE in job column of table emp .

Ans- SELECT INSTR(job, ‘LE’) FROM emp;
Note-Here job is a column name which will not come in inverted commas.

Q-10 How many characters are there in the string “Malala Yousufzai”.
Ans- SELECT LENGTH(“Malalal Yousufzai”);
Note- Space will also be counted

Q-11 Write a query to display the length of each column value of a column dept of the table emp and display that
column name as department size.
Ans- SELECT LENGTH(dept) AS “Department SIZE” FROM emp;

Q-12 Find out the remainder 11 divided by 4 and put it under the name Modulas.
Ans- SELECT MOD(11,4) AS “Modulas”;

Q-13 Write the query to find value of 32 and put it under the name Answer
Ans- SELECT POWER (3, 2) AS “Answer”;

DAV KOTKAPURA
Q-14 Write the above two queries jointly.
Ans- SELECT MOD(11, 4) AS “Modulas”, POWER(3,2) AS “Answer”;

Q-15 Truncate value 15.79 to 1 decimal space.

Ans- SELECT TRUNCATE(15.79,1);

Q-16 Write a query to display the annual salary of all employees whose commission is not null.(Table- emp, column-
monthly_salary)
Ans- SELECT monthly_salary AS “Annual Salary” FROM emp WHERE commission IS NOT NULL;

Q-17 Write a query to find how many types of jobs are offered to employees.
Ans- SELECT DISTINCT job FROM emp;

Q-18 Write a query to find the square root of a number 25

Ans- SELECT SQRT(25);

Q-19 Write the Query to find the sign of the number.

Ans- SELECT SIGN(-15);

Q-20 Write a query to find the remainder of the number when 26 divided by 4
Ans- SELECT MOD(26,4);

Q-21 Write the query to find the string from the middle of the string.
Ans- SELECT MID(“HINDUSTAN”,3,5);
Starting from 3rd i.e N till 5 characters i.e T. So the output will be NDUST

Q-22 Write the query to display first three characters from left of TABLET
Ans- SELECT LEFT(“Tablet”,3);
Output will be TAB

Q-23 Write the function to display current date.

Ans- SELECT CURDATE( );
i.e 2019-05-15

Q-24 Write the output SELECT MONTH(“2019-05-15”);

Ans- 05

Q-25 Write the output SELECT DAYNAME(“2019-05-15”);

Ans- Wednesday

Q-26 Write the output SELECT DAYOFWEEK(“2019-05-15”);

Ans- 04

Q-27 Write the output SELECT DAYOFYEAR(“2019-05-15”);

Ans- 135 ( number of day residing between 1 – 365)

Q-28 Write the output SELECT NOW( );

Ans- 2019-05-15 08:44:02
(YYYY-MM-DD HH-MM-SS)

Q-29 Write the query to display the names of those employees which have four lettered name.
Ans- SELECT * FROM emp WHERE ename LIKE ‘_ _ _ _’;

DAV KOTKAPURA