MySQL Interview Questions | Eklavya Online

MySQL Interview Questions

MySQL Commands:
Show databases;
Create database db_name;
Use dbname;
Show tables;
Create table tb_name(id int, name varchar(20));
Desc tb_name;
Insert into tb_name values(101 , ‘name of person’);
Insert into tb_name (id) values(102);
Update tb_name set name=’person name’ where id=102;
Select * from tb_name;
Delete from tb_name where id=102;
Drop table tb_name;
Drop database db_name;
Rename table tb_old_name to tb_new_name;
Alter table customer add (remark varchar(20));
Alter table customer modify remark varchar(25);
Alter table customer modify remark varchar(20);
Alter table customer drop remark;
Alter table customer add remark varchar(20) first;
Alter table customer add remark varchar(20) after customer_name;

How to Import data into MySQL from any file:
Mysql –u root <db.sql (for database and tables)
Mysql –u root <data.sql (for data into tables)

Interview Scenario on MySQL:
There is a table named SAMPLE, and we want to delete all the data from the
table. Which is better option?

  1. delete * from SAMPLE
  2. truncate table SAMPLE

In delete cursor is on the current location, data is deleted from the table but memory is not released by the table, by which searching and sorting operation may take so much time.

While, in truncate cursor is on the starting location, data is deleted permanently and memory is released for other entries, by which searching and sorting operation does not take so much time than delete the data by delete command.

to delete all records 2 is best (only if there is not any other effect related to primary key).

to delete few records 1 is best.

This deals with SQL. Left outer join preserves the unmatched rows from the first (left) table, joining them with a NULL row in the shape of the second (right) table.

This deals with SQL. UNION only selects distinct values, UNION ALL selects all values.

Introduction

Note: “MySQL” it third party (“sun micro system”)
C:\mysql –u root

Types of Table (Engine)

MyISAM: Foreign key constraint does not support
InnoDB: used to support foreign key constraint
BDB: support for UNIX environment
Heap: it is temporary or virtual table, which is created only in memory not in
hard disk
Merge: it is used, if we want to merge more than one table (it is also temporary
or virtual table)

Syntax:

Create table list ( — , — , — )engine=InnoDB;

# mysqladmin -u root -p oldpassword newpassword

mysql> SELECT * FROM tablename WHERE name != “sonia” AND phone_number = ‘9876543210’ order by phone_number;

mysql> alter table [table name] drop column [column name];

mysql> alter table [table name] add column [new column name] varchar (20);

mysql> select lookup.illustrationid, lookup.personid,person.birthday from lookup left join person on lookup.personid=person.personid=statement to join birthday in person table with primary illustration id

# [mysql dir]/bin/mysqldump -c -u username -ppassword databasename tablename > /tmp/databasename.tablename.sql

mysql> SELECT SUM(*) FROM [table name];

# mysql -u root -p

mysql> use mysql;

mysql> grant usage on *.* to sonia@localhost identified by ‘passwd’;

mysql> flush privileges;

mysql> SELECT * FROM tablename WHERE name like “sonia%” AND phone_number = ‘9876543210’;

mysql> alter table [table name] change [old column name] [new column name] varchar (50);

mysql> alter table [table name] add unique ([column name]);

# mysql -u root -p

mysql> use mysql;

mysql>INSERTINTO user (Host,User,Password) VALUES(‘%’,’username’,PASSWORD(‘password’));

mysql> flush privileges;

# [mysql dir]/bin/mysql -u username -ppassword databasename < /tmp/databasename.sql

mysql> drop table tablename;

mysql> SELECT * FROM tablename;

# mysql -u root -p

# mysql -u root -p

mysql> use mysql;

mysql> INSERT INTO user

(Host,Db,User,Select_priv,Insert_priv,Update_priv,Delete_priv,Create_priv,Drop_priv) VALUES (‘%’,’databasename’,’username’,’Y’,’Y’,’Y’,’Y’,’Y’,’N’);

mysql> flush privileges;

or

mysql> grant all privileges on databasename.* to username@localhost;

mysql> flush privileges

mysql> SELECT * FROM tablename WHERE name like “sonia%” AND phone_number = ‘9876543210’ limit 1,5;

mysql> alter table [table name] modify [column name] VARCHAR(3);

mysql> alter table [table name] drop index [colmn name];

# [mysql dir]/bin/mysqladmin -u username -h hostname.blah.org -p password ‘new-password’

mysql> CREATE TABLE [table name] (firstname VARCHAR(20), middleinitial VARCHAR(3), lastname VARCHAR(35),suffix VARCHAR(3),officeid VARCHAR(10),userid VARCHAR(15),username VARCHAR(8),email VARCHAR(35),phone VARCHAR(25), groups VARCHAR(15),datestamp DATE,timestamp time,pgpemail VARCHAR(255));

mysql> use databasename;

mysql> SELECT DISTINCT columnname FROM tablename;

mysql> LOAD DATA INFILE ‘/tmp/filename.csv’ replace INTO TABLE [table name] FIELDS TERMINATED BY ‘,’ LINES TERMINATED BY ‘\n’ (field1,field2,field3);

# mysql -u root -p

mysql> SET PASSWORD FOR ‘user’@’hostname’ = PASSWORD(‘passwordhere’);

mysql> flush privileges;

By below query we will get second maximum(second highest) salary value(integer)from table employee (field salary)in the manner so that mysql gets less load?

SELECT DISTINCT(salary) FROM employee order by salary desc limit 1 , 1 ;

(This way we will able to find out 3rd highest , 4th highest salary so on just need to change limit condtion like LIMIT 2,1 for 3rd highest and LIMIT 3,1 for 4th

some one may finding this way useing below query that taken more time as compare to above query SELECT salary FROM employee where salary < (select max(salary) from employe) order by salary DESC limit 1 ;

mysql> SELECT * FROM tablename WHERE fieldname = “pcds”;

mysql> UPDATE [table name] SET Select_priv = ‘Y’,Insert_priv = ‘Y’,Update_priv = ‘Y’ where [field name] = ‘user’;

mysql> DELETE from [table name] where [field name] = ‘whatever’;

mysql> SELECT col1,col2 FROM tablename ORDER BY col2 DESC;

mysql> SELECT col1,col2 FROM tablename ORDER BY col2 ASC;

# [mysql dir]/bin/mysqldump -u root -ppassword –opt >/tmp/alldatabases.sql

# /etc/init.d/mysql stop

# mysqld_safe –skip-grant-tables &

# mysql -u root

mysql> use mysql;

mysql> update user set password=PASSWORD(“newrootpassword”) where User=’root’;

mysql> flush privileges;

mysql> quit

# /etc/init.d/mysql stop

# /etc/init.d/mysql start

By below method if password is pass and user name is root

# [mysql dir]/bin/mysql -h hostname -u root -p pass

mysql> SELECT * FROM tablename WHERE name = “sonia” AND phone_number = ‘9876543210’

mysql> SELECT COUNT(*) FROM tablename;

# [mysql dir]/bin/mysqldump -u username -ppassword –databases databasename >/tmp/databasename.sql

mysql> drop database databasename;

# mysqladmin -u root password newpassword

Indexing is a process to find an unordered list into an ordered list. It helps in maximizing the query’s efficiency while searching on tables in MySQL. The working of MySQL indexing is similar to the book index.

Suppose we have a book and want to get information about, say, searching. Without indexing, it is required to go through all pages one by one, until the specific topic was not found. On the other hand, an index contains a list of keywords to find the topic mentioned on pages. Then, we can flip to those pages directly without going through all pages.

The default port of MySQL Server is 3306.

While creating a table, we have kept one of the column names incorrectly. To change or rename an existing column name in MySQL, we need to use the ALTER TABLE and CHANGE commands together. The following are the syntax used to rename a column in MySQL:

ALTER TABLE table_name
CHANGE COLUMN old_column_name new_column_name column_definition [FIRST|AFTER existing_column];
Suppose the column’s current name is S_ID, but we want to change this with a more appropriate title as Stud_ID. We will use the below statement to change its name:

ALTER TABLE Student CHANGE COLUMN S_ID Stud_ID varchar(10);

REGEXP is a pattern match using a regular expression. The regular expression is a powerful way of specifying a pattern for a sophisticated search.

Basically, it is a special text string for describing a search pattern. To understand it better, you can think of a situation of daily life when you search for .txt files to list all text files in the file manager. The regex equivalent for .txt will be .*\.txt.

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.mxz

A trigger is a procedural code in a database that automatically invokes whenever certain events on a particular table or view in the database occur. It can be executed when records are inserted into a table, or any columns are being updated. We can create a trigger in MySQL using the syntax as follows:

CREATE TRIGGER trigger_name
[before | after]
{insert | update | delete}
ON table_name [FOR EACH ROW]
BEGIN
–variable declarations
–trigger code
END;

We can update existing records in a table using the UPDATE statement that comes with the SET and WHERE clauses. The SET clause changes the values of the specified column. The WHERE clause is optional, which is used to specify the condition. This statement can also use to change values in one or more columns of a single row or multiple rows at a time. Following is a generic syntax of UPDATE command to modify data into the MySQL table:

UPDATE table_name
SET field1=new-value1, field2=new-value2, …
[WHERE Clause]

There are only six Triggers allowed to use in the MySQL database.

  1. Before Insert
  2. After Insert
  3. Before Update
  4. After Update
  5. Before Delete
  6. After Delete

Mysql_close() cannot be used to close the persistent connection. However, it can be used to close a connection opened by mysql_connect().

We can delete a table in MySQL using the Drop Table statement. This statement removes the complete data of a table, including structure and definition from the database permanently. Therefore, it is required to be careful while deleting a table. After using the statement, we cannot recover the table in MySQL. The statement is as follows:

DROP TABLE table_name;

  1. 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.
  2. MySQL uses only just under 1 MB of RAM on your laptop, while Oracle 9i installation uses 128 MB.
  3. MySQL is great for database enabled websites while Oracle is made for enterprises.
  4. MySQL is portable.

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.

The SELECT command is used to view the content of the table in MySQL.

Explain Access Control Lists.

An ACL is a list of permissions that are associated with an object. MySQL keeps the Access Control Lists cached in memory, and whenever the user tries to authenticate or execute a command, MySQL checks the permission required for the object, and if the permissions are available, then execution completes successfully.

Install antivirus and configure the operating system’s firewall.

Never use the MySQL Server as the UNIX root user.

Change the root username and password Restrict or disable remote access.

There is a major difference between a database and a table. The differences are as follows:

Tables are a way to represent the division of data in a database while the database is a collection of tables and data.
Tables are used to group the data in relation to each other and create a dataset. This dataset will be used in the database. The data stored in the table in any form is a part of the database, but the reverse is not true.
A database is a collection of organized data and features used to access them, whereas the table is a collection of rows and columns used to store the data.

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 a temporary basis. They do not allow BLOB or TEXT fields.

Majorly SQL commands can be divided into three categories, i.e., DDL, DML & DCL. Data Definition Language (DDL) deals with all the database schemas, and it defines how the data should reside in the database. Commands like CreateTABLE and ALTER TABLE are part of DDL.

Data Manipulative Language (DML) deals with operations and manipulations on the data. The commands in DML are Insert, Select, etc.

Data Control Languages (DCL) are related to the Grant and permissions. In short, the authorization to access any part of the database is defined by these.

MySQL is the most popular free and open-source database software which comes under the GNU General Public License. In the beginning, it was owned and sponsored by the Swedish company MySQL AB. Now, it is bought by Sun Microsystems (now Oracle Corporation), who is responsible for managing and developing the database.

Mysqladmin -u root -p password “newpassword”.

InnoDB is a storage database for SQL. The ACID-transactions are also provided in InnoDB and also includes support for the foreign key. Initially owned by InnobaseOY now belongs to Oracle Corporation after it acquired the latter since 2005.

MyISAM table is stored on disk in three formats.

  • ‘.frm’ file : storing the table definition
  • ‘.MYD’ (MYData): data file
  • ‘.MYI’ (MYIndex): index file

We can remove, drop, or delete one or more columns in an existing table using the ALTER TABLE statement as follows:

ALTER TABLE table_name DROP COLUMN column_name1, column_name2….;

MySQL Workbench is a unified visual database designing or GUI tool used for working on MySQL databases. It is developed and maintained by Oracle that provides SQL development, data migration, and comprehensive administration tools for server configuration, user administration, backup, etc. We can use this Server Administration to create new physical data models, E-R diagrams, and SQL development. It is available for all major operating systems. MySQL provides supports for it from MySQL Server version v5.6 and higher.

It is mainly available in three editions, which are given below:

  • Community Edition (Open Source, GPL)
  • Standard Edition (Commercial)
  • Enterprise Edition (Commercial)

MySQL data directory is a place where MySQL stores its data. Each subdirectory under this data dictionary represents a MySQL database. By default, the information managed my MySQL = server mysqld is stored in the data directory.

If we use MySQL in Windows, it is not possible to clear the screen before version 8. At that time, the Windows operating system provides the only way to clear the screen by exiting the MySQL command-line tool and then again open MySQL.

After the release of MySQL version 8, we can use the below command to clear the command line screen:

mysql> SYSTEM CLS;

You can a create maximum of 16 indexed columns for a standard table.

  1. MySQL is not so efficient for large scale databases.
  2. It does not support COMMIT and STORED PROCEDURES functions version less than 5.0.
  3. Transactions are not handled very efficiently.
  4. The functionality of MySQL is highly dependent on other addons.
  5. Development is not community-driven.

It is easy to back 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 back up. Then specify the option you want under export and save the output.

First of all, the MYSQL server is free to use for developers and small enterprises.

MySQL server is open source.

MySQL’s community is tremendous and supportive; hence any help regarding MySQL is resolved as soon as possible.

MySQL has very stable versions available, as MySQL has been in the market for a long time. All bugs arising in the previous builds have been continuously removed, and a very stable version is provided after every update.

The MySQL database server is very fast, reliable, and easy to use. You can easily use and modify the software. MySQL software can be downloaded free of cost from the internet.

We can change the MySQL root password using the below statement in the new notepad file and save it with an appropriate name:

ALTER USER ‘root’@’localhost’ IDENTIFIED BY ‘NewPassword’;
Next, open a Command Prompt and navigate to the MySQL directory. Now, copy the following folder and paste it in our DOS command and press the Enter key.

C:\Users\javatpoint> CD C:\Program Files\MySQL\MySQL Server 8.0\bin
Next, enter this statement to change the password:

mysqld –init-file=C:\\mysql-notepadfile.txt
Finally, we can log into the MySQL server as root using this new password. After launches the MySQL server, it is to delete the C:\myswl-init.txt file to ensure the password change.

Working with the MySQL server, it is a common task to view or list the available databases. We can view all the databases on the MySQL server host using the following command:

mysql> SHOW DATABASES;

ENUMs are string objects. By defining ENUMs, we allow the end-user to give correct input as in case the user provides an input that is not part of the ENUM defined data, then the query won’t execute, and an error message will be displayed which says “The wrong Query”. For instance, suppose we want to take the gender of the user as an input, so we specify ENUM(‘male’, ‘female’, ‘other’), and hence whenever the user tries to input any string any other than these three it results in an error.

ENUMs are used to limit the possible values that go in the table:

For example:

CREATE TABLE months (month ENUM ‘January’, ‘February’, ‘March’); INSERT months VALUES (‘April’).

MySQL primary key is a single or combination of the field used to identify each record in a table uniquely. A primary key column cannot be null or empty. We can remove or delete a primary key from the table using the ALTER TABLE statement. The following syntax is used to drop the primary key:

ALTER TABLE table_name DROP PRIMARY KEY;

BLOB is an acronym that stands for a large binary object. It is used to hold a variable amount of data.

There are four types of the BLOB.

  1. TINYBLOB
  2. BLOB
  3. MEDIUMBLOB
  4. LONGBLOB
    The differences among all these are the maximum length of values they can hold.

TEXT is a 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.

  1. TINYTEXT
  2. TEXT
  3. MEDIUMTEXT
  4. LONGTEXT

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.

The =, , <=, =, >, <>, , AND, OR or LIKE operator are the comparison operators in MySQL. These operators are generally used with SELECT statement.

The default location of MySQL data directory in windows is C:\mysql\data or C:\Program Files\MySQL\MySQL Server 5.0 \data.

We can insert data in a MySQL table using the INSERT STATEMENT. This statement allows us to insert single or multiple rows into a table. The following is the basic syntax to insert a record into a table:

INSERT INTO table_name ( field1, field2,…fieldN )
VALUES ( value1, value2,…valueN );
If we want to insert more than one rows into a table, use the below syntax:

INSERT INTO table(field1, field2,…fieldN)
VALUES
(value1, value 2, …),
(value1, value2, …),

(value1, value2, …);

A USER in MySQL is a record in the USER-TABLE. It contains the login information, account privileges, and the host information for MySQL account to access and manage the databases. We can create a new user account in the database server using the MySQL Create User statement. It provides authentication, SSL/TLS, resource-limit, role, and password management properties for the new accounts.

The following is the basic syntax to create a new user in MySQL:

CREATE USER [IF NOT EXISTS] account_name IDENTIFIED BY ‘password’;

  1. CHAR and VARCHAR have differed in storage and retrieval.
  2. CHAR column length is fixed, while VARCHAR length is variable.
  3. The maximum no. of character CHAR data types can hold is 255 characters, while VARCHAR can hold up to 4000 characters.
  4. CHAR is 50% faster than VARCHAR.
  5. CHAR uses static memory allocation, while VARCHAR uses dynamic memory allocation.

It is a system for file management developed by IBM, which allows records to access sequentially or even randomly.

There are many tables that remain present by default. But, MyISAM is the default database engine used in MySQL. There are five types of tables that are present:

  • MyISAM
  • Heap
  • Merge
  • INNO DB
  • ISAM

MySQL is a multithreaded, multi-user SQL database management system which has more than 11 million installations. It is the world’s second most popular and widely-used open source database. It is interesting how MySQL name was given to this query language. The term My is coined by the name of the daughter of co-founder Michael Widenius’s daughter, and SQL is the short form of Structured Query Language. Using MySQL is free of cost for the developer, but enterprises have to pay a license fee to Oracle.

Formerly MySQL was initially owned by a for-profit firm MySQL AB, then Sun Microsystems bought it, and then Oracle bought Sun Microsystems, so Oracle currently owns MySQL.

MySQL is an Oracle-supported Relational Database Management System (RDBMS) based on structured query language. MySQL supports a wide range of operating systems, most famous of those include Windows, Linux & UNIX. Although it is possible to develop a wide range of applications with MySQL, it is only used for web applications & online publishing. It is a fundamental part of an open-source enterprise known as Lamp.

What is the Lamp?

The Lamp is a platform used for web development. The Lamp uses Linux, Apache, MySQL, and PHP as an operating system, web server, database & object-oriented scripting language. And hence abbreviated as LAMP.

We can delete a row from the MySQL table using the DELETE STATEMENT within the database. The following is the generic syntax of DELETE statement in MySQL to remove one or more rows from a table:

DELETE FROM table_name WHERE Condition_specified;
It is noted that if we have not specified the WHERE clause with the syntax, this statement will remove all the records from the given table.

Sometimes our table name is non-meaningful. In that case, we need to change or rename the table name. MySQL provides the following syntax to rename one or more tables in the current database:

mysql> RENAME old_table TO new_table;
If we want to change more than one table name, use the below syntax:

RENAME TABLE old_tab1 TO new_tab1,
old_tab2 TO new_tab2, old_tab3 TO new_tab3;

Auto Increment is a constraint that automatically generates a unique number while inserting a new record into the table. Generally, it is used for the primary key field in a table. In MySQL, we can set the value for an AUTO_INCREMENT column using the ALTER TABLE statement as follows:

ALTER TABLE table_name AUTO_INCREMENT = value;

Actually, both Unix timestamp and MySQL timestamp are stored as 32-bit integers, but MySQL timestamp is represented in the readable format of YYYY-MM-DD HH:MM:SS format.

A trigger is a set of codes that executes in response to some events.

SELECT * FROM table_name LIMIT 0,20;

MyISAM follows a conservative approach to disk space management and stores each MyISAM table in a separate file, which can be further compressed if required. On the other hand, InnoDB stores the tables in the tablespace. Its further optimization is difficult.

A stored procedure is a group of SQL statements that we save in the database. The SQL queries, including INSERT, UPDATE, DELETE, etc. can be a part of the stored procedure. A procedure allows us to use the same code over and over again by executing a single statement. It stores in the database data dictionary.

We can create a stored procedure using the below syntax:

CREATE PROCEDURE procedure_name [ (parameter datatype [, parameter datatype]) ]
BEGIN
Body_section of SQL statements
END;
This statement can return one or more value through parameters or may not return any result. The following example explains it more clearly:

DELIMITER $$
CREATE PROCEDURE get_student_info()
BEGIN
SELECT * FROM Student_table;
END$$

If we want to manage a database in MySQL, it is required to see the list of all user’s accounts in a database server. The following command is used to check the list of all users available in the database server:

mysql> SELECT USER FROM mysql.user;

Mysql_connect:

  • It opens a new connection to the database.
  • Every time you need to open and close the database connection, depending on the request.
  • Opens page whenever it is loaded.

Mysql_pconnect:

  • In Mysql_pconnect, “p” stands for persistent connection, so it opens the persistent connection.
  • The database connection cannot be closed.
  • It is more useful if your site has more traffic because there is no need to open and close connection frequently and whenever the page is loaded.

To perform batch mode in MySQL, we use the following command:

mysql;
mysql mysql.out;

MySQL is written in C and C++, and its SQL parser is written in yacc.

Installing MySQL on our system allows us to safely create, drop, and test web applications without affecting our live website’s data. There are many ways to use MySQL on our system, but the best way is to install it manually. The manual installation allows us to learn more about the system and provides more control over the database. To see the installation steps of MySQL in Windows goes to the below link:

https://www.javatpoint.com/how-to-install-mysql

Manual installation of MySQL has several benefits:

  • Backing up, reinstalling, or moving databases from one location to another can be achieved in a second.
  • It provides more control to how and when MySQL server starts and closes.
  • We can install MySQL anywhere, like in a portable USB drive.

In MySQL, regular expressions are used in queries for searching a pattern in a string.

  • * Matches 0 more instances of the string preceding it.
  • + matches one more instances of the string preceding it.
  • ? Matches 0 or 1 instances of the string preceding it.
  • . Matches a single character.
  • [abc] matches a or b or z
  • | separates strings
  • ^ anchors the match from the start.
  • “.” Can be used to match any single character. “|” can be used to match either of the two strings
  • REGEXP can be used to match the input characters with the database.

Example:

The following statement retrieves all rows where column employee_name contains the text 1000 (example salary):

Select employee_name
From employee
Where employee_name REGEXP ‘1000
Order by employee_name

SELECT book_title FROM books LIMIT 20, 100;

Sometimes we need to change or rename the database name because of its non-meaningful name. To rename the database name, we need first to create a new database into the MySQL server. Next, MySQL provides the mysqldump shell command to create a dumped copy of the selected database and then import all the data into the newly created database. The following is the syntax of using mysqldump command:

mysqldump -u username -p “password” -R oldDbName > oldDbName.sql
Now, use the below command to import the data into the newly created database:

mysql -u username -p”password” newDbName < oldDbName.sql

We can execute a stored procedure in MySQL by simply CALL query. This query takes the name of the stored procedure and any parameters we need to pass to it. The following is the basic syntax to execute a stored procedure:

CALL stored_procedure_name (argument_list);
Let’s understand it with this example:

CALL Product_Pricing (@pricelow, @pricehigh);
Here, a stored procedure named Product_Pricing calculates and returns the lowest and highest product prices.

Let us take a table named the employee.

To find Nth highest salary is:

select distinct(salary)from employee order by salary desc limit n-1,1

if you want to find 3rd largest salary:

select distinct(salary)from employee order by salary desc limit 2,1

We can connect two or more tables in MySQL using the JOIN clause. MySQL allows various types of JOIN clauses. These clauses connect multiple tables and return only those records that match the same value and property in all tables. The following are the four easy ways to join two or more tables in MySQL:

  • Inner Join
  • Left Join
  • Right Join
  • Cross Join

If you want to display the current date and time, use –

SELECT NOW();

If you want to display the current date only, use:

SELECT CURRENT_DATE();

Heap tables:

Heap tables are found in memory that is used for high-speed storage temporarily. 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 transient data. Sometimes it is beneficial in cases to hold temporary data. The temporary table is deleted after the 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).

MySQL allows us to import the CSV (comma separated values) file into a database or table. A CSV is a plain text file that contains the list of data and can be saved in a tabular format. MySQL provides the LOAD DATA INFILE statement to import a CSV file. This statement is used to read a text file and import it into a database table very quickly. The full syntax to import a CSV file is given below:

LOAD DATA INFILE ‘C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/filename.csv’
INTO TABLE tablename
FIELDS TERMINATED BY ‘,’
OPTIONALLY ENCLOSED BY ‘”‘
LINES TERMINATED BY ‘\r\n’
IGNORE 1 ROWS;

Mysql_fetch_object is used to retrieve the result from the database as objects, while mysql_fetch_array returns result as an array. This will allow access to the data by the field names.

For example:

Using mysql_fetch_object field can be accessed as $result->name.

Using mysql_fetch_array field can be accessed as $result->[name].

Using mysql_fetch_row($result) where $result is the result resource returned from a successful query executed using the mysql_query() function.

Example:

$result = mysql_query(“SELECT * from students”);
while($row = mysql_fetch_row($result))
{
Some statement;
}

Federated tables are tables that point to the tables located on other databases on some other server.

The “i_am_a_dummy flag” enables the MySQL engine to refuse any UPDATE or DELETE statement to execute if the WHERE clause is not present. Hence it can save the programmer from deleting the entire table my mistake if he does not use WHERE clause.

MySQL uses the LIMIT keyword, which can be used to limit the result set. It will allow us to get the first few rows, last few rows, or range of rows. It can also be used to find the second, third, or nth highest salary. It ensures that you have use order by clause to sort the result set first and then print the output that provides accurate results. The following query is used to get the second highest salary in MySQL:

SELECT salary
FROM (SELECT salary FROM employees ORDER BY salary DESC LIMIT 2) AS Emp ORDER BY salary LIMIT 1;
There are some other ways to find the second highest salary in MySQL, which are given below:

This statement uses subquery and IN clause to get the second highest salary:

SELECT MAX(salary)
FROM employees
WHERE salary NOT IN ( SELECT Max(salary) FROM employees);
This query uses subquery and < operator to return the second highest salary:

SELECT MAX(salary) From employees
WHERE salary < ( SELECT Max(salary) FROM employees);

MySQL has the following technical specifications –

  • Flexible structure
  • High performance
  • Manageable and easy to use
  • Replication and high availability
  • Security and storage management
  • Drivers
  • Graphical Tools
  • MySQL Enterprise Monitor
  • MySQL Enterprise Security
  • JSON Support
  • Replication & High-Availability
  • Manageability and Ease of Use
  • OLTP and Transactions
  • Geo-Spatial Support

In MySQL, the “i-am-a-dummy” flag makes the MySQL engine to deny the UPDATE and DELETE commands unless the WHERE clause is present.

To get current date, use the following syntax:

SELECT CURRENT_DATE();

To identify each row of a table, we will use a primary key. For a table, there exists only one primary key.

A candidate key is a column or a set of columns, which can be used to uniquely identify any record in the database without having to reference any other data.

Sometimes we need to fetch data from three or more tables. There are two types available to do these types of joins. Suppose we have three tables named Student, Marks, and Details.

Let’s say Student has (stud_id, name) columns, Marks has (school_id, stud_id, scores) columns, and Details has (school_id, address, email) columns.

1. Using SQL Join Clause

This approach is similar to the way we join two tables. The following query returns result from three tables:

SELECT name, scores, address, email FROM Student s
INNER JOIN Marks m on s.stud_id = m.stud_id
INNER JOIN Details d on d.school_id = m.school_id;
2. Using Parent-Child Relationship

It is another approach to join more than two tables. In the above tables, we have to create a parent-child relationship. First, create column X as a primary key in one table and as a foreign key in another table. Therefore, stud_id is the primary key in the Student table and will be a foreign key in the Marks table. Next, school_id is the primary key in the Marks table and will be a foreign key in the Details table. The following query returns result from three tables:

SELECT name, scores, address, email
FROM Student s, Marks m, Details d
WHERE s.stud_id = m.stud_id AND m.school_id = d.school_id;

A column is a series of cells in a table that stores one value for each row in a table. We can add columns in an existing table using the ALTER TABLE statement as follows:

ALTER TABLE table_name
ADD COLUMN column_name column_definition [FIRST|AFTER existing_column];

  • TRUNCATE is a DDL command, and DELETE is a DML command.
  • It is not possible to use Where command with TRUNCATE QLbut you can use it with DELETE command.
  • TRUNCATE cannot be used with indexed views, whereas DELETE can be used with indexed views.
  • The DELETE command is used to delete data from a table. It only deletes the rows of data from the table while truncate is a very dangerous command and should be used carefully because it deletes every row permanently from a table.

MySQL default port number is 3306.

Importing database in MySQL is a process of moving data from one place to another place. It is a very useful method for backing up essential data or transferring our data between different locations. For example, we have a contact book database, which is essential to keep it in a secure place. So we need to export it in a safe place, and whenever it lost from the original location, we can restore it using import options.

In MySQL, we can import a database in mainly two ways:

  • Command Line Tool
  • MySQL Workbench

Mysql_connect() is used to open a new connection to the database, while mysql_pconnect() is used to open a persistent connection to the database. It specifies that each time the page is loaded, mysql_pconnect() does not open the database.

A view is a database object whose values are based on the base table. It is a virtual table created by a query by joining one or more tables. It is operated similarly to the base table but does not contain any data of its own. If any changes occur in the underlying table, the same changes reflected in the View also.

Following is the general syntax of creating a VIEW in MySQL:

CREATE [OR REPLACE] VIEW view_name AS
SELECT columns
FROM tables
[WHERE conditions];

FLOAT stores floating-point numbers with accuracy up to 8 places and allocate 4 bytes. On the other hand, DOUBLE stores floating-point numbers with accuracy up to 18 places and allocates 8 bytes.

A defined point in any transaction is known as savepoint.

SAVEPOINT is a statement in MySQL, which is used to set a named transaction savepoint with the name of the identifier.

MySQL allows us to use the INSERT STATEMENT to add the date in MySQL table. MySQL provides several data types for storing dates such as DATE, TIMESTAMP, DATETIME, and YEAR. The default format of the date in MySQL is YYYY-MM-DD. Following is the basic syntax to insert date in MySQL table:

INSERT INTO table_name (column_name, column_date) VALUES (‘DATE: Manual Date’, ‘2008-7-04’);
If we want to insert a date in the mm/dd/yyyy format, it is required to use the below statement:

INSERT INTO table_name VALUES (STR_TO_DATE(date_value, format_specifier));

SELECT team_name FROM team WHERE team_won IN (1, 3, 5, 7);

SQL is known as the standard query language. It is used to interact with the database like MySQL. MySQL is a database that stores various types of data and keeps it safe.

A PHP script is required to store and retrieve the values inside the database.

SQL is a computer language, whereas MySQL is a software or an application

SQL is used for the creation of database management systems whereas MySQL is used to enable data handling, storing, deleting and modifying data