Relational Databases Management Systems are core of the most of the web based applications. In order to store and retrive huge volumes of data, you need some kind of Database and when you have a database, you gotta have a Database management system, to take care of the databases, so that it doesn’t turns chaotic and everything remains in place and in order.
Some of the RDBMS that are prevalant in the market are ones developed by
MySQL (although it is now owned by Oracle itself),
and many others.
Since these DBMS and databases are very important or form the core of a web application, hence a Quality Analyst or a tester should have a good first hand knowledge or in-depth knowledge of one of these database management systems.
From the start, I’ve been focused on MySQL. So that’s my database and DBMS of choice. For people out there, let me have some questions mentioned here, which will help you get a good idea of what MySQL is all about. Also these questions would be helpful to any QA/Tester when going for an interview.
1)What is MySQL?
MySQL is a multi-threaded, multi-user SQL database management system which has more than 11 million installations. This is the world’s second most popular and widely used open source database.
2) In which language MySQL is written?
MySQL is written in C and C++ and its SQL parser is written in yacc.
3) What are the technical specification of MySQL?
MySQL has the following technical specifications –
- Flexible structure
- High performance
- Manageable and easy to use
- Replication and high availability
- Security and storage management
4) How many Triggers are possible in MySQL?
There are only six Triggers allowed to use in MySQL database.
- Before Insert
- After Insert
- Before Update
- After Update
- Before Delete
- After Delete
5) What is heap table?
Tables that are present in memory is known as HEAP tables. When you create a heap table in MySQL, you should need to specify the TYPE as HEAP. These tables are commonly known as memory tables. They are used for high speed storage on temporary basis. They do not allow BLOB or TEXT fields.
6) What is BLOB and TEXT in MySQL?
BLOB is an acronym stands for binary large object. It is used to hold a variable amount of data.
There are four types of BLOB.
- TINYBLOB
- BLOB
- MEDIUMBLOB
- LONGBLOB
The differences among all these are the maximum length of values they can hold.
TEXT is case-insensitive BLOB. TEXT values are non-binary strings (character string). They have a character set and values are stored and compared based on the collation of the character set.
There are four types of TEXT.
- TINYTEXT
- TEXT
- MEDIUMTEXT
- LONGTEXT
7) What is a trigger in MySQL?
A trigger is a set of codes that executes in response to some events.
8) What is the difference between heap table and temporary table?
Heap tables:
Heap tables are found in memory. They are used for high speed storage on temporary basis. They do not allow BLOB or TEXT fields.
Heap tables do not support AUTO_INCREMENT.
Indexes should be NOT NULL.
Temporary tables:
The temporary tables are used to keep the temporary data. Sometimes it is very useful in cases to keep temporary data. Temporary table is deleted after current client session terminates.
Main differences:
The heap tables are shared among clients while temporary tables are not shared.
Heap tables are just another storage engine, while for temporary tables you need a special privilege (create temporary table).
9) What is the difference between FLOAT and DOUBLE?
FLOAT stores floating point numbers with accuracy up to 8 places and allocates 4 bytes, on the other hand DOUBLE stores floating point numbers with accuracy up to 18 places and allocates 8 bytes.
10) What are the advantages of MySQL in comparison to Oracle?
- MySQL is a free, fast, reliable, open source relational database while Oracle is expensive, although they have provided Oracle free edition to attract MySQL users.
- MySQL uses only just under 1 MB of RAM on your laptop while Oracle 9i installation uses 128 MB.
- MySQL is great for database enabled websites while Oracle is made for enterprises.
- MySQL is portable.
11) What are the disadvantages of MySQL?
- MySQL is not so efficient for large scale databases.
- It does not support COMMIT and STORED PROCEDURES functions version less than 5.0.
- Transactions are not handled very efficiently.
12) What is the difference between CHAR and VARCHAR?
1) CHAR and VARCHAR are differ in storage and retrieval.
2) CHAR column length is fixed while VARCHAR length is variable.
3) The maximum no. of character CHAR data type can hold is 255 character while VARCHAR can hold up to 4000 character.
4) CHAR is 50% faster than VARCHAR.
5) CHAR uses static memory allocation while VARCHAR uses dynamic memory allocation.
13) What is the difference between MySQL_connect and MySQL_pconnect?
Mysql_connect:
- It opens a new connection to the database.
- Every time you need to open and close database connection, depending on the request.
- Opens page every time when it loaded.
Mysql_pconnect:
- In Mysql_pconnect, “p” stands for persistent connection so it opens the persistent connection.
- the database connection can not be closed.
- it is more useful if your site has more traffic because there is no need to open and close connection frequently and every time when page is loaded.
14) What does ” i_am_a_dummy flag” do in MySQL?
The ” i_am_a_dummy flag” enables MySQL engine to refuse any UPDATE or DELETE statement to execute if the WHERE clause is not present.
15)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.
16) What are the security alerts while using MySQL?
Install antivirus and configure the operating system’s firewall.
Never use the MySQL Server as the UNIX root user.
Change root username and password
Restrict or disable remote access.
17) Explain SHOW commands.
|
18) What is the difference between Unix timestamps and MySQL timestamps?
Actually both Unix timestamp and MySQL timestamp are stored as 32-bit integers but MySQL timestamp is represented in readable format of YYYY-MM-DD HH:MM:SS format.
19) What is 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.
20) What is MySQL default port number?
MySQL default port number is 3306.
21) What is REGEXP?
REGEXP is a pattern match using regular expression. Regular expression is a powerful way of specifying a pattern for a complex search.
22) How many columns can you create for an index?
You can create maximum of 16 indexed columns for a standard table.
23) What is the difference between NOW() and CURRENT_DATE()?
NOW() command is used to show current year, month, date with hours, minutes and seconds while CURRENT_DATE() shows the current year with month and date only.
24) Explain the difference between COMMIT and ROLLBACK?
| COMMIT : The COMMIT statement is used to signify the end of a transaction. At this point all changes to the tables are considered to be durable and will survive server failure.
ROLLBACK : ROLLBACK is used to roll back a transaction to either the state it was in before execution of the transaction or to a certain point prior to where execution is currently occurring. This point is called the SAVEPOINT |
25) Explain GRANT and REVOKE?
There are two commands that are used to control a user’s privileges. The GRANT command is used to give an existing user privileges, and REVOKE is used to remove privileges. If a user does not exist, GRANT will create a new user at the same time we are giving them privileges.
26) What is save point in MySQL?
A defined point in any transaction is known as savepoint.
SAVEPOINT is a statement in MySQL which is used to set a named transaction save point with a name of identifier.
27) What is SQLyog?
SQLyog program is the most popular GUI tool for admin. It is the most popular MySQL manager and admin tool. It combines the features of MySQL administrator, phpMyadmin and others MySQL front ends and MySQL GUI tools.
28) How do you backup a database in MySQl?
It is easy to backing up data with phpMyAdmin. Select the database you want to backup by clicking the database name in the left hand navigation bar. Then click the export button and make sure that all tables are highlighted that you want to backup. Then specify the option you want under export and save the output.
29) How many levels that privileges can be granted?
There are five levels that privileges can be granted-
Global – Global privileges apply to all databases on a MySQL server. These privileges are stored in the mysql.user table.
Database – Database privileges apply to all objects of a specified database. These privileges are stored in the mysql.db and mysql.host tables.
Table – Table privileges apply to all columns in a given table. These privileges are stored in the mysql.tables_priv table. The GRANT ALL ON db_name.table_name and REVOKE ALL ON db_name.table_name commands grant and revoke only table level privileges.
Column – Column level privileges apply to one or more columns in a given table. These privileges are stored in the mysql.columns_priv table. When using the REVOKE command to remove column level privileges, you must specify the same columns that were granted. The column or columns for which the privileges are to be granted are enclosed within parentheses.
Routine – The CREATE ROUTINE, ALTER ROUTINE, EXECUTE, and GRANT privileges apply to stored routines (functions and procedures). They can be granted at the global and database levels. Also, except for CREATE ROUTINE, these privileges can be granted at the routine level for individual routines. The privileges are stored in the mysql.procs_priv table.
30) IS SQL reserved words like SELECT, FROM, NULL, and AS are Case Sensitive?
No. These words are case-insensitive, meaning that SELECT, select, and SeLeCt are all parsed by mysqld as the same reserved word.
31) What do you use with ALTER TABLE statement?
- Add or drop columns
- Change the name or definition of a column
- Add or drop indexes
- Sort the table’s rows in a particular order
- Rename the table
32) What are the types of indexes in MySQL?
|
In case you’re wondering about some basic and advanced queries that you need to learn then here is a comprehensive list of query questions.
QATechnicals 