Data bit for the day – 8th December 2021
Data bit for the day – 27th August 2021
Data bit for the day – 9th July 2021
Data bit for the day – 24th May 2021
Data bit for the day – 15th February 2021
Data bit for the day – 9th February 2021
Primary and Foreign Key in SQL
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
Top N Salary – SQL
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
Joins in SQL
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