Category: Big Data

Syntax to set EMPLOYEE_ID  as primary key in employee table.

ALTER TABLE EMPLOYEE add CONSTRAINT EMPLOYEE_PK PRIMARY KEY(EMPLOYEE_ID)

Syntax to set 2 fields(EMPLOYEE_ID, FIRST_NAME) as primary key in employee table.

ALTER TABLE EMPLOYEE add CONSTRAINT EMPLOYEE_PK PRIMARY KEY(EMPLOYEE_ID, FIRST_NAME)

Syntax to drop primary key on employee table.

ALTER TABLE EMPLOYEE drop CONSTRAINT EMPLOYEE_PK

Syntax to create EMPLOYEE_REF_ID in INCENTIVES table as foreign key with respect to EMPLOYEE_ID in employee table.

ALTER TABLE INCENTIVES ADD CONSTRAINT INCENTIVES_FK FOREIGN KEY(EMPLOYEE_REF_ID) REFERENCES EMPLOYEE(EMPLOYEE_ID)

Syntax to drop foreign key on employee table.

ALTER TABLE INCENTIVES drop CONSTRAINT INCENTIVES_FK

Query to select TOP 2 salary from employee table.

select * from employee order by salary desc limit 2

Query to select TOP N salary from employee table.

select * from employee order by salary desc limit N

Query to select 2nd Highest salary from employee table.

select min(SALARY) from 
(select * from employee order by salary desc limit 2) a

Query to select Nth Highest salary from employee table.

select min(SALARY) from 
(select * from employee order by salary desc limit N) a

Query to select First_Name, Last_Name from employee table as separate rows.

select FIRST_NAME from EMPLOYEE union select LAST_NAME from EMPLOYEE

Query to select first_name, incentive amount from employee and incentives table for those employees who have incentives.

select FIRST_NAME, INCENTIVE_AMOUNT from employee A inner join incentives B on A.EMPLOYEE_ID=B.EMPLOYEE_REF_ID

Query to select first_name, incentive amount from employee and incentives table for those employees who have incentives and incentive amount greater than 3000.

select FIRST_NAME, INCENTIVE_AMOUNT from employee A inner join incentives B on A.EMPLOYEE_ID=B.EMPLOYEE_REF_ID and INCENTIVE_AMOUNT > 3000

Query to select first_name, incentive amount from employee and incentives table for all employees even if they didn’t get incentives.

select FIRST_NAME, INCENTIVE_AMOUNT from employee A left join incentives B on A.EMPLOYEE_ID=B.EMPLOYEE_REF_ID

Query to select first_name, incentive amount from employee and incentives table for all employees even if they didn’t get incentives and set incentive amount as 0 for those employees who didn’t get incentives.

select FIRST_NAME, IFNULL(INCENTIVE_AMOUNT,0) from employee A left join incentives B on A.EMPLOYEE_ID=B.EMPLOYEE_REF_ID

Query to select first_name, incentive amount from employee and incentives table for all employees who got incentives using right join.

select FIRST_NAME, IFNULL(INCENTIVE_AMOUNT,0) from employee A right join incentives B on A.EMPLOYEE_ID=B.EMPLOYEE_REF_ID

Query to select max incentive with respect to employee from employee and incentives table using sub query.

select DEPARTMENT,(select IFNULL(max(INCENTIVE_AMOUNT),0) from INCENTIVES where EMPLOYEE_REF_ID=EMPLOYEE_ID) Max_incentive from EMPLOYEE