• +91 9971497814
  • info@interviewmaterial.com

Computer Interview Questions Answers

MS SQL Interview Questions Answers

Question - 41 : - How MySQL Optimizes LEFT JOIN and RIGHT JOIN ?

Answer - 41 : - A LEFT JOIN B in MySQL is implemented as follows: The table B is set to be dependent on table A and all tables that A is dependent on. The table A is set to be dependent on all tables (except B) that are used in the LEFT JOIN condition. All LEFT JOIN conditions are moved to the WHERE clause. All standard join optimizations are done, with the exception that a table is always read after all tables it is dependent on. If there is a circular dependence then MySQL will issue an error. All standard WHERE optimizations are done. If there is a row in A that matches the WHERE clause, but there wasn't any row in B that matched the LEFT JOIN condition, then an extra B row is generated with all columns set to NULL. If you use LEFT JOIN to find rows that don't exist in some table and you have the following test: column_name IS NULL in the WHERE part, where column_name is a column that is declared as NOT NULL, then MySQL will stop searching after more rows (for a particular key combination) after it has found one row that matches the LEFT JOIN condition. RIGHT JOIN is implemented analogously as LEFT JOIN. The table read order forced by LEFT JOIN and STRAIGHT JOIN will help the join optimizer (which calculates in which order tables should be joined) to do its work much more quickly, as there are fewer table permutations to check. Note that the above means that if you do a query of type: SELECT * FROM a,b LEFT JOIN c ON (c.key=a.key) LEFT JOIN d (d.key=a.key) WHERE b.key=d.key MySQL will do a full scan on b as the LEFT JOIN will force it to be read before d. The fix in this case is to change the query to: SELECT * FROM b,a LEFT JOIN c ON (c.key=a.key) LEFT JOIN d (d.key=a.key) WHERE b.key=d.key

Question - 42 : - How can you ensure that a table named TableB with a field named Fld1 will only have those values in the Fld1 field that are also in the table named TableA with a field named Fld1?

Answer - 42 : - This relationship related question has two potential answers. The first answer (and the one that you want to hear) is the use of foreign key constraints. A foreign key constraint is used to maintain referential integrity. It is used to ensure that a field in a table will only hold values that are already defined in another field in a different (or the same) table. That field is the candidate key (usually a primary key of the other table). The other option is the use of triggers. Triggers can be used to ensure the same effect of constraints in a roundabout way, but it is much more difficult to set up and maintain, and the performance is typically worse. Because of this, Microsoft recommends that developers use foreign key constraints instead of triggers for maintaining referential integrity.

Question - 43 : - How can I use replication to improve performance of my system?

Answer - 43 : - You should set up one server as the master, and direct all writes to it, and configure as many slaves as you have the money and rackspace for, distributing the reads among the master and the slaves. You can also start the slaves with --skip-bdb, --low-priority-updates and --delay-key-write-for-all-tables to get speed improvements for the slave. In this case the slave will use non-transactional MyISAM tables instead of BDB tables to get more speed.

Question - 44 : - what are the features of MySQL ?

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

Question - 45 : - What are some good ideas regarding user security in MySQL?

Answer - 45 : - There is no user without a password. There is no user without a user name. There is no user whose Host column contains % (which here indicates that the user can log in from anywhere in the network or the Internet). There are as few users as possible (in the ideal case only root) who have unrestricted access.

Question - 46 : - How do you control the max size of a HEAP table?

Answer - 46 : - MySQL config variable max_heap_table_size.

Question - 47 : - What does myisamchk do?

Answer - 47 : - It compressed the MyISAM tables, which reduces their disk usage.

Question - 48 : - What are triggers? What are the different types of triggers in SQL Server 2000?

Answer - 48 : - It's very beneficial for a potential database developer to know the types of triggers available, and how to implement them. A trigger is a specialized type of stored procedure that is bound to a table or view in SQL Server 2000. In SQL Server 2000, there are INSTEAD-OF triggers and AFTER triggers. INSTEAD-OF triggers are procedures that execute in place of a Data Manipulation Language (DML) statement on a table. For example, if I have an INSTEAD-OF-UPDATE trigger on TableA, and I execute an update statement on that table, the code in the INSTEAD-OF-UPDATE trigger will execute instead of the update statement that I executed. An AFTER trigger executes after a DML statement has taken place in the database. These types of triggers are very handy for auditing data changes that have occurred in your database tables.

Question - 49 : - What Is MySQL?

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

Question - 50 : - Privileges Provided by MySQL ?

Answer - 50 : - Information about user privileges is stored in the user, db, host, tables_priv, and columns_priv tables in the mysql database (that is, in the database named mysql). The names used in this manual to refer to the privileges provided by MySQL are shown below, along with the table column name associated with each privilege in the grant tables and the context in which the privilege applies: Privilege Column Context select Select_priv tables insert Insert_priv tables update Update_priv tables delete Delete_priv tables index Index_priv tables alter Alter_priv tables create Create_priv databases, tables, or indexes drop Drop_priv databases or tables grant Grant_priv databases or tables references References_priv databases or tables reload Reload_priv server administration shutdown Shutdown_priv server administration process Process_priv server administration file File_priv file access on server The select, insert, update, and delete privileges allow you to perform operations on rows in existing tables in a database. SELECT statements require the select privilege only if they actually retrieve rows from a table. You can execute certain SELECT statements even without permission to access any of the databases on the server. For example, you could use the mysql client as a simple calculator: mysql> SELECT 1+1; mysql> SELECT PI()*2; The index privilege allows you to create or drop (remove) indexes. The alter privilege allows you to use ALTER TABLE. The create and drop privileges allow you to create new databases and tables, or to drop (remove) existing databases and tables. Note that if you grant the drop privilege for the mysql database to a user, that user can drop the database in which the MySQL access privileges are stored! The grant privilege allows you to give to other users those privileges you yourself possess. The file privilege gives you permission to read and write files on the server using the LOAD DATA INFILE and SELECT ... INTO OUTFILE statements. Any user to whom this privilege is

Computer Contributors

Rajeev Katiyar
Yes Baroda

Share your email for latest updates


Our partners