Let us have a look on How to use the Stored Functions in Mysql. Here I am trying to show you how to use mysql stored functions in PHP scrips. Hope you all know, what is Mysql Stored Functions. Its like normal mysql functions, its written by the user. It will return results as a normal mysql functions. It can be called in normal sql statements. Defining a stored function

DELIMITER $$

DROP FUNCTION IF EXISTS `tester`.`sf_test`$$
CREATE FUNCTION `tester`.`sf_test` ()
RETURNS INT
READS SQL DATA
BEGIN
    DECLARE tot_count INT;
    select count(*) INTO tot_count from students;
    RETURN tot_count;
END$$

DELIMITER ;

Calling a stored function from PHP

$res = mysql_query('select sf_test()');

if ($res === FALSE) {
    die(mysql_error());
} else {
    echo "@";
    print_r($res);
}

How to find out the CREATE TABLE statement of a selected table.

The 'SHOW CREATE TABLE' command will show the CREATE TABLE statement of a selected table. It will be very helpful for the database backup actions.

SHOW CREATE TABLE `tablename`

It will return two colomns one is its table name and other is the 'CREATE TABLE' statement. 

CREATE TABLE `tablename` (

 `id` int(10) NOT NULL,

 `title` varchar(256) NOT NULL

) ENGINE=MyISAM DEFAULT CHARSET=latin1

Hope this will helpful for someone ! 

How to find out the Mysql storage engine type of a table.

The following queries will show the engine type of a table

SHOW
TABLE STATUS
WHERE
name= 'tablename';

The Following query will also help you to find out the storage engine.

Each Mysql table's informations are stored in the 'INFORMATION_SCHEMA' table. 

The following query will show the storage engine's of the given databse table.

SELECT 
TABLE_NAME,ENGINE 
FROM information_schema.TABLES
WHERE 
TABLE_SCHEMA = 'dbname'

To show particular table's mysql engine type.

SELECT TABLE_NAME,
ENGINE FROM information_schema.TABLES
WHERE TABLE_SCHEMA = 'dbname'
AND TABLE_NAME = 'tablename'

Hope this will helpful for you all.

Relational DBMSes


There is a table of two fields, primary key integer ID and
char(50) VALUE. Before adding the unique index to it, you need to
know, if there are duplicated VALUEs in the table. How you will do it?

Ans: should check the primary key integer is repeating in the
primary key field. If any one value from the field returning the
count greater than 1, we can not add unique index to the the field

What's the difference between INNER JOIN and OUTER JOIN? What
other types of JOINs do you know?

Ans : The INNER JOIN takes data from both tables returns the
specified data exists in both tables. But the OUTER JOIN check both
tables and returns values from the outer table when the criteria mets.

Other major join are LEFT OUTER JOIN, RIGHT OUTER JOIN.

What is a VIEW? What are the advantages and disadvantages of views?

Ans : View is a representation of a sql statement stored in
memory, which can be easily reusable.
Advantages of views:
we can view the data without storing the data into the object.
We can restict the view of a table i.e. can hide some of columns
in the tables.
We can Join two or more tables and show it as one object to user.
Disadvantages of views
we can not use DML operations on views.
When a table is dropped view will becomes inactive.. it depends on
the table objects.
It is an object so it occupies space

What's the main difference between WHERE and HAVING?

Ans: WHERE is a single row function, where as Having is based on groups.
When we use having Having we should use the Group by keyword.

What are subqueries? Does MySQL support them?

A query that is used within another query. For example a
select-statement within the WHERE or HAVING clause of another SQL
statement.
Mysql supporting the subquery system.

please complete the questions through the comments

thanks

13.
What function would you use to redirect the browser to a new page?
1. redir()
This is not a function in PHP, so it will fail with an error.
2. header()
This is the correct function, it allows you to write header data to direct the page to a new location. For example:
PLAIN TEXT
PHP:
1.
header("Location: http://www.search-this.com/");

3. location()
This is not a function in PHP, so it will fail with an error.
4. redirect()
This is not a function in PHP, so it will fail with an error.
14.
What function can you use to open a file for reading and writing?
1. fget()
This is not a function in PHP, so it will fail with an error.
2. file_open()
This is not a function in PHP, so it will fail with an error.
3. fopen()
This is the correct function, it allows you to open a file for reading and/or writing. In fact, you have a lot of options, check out php.net for more information.
4. open_file()
This is not a function in PHP, so it will fail with an error.
15.
What's the difference between mysql_fetch_row() and mysql_fetch_array()?

mysql_fetch_row() returns all of the columns in an array using a 0 based index. The first row would have the index of 0, the second would be 1, and so on. Now another MySQL function in PHP is mysql_fetch_assoc(), which is an associative array. Its' indexes are the column names. For example, if my query was returning 'first_name', 'last_name', 'email', my indexes in the array would be 'first_name', 'last_name', and 'email'. mysql_fetch_array() provides the output of mysql_fetch_assoc and mysql_fetch_row().
16.
What does the following code do? Explain what's going on there.
PLAIN TEXT
PHP:
1.
$date='08/26/2003';
2.
print ereg_replace("([0-9]+)/([0-9]+)/([0-9]+)","\\2/\\1/\\3",$date);

This code is reformatting the date from MM/DD/YYYY to DD/MM/YYYY. A good friend got me hooked on writing regular expressions like below, so it could be commented much better, granted this is a bit excessive for such a simple regular expression.
PLAIN TEXT
PHP:
1.
// Match 0-9 one or more times then a forward slash
2.
$regExpression = "([0-9]+)/";
3.
// Match 0-9 one or more times then another forward slash
4.
$regExpression .= "([0-9]+)/";
5.
// Match 0-9 one or more times yet again.
6.
$regExpression .= "([0-9]+)";

Now the \\2/\\1/\\3 denotes the parentheses matches. The first parenthesis matches the month, the second the day, and the third the year.
17.
Given a line of text $string, how would you write a regular expression to strip all the HTML tags from it?

First of all why would you write a regular expression when a PHP function already exists? See php.net's strip_tags function. However, considering this is an interview question, I would write it like so:
PLAIN TEXT
PHP:
1.
$stringOfText = "

This is a test
";
2.
$expression = "/<(.*?)>(.*?)<\/(.*?)>/";
3.
echo preg_replace($expression, "\\2", $stringOfText);
4.
5.
// It was suggested (by Fred) that /(<[^>]*>)/ would work too.
6.
$expression = "/(<[^>]*>)/";
7.
echo preg_replace($expression, "", $stringOfText);

18.
What's the difference between the way PHP and Perl distinguish between arrays and hashes?

This is why I tell everyone to, "pick the language for the job!" If you only write code in a single language how will you ever answer this question? The question is quite simple. In Perl, you are required to use the @ sign to start all array variable names, for example, @myArray. In PHP, you just continue to use the $ (dollar sign), for example, $myArray.

Now for hashes in Perl you must start the variable name with the % (percent sign), as in, %myHash. Whereas, in PHP you still use the $ (dollar sign), as in, $myHash.
19.
How can you get round the stateless nature of HTTP using PHP?

The top two options that are used are sessions and cookies. To access a session, you will need to have session_start() at the top of each page, and then you will use the $_SESSION hash to access and store your session variables. For cookies, you only have to remember one rule. You must use the set_cookie function before any output is started in your PHP script. From then on you can use the $_COOKIE has to access your cookie variables and values.

There are other methods, but they are not as fool proof and most often than not depend on the IP address of the visitor, which is a very dangerous thing to do.
20.
What does the GD library do?

This is probably one of my favorite libraries, as it is built into PHP as of version 4.3.0 (I am very happy with myself, I didn't have to look up the version of PHP this was introduced on php.net). This library allows you to manipulate and display images of various extensions. More often than not, it is used to create thumbnail images. An alternative to GD is ImageMagick, however, unlike GD, this does not come built in to PHP and must be installed on the server by an Administrator.
21.
Name a few ways to output (print) a block of HTML code in PHP?

Well you can use any of the output statments in PHP, such as, print, echo, and printf. Most individuals use the echo statement as in:
PLAIN TEXT
PHP:
1.
echo "My string $variable";

However, you can also use it like so:
PLAIN TEXT
PHP:
1.
echo
2.
This text is written to the screen as output and this $variable is parsed too. If you wanted you can have HTML tags in here as well. The END; remarks must be on a line of its own, and can't contain any extra white space.
3.
END;

22.
Is PHP better than Perl? - Discuss.

Come on, let's not start a flame over such a trivial question. As I have stated many times before,

"Pick the language for the job, do not fit the job into a particular language."

Perl in my opinion is great for command line utilities, yes it can be used for the web as well, but its' real power can be really demonstrated through the command line. Likewise, PHP can be used on the command line too, but I personally feel it's more powerful on the web. It has a lot more functions built with the web in mind, whereas, Perl seems to have the console in mind.

Personally, I love both languages. I used Perl a lot in college and I used PHP and Java a lot in college. Unfortunately, my job requires me to use C#, but I spend countless hours at home working in PHP, Perl, Ruby (currently learning), and Java to keep my skills up to date. Many have asked me what happened to C and C++ and do they still fit into my applications from time to time. The answer is primarily 'No'. Lately all of my development work has been for the web and though C and C++ could be written for the web, they are hardly the language to use for such tasks. Pick the language for the job. If I needed a console application that was meant to show off the performance differences between a quick sort, bubble sort, and a merge sort, give me C/C++! If you want a Photo Gallery, give me PHP or C# (though I personally find .NET languages better for quick GUI applications than web).

I would like to take this time to challenge other companies to post their interview questions or feel free to email them to me at search-this [at] cpradio [dot] org. I will be glad to read through them, and write an article about them revealing their answers for everyone to learn.

Changed # to % for Perl Hashes - Thanks to MrSpooky for pointing that out, can't believe I forgot that!

Q1.What is a join, what types of join are supported in MySQL.
Ans: A ‘join’ joins two table in such a way that all or partial records are selected from both the table based on join criteria.

Joins supported in mysql are :

[INNER | CROSS] JOIN

STRAIGHT_JOIN

LEFT [OUTER] JOIN

NATURAL [LEFT [OUTER]] JOIN

RIGHT [OUTER] JOIN

NATURAL [RIGHT [OUTER]] JOIN



Q2.In MySQL, what table type is required for foreign keys to work?
Ans: innoDB


Q3.How does full text search work.
Ans: A table must be a myISAM table
Table must have char varchar and text columns
FULLTEXT index must be created at the time of creation of table


Q4.What is the use of files .frm, .MYD, .MYI
Ans: frm files store the table definition
MYD files store the data
MYI files store the index


Q5.What is maximum size of a database in MySQL?
Ans: 65+GB / table / [ limited by the OS]


Q6.How many columns can exist in a mySql table?
Ans: 4096 colums

Q7.What is the maximum size of a row in a mysql table?
Ans: 65,535 not including blobs (as these are stored separately)


Q8. What would you use if you have a choice Natural [left] join or inner join or left join with using clause?
Ans: The NATURAL [LEFT] JOIN of two tables is defined to be semantically
equivalent to an INNER JOIN or a LEFT JOIN with a USING clause that names all columns that exist in both tables

42. How many ways can we get the value of current session id?
ans:-
session_id() returns the session id for the current session.

43. How can we destroy the session, how can we unset the variable of a session?
Ans:-
session_unregister -- Unregister a global variable from the current session
session_unset -- Free all session variables

44. How can we destroy the cookie?
Ans:-
Set the cookie in past

45. How many ways we can pass the variable through the navigation between the pages?
Ans:-
GET or QueryString and POST

46. What is the difference between ereg_replace() and eregi_replace()?
Ans:-
eregi_replace() function is identical to ereg_replace() except that this ignores case distinction when matching alphabetic characters.eregi_replace() function is identical to ereg_replace() except that this ignores case distinction when matching alphabetic characters.

47. What are the different functions in sorting an array?
Ans:-
Sorting functions in PHP,
asort-http://www.php.net/manual/en/function.asort.php
arsort-http://www.php.net/manual/en/function.arsort.php
ksort-http://www.php.net/manual/en/function.ksort.php
krsort-http://www.php.net/manual/en/function.krsort.php
uksort-http://www.php.net/manual/en/function.uksort.php
sort-http://www.php.net/manual/en/function.sort.php
natsort-http://www.php.net/manual/en/function.natsort.php
rsort-http://www.php.net/manual/en/function.rsort.php

48. How can we know the count/number of elements of an array?
Ans:-
2 ways
a) sizeof($urarray) This function is an alias of count()
b) count($urarray)
interestingly if u just pass a simple var instead of a an array it will return 1.

1) Among the following which one returns the value of a field in a recordset?

a) mysql_field_table()
b) mysql_field_name()
*c) mysql_result()
d) mysql_list_fields()

2) Which function returns column information from a recordset as an object ?

a) mysql_fetch_object()
*b) mysql_fetch_field()
c) mysql_field_name()
d) mysql_fetch_row()

3) Which function returns MySQL host info ?

*a) mysql_get_host_info()
b) mysql_host()
c) mysql_host_info()
d) mysql_get_host()

4) What is the use of the function mysql_info() ?

a) Returns the information about mysql server
b) Returns the current status the of the mysql server
*c) Returns the information about the last query
d) None of the above

5) Which function returns the error number of the last MySQL operation ?

a) mysql_num_error()
b) mysql_error_num()
c) mysql_error()
*d) mysql_errno()

6) Which function returns length of each output in a result ?

a) mysql_field_len()
*b) mysql_fetch_lengths()
c) mysql_fetch_length()
d) mysql_field_length()

7) What is the second parameter does in the function mysql_field_table(param1,param2) ?

a) Database connection resource id
*b) The numerical field offset
c) Result set
d) Database name

8) Which function checks whether or not the connection to the server is working ?

a) mysql_ stat()
b) mysql_ get_ server_ info()
*c) mysql_ping()
d) mysql_ pconnect()

9) How to fetch a result row as an associative array ?

*a) mysql_fetch_assoc()
b) mysql_ fetch_ row()
c) mysql_ fetch_ field()
d) None of these

10) What is the return value of the function mysql_select_db() in case of success ?

a) Link Identifier
b) Resource
c) Array of strings
*d) True

1. Explain MySQL architecture. - The front layer takes care of network connections and security authentications, the middle layer does the SQL query parsing, and then the query is handled off to the storage engine. A storage engine could be either a default one supplied with MySQL (MyISAM) or a commercial one supplied by a third-party vendor (ScaleDB, InnoDB, etc.)
2. Explain MySQL locks. - Table-level locks allow the user to lock the entire table, page-level locks allow locking of certain portions of the tables (those portions are referred to as tables), row-level locks are the most granular and allow locking of specific rows.
3. Explain multi-version concurrency control in MySQL. - Each row has two additional columns associated with it - creation time and deletion time, but instead of storing timestamps, MySQL stores version numbers.

4. What are MySQL transactions? - A set of instructions/queries that should be executed or rolled back as a single atomic unit.
5. What’s ACID? - Automicity - transactions are atomic and should be treated as one in case of rollback. Consistency - the database should be in consistent state between multiple states in transaction. Isolation - no other queries can access the data modified by a running transaction. Durability - system crashes should not lose the data.
6. Which storage engines support transactions in MySQL? - Berkeley DB and InnoDB.
7. How do you convert to a different table type? - ALTER TABLE customers TYPE = InnoDB
8. How do you index just the first four bytes of the column? - ALTER TABLE customers ADD INDEX (business_name(4))
9. What’s the difference between PRIMARY KEY and UNIQUE in MyISAM? - PRIMARY KEY cannot be null, so essentially PRIMARY KEY is equivalent to UNIQUE NOT NULL.
10. How do you prevent MySQL from caching a query? - SELECT SQL_NO_CACHE …
11. What’s the difference between query_cache_type 1 and 2? - The second one is on-demand and can be retrieved via SELECT SQL_CACHE … If you’re worried about the SQL portability to other servers, you can use SELECT /* SQL_CACHE */ id FROM … - MySQL will interpret the code inside comments, while other servers will ignore it.

1. 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.
2. 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.

3. 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;
4. 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 is the number.
5. You wrote a search engine that should retrieve 10 results at a time, but at the same time you’d like to know how many rows there’re total. How do you display that to the user? - SELECT SQL_CALC_FOUND_ROWS page_title FROM web_pages LIMIT 1,10; SELECT FOUND_ROWS(); The second query (not that COUNT() is never used) will tell you how many results there’re total, so you can display a phrase "Found 13,450,600 results, displaying 1-10". Note that FOUND_ROWS does not pay attention to the LIMITs you specified and always returns the total number of rows affected by query.
6. How would you write a query to select all teams that won either 2, 4, 6 or 8 games? - SELECT team_name FROM teams WHERE team_won IN (2, 4, 6, 8)
7. How would you select all the users, whose phone number is null? - SELECT user_name FROM users WHERE ISNULL(user_phonenumber);
8. What does this query mean: SELECT user_name, user_isp FROM users LEFT JOIN isps USING (user_id) - It’s equivalent to saying SELECT user_name, user_isp FROM users LEFT JOIN isps WHERE users.user_id=isps.user_id
9. 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.
10. 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.
11. On executing the DELETE statement I keep getting the error about foreign key constraint failing. What do I do? - What it means is that so of the data that you’re trying to delete is still alive in another table. Like if you have a table for universities and a table for students, which contains the ID of the university they go to, running a delete on a university table will fail if the students table still contains people enrolled at that university. Proper way to do it would be to delete the offending data first, and then delete the university in question. Quick way would involve running SET foreign_key_checks=0 before the DELETE command, and setting the parameter back to 1 after the DELETE is done. If your foreign key was formulated with ON DELETE CASCADE, the data in dependent tables will be removed automatically.
12. When would you use ORDER BY in DELETE statement? - When you’re not deleting by row ID. Such as in DELETE FROM techinterviews_com_questions ORDER BY timestamp LIMIT 1. This will delete the most recently posted question in the table techinterviews_com_questions.
13. How can you see all indexes defined for a table? - SHOW INDEX FROM techinterviews_questions;
14. How would you change a column from VARCHAR(10) to VARCHAR(50)? - ALTER TABLE techinterviews_questions CHANGE techinterviews_content techinterviews_CONTENT VARCHAR(50).
15. How would you delete a column? - ALTER TABLE techinterviews_answers DROP answer_user_id.
16. How would you change a table to InnoDB? - ALTER TABLE techinterviews_questions ENGINE innodb;
17. 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
18. How do I find out all databases starting with ‘tech’ to which I have access to? - SHOW DATABASES LIKE ‘tech%’;
19. How do you concatenate strings in MySQL? - CONCAT (string1, string2, string3)
20. How do you get a portion of a string? - SELECT SUBSTR(title, 1, 10) from techinterviews_questions;
21. 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.
22. How do you convert a string to UTF-8? - SELECT (techinterviews_question USING utf8);
23. What do % and _ mean inside LIKE statement? - % corresponds to 0 or more characters, _ is exactly one character.
24. What does + mean in REGEXP? - At least one character. Appendix G. Regular Expressions from MySQL manual is worth perusing before the interview.
25. How do you get the month from a timestamp? - SELECT MONTH(techinterviews_timestamp) from techinterviews_questions;
26. How do you offload the time/date handling to MySQL? - SELECT DATE_FORMAT(techinterviews_timestamp, ‘%Y-%m-%d’) from techinterviews_questions; A similar TIME_FORMAT function deals with time.
27. How do you add three minutes to a date? - ADDDATE(techinterviews_publication_date, INTERVAL 3 MINUTE)
28. What’s the difference between Unix timestamps and MySQL timestamps? - Internally Unix timestamps are stored as 32-bit integers, while MySQL timestamps are stored in a similar manner, but represented in readable YYYY-MM-DD HH:MM:SS format.
29. How do you convert between Unix timestamps and MySQL timestamps? - UNIX_TIMESTAMP converts from MySQL timestamp to Unix timestamp, FROM_UNIXTIME converts from Unix timestamp to MySQL timestamp.
30. 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’);
31. How are ENUMs and SETs represented internally? - As unique integers representing the powers of two, due to storage optimizations.

1. How do you start and stop MySQL on Windows? - net start MySQL, net stop MySQL
2. How do you start MySQL on Linux? - /etc/init.d/mysql start
3. Explain the difference between mysql and mysqli interfaces in PHP? - mysqli is the object-oriented version of mysql library functions.
4. What’s the default port for MySQL Server? - 3306
5. What does tee command do in MySQL? - tee followed by a filename turns on MySQL logging to a specified file. It can be stopped by command notee.
6. Can you save your connection settings to a conf file? - Yes, and name it ~/.my.conf. You might want to change the permissions on the file to 600, so that it’s not readable by others.
7. How do you change a password for an existing user via mysqladmin? - mysqladmin -u root -p password "newpassword"
8. Use mysqldump to create a copy of the database? - mysqldump -h mysqlhost -u username -p mydatabasename > dbdump.sql
9. Have you ever used MySQL Administrator and MySQL Query Browser? Describe the tasks you accomplished with these tools.
10. 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.
11. Explain the difference between MyISAM Static and MyISAM Dynamic. - In 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.
12. What does myisamchk do? - It compressed the MyISAM tables, which reduces their disk usage.
13. Explain advantages of InnoDB over MyISAM? - Row-level locking, transactions, foreign key constraints and crash recovery.
14. Explain advantages of MyISAM over InnoDB? - Much more conservative approach to disk space management - each MyISAM table is stored in a separate file, which could be compressed then with myisamchk if needed. With InnoDB the tables are stored in tablespace, and not much further optimization is possible. All data except for TEXT and BLOB can occupy 8,000 bytes at most. No full text indexing is available for InnoDB. TRhe COUNT(*)s execute slower than in MyISAM due to tablespace complexity.
15. 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.
16. How do you control the max size of a HEAP table? - MySQL config variable max_heap_table_size.
17. What are CSV tables? - Those are the special tables, data for which is saved into comma-separated values files. They cannot be indexed.
18. Explain federated tables. - Introduced in MySQL 5.0, federated tables allow access to the tables located on other databases on other servers.
19. What is SERIAL data type in MySQL? - BIGINT NOT NULL PRIMARY KEY AUTO_INCREMENT
20. 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.
21. 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.
22. 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.
23. 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.
24. What happens if a table has one column defined as TIMESTAMP? - That field gets the current timestamp whenever the row gets altered.
25. 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.
26. 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.
27. 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.
28. 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.
29. 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.