Prepare better with the best interview questions and answers, and walk away with top interview tips. These interview questions and answers will boost your core interview skills and help you perform better. Be smarter with every interview.
Any RDBMS SQL Queries/commands can be divided into three major subgroups.
DDL (Data Definition Language ) which deals with database schemas and descriptions of how the data should reside in the database, thus the language statements like CREATE TABLE / ALTER TABLE belong to DDL
DML (Data Manipulation Language ) which deals with data manipulation, and therefore includes most common SQL statements such as SELECT, INSERT, etc.
DCL (Data Control Language ) which includes commands such as GRANT, and mostly concerns with rights, permissions, privileges and other controls of the database system.
Before executing any update/delete on the table data, it’s always best practice to check the number of rows that are going to be affected i.e., updated / removed from table with COUNT() operation with the given WHERE condition to cross check whether we’re doing correct update or not
SELECT COUNT(user_id) FROM users WHERE <>;
will only return the number of user_id s going to effect.
Use DISTINCT in the query, such as
SELECT DISTINCT fname FROM users;
You can also ask for a number of distinct values by saying SELECT COUNT (DISTINCT fname) FROM users;
To do that we can use the concatenate function as shown in the below
CONCAT (string1, string2, string3) .
Ex., SELECT CONCAT(fname, “ ”, lname) from users;
When you run mysqladmin status, you'll see something like this:
Uptime: 426 Running threads: 1 Questions: 11082 Reloads: 1 Open tables: 12
This can be somewhat perplexing if you only have 6 tables.
MySQL is multithreaded, so it may have many queries on the same table simultaneously.
To minimize the problem with two threads having different states on the same file, the table is opened independently by each concurrent thread.
This takes some memory and one extra file descriptor for the data file. The index file descriptor is shared between all threads.
A database is a structured collection of data. It may be anything from a simple shopping list to a picture gallery or the vast amounts of information in a corporate network.
To add, access, and process data stored in a computer database, you need a database management system such as MySQL.
Since computers are very good at handling large amounts of data, database management plays a central role in computing, as stand-alone utilities, or as parts of other applications.
MySQL is very fast, reliable, and easy to use. If that is what you are looking for, you should give it a try. MySQL also has a very practical set of features developed in very close cooperation with our users.
You can find a performance comparison of MySQL to some other database managers
on our benchmark page. See section 12.7 Using Your Own Benchmarks.
MySQL was originally developed to handle very large databases much faster than existing solutions and has been successfully used in highly demanding production environments for several years.
Though under constant development, MySQL today offers a rich and very useful set of functions. The connectivity, speed, and security make MySQL highly suited for accessing databases on the Internet.
In some cases MySQL will handle the query differently when you are using LIMIT # and not using HAVING:
If you are selecting only a few rows with LIMIT, MySQL will use indexes in some cases when it normally would prefer to do a full table scan.
If you use LIMIT # with ORDER BY, MySQL will end the sorting as soon as it has found the first # lines instead of sorting the whole table.
When combining LIMIT # with DISTINCT, MySQL will stop as soon as it finds # unique rows.
In some cases a GROUP BY can be resolved by reading the key in order (or do a sort on the key) and then calculate summaries until the key value changes. In this case LIMIT # will not calculate any unnecessary GROUP BY's.
As soon as MySQL has sent the first # rows to the client, it will abort the query.
LIMIT 0 will always quickly return an empty set. This is useful to check the query and to get the column types of the result columns.
The size of temporary tables uses the LIMIT # to calculate how much space is needed to resolve the query.
To delete all rows in the table, you should use TRUNCATE TABLE table_name. The time to delete a record is exactly proportional to the no of indexes. To delete records much faster, we can increase the size of the index cache.
Join is data retrieval operation that combines multiple query outputs of the same structure into a single output.
By default the MySQL UNION removes all duplicate rows from the result set even if you don’t explicit using DISTINCT after the keyword UNION.
SELECT customerNumber id, contactLastname name
SELECT employeeNurrber id, firstname name
Any RDBMS Transaction is a logical unit of work requested by a user to be applied on the database objects. MySQL server introduces the transaction concept to allow users to group one or many SQL statements into a single transaction, so that the effects of all the SQL statements in a transaction can be either all committed (applied to the database) or all rolled back (undone from the database).
mysql> DELIMITER // mysql> CREATE PROCEDURE OfficesByCounties (IN countryName VARCHAR(255)) -> BEGIN -> SELECT * -> FROM offices -> WHERE country = countryName; -> END // Query OK, 0 rows affected (0.04 sec) mysql> DELIMITER ; mysql> create table offices (name varchar(10), country varchar(50)); Query OK, 0 rows affected (0.06 sec) mysql> insert into offices values ('here' , 'india'),('mongo' , 'usa'), ('citi' , 'canada'),('JPM' , 'India'); Query OK, 4 rows affected (0.06 sec) Records: 4 Duplicates: 0 Warnings: 0 mysql> CALL GetOfficeByCountry('India'); +------+---------+ | name | country | +------+---------+ | here | india | | JPM | India | +------+---------+ 2 rows in set (0.00 sec)
As per this error it denotes that the data that we’re trying to delete is still present in another table.
Imagine if you have a table for universities and a table for students, which contains the ID of the university they go to, running a delete on a university table will fail if the students table still has people enrolled for that university.
Right way to do it would be to delete the offending data first, and then delete the university in query.
Simple workaround would be running SET foreign_key_checks=0 before the DELETE command, and setting the parameter back to 1 after the DELETE is done.
If your foreign key constraint has ON DELETE CASCADE, the data in dependent tables will be deleted automatically.
When a new threads connects to mysqld, mysqld will span a new thread to handle the request. This thread will first check if the hostname is in the hostname cache. If not the thread will call gethostbyaddr_r() and gethostbyname_r() to resolve the hostname.
If the operating system doesn't support the above thread-safe calls, the thread will lock a mutex and call gethostbyaddr() and gethostbyname() instead. Note that in this case no other thread can resolve other hostnames that is not in the hostname cache until the first thread is ready.
You can disable DNS host lookup by starting mysqld with --skip-name-resolve. In this case you can however only use IP names in the MySQL privilege tables.
If you have a very slow DNS and many hosts, you can get more performance by either disabling DNS lookop with --skip-name-resolve or by increasing the HOST_CACHE_SIZE define (default: 128) and recompile mysqld.
You can disable the hostname cache with --skip-host-cache. You can clear the hostname cache with FLUSH HOSTS or mysqladmin flush-hosts.
If you don't want to allow connections over TCP/IP, you can do this by starting mysqld with --skip-networking.
SELECT SQL_CALC_FOUND_ROWS page_title FROM web_pages LIMIT 1,10; SELECT FOUND_ROWS(); The second query (not that COUNT() is never used) will tell you how many results there’re total, so you can display a phrase "Found 13,450,600 results, displaying 1-10". Note that FOUND_ROWS does not pay attention to the LIMITs you specified and always returns the total number of rows affected by query.
There is no user without a password.
There is no user without a user name.
There is no user whose Host column contains % (which here indicates that the user can log in from anywhere in the network or the Internet).
There are as few users as possible (in the ideal case only root) who have unrestricted access.
In MyISAM static all the fields have fixed width.
The Dynamic MyISAM table would include fields such as TEXT, BLOB, etc. to accommodate the data types with various lengths.
MyISAM Static would be easier to restore in case of corruption, since even though you might lose some data, you know exactly where to look for the beginning of the next record.
HEAP tables are in-memory. They are usually used for high-speed temporary storage. No TEXT or
BLOB fields are allowed within HEAP tables. You can only use the comparison operators = and <=>. HEAP tables do not support
AUTO_INCREMENT. Indexes must be NOT NULL.
DISTINCT is converted to a GROUP BY on all columns, DISTINCT combined with ORDER BY will in many cases also need a temporary table.
When combining LIMIT # with DISTINCT, MySQL will stop as soon as it finds # unique rows.
If you don't use columns from all used tables, MySQL will stop the scanning of the not used tables as soon as it has found the first match.
SELECT DISTINCT t1.a FROM t1,t2 where t1.a=t2.a;
In the case, assuming t1 is used before t2 (check with EXPLAIN), then MySQL will stop reading from t2 (for that particular row in t1) when the first row in t2 is found.
Before MySQL don’t a supported stored procedure, functions, triggers, views, subqueries and partitioning.
But now they are supported. Small and middle level companies are relied on MySQL because it is opensource.
Supports replication and clustering for High Availability.