• +91 9971497814
  • info@interviewmaterial.com

Computer Interview Questions Answers

My SQL Interview Questions Answers

Question - 81 : - You wrote a search engine that should retrieve 10 results at a time, but at the same time you’d like to know how many rows there’re total. How do you display that to the user?

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

Question - 82 : - How MySQL Optimizes WHERE Clauses ?

Answer - 82 : - The WHERE optimizations are put in the SELECT part here because they are mostly used with SELECT, but the same optimizations apply for WHERE in DELETE and UPDATE statements. Also note that this section is incomplete. MySQL does many optimizations, and we have not had time to document them all. Some of the optimizations performed by MySQL are listed below: Removal of unnecessary parentheses: ((a AND b) AND c OR (((a AND b) AND (c AND d)))) -> (a AND b AND c) OR (a AND b AND c AND d) Constant folding: (a<b AND b=c) AND a=5 -> b>5 AND b=c AND a=5 Constant condition removal (needed because of constant folding): (B>=5 AND B=5) OR (B=6 AND 5=50) OR (B=7 AND 5=6) -> B=5 OR B=6 Constant expressions used by indexes are evaluated only once. COUNT(*) on a single table without a WHERE is retrieved directly from the table information. This is also done for any NOT NULL expression when used with only one table. Early detection of invalid constant expressions. MySQL quickly detects that some SELECT statements are impossible and returns no rows. HAVING is merged with WHERE if you don't use GROUP BY or group functions (COUNT(), MIN()...). For each sub-join, a simpler WHERE is constructed to get a fast WHERE evaluation for each sub-join and also to skip records as soon as possible. All constant tables are read first, before any other tables in the query. A constant table is: An empty table or a table with 1 row. A table that is used with a WHERE clause on a UNIQUE index, or a PRIMARY KEY, where all index parts are used with constant expressions and the index parts are defined as NOT NULL. All the following tables are used as constant tables: mysql> SELECT * FROM t WHERE primary_key=1; mysql> SELECT * FROM t1,t2 WHERE t1.primary_key=1 AND t2.primary_key=t1.id; The best join combination to join the tables is found by trying all possibilities. If all columns in ORDER BY and in GROUP BY come from the same table, then this table is preferred first when joining. If there is an ORDER BY clause and a different GROUP BY clause, or if the ORDER BY or GROUP BY contains columns from

Question - 83 : - System/Compile Time and Startup Parameter Tuning?

Answer - 83 : - We start with the system level things since some of these decisions have to be made very early. In other cases a fast look at this part may suffice because it not that important for the big gains. However, it is always nice to have a feeling about how much one could gain by changing things at this level. The default OS to use is really important! To get the most use of multiple CPU machines one should use Solaris (because the threads works really nice) or Linux (because the 2.2 kernel has really good SMP support). Also on 32-bit machines Linux has a 2G file size limit by default. Hopefully this will be fixed soon when new filesystems are released (XFS/Reiserfs). If you have a desperate need for files bigger than 2G on Linux-Intel 32 bit, you should get the LFS patch for the ext2 file system. Because we have not run MySQL in production on that many platforms, we advice you to test your intended platform before choosing it, if possible.

Question - 84 : - What are ENUMs used for in MySQL?

Answer - 84 : - You can limit the possible values that go into the table. CREATE TABLE months (month ENUM ‘January’, ‘February’, ‘March’,…); INSERT months VALUES (’April’);

Question - 85 : - How do you get the month from a timestamp?

Answer - 85 : - SELECT MONTH(techpreparation_timestamp) from techpreparation_questions;

Question - 86 : - What are the features of MySQL

Answer - 86 : - 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 - 87 : - What happens when the column is set to AUTO INCREMENT and you reach the maximum value for that table?

Answer - 87 : - It stops incrementing. It does not overflow to 0 to prevent data losses, but further inserts are going to produce an error, since the key has been used already.

Computer Contributors

krishan
Rajeev Katiyar
Yes Baroda

Share your email for latest updates

Name:
Email:

Our partners