• +91 9971497814
  • info@interviewmaterial.com

Computer Interview Questions Answers

My SQL Interview Questions Answers

Question - 21 : - Explain the difference between BOOL, TINYINT and BIT. ?

Answer - 21 : - Prior to MySQL 5.0.3: those are all synonyms. After MySQL 5.0.3: BIT data type can store 8 bytes of data and should be used for binary data.

Question - 22 : - On executing the DELETE statement I keep getting the error about foreign key constraint failing. What do I do?

Answer - 22 : - What it means is that so of the data that you’re trying to delete is still alive in another table. Like 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 contains people enrolled at that university. Proper way to do it would be to delete the offending data first, and then delete the university in question. Quick way would involve 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 was formulated with ON DELETE CASCADE, the data in dependent tables will be removed automatically.

Question - 23 : - what are the Basics of Database ?

Answer - 23 : - Databases are managed by a relational database management system (RDBMS). An RDBMS supports a database language to create and delete databases and to manage and search data. The database language used in almost all DBMSs is SQL, a set of statements that define and manipulate data. After creating a database, the most common SQL statements used are INSERT, UPDATE, DELETE, and SELECT, which add, change, remove, and search data in a database, respectively. Database A repository to store data. Table The part of a database that stores the data. A table has columns or attributes, and the data stored in rows. Attributes The columns in a table. All rows in table entities have the same attributes. For example, a customer table might have the attributes name, address, and city. Each attribute has a data type such as string, integer, or date. Rows The data entries in a table. Rows contain values for each attribute. For example, a row in a customer table might contain the values "Matthew Richardson," "Punt Road," and "Richmond." Rows are also known as records. Relational model A model that uses tables to store data and manage the relationship between tables. Relational database management system A software system that manages data in a database and is based on the relational model. DBMSs have several components described in detail in Chapter 1. SQL A query language that interacts with a DBMS. SQL is a set of statements to manage databases, tables, and data. Constraints Restrictions or limitations on tables and attributes. For example, a wine can be produced only by one winery, an order for wine can't exist if it isn't associated with a customer, having a name attribute could be mandatory for a customer. Primary key One or more attributes that contain values that uniquely identify each row. For example, a customer table might have the primary key of cust ID. The cust ID attribute is then assigned a unique value for each customer. A primary key is a constraint of most tables. Index A data structure

Question - 24 : - Functionality Missing from MySQL?

Answer - 24 : - The following functionality is missing in the current version of MySQL. For a prioritized list indicating when new extensions may be added to MySQL, you should consult the online MySQL TODO list. That is the latest version of the TODO list in this manual. The following functionality is missing in the current version of MySQL. For a prioritized list indicating when new extensions may be added to MySQL, you should consult the online MySQL TODO list. That is the latest version of the TODO list in this manual. MySQL - Sub-selects The following will not yet work in MySQL: SELECT * FROM table1 WHERE id IN (SELECT id FROM table2); SELECT * FROM table1 WHERE id NOT IN (SELECT id FROM table2); SELECT * FROM table1 WHERE NOT EXISTS (SELECT id FROM table2 where table1.id=table2.id); However, in many cases you can rewrite the query without a sub-select: SELECT table1.* FROM table1,table2 WHERE table1.id=table2.id; SELECT table1.* FROM table1 LEFT JOIN table2 ON table1.id=table2.id where table2.id IS NULL For more complicated subqueries you can often create temporary tables to hold the subquery. In some cases, however this option will not work. The most frequently encountered of these cases arises with DELETE statements, for which standard SQL does not support joins (except in sub-selects). For this situation there are two options available until subqueries are supported by MySQL. The first option is to use a procedural programming language (such as Perl or PHP) to submit a SELECT query to obtain the primary keys for the records to be deleted, and then use these values to construct the DELETE statement (DELETE FROM ... WHERE ... IN (key1, key2, ...)). The second option is to use interactive SQL to contruct a set of DELETE statements automatically, using the MySQL extension CONCAT() (in lieu of the standard || operator). For example: SELECT CONCAT('DELETE FROM tab1 WHERE pkid = ', tab1.pkid, ';') FROM tab1, tab2 WHERE tab1.col1 = tab2.col2; You can place this query in a script file and redirect input from it to the mysql command-line interpreter, piping its output back to a second instance of the interpreter: prompt> mysql --skip-column-names mydb > myscript.sql | mysql mydb MySQL only support

Question - 25 : - How MySQL Uses Indexes ?

Answer - 25 : - Indexes are used to find rows with a specific value of one column fast. Without an index MySQL has to start with the first record and then read through the whole table until it finds the relevant rows. The bigger the table, the more this costs. If the table has an index for the colums in question, MySQL can quickly get a position to seek to in the middle of the data file without having to look at all the data. If a table has 1000 rows, this is at least 100 times faster than reading sequentially. Note that if you need to access almost all 1000 rows it is faster to read sequentially because we then avoid disk seeks. All MySQL indexes (PRIMARY, UNIQUE, and INDEX) are stored in B-trees. Strings are automatically prefix- and end-space compressed. Indexes are used to: Quickly find the rows that match a WHERE clause. Retrieve rows from other tables when performing joins. Find the MAX() or MIN() value for a specific indexed column. This is optimized by a preprocessor that checks if you are using WHERE key_part_# = constant on all key parts < N. In this case MySQL will do a single key lookup and replace the MIN() expression with a constant. If all expressions are replaced with constants, the query will return at once: SELECT MIN(key_part2),MAX(key_part2) FROM table_name where key_part1=10 Sort or group a table if the sorting or grouping is done on a leftmost prefix of a usable key (for example, ORDER BY key_part_1,key_part_2 ). The key is read in reverse order if all key parts are followed by DESC. The index can also be used even if the ORDER BY doesn't match the index exactly, as long as all the unused index parts and all the extra are ORDER BY columns are constants in the WHERE clause. The following queries will use the index to resolve the ORDER BY part: SELECT * FROM foo ORDER BY key_part1,key_part2,key_part3; SELECT * FROM foo WHERE column=constant ORDER BY column, key_part1; SELECT * FROM foo WHERE key_part1=const GROUP BY key_part2; In some cases a query can be optimized to retrieve values without consulting the data file. If all used columns for some table are numeric and form a leftmost prefix for some key, the values may be retrieved from the index tree for greater speed: SELECT key_part3 FROM table_name WHERE key_

Question - 26 : - Speed of INSERT Queries ?

Answer - 26 : - The time to insert a record consists approximately of: Connect: (3) Sending query to server: (2) Parsing query: (2) Inserting record: (1 x size of record) Inserting indexes: (1 x number of indexes) Close: (1) where the numbers are somewhat proportional to the overall time. This does not take into consideration the initial overhead to open tables (which is done once for each concurrently running query). The size of the table slows down the insertion of indexes by N log N (B-trees). Some ways to speed up inserts: If you are inserting many rows from the same client at the same time, use multiple value lists INSERT statements. This is much faster (many times in some cases) than using separate INSERT statements. If you are inserting a lot of rows from different clients, you can get higher speed by using the INSERT DELAYED statement. Note that with MyISAM you can insert rows at the same time SELECTs are running if there are no deleted rows in the tables. When loading a table from a text file, use LOAD DATA INFILE. This is usually 20 times faster than using a lot of INSERT statements. It is possible with some extra work to make LOAD DATA INFILE run even faster when the table has many indexes. Use the following procedure: Optionally create the table with CREATE TABLE. For example, using mysql or Perl-DBI. Execute a FLUSH TABLES statement or the shell command mysqladmin flush-tables. Use myisamchk --keys-used=0 -rq /path/to/db/tbl_name. This will remove all usage of all indexes from the table. Insert data into the table with LOAD DATA INFILE. This will not update any indexes and will therefore be very fast. If you are going to only read the table in the future, run myisampack on it to make it smaller. Re-create the indexes with myisamchk -r -q /path/to/db/tbl_name. This will create the index tree in memory before writing it to disk, which is much faster because it avoids lots of disk seeks. The resulting index tree is also perfectly balanced. Execute a FLUSH TABLES statement or the shell command mysqladmin flush-tables. This procedure will be built into LOAD DATA INFILE in some future version of MySQL. You can

Question - 27 : - Why So Many Open tables?

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

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

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

Question - 29 : - General Information About MySQL

Answer - 29 : - MySQL is a very fast, multi-threaded, multi-user, and robust SQL (Structured Query Language) database server. MySQL is free software. It is licensed with the GNU GENERAL PUBLIC LICENSE http://www.gnu.org/.

Question - 30 : - How do you offload the time/date handling to MySQL?

Answer - 30 : - SELECT DATE_FORMAT(techpreparation_timestamp, ‘%Y-%m-%d’) from techpreparation_questions; A similar TIME_FORMAT function deals with time.

Computer Contributors

Rajeev Katiyar
Yes Baroda

Share your email for latest updates


Our partners