MYSQL Interview Questions and Answers

1. What is a relational database?

A relational database is a database based on a relational data model. Re-

The lection model is focused on organizing data in the form of two-dimensional tables. Each

relational table is a two-dimensional array and has the following properties

twami:

- each element of the table is one data element;

- all cells in the table column are homogeneous, that is, all elements in the column have the same

type (numeric, character, etc.);

- each column has a unique name;

- there are no identical rows in the table;

- the order of rows and columns can be arbitrary.

1.2 What types of keys do you know? purpose

1.3 what is a lock? what kinds are

1.4 Tell me about the locking mechanism using the example of Mycam Innodb engines.

1.5 purpose of transaction

1.6 levels of transaction isolation

1.7 What do you know non relational data? What is their scope?

2. What is a primary key?

Primary key (primary key) - a column whose values ​​in all rows are different. Per-

primary keys can be logical (natural) and surrogate (artificial).

So, for an imaginary table “Users”, the primary key can be an e-mail column (because

theoretically there can not be two users with the same e-mail). But in practice, better

use surrogate keys because their use allows you to abstract the keys to

data. In addition, the primary keys cannot be changed, but what if the user has

e-mail?

The surrogate key is an additional field in the database. This is usually

the sequence number of the record (although you can set them at your discretion, controlling to

they were unique).

3. What is normalization and denormalization?

Normalization is the process of bringing a database into a form in which it will correspond.

rules of normal forms. Normalization minimizes the amount of excess

information. Its purpose is to save data only once, but in the right place.

The normalized database eliminates duplication and multiple maintenance of data.

as well as data integrity issues that arise when re-entering

identical data.

Denormalization is the process of consciously bringing a database to a form in which it does not

will follow the normalization rules. Usually it is necessary to increase the

performance and speed of data extraction by increasing data redundancy.

If the application needs to frequently perform samples that take up too much

time (for example, combining data from multiple tables), then you should consider

possibility of denormalization.

A possible solution is the following: put the results of the sample in a separate table. This allows

increases the speed of query execution, but also means that

constant maintenance of this new table.

Before embarking on denormalization, it is necessary to make sure that the expected results

justify the costs that will be faced.

4. What is mysql_pconnect? How is it different from mysql_connect?

When using the mysql_connect () function, each time a new connection is opened to the

zoy data. After calling mysql_close () or after the completion of the script

is hidden.

The difference between mysql_pconnect () is that, first, when you call the function first

the already open (permanent) connection to the database is searched for (persistent connection), if not

new is being created. Secondly, after the script is completed and when you call mysql_close (),

MySQL database does not close, but remains open for future use.

5. What are MyISAM and InnoDB?

MyISAM and InnoDB are table types.

6. How do they differ?

MyISAM

- does not support transactions and its main disadvantages and advantages are associated with it;

- in most cases it is faster, since there are no transaction costs;

- takes up less disk space;

- less memory consumption for updates;

- full text index;

- fast INSERT, SELECT.

InnoDB

- transaction support;

- line lock. UPDATE does not lock the entire table;

- behaves well with a mixed load (insert | select | update | delete).

7. How to make an index in MySQL?

For primary keys (PRIMARY KEY) an index is created automatically.

8. What is SQL injection?

SQL injection - embedding malicious code in database queries. Using

SQL injection attacker can not only obtain sensitive information from the database

but also, under certain conditions, make changes there.

Vulnerability to SQL injection arises from the fact that user information

The matrix gets into the database request without proper processing: so that the script is not vulnerable,

it is required to ensure that all user data falls into all database requests

in shielded form.

9. Is there a universal protection against SQL injection?

For these purposes, PHP has special functions for working with strings:

strip_tags () - cuts HTML and PHP tags from a string.

htmlspecialchars (converts only special characters ('&', '”',” ',' <'and'> ') to HTML noun

('&', '"' ...). Used to filter user input for

protection against XSS attacks.

htmlentities () - converts all characters in the string (except letters) into HTML mnemonics. Using

is designed to protect against XSS, being a more flexible analogue of htmlspecialchars.

stripslashes () - removes escaped characters (after conversion, in essence,

there is no need to screen them with previous functions). Usually used in conjunction with test

with the get_magic_quotes_gpc () function, showing the current setting of the magic_ configuration

quotes_gpc. This configuration affects how special characters will be processed,

contained in the data transmitted by the user (arrays $ _GET, $ _POST, $ _COOKIE). With

magic_quotes_gpc = 1 these special characters (single and double quotes, backslash, byte

NULL) are automatically escaped. When magic_quotes_gpc = 0, all data is transmitted in such

the form in which the user entered them. In the latter case, for security purposes, processing is required.

transmit data.

mysql_real_escape_string - mnemonizes special characters in a string for use

in the SQL statement based on the current character set in the encoding of the connection. In other words,

the function turns any string into a proper and safe for MySQL query. Using

It is designed to clean up all data transferred to a MySQL query to protect against SQL injection.

10. There are two tables:

users - table with users (users_id, name)

orders - table with orders (orders_id, users_id, status)

1) Select all users from the users table for which ALL entries in the table are

orders have status = 0

2) Select all users from the users table with more than 5 entries.

This order in the orders table has status = 1

1) Selection of users using an attached request:

SELECT * FROM users WHERE users_id NOT IN (

SELECT users_id FROM orders WHERE status <> 0)

2) Using JOIN and HAVING:

SELECT u. * FROM orders o

JOIN users u ON u.users_id = o.users_id

WHERE o.status = 1 GROUP BY o.users_id

HAVING COUNT (o.status)> 5

11. What is the difference between LEFT, RIGHT and INNER JOIN?

The main difference is how tables are joined if there are no shared records.

A simple JOIN is the same as an INNER JOIN; it shows only general records of both tabs.

individuals. How records are considered common is determined by the fields in the join expression. For example

measures the following entry:

FROM t1 JOIN t2 on t1.id = t2.id

means that records with the same id that exist in both tables will be shown.

LEFT JOIN (or LEFT OUTER JOIN) means to show all the records from the left table (the one that

paradise goes first in the join-expression) regardless of the presence of the corresponding entries in the right

the table.

RIGHT JOIN (or RIGHT OUTER JOIN) acts as opposed to LEFT JOIN - shows all

records from the right (second) table and only matched from the left (first) table.

LEFT JOIN:

- when the condition of adhesion of the tables is fulfilled, the cells from the first table are joined

cells second;

- if the condition is not met, empty cells are added.

INNER JOIN:

- if the condition is fulfilled the same as with LEFT JOIN;

- if the condition is not met, the string is ignored altogether (there will be no cells even from the first

tables).

Simply put, the LEFT JOIN selects all the records from the first table, even if in the second table

no match for any condition. INNER JOIN will select only those that are fully compliant.

tweet condition.

12. What is the difference between WHERE and HAVING?

With HAVING, all previously grouped by GROUP BY are reflected.

data blocks that satisfy the conditions specified in HAVING. This is an additional opportunity.

to filter the output set.

Conditions in HAVING differ from conditions in WHERE:

- In the WHERE search condition, aggregate functions cannot be specified;

- HAVING excludes groups with the results of aggregation from the resulting data set

values;

- WHERE excludes from the calculation of aggregate values ​​by record grouping, not satisfying

conditions.

13. What can you say about the GROUP BY command?

GROUP BY is used to group the result of one or more columns.

Syntax:

SELECT column_name, aggregate_function (column_name)

FROM table_name

WHERE column_name operator value

GROUP BY column_name

14. Give an example of using GROUP BY.

We have “Orders” table:

Now we want to find the total amount of the order for each client. Run the query:

SELECT Customer, SUM (Price) FROM Orders GROUP BY Customer

Query result:

Now let's see what happens if we don't use the GROUP BY query:

SELECT Customer, SUM (OrderPrice) FROM Orders

Query result:

15. Suppose you have an online store. Make a request that shows how much money each individual buyer has brought in a total of all the time the store has existed.

SELECT customer_name, SUM (order_price) FROM orders

GROUP BY customer_name;

16. And now let the same request show only those who have bought goods in a total of at least 10 thousand euros.

SELECT customer_name, SUM (order_price) FROM orders

GROUP BY customer_name HAVING SUM (order_price)> = 10000;

17. What does the EXPLAIN command do?

EXPLAIN can tell exactly what happens when we execute a query. This information

The mation will allow us to detect slow queries and reduce the time spent on maintenance.

processing of the request, which subsequently can significantly speed up your application.

Here is an example of using this command:

EXPLAIN SELECT * FROM users WHERE id = '42 '

If the SELECT statement is preceded by the EXPLAIN keyword, MySQL will report how

details of SELECT processing, and will provide information on the order and method of binding

tables.

With the help of EXPLAIN you can find out when it is worth supplying tables with indexes in order to get

A faster sample using indexes to search for records. In addition, you can check how successful the table linking order was chosen by the optimizer.

You can make the optimizer link tables in a given order using the instructions

STRAIGHT_JOIN.

For complex connections, EXPLAIN returns a string of information about each used

tables in the SELECT statement. The tables are listed in the order in which they are

will be read. MySQL performs all bindings in a single pass (the method is called “single-

sweep multi-join ”). It is done this way: MySQL reads a row from the first table, finds a matching

giving a row in the second table, then in the third, and so on. When processing all tables

completes, MySQL returns the selected columns and reverses the list of tables

until the table with the most matching rows is found. Next line

is read from this table and the process continues in the following table.

18. How to display all fields from the super_table table?

SELECT * FROM super_table

19. How to display only the name_first, name_last, salary fields from the super_table table?

SELECT name_first, name_last, salary FROM super_table

20. In the super_table table, set the alias t and output all those who have salary above 3800

SELECT * FROM super_table AS t WHERE t.salary> 3800

21. Select the countries from which manufacturers supply products so that countries do not repeat 2 or more times.

SELECT DISTINCT country FROM manufacturers

22. Print all Ukrainian manufacturers.

SELECT * FROM manufacturers WHERE country = ”Ukraine”

23. Display only those stores that are located in Lviv and / or Kharkov.

and:

SELECT * FROM shops WHERE area = ”Lviv” AND area = ”Kharkov”

or:

SELECT * FROM shops WHERE area = ”Lviv” OR area = ”Kharkov”

24. To display all models of buses LAZ, with a capacity of at least 15 passengers.

SELECT * FROM buses WHERE brand = ”LAZ” AND NOT seats <15

25. Print all buses in ascending order of seats.

SELECT * FROM buses ORDER BY seats

26. Remove all buses in order of decreasing the number of seats.

SELECT * FROM buses ORDER BY seats DESC

27. What are the commands for counting field values?

SELECT MAX (seats) FROM buses // Bring out the bus with the maximum number of seats

SELECT MIN (seats) FROM buses // Take out the bus with the minimum number of seats

SELECT SUM (seats) FROM buses // Displays the total number of seats in all buses

SELECT AVG (seats) FROM buses // Displays the average number of seats

SELECT COUNT (*) FROM buses // Displays the total number of buses in the table

SELECT COUNT (*) FROM buses WHERE brand = ”LAZ” // Displays the number of LAZ buses

28. Suppose we have a table in which there are name and id fields. It is necessary to display the name with the highest id, without using the MAX command. How can I do that?

Sort by id downwards, but print only the first id. He will be the greatest

shim

SELECT name, id FROM customers ORDER BY id DESC LIMIT 1

29. With the help of the construction IN to deduce manufacturers from Ukraine, Germany and the USA.

SELECT * FROM manufacturer WHERE country IN

(“Ukraine”, “Germany”, “USA”)

30. Bring out all manufacturers except those in China, Tajikistan and Russia.

SELECT * FROM manufacturer WHERE country NOT IN

(“China”, “Tajikistan”, “Russia”)

31. Display empty / non-empty values.

empty:

SELECT * FROM manufacturer WHERE location IS NULL

not empty:

SELECT * FROM manufacturer WHERE location IS NOT NULL

32. Output only those buses whose names begin with the letter M.

SELECT * FROM buses WHERE brand LIKE “M%”

33. We don’t remember how to spell “Mercedes“ or “Mersedes“, but you need to select buses of this particular brand from the table. How to be?

Use the underscore character, which means “any character”:

SELECT * FROM buses WHERE brand LIKE “Mer_edes”

34. Select only those buses whose price ranges from 100,000 to 180,000 dollars, inclusive.

SELECT * FROM price BETWEEN 100000 AND 180000

35. Calculate the number of buses in the table, which have 45 seats.

SELECT COUNT (brand) FROM buses WHERE seats = 45

36. Give an example of a subquery.

SELECT * FROM buses WHERE price = (SELECT MAX (price) FROM buses)

37. Can I select data from several tables?

Yes, like this:

SELECT o.order_no, o.amount_paid, from.company

FROM orders AS o

LEFT JOIN customer AS with ON (c.custno = o.custno)

1. Adach: main table + directory. Print the union. Write a SQL query. What is the difference between INNER and OUTER JOINs?
2. Task: display a list of duplicate field values ​​and their number. Write a SQL query.
3. What is the optimization of the database, and in particular MySQL.
4. DB design. Normal forms.