Mysql interview questions

How do you get the month from a timestamp? 

SELECT MONTH(techpreparation_timestamp) from techpreparation_questions;

How would you change a table to InnoDB?

ALTER TABLE techpreparation_questions ENGINE innodb;

Thanks for downloading. Enjoy the reading.
What's MySQL ?
MySQL (pronounced "my ess cue el") is an open source relational database management system
(RDBMS) that uses Structured Query Language (SQL), the most popular language for adding,
accessing, and processing data in a database. Because it is open source, anyone can download MySQL
and tailor it to their needs in accordance with the general public license. MySQL is noted mainly for its
speed, reliability, and flexibility. ... 
What is DDL, DML and DCL ? 
If you look at the large variety of SQL commands, they can be divided into three large subgroups.

Data
Definition Language deals with database schemas and descriptions of how the data should reside in the
database, therefore language statements like CREATE TABLE or ALTER TABLE belong to DDL.

DML deals with data manipulation, and therefore includes most common SQL statements such
SELECT, INSERT, etc. Data Control Language includes commands such as GRANT, and mostly
concerns with rights, permissions and other controls of the database system. 

How do you get the number of rows affected by query? 
SELECT COUNT (user_id) FROM users would only return the number of user_id’s. 

If the value in the column is repeatable, how do you find out the unique values? 
Use DISTINCT in the query, such as SELECT DISTINCT user_firstname FROM users; You can also
ask for a number of distinct values by saying SELECT COUNT (DISTINCT user_firstname) FROM
users; 

How do you return the a hundred books starting from 25th? 
SELECT book_title FROM books LIMIT 25, 100. The first number in LIMIT is the offset, the second

When you create a table, and then run SHOW CREATE TABLE on it, you occasionally get

different results than what you typed in. What does MySQL modify in your newly created

tables? 

1. VARCHARs with length less than 4 become CHARs

2. CHARs with length more than 3 become VARCHARs.

3. NOT NULL gets added to the columns declared as PRIMARY KEYs

4. Default values such as NULL are specified for each column

is the number. 
You wrote a sear

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. 

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

MySQL config variable max_heap_table_size. 

What are CSV tables? 

Those are the special tables, data for which is saved into comma-separated values files. They cannot be

indexed. 

Explain federated tables. ?

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

other servers. 

What is SERIAL data type in MySQL? 

BIGINT NOT NULL PRIMARY KEY AUTO_INCREMENT 

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. 

Explain the difference between BOOL, TINYINT and BIT. ?

Prior to MySQL 5.0.3: those are all synonyms. After MySQL 5.0.3: BIT data type can store 8 bytes of

data and should be used for binary data. 

Explain the difference between FLOAT, DOUBLE and REAL. ?

FLOATs store floating point numbers with 8 place accuracy and take up 4 bytes. DOUBLEs store

floating point numbers with 16 place accuracy and take up 8 bytes. REAL is a synonym of FLOAT for

now.

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. 

What happens if a table has one column defined as TIMESTAMP? 

That field gets the current timestamp whenever the row gets altered. 

But what if you really want to store the timestamp data, such as the publication date of the

article? 

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

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.