Mysql Interview Question and Answers

1) What is MySQL?

MySQL is a multithreaded, multi-user SQL database management system which has more than 11 million installations. This is the world’s second most popular and widely used open source database.

2)Find max and second max salary for a employee table MySQL 

SELECT Salary FROM Employee ORDER BY Salary DESC LIMIT n-1,1

3) Compare MySQL Vs. SQL Server ?

Criteria MySQL SQL Server
Targeted towards Internet servers & Open Source software Corporate & Enterprise market
Functionality Speed Administration, Graphical data modelling
Works with Assumes internet access Administration, Graphical data modelling

4) What are the common MySQL functions?

Common MySQL functions are as follows: • NOWO – function for returning current date and time as single value. • CURRDATEO – function for returning the current date or time. • CONCAT (X, Y) – function to concatenates two string values creating single string output. • DATEDIFF (X, Y) – function to determine difference two dates.

5)What is the difference between CHAR and VARCHAR?

When the table is created, CHAR is used to define the fixed length of the table and columns. The length value could be in the range of 1-255. VARCHAR command is given to adjust the column and table length as required.

6) In which language MySQL is written?

MySQL is written in C and C++ and its SQL parser is written in yacc.

7)What are the technical specification of MySQL?

MySQL has the following technical specifications –

  • Flexible structure
  • High performance
  • Manageable and easy to use
  • Replication and high availability
  • Security and storage management

 8)What is the difference between MySQL and SQL? 

SQL stands for Structured Query Language. It’s a standard language for accessing and manipulating databases. MySQL is a database management system, like SQL Server, Oracle, Informix, Postgres, etc. MySQL is a RDMS (Relational Database Management System).

When considering a utility for data management the two most popular choices are MySQL and SQL Server. Both are efficient at keeping your data organized and readily available through a user interface. Both technologies have the concept of schema (that is table storage) for data storage.

 9) What is the difference between MySQL and MS SQL? 

MySQL MS SQL
MySQL is open source. MS SQL is a closed source.
MySQL can be used without paying any amount. MS SQL is not an open source database system. The developer can use MS SQL only after procuring a license. For simple development, the developer can use the free version of SQL Server. But for commercial purpose, the developer has to purchase the SQL Server Standard Edition or any higher editions.
MySQL is the database system that offers different variations like derived engine based on Sybase, Berkeley DB, Heap, InnoDB and many others. MS SQL is the database system that is limited for usage within the derived engine of Sybase.
MySQL requires very less disk space. MS SQL requires more disk space.
MySQL is a simple language that can be easily used by beginners. MS SQL is a complex language that requires more understanding to work with.
MySQL offers excellent performance when compared to any other databases due to the presence of MyISAM. Performance of MS SQL is less when compared to MS SQL.
MySQL does not support certain foreign key concepts and relational functions available in MS SQL. MS SQL has extensive foreign key features and rich relational features.
Though usage of MyISAM in MySQL is advantageous in terms of performance, MyISAM creates disturbance in recovery. MS SQL has efficient recovery mechanism.
MySQL can be installed in Linux and Unix. MS SQL doesn’t work in Linux and Unix.
MySQL does not contain management tools based on GUI. MS SQL provides management tools based on GUI.

10)What is the difference between database and table?

There is a major difference between a database and a table. The differences are as follows:

  • Tables are a way to represent the division of data in a database while, database is a collection of tables and data.
  • Tables are used to group the data in relation with each other and create a dataset. This dataset will be used in the database. The data which are stored in the table in any form is a part of the database, but the reverse is not true.

 11)Why do we use MySQL database server?

The MySQL database server is very fast, reliable and easy to use. You can easily use and modify the software. MySQL software can be downloaded free of cost from the internet.

 12)What are the different tables present in MySQL?

There are many tables that remain present by default. But, MyISAM is the default database engine used in MySQL. There are five types of tables that are present:

  • MyISAM
  • Heap
  • Merge
  • INNO DB
  • ISAM

 13)What is the difference between CHAR and VARCHAR?

A list of differences between CHAR and VARCHAR:

  • CHAR and VARCHAR types are different in storage and retrieval.
  • CHAR column length is fixed to the length that is declared while creating table. The length value ranges from 1 and 255.
  • When CHAR values are stored then they are right padded using spaces to specific length. Trailing spaces are removed when CHAR values are retrieved.

 14)What is the difference between TRUNCATE and DELETE in MySQL?

The DELETE command is used to delete data from a table. It only deletes the rows of data from the table while, truncate is very dangerous command and should be used carefully because it deletes every row permanently from a table.

 15) How many Triggers are possible in MySQL?

There are only six Triggers allowed to use in MySQL database.

  1. Before Insert
  2. After Insert
  3. Before Update
  4. After Update
  5. Before Delete
  6. After Delete

 16)What is heap table?

Tables that are present in memory is known as HEAP tables. When you create a heap table in MySQL, you should need to specify the TYPE as HEAP. These tables are commonly known as memory tables. They are used for high speed storage on temporary basis. They do not allow BLOB or TEXT fields.

 17)What is BLOB and TEXT in MySQL?

BLOB is an acronym stands for binary large object. It is used to hold a variable amount of data.

There are four types of BLOB.

  1. TINYBLOB
  2. BLOB
  3. MEDIUMBLOB
  4. LONGBLOB

The differences among all these are the maximum length of values they can hold.

TEXT is case-insensitive BLOB. TEXT values are non-binary strings (character string). They have a character set and values are stored and compared based on the collation of the character set.

There are four types of TEXT.

  1. TINYTEXT
  2. TEXT
  3. MEDIUMTEXT
  4. LONGTEXT

 18)What is a trigger in MySQL?

A trigger is a set of codes that executes in response to some events.

 19)What is the difference between heap table and temporary table?

Heap tables:

Heap tables are found in memory. They are used for high speed storage on temporary basis. They do not allow BLOB or TEXT fields.

Heap tables do not support AUTO_INCREMENT.

Indexes should be NOT NULL.

Temporary tables:

The temporary tables are used to keep the temporary data. Sometimes it is very useful in cases to keep temporary data. Temporary table is deleted after current client session terminates.

Main differences:

The heap tables are shared among clients while temporary tables are not shared.

Heap tables are just another storage engine, while for temporary tables you need a special privilege (create temporary table).[/vc_column_text][/vc_tta_section][vc_tta_section title=”20)What is the difference between FLOAT and DOUBLE?” tab_id=”1491490591139-4dc5c7a0-f2ba”][vc_column_text]FLOAT stores floating point numbers with accuracy up to 8 places and allocates 4 bytes, on the other hand DOUBLE stores floating point numbers with accuracy up to 18 places and allocates 8 bytes.[/vc_column_text][/vc_tta_section][vc_tta_section title=”21)What are the advantages of MySQL in comparison to Oracle?” tab_id=”1491490644488-0d6295df-ff57″][vc_column_text]

  1. MySQL is a free, fast, reliable, open source relational database while Oracle is expensive, although they have provided Oracle free edition to attract MySQL users.
  2. MySQL uses only just under 1 MB of RAM on your laptop while Oracle 9i installation uses 128 MB.
  3. MySQL is great for database enabled websites while Oracle is made for enterprises.
  4. MySQL is portable.

[/vc_column_text][/vc_tta_section][vc_tta_section title=”22)What are the disadvantages of MySQL?” tab_id=”1491490687279-d8fba403-3d78″][vc_column_text]

  1. MySQL is not so efficient for large scale databases.
  2. It does not support COMMIT and STORED PROCEDURES functions version less than 5.0.
  3. Transactions are not handled very efficiently.

[/vc_column_text][/vc_tta_section][vc_tta_section title=”23)What is the difference between CHAR and VARCHAR?” tab_id=”1491490744070-83055a98-4330″][vc_column_text]

  1. CHAR and VARCHAR are differ in storage and retrieval.
  2. CHAR column length is fixed while VARCHAR length is variable.
  3. The maximum no. of character CHAR data type can hold is 255 character while VARCHAR can hold up to 4000 character.
  4. CHAR is 50% faster than VARCHAR.
  5. CHAR uses static memory allocation while VARCHAR uses dynamic memory allocation.

[/vc_column_text][/vc_tta_section][vc_tta_section title=”24)What is the difference between MySQL_connect and MySQL_pconnect?” tab_id=”1491491139766-238b29ba-b647″][vc_column_text]Mysql_connect:

  1. It opens a new connection to the database.
  2. Every time you need to open and close database connection, depending on the request.
  3. Opens page every time when it loaded.

Mysql_pconnect:

  1. In Mysql_pconnect, “p” stands for persistent connection so it opens the persistent connection.
  2. the database connection can not be closed.
  3. it is more useful if your site has more traffic because there is no need to open and close connection frequently and every time when page is loaded.

[/vc_column_text][/vc_tta_section][vc_tta_section title=”25)What does “ i_am_a_dummy flag“ do in MySQL?” tab_id=”1491491176582-2e48256b-9df0″][vc_column_text]The ” i_am_a_dummy flag” enables MySQL engine to refuse any UPDATE or DELETE statement to execute if the WHERE clause is not present.[/vc_column_text][/vc_tta_section][vc_tta_section title=”25)How to get the current date in MySQL?” tab_id=”1491491213203-2331033c-ec06″][vc_column_text]To get current date, use the following syntax:

1
SELECT CURRENT_DATE();

[/vc_column_text][/vc_tta_section][vc_tta_section title=”26)What are the security alerts while using MySQL?” tab_id=”1491491259120-9dfdf176-ebcd”][vc_column_text]Install antivirus and configure the operating system’s firewall.

Never use the MySQL Server as the UNIX root user.

Change root username and password

Restrict or disable remote access.[/vc_column_text][/vc_tta_section][vc_tta_section title=”27)How to change a password for an existing user via Mysqladmin?” tab_id=”1491491365013-92364b5f-5837″][vc_column_text]Mysqladmin -u root -p password “newpassword”.[/vc_column_text][/vc_tta_section][vc_tta_section title=”28)what is main difference between mysql and mongodb?” tab_id=”1491569909630-6906d31f-aed0″][vc_column_text]The comparison between MongoDB, the poster child of NoSQL, and MySQL has been raging for a while now. It is important that you know the difference between the two as this will assist you in making an informed decision.

The Major Differences between MongoDB and MySQL
1. There is a difference in the representation of data in the two databases. In MongoDB, data represents in a collection of JSON documents while in MySQL, data is in tables and rows. JSON documents can compare to associative arrays when using PHP and directory objects when using Python.
2. When it comes to querying, you have to put a string in the query language that the DB system parses. The query language is called Structured Query Language, or SQL,from where MySQL gets its name. This exposes your DB susceptible to SQL injectionattacks. On the other hand, MongoDB’s querying is object-oriented, which means you pass MongoDB a document explaining what you are querying. There is no parsing whatsoever, which will take some time getting used to if you already use SQL.
3. One of the greatest benefits of relational databases like MySQL is the JOIN operation. The operation allows for the querying across several tables. Although MongoDB doesn’t support joints, it supports multi-dimensional data types like other documents and arrays.
4. With MySQL, you can have one document inside another (embedding). You would have to create one table for comments and another for posts if you are using MySQL to create a blog. In MongoDB, you will only have one array of comments and one collection of posts within a post.
5. MySQL supports atomic transactions. You can have several operations within a transaction and you can roll back as if you have a single operation. There is no support for transactions in MongoDB and the single operation is atomic.
6. One of the best things about MongoDB is that you are not responsible for defining the schema. All you need to do is drop in documents. Any 2 documents in a collection need not be in the same field. You have to define the tables and columns before storage in MySQL. All rows in a table share the same columns.
7. MongoDB’s performance is better than that of MySQL and other relational DBs. This is because MongoDB sacrifices JOINS and other things and has excellent performance analysis tools. Note that you still have to index the data and the data in most applications is not enough for them to see a difference. MySQL is criticized for poor performance, especially in ORM application. However, you are unlikely to have an issue if you do proper data indexing and you are using a database wrapper.
8. One advantage of MySQL over NoSQL like MongoDB is that the community in MySQL is much better than NoSQL. This is mostly because NoSQL is relatively new while MySQL has been around for several years.
9. There are no reporting tools with MongoDB, meaning performance testing and analysis is not always possible. With MySQL, you can get several reporting tools that help you rove the validity of your applications.
10. RDBSs function on a paradigm called ACID, which is an acronym for (Atomicity, Consistency, Isolation, and Durability). This is not present in MongoDB database.
11. MongoDB has a Map Reduce feature that allows for easier scalability. This means you can get the full functionality of MongoDB database even if you are using low-cost hardware.
12. You do not have to come up with a detailed DB model with MongoDB because of is non-relational. A DB architect can quickly create a DB without a fine-grained DB model, thereby saving on development time and cost.
MongoDB: A Brief Overview
Several organizations that have to deal with big data are increasingly using MongoDB. MongoDB is a good tool in handling database comprising unstructureddata.
The NoSQL idea saw first creation in 1998. Carlo Strozzi was the first person to create the DB management system that was characterized by stream-operator paradigm for accessing data, instead of the traditional SQL syntax. The steam- operator paradigm resembles a mathematical operation. However, NoSQLs like MongoDB still use RDBMS model.
MongoDB is one of the most popular NoSQL solutions. Its main purpose is to assist in data storage and the solution can indeed store documents on the World Wide Web. MongoDB was first built in 2007. One of its most outstanding features is data storage in a dynamic schema, as opposed to the tabular DB characteristic of SQL.
The MongoDB data bases on the JSON (Java Script Object Notation) format. JSON allows for the transfer of data between web applications and servers using a human readable format. Before JSON, XML was used for this. JSON is defined in MongoDB’s BSON (Binary JSON). The binary format of BSON provides reliability and greater efficiency when it comes to speed and storage space.
Clients who want a document-oriented DB mostly use MongoDB. The most prominent of these is Craigslist with over 2 billion records in MongoDB storage. Other prominent users are Shutterfly with over 18 billion photos, Foursquare, New York Times and Forbes, among many others.
MySQL: A Brief Overview
MySQL is synonymous with RDBMS. Its first release was in 1995 under the GNU GPL License. MySQL gained popularity in the web application world because of its LAMP and other software stack (all that have the AMP acronym). LAMP (Linux-Apache-MySQL-PHP) is a software stack that you will find in many web applications.
Oracle, the current owners of MySQL after acquiring Sun Microsystems, sells MySQL to private customers and Oracle DB for big corporate customers.
The major drawback of SQL in today’s world is that most of the data today is non-structured data, meaning the traditional tabular model of SQL is ineffective. According to Merril Lynch’s 1998 estimation, between 80% and 90% of a company’s data is unstructured.[/vc_column_text][/vc_tta_section][vc_tta_section title=”29)how to delete rows with null values in mysql” tab_id=”1491623909250-550f2921-b04e”][vc_column_text]

[highlight txtcolor="#000000"]DELETE FROM table_name WHERE some_column IS NULL;[/highlight]

[/vc_column_text][/vc_tta_section][vc_tta_section title=”30)Difference between a stored procedure and function?” tab_id=”1491729060317-b29c3ea8-db1f”][vc_column_text]Benefits of Stored Procedures

  • Precompiled execution: SQL Server compiles each Stored Procedure once and then re utilizes the execution plan. This results in tremendous performance boosts when Stored Procedures are called repeatedly.
  • Reduced client/server traffic: If network bandwidth is a concern in your environment then you’ll be happy to learn that Stored Procedures can reduce long SQL queries to a single line that is transmitted over the wire.
  • Efficient reuse of code and programming abstraction: Stored Procedures can be used by multiple users and client programs. If you utilize them in a planned manner then you’ll find the development cycle requires less time.
  • Enhanced security controls: You can grant users permission to execute a Stored Procedure independently of underlying table permissions.

Benefits of User Defined Functions

  • They allow modular programming: You can create the function once, store it in the database, and call it any number of times in your program. User Defined Functions can be modified independently of the program source code.
  • They allow faster execution: Similar to Stored Procedures, Transact-SQL User Defined Functions reduce the compilation cost of Transact-SQL code by caching the plans and reusing them for repeated executions. This means the user-defined function does not need to be reparsed and reoptimized with each use resulting in much faster execution times. CLR functions offer significant performance advantage over Transact-SQL functions for computational tasks, string manipulation, and business logic. Transact-SQL functions are better suited for data-access intensive logic.
  • They can reduce network traffic: An operation that filters data based on some complex constraint that cannot be expressed in a single scalar expression can be expressed as a function. The function can then invoked in the WHERE clause to reduce the number or rows sent to the client.
Sr.No. User Defined Function Stored Procedure
1  Function must return a value. Stored Procedure may or not return values.
2 Will allow only Select statements, it will not allow us to use DML statements. Can have select statements as well as DML statements such as insert, update, delete and so on
3  It will allow only input parameters, doesn’t support output parameters. It can have both input and output parameters.
4 It will not allow us to use try-catch blocks. For exception handling we can use try catch blocks.
5 Transactions are not allowed within functions. Can use transactions within Stored Procedures.
6 We can use only table variables, it will not allow using temporary tables. Can use both table variables as well as temporary table in it.
7 Stored Procedures can’t be called from a function. Stored Procedures can call functions.
8  Functions can be called from a select statement. Procedures can’t be called from Select/Where/Having and so on statements. Execute/Exec statement can be used to call/execute Stored Procedure.
9 A UDF can be used in join clause as a result set. Procedures can’t be used in Join clause

[/vc_column_text][/vc_tta_section][vc_tta_section title=”31)How to add +1 to already existing values?” tab_id=”1491828983278-ff684d7e-77af”][vc_column_text][highlight txtcolor=”#000000″]UPDATE TABLENAME SET DISPLAYORDER = DISPLAYORDER +1 WHERE CONDITION [/highlight][/vc_column_text][/vc_tta_section][vc_tta_section title=”32)how many type SQL Views?” tab_id=”1491928967317-36ae6c5f-c9f7″][vc_column_text]Standard Views
Combining data from one or more tables through a standard view lets you satisfy most of the benefits of using views. These include focusing on specific data and simplifying data manipulation. These benefits are described in more detail in Scenarios for Using Views.
Indexed Views
An indexed view is a view that has been materialized. This means it has been computed and stored. You index a view by creating a unique clustered index on it. Indexed views dramatically improve the performance of some types of queries. Indexed views work best for queries that aggregate many rows. They are not well-suited for underlying data sets that are frequently updated. For more information, see Designing Indexed Views.
Partitioned Views
A partitioned view joins horizontally partitioned data from a set of member tables across one or more servers. This makes the data appear as if from one table. A view that joins member tables on the same instance of SQL Server is a local partitioned view.[/vc_column_text][/vc_tta_section][vc_tta_section title=”33)How to swap entires of two columns with just one query?” tab_id=”1492021594788-e36058bf-6b3b”][vc_column_text][highlight txtcolor=”#000000″] UPDATE test SET first_name=@tmp:=first_name, first_name=last_name, last_name=@tmp;[/highlight][/vc_column_text][/vc_tta_section][vc_tta_section title=”34)What are the main differences between InnoDB and MyISAM?” tab_id=”1492092935698-a2f792db-4725″][vc_column_text]

InnoDB MyISAM
It is not support FULLTEXT index MyISAM supports FULLTEXT indexes and compressed read-only storage.
InnoDB is not save data as the table level so the implementation of select count (*) from table in InnoDB will again scan the entire table to calculate the number of rows. MyISAM save data as table level so it simply read out the saved row number.
InnoDB table have AUTO_INCREMENT field as the part of index. MyISAM can set up join index and other fields together.
You can not re-establish the deleted table using InnoDB.
LOAD TABLE FROM MASTER operations does not work on InnoDB so the solution is first import data into a MyISAM table and then import the data into InnoDB table but for the extra use InnoDB characteristics such as foreign keys will not apply.
InnoDB support row level locking so operations will not lock the whole table but InnoDB tables are not absolute for row level locking/ MyISAM support table level locking so it will lock whole table when perform the the implementation of a SQL statement.
InnoDB support transaction processing and foreign keys MyISAM does not
InnoDB have index and data are closely tied but do not use compression because of it InnoDB is huge. MyISAM indexes and data are separate and the index is compressed also the memory usage on the corresponding increase a lot.

[/vc_column_text][/vc_tta_section][vc_tta_section title=”35)how to delete null values from table in mysql?” tab_id=”1492093954402-c56541ed-43cb”][vc_column_text][highlight txtcolor=”#000000″]DELETE FROM table_name WHERE username IS NULL;[/highlight]
and for removing all those rows in select statement
[highlight txtcolor=”#000000″]SELECT * FROM table_name WHERE username IS NOT NULL;[/highlight][/vc_column_text][/vc_tta_section][vc_tta_section title=”36)What are HEAP tables in MySQL?” tab_id=”1492100683786-a5c3b33a-524b”][vc_column_text]HEAP tables are in-memory. They are usually used for high-speed temporary storage. No TEXT or BLOB fields are allowed within HEAP tables. You can only use the comparison operators = and <=>. HEAP tables do not support AUTO_INCREMENT. Indexes must be NOT NULL.[/vc_column_text][/vc_tta_section][vc_tta_section title=”37)What is the difference between UNION and UNION ALL?” tab_id=”1492100875696-7d2ba26d-1105″][vc_column_text]UNION removes duplicate records (where all columns in the results are the same), UNION ALL does not.

There is a performance hit when using UNION vs UNION ALL, since the database server must do additional work to remove the duplicate rows, but usually you do not want the duplicates (especially when developing reports).

UNION Example:

SELECT 'foo' AS bar UNION SELECT 'foo' AS bar

Result:

+-----+
| bar |
+-----+
| foo |
+-----+
1 row in set (0.00 sec)

UNION ALL example:

SELECT 'foo' AS bar UNION ALL SELECT 'foo' AS bar

Result:

+-----+
| bar |
+-----+
| foo |
| foo |
+-----+
2 rows in set (0.00 sec)

[/vc_column_text][/vc_tta_section][vc_tta_section title=”38)what is MySQL Triggers?” tab_id=”1492695923111-a5f54b1c-8774″][vc_column_text]A trigger is a set of actions that are run automatically when a specified change operation (SQL INSERT, UPDATE, or DELETE statement) is performed on a specified table. Triggers are useful for tasks such as enforcing business rules, validating input data, and keeping an audit trail.

More Information Here…[/vc_column_text][/vc_tta_section][vc_tta_section title=”39) Mysqli select duplicate records only?” tab_id=”1492701528016-8a784cb6-ffcc”][vc_column_text][highlight txtcolor=”#000000″]SELECT first_name FROM test GROUP BY first_name HAVING COUNT(*) > 1;[/highlight][/vc_column_text][/vc_tta_section][/vc_tta_accordion][/vc_column][/vc_row]

Leave a Reply

Your email address will not be published. Required fields are marked *