Mysql Interview Questions and answers
Mysql is widely used database. Here I am putting MySql Interview Questions answers Query based
1. What is “SHOW” in mysql?
Ans:- SHOW is used for find all existent database on the server. But It does not show those database which have not user access privilege.
mysql> SHOW DATABASES;
2. What is “USE” in mysql?
Ans:- “USE” syntax is used for access and use of database.
mysql> USE databasename
3. How to grant managerial permission to the mysql database?
Ans:- GRANT ALL ON menagerie.* TO ‘your_mysql_name’@’your_client_host’;
4. How to connect mysql database using command line?
Ans:- There are following steps need to be follow:-
shell>mysql -h host -u user -p
Enter Password:- …….
For exit mysql database
5. How to get current version of Mysql database?
Ans:- Using VERSION() we can get current running version of mysql
mysql> SELECT VERSION();
6. How to get user of mysql database?
Ans:- Using USER() we can get user of mysql database
7. In mysql command line how we can cancel a query?
Ans:- Using \c for cancel current query;
mysql>SELECT * FROM tablename \c
8. How to create database in mysql?
mysql> CREATE DATABASE databasename;
9. How to create a table?
Ans:- CREATE TABLE syntax is use for creating table.
CREATE TABLE employee (firstname VARCHAR(30)), (lastname VARCHAR(30)), (salary VARCHAR(50)),(designation VARCHAR(50));
10. How to list table name from database?
Ans:- mysql> SHOW TABLES;
11. How to describe table?
Ans:- mysql>DESCRIBE employee
12. WHAT is LOAD DATA in mysql?
Ans:- If we create a text file containing a row for each of your animals, then load the contents of the file into the table with a single statement. For this we will use LOAD DATA
mysql> LOAD DATA LOCAL INFILE ‘/path/employee.txt’ INTO TABLE employee;
13. INSERT syntax in mysql?
Ans:- mysql> INSERT INTO employee VALUES (‘firstname’,’lastname’,’salary’,’address’);
14. Write update query in mysql?
Ans:- mysql> UPDATE employee SET salary=10000 WHERE salary=’5000′;
15. What is select statement in mysql?
Ans:- The SELECT statement is used to pull information from a tableSELECT what_to_select FROM which_table WHERE conditions_to_satisfy;
SELECT * FROM table name
SELECT * FROM employee WHERE salary > 2000;
16. INSERT syntax in mysql?
Ans :- mysql> INSERT INTO employee VALUES (‘firstname’,’lastname’,’salary’,’address’);
17. Write update query in mysql?
Ans :- mysql> UPDATE employee SET salary=10000 WHERE salary=’5000′;
18. What is select statement in mysql?
Ans :- The SELECT statement is used to pull information from a table
SELECT what_to_select FROM which_table WHERE conditions_to_satisfy;
SELECT * FROM table name
SELECT * FROM employee WHERE salary > 2000;
19. How to get detail of all Employee whom designation is accountant?
Ans:- SELECT * from Employee where designation = “accountant”.
20. How to get the detail of all employee whom salary will be greater and equal than 10000?
Ans:- SELECT * FROM Employee WHERE salary >=10000
21. Get the detail of all employee whom salary is 10000 and designation is accountant?
Ans:- SELECT * FROM employee WHERE salary =10000 AND designation = “accountant”
22. Get the detail of all employee either they are accountant or they are executive?
Ans:- SELECT * FROM employee WHERE designation = “accountant” OR designation = “executive”
23. In SQL query which precedence have higher AND or OR?
Ans:- Precedence of AND have higher than OR. So, If in query we are using both operator, then it is good idea to use parentheses.
24. Get the detail of all accountant male employee and all executive female employee?
Ans:- SELECT * FROM employee WHERE (designation = “accountant” AND sex = M) OR (designation = “executive” AND sex = F)
25. How to get name and designation of all employee?
Ans:-SELECT name, designation FROM employee;
26. What is DISTINCT in Mysql?
Ans:- DISTINCT clause is used for remove duplicate rows from SELECT statement. That means if we use DISTINCT clause in our SQL query then it will return only distinct(different) values.
Example:- If in a company, employee uses different brand of cars, May be 5 employee will use Maruti and 3 employee will use Foard
If we write our query for find the brand of cars of all employee then it will return 8 rows and if we will use DISTINCT then it will return only two rows.
SELECT DISTINC brandofcar FROM employee;
DISTINCT clause only support single column
27. Write a query which combine row selection with column selection?
Ans:- SELECT name, brandofcar, designation FROM employee WHERE brandofcar = ‘maruti’ or brandofcar = ‘foard’;
28. How to sort a row in SQL?
Ans:- Using ORDER BY clause we can sort a row in SQL. Default, ORDER BY clause sort a row in ascending order.But, By using keyword DESC we can sort descending order.
Default It perform case insensitive fashion. But, using BINERY key word Like ORDER BY BINERY col_namme , we can force case sensitive sorting.
we can sort multiple column and also different column in different direction.
Example:- SELECT * FROM employee ORDER BY name;
SELECT * FROM employee ORDER BY name DESC;
SELECT * FROM employee ORDER BY name designation DESC;
29. Please define mysql function timestampdiff()?
Ans:- timestampdiff() function is used for find the difference of two date or datetime.
Syntax is TIMESTAMPDIFF(unit, datetime_exp1, datetimeexp2);
Here UNIT can be MICROSECOND, SECOND, MINUTE, HOUR, DAY, WEEK, MONTH, QUARTER, YEAR
expression first and expression second is not necessary to same type. It can be different type. Like expression first can be date and expression second can be datetime.
TIMESTAMPDIFF(MONTH, ‘2019-01-05’, ‘2019-04-10 15:16:40’);
30. Write a query which will find how many old all employee in your company?
Ans:- SELECT name, birth, CURDATE(), TIMESTAMPDIFF(MONTH, birth, CURDATE()) AS age FROM employee ORDER BY age;
31. Write a query which will find the birthday of all employee, which is coming next month?
Ans:- SELECT name, birth FROM employee WHERE MONTH(birth) = MONTH(DATE_ADD(CURRDATE(), INTERVAL 1 MONTH));
SELECT name, birth FROM employee WHERE MONTH(birth) = MOD(MONTH(CURRDATE()), 12) +1;
32.Define NULL in Mysql?
Ans:- NULL means ‘missing unknown value’. It is place holder of data which is not exist. It is different in zero or empty.
So, we are using NULL, if data is missing, unknown or not applicable.
NULL is not a data type. If we use the arithmetic operator with NULL then it will always produce a NULL result.
We can use IS NULL or IS NOT NULL compression operator for test NULL.
33. What is MYSQL wildcard?
Ans:-Wildcard is a symbol, which use to replace or represent one or more character. Mysql use underscore ( _ ) and Percentage ( % ) as a wildcard symbol. Underscore ( _ ) represent single character and percentage ( % ) represent more than one character. It is used with Mysql LIKE or NOT LIKE operator for writing Mysql search query.
34.Define Mysql Like Operator?
Ans:- In MySQL query, when we want to search a specific pattern, at that time we use LIKE operator. Two wildcard character, Underscore ( _ ) and percentage ( % ) are often conjunctions with the LIKE operator.
LIKE operator opposite is NOT LIKE operator.
35. How to use regular expression in mysql Pattern matching?
Ans:- Regular expression search data using match complex criteria. Mysql use REGEXP or REGEXP_LIKE operator for regular expression.
SELECT name FROM employee WHERE name REFEXP [a-e].[k];
SELECT department FROM employee WHERE REGEXP_LIKE(name, ‘^n’);
Using BINARY Key word we can make case sensitive.
SELECT department FROM employee WHERE REGEX_LIKE(name, BINERY ‘^n’);
36. Find the employee name beginning with ‘a’?
Ans:- SELECT name FROM employee WHERE name LIKE ‘a%’;
37. Find the employee name ending with ‘an’?
Ans:- SELECT name FROM employee WHERE name LIKE ‘%an’;
38. Find the employee name containing ‘ma’?
Ans:- SELECT name FROM employee WHERE name LIKE ‘%ma%;
39. Find the name of employee which exactly containing six characters?
Ans:- SELECT name FROM employee WHER name LIKE ‘______’
40. What is dot(.) in regular experession?
Ans:- dot (.) represents a single character.
41. How to match any character in regular expression?
Ans:- Using  bracket we can match any character. Like [a-e] It will match five characters a, b, c, d and e [0-9] It will match any digit.
42. What is the meaning of star (*) in a regular expression?
Ans:- (*) star matches zero or more instance of the thing preceding. Like a* matches any number of ‘a’ character.
43.How to force in regular expressing, must match the beginning or end of the value?
Ans:- use ^ for beginning value and use $ for end of the value.
44. write the difference between LIKE operator pattern match and REGEXP operator pattern match?
Ans:- LIKE operator match entire value but REGEXP operator match the beginning or end of the value being tested.
45. How to get the total count of a certain type of data?
Ans:- Using count(*) syntax we can get the total count of certain data. It returns the total count of rows. When we use GROUP BY close, conjunction with count(*). Then we can get a more appropriate result.
46. Find a total number of employee as per sex?
Ans:- SELECT sex COUNT(*) FROM employee GROUP BY sex;
47. How to insert text file data into MYSQL?
Ans:- Using LOAD syntax we can insert text file data into the MySQL database table.
LOAD DATA LOCAL INFILE ‘data.txt’ INTO TABLE ;
48. Write a query which displays 25% increase salary of employees.
Ans:- SELECT name, (salary*0.25+salary) AS salary FROM employee;
49. Write a query which finds the number of character of each employee name?
Ans:- SELECT CHAR_LENGTH(name) FROM employee;
50. Find the average salary from the employee?
Ans:- SELECT AVG(salary) FROM employee;