• +91 9971497814
  • info@interviewmaterial.com

Computer Interview Questions Answers

My SQL Interview Questions Answers

Question - 51 : - Portability ?

Answer - 51 : - Because all SQL servers implement different parts of SQL, it takes work to write portable SQL applications. For very simple selects/inserts it is very easy, but the more you need the harder it gets. If you want an application that is fast with many databases it becomes even harder! To make a complex application portable you need to choose a number of SQL servers that it should work with. You can use the MySQL crash-me program/web-page http://www.mysql.com/information/crash-me.php to find functions, types, and limits you can use with a selection of database servers. Crash-me now tests far from everything possible, but it is still comprehensive with about 450 things tested. For example, you shouldn't have column names longer than 18 characters if you want to be able to use Informix or DB2. Both the MySQL benchmarks and crash-me programs are very database-independent. By taking a look at how we have handled this, you can get a feeling for what you have to do to write your application database-independent. The benchmarks themselves can be found in the `sql-bench' directory in the MySQL source distribution. They are written in Perl with DBI database interface (which solves the access part of the problem). See http://www.mysql.com/information/benchmarks.html for the results from this benchmark. As you can see in these results, all databases have some weak points. That is, they have different design compromises that lead to different behavior. If you strive for database independence, you need to get a good feeling for each SQL server's bottlenecks. MySQL is VERY fast in retrieving and updating things, but will have a problem in mixing slow readers/writers on the same table. Oracle, on the other hand, has a big problem when you try to access rows that you have recently updated (until they are flushed to disk). Transaction databases in general are not very good at generating summary tables from log tables, as in this case row locking is almost useless. To get your application really database-independent, you need to define an easy extendable interface through which you manipulate your data. As C++ is available on most systems, it makes sense to use a C++ classes interface to t

Question - 52 : - How would you select all the users, whose phone number is null?

Answer - 52 : - SELECT user_name FROM users WHERE ISNULL(user_phonenumber);

Question - 53 : - Explain advantages of InnoDB over MyISAM?

Answer - 53 : - Row-level locking, transactions, foreign key constraints and crash recovery.

Question - 54 : - Using Your Own Benchmarks?

Answer - 54 : - You should definately benchmark your application and database to find out where the bottlenecks are. By fixing it (or by replacing the bottleneck with a 'dummy module') you can then easily identify the next bottleneck (and so on). Even if the overall performance for your application is sufficient, you should at least make a plan for each bottleneck, and decide how to solve it if someday you really need the extra performance. For an example of portable benchmark programs, look at the MySQL benchmark suite. You can take any program from this suite and modify it for your needs. By doing this, you can try different solutions to your problem and test which is really the fastest solution for you. It is very common that some problems only occur when the system is very heavily loaded. We have had many customers who contact us when they have a (tested) system in production and have encountered load problems. In every one of these cases so far, it has been problems with basic design (table scans are NOT good at high load) or OS/Library issues. Most of this would be a LOT easier to fix if the systems were not already in production. To avoid problems like this, you should put some effort into benchmarking your whole application under the worst possible load! You can use Sasha's recent hack for this - super-smack. As the name suggests, it can bring your system down to its knees if you ask it, so make sure to use it only on your development systems.

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

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

Question - 56 : - What does this query mean: SELECT user_name, user_isp FROM users LEFT JOIN isps USING (user_id) ?

Answer - 56 : - It’s equivalent to saying SELECT user_name, user_isp FROM users LEFT JOIN isps WHERE users.user_id=isps.user_id

Question - 57 : - Explain the difference between MyISAM Static and MyISAM Dynamic. ?

Answer - 57 : - 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.

Question - 58 : - How do you get the number of rows affected by query?

Answer - 58 : - SELECT COUNT (user_id) FROM users would only return the number of user_id’s.

Question - 59 : - How do you start and stop MySQL on Windows?

Answer - 59 : - net start MySQL, net stop MySQL

Question - 60 : - What's MySQL ?

Answer - 60 : - MySQL (pronounced "my ess cue el") is an open source relational database management system (RDBMS) that uses Structured Query Language (SQL), the most popular language for adding, accessing, and processing data in a database. Because it is open source, anyone can download MySQL and tailor it to their needs in accordance with the general public license. MySQL is noted mainly for its speed, reliability, and flexibility. ...

Computer Contributors

Rajeev Katiyar
Yes Baroda

Share your email for latest updates


Our partners