{
"iq":[{"id":"1",
"q":"What is MySQL? ",
"answer":"MySQL is an open source DBMS which is built, supported and distributed by MySQL AB (now acquired by Oracle) "
},
{"id":"2",
"q":"What are the technical features of MySQL? ",
"answer":"MySQL database software is a client or server system which includes\nMultithreaded SQL server supporting various client programs and libraries\nDifferent backend\nWide range of application programming interfaces and\nAdministrative tools. "
},
{"id":"3",
"q":"How many TRIGGERS are allowed in MySql table?",
"answer":" MySql table allows following 6 triggers:\n- BEFORE INSERT\n- AFTER INSERT\n- BEFORE UPDATE\n- AFTER UPDATE\n- BEFORE DELETE and\n- AFTER DELETE"
},
{"id":"4",
"q":"Differentiate between FLOAT and DOUBLE.",
"answer":"FLOAT stores floating point numbers with accuracy up to eight places and has four bytes while DOUBLE stores floating point numbers with accuracy upto 18 places and has eight bytes."
},
{"id":"5",
"q":"How do you control the max size of a HEAP table?",
"answer":"Maximum size of Heap table can be controlled using MySQL config variable called max_heap_table_size."
},
{"id":"6",
"q":"How to see table’s field formats or description of table . ",
"answer":"mysql> describe tablename; "
},
{"id":"7",
"q":"How to delete a database from mysql server.",
"answer":"mysql> drop database databasename; "
},
{"id":"8",
"q":"How you will Show all data from a table.",
"answer":"mysql> SELECT * FROM tablename;"
},
{"id":"9",
"q":"How to Show certain selected rows with the value “stuv”.",
"answer":"mysql> SELECT * FROM tablename WHERE fieldname = “stuv”;"
},
{"id":"10",
"q":"How to Show all records starting with the letters ‘anita’ AND the phone number ‘986600000’.",
"answer":"mysql> SELECT * FROM tablename WHERE name like “anita%” AND phone_number = ‘9866000000’;"
},
{"id":"11",
"q":"What does myisamchk do?",
"answer":"It compresses the MyISAM tables, which reduces their disk or memory usage"
},
{"id":"12",
"q":"How can we convert between Unix & MySQL timestamps?",
"answer":"MySQL timestamp can be converted into Unix timestamp using the command UNIX_TIMESTAMP.\n- Unix timestamp can be converted into MySQL timestamp using the command FROM_UNIXTIME."
},
{"id":"13",
"q":"What is BLOB?",
"answer":"- BLOB stands for binary large object.\n- It that can hold a variable amount of data."
},
{"id":"14",
"q":"What is TEXT?",
"answer":"TEXT is case-insensitive BLOB. The four types of TEXT are:\n- TINYTEXT\n- TEXT\n- MEDIUMTEXT\n- LONGTEXT"
},
{"id":"15",
"q":"- In BLOB sorting and comparison is performed in case-sensitive for BLOB values.\n- In TEXT types sorting and comparison is performed case-insensitive. "
},
{"id":"16",
"q":"How would concatenate strings in MySQL?",
"answer":"With the use of - CONCAT (string1, string2, string3) "
},
{"id":"17",
"q":"How are MySQL timestamps seen to a user?",
"answer":" MySQL time stamps are seen to a user in a readable format : YYYY-MM-DD HH:MM:SS. "
},
{"id":"18",
"q":"What is MySQL data directory? How to determine the location of the data directory?",
"answer":"MySQL stores its data on the disk on the data dictionary. Each subdirectory under this data dictionary represents a MySQL database, inside those directories. By default the information managed my MySQL = server mysqld is stored in data directory.A default location of data directory in windows is C:\mysql\data or C:\Program Files\MySQL\MySQL Server 5.0 \data.. "
},
{"id":"19",
"q":"How will you export tables as an XML file in MySQL? ",
"answer":" From the command prompt type the following statement:\nmysql -u test --xml -e 'SELECT * FROM t1' > t1.xml\nwhere ‘–u test‘ is the user name, --xml indicates the type of the file is xml, -e for export "
},
{"id":"20",
"q":" What is MySQL data directory? How to determine the location of the data directory?",
"answer":"MySQL data directory is most important location in which all MySQL databases are stored. The default data directory is located in the file mysql.\nIf the out of the space is the issue, then the directory need to be moved another location. Before moving, the database need to be closed. After moving the MySQL configuration file need to be edited. Look for the ‘datadir’ entry and change the path to the new directory. "
},
{"id":"21",
"q":"Write a command to view the content of the table.?",
"answer":" To view all the data that is contained inside a table named sales use the select command. For example: to see the data of first row in a table using the command as:\nmysql > select * from sales limit 1;\nIf the database is created recently then it will give a blank listing, but after the data is being entered it will show the full listing in a tabular form. "
},
{"id":"22",
"q":" How you will Show unique records. ",
"answer":"mysql> SELECT DISTINCT columnname FROM tablename; "
},
{"id":"23",
"q":"how to Return total number of rows.",
"answer":" mysql> SELECT COUNT(*) FROM tablename;"
},{"id":"24",
"q":"Give string types available for column?",
"answer":"The string types are:\nSET\nBLOB\nENUM\nCHAR\nTEXT\nVARCHAR"
},{"id":"25",
"q":"How to get current MySQL version? ",
"answer":"SELECT VERSION ();\nis used to get the current version of MySQL."
},{"id":"26",
"q":"What does a TIMESTAMP do on UPDATE CURRENT_TIMESTAMP data type?",
"answer":"TIMESTAMP column is updated with Zero when the table is created. UPDATE CURRENT_TIMESTAMP modifier updates the timestamp field to current time whenever there is a change in other fields of the table."
},{"id":"27",
"q":"What is the difference between primary key and candidate key?",
"answer":"Every row of a table is identified uniquely by primary key. There is only one primary key for a table.\nPrimary Key is also a candidate key. By common convention, candidate key can be designated as primary and which can be used for any foreign key references."
},{"id":"28",
"q":"How do you login to MySql using Unix shell? ",
"answer":" We can login through this command:\n# [mysql dir]/bin/mysql -h hostname -u <UserName> -p <password>"
},{"id":"29",
"q":"What are federated tables? ",
"answer":"\nFederated tables which allow access to the tables located on other databases on other servers."
},{"id":"30",
"q":"How can you see all indexes defined for a table?",
"answer":"Indexes are defined for the table by:\nSHOW INDEX FROM <tablename>;"
},{"id":"31",
"q":"What do you mean by % and _ in the LIKE statement? ",
"answer":"% corresponds to 0 or more characters, _ is exactly one character in the LIKE statement."
},{"id":"32",
"q":"How can we convert between Unix & MySQL timestamps?",
"answer":"UNIX_TIMESTAMP is the command which converts from MySQL timestamp to Unix timestamp\nFROM_UNIXTIME is the command which converts from Unix timestamp to MySQL timestamp. "
},{"id":"33",
"q":"What is the difference between mysql_fetch_array and mysql_fetch_object?",
"answer":"Following are the differences between mysql_fetch_array and mysql_fetch_object:\nmysql_fetch_array() -Returns a result row as an associated array or a regular array from database.\nmysql_fetch_object – Returns a result row as object from database. "
},{"id":"34",
"q":"How can we run batch mode in mysql?",
"answer":"mysql ;\nmysql mysql.out"
},{"id":"35",
"q":"What is InnoDB?",
"answer":"lnnoDB is a transaction safe storage engine developed by Innobase Oy which is a Oracle Corporation now."
},{"id":"36",
"q":"How to enter Characters as HEX Numbers?",
"answer":"If you want to enter characters as HEX numbers, you can enter HEX numbers with single quotes and a prefix of (X), or just prefix HEX numbers with (Ox).\nA HEX number string will be automatically converted into a character string, if the expression context is a string. "
},{"id":"37",
"q":"How many columns can be used for creating Index?",
"answer":"Maximum of 16 indexed columns can be created for any standard table. "
},{"id":"38",
"q":"What is the different between NOW() and CURRENT_DATE()?",
"answer":"NOW () command is used to show current year,month,date with hours,minutes and seconds.\nCURRENT_DATE() shows current year,month and date only."
},{"id":"39",
"q":" How many TRIGGERS are allowed in MySql table? ",
"answer":"SIX triggers are allowed in MySql table. They are as follows:\nBEFORE INSERT\nAFTER INSERT\nBEFORE UPDATE\nAFTER UPDATE\nBEFORE DELETE and\nAFTER DELETE"
},{"id":"40",
"q":"What are ENUMs used for in MySQL?",
"answer":"ENUM is used to limit the possible values and store it together. It is a function that can be created to store the similar values together. It is used in creation of table.\nThe syntax of it is as follows:\nCREATE TABLE months (month ENUM “January”, “February”, “March”,…);\nINSERT months VALUES (“April”);"
},{"id":"41",
"q":"How to find the unique values if the value in the column is repeated? ",
"answer":"If the values in the column of a table are repeating and a unique value has to be found then the following command can be used in the query:\nSELECT DISTINCT user_firstname FROM users;\nThere is another command which can be used to find the command to see the distinct values as:\nSELECT COUNT (DISTINCT user_firstname) FROM users;"
},{"id":"42",
"q":"What is the difference between Unix timestamps and MySQL timestamps? ",
"answer":"The unix timestamp is stored as 32 bit integer whereas, MySQL timestamps are stored in 32 bit integers but represented differently then UNIX timestamps like YYYY-MM-DD HH:MM:SS format. Unix timestamp is given as month-day-year-HH:MM:SS.."
},{"id":"43",
"q":"Why to use CHAR instead of VARCHAR in the database?",
"answer":"CHAR is much more accurate and efficient to use. CHAR doesn’t have to keep a count of the variable length. It is more efficient when you have to use it for a text column which is of an exact length. Char is used for the data which are fixed, but VARCHAR is used for data like password, which are variable."
},{"id":"44",
"q":"How important is to list the column names when doing an INSERT?",
"answer":"It is not important to list the column names when doing using an INSERT command as you can provide the column information and values in the table in the same order in which they appear in the table structure. It is safer and convenient way to specify the column names as it will keep the count of the column you are visiting."
},{"id":"45",
"q":"How can you make a database as your current database? ",
"answer":"After making a database the first thing which has to be done is to create a table inside the database to test the new database that is being created. The command which is used to do that is:\nUSE aliendatabase;\nThis command allows us to make a database which is not a current database as my current. I have to just use the USE variable and the name of the database and it will become active for use."
},{"id":"46",
"q":"Write a query to create a database and a table?",
"answer":"MySQL comes up with some default database that can be used as a base to create a new one. The command that is used to create a new database is as follows:\nCREATE DATABASE SQL command\nThe command has to be written in MySQL terminal. This command will create a new database and then you can create new tables and include data in it. "
},{"id":"47",
"q":"How to Join tables on common columns.",
"answer":" mysql> select lookup.illustrationid, lookup.personid,person.birthday from lookup left join person on lookup.personid=person.personid=statement to join birthday in person table with primary illustration id."
},{"id":"48",
"q":"How to Change a users password from unix shell.",
"answer":"# [mysql dir]/bin/mysqladmin -u username -h hostname.blah.org -p password ‘new-password’"},
{"id":"49",
"q":"How To update info already in a table and Delete a row(s) from a table. ",
"answer":"mysql> UPDATE [table name] SET Select_priv = ‘Y’,Insert_priv = ‘Y’,Update_priv = ‘Y’ where [field name] = ‘user’;\nmysql> DELETE from [table name] where [field name] = ‘whatever’; "
},
{"id":"49",
"q":"How to Update database permissions/privilages. ",
"answer":"mysql> flush privileges;"
},
{"id":"50",
"q":"How to Load a CSV file into a table.",
"answer":"mysql> LOAD DATA INFILE ‘/tmp/filename.csv’ replace INTO TABLE [table name] FIELDS TERMINATED BY ‘,’ LINES TERMINATED BY ‘n’ (field1,field2,field3);"
}
]
}
"iq":[{"id":"1",
"q":"What is MySQL? ",
"answer":"MySQL is an open source DBMS which is built, supported and distributed by MySQL AB (now acquired by Oracle) "
},
{"id":"2",
"q":"What are the technical features of MySQL? ",
"answer":"MySQL database software is a client or server system which includes\nMultithreaded SQL server supporting various client programs and libraries\nDifferent backend\nWide range of application programming interfaces and\nAdministrative tools. "
},
{"id":"3",
"q":"How many TRIGGERS are allowed in MySql table?",
"answer":" MySql table allows following 6 triggers:\n- BEFORE INSERT\n- AFTER INSERT\n- BEFORE UPDATE\n- AFTER UPDATE\n- BEFORE DELETE and\n- AFTER DELETE"
},
{"id":"4",
"q":"Differentiate between FLOAT and DOUBLE.",
"answer":"FLOAT stores floating point numbers with accuracy up to eight places and has four bytes while DOUBLE stores floating point numbers with accuracy upto 18 places and has eight bytes."
},
{"id":"5",
"q":"How do you control the max size of a HEAP table?",
"answer":"Maximum size of Heap table can be controlled using MySQL config variable called max_heap_table_size."
},
{"id":"6",
"q":"How to see table’s field formats or description of table . ",
"answer":"mysql> describe tablename; "
},
{"id":"7",
"q":"How to delete a database from mysql server.",
"answer":"mysql> drop database databasename; "
},
{"id":"8",
"q":"How you will Show all data from a table.",
"answer":"mysql> SELECT * FROM tablename;"
},
{"id":"9",
"q":"How to Show certain selected rows with the value “stuv”.",
"answer":"mysql> SELECT * FROM tablename WHERE fieldname = “stuv”;"
},
{"id":"10",
"q":"How to Show all records starting with the letters ‘anita’ AND the phone number ‘986600000’.",
"answer":"mysql> SELECT * FROM tablename WHERE name like “anita%” AND phone_number = ‘9866000000’;"
},
{"id":"11",
"q":"What does myisamchk do?",
"answer":"It compresses the MyISAM tables, which reduces their disk or memory usage"
},
{"id":"12",
"q":"How can we convert between Unix & MySQL timestamps?",
"answer":"MySQL timestamp can be converted into Unix timestamp using the command UNIX_TIMESTAMP.\n- Unix timestamp can be converted into MySQL timestamp using the command FROM_UNIXTIME."
},
{"id":"13",
"q":"What is BLOB?",
"answer":"- BLOB stands for binary large object.\n- It that can hold a variable amount of data."
},
{"id":"14",
"q":"What is TEXT?",
"answer":"TEXT is case-insensitive BLOB. The four types of TEXT are:\n- TINYTEXT\n- TEXT\n- MEDIUMTEXT\n- LONGTEXT"
},
{"id":"15",
"q":"- In BLOB sorting and comparison is performed in case-sensitive for BLOB values.\n- In TEXT types sorting and comparison is performed case-insensitive. "
},
{"id":"16",
"q":"How would concatenate strings in MySQL?",
"answer":"With the use of - CONCAT (string1, string2, string3) "
},
{"id":"17",
"q":"How are MySQL timestamps seen to a user?",
"answer":" MySQL time stamps are seen to a user in a readable format : YYYY-MM-DD HH:MM:SS. "
},
{"id":"18",
"q":"What is MySQL data directory? How to determine the location of the data directory?",
"answer":"MySQL stores its data on the disk on the data dictionary. Each subdirectory under this data dictionary represents a MySQL database, inside those directories. By default the information managed my MySQL = server mysqld is stored in data directory.A default location of data directory in windows is C:\mysql\data or C:\Program Files\MySQL\MySQL Server 5.0 \data.. "
},
{"id":"19",
"q":"How will you export tables as an XML file in MySQL? ",
"answer":" From the command prompt type the following statement:\nmysql -u test --xml -e 'SELECT * FROM t1' > t1.xml\nwhere ‘–u test‘ is the user name, --xml indicates the type of the file is xml, -e for export "
},
{"id":"20",
"q":" What is MySQL data directory? How to determine the location of the data directory?",
"answer":"MySQL data directory is most important location in which all MySQL databases are stored. The default data directory is located in the file mysql.\nIf the out of the space is the issue, then the directory need to be moved another location. Before moving, the database need to be closed. After moving the MySQL configuration file need to be edited. Look for the ‘datadir’ entry and change the path to the new directory. "
},
{"id":"21",
"q":"Write a command to view the content of the table.?",
"answer":" To view all the data that is contained inside a table named sales use the select command. For example: to see the data of first row in a table using the command as:\nmysql > select * from sales limit 1;\nIf the database is created recently then it will give a blank listing, but after the data is being entered it will show the full listing in a tabular form. "
},
{"id":"22",
"q":" How you will Show unique records. ",
"answer":"mysql> SELECT DISTINCT columnname FROM tablename; "
},
{"id":"23",
"q":"how to Return total number of rows.",
"answer":" mysql> SELECT COUNT(*) FROM tablename;"
},{"id":"24",
"q":"Give string types available for column?",
"answer":"The string types are:\nSET\nBLOB\nENUM\nCHAR\nTEXT\nVARCHAR"
},{"id":"25",
"q":"How to get current MySQL version? ",
"answer":"SELECT VERSION ();\nis used to get the current version of MySQL."
},{"id":"26",
"q":"What does a TIMESTAMP do on UPDATE CURRENT_TIMESTAMP data type?",
"answer":"TIMESTAMP column is updated with Zero when the table is created. UPDATE CURRENT_TIMESTAMP modifier updates the timestamp field to current time whenever there is a change in other fields of the table."
},{"id":"27",
"q":"What is the difference between primary key and candidate key?",
"answer":"Every row of a table is identified uniquely by primary key. There is only one primary key for a table.\nPrimary Key is also a candidate key. By common convention, candidate key can be designated as primary and which can be used for any foreign key references."
},{"id":"28",
"q":"How do you login to MySql using Unix shell? ",
"answer":" We can login through this command:\n# [mysql dir]/bin/mysql -h hostname -u <UserName> -p <password>"
},{"id":"29",
"q":"What are federated tables? ",
"answer":"\nFederated tables which allow access to the tables located on other databases on other servers."
},{"id":"30",
"q":"How can you see all indexes defined for a table?",
"answer":"Indexes are defined for the table by:\nSHOW INDEX FROM <tablename>;"
},{"id":"31",
"q":"What do you mean by % and _ in the LIKE statement? ",
"answer":"% corresponds to 0 or more characters, _ is exactly one character in the LIKE statement."
},{"id":"32",
"q":"How can we convert between Unix & MySQL timestamps?",
"answer":"UNIX_TIMESTAMP is the command which converts from MySQL timestamp to Unix timestamp\nFROM_UNIXTIME is the command which converts from Unix timestamp to MySQL timestamp. "
},{"id":"33",
"q":"What is the difference between mysql_fetch_array and mysql_fetch_object?",
"answer":"Following are the differences between mysql_fetch_array and mysql_fetch_object:\nmysql_fetch_array() -Returns a result row as an associated array or a regular array from database.\nmysql_fetch_object – Returns a result row as object from database. "
},{"id":"34",
"q":"How can we run batch mode in mysql?",
"answer":"mysql ;\nmysql mysql.out"
},{"id":"35",
"q":"What is InnoDB?",
"answer":"lnnoDB is a transaction safe storage engine developed by Innobase Oy which is a Oracle Corporation now."
},{"id":"36",
"q":"How to enter Characters as HEX Numbers?",
"answer":"If you want to enter characters as HEX numbers, you can enter HEX numbers with single quotes and a prefix of (X), or just prefix HEX numbers with (Ox).\nA HEX number string will be automatically converted into a character string, if the expression context is a string. "
},{"id":"37",
"q":"How many columns can be used for creating Index?",
"answer":"Maximum of 16 indexed columns can be created for any standard table. "
},{"id":"38",
"q":"What is the different between NOW() and CURRENT_DATE()?",
"answer":"NOW () command is used to show current year,month,date with hours,minutes and seconds.\nCURRENT_DATE() shows current year,month and date only."
},{"id":"39",
"q":" How many TRIGGERS are allowed in MySql table? ",
"answer":"SIX triggers are allowed in MySql table. They are as follows:\nBEFORE INSERT\nAFTER INSERT\nBEFORE UPDATE\nAFTER UPDATE\nBEFORE DELETE and\nAFTER DELETE"
},{"id":"40",
"q":"What are ENUMs used for in MySQL?",
"answer":"ENUM is used to limit the possible values and store it together. It is a function that can be created to store the similar values together. It is used in creation of table.\nThe syntax of it is as follows:\nCREATE TABLE months (month ENUM “January”, “February”, “March”,…);\nINSERT months VALUES (“April”);"
},{"id":"41",
"q":"How to find the unique values if the value in the column is repeated? ",
"answer":"If the values in the column of a table are repeating and a unique value has to be found then the following command can be used in the query:\nSELECT DISTINCT user_firstname FROM users;\nThere is another command which can be used to find the command to see the distinct values as:\nSELECT COUNT (DISTINCT user_firstname) FROM users;"
},{"id":"42",
"q":"What is the difference between Unix timestamps and MySQL timestamps? ",
"answer":"The unix timestamp is stored as 32 bit integer whereas, MySQL timestamps are stored in 32 bit integers but represented differently then UNIX timestamps like YYYY-MM-DD HH:MM:SS format. Unix timestamp is given as month-day-year-HH:MM:SS.."
},{"id":"43",
"q":"Why to use CHAR instead of VARCHAR in the database?",
"answer":"CHAR is much more accurate and efficient to use. CHAR doesn’t have to keep a count of the variable length. It is more efficient when you have to use it for a text column which is of an exact length. Char is used for the data which are fixed, but VARCHAR is used for data like password, which are variable."
},{"id":"44",
"q":"How important is to list the column names when doing an INSERT?",
"answer":"It is not important to list the column names when doing using an INSERT command as you can provide the column information and values in the table in the same order in which they appear in the table structure. It is safer and convenient way to specify the column names as it will keep the count of the column you are visiting."
},{"id":"45",
"q":"How can you make a database as your current database? ",
"answer":"After making a database the first thing which has to be done is to create a table inside the database to test the new database that is being created. The command which is used to do that is:\nUSE aliendatabase;\nThis command allows us to make a database which is not a current database as my current. I have to just use the USE variable and the name of the database and it will become active for use."
},{"id":"46",
"q":"Write a query to create a database and a table?",
"answer":"MySQL comes up with some default database that can be used as a base to create a new one. The command that is used to create a new database is as follows:\nCREATE DATABASE SQL command\nThe command has to be written in MySQL terminal. This command will create a new database and then you can create new tables and include data in it. "
},{"id":"47",
"q":"How to Join tables on common columns.",
"answer":" mysql> select lookup.illustrationid, lookup.personid,person.birthday from lookup left join person on lookup.personid=person.personid=statement to join birthday in person table with primary illustration id."
},{"id":"48",
"q":"How to Change a users password from unix shell.",
"answer":"# [mysql dir]/bin/mysqladmin -u username -h hostname.blah.org -p password ‘new-password’"},
{"id":"49",
"q":"How To update info already in a table and Delete a row(s) from a table. ",
"answer":"mysql> UPDATE [table name] SET Select_priv = ‘Y’,Insert_priv = ‘Y’,Update_priv = ‘Y’ where [field name] = ‘user’;\nmysql> DELETE from [table name] where [field name] = ‘whatever’; "
},
{"id":"49",
"q":"How to Update database permissions/privilages. ",
"answer":"mysql> flush privileges;"
},
{"id":"50",
"q":"How to Load a CSV file into a table.",
"answer":"mysql> LOAD DATA INFILE ‘/tmp/filename.csv’ replace INTO TABLE [table name] FIELDS TERMINATED BY ‘,’ LINES TERMINATED BY ‘n’ (field1,field2,field3);"
}
]
}