EVERY POSSIBLE MYSQL INTERVIEW QUESTION FOR DATA SCIENCE, DATA ANALYST, DATA ENGINEER, AND DATABASE MANAGER.
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
/* 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;
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;
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;
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;
SELECT TOP 1 COUNT(*) AS cnt_of_emp_as_per_dep, e_dept FROM Employees
GROUP BY e_dept
ORDER BY COUNT(*) DESC;
SELECT ENAME, DNAME, Gender
FROM EmployeeD
JOIN Departements ON EmployeeD.DID = Departements.DID
JOIN Genders ON Genders.GID = EmployeeD.GID;
select T1.T1Column1,T2.T2Column1
from T1
JOIN T2 on T1.ID = T2.ID;
Alter Table T2 Alter column Id nvarchar(3); /*Change datatype*/
select * from EmpPos where DATEPART(Month,e_join)='12';
SELECT e_dept, AVG(e_sal) AS DEPT_COST_AVERAGE FROM Employees
WHERE e_dept = 'ALY'
GROUP BY e_dept;
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;
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');
select * from Employee where first_name like 'k%';
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);
select * from Employee where year(joining_date)=2014 and month(joining_date)=12 and day(joining_date)=13;
select department_name, count(empid) as cnt
from Employee Group by department_name;
/* 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';
/* Copy only schema / structure */
create table clone_emp like Employee;
/* Copy whole table */
create table clone_table as select * from Employee;
select * from Employee
order by salary
desc limit 2,1;
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);
SELECT * FROM tablename WHERE age>25 GROUP BY id HAVING something > 5 ORDER BY date_from LIMIT 10;
select LEFT(first_name, 5) as First_five from Employee;
SELECT CONCAT(LEFT(credit_card_no,4),'*****') as masked_cred_card_details from CREDIT_CARD_DETAILS;
select substr(credit_card_no,3,7) from credit_card_details;
select * from employee where salary > (select salary from Employee where first_name='Krish');
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 ) ;
(select * from Employee order by empid ASC Limit 1) /* First */
UNION
(select * from Employee order by empid DESC Limit 1); /* Last */
SELECT SUBSTRING(first_name,1,4) FROM employee;
select * from employee
where department_name NOT in ("HR" , "Development");
(select * from Employee order by salary asc limit 2)
UNION
(select * from Employee order by salary desc limit 2);
select * from Employee order by Department_name ASC, salary DESC;
select * from employee
where month(joining_date) = 12;
ALTER TABLE employee
ALTER COLUMN DateOfBirth date;
DELETE t1 FROM Employee t1
INNER JOIN Employee t2
WHERE t1.empid < t2.empid;
select first_name, instr(first_name, 'a') from worker;
select distinct length(department) from worker;
select * from worker
where instr(first_name, "a");
select * from worker
where first_name like "_____h"
# ---------------------------------------------------------------
select * from worker
where lenght(first_name) = 6 and first_name like "%h";
select department, count(*) as numOfWorker from worker
group by department
order by numOfWorker DESC;
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';
select * from worker
where (worker_id%2) != 0;
select * from worker limit 2;
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;
select department, count(worker_id) as numOfEmp from worker
group by department having count(worker_id) < 4 ;
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;
/* 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;
SELECT empid, first_name, salary,
IF (salary>55000, 'Rich Employee', 'Poor Employee')
AS emp_Status
FROM Employee;
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;
/ *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;
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;
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';
LOAD DATA LOCAL INFILE '/path/to/your/csv/file/model.csv'
INTO TABLE test.dummy FIELDS TERMINATED BY ','
ENCLOSED BY '"' LINES TERMINATED BY '\n';
No comments:
No Spamming and No Offensive Language