Certainly!

Here are answers to all the provided SQL queries using PostgreSQL syntax:

Second Highest Salary of Employee using Subquery:

sql

Copy code

SELECT MAX(salary) FROM employee WHERE salary < (SELECT MAX(salary) FROM employee);

Max Salary from each department:

sql

Copy code

SELECT deptid, MAX(salary) FROM employee GROUP BY deptid;

Display Current Date:

sql

Copy code

SELECT CURRENT_DATE;

Check Whether Date Passed to Query is in Given Format:

sql

Copy code

SELECT ISDATE('1/08/13') AS "MM/DD/YY";

Distinct Employee Names with DOB between 01/01/1960 to 31/12/1975:

sql

Copy code

SELECT DISTINCT empname FROM employees WHERE dob BETWEEN '1960-01-01' AND '1975-12-31';

Number of Employees According to Gender with DOB between 01/01/1960 to 31/12/1975:

sql
Copy code

SELECT COUNT(*), sex FROM employees WHERE dob BETWEEN '1960-01-01' AND '1975-12-31'
GROUP BY sex;

Employees with Salary Equal or Greater Than 10000:

sql

Copy code

SELECT empname FROM employees WHERE salary >= 10000;

Employee Names Starting with 'M':

sql

Copy code

SELECT * FROM employees WHERE empname LIKE 'M%';

Employee Records Containing the Word "Joe" (case-insensitive):

sql

Copy code

SELECT * FROM employees WHERE UPPER(empname) LIKE UPPER('%Joe%');

Year from Date:

sql

Copy code

SELECT EXTRACT(YEAR FROM CURRENT_DATE) AS "Year";

Fetch ALTERNATE Records (EVEN NUMBERED) from a table:

sql

Copy code

SELECT * FROM emp WHERE MOD(row_number() OVER(), 2) = 0;

Select ALTERNATE Records (ODD NUMBERED) from a table:

sql
Copy code

SELECT * FROM emp WHERE MOD(row_number() OVER(), 2) = 1;

Find the 3rd MAX salary in the emp table:

sql

Copy code

SELECT DISTINCT sal FROM emp ORDER BY sal DESC LIMIT 1 OFFSET 2;

Find the 3rd MIN salary in the emp table:

sql

Copy code

SELECT DISTINCT sal FROM emp ORDER BY sal LIMIT 1 OFFSET 2;

Select FIRST n records from a table:

sql

Copy code

SELECT * FROM emp LIMIT n;

Select LAST n records from a table:

sql

Copy code

SELECT * FROM emp ORDER BY column_name DESC LIMIT n;

List dept no., Dept name for all the departments in which there are no employees:

sql

Copy code

SELECT d.deptno, d.dname FROM dept d LEFT JOIN emp e ON d.deptno = e.deptno WHERE e.empno
IS NULL;

How to get 3 Max salaries:

sql
Copy code

SELECT DISTINCT sal FROM emp ORDER BY sal DESC LIMIT 3;

How to get 3 Min salaries:

sql

Copy code

SELECT DISTINCT sal FROM emp ORDER BY sal LIMIT 3;

How to get nth max salaries:

sql

Copy code

SELECT DISTINCT sal FROM emp ORDER BY sal DESC LIMIT 1 OFFSET n - 1;

Select DISTINCT RECORDS from emp table:

sql

Copy code

SELECT DISTINCT * FROM emp;

Delete duplicate rows in a table:

sql

Copy code

DELETE FROM emp a WHERE a.ctid <> (SELECT min(b.ctid) FROM emp b WHERE a.empno =
b.empno);

Count of number of employees in department wise:

sql

Copy code

SELECT deptno, COUNT(empno) FROM emp GROUP BY deptno;

Fetch monthly salary of each employee:

sql
Copy code

SELECT ename, sal / 12 AS monthlysal FROM emp;

Select all records from emp table where deptno = 10 or 40:

sql

Copy code

SELECT * FROM emp WHERE deptno IN (10, 40);

Select all records from emp table where deptno = 30 and sal > 1500:

sql

Copy code

SELECT * FROM emp WHERE deptno = 30 AND sal > 1500;

Select all records from emp where job not in SALESMAN or CLERK:

sql

Copy code

SELECT * FROM emp WHERE job NOT IN ('SALESMAN', 'CLERK');

Select all records from emp where ename in 'BLAKE', 'SCOTT', 'KING'and 'FORD':

sql

Copy code

SELECT * FROM emp WHERE ename IN ('BLAKE', 'SCOTT', 'KING', 'FORD');

Select all records where ename starts with ‘S’ and its length is 6 char:

sql

Copy code

SELECT * FROM emp WHERE ename LIKE 'S____';

Select all records where ename may be any no of characters but it should end with ‘R’:

sql

Copy code
SELECT * FROM emp WHERE ename LIKE '%R';

Count MGR and their salary in emp table:

sql

Copy code

SELECT COUNT(mgr), SUM(sal) FROM emp;

In emp table add comm+sal as total sal:

sql

Copy code

SELECT ename, sal + COALESCE(comm, 0) AS totalsal FROM emp;

Select any salary < 3000 from emp table:

sql

Copy code

SELECT * FROM emp WHERE sal < ANY (SELECT sal FROM emp WHERE sal < 3000);

Select all salary < 3000 from emp table:

sql

Copy code

SELECT * FROM emp WHERE sal < ALL (SELECT sal FROM emp WHERE sal < 3000);

Select all the employee group by deptno and sal in descending order:

sql

Copy code

SELECT ename, deptno, sal FROM emp ORDER BY deptno, sal DESC;

Create an empty table emp1 with the same structure as emp:

sql

Copy code

CREATE TABLE emp1 AS SELECT * FROM emp WHERE 1 = 2;

sql

Copy code

SELECT * FROM emp WHERE sal BETWEEN 1000 AND 2000;

**Select all records where dept no of both emp and

User

continue

ChatGPT

dept table matches:

sql

Copy code

SELECT * FROM emp WHERE deptno IN (SELECT deptno FROM dept);

Fetch all records but common records only once from emp1 and emp2:

sql

Copy code

(SELECT * FROM emp1) UNION (SELECT * FROM emp2);

Fetch only common records from emp and emp1:

sql

Copy code

(SELECT * FROM emp) INTERSECT (SELECT * FROM emp1);

Retrieve all records of emp1 that are not present in emp2:

sql

Copy code

(SELECT * FROM emp1) EXCEPT (SELECT * FROM emp2);

Count the total salary department-wise where more than 2 employees exist:
sql

Copy code

SELECT deptno, SUM(sal) AS totalsal

FROM emp

GROUP BY deptno

HAVING COUNT(empno) > 2;

Names of employees who have been working in the company for the past 5 years:

sql

Copy code

SELECT ename FROM emp WHERE CURRENT_DATE - hiredate > interval '5 years';

Employees who joined the company before 30th June 90 or after 31st Dec 90:

sql

Copy code

SELECT * FROM emp WHERE hiredate < '1990-06-30' OR hiredate > '1990-12-31';

Names of employees working in department number 10 or 20 or 40 or employees working as clerks,

sql

Copy code

SELECT ename FROM emp WHERE deptno IN (10, 20, 40) OR job IN ('CLERK', 'SALESMAN', 'ANALYST');

Employee names whose name starts with the alphabet S:

sql

Copy code

SELECT ename FROM emp WHERE ename LIKE 'S%';

Employee names whose name ends with the alphabet S:

sql
Copy code

SELECT ename FROM emp WHERE ename LIKE '%S';