www.worldentity.com

1. What is the maximum length of a table name, database name, and fieldname in MySQL?
Answer. Table Name -64
Database- 64
Columns-64

2. How many values can the SET function of MySQL take?
Answer. Mysql set can take 64 values.

3.What is the purpose of the following files having extensions
1 .frm
2 .myd
3 .myi
What do these files contain?
Answer.
.Myd is used for saving table data
.frm is used for saving table Structure
.myi is used for saving table Index
4. How can we know the number of days between two given dates using MySQL?
Answer.
1. select DATEDIFF(expr,expr2)
2. SELECT DATEDIFF(now(),’1981-06-10′);
5. What are the differences between drop a table and truncate a table?
Answer.
1. DROP TABLE table_name will DELETE the table structure and table data
2. TRUNCATE TABLE table_name will DELETE the table DATA not the table structure

6. Write a select query that will display the duplicated records and how many times the records has been duplicated? see the following table.

Answer. select count(Id) Id ,Name from tbl_users group by Name having Id >1;
7. What’s the default port for MySQL Server?
Answer. 3306

8 .TBL_AUTHOR - holds author id, author name and address for each author
TBL_BOOK - holds book id, book name and synopsis of each book
TBL_AUTHOR_BOOK_LINK - holds author_id, book_id to indicate which author has written which book. It contains role of the author also which is presently not used.
Each author can write multiple books. Each book can have multiple authors.
Some rules on solutions:
GROUP BY not allowed
Author name in output should not be repeated
Output required in all queries is author_id and name.
You can have additional output, but must not violate the other rule above.
Queries required:
We need to find queries to get the following:
1. Find all authors who have written at least one book.
2. Find all authors who have written a specific book.
3. Find all authors who have NOT written ANY book
4. Find all authors who have NOT written a specific book or r not associated with the specified book.

Answer: Please type in the answer for this question

1. SELECT DISTINCT (TA.author_id), TA.author_name FROM TBL_AUTHOR AS TA JOIN TBL_AUTHOR_BOOK_LINK AS TBL ON TA.author_id = TBL.author_id;

2. SELECT DISTINCT (TA.author_id), TA.author_name FROM TBL_AUTHOR AS TA JOIN TBL_AUTHOR_BOOK_LINK AS TBL ON TA.author_id = TBL.author_id JOIN TBL_BOOK TB ON TBL.book_id = TB.book_id WHERE TB.book_name = 'NAME';

3. SELECT DISTINCT (TA.author_id), TA.author_name FROM TBL_AUTHOR AS TA WHERE TA.author_id NOT IN ( SELECT DISTINCT( author_id) FROM TBL_AUTHOUR );

4. SELECT DISTINCT (TA.author_id), TA.author_name FROM TBL_AUTHOR AS TA WHERE TA.author_id NOT IN ( SELECT DISTINCT( author_id) FROM TBL_AUTHOUR ) OR TA.author_id IN (SELECT DISTINCT ( TA1.author_id) FROM TBL_AUTHOR AS TA1 JOIN TBL_AUTHOR_BOOK_LINK AS TBL ON TA1.author_id = TBL.author_id JOIN TBL_BOOK TB ON TBL.book_id = TB.book_id WHERE TB.book_name = 'NAME') ;

8. query which generates the second highest salary in the table?
Answer: SELECT Distinct * FROM emp where dept='computer' ORDER BY salary desc LIMIT 0,1

9. How can we store binary data in mysql?
Answer: Blob
10. What is maximum size of a database in MySQL?
Answer: 60 MB