MySQL Interview Questions -1

MySQL Interview Questions -1
Q.How do you find out which auto increment was assigned on the last insert?
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.

Q.What does + mean in REGEXP?

At least one character. Appendix G. Regular Expressions from MySQL manual is worth perusing before the interview.

Q.How do you get the month from a timestamp?

SELECT MONTH(techpreparation_timestamp)

Q.How do you change a password for an existing user via mysqladmin?

mysqladmin -u root -p password "newpassword"

Q.Use mysqldump to create a copy of the database?

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

Q.Explain federated tables. ?

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

Q.What is SERIAL data type in MySQL?

BIGINT NOT NULL PRIMARY KEY AUTO_INCREMENT

Q.Explain TIMESTAMP DEFAULT ‘2006:09:02 17:38:44? ON UPDATE CURRENT_TIMESTAMP. ?

A default value is used on initialization, a current timestamp is inserted on update of the row.

Q.If I created a column with data type VARCHAR(3), what would I expect to see in MySQL table?

CHAR(3), since MySQL automatically adjusted the data type.

Q.How to connect mysql from jsp(Java Server Page)?

<%<br>String connectionURL = "jdbc:mysql://localhost:3306/database_name";<br> Connection connection = null;<br> Statement statement = null;<br><br>Class.forName("com.mysql.jdbc.Driver").newInstance();<br> connection = DriverManager.getConnection(connectionURL, "database_username", "database_password");<br> statement = connection.createStatement(); <br><br>%>

Q.Explain the difference between MyISAM Static and MyISAM Dynamic

MyISAM static all the fields have fixed width. The Dynamic MyISAM table would include fields such as TEXT, BLOB, etc. to accommodate the data types with various lengths. MyISAM Static would be easier to restore in case of corruption, since even though you might lose some data, you know exactly where to look for the beginning of the next record.

Q.What are some good ideas regarding user security in MySQL?

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.

Q.What is the Oracle rowid counterpart in MySQL?

As row id is in oracle same as id is in mysql but in mysql there is no concept of rownum.

Q.When is a declare statement needed ?

The DECLARE statement is used in PL/SQL anonymous blocks such as with stand alone, non-stored PL/SQL procedures. It must come first in a PL/SQL stand alone file if it is used.

Q.Explain about MYSQL and its features

MYSQL is a relational data base model and most widely RDBMS all over the world. It provides multi user access to databases. Source code for this database is available under General public license. It became popular for web applications and this is widely used. Performance and reliability became its features.

Q.Use mysqldump to create a copy of the database?

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

Q.What are the advantages of Mysql comparing with oracle?

MySql has many advantages in comparison with Oracle.MySql is Open source, which can be available any time.MySql has no cost of development purpose.MySql has most of features , which oracle provides.MySql day by day updating with new facilities.Good for small application.easy to learn and to become master.MySql has a good power these days.even though MySql having so many advantages, Oracle is best database ever in Software development.

Q.How to create MYSQL new users?

There are many different ways to establish users and privileges in MYSQL. Client and GRANT command assure you about a safe connection. The syntax for establishing new users and privileges is as follows

GRANT privileges ON database.* TO username@hostname This can be identified by the password. Privileges can be assigned one by one or by specifying all.

Q.How do you control the max size of a HEAP table?

MySQL config variable max_heap_table_size.

Q.What packages (if any) has Oracle provided for use by developers?

Oracle provides the DBMS_ series of packages. There are many
which developers should be aware of such as DBMS_SQL, DBMS_PIPE, DBMS_TRANSACTION,
DBMS_LOCK, DBMS_ALERT, DBMS_OUTPUT, DBMS_JOB, DBMS_UTILITY, DBMS_DDL, UTL_FILE. If they can mention a few of these and describe how they used them, even better. If they include the SQL routines provided by Oracle, great, but not really what was asked.

Q.What are HEAP tables in MySQL?

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.

Q.Explain about creating database?

CREATE DATABASE command will create you a database with the assigned name by the user. This is an optional statement but when you actually assign a name it checks for similarity and gives error if it encounters one. CREATE DATABASE models help you to create classic models.

Q.How many drivers in MYSQL?

There are eleven drivers in MYSQL .Six of them from MySQL AB and five by MYSQL Communities.They are1.PHP Driver2.ODBC Driver3.JDBC Driver4.ado.net5.mxj6.CAPI1PHP DRIVER2.PERL DRIVER3.PYTHON DRIVER4.RUBY DRIVER5.C++ WRAPPER

Q.How To Drop an Existing Index in MySQL?

If you don't need an existing index any more, you should delete it with the "DROP INDEX indexName ON tableName" statement. Here is an example SQL script:
mysql> DROP INDEX tip_subject ON tip;
Query OK, 0 rows affected (0.13 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> SHOW INDEX FROM TIP;
+------------+-------------+--------------+-------------+...

| Non_unique | Key_name | Seq_in_index | Column_name |...

+------------+-------------+--------------+-------------+...

| 0 | PRIMARY | 1 | id |...

+------------+-------------+--------------+-------------+...
1 row in set (0.00 sec)

Q.How To Rename an Existing Table in MySQL?

If you want to rename an existing table, you can use the "ALTER TABLE ... RENAME TO" statement. The tutorial script below shows you a good example:
mysql> ALTER TABLE tip RENAME TO faq;
Query OK, 0 rows affected (0.01 sec)

Q.What Is Join?

Join is data retrieval operation that combines rows from multiple tables under certain matching conditions to form a single row.

Q.What Is "mysqldump"?

"mysqldump" - A command-line interface for administrators or end users to export data from the server to files. Here are some sample commands supported by "mysqldump":
"mysqldump databaseName tableName" - Dumps the specified table in the specified database.
"mysqldump databaseName" - Dumps all the tables in the specified database.

Q.How To Enter Binary Numbers in SQL Statements?

If you want to enter character strings or numeric values as binary numbers, you can quote binary numbers with single quotes and a prefix of (B), or just prefix binary numbers with (0b). Binary numbers will be automatically converted into character strings or numeric values based on the expression contexts. Here are some good examples:
SELECT B'010000010100001001000011' FROM DUAL;
ABC
SELECT 0b1000 + 0 FROM DUAL;
8

Q.How To Enter Boolean Values in SQL Statements?

If you want to enter Boolean values in SQL statements, you use (TRUE), (FALSE), (true), or (false). Here are some good examples:
SELECT TRUE, true, FALSE, false FROM DUAL;
+------+------+-------+-------+

| TRUE | TRUE | FALSE | FALSE |

+------+------+-------+-------+

| 1 | 1 | 0 | 0 |

+------+------+-------+-------+

Q.What Is BDB (BerkeleyDB)?

BDB (BerkeleyDB) is transaction safe storage engine originally developed at U.C. Berkeley. It is now developed by Sleepycat Software, Inc. (an Oracle company now).

Q.What Are Date and Time Data Types in MySQL?

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 Convert Character Strings to Dates?

If you have a character string that represents a date, and you want to convert it into a date value, you can use the STR_TO_DATE(string, format) function. STR_TO_DATE() shares the same formatting codes with DATE_FORMAT() function. The tutorial exercise below shows you some good examples:
SELECT STR_TO_DATE('Friday, January 31, 1997',
'%W, %M %e, %Y') FROM DUAL;
1997-01-31
SELECT STR_TO_DATE('Friday, January 31, 1997, 09:26:50 AM',
'%W, %M %e, %Y, %h:%i:%s %p') FROM DUAL;
1997-01-31 09:26:50
SELECT STR_TO_DATE('31-Jan-1997 09:26:50.000123',
'%d-%b-%Y %H:%i:%s.%f') FROM DUAL;
1997-01-31 09:26:50.000123

Q.What Are Date and Time Intervals?

A date and time interval is special value to be used to increment or decrement a date or a time at a given date or time unit. A data and time interval should be expression in the format of "INTERVAL expression unit", where "unit" and "expression" should follow these rules:
unit expression value format
MICROSECOND MICROSECONDS
SECOND SECONDS
MINUTE MINUTES
HOUR HOURS
DAY DAYS
WEEK WEEKS
MONTH MONTHS
QUARTER QUARTERS
YEAR YEARS
SECOND_MICROSECOND 'SECONDS.MICROSECONDS'
MINUTE_MICROSECOND 'MINUTES.MICROSECONDS'
MINUTE_SECOND 'MINUTES:SECONDS'
HOUR_MICROSECOND 'HOURS.MICROSECONDS'
HOUR_SECOND 'HOURS:MINUTES:SECONDS'
HOUR_MINUTE 'HOURS:MINUTES'
DAY_MICROSECOND 'DAYS.MICROSECONDS'
DAY_SECOND 'DAYS HOURS:MINUTES:SECONDS'
DAY_MINUTE 'DAYS HOURS:MINUTES'
DAY_HOUR 'DAYS HOURS'
YEAR_MONTH 'YEARS-MONTHS'

Q.How To Increment Dates by 1 in MySQL?

If you have a date, and you want to increment it by 1 day, you can use the DATE_ADD(date, INTERVAL 1 DAY) function. You can also use the date interval add operation as "date + INTERVAL 1 DAY". The tutorial exercise below gives you some good examples:
SELECT DATE_ADD(DATE('1997-01-31'), INTERVAL 1 DAY)
FROM DUAL;
1997-02-01
SELECT DATE('1997-01-31') + INTERVAL 1 DAY FROM DUAL;
1997-02-01

Q.What Is Primary Key?

A primary key is a single column or multiple columns defined to have unique values that can be used as row identifications.

Q.What Is Union?

Join is data retrieval operation that combines multiple query outputs of the same structure into a single output.

Q.What Is ISAM?

ISAM (Indexed Sequential Access Method) was developed by IBM to store and retrieve data on secondary storage systems like tapes.

Q.What Is Transaction?

A transaction is a logical unit of work requested by a user to be applied to the database objects. MySQL server introduces the transaction concept to allow users to group one or more SQL statements into a single transaction, so that the effects of all the SQL statements in a transaction can be either all committed (applied to the database) or all rolled back (undone from the database).

Q.What Is Commit?

Commit is a way to terminate a transaction with all database changes to be saved permanently to the database server.

Q.How To Install MySQL?

MySQL is an open source database management system developed by MySQL AB, http://www.mysql.com. You can download a copy and install it on your local computer very easily. Here is how you can do this:
Go to http://dev.mysql.com/downloads/mysql/5.0.html.
Select the "Windows" and "Without installer" version.
Find a mirror site and download "mysql-noinstall-5.0.24-win32.zip".
Unzip the file, you will get a new sub-directory, ".mysql-5.0.24-win32".
Move and rename this sub-directory to mysql.
The installation is done and your MySQL server is ready.

Q.How To Start MySQL Server?

If you want to start the MySQL server, you can run the "mysqld" program in a command window as shown in the following tutorial:
>cd mysqlin
>mysqld
"mysqld" will run quietly without printing any message in you command window. So you will see nothing after entering the "mysqld" command. You should trust "mysqld" and believe that MySQL server is running ok on your local computer now.
Another way to start the MySQL server is double-click mysqlinmysqld.exe on your file explorer window.

Q.How To Shutdown MySQL Server?

If you want to shutdown your MySQL server, you can run the "mysqladmin" program in a command window as shown in the following tutorial:
>cd mysqlin
>mysqladmin shutdown

Q.What Tools Available for Managing MySQL Server?

MySQL comes with the following programs as administration tools for you to manage your MySQL server:
mysqld - MySQL server daemon. You can use "mysqld" to start your MySQL server.
mysqladmin - A command-line interface for administrators to perform server administration tasks.
mysql - A command-line interface for end users to manage user data objects.
mysqlcheck - A command-line interface for administrators to check and repair tables.
mysqlshow - A command-line interface for end users to see information on tables and columns.
mysqldump - A command-line interface for administrators or end users to export data from the server to files.
mysqlimport - A command-line interface for administrators or end users to load data files into tables program tool to load data into tables.

Q.How To Show All Tables with "mysql"

If you want to see all the tables in a database, you run the non-SQL command "SHOW TABLES" at the "mysql" prompt. See the following tutorial exercise for example:
>cd mysqlin
>mysql -u root test
Welcome to the MySQL monitor. Commands end with ; or g.
Your MySQL connection id is 14 to server version: 5.0.24
mysql> SHOW TABLES;
+----------------+
| Tables_in_test |
+----------------+
| links |
+----------------+
1 row in set (0.01 sec)
The output shows you that there is only one table in the "test" database.

Q.How To Analyze Tables with "mysqlcheck"?

If you want analyze tables with "mysqlcheck", you need to use the "--analyze" option. The following tutorial exercise shows you how to analyze all tables in "mysql" database:
>cd mysqlin
>mysqlcheck -u root --analyze mysql
mysql.columns_priv Table is already up to date
mysql.db Table is already up to date
mysql.func Table is already up to date
mysql.help_category Table is already up to date
mysql.help_keyword Table is already up to date
mysql.help_relation Table is already up to date
mysql.help_topic Table is already up to date
mysql.host Table is already up to date
mysql.proc Table is already up to date
mysql.tables_priv Table is already up to date
mysql.time_zone Table is already up to date
mysql.time_zone_leap_second Table is already up to date
mysql.time_zone_name Table is already up to date
mysql.time_zone_transition Table is already up to date
mysql.time_zone_transition_type Table is already up to date
mysql.user Table is already up to date

Q.What Is "mysqlimport"?

"mysqlimport" - A command-line interface for administrators or end users to load data files into tables program tool to load data into tables. Here is a sample commands supported by "mysqlimport":
"mysqlimport databaseName fileName" - Imports the data from the specified file to the specified database. The data will be loaded into the table who's name matches the specified file name

Q.How To Load Data Files into Tables with "mysqlimport"?

If you want to load a data file directly into a table, you need to prepare the data file as one line per data row, and use tab character as the column delimiter. The data file name should match the target table name. The following is a good tutorial exercise on using "mysqlimport":
>cd mysqlin
>type emplinks.tab
>mysqlimport -u root test emplinks.tab
test.links: Records: 2 Deleted: 0 Skipped: 0 Warnings: 0
>mysql -u root -e "SELECT * FROM links" test
+-------------------------+
| name |
+-------------------------+
| www.mysql.com |
+-------------------------+

Q.How To Get Help Information from the Server?

While you are at the "mysql>" prompt, you can get help information from the server by using the "HELP" command. The tutorial exercise below shows several examples:
>cd mysqlin
>mysql -u root
mysql> HELP;
...
List of all MySQL commands:
Note that all text commands must be end with ';'
? (?) Synonym for `help'.
clear (c) Clear command.
connect ( ) Reconnect to the server.
...
mysql> HELP SHOW;
Name: 'SHOW'
Description:
SHOW has many forms that provide information about
databases, tables, columns, or status information about
the server. This section describes those following:
SHOW CREATE DATABASE db_name
SHOW CREATE FUNCTION funcname
SHOW CREATE PROCEDURE procname
SHOW CREATE TABLE tbl_name
SHOW DATABASES [LIKE 'pattern']
SHOW ENGINE engine_name {LOGS | STATUS }
...
mysql> HELP CREATE TABLE;
Name: 'CREATE TABLE'
Description:
Syntax:
CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
(create_definition,...)
[table_option ...]
...

Q.How To Run "mysql" Commands from a Batch File?

If you have group of "mysql" commands that need to be executed repeatedly, you can put them into a file, and run them from the file in "mysql" batch mode. Here is a batch file, emplinks.sql, contains following commands:
USE test;
INSERT INTO links VALUES (10, 'www.GlobalGuideLine.com');
SELECT * FROM links;
To run this batch file, you need to follow this tutorial:
>cd mysqlin
>mysql -u root < emplinks.sql
id name

0 comments:

Post a Comment