Q.1 What does tee command do in MySQL?
Ans: tee followed by a filename turns on MySQL logging to a specified file. It can be stopped by command note.
Q.2 How do you start MySQL on Linux?
Ans: /etc/init.d/mysql start
Q.3 What are HEAP tables in MySQL?
Ans: 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.4 how you will Create a database on the mysql server with unix shell
Ans: mysql> create database databasename.
Q.5 how to list or view all databases from the mysql server.
Ans: mysql> show databases;
Q.6 How do you concatenate strings in MySQL?
Ans: CONCAT (string1, string2, string3)
.
.
Q.7 What are ENUMs used for in MySQL?
Ans: You can limit the possible values that go into the table. CREATE TABLE months (month ENUM ‘January’, ‘February’, ‘March’,…); INSERT months VALUES (’April’).
Q.8 What does tee command do in MySQL?
Ans: tee followed by a filename turns on MySQL logging to a specified file. It can be stopped by command note.
Q.9 How do you change a password for an existing user via mysqladmin?
Ans: mysqladmin -u root -p password “newpassword”.
Q.10 Use mysqldump to create a copy of the database?
Ans: mysqldump -h mysqlhost -u username -p mydatabasename > dbdump.sql.
Q.11 What does myisamchk do?
Ans: It compressed the MyISAM tables, which reduces their disk usage.
Q.12 How do you start and stop MySQL on Windows?
Ans: net start MySQL, net stop MySQL.
Q.13 How do you control the max size of a HEAP table?
Ans: MySQL config variable max_heap_table_size.
Q.14 How to see table's field formats or description of table .
Ans: mysql> describe tablename.
Q.15 How to delete a database from mysql server.
Ans: mysql> drop database databasename.
Q.16 How to delete a table
Ans: mysql> drop table tablename.
Q.17 How you will Show all data from a table.
Ans: mysql> SELECT * FROM tablename.
Q.18 What is SERIAL data type in MySQL?
Ans: BIGINT NOT NULL PRIMARY KEY AUTO_INCREMENT.
Q.19 Explain the difference between FLOAT, DOUBLE and REAL. ?
Ans: 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.
Q.20 What happens when the column is set to AUTO INCREMENT and you reach the maximum value for that table?
Ans: 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.21 What does myisamchk do?
Ans: It compressed the MyISAM tables, which reduces their disk usage.
Q.22 how to Return total number of rows.
Ans: mysql> SELECT COUNT(*) FROM tablename.
Q.23 How to Update database permissions/privilages.
Ans: mysql> flush privileges.
Q.24 How we get Sum of column ?
Ans: mysql> SELECT SUM(*) FROM [table name].
Q.25 How do you find out which auto increment was assigned on the last insert?
Ans: 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.
that you don’t have to specify the table name.
Q.26 Explain the difference between FLOAT, DOUBLE and REAL. ?
Ans: 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.
Q.27 Can you save your connection settings to a conf file?
Ans: 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.
Q.28 Explain the difference between MyISAM Static and MyISAM Dynamic. ?
Ans: 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.
Q.29 How would you select all the users, whose phone number is null?
Ans: SELECT user_name FROM users WHERE ISNULL(user_phonenumber).
Q.30 When would you use ORDER BY in DELETE statement?
Ans: When you’re not deleting by row ID. Such as in DELETE FROM techpreparation_com_questions ORDER BY timestamp LIMIT 1. This will delete the most recently posted question in the table techpreparation_com_questions.