MySql

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?
Q13. How to do search based on relevance?
Q14. Maximum size of table?
Q15. Maximum number of columns in 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

Leave a Reply

Your email address will not be published. Required fields are marked *