MySQL Technical Interview Question & Answers

1.What's MySQL ? Ans. MySQL (pronounced "my ess cue el") is an open source relational database management system (RDBMS) t...

1.What's MySQL ?
Ans.MySQL (pronounced "my ess cue el") is an open source relational database management system (RDBMS) that uses Structured Query Language (SQL), the most popular language for adding, accessing, and processing data in a database. Because it is open source, anyone can download MySQL and tailor it to their needs in accordance with the general public license. MySQL is noted mainly for its speed, reliability, and flexibility

2.Why use the MySQL Database Server?
Ans.The MySQL Database Server is very fast, reliable, and easy to use. it is possible for anyone to use and modify the software. Anybody can download the MySQL software from the Internet and use it without paying anything.

3.What is the technical features of MySQL Server?
Ans.The MySQL Database Software is a client/server system that consists of a multi-threaded SQL server that supports different backends, several different client programs and libraries, administrative tools, and a wide range of application programming interfaces (APIs).

4.What is DDL, DML and DCL ? 
Ans.If you look at the large variety of SQL commands, they can be divided into three large subgroups. Data Definition Language deals with database schemas and descriptions of how the data should reside in the database, therefore language statements like CREATE TABLE or ALTER TABLE belong to DDL. DML deals with data manipulation, and therefore includes most common SQL statements such SELECT, INSERT, etc. Data Control Language includes commands such as GRANT, and mostly concerns with rights, permissions and other controls of the database system

5.What are the column comparisons operators?
Ans.The = , ‹›, ‹=, ‹, ›=, ›,‹‹,››, ‹=›, AND, OR, or LIKE operators may be used in column comparisons to the left of the FROM in SELECT statements.

6.How do you get the number of rows affected by query? 
Ans.SELECT COUNT (user_id) FROM users would only return the number of user_id’s.

7.How do you return the a hundred books starting from 30th?
Ans.SELECT book_title FROM books LIMIT 35, 100;

8.How would you write a query to select all teams that won either 2, 4, 6 or 8 games?
Ans.SELECT team_name FROM teams WHERE team_won IN (2, 4, 6, 8).

9.How to do login in mysql with unix shell
Ans.By below method if password is pass and user name is root
# [mysql dir]/bin/mysql -h hostname -u root -p pass

10.How to list or view all databases from the mysql server
Ans.mysql> show databases;

11.What is the default port for MySQL Server?
Ans.The default port is 3306.

12.How would you select all the users, whose phone number is null? 
Ans.SELECT user_name FROM users WHERE ISNULL(user_phonenumber);

13.How do you find out which auto increment was assigned on the last insert?
Ans.SELECT LAST_INSERT_ID() will return the last value assigned by the auto_increment function. Note that you don’t have to specify the table name.

14.What are ENUMs used for in MySQL?
Ans.You can limit the possible values that go into the table. 
CREATE TABLE months (month ENUM ’January’, ’February’, ’March’,); INSERT months VALUES (’April’).

15.How to see table's field formats or description of table
Ans.mysql> describe tablename;

16.What is the difference between CHAR_LENGTH and LENGTH?
Ans.The first is, naturally, the character count. The second is byte count. For the Latin characters the numbers are the same, but they’re not the same for Unicode and other encodings.

17.How can you see all indexes defined for a table? 
Ans.SHOW INDEX FROM techpreparation_questions;

18.How are ENUMs and SETs represented internally?
Ans.As unique integers representing the powers of two, due to storage optimizations.

19.How to delete a database from mysql server.
Ans.mysql> drop database databasename;

20.If the value in the column is repeatable, how do you find out the unique values?
Ans.SELECT DISTINCT user_firstname FROM users;

21.How you will Show all data from a table
Ans.mysql> SELECT * FROM tablename;

22.How do you get the current version of mysql?

23.Can you save your connection settings to a conf file? 
Ans.Yes, and name it ~/.my.conf. You might want to change the permissions on the file to 600, so that it’s not readable by others

24.How do you change a password for an existing user via mysqladmin?
Ans.mysqladmin -u root -p password "newpassword"

25.How to Return total number of rows.
Ans.mysql> SELECT COUNT(*) FROM tablename;

26.Differentiate the LIKE and REGEXP operators?
Ans.SELECT * FROM pet WHERE name REGEXP "^b";
SELECT * FROM pet WHERE name LIKE "%b";

27.What does myisamchk do? 
Ans.It compressed the MyISAM tables, which reduces their disk usage.

28.How to Delete a column and Add a new column to database
Ans.mysql> alter table [table name] drop column [column name];
mysql> alter table [table name] add column [new column name] varchar (20);

29.What are the String types are available for a column?
Ans.The string types are CHAR, VARCHAR, BLOB, TEXT, ENUM, and SET.

30.What is the difference between mysql_fetch_array and mysql_fetch_object?
Ans.mysql_fetch_array(): - returns a result row as a associated array, regular array from database.
mysql_fetch_object: - returns a result row as object from database.

31.What does this query mean: SELECT user_name, user_isp FROM users LEFT JOIN isps USING (user_id)?
Ans.It’s equivalent to saying SELECT user_name, user_isp FROM users LEFT JOIN isps WHERE users.user_id=isps.user_id

32.How to Update database permissions/privilages.
Ans.mysql> flush privileges;

33.Change column name and Make a unique column so we get no dupes.
Ans.mysql> alter table [table name] change [old column name] [new column name] varchar (50);
mysql> alter table [table name] add unique ([column name]);

34.How to dump one database for backup.
Ans.# [mysql dir]/bin/mysqldump -u username -ppassword --databases databasename >/tmp/databasename.sql

Ans.On initialization places a zero in that column, on future updates puts the current value of the timestamp in.

36.How many drivers in Mysql?
Ans.There are eleven drivers in MYSQL .Six of them from MySQL AB and five by MYSQL Communities.They are
  • PHP Driver
  • ODBC Driver
  • JDBC Driver
  • ado.net5.mxj
37.How do you run batch mode in mysql?
Ans.mysql < batch-file >;
mysql < batch-file > mysql.out

38.Restore database (or database table) from backup.
Ans.# [mysql dir]/bin/mysql -u username -ppassword databasename < /tmp/databasename.sql

39.Where MyISAM table is stored?
Ans.Each MyISAM table is stored on disk in three files.
  • The ‘.frm’ file stores the table definition.
  • The data file has a ‘.MYD’ (MYData) extension.
  • The index file has a ‘.MYI’ (MYIndex) extension
40.Define Primary key?
Ans.MYSQL allows only one primary key. A primary key is used to uniquely identify each row in a table. It can either be part of the actual record itself.A primary key can consist of one or more fields on a table. When multiple fields are used as a primary key, they are called a composite key.

41.How will you export tables as an XML file in MySQL?
Ans.MYSQL’s query browser has a provision called “Export Result Set” which allows the tables to be exported as XML.

42.How do you control the max size of a HEAP table?
Ans.Maximum size of Heap table can be controlled using MySQL config variable called max_heap_table_size.

43.What are the advantages of MySQL in comparison to Oracle?
  • MySQL is open source software available at zero cost. 
  • It is portable 
  • GUI with command prompt.
  • Administration is supported by MySQL Query Browser

44.What is BLOB?
Ans.BLOB stands for binary large object.It that can hold a variable amount of data. 

45.What is the difference between BLOB and TEXT?
Ans.In BLOB sorting and comparison is performed in case-sensitive for BLOB values 
In TEXT types sorting and comparison is performed case-insensitive.


Interview Questions 5641746438576651260

Post a Comment

Follow Us

Hot in week



Side Ads