• +91 9971497814
  • info@interviewmaterial.com

My SQL Interview Questions Answers

Question 1 : `--' as the Start of a Comment

Answer 1 : MySQL doesn't support views. Some other SQL databases use `--' to start comments. MySQL has `#' as the start comment character, even if the mysql command-line tool removes all lines that start with `--'. You can also use the C comment style /* this is a comment */ with MySQL. MySQL Version 3.23.3 and above supports the `--' comment style only if the comment is followed by a space. This is because this degenerate comment style has caused many problems with automatically generated SQL queries that have used something like the following code, where we automatically insert the value of the payment for !payment!: UPDATE tbl_name SET credit=credit-!payment! What do you think will happen when the value of payment is negative? Because 1--1 is legal in SQL, we think it is terrible that `--' means start comment. In MySQL Version 3.23 you can, however, use: 1-- This is a comment The following discussion only concerns you if you are running a MySQL version earlier than Version 3.23: If you have a SQL program in a text file that contains `--' comments you should use: shell> replace " --" " #" < text-file-with-funny-comments.sql \ | mysql database instead of the usual: shell> mysql database < text-file-with-funny-comments.sql You can also edit the command file ``in place'' to change the `--' comments to `#' comments: shell> replace " --" " #" -- text-file-with-funny-comments.sql Change them back with this command: shell> replace " #" " --" -- text-file-with-funny-comments.sql

Question 2 : Access Control, Stage 2: Request Verification?

Answer 2 : Once you establish a connection, the server enters Stage 2. For each request that comes in on the connection, the server checks whether you have sufficient privileges to perform it, based on the type of operation you wish to perform. This is where the privilege fields in the grant tables come into play. These privileges can come from any of the user, db, host, tables_priv, or columns_priv tables. The grant tables are manipulated with GRANT and REVOKE commands. The user table grants privileges that are assigned to you on a global basis and that apply no matter what the current database is. For example, if the user table grants you the delete privilege, you can delete rows from any database on the server host! In other words, user table privileges are superuser privileges. It is wise to grant privileges in the user table only to superusers such as server or database administrators. For other users, you should leave the privileges in the user table set to 'N' and grant privileges on a database-specific basis only, using the db and host tables. The db and host tables grant database-specific privileges. Values in the scope fields may be specified as follows: The wild-card characters `%' and `_' can be used in the Host and Db fields of either table. A '%' Host value in the db table means ``any host.'' A blank Host value in the db table means ``consult the host table for further information.'' A '%' or blank Host value in the host table means ``any host.'' A '%' or blank Db value in either table means ``any database.'' A blank User value in either table matches the anonymous user. The db and host tables are read in and sorted when the server starts up (at the same time that it reads the user table). The db table is sorted on the Host, Db, and User scope fields, and the host table is sorted on the Host and Db scope fields. As with the user table, sorting puts the most-specific values first and least-specific values last, and when the server looks for matching entries, it uses the first match that it finds. The tables_priv and columns_priv tables grant table- and column-specific privileges. Values in the scope fields may be specified as follows: The wild-card characters `%' and `_' can be used in the Host field of either table. A '%' or blank Host value in either table means ``any host.'' The Db, Table_na

Question 3 : Adding New User Privileges to MySQL?

Answer 3 : you can add users two different ways: by using GRANT statements or by manipulating the MySQL grant tables directly. The preferred method is to use GRANT statements, because they are more concise and less error-prone. The examples below show how to use the mysql client to set up new users. These examples assume that privileges are set up according to the defaults described in the previous section. This means that to make changes, you must be on the same machine where mysqld is running, you must connect as the MySQL root user, and the root user must have the insert privilege for the mysql database and the reload administrative privilege. Also, if you have changed the root user password, you must specify it for the mysql commands below. You can add new users by issuing GRANT statements: shell> mysql --user=root mysql mysqlgt; GRANT ALL PRIVILEGES ON *.* TO monty@localhost IDENTIFIED BY 'some_pass' WITH GRANT OPTION; mysqlgt; GRANT ALL PRIVILEGES ON *.* TO monty@"%" IDENTIFIED BY 'some_pass' WITH GRANT OPTION; mysqlgt; GRANT RELOAD,PROCESS ON *.* TO admin@localhost; mysqlgt; GRANT USAGE ON *.* TO dummy@localhost; These GRANT statements set up three new users: monty A full superuser who can connect to the server from anywhere, but who must use a password 'some_pass' to do so. Note that we must issue GRANT statements for both monty@localhost and monty@"%". If we don't add the entry with localhost, the anonymous user entry for localhost that is created by mysql_install_db will take precedence when we connect from the local host, because it has a more specific Host field value and thus comes earlier in the user table sort order. admin A user who can connect from localhost without a password and who is granted the reload and process administrative privileges. This allows the user to execute the mysqladmin reload, mysqladmin refresh, and mysqladmin flush-* commands, as well as mysqladmin process list . No database-related privileges are granted. (They can be granted later by issuing additional GRANT statements.) dummy A user who can connect without a password, but only from the local host. The global privileges are all set to 'N' -- the USAGE privilege type allows you to create a

Question 4 : Command Interpreter of mysql ?

Answer 4 : The MySQL command interpreter is commonly used to create databases and tables in web database applications and to test queries. Throughout the remainder of this chapter we discuss the SQL statements for managing a database. All these statements can be directly entered into the command interpreter and executed. The statements can also be included in server-side PHP scripts, as discussed in later chapters. Once the MySQL DBMS server is running, the command interpreter can be used. The command interpreter can be run using the following command from the shell, assuming you've created a user hugh with a password shhh: % /usr/local/bin/mysql -uhugh -pshhh The shell prompt is represented here as a percentage character, %. Running the command interpreter displays the output: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 36 to server version: 3.22.38 Type 'help' for help. mysql> The command interpreter displays a mysql> prompt and, after executing any command or statement, it redisplays the prompt. For example, you might issue the statement: mysql> SELECT NOW( ); This statement reports the time and date by producing the following output: +---------------------+ | NOW( ) | +---------------------+ | 2002-01-01 13:48:07 | +---------------------+ 1 row in set (0.00 sec) mysql> After running a statement, the interpreter redisplays the mysql> prompt. We discuss the SELECT statement later in this chapter. As with all other SQL statements, the SELECT statement ends in a semicolon. Almost all SQL command interpreters permit any amount of whitespace—spaces, tabs, or carriage returns—in SQL statements, and they check syntax and execute statements only after encountering a semicolon that is followed by a press of the Enter key. We have used uppercase for the SQL statements throughout this book. However, any mix of upper- and lowercase is equivalent. On startup, the command interpreter encourages the use of the help command. Typing help produces a list of com

Question 5 : Disk Issues ?

Answer 5 : 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

Question 6 : Explain advantages of InnoDB over MyISAM?

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

Question 7 : Explain data type TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP ?

Answer 7 : The column exhibits the same behavior as a single timestamp column in a table with no other timestamp columns.

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

Answer 8 : 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 9 : Explain the difference between FLOAT, DOUBLE and REAL. ?

Answer 9 : FLOATs store floating point numbers with 8 place accuracy and take up 4 bytes. DOUBLEs store floating point numbers with 16 place accuracy and take up 8 bytes. REAL is a synonym of FLOAT for now.

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

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

My SQL Contributors

krishan

Share your email for latest updates

Name:
Email:

Our partners