MySQL Interview Questions -2

MySQL Interview Questions -2

Q.What Is MySQL?
MySQL is an open source database management system developed by MySQL AB.

Q.What Is Column?
A column defines one piece of data stored in all rows of the table.

Q.What Is CSV?
CSV (Comma Separated Values) is a file format used to store database table contents, where one table row is stored as one line in the file, and each data field is separated with comma.

Q.What Is Rollback?
Rollback is a way to terminate a transaction with all database changes not saving to the database server.

Q.How Do You Know the Version of Your MySQL Server?
If you want to know the version number of your MySQL server, you can use the "mysqladmin" program in a command window as shown in the following tutorial:
>cd mysqlin
>mysqladmin -u root version
mysqladmin Ver 8.41 Distrib 5.0.24, for Win32 on ia32
Copyright (C) 2000 MySQL AB & MySQL Finland AB
& TCX DataKonsult AB
...
Server version 5.0.24-community
Protocol version 10
Connection localhost via TCP/IP
TCP port 3306
Uptime: 25 min 9 sec
Threads: 1 Questions: 2 Slow queries: 0 Opens: 12
Flush tables: 1 Open tables: 6
Queries per second avg: 0.001
The output in the above example tells you that the version number is 5.0.24.

Q.What Is "mysqld"?
"mysqld" is MySQL server daemon program which runs quietly in background on your computer system. Invoking "mysqld" will start the MySQL server on your system. Terminating "mysqld" will shutdown the MySQL server. Here is a tutorial example of invoking "mysqld" with the "--console" option:
>cd mysqlin
>mysqld --console
... 21:52:54 InnoDB: Started; log sequence number 0 43655
... 21:52:54 [Note] mysqld: ready for connections.
Version: '5.0.24-community' socket: '' port: 3306
MySQL Community Edition (GPL)
The returning message indicates that "mysqld" running now, and your MySQL server is ready to take client connections. To know about "mysqld", read other parts of this FAQ collection.

Q.How To Shut Down the Server with "mysqladmin"?
If you want to shut down the server with "mysqladmin", you can use the command "mysqladmin shutdown" as shown in the following tutorial example:
>cd mysqlin
>mysqladmin -u root shutdown
If this command returns no messages, your MySQL server should be terminated successfully.

Q.How To Use "mysql" to Run SQL Statements?
If you want to run SQL statement to your server with "mysql", you need to start "mysql" and enter your SQL statement at the "mysql" prompt. Here is a good tutorial exercise that shows you how to run two SQL statements with "mysql":
>cd mysqlin
>mysql -u root test
Welcome to the MySQL monitor. Commands end with ; or g.
Your MySQL connection id is 4 to server version: 5.0.24
mysql> CREATE TABLE links (name VARCHAR(80));
Query OK, 0 rows affected (0.10 sec)
mysql> INSERT INTO links VALUES ('www.vyomtechnosoft.com');
Query OK, 1 row affected (0.02 sec)
mysql> quit;
Bye

Q.How To Dump a Table to a File with "mysqldump"
If you want to dump all rows in a table from the server to a file, you can use "mysqldump" with the "-f fileName" option as show in the following tutorial exercise:
>cd mysqlin
>mysqldump -u root -r emplinks.txt test links
>type emplinks.txt
>type emplinks.txt | more
-- MySQL dump 10.10
-- Host: localhost Database: test
-- Server version 5.0.24-community
The dump file contains SQL statements that you can use to restore the table and its data content.

Q.What Is the Command Line End User Interface - mysql?
"mysql", official name is "MySQL monitor", is a command-line interface for end users to manage user data objects. "mysql" has the following main features:
"mysql" is command line interface. It is not a Graphical User Interface (GUI).
"mysql" supports all standard SQL Data Definition Language (DDL) commands for the server to execute.
"mysql" supports all standard SQL Data Manipulation Language (DML) commands for the server to execute.
"mysql" supports many of non-SQL commands that "mysql" will execute by itself.
"mysql" provides access to the server-side help system.
"mysql" allows command files to be executed in a batch mode.
"mysql" allows query output to be formatted as HTML tables.
"mysql" allows query output to be formatted as XML elements.

Q.How To Return Query Output in HTML Format?
By default, "mysql" returns query output in text table format. If you want to receive query output in HTML format, you need to use the "-H" command option. Here is a good tutorial exercise:
>cd mysqlin
>mysql -u root -H test
mysql> SELECT * FROM links;
<TABLE BORDER=1><TR><TH>id</TH><TH>name</TH></TR>
<TR><TD>1</TD><TD>www.vyomtchnosoft.com</TD></TR>
<TR><TD>10</TD><TD>www.vyomtchnosoft.com</TD></TR></TABLE>
2 rows in set (0.00 sec)

Q.How To Return Query Output in XML Format?
By default, "mysql" returns query output in text table format. If you want to receive query output in XML format, you need to use the "-X" command option. Here is a good tutorial exercise:
>cd mysqlin
>mysql -u root -X test
mysql> SELECT * FROM links;
<xml version="1.0">
<resultset statement="SELECT * FROM links">
<row>
<field name="id">1</field>
<field name="name">www.vyomtechnosoft.com</field>
</row>
<row>
<field name="id">10</field>
<field name="name">www.vyomtechnosoft.com</field>
</row>
</resultset>
2 rows in set (0.00 sec)

Q.What Are the Differences between CHAR and NCHAR?Both CHAR and NCHAR are fixed length string data types. But they have the following differences:
CHAR's full name is CHARACTER.
NCHAR's full name is NATIONAL CHARACTER.
By default, CHAR uses ASCII character set. So 1 character is always stored as 1 byte.
By default, NCHAR uses Unicode character set. NCHAR data are stored in UTF8 format. So 1
character could be stored as 1 byte or upto 4 bytes.
Both CHAR and NCHAR columns are defined with fixed lengths in units of characters.
The following column definitions are the same:
CREATE TABLE faq (Title NCHAR(80));
CREATE TABLE faq (Title NATIONAL CHAR(80));
CREATE TABLE faq (Title NATIONAL CHARACTER(80));
CREATE TABLE faq (Title CHAR(80) CHARACTER SET utf8);
CREATE TABLE faq (Title CHARACTER(80) CHARACTER SET utf8);

Q.What Are the Differences between CHAR and VARCHAR?

CHAR and VARCHAR are both ASCII character data types by default. But they have the following major differences:
CHAR stores values in fixed lengths. Values are padded with space characters to match the specified length.
VARCHAR stores values in variable lengths. Values are not padded with any characters. But 1 or 2 extra bytes are added to store the length of the data.
The table below shows you a good comparison of CHAR and VARCHAR data types:
Value CHAR(4) Length
'' ' ' 4 bytes
'ab' 'ab ' 4 bytes
'abcd' 'abcd' 4 bytes
Value VARCHAR(4) Length
'' '' 1 byte
'ab' 'ab' 3 bytes
'abcd' 'abcd' 5 bytes

Q.What Are Date and Time Data Types?

MySQL supports the following date and time data types:
DATE - A date in the range of '1000-01-01' and '9999-12-31'. Default DATE format is "YYYY-MM-DD".
DATETIME - A date with the time of day in the range of '1000-01-01 00:00:00' and '9999-12-31 23:59:59'. Default DATETIME format is "YYYY-MM-DD HH:MM:SS".
TIMESTAMP - A timestamp. The range is '1970-01-01 00:00:00' to partway through the year 2037.
Default DATETIME format is "YYYY-MM-DD HH:MM:SS".
TIME - A time. The range is '-838:59:59' to '838:59:59'. Default TIME format is "HH:MM:SS".
TIME - A time. The range is '-838:59:59' to '838:59:59'. Default TIME format is "HH:MM:SS".
YEAR - A year in 4 digits in the range of 1901 and 2155. Default YEAR format is "YYYY".

Q.How To Calculate Expressions with SQL Statements?

There is no special SQL statements to calculate expressions. But you can use the "SELECT expression FROM DUAL" statement return the calculated value of an expression. "DUAL" is a dummy table in the server. The tutorial exercise below shows you some good examples:
SELECT 'Hello world!' FROM DUAL;
Hello world!
SELECT (1+2)*3/4 FROM DUAL;
2.2500
SELECT TRUE FROM DUAL;
1
SELECT TRUE AND FALSE FROM DUAL;
0
SELECT TIME(SYSDATE()) FROM DUAL;
21:30:26

Q.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.

Q.How can you see all indexes defined for a table?

SHOW INDEX 

Q.What are ENUMs used for in MySQL?

You can limit the possible values that go into the table. CREATE TABLE months (month ENUM ‘January’, ‘February’, ‘March’,…); INSERT months VALUES (’April’);

Q.How are ENUMs and SETs represented internally?

As unique integers representing the powers of two, due to storage optimizations.

Q.Have you ever used MySQL Administrator and MySQL Query Browser?

Describe the tasks you accomplished with these tools.

Q.What does myisamchk do?

It compressed the MyISAM tables, which reduces their disk usage.

Q.Explain data type TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP ?

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

Q.What does TIMESTAMP ON UPDATE CURRENT_TIMESTAMP data type do?

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

Q.How MySQL Optimizes DISTINCT ?

DISTINCT is converted to a GROUP BY on all columns, DISTINCT combined with ORDER BY will in many cases also need a temporary table.
When combining LIMIT # with DISTINCT, MySQL will stop as soon as it finds # unique rows.
If you don't use columns from all used tables, MySQL will stop the scanning of the not used tables as soon as it has found the first match.
SELECT DISTINCT t1.a FROM t1,t2 where t1.a=t2.a;
In the case, assuming t1 is used before t2 (check with EXPLAIN), then MySQL will stop reading from t2 (for that particular row in t1) when the first row in t2 is found.

Q.How MySQL Optimizes LEFT JOIN and RIGHT JOIN ?

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

Q.MySQL - Speed of UPDATE Queries ?

Update queries are optimized as a SELECT query with the additional overhead of a write. The speed of the write is dependent on the size of the data that is being updated and the number of indexes that are updated. Indexes that are not changed will not be updated.
Also, another way to get fast updates is to delay updates and then do many updates in a row later. Doing many updates in a row is much quicker than doing one at a time if you lock the table.
Note that, with dynamic record format, updating a record to a longer total length may split the record. So if you do this often, it is very important to OPTIMIZE TABLE sometimes.

Q.MySQL - Speed of DELETE Queries ?

If you want to delete all rows in the table, you should use TRUNCATE TABLE table_name.
The time to delete a record is exactly proportional to the number of indexes. To delete records more quickly, you can increase the size of the index cache.

Q.What Are NULL Values?

NULL is a special value that represents no value. Here are basic rules about NULL values:
NULL presents no value.
NULL is not the same as an empty string ''.
NULL is not the same as a zero value 0.
NULL can be used as any data type.
NULL should not be used in any comparison options.
NULL has its own equality operator "IS".
NULL has its own not-equality operator "IS NOT".
The tutorial exercise shows you some interesting examples:
SELECT 0 IS NULL FROM DUAL;
0
SELECT 0 IS NOT NULL FROM DUAL;
1
SELECT '' IS NULL FROM DUAL;
0
SELECT '' IS NOT NULL FROM DUAL;
1
SELECT NULL IS NULL FROM DUAL;
1
SELECT NULL IS NOT NULL FROM DUAL;
0

Q.How To Convert Character Strings to Numeric Values?

You can convert character strings to numeric values by using the CAST(string AS DECIMAL) or CAST(string AS SIGNED INTEGER) function as shown in the following examples:
SELECT CAST('4123.45700' AS DECIMAL) FROM DUAL;
4123.46
-- Very poor conversion
SELECT CAST('4.12345700e+3' AS DECIMAL) FROM DUAL;
4123.46
-- Very poor conversion
SELECT CAST('4123.45700' AS SIGNED INTEGER) FROM DUAL;
4123
SELECT CAST('4.12345700e+3' AS SIGNED INTEGER) FROM DUAL;
4

Q.What Is CSV?

CSV (Comma Separated Values) is a file format used to store database table contents, where one table row is stored as one line in the file, and each data field is separated with comma.

Q.How To Write Date and Time Literals?

MySQL offers a number of formats for you to use to enter date and time literals:
ANSI standard format: "YYYY-MM-DD HH:MM:SS".
Non-standard limiters. Like: "YYYY/MM/DD HH^MM^SS" or "YYYY.MM.DD HH-MM-SS".
No limiters. Like: "YYYYMMDD" for a date or "HHMMSS" for a time.
Decimal numbers. Like: 8 digits dddddddd for a date or 6 digits dddddd for a time.
The tutorial exercise below gives you some good examples:
SELECT DATE('1997-01-31') FROM DUAL;
1997-01-31
SELECT DATE('1997-01-31 09:26:50') FROM DUAL;
1997-01-31
SELECT TIME('1997-01-31 09:26:50') FROM DUAL;
09:26:50
SELECT DATE('1997/01/31 09^26^50') FROM DUAL;
1997-01-31
SELECT TIME('1997/01/31 09^26^50') FROM DUAL;
09:26:50
SELECT DATE('19970131') FROM DUAL;
1997-01-31
SELECT TIME('092650') FROM DUAL;
09:26:50
SELECT DATE(19970131) FROM DUAL; -- Crazy format
1997-01-31
SELECT TIME(092650) FROM DUAL; -- Crazy format
09:26:50

Q.What Is Row?

A row is a unit of data with related data items stored as one item in one column in a table.

Q.What Is Foreign Key?

A foreign key is a single column or multiple columns defined to have values that can be mapped to a primary key in another table.

Q.How To Create a Test Table in Your MySQL Server?

If you want to create a test table in your MySQL server, you can use the "mysql" program in a command window as shown in the following tutorial:
>cd mysqlin
>mysql -u root
Welcome to the MySQL monitor. Commands end with ; or g.
Your MySQL connection id is 8 to server version: 5.0.24-
mysql> use test
Database changed
mysql> CREATE TABLE test (message VARCHAR(80));
Query OK, 0 rows affected (0.13 sec)
mysql> INSERT INTO test
-> VALUES ('Welcome to GlobalGuideLine.com');
Query OK, 1 row affected (0.03 sec)
mysql> SELECT * FROM test;
+---------------------------------+
| message |
+---------------------------------+
| Welcome to GlobalGuideLine.come |
+---------------------------------+
1 row in set (0.00 sec)
mysql> DROP TABLE test;
Query OK, 0 rows affected (0.01 sec)
mysql> quit
Bye

Q.How To Check Server Status with "mysqladmin"?

If you want to check the server status by with "mysqladmin", you can following this tutorial example:
>cd mysqlin
>mysqladmin -u root status
Uptime: 223 Threads: 1 Questions: 1 Slow queries: 0
Opens: 12 Flush tables: 1 Open tables: 6
Queries per second avg: 0.004
The returning message indicates that the server is almost doing nothing at this moment.

Q.What Are the "mysql" Command Line Arguments?

"mysql" supports only one optional command line argument, "database". But "mysql" allows the operating system to redirect input and output streams at the command line level. Here are some good examples:
"mysql databaseName" - Starts "mysql" in interactive mode and use the specified database.
"mysql < fileName" - Starts "mysql" in batch mode and executes all commands in the specified file.
"mysql < fileName > fileName" - Starts "mysql" in batch mode, executes all commands in the specified file, and write output to the specified file.
Here is a tutorial exercise of how to use the command line argument to specify the database to use:
>cd mysqlin
>mysql -u root test
Welcome to the MySQL monitor. Commands end with ; or g.
Your MySQL connection id is 4 to server version: 5.0.24
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| links |
+----------------+
1 row in set (0.00 sec)
mysql> quit;
Bye

Q.How Many SQL DDL Commands Are Supported by "mysql"?

There are 4 SQL Data Definition Language (DDL) commands that are supported by "mysql". They are listed below with short descriptions:
"CREATE dataObjectType dataObjectName" - Creates new databases, tables, views, triggers, indexes, and other data objects.
"RENAME dataObjectType dataObjectName" - Renames existing databases, tables, views, triggers, indexes, and other data objects.
"ALTER dataObjectType dataObjectName" - Alters properties of existing databases, tables, views, triggers, indexes, and other data objects.
"DROP dataObjectType dataObjectName" - Drops existing databases, tables, views, triggers, indexes, and other data objects.
Here is a tutorial exercise of how to use DDL commands to create a database and a table:
>cd mysqlin
>mysql -u root
mysql> CREATE DATABASE ggl;
Query OK, 1 row affected (0.50 sec)
mysql> CREATE TABLE articles (name VARCHAR(80));
Query OK, 0 rows affected (0.25 sec)
mysql> DROP DATABASE ggl;
Query OK, 0 rows affected (0.41 sec)

Q.How Many Groups of Data Types?

MySQL support 3 groups of data types as listed below:
String Data Types - CHAR, NCHAR, VARCHAR, NVARCHAR, BINARY, VARBINARY, TINYBLOB, TINYTEXT, BLOB, TEXT, MEDIUMBLOB, MEDIUMTEXT, LONGBLOB, LONGTEXT, ENUM, SET
Numeric Data Types - BIT, TINYINT, BOOLEAN, SMALLINT, MEDIUMINT, INTEGER, BIGINT, FLOAT, DOUBLE, REAL, DECIMAL.
Date and Time Data Types - DATE, DATETIME, TIMESTAMP, TIME, YEAR.

Q.What Are String Data Types?

MySQL supports the following string data types:
CHAR(n) same as CHARACTER(n) - Fixed width and " " padded characters strings.
Default character set is ASCII.
NCHAR(n) same as NATIONAL CHARACTER(n) - Fixed width and " " padded character strings with UTF8 character set.
VARCHAR(n) same as CHARACTER VARYING(n) - Variable width character strings. Default character set is ASCII.
NVARCHAR(n) same as NATIONAL CHARACTER VARYING(n) - Variable width character strings with UTF8 character set.
BINARY(n) - Fixed width and 0x00 padded byte strings.
VARBINARY(n) same as BINARY VARYING(n) - Variable width byte string.
TINYBLOB - BLOB (Binary Large Object) upto 255 bytes.
BLOB - BLOB (Binary Large Object) upto 64K bytes.
MEDIUMBLOB - BLOB (Binary Large Object) upto 16M bytes.
LONGBLOB - BLOB (Binary Large Object) upto 4G bytes.
TINYTEXT - CLOB (Binary Large Object) upto 255 characters.
TEXT - CLOB (Binary Large Object) upto 64K characters.
MEDIUMTEXT - CLOB (Binary Large Object) upto 16M characters.
LONGTEXT - CLOB (Binary Large Object) upto 4G characters.
ENUM - An enumeration to hold one entry of some pre-defined strings.
SET - A set to hold zero or more entries of some pre-defined strings.

Q.How To Include Comments in SQL Statements?

If you want to include comments in a SQL statement, you can first enter "--", then enter your comment until the end of the line. The tutorial exercise below shows you some good examples:
SELECT 'Hello world!' FROM DUAL; -- My first SQL statement!
INSERT INTO links VALUES ('GlobalGuideLine.com'); -- Top rated!
CREATE TABLE faq (
id INTEGER, -- primary key
title VARCHAR(80) -- FAQ title
);

Q.How To Include Character Strings in SQL statements?

If you want to include character strings in your SQL statements, you need to quote them in one of the following formats:
Using single quotes. For example 'GlobalGuideLine.com'.
Using double quotes. For example "ggl Center".
Using single quotes prefixed with N for NATIONAL characters (same as UTF8 characters). For example N'Allo, Francois.'.
Using single quotes prefixed with _utf8 for UTF8 characters. For example _utf8'Allo, Francois.'

Q.How would you change a table to InnoDB?

ALTER TABLE techpreparation_questions ENGINE innodb;

Q.How do you concatenate strings in MySQL?

CONCAT (string1, string2, string3)

Q.How do you start MySQL on Linux?

/etc/init.d/mysql start

Q.Explain advantages of InnoDB over MyISAM?

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

Q.How do you get a portion of a string?

SELECT SUBSTR(title, 1, 10) from techpreparation_questions;

Q.What’s the difference between CHAR_LENGTH and LENGTH?

The first is, naturally, the character count. The second is byte count. For the Latin characters the numbers are the same, but they’re not the same for Unicode and other encodings.

Q.What happens when the column is set to AUTO INCREMENT and you reach the maximum value for that table?

It stops incrementing. It does not overflow to 0 to prevent data losses, but further inserts are going to produce an error, since the key has been used already.

Q.If you specify the data type as DECIMAL (5,2), what’s the range of values that can go in this table?

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

0 comments:

Post a Comment