MySQL
1. What is SQL Server?
SQL Server is one of the database management systems (DBMS) and is designed by
Microsoft. DBMS are computer software applications with the capability of interacting with users,
various other applications, and databases. The objective of SQL Server is capturing and analyzing
data and managing the definition, querying, creation, updating, and administration of the database.
2. What is the default port for MySQL Server?
The default port for MySQL Server is 3306. Another standard default port is 1433 in
TCP/IP for SQL Server.
3. What is the difference between CHAR and VARCHAR?
When a table is created, CHAR is used to define the fixed length of the table and columns.
The length value could be in the range of 1–255. The VARCHAR command is used to adjust the
column and table lengths as required.
4. How can a user get the current SQL version?
The syntax for getting the current version of MySQL: SELECT VERSION ();
5. What is the difference between primary key and unique key?
While both are used to enforce the uniqueness of the column defined, the primary key would
create a clustered index, whereas the unique key would create a non-clustered index on the column.
The primary key does not allow ‘NULL’, but the unique key does.
6. What are the differences between a primary key and a foreign key?
Primary Key: It helps in the unique identification of data in a database. There can be only
one primary key for a table. Primary key attributes cannot have duplicate values in a table. Null
values are not acceptable. We can define primary key constraints for temporarily created tables.
The primary key index is automatically created.
Foreign Key: It helps establish a link between tables. There can be more than one foreign
key for a table. Duplicate values are acceptable for a foreign key. Null values are acceptable. It
cannot be defined for temporary tables. The index is not created automatically.
7. What is the use of ENUM in MySQL?
The use of ENUM will limit the values that can go into a table. For instance, a user can
create a table giving specific month values and other month values would not enter into the table.
8. What is the difference between LIKE and REGEXP operators in MySQL?
LIKE matches an entire column. If the text to be matched existed in the middle of a column
value, LIKE would not find it and the row would not be returned (unless wildcard characters were
used). LIKE is denoted using the ‘%’ sign.
For example: SELECT * FROM user WHERE user name LIKE “%NAME”
REGEXP, on the other hand, looks for matches within column values, and so if the text to be
matched existed in the middle of a column value, REGEXP would find it and the row would be
returned. The use of REGEXP is as follows:
SELECT * FROM user WHERE username REGEXP “^NAME”;
9. What are the types of joins in MySQL?
There are four types of joins in MySQL.
Inner join returns rows if there is at least one match in both tables. Left join returns all the
rows from the left table even if there is no match in the right table. Right join returns all the rows
from the right table even if no matches exist in the left table. Full join would return rows when there
is at least one match in the tables.
10. What are the storage models of OLAP?
The storage models in OLAP are MOLAP, ROLAP, and HOLAP.
11. What are the features of MySQL?
MySQL provides cross-platform support, a wide range of interfaces for application
programming, and has many stored procedures like triggers and cursors that help in managing the
database.
12. What are the advantages and disadvantages of using MySQL?
Advantages
• MySQL helps in the secure management of databases. By using it, we can securely execute
database transactions.
• It is fast and efficient in comparison to other database management systems as it supports
varieties of storage engines.
• As its transaction processing is high, MySQL can execute millions of queries.
Disadvantages
• Scalability in MySQL is a redundant task.
• MySQL serves good for large databases mostly.
• There are issues of the instability of software.
13. What do DDL, DML, and DCL stand for?
DDL is the abbreviation for Data Definition Language dealing with database schemas, as
well as the description of how data resides in the database. An example of this is the CREATE
TABLE command. DML denotes Data Manipulation Language which includes commands such as
SELECT, INSERT, etc. DCL stands for Data Control Language and includes commands like
GRANT, REVOKE, etc.
14. What is a join in MySQL?
In MySQL, joins are used to query data from two or more tables. The query is made using
the relationship between certain columns existing in the table. There are four types of joins in
MySQL.
Inner join returns rows if there is at least one match in both tables. Left join returns all the
rows from the left table even if there is no match in the right table. Right join returns all the rows
from the right table even if no matches exist in the left table. Full join would return rows when there
is at least one match in the tables.
15. What are the common MySQL functions?
Common MySQL functions are as follows:
• ABS(): Returns the absolute value of a number. It removes the negative sign if the number is
negative.
• ROUND(): Rounds a number to a specified number of decimal places. It can round to the
nearest integer or a specific decimal position.
• CEIL(): Returns the smallest integer greater than or equal to a given number. It rounds up
the value to the nearest integer.
• FLOOR(): Returns the largest integer less than or equal to a given number. It rounds down
the value to the nearest integer.
• EXP(): Calculates the exponential value of a number. It returns the result of raising the
mathematical constant e to the power of the given number.
• LOG(): Calculates the natural logarithm of a number. It returns the logarithm base e (natural
logarithm) of the given number.
• NOWO: The function for returning the current date and time as a single value
• CURRDATEO: The function for returning the current date or time
• CONCAT (X, Y): The function to concatenate two string values creating a single string
output
• DATEDIFF (X, Y): The function to determine the difference between two dates
16. What is the syntax for concatenating tables in MySQL?
The syntax for concatenating tables in MySQL: CONCAT (string 1, string 2, string 3)
17. What is the limit of indexed columns that can be created for a table?
It depends on the storage engine used:
For the MyISAM storage engine, the limit is 64 while for the InnoDB storage engine, the limit is
16.
18. What are the different types of strings used in database columns in MySQL?
In MySQL, the different types of strings that can be used for database columns are SET,
BLOB, VARCHAR, TEXT, ENUM, and CHAR.
18. Is there an object-oriented version of MySQL library functions?
Yes. MySQL is the object-oriented version of MySQL, and it interfaces in PHP.
20. What is the storage engine used for MySQL?
The storage engine used for MySQL refers to the component responsible for managing how
data is stored, organized, and accessed within the database system. The default storage engine in
MySQL is InnoDB, offering features like transaction support and referential integrity.
Other commonly used engines include MyISAM, known for its simplicity and performance,
and NDB Cluster, providing distributed storage for MySQL Cluster. The choice of engine depends
on specific application requirements such as data integrity, performance, and scalability.
21. What is the difference between the primary key and the candidate key?
The primary key in MySQL is used to identify every row of a table in a unique manner. For
one table, there is only one primary key. The candidate keys can be used to reference the foreign
keys. One of the candidate keys is the primary key.
22. What are the different types of tables in MySQL?
MyISAM is the default table that is based on the sequential access method.
• Heap is the table that is used for fast data access, but the data will be lost if the table or the
system crashes.
• InnoDB is the table that supports transactions using the COMMIT and ROLLBACK
commands.
• BDB can support transactions similar to InnoDB, but the execution is slower.
23. What are the TRIGGERS that can be used in MySQL tables?
Following TRIGGERS are allowed in MySQL:
BEFORE INSERT
• AFTER INSERT
• BEFORE UPDATE
• AFTER UPDATE
• BEFORE DELETE
• AFTER DELETE
24. How to use the MySQL slow query log?
Information that is provided on the slow query log could be huge in size. The query could
also be listed over a thousand times. In order to summarize the slow query log in an informative
manner, one can use the third-party tool ‘pt-query-digest’.
25. How can one take an incremental backup in MySQL?
A user can take an incremental backup in MySQL using Percona XtraBackup.
26. What is meant by transaction? What are ACID properties?
In the context of databases, a transaction refers to a logical unit of work that consists of one
or more database operations. These operations are treated as a single, indivisible unit, meaning they
either all succeed or all fail. Transactions are used to ensure data consistency and integrity within a
database system.
Transactions are commonly used in scenarios where multiple database operations need to be
executed as a cohesive unit. For example, consider a banking application where a transfer of funds
involves deducting an amount from one account and adding it to another account. In this case, the
deducting and adding operations should be performed together to maintain data consistency. If one
operation succeeds but the other fails, it could lead to an inconsistent state in the database.
ACID properties are a set of fundamental principles that ensure reliability and consistency in
database transactions. ACID stands for Atomicity, Consistency, Isolation, and Durability.
27. What is the difference between BLOB and TEXT?
BLOBs are binary large objects holding huge data. The 4 types of BLOB are:
TINYBLOB: This data type can store up to 255 bytes of binary data.
BLOB: This data type can store up to 65,535 bytes of binary data.
MEDIUMBLOB: This data type can store up to 16,777,215 bytes of binary data.
LONGBLOB: This data type can store up to 4 GB of binary data.
TEXT is a case-sensitive BLOB. Four types of TEXT are TINY TEXT, TEXT, MEDIUMTEXT,
and LONG TEXT.
28. What is the TIMESTAMP data type?
Timestamp in SQL Server helps in row versioning. Row versioning is a type of concurrency
that allows retaining the value until it is committed in the database. It shows the instant time of any
event. It consists of both the date and time of the event. Also, timestamp helps in backing up data
during the failure of a transaction.
While we insert, update, or delete a record, the date and time automatically get inserted.
• Format of timestamp: YYYY-MM-DD HH:MM: SS
• Range of timestamp: “1970-01-01 00:00:01” UTC to “2038-01-19 03:14:07” UTC
29. What is the function of mysqldump?
As the name suggests, mysqldump is used to dump one or more created databases. It
performs backups for data or transfers the data from SQL Server to another. Also, it helps in
producing the initial database schema by logical backups. Moreover, unlike triggers, mysqldump
does not backup the stored procedures or functions by default.
For a single database: mysqldump [options] db_name [tables]
For multiple databases: mysqldump [options] –databases db1 [db2 db3...]
For all databases: mysqldump [options] –all-databases
30. How to create a user-defined function in MySQL?
User-defined functions are created using the ‘CREATE FUNCTION’ syntax. It allows you to
define your function that can be used in SQL statements. They come in handy when you perform
calculations or operations frequently.
31. What are subqueries in MySQL?
When we write queries inside a query or nested queries are called subqueries. They allow
complex operations and can be used in SELECT, INSERT, UPDATE, or DELETE statements. It
can return single values, row sets, or tables as well as an output. They are often used for comparison
and aggregation.
32. How does MySQL handle transactions?
MySQL handles transactions using START TRANSACTION, COMMIT, and ROLLBACK.
Transactions ensure that a series of database operations will either all succeed or fail, maintaining
data integrity.
33. How to implement full-text search in MySQL?
Full-text search in MySQL can be implemented using FULLTEXT indexes. It allows natural
language searching of text-based columns and is particularly efficient for searching large texts
within the VARCHAR and TEXT columns.
34. What is known as the covering index in MySQL?
A covering index is an index that includes all the columns needed to answer a query,
allowing the database to retrieve query results directly from the index without accessing the table
data.
35. What is the use of MySQL in web development?
MySQL is a cornerstone for web development, offering a reliable and efficient database
management system for storing and retrieving data for websites and supporting dynamic content
generation using the CRUD (Create, Read, Update, Delete) operations performed in MySQL.
36. How to handle large datasets in MySQL?
Handling large datasets in MySQL efficiently requires partitioning tables, using indexing
strategically, optimizing queries, and possibly leveraging MySQL’s advanced storage engines like
InnoDB for better performance and reliability.
37. What is the difference between the DELETE TABLE and TRUNCATE TABLE commands
in MySQL?
DELETE TABLE
1. Syntax and Usage: Typically, you would use DELETE FROM table_name [WHERE
condition];. “DELETE TABLE” is not a standard SQL statement.
2. Logged Operation: DELETE is indeed a logged operation, and each row deletion is logged.
3. Where Clause: It allows for conditionally deleting data, i.e., you can specify a WHERE
clause to delete specific data.
4. Triggers: DELETE will activate any triggers associated with the table.
5. Speed: It is generally slower than TRUNCATE especially for deleting all rows.
6. Space Reclaim: Space used by the table is not reclaimed (unless using DELETE with no
WHERE clause in some MySQL storage engines like InnoDB).
TRUNCATE TABLE
1. Syntax and Usage: The correct syntax is TRUNCATE TABLE table_name;.
2. Logged Operation: TRUNCATE is also a logged operation, but it typically logs fewer
transactions because it logs the deallocation of the data pages in which the data exists, not
the individual row deletions.
3. Where Clause: It does not allow for a WHERE clause. It will remove all rows.
4. Triggers: TRUNCATE will not activate triggers.
5. Speed: It is usually faster for deleting all rows in a table because it does not log individual
row deletions.
6. Space Reclaim: Space used by the table is reclaimed, and the table is reset to its empty
state, often times also resetting the auto-increment value to zero (or the starting value).
Rollback
Both DELETE and TRUNCATE operations can be rolled back if used within a transaction
that is not yet committed. However, it’s essential to note that TRUNCATE is a data definition
language (DDL) statement, and in some database systems, it might auto-commit the transaction,
making the rollback impossible for the previous transactions within the same transaction block.
38. How to define the testing of network layers in MySQL?
For this, it is necessary to review the layered architecture and determine hardware and
software configuration dependencies with respect to the application put to test.
38. How can one restart SQL Server in the single user or the minimal configuration modes?
The command line SQLSERVER.EXE used with ‘–m’ will restart SQL Server in the single-
user mode and the same with ‘–f’ will restart it in the minimal configuration mode.
40. What is an access control list?
Every organization has some crucial data specific to its business. This data needs secure
access so that any consequence due to data loss can be avoided. For this, organizations create a
sequence of permissions that are linked to various data objects. These lists are known as the access
control list (ACL).
ACL serves as the basis for the server’s security that helps troubleshoot the connection
problems for users. These are also known as grant tables that are cached by MySQL. MySQL
verifies a user for authentication and grants permissions in a sequence when the user executes a
command.
41. What is the main difference between MySQL and PostgreSQL?
My SQL is purely a relational database whereas PostgreSQL is an object-relational database.
PostgreSQL is more complex and slower than MySQL. In MYSQL, troubleshooting is easy but it is
difficult to troubleshoot PostgreSQL. MySQL does not support materialized view whereas
PostgreSQL support materialized view.
42. How can you optimize MySQL queries for faster data retrieval?
To optimize MySQL queries for faster data retrieval, indexing on columns can be used in
WHERE clauses; avoid SELECT *; and refine queries with EXPLAIN to analyze performance.
Implementing query caching can also significantly reduce load times for frequently requested data.
43. Explain the GRANT command in MySQL.
The GRANT command is used to give permission or certain privileges to perform various
database operations whenever a new user is created.
44. What is the use of the DELIMITER command in MySQL?
The DELIMITER command is used to change the default delimiter used by MySQL, which
is a semicolon (;). DELEIMITER is used while writing TRIGGER and STORED PROCEDURES
in MySQL.
45. How do you search exactly as you type in MySQL?
To search exactly as typed, add BINARY before your search term. For instance, SELECT *
FROM table_name WHERE BINARY column_name = ‘value’; checks for exact matches, including
letter cases.
46. Why use the HAVING clause in MySQL?
Use HAVING to filter data after grouping it with GROUP BY. It works like WHERE but
applies to groups, not individual rows.
47. How do you see all shortcuts for finding data in a table?
To see all data-finding shortcuts, type SHOW INDEX FROM table_name; It shows
shortcuts’ names, types, and which columns they use.
48. How do you find the difference in time between the two dates?
Use TIMESTAMPDIFF to calculate the time gap. For example, SELECT
TIMESTAMPDIFF(DAY, ‘2020-01-01’, ‘2020-01-31’); tells you the days between dates.
49. What does a VIEW do in MySQL?
A VIEW acts like a pretend table made from other table data. It makes complex data simple,
limits access for safety, and shows parts of data.
50. How do you copy how a table is set up but not its data?
To copy a table’s setup, use CREATE TABLE new_table LIKE original_table;. This copies
how the table is set up without the data.
51. What's the difference between CHAR_LENGTH and LENGTH in MySQL?
CHAR_LENGTH counts how many characters are in a string, seeing all as single characters.
LENGTH counts how many bytes the string is, which changes with different characters.
52. How do you change a table's name?
Change a table’s name with RENAME TABLE old_table_name TO new_table_name;. This
updates the table’s name in your database.
53. Why is AUTO_INCREMENT used with primary keys?
AUTO_INCREMENT gives each new row a unique number, making it perfect for primary
keys. It makes adding records easy without manually setting the key.
