• +91 9971497814
  • info@interviewmaterial.com

Computer Interview Questions Answers

My SQL Interview Questions Answers

Question - 11 : - What does –i-am-a-dummy flag to do when starting MySQL?

Answer - 11 : - What does –i-am-a-dummy flag to do when starting MySQL? Makes the MySQL engine refuse UPDATE and DELETE commands where the WHERE clause is not present. On executing the DELETE statement I keep getting the error about foreign key constraint failing.

Question - 12 : - How do you find out which auto increment was assigned on the last insert?

Answer - 12 : - SELECT LAST_INSERT_ID() will return the last value assigned by the auto_increment function. Note that you don’t have to specify the table name.

Question - 13 : - How do you start MySQL on Linux?

Answer - 13 : - /etc/init.d/mysql start

Question - 14 : - How do you concatenate strings in MySQL?

Answer - 14 : - CONCAT (string1, string2, string3)

Question - 15 : - How MySQL uses DNS ?

Answer - 15 : - When a new threads connects to mysqld, mysqld will span a new thread to handle the request. This thread will first check if the hostname is in the hostname cache. If not the thread will call gethostbyaddr_r() and gethostbyname_r() to resolve the hostname. If the operating system doesn't support the above thread-safe calls, the thread will lock a mutex and call gethostbyaddr() and gethostbyname() instead. Note that in this case no other thread can resolve other hostnames that is not in the hostname cache until the first thread is ready. You can disable DNS host lookup by starting mysqld with --skip-name-resolve. In this case you can however only use IP names in the MySQL privilege tables. If you have a very slow DNS and many hosts, you can get more performance by either disabling DNS lookop with --skip-name-resolve or by increasing the HOST_CACHE_SIZE define (default: 128) and recompile mysqld. You can disable the hostname cache with --skip-host-cache. You can clear the hostname cache with FLUSH HOSTS or mysqladmin flush-hosts. If you don't want to allow connections over TCP/IP, you can do this by starting mysqld with --skip-networking. MySQL - Get Your Data as Small as Possible One of the most basic optimization is to get your data (and indexes) to take as little space on the disk (and in memory) as possible. This can give huge improvements because disk reads are faster and normally less main memory will be used. Indexing also takes less resources if done on smaller columns. MySQL supports a lot of different table types and row formats. Choosing the right table format may give you a big performance gain. You can get better performance on a table and minimize storage space using the techniques listed below: Use the most efficient (smallest) types possible. MySQL has many specialized types that save disk space and memory. Use the smaller integer types if possible to get smaller tables. For example, MEDIUMINT is often better than INT. Declare columns to be NOT NULL if possible. It makes everything faster and you save one bit per column. Note that if you really need NULL in your application you should definitely use

Question - 16 : - Speed of Queries that Access or Update Data ?

Answer - 16 : - First, one thing that affects all queries: The more complex permission system setup you have, the more overhead you get. If you do not have any GRANT statements done, MySQL will optimize the permission checking somewhat. So if you have a very high volume it may be worth the time to avoid grants. Otherwise more permission check results in a larger overhead. If your problem is with some explicit MySQL function, you can always time this in the MySQL client: mysql> select benchmark(1000000,1+1); +------------------------+ | benchmark(1000000,1+1) | +------------------------+ | 0 | +------------------------+ 1 row in set (0.32 sec) The above shows that MySQL can execute 1,000,000 + expressions in 0.32 seconds on a PentiumII 400MHz. All MySQL functions should be very optimized, but there may be some exceptions, and the benchmark(loop_count,expression) is a great tool to find out if this is a problem with your query.

Question - 17 : - Setting Up the Initial MySQL Privileges ? 

Answer - 17 : - After installing MySQL, you set up the initial access privileges by running scripts/mysql_install_db. The mysql_install_db script starts up the mysqld server, then initializes the grant tables to contain the following set of privileges: The MySQL root user is created as a superuser who can do anything. Connections must be made from the local host. NOTE: The initial root password is empty, so anyone can connect as root without a password and be granted all privileges. An anonymous user is created that can do anything with databases that have a name of 'test' or starting with 'test_'. Connections must be made from the local host. This means any local user can connect without a password and be treated as the anonymous user. Other privileges are denied. For example, normal users can't use mysqladmin shutdown or mysqladmin processlist. NOTE: The default privileges are different for Windows. Because your installation is initially wide open, one of the first things you should do is specify a password for the MySQL root user. You can do this as follows (note that you specify the password using the PASSWORD() function): shell> mysql -u root mysql mysql> UPDATE user SET Password=PASSWORD('new_password') WHERE user='root'; mysql> FLUSH PRIVILEGES; You can, in MySQL Version 3.22 and above, use the SET PASSWORD statement: shell> mysql -u root mysql mysql> SET PASSWORD FOR root=PASSWORD('new_password'); Another way to set the password is by using the mysqladmin command: shell> mysqladmin -u root password new_password Only users with write/update access to the mysql database can change the password for others users. All normal users (not anonymous ones) can only change their own password with either of the above commands or with SET PASSWORD=PASSWORD('new password'). Note that if you update the password in the user table directly using the first method, you must tell the server to re-read the grant tables (with FLUSH PRIVILEGES), because the change will go unnoticed otherwise. Once the root password has been set, thereafter you must supply that password whe

Question - 18 : - Use mysqldump to create a copy of the database?

Answer - 18 : - mysqldump -h mysqlhost -u username -p mydatabasename > dbdump.sql

Question - 19 : - How do you change a password for an existing user via mysqladmin?

Answer - 19 : - mysqladmin -u root -p password "newpassword"

Question - 20 : - What’s the default port for MySQL Server?

Answer - 20 : - 3306

Computer Contributors

krishan
Rajeev Katiyar
Yes Baroda

Share your email for latest updates

Name:
Email:

Our partners