• +91 9971497814
  • info@interviewmaterial.com

My SQL Interview Questions Answers

Question 1 : What do % and _ mean inside LIKE statement?

Answer 1 : % corresponds to 0 or more characters, _ is exactly one character.

Question 2 : How do you start MySQL on Linux?

Answer 2 : /etc/init.d/mysql start

Question 3 : What are the features of MySQL

Answer 3 : 1) Internals and Portability       Written in C and C++, Works on different platforms, Fully multi-threaded, SQL functions are implemented 2) Column Types       signed/unsigned integers, FLOAT, DOUBLE, CHAR, VARCHAR, TEXT, BLOB, DATE, TIME, DATETIME, TIMESTAMP, YEAR, SET, ENUM, and OpenGIS spatial types 3) Statements and Functions       Full operator and function support in the SELECT and WHERE clauses of queries       Full support for SQL GROUP BY and ORDER BY clauses. Support for group functions (COUNT(), COUNT(DISTINCT ...), AVG(), STD(), SUM(), MAX(), MIN(), and GROUP_CONCAT()).       Support for LEFT OUTER JOIN and RIGHT OUTER JOIN       Support for aliases on tables and columns as required by standard SQL       DELETE, INSERT, REPLACE, and UPDATE return the number of rows that were changed (affected)       SHOW command can be used to retrieve information about databases, database engines, tables, and indexes       EXPLAIN command can be used to determine how the optimizer resolves a query.       Function names do not clash with table or column names 4) Security       A privilege and password system that is very flexible and secure, because all password traffic is encrypted 5) Scalability and Limits       Databases can contain 50 million records, 60,000 tables and about 5,000,000,000 rows       64 indexes per table are allowed and Each index may consist of 1 to 16 columns or parts of columns, maximum index width is 1000 bytes (CHAR, VARCHAR, BLOB, or TEXT ) 6) Connectivity       Clients can connect to the MySQL server using TCP/IP sockets on any platform       Windows systems in the NT family (NT, 2000, XP, or 2003)       On Unix systems, clients can connect using Unix domain socket files. 7) Localization  &nb

Question 4 : What’s the default port for MySQL Server?

Answer 4 : 3306

Question 5 : Explain TIMESTAMP DEFAULT ‘2006:09:02 17:38:44? ON UPDATE CURRENT_TIMESTAMP. ?

Answer 5 : A default value is used on initialization, a current timestamp is inserted on update of the row.

Question 6 : What should I do to prepare my client code to use performance-enhancing replication?

Answer 6 : A: If the part of your code that is responsible for database access has been properly abstracted/modularized, converting it to run with the replicated setup should be very smooth and easy - just change the implementation of your database access to read from some slave or the master, and to awlays write to the master. If your code does not have this level of abstraction, setting up a replicated system will give you an opportunity/motivation to it clean up. You should start by creating a wrapper library /module with the following functions: safe_writer_connect() safe_reader_connect() safe_reader_query() safe_writer_query() safe_ means that the function will take care of handling all the error conditions. You should then convert your client code to use the wrapper library. It may be a painful and scary process at first, but it will pay off in the long run. All applications that follow the above pattern will be able to take advantage of one-master/many slaves solution. The code will be a lot easier to maintain, and adding troubleshooting options will be trivial. You will just need to modify one or two functions, for example, to log how long each query took, or which query, among your many thousands, gave you an error. If you have written a lot of code already, you may want to automate the conversion task by using Monty's replace utility, which comes with the standard distribution of MySQL, or just write your own Perl script. Hopefully, your code follows some recognizable pattern. If not, then you are probably better off re-writing it anyway, or at least going through and manually beating it into a pattern. Note that, of course, you can use different names for the functions. What is important is having unified interface for connecting for reads, connecting for writes, doing a read, and doing a write.

Question 7 : Setting Up the Initial MySQL Privileges ? 

Answer 7 : After installing MySQL, you set up the initial access privileges by running scripts/mysql_install_db. The mysql_install_db script starts up the mysqld server, then initializes the grant tables to contain the following set of privileges: The MySQL root user is created as a superuser who can do anything. Connections must be made from the local host. NOTE: The initial root password is empty, so anyone can connect as root without a password and be granted all privileges. An anonymous user is created that can do anything with databases that have a name of 'test' or starting with 'test_'. Connections must be made from the local host. This means any local user can connect without a password and be treated as the anonymous user. Other privileges are denied. For example, normal users can't use mysqladmin shutdown or mysqladmin processlist. NOTE: The default privileges are different for Windows. Because your installation is initially wide open, one of the first things you should do is specify a password for the MySQL root user. You can do this as follows (note that you specify the password using the PASSWORD() function): shell> mysql -u root mysql mysql> UPDATE user SET Password=PASSWORD('new_password') WHERE user='root'; mysql> FLUSH PRIVILEGES; You can, in MySQL Version 3.22 and above, use the SET PASSWORD statement: shell> mysql -u root mysql mysql> SET PASSWORD FOR root=PASSWORD('new_password'); Another way to set the password is by using the mysqladmin command: shell> mysqladmin -u root password new_password Only users with write/update access to the mysql database can change the password for others users. All normal users (not anonymous ones) can only change their own password with either of the above commands or with SET PASSWORD=PASSWORD('new password'). Note that if you update the password in the user table directly using the first method, you must tell the server to re-read the grant tables (with FLUSH PRIVILEGES), because the change will go unnoticed otherwise. Once the root password has been set, thereafter you must supply that password whe

Question 8 : How would you change a table to InnoDB?

Answer 8 : ALTER TABLE techpreparation_questions ENGINE innodb;

Question 9 : MySQL - General Security

Answer 9 : Anyone using MySQL on a computer connected to the Internet should read this section to avoid the most common security mistakes. In discussing security, we emphasize the necessity of fully protecting the entire server host (not simply the MySQL server) against all types of applicable attacks: eavesdropping, altering, playback, and denial of service. We do not cover all aspects of availability and fault tolerance here. MySQL uses Access Control Lists (ACLs) security for all connections, queries, and other operations that a user may attempt to perform. There is also some support for SSL-encrypted connections between MySQL clients and servers. Many of the concepts discussed here are not specific to MySQL at all; the same general ideas apply to almost all applications. When running MySQL, follow these guidelines whenever possible: DON'T EVER GIVE ANYONE (EXCEPT THE MySQL ROOT USER) ACCESS TO THE mysql.user TABLE! The encrypted password is the real password in MySQL. If you know this for one user you can easily login as him if you have access to his 'host'. Learn the MySQL access privilege system. The GRANT and REVOKE commands are used for restricting access to MySQL. Do not grant any more privileges than necessary. Never grant privileges to all hosts. Checklist: Try mysql -u root. If you are able to connect successfully to the server without being asked for a password, you have problems. Any user (not just root) can connect to your MySQL server with full privileges! Review the MySQL installation instructions, paying particular attention to the item about setting a root password. Use the command SHOW GRANTS and check to see who has access to what. Remove those privileges that are not necessary using the REVOKE command. Do not keep any plain-text passwords in your database. When your computer becomes compromised, the intruder can take the full list of passwords and use them. Instead use MD5() or another one-way hashing function. Do not use passwords from dictionaries. There are special programs to break them. Even passwords like ``xfish98'' are very bad. Much better is ``duag98'' which contains the same word ``fish'' but typed one key to the left on a standard QWERTY keyboard. Another method is to use ``Mhall'' which is taken from the first characters of of each word in the

Question 10 : Disk Issues ?

Answer 10 : As mentioned before, disks seeks are a big performance bottleneck. This problems gets more and more apparent when the data starts to grow so large that effective caching becomes impossible. For large databases, where you access data more or less randomly, you can be sure that you will need at least one disk seek to read and a couple of disk seeks to write things. To minimize this problem, use disks with low seek times. Increase the number of available disk spindles (and thereby reduce the seek overhead) by either symlink files to different disks or striping the disks. Using symbolic links This means that you symlink the index and/or data file(s) from the normal data directory to another disk (that may also be striped). This makes both the seek and read times better (if the disks are not used for other things). Striping Striping means that you have many disks and put the first block on the first disk, the second block on the second disk, and the Nth on the (N mod number_of_disks) disk, and so on. This means if your normal data size is less than the stripe size (or perfectly aligned) you will get much better performance. Note that striping is very dependent on the OS and stripe-size. So benchmark your application with different stripe-sizes. Note that the speed difference for striping is very dependent on the parameters. Depending on how you set the striping parameters and number of disks you may get a difference in orders of magnitude. Note that you have to choose to optimize for random or sequential access. For reliability you may want to use RAID 0+1 (striping + mirroring), but in this case you will need 2*N drives to hold N drives of data. This is probably the best option if you have the money for it! You may, however, also have to invest in some volume-management software to handle it efficiently. A good option is to have semi-important data (that can be regenerated) on RAID 0 disk while storing really important data (like host information and logs) on a RAID 0+1 or RAID N disk. RAID N can be a problem if you have many writes because of the time to update the parity bits. You may also set the parameters for the file system that the database uses. One easy change is to mount the file system with the noatime option. That makes it skip the updating of the last access

My SQL Contributors


Share your email for latest updates


Our partners