Mainframe – SQL Challenge -1

Question 1:

Get maximum salaries department wise?

Sol: SELECT DeptID, MAX(Salary) FROM EmpDetails GROUP BY DeptID

Question 2:

Write COBOL logic to get nth prime number?

IDENTIFICATION DIVISION.

PROGRAM-ID. PRIME.

AUTHOR. XYZ.

DATE-WRITTEN. JULY 2014.

ENVIRONMENT DIVISION.
CONFIGURATION SECTION.
SOURCE-COMPUTER. IBM-370 WITH DEBUGGING MODE.

DATA DIVISION.
WORKING-STORAGE SECTION.

01 WS-VAR.
10 WS-INPUT PIC 9(04) VALUE ZEROES.

10 WS-OUT PIC 9(04) VALUE ZEROES.

10 WS-TEMP-1 PIC 9(4) VALUE 0.
10 WS-TEMP-2 PIC 9(4) VALUE 0.
10 WS-TEMP-3 PIC 9(4) VALUE 0.
10 WS-TEMP-4 PIC 9(4) VALUE 0.
10 WS-TEMP-5 PIC 9(4) VALUE 0.

PROCEDURE DIVISION.
ACCEPT WS-INPUT.
DISPLAY ‘INPUT:’ WS-INPUT
MOVE 1 TO WS-TEMP-1
MOVE 2 TO WS-OUT
MOVE 2 TO WS-TEMP-2

IF WS-TEMP-1 = WS-INPUT
DISPLAY WS-INPUT ‘TH PRIME NO IS:’ WS-OUT
GO TO 500-PARA
ELSE
COMPUTE WS-OUT = WS-OUT + 1
PERFORM 100-PARA
END-IF.

100-PARA.
PERFORM 200-PARA UNTIL WS-TEMP-2 >= WS-OUT
PERFORM 300-PARA.

200-PARA.
DIVIDE WS-OUT BY WS-TEMP-2 GIVING WS-TEMP-3
REMAINDER WS-TEMP-4
IF WS-TEMP-4 = 0
COMPUTE WS-OUT = WS-OUT + 1
MOVE 2 TO WS-TEMP-2
ELSE
COMPUTE WS-TEMP-2 = WS-TEMP-2 + 1
END-IF
GO TO 100-PARA.

300-PARA.
COMPUTE WS-TEMP-1 = WS-TEMP-1 + 1
IF WS-TEMP-1 = WS-INPUT
DISPLAY WS-INPUT ‘TH PRIME NO IS:’ WS-OUT
GO TO 500-PARA
ELSE
COMPUTE WS-OUT = WS-OUT + 1
MOVE 2 TO WS-TEMP-2
GO TO 100-PARA
END-IF.

500-PARA.
DISPLAY WS-OUT
STOP RUN.

Question 3:

Get employee details where salaries equal in each department?

SELECT * FROM employees e1
WHERE e1.employee_id = ( SELECT employee_id
FROM employees e2
WHERE e2.department_id = e1.department_id
AND e2.salary = e1.salary);

Question 4:

Select employee details , who do not have supervisor?

Select * from emp where supervisor is null.