My SQL Interview Questions and Answers
Question - 91 : - How does database import/export work in MySQL?
Answer - 91 : -
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 - 92 : - How can we delete a column or a row in MySQL?
Answer - 92 : -
Now dropping a column can be simply done by using the ALTER TABLE command and then using the DROP command. It goes something like this:
ALTER TABLE table_name DROP column name;
To drop a row, first, an identification for the row is required. Once that is handy, use the DELETE command in conjunction with the conditional WHERE command. Something like this:
DELETE FROM cars WHERE carID = 3;
Question - 93 : - What are the different ways to join tables in MySQL?
Answer - 93 : -
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 - 94 : - Can a primary key be dropped in MySQL? If yes, how?
Answer - 94 : -
Yes, it is possible to drop the primary key from a table. The command to use is again, the ALTER TABLE followed by DROP. It goes like this:
ALTER TABLE table_name DROP PRIMARY KEY;
Question - 95 : - How to save images in MySQL?
Answer - 95 : -
Images can be stored in the MySQL database by converting them to BLOBS. But it is not preferred due to the large overhead it creates. Plus, it puts unnecessary load on the RAM while loading the entire database. It is hence preferred to store the paths in the database and store the images on disk.
Question - 96 : - Can a query be written in any case in MySQL?
Answer - 96 : -
This MySQL interview question often confuses people who are just getting started with MySQL. Although most of the time, the queries are written in capital or some in small letters, there is no such case sensitivity to MySQL queries.
For example, both create table tablename and CREATE TABLE tablename, works fine.
However, if required, it is possible to make the query case sensitive by using the keyword BINARY.
Question - 97 : - How to convert timestamps to date in MySQL?
Answer - 97 : -
It is a rather simple question that requires knowledge on two commands, like DATE_FORMAT and FROM_UNIXTIME.
DATE_FORMAT(FROM_UNIXTIME(`date_in_timestamp`), ‘%e %b %Y’) AS ‘date_formatted’
Question - 98 : - What is a LIKE statement? Explain % and _ in LIKE.
Answer - 98 : -
While using filters in commands like SELECT, UPDATE, and DELETE, conditions might require a pattern to detect. LIKE is used to do just that. LIKE has two wildcard characters, namely % (percentage) and _ (underscore). Percentage(%) matches a string of characters, while underscore matches a single character.
For example, %t will detect trees and tea both. However, _t will only detect one extra character, i.e., strings like ti or te.
Question - 99 : - Which drivers are necessary for MySQL?
Answer - 99 : -
There are many types of drivers in MySQL. Mostly they are used for connections with different computational languages. Some of them are listed below:
· PHP Driver
· JDBC
· OBDC
· Python Driver
· C – Wrapper
· Perl and Ruby Drivers
Question - 100 : - What is CHAR and VARCHAR in MySQL?
Answer - 100 : -
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.