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