• +91 9971497814
  • info@interviewmaterial.com

Computer Interview Questions Answers

My SQL Interview Questions Answers

Question - 1 : - Keeping Your Password Secure ?

Answer - 1 : - It is inadvisable to specify your password in a way that exposes it to discovery by other users. The methods you can use to specify your password when you run client programs are listed below, along with an assessment of the risks of each method: Never give a normal user access to the mysql.user table. Knowing the encrypted password for a user makes it possible to login as this user. The passwords are only scrambled so that one shouldn't be able to see the real password you used (if you happen to use a similar password with your other applications). Use a -pyour_pass or --password=your_pass option on the command line. This is convenient but insecure, because your password becomes visible to system status programs (such as ps) that may be invoked by other users to display command lines. (MySQL clients typically overwrite the command-line argument with zeroes during their initialization sequence, but there is still a brief interval during which the value is visible.) Use a -p or --password option (with no your_pass value specified). In this case, the client program solicits the password from the terminal: shell> mysql -u user_name -p Enter password: ******** The `*' characters represent your password. It is more secure to enter your password this way than to specify it on the command line because it is not visible to other users. However, this method of entering a password is suitable only for programs that you run interactively. If you want to invoke a client from a script that runs non-interactively, there is no opportunity to enter the password from the terminal. On some systems, you may even find that the first line of your script is read and interpreted (incorrectly) as your password! Store your password in a configuration file. For example, you can list your password in the [client] section of the `.my.cnf' file in your home directory: [client] password=your_pass If you store your password in `.my.cnf', the file should not be group or world readable or writable. Make sure the file's access mode is 400 or 600. You can store your password in the MYSQL_PWD environment variable, but this method must be considered extremely insecure and should not be used. Some versions of ps include an option to display the environment of running processes; your password will be in plain sight for all to see if you set

Question - 2 : - Optimization Overview ?

Answer - 2 : - The most important part for getting a system fast is of course the basic design. You also need to know what kinds of things your system will be doing, and what your bottlenecks are. The most common bottlenecks are: Disk seeks. It takes time for the disk to find a piece of data. With modern disks in 1999, the mean time for this is usually lower than 10ms, so we can in theory do about 1000 seeks a second. This time improves slowly with new disks and is very hard to optimize for a single table. The way to optimize this is to spread the data on more than one disk. Disk reading/writing. When the disk is at the correct position we need to read the data. With modern disks in 1999, one disk delivers something like 10-20Mb/s. This is easier to optimize than seeks because you can read in parallel from multiple disks. CPU cycles. When we have the data in main memory (or if it already were there) we need to process it to get to our result. Having small tables compared to the memory is the most common limiting factor. But then, with small tables speed is usually not the problem. Memory bandwidth. When the CPU needs more data than can fit in the CPU cache the main memory bandwidth becomes a bottleneck. This is an uncommon bottleneck for most systems, but one should be aware of it.

Question - 3 : - What is DDL, DML and DCL ?

Answer - 3 : - If you look at the large variety of SQL commands, they can be divided into three large subgroups. Data Definition Language deals with database schemas and descriptions of how the data should reside in the database, therefore language statements like CREATE TABLE or ALTER TABLE belong to DDL. DML deals with data manipulation, and therefore includes most common SQL statements such SELECT, INSERT, etc. Data Control Language includes commands such as GRANT, and mostly concerns with rights, permissions and other controls of the database system.

Question - 4 : - How do I rotate replication logs?

Answer - 4 : - In Version 3.23.28 you should use PURGE MASTER LOGS TO command after determining which logs can be deleted, and optionally backing them up first. In earlier versions the process is much more painful, and cannot be safely done without stopping all the slaves in the case that you plan to re-use log names. You will need to stop the slave threads, edit the binary log index file, delete all the old logs, restart the master, start slave threads,and then remove the old log files.

Question - 5 : - What is the official way to pronounce “MySQL”

Answer - 5 : - The official way to pronounce “MySQL” is “My Ess Que Ell” (not “my sequel”).

Question - 6 : - Startup options to mysqld which concerns security ?

Answer - 6 : - The following mysqld options affect networking security: --secure IP numbers returned by the gethostbyname() system call are checked to make sure they resolve back to the original hostname. This makes it harder for someone on the outside to get access by pretending to be another host. This option also adds some sanity checks of hostnames. The option is turned off by default in MySQL Version 3.21 because sometimes it takes a long time to perform backward resolutions. MySQL Version 3.22 caches hostnames and has this option enabled by default. --skip-grant-tables This option causes the server not to use the privilege system at all. This gives everyone full access to all databases! (You can tell a running server to start using the grant tables again by executing mysqladmin flush-privileges or mysqladmin reload.) --skip-name-resolve Hostnames are not resolved. All Host column values in the grant tables must be IP numbers or localhost. --skip-networking Don't allow TCP/IP connections over the network. All connections to mysqld must be made via Unix sockets. This option is unsuitable for systems that use MIT-pthreads, because the MIT-pthreads package doesn't support Unix sockets. --skip-show-database SHOW DATABASE command doesn't return anything. --safe-show-database SHOW DATABASE only returns databases for which the user have some kind of privilege.

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 : - How MySQL Opens and Closes Tables ?

Answer - 8 : - table_cache, max_connections, and max_tmp_tables affect the maximum number of files the server keeps open. If you increase one or both of these values, you may run up against a limit imposed by your operating system on the per-process number of open file descriptors. However, you can increase the limit on many systems. Consult your OS documentation to find out how to do this, because the method for changing the limit varies widely from system to system. table_cache is related to max_connections. For example, for 200 concurrent running connections, you should have a table cache of at least 200 * n, where n is the maximum number of tables in a join. The cache of open tables can grow to a maximum of table_cache (default 64; this can be changed with the -O table_cache=# option to mysqld). A table is never closed, except when the cache is full and another thread tries to open a table or if you use mysqladmin refresh or mysqladmin flush-tables. When the table cache fills up, the server uses the following procedure to locate a cache entry to use: Tables that are not currently in use are released, in least-recently-used order. If the cache is full and no tables can be released, but a new table needs to be opened, the cache is temporarily extended as necessary. If the cache is in a temporarily-extended state and a table goes from in-use to not-in-use state, the table is closed and released from the cache. A table is opened for each concurrent access. This means that if you have two threads accessing the same table or access the table twice in the same query (with AS) the table needs to be opened twice. The first open of any table takes two file descriptors; each additional use of the table takes only one file descriptor. The extra descriptor for the first open is used for the index file; this descriptor is shared among all threads. You can check if your table cache is too small by checking the mysqld variable opened_tables. If this is quite big, even if you haven't done a lot of FLUSH TABLES, you should increase your table cache. MySQL - Drawbacks to Creating Large Numbers of Tables in the Same Database If you have many files in a directory, open, close, and create operations will be slow. If you execute SELECT sta

Question - 9 : - What are HEAP tables in MySQL?

Answer - 9 : - HEAP tables are in-memory. They are usually used for high-speed temporary storage. No TEXT or BLOB fields are allowed within HEAP tables. You can only use the comparison operators = and <=>. HEAP tables do not support AUTO_INCREMENT. Indexes must be NOT NULL.

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

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

Computer Contributors

krishan
Rajeev Katiyar
Yes Baroda

Share your email for latest updates

Name:
Email:

Our partners