Showing posts with label innodb. Show all posts
Showing posts with label innodb. Show all posts

November 18, 2021

Top 20 MySQL Interview Questions & Answers

  

Ques: 1). I keep getting an error about a foreign key constraint failing when I run the DELETE statement. So, what do I do now?

Answer:

This suggests that some of the data you're attempting to delete is still present in another table. For example, if you have a table for universities and a table for students, both of which contain the ID of the university they attend, deleting a university table will fail if the students table still contains people who are enrolled at that university. The proper procedure is to delete the offending data first, followed by the university in question. Running SET foreign key checks=0 before the DELETE operation and then adjusting the parameter back to 1 after the DELETE command would be a quick way.

 

Ques: 2). You created a search engine that should provide ten results at a time, but you also want to know how many rows there are in total. How are you going to show that to the user?

Answer:

SELECT page title FROM web pages FROM SQL CALC FOUND ROWS LIMIT 1,10; 

SELECT FOUND ROWS(); 

The second query will tell you how many results there are (not that COUNT() is never utilised). the total, so you can put something like "Found 13,450,600 results, displaying 1-10" on the screen. It's worth noting that FOUND ROWS ignores the LIMITS you set and always returns the whole number of rows affected by the query.

 

Ques: 3). Differentiate between MyISAM Static and MyISAM Dynamic.

Answer:

All fields in MyISAM static have a defined width. To handle data types of varied lengths, the Dynamic MyISAM table would have fields such as TEXT, BLOB, and so on. In the event of corruption, MyISAM Static would be easier to restore since, even if you lose some data, you know exactly where to search for the beginning of the next record.

 

Ques: 4). What are the benefits of MyISAM versus InnoDB?

Answer:

Much more cautious disc space management - each MyISAM table is saved in its own file, which may subsequently be compressed using myisamchk if necessary. Tables in InnoDB are kept in tablespace, and there isn't much room for further optimization. Except for TEXT and BLOB, all data can only take up 8,000 bytes. InnoDB does not support full text indexing. Due to tablespace complexity, TRhe COUNT(*)s run slower than in MyISAM.

 

Ques: 5). What are MySQL's HEAP tables?

Answer:

The HEAP tables are stored in memory. They're typically utilised for high-speed, short-term storage. Within HEAP tables, no TEXT or BLOB fields are permitted. Only the comparison operators = and => can be used. AUTO INCREMENT is not supported by HEAP tables. The indexes must not be NULL.

 

Ques: 6). Difference between primary key, unique key and candidate key.

Answer:

Primary Key:- (i) It has unique value and it can’t accept null values.

(ii) We can have only one Primary key in a table.

Unique Key:- (i) It has unique value and it can accept only one null values.

(ii) We can have more than one unique key in a table.

Candidate Key:- candidate key full fill all the requirements of primary key which is not null and have unique records is a candidate for primary key. So thus type of key is known as candidate key. Every table must have at least one candidate key but at the same time can have several.

 

Ques: 7). What is the Difference Between MySQL and MySQL AB?

Answer:

The term "MySQL" is used to refer to both the MySQL database management system and the corporation that created it. MySQL AB is the full name of the firm. MySQL is the database management system (DBMS) that MySQL AB owns, develops, and sells—that is, the "MySQL" database server software and related items such client applications for talking with the server and programming interfaces for creating new clients.

The "AB" in the company name is an abbreviation for the Swedish "aktiebolag," or "stock company." As a result, the company's name is "MySQL Inc." MySQL Inc. and MySQL GmbH, for example, are subsidiaries of MySQL AB.

 

Ques: 8). What are the features of MYSQL?

Answer:

It's a pretty effective programme on its own. It can handle a significant portion of the features found in the most expensive and sophisticated database solutions.

  • It makes use of a standard version of the widely used SQL data language.
  • It's available under a free and open-source licence.
  • It is compatible with a wide range of operating systems and languages.
  • It operates swiftly and efficiently, even when dealing with massive data sets.
  • PHP has a variety of functions for working with MySQL databases.

 

Ques: 9). What are the steps you take in phpMyAdmin to modify a table?

Answer:

From the table list on the left side of the SQL screen, choose the table you want. In the main area of the screen, the table shows in a spreadsheet-like manner. In this window, we can change the table's contents.

By clicking the appropriate button displayed near the record, you can edit or remove it.

By clicking the corresponding link near the bottom of the table, you can add a row.

Press the Enter key to exit the cell you just edited. Any modifications you make to the data in the table are automatically transformed to SQL code.

 

Ques: 10). What are your thoughts on TIMESTAMP?

Answer:

The attributes DEFAULT CURRENT TIMESTAMP and ON UPDATE CURRENT TIMESTAMP of the TIMESTAMP data type enable for automated updating. Each table can only have one automatically updated TIMESTAMP field. That is, only one TIMESTAMP field with DEFAULT CURRENT TIMESTAMP or ON UPDATE CURRENT TIMESTAMP can be defined. On a single field, one or both attributes can be given.

 

Ques: 11). Is the data type DATETIME a STRING?

Answer:

DATETIME data types are commonly formatted as strings, and they can also be inputted as strings. They do, however, have numerical representations, which are visible when the value is cast into a numeric data type.

 

Ques: 12). What is the maximum number of privilege levels that can be granted?

Answer:

Privileges can be issued at five different levels.

Global - Privileges that apply to all databases on a MySQL server are known as global privileges. The mysql.user table stores these privileges.

Database - Database rights are applied to all items in a given database. The mysql.db and mysql.host tables store these privileges.

Table  - Table privileges are applied to all columns in a table. The mysql.tables priv table stores these privileges. Only table level rights are granted and revoked using the GRANT ALL ON db name.table name and REVOKE ALL ON db name.table name commands.

Column - Privileges at the column level apply to one or more columns in a table. The mysql.columns priv table stores these privileges. You must specify the same columns that were given when using the REVOKE command to remove column level access. Within parenthesis, type the column or columns for which privileges are to be provided.

Routine - For stored routines, the privileges CREATE ROUTINE, ALTER ROUTINE, EXECUTE, and GRANT apply (functions and procedures). At the global and database levels, they can be granted. These privileges can also be provided at the routine level for individual routines, with the exception of CREATE ROUTINE. The privileges are kept in the table mysql.procs priv.

 

Ques: 13). What exactly is SQLyog?

Answer:

SQLyog is perhaps the most widely used GUI tool for MySQL. The programme has been around since August 2002, and we can see how mature it is when we utilise it. This can be a very useful and practical tool for folks who come from a Windows desktop experience. There is a free community version that is somewhat limited, however it only operates on Windows desktops.

 

Ques: 14). Tell me how you back up a MYSQL database?

Answer:

With phpMyAdmin, backing up databases is simple. By clicking the database name in the left-hand navigation bar, we can choose the database we wish to back up. After that, we select the Export button and make sure that all of the tables we want to back up are highlighted.

Then, under Export, we can define the choices we desire. Make sure we can save the output by entering a filename.

 

Ques: 15). What are the differences between the heap table & temporary table?

Answer:

Heap Table:

  • Found in memory. It works as storage temporarily.
  • BLOB & TEXT fields aren’t allowed
  • Indexes should be “NOT NULL”
  • Doesn’t supports “AUTO_INCREMENT”
  • Can be shared among clients
  • Only comparison operators can be used (=,<,>,>=, <=)

Temporary Table:

  •  Used to store provisional data
  • Temporarily stored data is deleted after client session ends
  • Aren’t shared among clients
  • Special syntax is used; “create temporary table”

 

Ques: 16). Why would you want to utilise the MySQL Database Server?

Answer:

The MySQL Database Server is a fast, dependable, and simple to use database server. Anyone with access to the internet can use and alter the software. Anyone can use MySQL for free by downloading it from the Internet.

MySQL Database Software is a client/server system that includes a multi-threaded SQL server that supports a variety of backends, a variety of client programmes and libraries, administrative tools, and a variety of application programming interfaces (APIs).

 

Ques: 17). Is the phpMyAdmin interface user-friendly?

Answer:

The UI of the phpMyAdmin application is fairly user-friendly. Many administrators are at least somewhat familiar with it because it is utilised by a number of web hosting companies. Although it has a limited set of capabilities, it does offer some flexibility in that we can use a web browser to view the application from anywhere.

 

Ques: 18). What are the Common MYSQL Function?

Answer:

CONCAT(A, B) - Concatenates two string values to create a single string output. Often used to combine two or more fields into one

FORMAT(X, D) - Formats the number X to D significant digits.

CURRDATE(), CURRTIME() - Returns the current date or time.

NOW() - Returns the current date and time as one value.

MONTH(), DAY(), YEAR(), WEEK(), WEEKDAY() - Extracts the given data from a date value.

HOUR(), MINUTE(), SECOND() - Extracts the given data from a time value.

DATEDIFF(A, B) - Determines the difference between two dates-commonly used to calculate ages

SUBTIMES(A, B) - Determines the difference between two times.

FROMDAYS(INT) - Converts an integer number of days into a date value.

 

Ques: 19). What are Access Control Lists and how do they work?

Answer:

An Access Control List (ACL) is a set of permissions associated with a certain item. This list is the foundation of the MySQL server's security paradigm, and knowing it can immensely assist you in diagnosing problems with users being unable to connect.

The ACLs (also known as grant tables) are cached in memory by MySQL. MySQL validates the authentication information and permissions against the ACLs in a predetermined order when a user tries to authenticate or run a command.

 

Ques: 20). What is the "i-am-a-dummy flag" in MySQL used for?

Answer:

When we want to deny or suspend "UPDATE & DELETE" instructions unless there is a WHERE clause present, we utilise the "i-am-a-dummy" flag.