Get Latest MySQL MCQ with answers for technical interviews, IT competitive, CUET, IP, CS preparations and various competitive exams.
- Which of the following is not multiple row function?
a. Avg()
b. Sum()
c. Sqrt()
d. Both (a) and (b)
Show Answer
c. Sqrt()
- SELECT UPPER(SUBSTR(‘Computer System’, 3, 6));
Gives the output as:
a. MPU
b. MPUT
c. MPUTER
d. PUT
Show Answer
c. MPUTER
- substr( 56434.7812,4,5) gives the output ______
a. Will produce error
b. No Output
c. 34.78
d. 4.781
Show Answer
- Write SQL command to display the name of current month
a. SELECT MONTH(CURDATE());
b. SELECT NAMEOFMONTH(CURDATE());
c. SELECT MONTHNAME(CURDATE());
d. All of the above
Show Answer
c. SELECT MONTHNAME(CURDATE());
- WHERE and HAVING clause can be used interchangeably in SELECT query.
a. Yes
b. No
c. Depends on RDBMS
d. Not Sure
Show Answer
b. No
- Length() is a
a. Text function
b. Numeric function
c. Date function
d. Aggregate function
Show Answer
a. Text function
- Consider the string ‘techtipnow’. Write SQL command to display first five characters
a. Select left(‘techtipnow’,5);
b. Select substr(‘techtipnow’,1,5);
c. Select ltrim(‘techtipnow’,5);
d. Both (a) and (b)
Show Answer
d. Both (a) and (b)
- Write a query to display total no of departments from the given table ‘emp’.
| eno | ename | sal | Dept |
| 1 | Ram | 3400 | Store |
| 2 | Rakesh | 8000 | Back office |
| 3 | Prakash | 6200 | Back office |
| 4 | Daya | 1900 | Operation |
| 5 | Pawan | 3000 | Operation |
| 6 | Abdul | 2800 | Store |
| 7 | Simon | 4500 | operation |
a. SELECT COUNT(dept) FROM emp;
b. SELECT COUNT(DISTINCT dept) FROM emp;
c. SELECT eno, COUNT(dept) FROM emp GROUP BY eno;
d. SELECT dept, COUNT(ename) FROM emp GROUP BY dept;
Show Answer
b. SELECT COUNT(DISTINCT dept) FROM emp;
- Surendra have been asked to differentiate between WHERE and HAVING clause by his teacher. He wrote two statements for the same as given below. you have to state which statement is True and which is False.
(i) Both WHERE and HAVING clause can contain aggregate functions.
(ii) WHERE clause can used without GROUP BY clause but HAVING can not.
a. (i) True,(ii) False
b. (i) False, (ii) True
c. Both are True
d. Both are False
Show Answer
b. (i) False, (ii) True
- Consider following table ‘item’ and predict the output of following:
Select sum(length(title)) from item where author = ‘Dixit’ Group By Author
| No | Title | Author | Type | DOP | Qty | price |
| 1 | Data Structure | Dixit | DS | 12/10/2003 | 4 | 300 |
| 2 | Computer Studies | French | FND | 11/11/2003 | 2 | 75 |
| 3 | Advanced Pascal | Shildt | PROG | 01/12/2003 | 4 | 350 |
| 4 | Dbase Dummies | Palmer | DBMS | 23/12/2003 | 5 | 130 |
| 5 | Mastering C++ | Dixit | PROG | 08/01/2004 | 3 | 295 |
| 6 | Guide Network | Freed | NET | 10/02/2004 | 3 | 200 |
| 7 | Mastering Foxpro | Seigal | DBMS | 17/02/2004 | 2 | 135 |
| 8 | DOS Guide | Norton | OS | 11/03/2004 | 3 | 175 |
| 9 | Basic for beginner | Morton | PROG | 21/04/2004 | 3 | 40 |
| 10 | Mastering C Programs | Dixit | PROG | 06/05/2004 | 1 | 195 |
a. 45
b. 47
c. 3
d. 15
Show Answer
b. 47
- For given string ‘techtipnow’ sanjay wants to display the position of ‘p’. what code he should write to get this?
a. Select mid(‘techtipnow’,’p’);
b. Select substr(‘techtipnow’,’p’);
c. Select instr(‘techtipnow’,’p’);
d. All of the above
Show Answer
c. Select instr(‘techtipnow’,’p’);
- Chunnu has created following table to store the records:
Table : Loan
| Accno | Accname | Amount | Laontype | Interest |
| TT101 | Sanjeev | 55000 | Auto | 7.65 |
| TT102 | Abhishek | 300000 | Home | 7.1 |
| TT103 | Shagun | 12000 | Personal | 9.85 |
| TT104 | Gunjan | 30000 | Auto | 6.90 |
| TT105 | Jyoti | 100000 | Home | 9.10 |
He wants to calculate average interest rate of ‘Auto’ type of laon. Help him to do so.
a. select avg(interest) from loan where loantype = ‘auto’;
b. select avg(interest) from loan where loantype in (‘auto’);
c. select avg(interest) from loan where loantype = ‘auto’ group by loantype;
d. all of above
Show Answer
d. all of above
- which of the following is not a single row function?
a. Mod()
b. Ltrim()
c. Year()
d. Max()
Show Answer
d. Max()
- Predict the output of ‘Select Round(33792.845,-3);
a. 34000
b. 33000
c. 33800
d. 33793
Show Answer
a. 34000
- Rahul has created a table book with following columns-
Bcode, bname, price, publication
He wants to display lowest price of book publication wise. He wrote following code
Select publication, min(price) from book;
But he did not get the desired result. Help him to get the desired result by rewriting query.
a. Select min(price), publication from book;
b. Select publication, min(price) from book group by publication;
c. Select publication, min(price) from book having publication
d. All of above
Show Answer
b. Select publication, min(price) from book group by publication;
- Consider the following table Hospital given below and answer your question from (i)-(v).
| No | Name | Age | Department | Deteofadm | Charges | Gender |
| 1 | Suman | 56 | Surgery | 21/01/21 | 300 | F |
| 2 | Subhash | 65 | ENT | 12/12/20 | 250 | M |
| 3 | Raheem | 43 | Orthopedic | 19/02/20 | 200 | M |
| 4 | Arush | 24 | Surgery | 11/01/20 | 300 | M |
| 5 | Sarita | 32 | ENT | 12/01/20 | 250 | F |
| 6 | Guleshwari | 18 | Cardiology | 20/02/20 | 800 | F |
| 7 | Kushagra | 28 | Cardiology | 13/01/20 | 800 | M |
| 8 | Shilpa | 24 | ENT | 21/02/20 | 250 | F |
Select appropriate SQL command to display name and department of all the male candidates with their date of admission in ascending order.
a. Select name, department from hospital where gender is ‘M’ and order by dateodadm;
b. Select name, department from hospital where gender = ‘M’ and orderby dateofadm;
c. Select name, department from hospital where gender = ‘M’ order by dateofadm;
d. Select name, department from hospital order by dateofadm where gender = ‘M’;
Show Answer
c. Select name, department from hospital where gender = ‘M’ order by dateofadm;
- Jamwant intends to group the result set based on some column’s value. Also, it is required that the grouped result should appear in sorted order. In which order should the Jamwant write the two clauses (for sorting and grouping) ?
a. Select <fieldnames>
From <table name>
Order by <fieldname>
Group by <fieldname>
b. Select <fieldnames>
From <table name>
Group by <fieldname>
Order by <fieldname>
c. Both (a) and (b) can be taken
d. None of these
Show Answer
b. SelectFrom Group by
Order by
- Predict the output of
LENGTH(RTRIM(“ Techtipnow “)); [Note: there are 3 space before and after ‘techtipnow’]
a. 13
b. 10
c. 16
d. 9Show Answer
a. 13
- Which of the following can be used to display current date?
a. Now()
b. Date()
c. Curdate()
d. Both (a) and (c)
e. All of the aboveShow Answer
d. Both (a) and (c)
- Name the functions to remove unnecessary leading and trailing space characters comes with string.
a. Left(), right()
b. Lefttrim(), righttrim()
c. Ltrim(), rtrim()
d. Substr(), instr()Show Answer
c. Ltrim(), rtrim()
- Consider the following table ‘Student’ and predict the output of following query:
Select count(*), count(marks) from Student;
Rollno Name Subject Marks 1 Sanjay Science 58 2 Ravi Math 89 3 Gaurav Math Null 4 Vishal Science 88 5 Rahul Art 90 6 Jaishankar Science 76 a. 6,6
b. 6,5
c. 5,6
d. 5,5Show Answer
b. 6,5
- For given table ‘student’ in Q21, find the output of :
Select max(marks) + Min(marks) from student where subject = ‘science’;
a. 134
b. 146
c. No Output
d. Will generate ErrorShow Answer
b. 146
- Aryan, a database administrator, has grouped records of a table with the help of group by clause. He needs to further filter groups of records generated through group by clause. Suggest suitable clause for it
a. Where Clause
b. Having Clause
c. Both
d. None of theseShow Answer
b. Having Clause
- Consider the following tables Product and Customer.
PID PNAME BRAND PRICE Tp01 Telcom powder LAK 40 Fw05 Face Wash ABC 45 B501 Bath soap ABC 55 Sh06 Shampoo XYZ 120 Fw12 Face wash XYZ 95
CID CNAME CITY PID 01 Cosmetic shop Delhi Fw05 02 Total health Mumbai B501 12 Live life Delhi Sh06 15 Pretty woman Delhi Fw12 10 Dreams Chennai Tp01 Write SQL command to display customername and products name purchased by customer who lives in Delhi
a. Select cname, pname from customer, product where product.pid = customer.pid having city = ‘delhi’;
b. Select cname, pname from customer, product where product.pid = customer.pid and city = ‘delhi’;
c. Select cname, pname from customer c, product p where p.pid = c.pid and city = ‘delhi’;
d. All of above
e. Both (b) and (c)Show Answer
e. Both (b) and (c)
- Consider the following tables Employee and Job
Eid Ename Sales Jobid E1 Sanjay lal 1100 102 E2 Ravi batra 1200 101 E3 Jagbir singh 1400 103 E4 Mohit mathur 1250 102 E5 Ashish yadav 1450 103
Jobid Title sal 101 President 20000 102 Director 16500 103 Manager 12000 104 Accountant 8000 105 Clerk 6000 Write SQL queries to display name of employee whose salary is lowest.
a. Select ename, min(sal) from employees, job where job.jobid = employee.jobid;
b. Select ename, min(sal) from employees, job;
c. Select ename, min(sal) from employee, job having job.jobid = employee.jobid;
d. All of the aboveShow Answer
a. Select ename, min(sal) from employees, job where job.jobid = employee.jobid;
- Predict the output of
MOD(ROUND(13.9,0),3); [CBSE IP TERM-2 2022]
a. 4
b. 2
c. 1
d. 1.9Show Answer
b. 2
- Srikant created the following table ‘Student’ in his database; [CBSE IP TERM 2 2022]
Table: Student
Rollno Name Class Marks 1 Ritika 12 40 2 Angad 12 35 3 Kaveri 11 42 4 Durgesh 12 21 5 Denial 11 44 6 Rabindra 11 39 7 Rabia 11 28 He now wants to count number of student in each class where the number of student is more than 3. He has executed the following query:
Select max(marks) from student where count(*) >3 group by class;
But he got an error. Help him in writing query correctly
a. Select max(marks) from student group by class having count(*)>3;
b. Select max(marks) from student group by class where count(*) >3;
c. Select count(marks) from student group by class having count(*) >3;
d. Select count(marks) from student group by class where count(*) >3;Show Answer
c. Select count(marks) from student group by class having count(*) >3;
- Write SQL command to display book details of those books purchased on Monday for given table ‘book’
No Title Author Type DOP Qty price 1 Data Structure Dixit DS 12/10/2003 4 300 2 Computer Studies French FND 11/11/2003 2 75 3 Advanced Pascal Shildt PROG 01/12/2003 4 350 4 Dbase Dummies Palmer DBMS 23/12/2003 5 130 5 Mastering C++ Dixit PROG 08/01/2004 3 295 6 Guide Network Freed NET 10/02/2004 3 200 7 Mastering Foxpro Seigal DBMS 17/02/2004 2 135 a. Select * from book where day(dop) like ‘Monday’;
b. Select * from book where day(dop) = ‘Monday’;
c. Select * from book where dayname(dop) like ‘Monday’;
d. Select * from book where dayname(dop) = ‘Monday’;Show Answer
d. Select * from book where dayname(dop) = ‘Monday’;
- Predict the output of following code :
SELECT UPPEER(MID(‘techtipnow computers’,10));
a. techtipnow
b. TECHTIPNOW
c. W COMPUTERS
d. IPNOW COMPShow Answer
c. W COMPUTERS
- Which of the following statement is true about ‘Cartesian Product’?
a. It is an operation that combines tuples from two relations regardless of whether they have the same values or not.
b. The degree of resulting relation is calculated as the product of the degrees of both the relation under construction.
c. The cardinality of resulting relation is calculated as the sum of the cardinality of relations under consideration
d. All are trueShow Answer
a. It is an operation that combines tuples from two relations regardless of whether they have the same values or not.
- Which of the following is not a valid join type?
a. Equi join
b. Non equi join
c. Natural join
d. Inner joinShow Answer
b. Non equi join
2 thoughts on “MySQL Functions MCQ”
Leave a Comment
Pingback: Library Management System in Python | Download Free – techtipnow
Pingback: Hospital Management System in Python | Mini School College Project – techtipnow