EVERY POSSIBLE MYSQL INTERVIEW QUESTION FOR DATA SCIENCE, DATA ANALYST, DATA ENGINEER, AND DATABASE MANAGER.

MySQL

If you want to be a successful data scientist or data analyst or data engineer or database manager MySQL is must to know and most of the time we ignore it and fail to crack the interview due to lack of knowledge or we couldn't find a place where we can find solutions of almost all the MySQL questions. Here are some of the best MySQL / SSMS interview questions which are guaranteed to be asked in an interview.

So without wasting any time let's get started.

See All DataBases:

show databases;

See All Tables:

show tables;

Use Database:

use company; /* company is database name */

See Table Content:

select * from Employee; /* Employee table name */

Create Table:

 
CREATE TABLE Employees(e_id int, 
                       e_name varchar(20), 
                       join_date date, 
                       e_gender varchar(5), 
                       e_age int(10),
                       e_dept varchar(20), 
                       e_sal int(20));


CREATE TABLE Departement(d_id int, 
                         d_name varchar(20), 
                         d_loc varchar(20)); 
                         

Insert Into Table Values:

 
INSERT INTO Employees VALUES
(101, 'Naruto', '20-12-2020', 'M', 22, 'IT', 100000),
(102, 'Priya', '22-02-2021', 'F', 23, 'MARKET', 50000),
(103, 'Jay', '25-10-2021', 'M', 21, 'FINANCE', 30000);


INSERT INTO Departement VALUES
(1, 'IT', 'MUMBAI'),
(2, 'ACCOUNTS', 'DELHI'),
(3, 'MARKET', 'PUNE'); 

Create Copy Of Table:

SELECT *
INTO Copy_Employees
FROM Employees;

Where Clause:

/* Search Record */

SELECT * FROM Employees
WHERE e_name = 'Priya';

/* ------------------------------ */

/* Delete Record */

DELETE FROM Employees
WHERE e_name = 'Priya';

AND | OR | NOT Operators:

/* AND */

SELECT * FROM Employees
WHERE e_name = 'Priya' AND e_age = 23;

/* --------------------------------------- */

/* OR */

SELECT * FROM Employees
WHERE e_age = 22 OR e_dept = 'SALES';

/* --------------------------------------- */

SELECT * FROM Employees
WHERE e_age = 22 OR e_age = 23;

/* --------------------------------------- */

/* NOT */

SELECT * FROM Employees
WHERE NOT e_dept = 'IT';

Distinct Operator:

/* (GIVES UNIQUE VALUES) */

SELECT DISTINCT e_gender FROM Employees; 

Between Operator:

SELECT * FROM Employees
WHERE e_age
BETWEEN 20 AND 30;

Basic Functions For Data Interpretation:

• MIN()
• MAX()
• COUNT()
• SUM()
• AVG()

/* MIN */

SELECT MIN(e_age) FROM Employees;

/* MAX */

SELECT MAX(e_age) FROM Employees;

/* COUNT */

SELECT COUNT(*) FROM Employees WHERE e_gender='M';

/* SUM */

SELECT SUM(e_sal) FROM Employees;

/* AVG */

SELECT AVG(e_sal) FROM Employees;

Like Operator:

/* Stats With } 'value%' */

SELECT * FROM Employees WHERE e_name LIKE 'M%';     /* Name Starts With M */

/* Ends With } '%value' */

SELECT * FROM Employees WHERE e_name LIKE '%m';     /* Name Ends With m */

/* In a specfic value rage } 'value_' */

SELECT * FROM Employees WHERE e_age LIKE '2_';      /* Age In 20's */

String Functions:

• LTRIM()
• UPPER()
• LOWER()
• REVERSE()
• SUBSTRING()

/* LTRIM */

SELECT LTRIM('    Naruto');

/* LOWER */

SELECT LOWER('NARUTO');

/* UPPER */

SELECT UPPER('naruto');

/* REVERSE */

SELECT REVERSE('naruto');

/* SUBSTRING */

SELECT SUBSTRING('This is sparta',9,6);

Group By:

/* Get AVG sal with respect to gender */

SELECT AVG(e_sal) FROM Employee
GROUP BY e_gender;

/* Get AVG age with respect to gender */

SELECT AVG(e_age) FROM Employee
GROUP BY e_gender;

Order By & Top:

/* Order By } Sort The Data by ASC or DESC */

SELECT * FROM Employees ORDER BY e_age;

/* ---------------------------------------- */

SELECT * FROM Employees ORDER BY e_age DESC;

/* ---------------------------------------- */

/* Top x */

SELECT TOP 3 * FROM Employees ORDER BY e_age DESC;

Having Clause:

/* Departement Name Whose AVG(e_sal) is Greater than 80000 */

SELECT e_dept, AVG(e_sal) AS dep_avg_sal
FROM Employees
GROUP BY e_dept
HAVING AVG(e_sal) > 80000
ORDER BY dep_avg_sal DESC;

/* ------------------------------------------------------------ */

/* The following SQL statement lists the number of Emp in each Departement. 
Only include Departement with more than 5 Emp: */

SELECT COUNT(e_id), e_dept
FROM Employees
GROUP BY e_dept
HAVING COUNT(e_id)>5;

Alter Table:

/* Add Column To a Table */

ALTER TABLE Employees
ADD e_height int;

/* ---------------------------------------- */

/* Delete Column To a Table */

ALTER TABLE Employees
DROP COLUMN e_height;

Update Statement:

UPDATE Employees
SET e_age = 24
WHERE e_name = 'Priya';

Delete Statement:

/* Delete specific record */

DELETE Employees
WHERE e_name = 'Priya';

Truncate Statement:

/* Delete all the value from table but structure remain same */

TRUNCATE TABLE Employees;

Drop Statement:

/* Delete table including structure */

DROP TABLE Employees;

Union | Union All | Except | Intersect Operators:

/* Union (A + B) Drop Duplicates */

SELECT * FROM Employees
UNION
SELECT * FROM Customers

/* ----------------------------------------- */

/* Union All (A + B) Include Duplicates */

SELECT * FROM Employees
UNION ALL
SELECT * FROM Customers

/* ----------------------------------------- */

/* Except (A - B) Drop Duplicates */

SELECT * FROM Employees
EXCEPT
SELECT * FROM Customers

/* ----------------------------------------- */

/* Intersect (A n B) Common to both */

SELECT * FROM Employees
INTERSECT
SELECT * FROM Customers

Joins:

• INNER
• LEFT
• RIGHT
• FULL

/* INNER Join } Return records which has matching values */

SELECT 
Employees.e_name, Employees.e_dept, 
Departement.d_name, Departement.d_loc
FROM Employees
INNER JOIN Departement
ON Employees.e_dept = Departement.d_name;

/* ----------------------------------------- */

/* LEFT Join } Return all records from left tables & 
   matching records from right */

SELECT 
Employees.e_name, Employees.e_dept, 
Departement.d_name, Departement.d_loc
FROM Employees
LEFT JOIN Departement
ON Employees.e_dept = Departement.d_name;

/* ----------------------------------------- */

/* RIGHT Join } Return all records from right tables & 
   matching records from left */

SELECT 
Employees.e_name, Employees.e_dept, 
Departement.d_name, Departement.d_loc
FROM Employees
RIGHT JOIN Departement
ON Employees.e_dept = Departement.d_name;

/* ----------------------------------------- */

/* FULL Join } Return all records from right tables & left table */

SELECT 
Employees.e_name, Employees.e_dept, 
Departement.d_name, Departement.d_loc
FROM Employees
FULL JOIN Departement
ON Employees.e_dept = Departement.d_name;

Update & Delete Using Joins:

/* Update e_sal value in Employee table where ever
 the departement location is Mumbai */

UPDATE Employees
SET e_age = e_age+10
FROM Employees 
JOIN Departement ON Employees.e_dept = Departement.d_name
WHERE d_loc = 'Mumbai';

/* ---------------------------------------------------------- */

/* Delete value in Employee table where ever 
   the departement location is Mumbai */

DELETE Employees
FROM Employees 
JOIN Departement ON Employees.e_dept = Departement.d_name
WHERE d_loc = 'Mumbai';

Views:

/* Create view of only female employees */

CREATE VIEW Female_emps AS
SELECT * FROM Employees
WHERE e_gender = 'F';

/* ---------------------------------------------------------- */

/* See view */

SELECT * FROM Female_emps;

/* ---------------------------------------------------------- */

/* Drop view */

DROP VIEW Female_emps;

Merge:

Perform INSERT, UPDATE, and DELETE all at once.

• When rows match we want to perform an update.

• When rows don't match but the data is present in the SOURCE table but not in the TARGET table we need to INSERT all such rows into the TARGET table.

• When records are present only in the TARGET table but not in the SOURCE table, we need to DELETE all such rows from the TARGET table.

MERGE [TARGET] AS T

USING [SOURCE] AS S

ON [JOIN_CONDITION]

WHEN MATCHED THEN

UPDATE STATEMENT
WHEN NOT MATCHED BY TARGET THEN

INSERT STATEMENT
WHEN NOT MATCHED BY SOURCE THEN

DELETE STATEMENT;

/* CODE: */

MERGE Students AS S
USING students_new AS SN
ON S.s_id = SN.s_id
WHEN MATCHED THEN
    UPDATE SET S.s_name = SN.s_name
WHEN NOT MATCHED BY TARGET THEN
    INSERT(s_id, s_name) VALUES(SN.s_id, SN.s_name)
WHEN NOT MATCHED BY SOURCE THEN
    DELETE;

Function Types In SQL:

(1) Scalar Valued Function

(2) Table Valued Function

/* 1.) Scalar Valued Function 
  (Returns Scalar Values such as int, varchar...etc) */

CREATE FUNCTION add_five(@num as int)

RETURNS int
AS
BEGIN
RETURN
(@num + 5)
END

SELECT dbo.add_five(100);

/* ----------------------------------------------------------------- */

/* 2.) Table Valued Funtion (Returns Tabular Values) */

CREATE FUNCTION select_gender(@gender AS VARCHAR(20))
RETURNS TABLE
AS 
RETURN
(SELECT * FROM Employees WHERE e_gender = @gender)


SELECT * FROM DBO.select_gender('M');

Temporary Table:

The table which gets deleted as soon as terminated.

CREATE TABLE /* NEW_STUDENTS */
(ns_id int, ns_name varchar(20));

INSERT INTO /* NEW_STUDENTS */
VALUES 
(101, 'Ram'),
(102, 'Jay');

SELECT * FROM NEW_STUDENTS;

Case Statement:

/* e_sal_cmt will be a new column which will 
  tell if employee is rich or poor */

SELECT *, e_sal_cmt =                     
CASE
    WHEN e_sal < 90000 THEN 'POOR'
    WHEN e_sal > 90000 THEN 'RICH'
    ELSE 'MIDDLE CLASS'
END
FROM Employees
GO

IIF() Function:

SELECT e_id, e_name, e_age,
IIF (e_age>35, 'Old Employee', 'New Employee')
AS e_generation
FROM Employees;	
  

Stored Procedure:

Functions Which Call Be Called Anytime We Want

/* Normal Stored Procedure */

CREATE PROCEDURE access_age
AS
SELECT e_id, e_name, e_age
FROM Employees
WHERE e_age>35
GO

EXEC access_age;

/* --------------------------------------------- */

/* Stored Procedure With Parameters (Get Male & Female Employee Seperately) */

CREATE PROCEDURE emp_gender @gender varchar(20)
AS
SELECT *
FROM Employees
WHERE e_gen = @gender
GO

EXEC emp_gender @gender = 'M';

Try Catch:

DECLARE @val1 int;
DECLARE @val2 int;

BEGIN TRY
    SET @val1 = 5;
    SET @val2 = @val1/0;
END TRY

BEGIN CATCH
PRINT ERROR_MESSAGE()
END CATCH;

/* --------------------------------------------- */

BEGIN TRY
    SELECT e_name + e_sal FROM Employees
END TRY

BEGIN CATCH
    PRINT 'CANNOT ADD NUM VALUE WTIH STRING'
END CATCH
GO

Commit & RollBack Using Transcation:

/* Roll Back */

BEGIN TRANSACTION
    UPDATE Employees
    SET e_age = 30
    WHERE e_name = 'Sam';

SELECT * FROM Employees;

ROLLBACK TRANSACTION;

SELECT * FROM Employees;

/* --------------------------------------------- */

/* Commit */

BEGIN TRANSACTION
    UPDATE Employees
    SET e_age = 30
    WHERE e_name = 'Sam';

SELECT * FROM Employees;

COMMIT TRANSACTION;

SELECT * FROM Employees;

Transaction (If Error In Code End Whole Transaction & RollBack):

SELECT *
INTO Copy_Employees
FROM Employees;

/* ------------------------------------------------------------ */

/* This Code Will RollBack Since 120/0 gives ZeroDivisionError */

SELECT * FROM Copy_Employees;



BEGIN TRY
    BEGIN TRANSACTION
        UPDATE Copy_Employees SET e_sal = 500 WHERE e_gender = 'M'
        UPDATE Copy_Employees SET e_sal = 120/0 WHERE e_gender = 'F'
    COMMIT TRANSACTION
    PRINT 'Transaction Commited'
END TRY

BEGIN CATCH
    ROLLBACK TRANSACTION
    PRINT 'Transaction RolledBack'
END CATCH



SELECT * FROM Copy_Employees;

/* --------------------------------------------------------------------- */

/* This Code Will Get Commited */

SELECT * FROM Copy_Employees;



BEGIN TRY
    BEGIN TRANSACTION
        UPDATE Copy_Employees SET e_sal = 500 WHERE e_gender = 'M'
        UPDATE Copy_Employees SET e_sal = 120 WHERE e_gender = 'F'
    COMMIT TRANSACTION
    PRINT 'Transaction Commited'
END TRY

BEGIN CATCH
    ROLLBACK TRANSACTION
    PRINT 'Transaction RolledBack'
END CATCH



SELECT * FROM Copy_Employees;

SQL With Python:

Connect MySQL to Python

import mysql.connector

mydb = mysql.connector.connect(host='localhost',user='root',password='mousepad',port=3306)

print(mydb.connection_id)

Create Data Base In MySQL with Python

import mysql.connector

mydb = mysql.connector.connect(host='localhost', user='root', password='mousepad',port=3306)

cur = mydb.cursor()

cur.execute("CREATE DATABASE my_database1")

Create Table In Database using Python

import mysql.connector

mydb = mysql.connector.connect(host='localhost', user='root', password='mousepad', port=3306, database='my_database1')

cur = mydb.cursor()

str1 = "CREATE TABLE SCHOOL(Stu_id int(10), Stu_fname varchar(10), Stu_lname varchar(10))"

cur.execute(str1)

Insert Into School Value Using Python

import mysql.connector

mydb = mysql.connector.connect(host='localhost', user='root', password='mousepad', port=3306, database='my_database1')

cur = mydb.cursor()

str2 = "INSERT INTO SCHOOL VALUES(%s,%s,%s)"

vals = [(101,'Naruto','Uzumaki'),(102,'Rock','Lee'),(103,'Rin','Nohara')]

cur.execute(str2,vals)

mydb.commit()

Extract Data From Table Using Python

import mysql.connector

mydb = mysql.connector.connect(host='localhost', user='root', password='mousepad', port=3306, database='my_database1')

cur = mydb.cursor()

str1 = "select * from school"

cur.execute(str1)
result = cur.fetchall()

for i in result:
    print(i)

Update Table With Python

import mysql.connector

mydb = mysql.connector.connect(host='localhost', user='root', password='mousepad', port=3306, database='my_database1')

cur = mydb.cursor()

s = "UPDATE SCHOOL SET Stu_id=Stu_id+1 WHERE Stu_id>102"

cur.execute(s)
mydb.commit()

Delete Record With Python

import mysql.connector

mydb = mysql.connector.connect(host='localhost', user='root', password='mousepad', port=3306, database='my_database1')

cur = mydb.cursor()

s = "DELETE FROM school WHERE Stu_id>102"

cur.execute(s)
mydb.commit()

Interview Questions:

Q.) Change the Data Type Of a column

ALTER TABLE Employees
ALTER COLUMN Age float;

Q.) Get the Average Age Of Females

SELECT AVG(e_age) AS avg_female_Age 
FROM Employees
WHERE e_gender = 'F';

Q.) Second Highest Salary In Employee Table

SELECT MAX(e_sal) FROM Employees
WHERE e_sal < (SELECT MAX(e_sal) FROM Employees);

Q.) Top N=2 Salary From Employees

SELECT TOP 2 * FROM 
(SELECT DISTINCT TOP 2 * 
 FROM Employees 
 ORDER BY e_sal DESC) AS RESULT
ORDER BY e_sal DESC;

Q.) Get Nth Highest Salary

# 3rd highest salary

WITH RESULT AS
(
    SELECT e_sal, DENSE_RANK() OVER (ORDER BY e_sal DESC) as D
    FROM Employees
)
SELECT TOP 1 e_sal
FROM RESULT
WHERE RESULT.D = 3;

Q.) Department wise highest salary

SELECT MAX(e_sal) AS Max_Dep_sal, e_dept FROM Employees
GROUP BY e_dept
ORDER BY Max_Dep_sal DESC;

Q.) Number of employees in each department

SELECT COUNT(*) AS EMP_CNT, e_dept FROM Employees
GROUP BY e_dept;

Q.) Displayed Duplicated Records

SELECT COUNT(*) AS Number_of_times_Duplicated, e_id FROM Copy_Employees
GROUP BY e_id
HAVING COUNT(*)>1;

Q.) Delete Duplicate Records in SQL

WITH EmployeesCTE AS
(
    SELECT *, ROW_NUMBER() OVER (PARTITION BY e_id ORDER BY e_id) as Row_Num
    FROM Copy_Employees
)
DELETE FROM EmployeesCTE WHERE Row_Num>1;

Q.) Top 3 Employees With Highest Salary

Select TOP 3 * from Employees
ORDER BY e_sal DESC;

Q.) Concat First Name & Last Name Of Employee

SELECT CONCAT(f_name,'_',l_name) as Full_Name FROM Employees; 

Q.) Departement Wise Count Of Employee

SELECT COUNT(*) AS cnt_of_emp_as_per_dep, e_dept FROM Employees
GROUP BY e_dept
ORDER BY COUNT(*) ASC;

Q.) Show All Even Records

SELECT * FROM Employees
WHERE e_id%2=0;

Q.) Last 2 Rows

SELECT TOP(2) e_id, e_name, e_sal    
FROM Employees    
Order By e_id DESC;

Q.) Show Employee's location and date of joining which is in the EmpPos table

SELECT
Employees.e_id, Employees.e_name, Employees.sal,
EmpPos.e_loc, EmpPos.e_join
FROM Employees
INNER JOIN EmpPos
ON Employees.e_id = EmpPos.e_id;

Q.) Recently join employees in a given range (MONTH)

SELECT *, DATEDIFF (MONTH, e_join, GETDATE()) as Diff
FROM EmpPos
WHERE DATEDIFF(MONTH, e_join, GETDATE()) BETWEEN 1 AND 25
ORDER BY e_join DESC;

Q.) Recently join employees in a given range (DAY)

SELECT *, DATEDIFF (DAY, e_join, GETDATE()) as Diff
FROM EmpPos
WHERE DATEDIFF(DAY, e_join, GETDATE()) BETWEEN 200 AND 600
ORDER BY e_join DESC;

Q.) Recently join employees in a given range (YEAR)

SELECT *, DATEDIFF (YEAR, e_join, GETDATE()) as Diff
FROM EmpPos
WHERE DATEDIFF(YEAR, e_join, GETDATE()) BETWEEN 1 AND 5
ORDER BY e_join DESC;

Q.) Dept with the highest number of employees

SELECT TOP 1 COUNT(*) AS cnt_of_emp_as_per_dep, e_dept FROM Employees
GROUP BY e_dept
ORDER BY COUNT(*) DESC;

Q.) Join 3 tables in SQL server

SELECT ENAME, DNAME, Gender
FROM EmployeeD
JOIN Departements ON EmployeeD.DID = Departements.DID
JOIN Genders ON Genders.GID = EmployeeD.GID;

Q.) If we have 2 different datatypes we can do that too.

select T1.T1Column1,T2.T2Column1
from T1
JOIN T2 on T1.ID = T2.ID;


Alter Table T2 Alter column Id nvarchar(3); /*Change datatype*/

Q.) Employee who join in December

select * from EmpPos where DATEPART(Month,e_join)='12';

Q.) Average Cost Of Individual Departement

SELECT e_dept, AVG(e_sal) AS DEPT_COST_AVERAGE FROM Employees
WHERE e_dept = 'ALY'
GROUP BY e_dept;

Q.) Give e_name and how many happen he/she has month has joined the organization

SELECT 
Employees.e_name,
EmpPos.e_join, DATEDIFF(MM,CONVERT(DATETIME, e_join),GETDATE()) AS Months_Of_Joining
FROM Employees
INNER JOIN EmpPos 
ON Employees.e_id = EmpPos.e_id;

Q.) Display all the e_name and their specific month of joining

SELECT Employees.e_name,
EmpPos.e_join
FROM Employees
INNER JOIN EmpPos
ON Employees.e_id = EmpPos.e_id
WHERE DATEPART(Month,e_join)='12';

MySQL Prac

Create table Employee

create table Employee(
    empid int Not null primary key auto_increment,
    first_name varchar(25), 
    last_name varchar(25), 
    salary int, 
    joining_date datetime,
    department_name varchar(25)
);

insert into Employee values
(0001,'Krish','Naik',50000,'14-12-11 09:00:00','Development'),
(0002,'Sudhanshu','Kumar',60000,'14-12-11 09:00:00','Development'),
(0003,'Sanket','Kumar',70000,'14-12-12 09:00:00','HR'),
(0004,'Darius','Bengali',70000,'14-12-13 09:00:00','HR'),
(0005,'Satish','Bhansal',30000,'15-10-21 09:00:00','Accountant'),
(0006,'Shaktiman','Hero',50000,'15-10-15 09:00:00','Accountant');

Check Schema

desc Employee;

Creating Bonus Table

create table Bonus(
    emp_ref_id int,
    bonus_amt int,
    bonus_date datetime,
    foreign key(emp_ref_id) references Employee(empid)  # foreign key example
    on Delete cascade                                   # change in employee table then changes will also happen in Bonus table
);

insert into bonus values
(0001,5000,'16-03-14'),
(0002,5000,'16-03-13'),
(0003,5000,'17-03-15'),
(0001,5000,'18-03-17'),
(0004,3500,'16-03-19'),
(0005,7000,'16-03-20'),
(0001,8000,'20-03-21'),
(0002,8500,'20-03-21');

Creating Emp Designation

create table Designation(
    emp_ref_id int,
    designation varchar(25),
    designation_date datetime,
    foreign key(emp_ref_id) references Employee(empid)
    on delete cascade
);

INSERT INTO designation VALUES
 (0001,'Manager','2016-02-5 00:00:00'),
 (0002,'Executive','2016-06-11 00:00:00'),
 (0003,'Executive','2016-06-11 00:00:00');

1. Write an SQL query to receive all details where the first name from the employee table which starts with 'k'

select * from Employee where first_name like 'k%';

2. Write an Sq1 query to print all details of the employees whose salaries between 10000 to 35000 fetch the Employee name

select * from Employee where salary between 10000 and 35000;

/* using subquery */

select concat(first_name,' ',last_name) as Fullname, salary from Employee
where empid in
(select empid from Employee where salary between 10000 and 55000);

3. SQL query to retrieve details of the employees who have joined on a date

select * from Employee where year(joining_date)=2014 and month(joining_date)=12 and day(joining_date)=13;

4. SQL query to fetch the number of employees in every department

select department_name, count(empid) as cnt 
from Employee Group by department_name;

5. SQL query to print details of the employee who are also Executives

/* Executives is in Designation */

select employee.empid, employee.first_name,
designation.emp_ref_id, designation.designation
from Employee
right Join designation
on Employee.empid = designation.emp_ref_id
where designation.designation = 'Executive';

6. SQL query to clone a new table from another table

/* Copy only schema / structure */

create table clone_emp like Employee;

/* Copy whole table */

create table clone_table as select * from Employee;

7. Top n salary of the employee

select * from Employee
order by salary
desc limit 2,1;

8. Nth highest salary

select first_name, salary from employee
order by salary 
desc limit 3,1;                               # N --> 4 } 4th highest salary

/* Without Limit */

SELECT * FROM Employee Emp1
WHERE (3-1) = 
(SELECT COUNT(DISTINCT(Emp2.Salary))
 FROM Employee Emp2
 WHERE Emp2.Salary > Emp1.Salary);

9. Arrange

SELECT * FROM tablename WHERE age>25 GROUP BY id HAVING something > 5 ORDER BY date_from LIMIT 10;

10. First 5 char of employee name

select LEFT(first_name, 5) as First_five from Employee;

11. Mask a column from credit_card_details such last few chars are converted to '*'

SELECT CONCAT(LEFT(credit_card_no,4),'*****') as masked_cred_card_details from CREDIT_CARD_DETAILS;

12. Get Credit_card_no from 3rd digit to 7th

select substr(credit_card_no,3,7) from credit_card_details;

13. Get all the employee name who has salary greater than Krish's salary

select * from employee where salary > (select salary from Employee where first_name='Krish');

14. Get all the employee name who has salary greater than Krish's salary and create a view

create view new_view as
select * from employee where salary > (select salary from Employee where first_name='Krish');

select * from new_view;

/* Let say you have wifi_id , wifi_speed , wifi_latency , date */


/* Question 1 : Find the average wifi speed for each wifi */
    
select avg(wifi_speed), wifi_id from wifi_details group by wifi_id;    
    
    
/* Question 2 : Find the average wifi speed for each wifi in the last 2 days */
    
select avg(wifi_speed), wifi_id, dt from wifi_details
where dt>=date_add(SYSDATE(), interval -2 day)
group by wifi_id, dt
order by dt DESC;

create table WIFI_DETAILS (
wifi_id varchar(100),
wifi_speed varchar(100),
wifi_latency int,
dt date )


INSERT INTO WIFI_DETAILS VALUES ( '1' , '120' , 12 , sysdate ( ) ) ;
INSERT INTO WIFI_DETAILS VALUES ( '2' , '110' , 12 , sysdate ( ) ) ;
INSERT INTO WIFI_DETAILS VALUES ( '1' , '111' , 12 , sysdate ( ) - 1 ) ;
INSERT INTO WIFI_DETAILS VALUES ( '2' , '122' , 12 , sysdate ( ) - 1 ) ;
INSERT INTO WIFI_DETAILS VALUES ( '1' , '89' , 12 , sysdate ( ) - 2 ) ;
INSERT INTO WIFI_DETAILS VALUES ( '2' , '56' , 12 , sysdate ( ) - 3 ) ;

15. Show the first and last values of the table

(select * from Employee order by empid ASC Limit 1)        /* First */
UNION
(select * from Employee order by empid DESC Limit 1);      /* Last */

16. First 4 char of name column string

SELECT SUBSTRING(first_name,1,4) FROM employee;

17. Get all the records that are not working in "HR" and "Development"

select * from employee
where department_name NOT in ("HR" , "Development");

18. Top 2 and last 2 salary

(select * from Employee order by salary asc limit 2)
UNION
(select * from Employee order by salary desc limit 2);

19. Employee Id in Ascending and Salary in descending

select * from Employee order by Department_name ASC, salary DESC;

20. Joining date in December

select * from employee
where month(joining_date) = 12;

21. Change datatype of a column

ALTER TABLE employee
ALTER COLUMN DateOfBirth date;

22. Remove Duplicate Rows

DELETE t1 FROM Employee t1
INNER JOIN Employee t2 
WHERE t1.empid < t2.empid;

23. Position of "a" in each first_name

select first_name, instr(first_name, 'a') from worker;

24. Unique value of the department and its length

select distinct length(department) from worker;

25. details of the workers whose names contain "a"

select * from worker
where instr(first_name, "a");

26. details of a worker whose name ends with 'h' and contains 6 alphabets

select * from worker
where first_name like "_____h"

# ---------------------------------------------------------------

select * from worker
where lenght(first_name) = 6 and first_name like "%h";

27. number of workers in each department

select department, count(*) as numOfWorker from worker
group by department
order by numOfWorker DESC;

28. Print details of the workers who are also managers

select worker_id, first_name, department, salary, worker_ref_id, worker_title
from worker
left join title
on worker.worker_id = title.worker_ref_id
where title.worker_title = 'Manager';

29. Show only odd rows from the table worker

select * from worker
where (worker_id%2) != 0;

30. Show top n records of the table

select * from worker limit 2;

31. 3rd highest salary from the table

select * from worker as w1
where (3-1) = (select count(distinct(w2.salary)) from worker as w2
               where w1.salary < w2.salary);

# -------------------------------------------------------------------------

select salary from worker
order by salary DESC
LIMIT N-1,1;

32. List of workers with the same salary

select department, count(worker_id) as numOfEmp from worker
group by department having count(worker_id) < 4 ;

34. department with less than 5 people

SELECT t.DEPARTMENT,t.FIRST_NAME,t.Salary 
from(SELECT max(Salary) as TotalSalary,DEPARTMENT from Worker group by DEPARTMENT) as TempNew 
Inner Join Worker t on TempNew.DEPARTMENT=t.DEPARTMENT 
and TempNew.TotalSalary=t.Salary;

36. create a view of only admin, show, drop

/* create view */

CREATE VIEW department_only_admin AS
SELECT * FROM worker
where department = 'Admin';

/* show view */

SELECT * FROM department_only_admin;

/* drop view */

DROP VIEW department_only_admin;

37. Rich or Poor Employee

SELECT empid, first_name, salary,
IF (salary>55000, 'Rich Employee', 'Poor Employee')
AS emp_Status
FROM Employee;

38. Rich, Poor, Middle Employee Status using CASE

SELECT empid, first_name, salary,
(CASE
    WHEN salary > 50000 THEN "RICH EMP"
    WHEN salary = 50000 THEN "MIDDLE EMP"
    ELSE "POOR EMP"
END) AS emp_status
FROM Employee;

39. Create a Stored Procedure

/ *Schemas ==> Stored Procedures (Right Click) ==> Create Stored Procedure ==> {write code} ==> Apply ==> Apply */

/* call strored_procedure_name */


CREATE DEFINER=`root`@`localhost` PROCEDURE `get_emp_info`()
BEGIN
    select * from Employee;
END

/* ---------------------------------------------------------------- */

call get_emp_info;

/* IN */

CREATE DEFINER=`root`@`localhost` PROCEDURE `get_rich_emp_info`(IN amt int)
BEGIN
    select * from Employee where salary > amt;
END

/* ---------------------------------------------------------------- */

call get_emp_info(50000);

/* OUT */

CREATE DEFINER=`root`@`localhost` PROCEDURE `get_rich_emp_info`(OUT records int)
BEGIN
    select count(*) into records from Employee where salary = 50000;
END

/* ---------------------------------------------------------------- */

call get_rich_emp_info(@records);

select @records as TotalRecords;

40. Commit and Rollback

SET SQL_SAFE_UPDATES = 0;

# your code SQL here

SET SQL_SAFE_UPDATES = 1;

/* example: */
    
SET autocommit = 0;

update employee set salary = 60000 where first_name = 'Krish';

SET autocommit = 1;

rollback;

41. Export in CSV

SELECT Id, Name, Email, Phone, City FROM employee
INTO OUTFILE 'C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/employee_backup.csv'   
FIELDS ENCLOSED BY '"'   
TERMINATED BY ';'   
ESCAPED BY '"'   
LINES TERMINATED BY '\r\n';

42. CSV in MySQL

LOAD DATA LOCAL INFILE '/path/to/your/csv/file/model.csv'
INTO TABLE test.dummy FIELDS TERMINATED BY ','
ENCLOSED BY '"' LINES TERMINATED BY '\n';

So these are the question which is asked the most.