Showing posts with label mysql. Show all posts
Showing posts with label mysql. 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?


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?


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


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.


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?


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?


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.


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?


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?


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?


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?


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?


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?


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


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?


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?


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?


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?


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?


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?


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?


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.

December 02, 2019

Top 20 PHP Interview Questions and Answers

Ques: 1) What is CAPTCHA?


CAPTCHA stands for Completely Automated Public Turing Test to tell Computers and Humans Apart. To prevent spammers from using bots to automatically fill out forms, CAPTCHA programmers will generate an image containing distorted images of a string of numbers and letters. Computers cannot determine what the numbers and letters are from the image but humans have great pattern recognition abilities and will be able to fairly accurately determine the string of numbers and letters. By entering the numbers and letters from the image in the validation field, the application can be fairly assured that there is a human client using it.


BlockChain interview Questions and Answers


Ques: 2) What is meant by urlencode() and urldecode()?


string urlencode(str)

When str contains a string like this “hello world” and the return value will be URL encoded and can be use to append with URLs, normally used to append data for GET like

string urldocode(str)

This will simple decode the GET variable’s value. Example: echo (urldecode($_GET_VARS[var])) will output “hello world”


C language Interview Questions and Answers


Ques: 3) What is difference between mysql_fetch_array(), mysql_fetch_row() and mysql_fetch_object()?


mysql_fetch_array - Fetch the all matching records of results.

mysql_fetch_object - Fetch the first single matching record of results.

mysql_fetch_row - fetches a result row as array.


C++ language Interview Questions and Answers


Ques: 4) What is difference between srand & shuffle?


The srand function seeds the random number generator with seed and shuffle is used for shuffling the array values.

shuffle - This function shuffles (randomizes the order of the elements in) an array. This function assigns new keys for the elements in array. It will remove any existing keys you may have assigned, rather than just reordering the keys.

srand - Seed the random number generator


Machine Learning Interview Questions and Answers


Ques: 5) How do you capture audio/video in PHP?


You need a module installed - FFMPEG. FFmpeg is a complete solution to record, convert and stream audio and video. It includes libavcodec, the leading audio/video codec library. FFmpeg is developed under Linux, but it can be compiled under most operating systems, including Windows.


MySQL Interview Questions and Answers


Ques: 6) What's the difference between COPY OF A FILE & MOVE_UPLOAD_FILE in file uploading?


Move: This function checks to ensure that the file designated by filename is a valid upload file (meaning that it was uploaded via PHP's HTTP POST upload mechanism). If the file is valid, it will be moved to the filename given by destination.

If filename is not a valid upload file, then no action will occur, and move_uploaded_file() will return FALSE.

Copy: Makes a copy of a file. Returns TRUE if the copy succeeded, FALSE otherwise.


PowerShell Interview Questions and Answers


Ques: 7) What is the difference between echo and print?


Main difference between echo() and print() is that echo is just an statement not a function and doesn't return's value or it just prints a value whereas print() is an function which prints a value and also it returns value.

We cannot pass arguments to echo since it is just a statement whereas print is a function and we can pass arguments to it and it returns true or false. print can be used as part of a more complex expression whereas echo cannot. echo is marginally faster since it doesn't set a return value.


Python Interview Questions and Answers


Ques: 8) What is the difference between require() and include()?


Both of these constructs includes and evaluates the specific file. The two functions are identical in every way except how they handle failure. If filepath not found, require() terminates the program and gives fatal error, but include() does not terminate the program; It gives warning message and continues to program.

include() produces a Warning while require() results in a Fatal Error if the filepath is not correct.


Python Pandas Interview Questions and Answers

Ques: 9) How do we know properties of the browser?


You can gather a lot of information about a person's computer by using $_SERVER['HTTP_USER_AGENT']. This can tell us more about the user's operating system, as well as their browser. For example I am revealed to be Mozilla/5.0 (Macintosh; U; PPC Mac OS X; en) AppleWebKit/418 (KHTML, like Gecko) Safari/417.9.3 when visiting a PHP page.

This can be useful to programmers if they are using special features that may not work for everyone, or if they want to get an idea of their target audience. This also is important when using the get_browser() function for finding out more information about the browser's capabilities. By having this information the user can be directed to a version of your site best suited to their browser.

get_browser() attempts to determine the capabilities of the user's browser. This is done by looking up the browser's information in the browscap.ini file.

echo $_SERVER['HTTP_USER_AGENT'] . "<hr />\n";

$browser = get_browser();

foreach ($browser as $name => $value)  { echo "<b>$name</b> $value <br />\n";



SQL Server Interview Questions and Answers


Ques: 10) What is difference between require_once(), require(), include(). Because all these function are used to call a file in another file.


Difference between require() and require_once(): require() includes and evaluates a specific file, while require_once() does that only if it has not been included before (on the same page).

So, require_once() is recommended to use when you want to include a file where you have a lot of functions for example. This way you make sure you don't include the file more times and you will not get the "function re-declared" error.

Difference between require() and include() is that require() produces a FATAL ERROR if the file you want to include is not found, while include() only produces a WARNING.

There is also include_once() which is the same as include(), but the difference between them is the same as the difference between require() and require_once().


Unix interview Questions and Answers


Ques: 11) What are the different types of errors in PHP?


1. Notices: These are trivial, non-critical errors that PHP encounters while executing a script - for example, accessing a variable that has not yet been defined. By default, such errors are not displayed to the user at all - although you can change this default behavior.

2. Warnings: These are more serious errors - for example, attempting to include() a file which does not exist. By default, these errors are displayed to the user, but they do not result in script termination.

3. Fatal errors: These are critical errors - for example, instantiating an object of a non-existent class, or calling a non-existent function. These errors cause the immediate termination of the script, and PHP’s default behavior is to display them to the user when they take place.


C# Language Interview Questions and Answers


Ques: 12) How to Create a Cookie & destroy it in PHP?



variable - name of the cookie variable variable - value of the cookie variable time - expiry time

Example: setcookie(”test”,$i,time()+3600);

Test - cookie variable name

$i - value of the variable ‘Test’ time()+3600 - denotes that the cookie will expire after an one hour.

Destroy a cookie by specifying expiry time

Example: setcookie(”test”,$i,time()-3600); // already expired time

Reset a cookie by specifying its name only



CSS (Cascading Style Sheets ) Interview Questions and Answers


Ques: 13) What is the difference between the functions unlink and unset?


unlink is a function for file system handling. It will simply delete the file in context. unset will set UNSET the specified variable.

unlink is used to delete a file. unset is used to destroy an earlier declared variable.


Robotic Process Automation(RPA) Interview Questions and Answers


Ques: 14) How do you know (status) whether the recipient of your mail had opened the mail i.e. read the mail?


Embed an URL in a say 0-byte image tag may be the better way to go. In other word, you embed an invisible image on you html email and when the src URL is being rendered by the server, you can track whether your recipients have view the emails or not.


UX Design Interview Questions and Answers


Ques: 15) What is difference between mysql_connect and mysql_pconnect?


mysql_connect opens up a database connection every time a page is loaded. mysql_pconnect opens up a connection, and keeps it open across multiple requests.

mysql_pconnect uses less resources, because it does not need to establish a database connection every time a page is loaded.


Docker Interview Questions and Answers


Ques: 16) What do you need to do to improve the performance (speedy execution) for the script you have written?


If your script is to retrieve data from Database, you should use "Limit" syntax. Break down the non dynamic sections of website which need not be repeated over a period of time as include files.


Google Cloud Computing Interview Questions and Answers


Ques: 17) How do you insert single & double quotes in MySQL db without using PHP?


By using &amp; / &quote;

Alternately, escape single quote using forward slash \' . In double quote you don't need to escape quotes. Insert double quotes as "".


Azure Interview Questions and Answers


Ques: 18) What is the difference between strstr & stristr?


For strstr, the syntax is: string strstr(string $string,string $str ); The function strstr will search $str in $string. If it finds the string means it will return string from where it finds the $str upto end of $string.

For Example:

$string = ""; $str="yahoomail";

The output is "". The main difference between strstr and stristr is of case sensitivity. The former consider the case difference and later ignore the case difference.


Linux Interview Questions and Answers


Ques: 19) What is the difference between explode and split?


Split function splits string into array by regular expression. Explode splits a string into array by string.

For Example:

explode(" and", "India and Pakistan and Srilanka"); split(" :", "India : Pakistan : Srilanka");

Both of these functions will return an array that contains India, Pakistan, and Srilanka.


Data Science Interview Questions and Answers


Ques: 20) How can you avoid execution time out error while fetching record from MySQL?


set_time_limit -- Limits the maximum execution time

For Example:


If you set to 0 you say that there is not limit.


Edge Computing Interview Questions and Answers


More Interview Questions and Answers:

Hadoop Technical Interview Questions and Answers


Hyperion Technical Interview Questions and Answers


Internet of Things (IOT) Interview Questions and Answers


C# Language Interview Questions and Answers