Intermediate Level

Explain the difference between primary key and candidate key in Mysql?

Primary Key - It is a column that uniquely identifies a record. In Mysql, only one candidate key can
behave like Primary Key.
Candidate Key - It can be any column that can qualify as a unique key in the database. In MySQL, there can be multiple candidate keys in one table. Each candidate key can behave like as a primary key.

What is TRIGGERS and how it can be used in MySQL?

In Mysql, a trigger is a database object that is directly associated with a
table. It will be activated when a defined action is executed for the table.
It can be performed when you run one of the following MySQL like
INSERT, UPDATE and DELETE occurred in a table. It's activation time can
be BEFORE or AFTER

Example
mysql> delimiter //
mysql> CREATE TRIGGER age_check BEFORE INSERT ON people FOR
EACH ROW IF NEW.age < 0 THEN SET NEW.age = 0; END IF; mysql> delimiter ;

What are the difference between NOW and CURRENT_DATE in
MySQL?

NOW - NOW() give you the current date time in the format 'YYYY-MM_DD HH:MM: SS'

CURRENT_DATE - CURRENT_DATE() will only give you the current date in format "YYYY-MM_DD"

List some comparisons operators used in Mysql?

Comparisons operators are used to comparing one expression to another
value or expression. It is just like = , < , > , => , =<, <>

What is the use of CONCAT() in Mysql?

It is used to concatenate two or more strings.

Example
SELECT CONCAT('BestInterview', ' ', 'Question') AS 'Name';

What is the difference between IS NULL & IS NOT NULL?

IS NULL checks to see if the cell is empty but IS NOT NULL Checks to see if the cell is not empty.

Example :
SELECT id FROM users WHERE 'user_type' IS NOT NULL;
SELECT id FROM users WHERE 'user_type' NOT IS NULL;

What are the drivers in MySQL?

In MySQL, standards-based drivers for JDBC, ODBC, and .Net are
provided in order to enable developers in building database applications
with their language of choice.
Available Drivers in MySQL:
• PHP Driver
• JDBC Driver
• ODBC Driver
• C WRAPPER
• PYTHON Driver
• PERL Driver
• RUBY Driver
• CAP11PHP Driver
• Ado.net5.mxj
JDBC, ODBC, and .Net drivers are necessary for MySQL as they enable the
developers for building their database applications.

Why use triggers in MySQL?

In MySQL, a trigger corresponds to a set of actions running automatically
when a particular change operation like SQL INSERT, UPDATE, or
DELETE query is performed on a table.
Example:

CREATE TRIGGER data_backup BEFORE UPDATE ON users
FOR EACH ROW
BEGIN
IF NEW.amount < 0 THEN SET NEW.amount = 0; ELSEIF NEW.amount > 100 THEN
SET NEW.amount = 100;
END IF;
END;//

How would you select all the users whose phone is null MySQL?

SELECT id, name FROM users WHERE phone IS NULL;


Explore the Data Science Course in Pune
Watch the video on SQL Interview Question Set - 2