MS SQL Interview Questions and Answers

Answer - 31 : - Introduced in MySQL 5.0, federated tables allow access to the tables located on other databases on other servers.

Answer - 32 : - 999.99 to -99.99. Note that with the negative number the minus sign is considered one of the digits.

Answer - 33 : - Create two columns of type TIMESTAMP and use the second one for your real data.

Answer - 34 : - On initialization places a zero in that column, on future updates puts the current value of the timestamp in.

Answer - 35 : - CHAR(3), since MySQL automatically adjusted the data type.

Answer - 36 : - MySQL, the most popular Open Source SQL database, is provided by MySQL AB. MySQL AB is a commercial company that builds is business providing services around the MySQL database. See section 1.2 What Is MySQL AB. MSQL is a database management system. A database is a structured collection of data. It may be anything from a simple shopping list to a picture gallery or the vast amounts of information in a corporate network. To add, access, and process data stored in a computer database, you need a database management system such as MySQL. Since computers are very good at handling large amounts of data, database management plays a central role in computing, as stand-alone utilities, or as parts of other applications. MySQL is a relational database management system. A relational database stores data in separate tables rather than putting all the data in one big storeroom. This adds speed and flexibility. The tables are linked by defined relations making it possible to combine data from several tables on request. The SQL part of MySQL stands for "Structured Query Language" - the most common standardized language used to access databases. MySQL is Open Source Software. Open source means that it is possible for anyone to use and modify. Anybody can download MySQL from the Internet and use it without paying anything. Anybody so inclined can study the source code and change it to fit their needs. MySQL uses the GPL (GNU General Public License) http://www.gnu.org, to define what you may and may not do with the software in different situations. If you feel uncomfortable with the GPL or need to embed MySQL into a commercial application you can buy a commercially licensed version from us.

Answer - 37 : - The technical features of MySQL For advanced technical information, see section 7 MySQL Language Reference. MySQL is a client/server system that consists of a multi-threaded SQL server that supports different backends, several different client programs and libraries, administrative tools, and a programming interface. We also provide MySQL as a multi-threaded library which you can link into your application to get a smaller, faster, easier to manage product. MySQL has a lot of contributed software available. It is very likely that you will find that your favorite application/language already supports MySQL. The official way to pronounce MySQL is ``My Ess Que Ell'' (not MY-SEQUEL). But we try to avoid correcting people who say MY-SEQUEL. The Main Features of MySQL The following list describes some of the important characteristics of MySQL: Fully multi-threaded using kernel threads. That means it can easily use multiple CPUs if available. C, C++, Eiffel, Java, Perl, PHP, Python and Tcl APIs. Works on many different platforms. Many column types: signed/unsigned integers 1, 2, 3, 4, and 8 bytes long, FLOAT, DOUBLE, CHAR, VARCHAR, TEXT, BLOB, DATE, TIME, DATETIME, TIMESTAMP, YEAR, SET, and ENUM types. Very fast joins using an optimized one-sweep multi-join. Full operator and function support in the SELECT and WHERE parts of queries. Example: mysql> SELECT CONCAT(first_name, " ", last_name) FROM tbl_name WHERE income/dependents > 10000 AND age > 30; SQL functions are implemented through a highly optimized class library and should be as fast as they can get! Usually there shouldn't be any memory allocation at all after query initialization. Full support for SQL GROUP BY and ORDER BY clauses. Support for group functions (COUNT(), COUNT(DISTINCT), AVG(), STD(), SUM(), MAX() and MIN()). Support for LEFT OUTER JOIN and RIGHT OUTER JOIN with ANSI SQL and ODBC syntax. You can mix tables from different databases in the same query (as of Version 3.22). A privilege and password system that is very flexible and secure and allows host-based verification. Passwords are secure because all password traffic is encrypted when you connect to a server. ODBC (Open-DataBase-Connectivity) support for Win32 (with source). All ODBC 2.5 functions and many others. For example, you can use MS Acce

Answer - 38 : - You need the following tools to install a MySQL binary distribution: GNU gunzip to uncompress the distribution. A reasonable tar to unpack the distribution. GNU tar is known to work. Sun tar is known to have problems. An alternative installation method under Linux is to use RPM (RedHat Package Manager) distributions. If you run into problems, PLEASE ALWAYS USE mysqlbug when posting questions to mysql@lists.mysql.com. Even if the problem isn't a bug, mysqlbug gathers system information that will help others solve your problem. By not using mysqlbug, you lessen the likelihood of getting a solution to your problem! You will find mysqlbug in the `bin' directory after you unpack the distribution. The basic commands you must execute to install and use a MySQL binary distribution are: shell> groupadd mysql shell> useradd -g mysql mysql shell> cd /usr/local shell> gunzip < /path/to/mysql-VERSION-OS.tar.gz | tar xvf - shell> ln -s mysql-VERSION-OS mysql shell> cd mysql shell> scripts/mysql_install_db shell> chown -R mysql /usr/local/mysql shell> chgrp -R mysql /usr/local/mysql shell> bin/safe_mysqld --user=mysql & You can add new users using the bin/mysql_setpermission script if you install the DBI and Msql-Mysql-modules Perl modules. A more detailed description follows. Pick the directory under which you want to unpack the distribution, and move into it. In the example below, we unpack the distribution under `/usr/local' and create a directory `/usr/local/mysql' into which MySQL is installed. (The following instructions therefore assume you have permission to create files in `/usr/local'. If that directory is protected, you will need to perform the installation as root.)

Answer - 39 : - The following mostly applies only for ISAM, MyISAM, and HEAP tables. If you only use transaction-safe tables (BDB tables) in an a update, you can do COMMIT and ROLLBACK also with MySQL. The problem with handling COMMIT-ROLLBACK efficiently with the above table types would require a completely different table layout than MySQL uses today. The table type would also need extra threads that do automatic cleanups on the tables, and the disk usage would be much higher. This would make these table types about 2-4 times slower than they are today. For the moment, we prefer implementing the SQL server language (something like stored procedures). With this you would very seldom really need COMMIT-ROLLBACK. This would also give much better performance. Loops that need transactions normally can be coded with the help of LOCK TABLES, and you don't need cursors when you can update records on the fly. We at TcX had a greater need for a real fast database than a 100% general database. Whenever we find a way to implement these features without any speed loss, we will probably do it. For the moment, there are many more important things to do. Check the TODO for how we prioritize things at the moment. (Customers with higher levels of support can alter this, so things may be reprioritized.) The current problem is actually ROLLBACK. Without ROLLBACK, you can do any kind of COMMIT action with LOCK TABLES. To support ROLLBACK with the above table types, MySQL would have to be changed to store all old records that were updated and revert everything back to the starting point if ROLLBACK was issued. For simple cases, this isn't that hard to do (the current isamlog could be used for this purpose), but it would be much more difficult to implement ROLLBACK for ALTER/DROP/CREATE TABLE. To avoid using ROLLBACK, you can use the following strategy: Use LOCK TABLES ... to lock all the tables you want to access. Test conditions. Update if everything is okay. Use UNLOCK TABLES to release your locks. This is usually a much faster method than using transactions with possible ROLLBACKs, although not always. The only situation this solution doesn't handle is when someone kills the threads in the middle of an update. In this case, all locks will be released but some of the updates may not have been executed. You can also use functions to update records in a

Answer - 40 : - The primary function of the MySQL privilege system is to authenticate a user connecting from a given host, and to associate that user with privileges on a database such as select, insert, update and delete. Additional functionality includes the ability to have an anonymous user and to grant privileges for MySQL-specific functions such as LOAD DATA INFILE and administrative operations. MySQL User Names and Passwords There are several distinctions between the way user names and passwords are used by MySQL and the way they are used by Unix or Windows: User names, as used by MySQL for authentication purposes, have nothing to do with Unix user names (login names) or Windows user names. Most MySQL clients by default try to log in using the current Unix user name as the MySQL user name, but that is for convenience only. Client programs allow a different name to be specified with the -u or --user options. This means that you can't make a database secure in any way unless all MySQL user names have passwords. Anyone may attempt to connect to the server using any name, and they will succeed if they specify any name that doesn't have a password. MySQL user names can be up to 16 characters long; Unix user names typically are limited to 8 characters. MySQL passwords have nothing to do with Unix passwords. There is no necessary connection between the password you use to log in to a Unix machine and the password you use to access a database on that machine. MySQL encrypts passwords using a different algorithm than the one used during the Unix login process. Note that even if the password is stored 'scrambled', and knowing your 'scrambled' password is enough to be able to connect to the MySQL server! Connecting to the MySQL Server MySQL client programs generally require that you specify connection parameters when you want to access a MySQL server: the host you want to connect to, your user name, and your password. For example, the mysql client can be started like this (optional arguments are enclosed between `[' and `]'): shell> mysql [-h host_name] [-u user_name] [-pyour_pass] Alternate forms of the -h, -u, and -p options are --host=host_name, --user=user_name, and --password=your_pass. Note that there is no space between -p or --password= and the password following