My SQL Interview Questions

Question: 1 What is a foreign key? Write a query to implement the same in MySQL.

A foreign key is used to connect two tables. A FOREIGN KEY is a field (or assortment of it) in one table that alludes to the PRIMARY KEY in another table. The FOREIGN KEY requirement is utilised to forestall activities that would crush joins between tables.

To assign a foreign key, it is important to mention it while creating the table. It can be assigned by invoking the FOREIGN KEY query. Something like this:

FOREIGN KEY (Any_ID) REFERENCES Table_to_reference(Any_ID)

Question: 2 How does database import/export work in MySQL?

It can be done in two ways. One is to use phpMyAdmin, and the second is to use the command line access of MySQL. The latter can be done by using the command named mysqldump. It goes something like this:

· mysqldump -u username -p databasename > dbsample.sql

To import a database into MySQL, only a sign change is required, with a command of MySQL. The command goes something like this:

· mysql -u username -p databasename < dbsample.sql

Question: 3 What are the different ways to join tables in MySQL?

Join is used to link one or more tables together, with the common column’s values in both tables. Primarily there are four types of joins:

1. Inner Join – Inner join uses a join predicate, which is a condition used to make the join. Here is the syntax:

SELECT something FROM tablename INNER JOIN another table ON condition;

2. Left Join – Left join also requires a join condition. The left join chooses information beginning from the left table. For each entry in the left table, the left compares each entry in the right table. Here is the syntax:

SELECT something FROM tablename LEFT JOIN another table ON condition;

3. Right Join – Opposite to left join and, with one difference in the query, that is the name of join. Here care should be taken about the order of tables. Here is the syntax:

SELECT something FROM tablename LEFT JOIN another table ON condition;

4. Cross Join – Cross join has no join condition. It makes a cartesian of rows of both the tables. Here is the syntax:

SELECT something FROM tablename CROSS JOIN another table;

Note: While dealing with just one table, self-join is also possible. 

It is one of the most dealt with MySQL interview questions. Interviewers do like to see if the candidate understands the basics or not and join one of the core concepts. 

Question: 4 What is a trigger in MySQL?

A trigger is a table-associated database object in MySQL. It is activated when a specified action takes place. 

A trigger can be invoked after or before the event takes place. It can be used on INSERT, DELETE, and UPDATE. It uses the respective syntax to define the triggers. For example, BEFORE INSERT, AFTER DELETE, etc.

Question: 5 What is CHAR and VARCHAR in MySQL?

Both of them define a string. The core difference is that CHAR is a fixed-length while VARCHAR is variable length. For example, if CHAR(5) is defined, then it needs exactly five characters. If VARCHAR(5) is defined, then it can take at most five characters. VARCHAR can be said to have more efficiency in the usage of memory as it can have dynamic memory allocations. 

Question: 6 What is Database White Box Testing?

The Database Whitebox Testing deals with the tables, data model, schema and referential integrity rules. It also deals with the triggers, logical views with database consistency and ACID properties.

QUESTION: 7  What are the different tables present in Mysql?

There are mainly five types of tables present in MySQL. Out of all these database engines, the default database engine used in MySQL is MyISAM. Refer below to know the five types of tables:

  1. MyISAM
  2. Heap
  3. Merge
  4. INNO DB
  5. ISAM

QUESTION: 8  What is the difference between the NVL function, IFNULL function, and the ISNULL function?

The NVL function, IFNULL function, and the ISNULL function all of them are used to replace the NULL value with another value. The ORACLE users use the NVL function, MySQL users use the IFNULL function and the SQL servers use the ISNULL function
For example, let us say we have a column(column_3) which has NULL values.
So, if you run the below statement, the output you would get is a NULL value.

1SELECT column_1 * (column_2 + column_3) FROM Example_Table

Now, to overcome this, you can use the above three functions as follows:

1
2
3
SELECT column_1 * (column_2 + NVL(column_3,0)) FROM Example_Table
SELECT column_1 *(column_2 + IFNULL(column_3,0)) FROM Example_Table
SELECT column_1 * (column_2 + ISNULL(column_3,0)) FROM Example_Table

QUESTION: 9  Can you tell the  Difference Between Mysql_fetch_object And Mysql_fetch_array?

Both of them are similar but vary with a single difference. Mysql_fetch_object return as object and Mysql_fetch_array returns an array. This means that you cannot access the data by their offsets but can only access through its fields names.

QUESTION: 10 Can you tell the reasons for selecting Lamp(Linux, Apache, MySQL, PHP) instead of any other combination of software programs, servers, and operating system?

The reason behind selecting Lamp stack is very simple. Linux, Apache, MySQL, PHP are open source software. The security of the Linux operating system is much more than Windows.  The Apache server is a better server than others in the perspective of functionalities and security. MySQL is one of the most popular open source databases is used with PHP to perform various functionalities.

QUESTION: 11  What Storage Engines do you use in MySQL?

Storage engines used to be called table types.
Data in MySQL is stored in files (or memory) using a variety of different techniques. Each of these techniques employs different storage mechanisms, indexing facilities, locking levels and ultimately provides a range of different functions and capabilities. By choosing a different technique you can gain additional speed or functionality benefits that will improve the overall functionality of your application. 

QUESTION: 12 Consider a scenario where you have two to three tables with thousand tuples in each of them. Now, if you have to perform a JOIN operation between them will you choose to perform filtering of rows or transforming of rows first. 

The answer to this question is quite logical. If you have three tables with thousands of tuples in each of them, then you are first supposed to filter the rows in those tables and then transform the table. This would be beneficiary as if you transform the table, then the number of columns may increase reducing the performance. Due to such performance issues, a lot of memory will be used and the output will appear on your screen after quite a long wait of time.

QUESTION: 13 Can you tell which of the following WHERE clauses is faster?
WHERE col * 4 < 16
WHERE col < 16 / 4

If we compare both the statements, then the second WHERE clause would be comparatively faster than the first one. That is because, for the first statement, MYSQL would retrieve the value of ‘col’ for each and every row, multiplied by four. After that, it would compare the result to 16. Also, in the first case no Index can be used, and hence it makes it further slow.

QUESTION: 14 Consider a scenario where you have to send an email to a client from the SQL database. How do you think you can achieve this task?

To send an email from the database, you can use the stored procedures. Follow the below procedure to send the emails:

  • Configure your SQL Server Mail account.
  • Enable the database mail.
  • Write a script to send an email. Refer below for the script.
1
2
3
4
5
6
USE [YourDB]
EXEC msdb.dbo.sp_send_dbmail
@recipients = 'abc@example.com; def@example.com;xyz@example.com’
@body = ' Sample Body Text',
@subject = 'Example Email' ;
GO

QUESTION: 15 Explain a few pros and cons of MySQL?

Pros:

  1. The MySQL products are more stable and solid compared to its competitors
  2. It is easier to use from the user experience perspective
  3. It is open-source and thus is free to use.
  4. There is large and extensive community support exists for MySQL

Cons:

  1. There is sometimes a bit performance scaling issues exists in the case of MySQL
  2. Sometimes development progress gets lagged due to lacking of the community development approach.

QUESTION: 16  Explain MySQL transaction properties?

This is the most asked MySQL Interview Questions asked in an interview. The transactions mainly consist of four properties.

  1. Atomicity: The property is mainly used to control all the operations and ensure all the transactions get completed successfully.
  2. Consistency: This ensures the database changes according to the successfully committed transactions.
  3. Isolation: This is basically used to control the independent transactions.
  4. Durability: This is used to control and measure the effect and presence of committed transactions in case of system or database failure.

QUESTION: 17 Explain the differences between delete, drop, and truncate?

  • Delete: Delete is a DML statement and it can be implemented using ‘where’ clause and can also be rolled back. Syntax: DELETE FROM table_name WHERE column_name = column_value;
  • Drop: Drop is a DDL statement and it can’t be rolled back. By using this, the entire table and all its constraints and privileges will be removed. Syntax: DROP TABLE table_name;
  • Truncate: Truncate is a DDL statement and it is used to remove all the rows from a table but its structures, constraints, and indexes will remain as it is. Syntax: TRUNCATE TABLE table_name;

QUESTION:18  How many TRIGGERS are allowed in MySql table?

SIX triggers are allowed in MySql table. They are as follows:

  • BEFORE INSERT
  • AFTER INSERT
  • BEFORE UPDATE
  • AFTER UPDATE
  • BEFORE DELETE and
  • AFTER DELETE

QUESTION: 19  Explain Access Control Lists.

An ACL (Access Control List) is a list of permissions that is associated with an object. This list is the basis for MySQL server’s security model and it helps in troubleshooting problems like users not being able to connect.

MySQL keeps the ACLs (also called grant tables) cached in memory. When a user tries to authenticate or run a command, MySQL checks the authentication information and permissions against the ACLs, in a predetermined order.

QUESTION: 20 What is the difference between BLOB AND TEXT?

A BLOB is a binary large object that can hold a variable amount of data. There are four types of BLOB –

  • TINYBLOB
  • BLOB
  • MEDIUMBLOB and
  • LONGBLOB

They all differ only in the maximum length of the values they can hold.

A TEXT is a case-insensitive BLOB. The four TEXT types

  • TINYTEXT
  • TEXT
  • MEDIUMTEXT and
  • LONGTEXT

They all correspond to the four BLOB types and have the same maximum lengths and storage requirements.

The only difference between BLOB and TEXT types is that sorting and comparison is performed in case-sensitive for BLOB values and case-insensitive for TEXT values.

Lesson Content
0% Complete 0/1 Steps