mysql query interview questions

• What is MySQL? …
• What are some of the advantages of using MySQL? …
• What do you mean by ‘databases’? …
• What does SQL in MySQL stand for? …
• What does a MySQL database contain? …
• How can you interact with MySQL? …
• What are MySQL Database Queries? …
• What are some of the common MySQL commands?

Top 9 SQL queries for interview | SQL Tutorial | Interview Question

EmployeeInfo Table:

EmployeePosition Table:

Let us start by taking a look at some of the most frequently asked SQL Query interview questions,

Prepare Sample Data To Practice SQL Skill.

To prepare the sample data, you can run the following queries in your database query executor or on the SQL command line. We’ve tested them with MySQL Server 5.7 and MySQL Workbench 6.3.8 query browser. You can also download these Softwares and install them to carry on the SQL exercise.

Once above SQL would run, you’ll see a result similar to the one attached below.

Explore our Popular Software Engineering Courses

6. What is MySQL workbench?

MySQL Workbench is a bound together visual instrument for database modelers, designers, and DBAs. MySQL Workbench provides Data modelling, SQL, and server setup set of administrative tools. To put it simply, MySQL workbench makes it possible to operate the database management system through GUI.

7. 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

8. How can we delete a column or a row in MySQL?

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;

9. What are the different ways to join tables in MySQL?

This is one of the most important MySQL database interview questions.

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.

10. Can a primary key be dropped in MySQL? If yes, how?

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;

11. What are Procedures in MySQL?

This is a MySQL basic interview questions. A thorough understanding of this is very important.

Procedures (or stored procedures) are subprograms, just like in a regular language, embedded in the database. A stored procedure consists of a name, SQL statement(s) and parameters. It utilises the caching in MySQL and hence saves time and memory, just like the prepared statements.

upGrad’s Exclusive Software and Tech Webinar for you –

SAAS Business – What is So Different?

12. 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.

13. How to add users in MySQL?

To simply put, the user can be added by using the CREATE command and specifying the necessary credentials. First, log in to the MySQL account and then apply the syntax. Something like this:

CREATE USER ‘testuser’ IDENTIFIED BY ‘sample password’;

Users can be granted permissions, by the following commands:

GRANT SELECT ON * . * TO ‘testuser’;

14. What is the core difference between Oracle and MySQL?

The core difference is that MySQL works on a single-model database. That means it can only work with one base structure, while Oracle is a multi-model database. It means it can support various data models like graph, document, key-value, etc.

Another fundamental difference is that Oracle’s support comes with a price tag for industrial solutions. While MySQL is open-source.

Now this question is one of the MySQL interview questions that should be understood carefully. Because it directly deals with the industry standards and what the company wants.

MySQL is free and open-source, whereas Oracle is commercial and paid. MySQL is more customizable than Oracle because Oracle is a finished product.

From the software perspective, Oracle is more powerful owing to its extra features. Also, it offers better indexing due to which it provides a competitive benefit over MySQL.

15. What is CHAR and VARCHAR in MySQL?

This is one of the most important interview questions on 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.

16. Which drivers are necessary for MySQL?

There are many types of drivers in MySQL. Mostly they are used for connections with different computational languages. Some of them are listed below:

· Perl and Ruby Drivers

17. What is a LIKE statement? Explain % and _ in LIKE.

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.

18. How to convert timestamps to date in MySQL?

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’

19. Can a query be written in any case in MySQL?

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.

This MySQL interview question can be tricky, especially when asked to make the query case-sensitive explicitly.

20. How to save s in MySQL?

This is one of the most basic MySQL interview questions.

s 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 s on disk.

21. How to get multiple condition results from data in MySQL?

There are two ways to do so. The first is to use the keyword OR while using the WHERE condition. The other is to use a list of values to check and use IN with WHERE.

22. What are the different file formats used by MyISAM?

Typically, a MyISAM table is stored using three files on disk. The data file and the index file, which are defined with extensions .MYD and .MYI, respectively. There is a table definition file that has .frm extension.

23. How does DISTINCT work in MySQL?

DISTINCT is used to avoid the problem of duplicity while fetching the results of a particular query. DISTINCT is used to make sure the results do not contain repeated values. DISTINCT can be used with the SELECT clause. Here is the syntax for it:

SELECT DISTINCT something FROM tablename;

24. Is there any upper limit for the number of columns in a table?

Although the exact size limitation depends on many factors, MySQL has a hard limit on max size to be 4096 columns. But as said, for a given table, the effective-maximum may be less.

25. What are Access Control Lists or ACLs, in accordance with MySQL?

The ACLs or Access control lists are used in a way to give a guideline for security in the MySQL database. MySQL provides security based on ACLs for all the tasks performed by users like connection requests, queries, and any other operation.

26. How to make connections persistent in MySQL?

While making a connection request, if Mysql_pconnect is used rather than mysql_connect, then it can make the connection persistent. Here ‘p’ means persistent. The database connection is not closed every time.

27. Explain the SAVEPOINT statement in MySQL.

SAVEPOINT is a way of making sub-transactions in MySQL, which are also known as nested transactions.

SAVEPOINT marks a point in a regular transaction. It indicates a point to which the system can rollback.

Check out: SQL Developer Salary in India

Learn Software development Courses online from the World’s top Universities. Earn Executive PG Programs, Advanced Certificate Programs, or Masters Programs to fast-track your career.

FAQ

What are the basic queries in MySQL?

• SELECT – extracts data from a database.
• UPDATE – updates data in a database.
• DELETE – deletes data from a database.
• INSERT INTO – inserts new data into a database.
• CREATE DATABASE – creates a new database.
• ALTER DATABASE – modifies a database.
• CREATE TABLE – creates a new table.

How do I practice SQL queries in interview?

1. SQL Exercise 1 – Write a Statement. …
2. SQL Exercise 2 – Write a Statement. …
3. SQL Exercise 3 – Find the Error. …
4. SQL Exercise 4 – Find the Result. …
5. SQL Exercise 5 – Write a Query. …
6. SQL Exercise 6 – Write a Date Query. …
7. SQL Exercise 7 – Write a Query. …
8. SQL Exercise 8 – Find and Delete Duplicates.

What are the basic SQL queries asked in interview?

• What is Database? …
• What is DBMS? …
• What is RDBMS? …
• What is SQL? …
• What is the difference between SQL and MySQL? …
• What are Tables and Fields? …
• What are Constraints in SQL? …
• What is a Primary Key?

What is an MySQL query?