MySQL Interview

Multiple column subqueries return more than one column from the inner SELECT statement.

True

What are the different tables present in MySQL?

Total 5 types of tables are present: MyISAM Heap Merge INNO DB ISAM MyISAM is the default storage engine as of MySQL .

A multiple row subquery returns more than one row from the inner SELECT statement.

True

A view doesn't have data of its own.

True

If a column value taking part in an arithmetic expression is NULL, then the result obtained would be NULLM. True or False

True

The DISTINCT keyword allows a function consider only non-duplicate values.

True

While inserting new rows in a table you must list values in the default order of the columns.

True

How can we convert between Unix & MySQL timestamps?

UNIX_TIMESTAMP is the command which converts from MySQL timestamp to Unix timestamp FROM_UNIXTIME is the command which converts from Unix timestamp to MySQL timestamp.

What is InnoDB?

lnnoDB is a transaction safe storage engine developed by Innobase Oy which is a Oracle Corporation now.

What is ISAM?

ISAM is abbreviated as Indexed Sequential Access Method.It was developed by IBM to store and retrieve data on secondary storage systems like tapes.

How to get current MySQL version?

SELECT VERSION (); is used to get the current version of MySQL.

What are the column comparisons operators?

The = , <>, <=, <, >=, >,<<,>>, <=>, AND, OR, or LIKE operators are used in column comparisons in SELECT statements.

Which SQL statement is used to add, modify or drop columns in a database table?

The ALTER TABLE statement.

What is the purpose of the condition operators BETWEEN and IN?

The BETWEEN operator displays rows based on a range of values. The IN condition operator checks for values contained in a specific set of values.

What is the default port for MySQL Server?

The default port for MySQL server is 3306.

What does myisamchk do?

It compress the MyISAM tables, which reduces their disk or memory usage.

What do you mean by % and _ in the LIKE statement?

% corresponds to 0 or more characters, _ is exactly one character in the LIKE statement.

What is the difference between BLOB AND TEXT?

A BLOB is a binary large object that can hold a variable amount of data. There are four types of BLOB - TINYBLOB BLOB MEDIUMBLOB and LONGBLOB They all differ only in the maximum length of the values they can hold. A TEXT is a case-insensitive BLOB. The four TEXT types TINYTEXT TEXT MEDIUMTEXT and LONGTEXT They all correspond to the four BLOB types and have the same maximum lengths and storage requirements. The only difference between BLOB and TEXT types is that sorting and comparison is performed in case-sensitive for BLOB values and case-insensitive for TEXT values.

Is a NULL value same as zero or a blank space? If not then what is the difference?

A NULL value is not same as zero or a blank space. A NULL value is a value which is 'unavailable, unassigned, unknown or not applicable'. Whereas, zero is a number and blank space is a character.

If a table contains duplicate rows, does a query result display the duplicate values by default? How can you eliminate duplicate rows from a query result?

A query result displays all rows including the duplicate rows. To eliminate duplicate rows in the result, the DISTINCT keyword is used in the SELECT clause.

What do you understand by a subquery? When is it used?

A subquery is a SELECT statement embedded in a clause of another SELECT statement. It is used when the inner query, or the subquery returns a value that is used by the outer query. It is very useful in selecting some rows in a table with a condition that depends on some data which is contained in the same table.

What is a view? Why should you use a view?

A view is a logical snapshot based on a table or another view. It is used for − Restricting access to data; Making complex queries simple; Ensuring data independency; Providing different views of same data.

What happens if you omit the WHERE clause in a delete statement?

All the rows in the table are deleted.

What happens if you omit the WHERE clause in the UPDATE statement?

All the rows in the table are modified.

Explain Access Control Lists.

An ACL (Access Control List) is a list of permissions that is associated with an object. This list is the basis for MySQL server's security model and it helps in troubleshooting problems like users not being able to connect. MySQL keeps the ACLs (also called grant tables) cached in memory. When a user tries to authenticate or run a command, MySQL checks the authentication information and permissions against the ACLs, in a predetermined order.

Differentiate CHAR_LENGTH and LENGTH?

CHAR_LENGTH is character count whereas the LENGTH is byte count. The numbers are same for Latin characters but they are different for Unicode and other encodings.

What are all the Common SQL Function?

CONCAT(A, B) - Concatenates two string values to create a single string output. Often used to combine two or more fields into one single field. FORMAT(X, D) - Formats the number X to D significant digits. CURRDATE(), CURRTIME() - Returns the current date or time. NOW() - Returns the current date and time as one value. MONTH(), DAY(), YEAR(), WEEK(), WEEKDAY() - Extracts the given data from a date value. HOUR(), MINUTE(), SECOND() - Extracts the given data from a time value. DATEDIFF(A, B) - Determines the difference between two dates and it is commonly used to calculate age SUBTIMES(A, B) - Determines the difference between two times. FROMDAYS(INT) - Converts an integer number of days into a date value.

How MySQL Optimizes DISTINCT?

DISTINCT is converted to a GROUP BY on all columns and it will be combined with ORDER BY clause. SELECT DISTINCT t1.a FROM t1,t2 where t1.a=t2.a;

What is the usage of ENUMs in MySQL?

ENUM is a string object used to specify set of predefined values and that can be used during table creation. Create table size(name ENUM('Small', 'Medium','Large');

How to represent ENUMs and SETs internally?

ENUMs and SETs are used to represent powers of two because of storage optimizations.

Where MyISAM table will be stored and also give their formats of storage?

Each MyISAM table is stored on disk in three formats: The '.frm' file stores the table definition The data file has a '.MYD' (MYData) extension The index file has a '.MYI' (MYIndex) extension

What is the difference between primary key and candidate key?

Every row of a table is identified uniquely by primary key. There is only one primary key for a table. Primary 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.

A single row subquery returns only one row from the outer SELECT statement. True or False

False. A single row subquery returns only one row from the inner SELECT statement.

By default the group functions consider only distinct values in the set. True or False

False. By default, group functions consider all values including the duplicate values.

COUNT(*) returns the number of columns in a table.

False. COUNT(*) returns the number of rows in a table.

Group functions cannot be nested. True or false

False. Group functions can be nested to a depth of two.

What are federated tables?

Federated tables which allow access to the tables located on other databases on other servers.

What are the nonstandard string types?

Following are Non-Standard string types: TINYTEXT TEXT MEDIUMTEXT LONGTEXT

Differentiate between FLOAT and DOUBLE?

Following are differences for FLOAT and DOUBLE: • Floating point numbers are stored in FLOAT with eight place accuracy and it has four bytes. • Floating point numbers are stored in DOUBLE with accuracy of 18 places and it has eight bytes.

Difference between CHAR and VARCHAR?

Following are the differences between CHAR and VARCHAR: CHAR and VARCHAR types differ in storage and retrieval CHAR column length is fixed to the length that is declared while creating table. The length value ranges from 1 and 255 When CHAR values are stored then they are right padded using spaces to specific length. Trailing spaces are removed when CHAR values are retrieved.

What is the difference between mysql_fetch_array and mysql_fetch_object?

Following are the differences between mysql_fetch_array and mysql_fetch_object: mysql_fetch_array() -Returns a result row as an associated array or a regular array from database. mysql_fetch_object - Returns a result row as object from database.

What are the drivers in MySQL?

Following are the drivers available in MySQL: PHP Driver JDBC Driver ODBC Driver C WRAPPER PYTHON Driver PERL Driver RUBY Driver CAP11PHP Driver Ado.net5.mxj

What are various DCL commands in SQL? Give brief description of their purposes.

Following are various DCL or Data Control Language commands in SQL − GRANT − it gives a privilege to user. REVOKE − it takes back privileges granted from user.

What are various DDL commands in SQL? Give brief description of their purposes.

Following are various DDL or Data Definition Language commands in SQL − CREATE − it creates a new table, a view of a table, or other object in database. ALTER − it modifies an existing database object, such as a table. DROP − it deletes an entire table, a view of a table or other object in the database.

What are various DML commands in SQL? Give brief description of their purposes.

Following are various DML or Data Manipulation Language commands in SQL − SELECT − it retrieves certain records from one or more tables. INSERT − it creates a record. UPDATE − it modifies records. DELETE − it deletes records.

How can we run batch mode in mysql?

Following commands are used to run in batch mode: mysql ; mysql mysql.out

What are the objects can be created using CREATE statement?

Following objects are created using CREATE statement: DATABASE EVENT FUNCTION INDEX PROCEDURE TABLE TRIGGER USER VIEW

What is the purpose of the group functions in SQL? Give some examples of group functions.

Group functions in SQL work on sets of rows and returns one result per group. Examples of group functions are AVG, COUNT, MAX, MIN, STDDEV, SUM, VARIANCE.

What are Heap tables?

HEAP tables are present in memory and they are used for high speed storage on temporary basis. • BLOB or TEXT fields are not allowed • Only comparison operators can be used =, <,>, = >,=< • AUTO_INCREMENT is not supported by HEAP tables • Indexes should be NOT NULL

What's wrong in the following query? SELECT student_code, name FROM students WHERE marks = (SELECT MAX(marks) FROM students GROUP BY subject_code);

Here a single row operator = is used with a multiple row subquery.

What are the various multiple row comparison operators in SQL?

IN, ANY, ALL.

How to enter Characters as HEX Numbers?

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). A HEX number string will be automatically converted into a character string, if the expression context is a string.

What is the difference between MyISAM Static and MyISAM Dynamic?

In MyISAM static all the fields will have fixed width. The Dynamic MyISAM table will have fields like TEXT, BLOB, etc. to accommodate the data types with various lengths. MyISAM Static would be easier to restore in case of corruption.

How to display top 50 rows?

In MySql, top 50 rows are displayed by using this following query: SELECT * FROM LIMIT 0,50;

How do you search for a value in a database table when you don't have the exact value to search for?

In such cases, the LIKE condition operator is used to select rows that match a character pattern. This is also called 'wildcard' search.

What's wrong in the following query? SELECT subject_code, count(name) FROM students;

It doesn't have a GROUP BY clause. The subject_code should be in the GROUP BY clause.

What happens when the column is set to AUTO INCREMENT and if you reach maximum value in the table?

It stops incrementing. Any further inserts are going to produce an error, since the key has been used already.

How can we find out which auto increment was assigned on Last insert?

LAST_INSERT_ID will return the last value assigned by Auto_increment and it is not required to specify the table name.

What is the difference between the LIKE and REGEXP operators?

LIKE and REGEXP operators are used to express with ^ and %. SELECT * FROM employee WHERE emp_name REGEXP "^b"; SELECT * FROM employee WHERE emp_name LIKE "%b";

What are the case manipulation functions of SQL?

LOWER, UPPER, INITCAP

How many columns can be used for creating Index?

Maximum of 16 indexed columns can be created for any standard table.

How do you control the max size of a HEAP table?

Maximum size of HeaP table can be controlled by MySQL config variable called max_heap_table_size.

Why MySQL is used?

MySQL database server is reliable, fast and very easy to use. This software can be downloaded as freeware and can be downloaded from the internet.

2. What are the technical features of MySQL?

MySQL database software is a client or server system which includes Multithreaded SQL server supporting various client programs and libraries Different backend Wide range of application programming interfaces and Administrative tools.

What is MySQL?

MySQL is an open source DBMS which is built, supported and distributed by MySQL AB (now acquired by Oracle)

What are the advantages of MySQL when compared with Oracle?

MySQL is open source software which is available at any time and has no cost involved. MySQL is portable GUI with command prompt. Administration is supported using MySQL Query Browser

What is the different between NOW() and CURRENT_DATE()?

NOW () command is used to show current year,month,date with hours,minutes and seconds. CURRENT_DATE() shows current year,month and date only.

Can a drop table statement be rolled back

No

Is Mysql query is case sensitive?

No.

How do you insert null values in a column while inserting data?

Null values can be inserted into a table by one of the following ways − Implicitly by omitting the column from the column list. Explicitly by specifying the NULL keyword in the VALUES clause.

How can we get the number of rows affected by query?

Number of rows can be obtained by SELECT COUNT (user_id) FROM users;

What is the difference between SQL and PL/SQL?

PL/SQL is a dialect of SQL that adds procedural features of programming languages in SQL. It was developed by Oracle Corporation in the early 90's to enhance the capabilities of SQL.

Define REGEXP?

REGEXP is a pattern match in which matches pattern anywhere in the search value.

How can you see all indexes defined for a table?

SHOW INDEX FROM <tablename>;

How many TRIGGERS are allowed in MySql table?

SIX triggers are allowed in MySql table. They are as follows: BEFORE INSERT AFTER INSERT BEFORE UPDATE AFTER UPDATE BEFORE DELETE and AFTER DELETE

What are the specific uses of SQL functions?

SQL functions have the following uses − Performing calculations on data Modifying individual data items Manipulating the output Formatting dates and numbers Converting data types

What is the difference between SQL and MySQL or SQL Server?

SQL or Structured Query Language is a language; language that communicates with a relational database thus providing ways of manipulating and creating databases. MySQL and Microsoft's SQL Server both are relational database management systems that use SQL as their standard relational database language.

. What storage engines are used in MySQL?

Storage engines are called table types and data is stored in files using various techniques. Technique involves: Storage mechanism Locking levels Indexing Capabilities and functions.

What does a TIMESTAMP do on UPDATE CURRENT_TIMESTAMP data type?

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.

Discuss the syntax and use of the COALESCE function?

The COALESCE function has the expression COALESCE(exp1, exp2, .... expn) It returns the first non-null expression given in the parameter list.

What is the pupose of DML statements in SQL?

The DML statements are used to add new rows to a table, update or modify data in existing rows, or remove existing rows from a table.

Which statement is used to add a new row in a database table?

The INSERT INTO statement.

How do you copy rows from one table to another?

The INSERT statement can be used to add rows to a table by copying from another table. In this case, a subquery is used in the place of the VALUES clause.

What is the purpose of the MERGE statement in SQL?

The MERGE statement allows conditional update or insertion of data into a database table. It performs an UPDATE if the rows exists, or an INSERT if the row does not exist.

Which function returns the remainder in a division operation?

The MOD function returns the remainder in a division operation.

What is the use of the NULLIF function?

The NULLIF function compares two expressions. If they are equal, the function returns null. If they are not equal, the first expression is returned.

What is the purpose of the NVL function?

The NVL function converts a NULL value to an actual value.

What is the difference between the NVL and the NVL2 functions?

The NVL(exp1, exp2) function converts the source expression (or value) exp1 to the target expression (or value) exp2, if exp1 contains NULL. The return value has the same data type as that of exp1. The NVL2(exp1, exp2, exp3) function checks the first expression exp1, if it is not null then, the second expression exp2 is returned. If the first expression exp1 is null, then the third expression exp3 is returned.

What's wrong in the following query? SELECT subject_code, AVG (marks) FROM students WHERE AVG(marks) > 75 GROUP BY subject_code;

The WHERE clause cannot be used to restrict groups. The HAVING clause should be used. SELECT subject_code, AVG (marks) FROM students HAVING AVG(marks) > 75 GROUP BY subject_code;

What is the difference between cross joins and natural joins?

The cross join produces the cross product or Cartesian product of two tables. The natural join is based on all the columns having same name and data types in both the tables.

What is the default ordering of data using the ORDER BY clause? How could it be changed?

The default sorting order is ascending. It can be changed using the DESC keyword, after the column name in the ORDER BY clause.

You want to display a result query from joining two tables with 20 and 10 rows respectively. Erroneously you forget to write the WHERE clause. What would be the result?

The result would be the Cartesian product of two tables with 20 x 10 = 200 rows.

Give string types available for column?

The string types are: SET BLOB ENUM CHAR TEXT VARCHAR

Which expressions or functions allow you to implement conditional processing in a SQL statement?

There are two ways to implement conditional processing or IF-THEN-ELSE logic in a SQL statement. Using CASE expression Using the DECODE function

What, if a table has one column defined as TIMESTAMP?

Timestamp field gets the current timestamp whenever the row gets altered.

What is the difference between VARCHAR2 AND CHAR datatypes?

VARCHAR2 represents variable length character data, whereas CHAR represents fixed length character data.

How do you login to MySql using Unix shell?

We can login through this command: # [mysql dir]/bin/mysql -h hostname -u <UserName> -p <password>

Can you remove rows from a table based on values from another table? Explain.

Yes, subqueries can be used to remove rows from a table based on values from another table.

Can you sort a column using a column alias?

Yes. A column alias could be used in the ORDER BY clause.

Can you modify the rows in a table based on values from another table? Explain.

Yes. Use of subqueries in UPDATE statements allow you to update rows in a table based on values from another table.