Q1. What is the difference between Where and having clause?
A: HAVING specifies search condition for a group or an aggregate function used in SELECT statement.
HAVING is typically used in GROUP BY clause. When GROUP BY is not used, HAVING behaves like WHERE clause.
Q2. What is Normalization and its types?
Q3. Different types of tables in MySql?
Q4. Different storage engines in MySql?
A:
a) MyISAM:
It was the default table type before MySQL version 5.5.
optimized for compression and speed
Employs table locking.
Size of MyISAM table can be up to 256TB
b) InnoDB:
From version 5.5, MySQL uses InnoDB as the default storage engine
Supports transactions and referential integrity
Supports foreign keys, commit, rollback, roll-and forward operations
Size of an InnoDB table can be up to 64TB.
c) Merge:
Virtual table that combines multiple MyISAM tables that have similar structure into one table.
d) Memory:
Stored in memory and use hash indexes
Faster than MyISAM tables.
Lifetime of the data of depends on the up time of the database server.
Memory storage engine is formerly known as HEAP.
e) Archive
The archive storage engine is used to store large number of records, which for archiving purpose, into a compressed format to save disk space.
Compresses a record when it is inserted and decompress it using zlib library as it is read.
f) CSV
Stores data in comma-separated values (CSV) file format.
g) FEDERATED
Allows you to manage data from a remote MySQL server without using cluster or replication technology.
Q5. What is the difference between Myisam and Innodb?
Q6. What is mysql_pconnect()?
A: mysql_pconnect will first try to find persistent link which is already open with the same host, username and password. If that one is found, an identifier for it will be returned instead of opening a new connection.
Using mysql_pconnect, connection to SQL server will not be closed when the execution of the script ends. Instead, the link will remain open for future use.
Q7. What are stored procedures? Share is it’s Syntax with example!
A: A stored procedure is segment of declarative SQL statements stored inside the database catalog. Stored procedure helps in increasing the performance of applications. MySQL stored procedures are compiled on demand. After compiling the stored procedure, MySQL puts it to a cache.
Stored procedures helps in reducing traffic between application and database server. So instead of sending multiple SQL statements, the application sends only name and parameters of the stored procedure.
Syntax:
——
CREATE PROCEDURE GetUsers() //Stored procedure name
BEGIN
SELECT * FROM users;
END
How to Call it:
————–
CALL GetUsers(); // Stored procedure name
How to use the IN parameter
—————————
// to selects users located in a particular location
CREATE PROCEDURE GetUsers(IN locationName VARCHAR(255))
BEGIN
SELECT * FROM users
WHERE location = locationName;
END
Get all users for a particular location ‘Haryana’
————————————————
CALL GetUsers(‘Haryana’);
Q8. What is Index and how we use Indexing?
A: Indexes are used to find rows with specific column values quickly. Without an index, MySQL must begin with the first row and then read through entire table to find relevant rows.
MySQL indexes are: (PRIMARY KEY, UNIQUE, INDEX, and FULLTEXT).
Syntax:
CREATE INDEX index_name
ON tbl_name
index_col_name [(length)];
MySQL uses indexes for:
– To find the rows matching a WHERE clause quickly.
– To eliminate rows from consideration. If there is a choice between multiple indexes, MySQL normally uses the index that finds the smallest number of rows
Q9. How to get the second highest salary?
Q10. Difference between Primary Key and Unique key?
A:
Primary Key
– A table can have only one PRIMARY KEY Column
– It doesn’t allow Null values.
Unique Key
– A table can have more than one UNIQUE Key Columns
– Allows Null value. But only one Null value.
Q12. What is referential integrity?
A: Referential integrity is a relational database concept in which multiple tables share a relationship based on the data stored in the tables, and that relationship must remain consistent. It is something that must be enforced
Q12. What is Foreign key?
A: It is a field or a set of fields in a table that uniquely identifies a row of another table. The table in which the foreign key is defined is called the “child table” and often refers to the primary key in the parent table.
Q13. How to do search based on relevance?
A: Here is the example for it:
SELECT * FROM <TABLE-NAME>
WHERE MATCH(<COLUMN-NAME>) AGAINST(‘+keyword1 +keyword2 +keyword3’ IN BOOLEAN MODE)
ORDER BY MATCH(<COLUMN-NAME>) AGAINST(‘+keyword1 +keyword2 +keyword3′ IN BOOLEAN MODE) DESC;
> In above query, “+” indicates that these keywords MUST be present in the text.
Q14. Maximum no of tables?
A: MySQL has no limit on the number of tables & databases
Q15. Maximum number of columns in table?
A: Approx 4096 columns per table.
Q16: What is Database Optimization?
A: Database optimization refers to variety of strategies for reducing database system response time. It involves maximizing the speed and efficiency with which data is retrieved.
Optimizing the database:-
1. Tables should be properly structured. All columns should have right data types
2. Index should be used correctly to get the results fast.
3. Appropriate storage engine should be used.
Q17: What is the maximum length of a table name, a database name, or a field name in MySQL?
A: Database name: 64 characters
Table name: 64 characters
Column name: 64 characters
Q18: Query for finding 2nd max salary from ’employees’ table
A: > Option 1: SELECT MAX(Salary) FROM employee WHERE Salary NOT IN (SELECT MAX(Salary) FROM employee )
> Option 2: SELECT Salary from employee order by Salary desc limit 1,1
Q19: Where MyISAM table will be stored and in which formats?
A: 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) extensionQ20: What is the default port for MySQL Server?
A: Default port is 3306.
Q21: What will happen when the column is set to ‘AUTO INCREMENT’ and you reaches the maximum value in the table?
A: It will stop incrementing. Any further inserts will produce an error, since the key has been used already.
Q22: What are Heap tables?
A: HEAP tables are present in memory. They are used for high speed storage on temporary basis and commonly known as memory tables.
