Scribd
Upload
6 views2 pages

Acropolis Q1

The document outlines three tables: DEPARTMENT, EMPLOYEE, and PAYMENTS, detailing their structures and relationships. It presents a problem statement requiring the extraction of the highest salary credited to an employee, excluding transactions made on the 1st of any month, along with employee details such as name, age, and department. The output format specifies the required columns: SALARY, NAME, AGE, and DEPARTMENT_NAME.

Uploaded by

shashwatyadav576
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
6 views2 pages

Acropolis Q1

The document outlines three tables: DEPARTMENT, EMPLOYEE, and PAYMENTS, detailing their structures and relationships. It presents a problem statement requiring the extraction of the highest salary credited to an employee, excluding transactions made on the 1st of any month, along with employee details such as name, age, and department. The output format specifies the required columns: SALARY, NAME, AGE, and DEPARTMENT_NAME.

Uploaded by

shashwatyadav576
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
You are on page 1/ 2

DEPARTMENT

DEPARTMENT_ID DEPARTMENT_NAME
1 HR
2 Finance
3 Engineering
4 Sales
5 Marketing
6 IT

EMPLOYEE
EMP_ID FIRST_NAME LAST_NAME DOB GENDER DEPARTMENT
1 John Williams 1980-05-15 Male 3
2 Sarah Johnson 1990-07-20 Female 2
3 Michael Smith 1985-02-10 Male 3
4 Emily Brown 1992-11-30 Female 4
5 David Jones 1988-09-05 Male 5
6 Olivia Davis 1995-04-12 Female 1
7 James Wilson 1983-03-25 Male 6
8 Sophia Anderson 1991-08-17 Female 4
9 Liam Miller 1979-12-01 Male 1
10 Emma Taylor 1993-06-28 Female 5

PAYMENTS
PAYMENT_ID EMP_ID AMOUNT PAYMENT_TIME
1 2 65784.00 2025-01-01 13:44:12.824
2 4 62736.00 2025-01-06 18:36:37.892
3 1 69437.00 2025-01-01 10:19:21.563
4 3 67183.00 2025-01-02 17:21:57.341
5 2 66273.00 2025-02-01 11:49:15.764
6 5 71475.00 2025-01-01 07:24:14.453
7 1 70837.00 2025-02-03 19:11:31.553
8 6 69628.00 2025-01-02 10:41:15.113
9 4 71876.00 2025-02-01 12:16:47.807
10 3 70098.00 2025-02-03 10:11:17.341
11 6 67827.00 2025-02-02 19:21:27.753
12 5 69871.00 2025-02-05 17:54:17.453
13 2 72984.00 2025-03-05 09:37:35.974
14 1 67982.00 2025-03-01 06:09:51.983
15 6 70198.00 2025-03-02 10:34:35.753
16 4 74998.00 2025-03-02 09:27:26.162
There are three tables: -
1. DEPARTMENT: Contains details about the department.

o DEPARTMENT_ID (Primary Key)

o DEPARTMENT_NAME

2. EMPLOYEE: Contains employee details.

o EMP_ID (Primary Key)

o FIRST_NAME

o LAST_NAME

o DOB (Date of Birth)

o GENDER

o DEPARTMENT (Foreign Key referencing DEPARTMENT_ID in DEPARTMENT)

3. PAYMENTS: Contains salary payment records.

o PAYMENT_ID (Primary Key)

o EMP_ID (Foreign Key referencing EMP_ID in EMPLOYEE)

o AMOUNT (Salary credited)

o PAYMENT_TIME (Date and time of the transaction)

Problem Statement:
Find the highest salary that was credited to an employee, but only for transactions that were not
made on the 1st day of any month. Along with the salary, you are also required to extract the
employee data like name (combine first name and last name into one column), age and
department who received this salary.

Output Format:

• The output should contain the following columns:


1. SALARY: The highest salary that was credited not on the 1st day of the month.
2. NAME: Combine the columns FIRST_NAME and LAST_NAME into one single
column as NAME with format <first name><space><last name>. Ex.
FIRST_NAME: John, LAST_NAME: Doe then, NAME should be combined as “John
Doe”
3. AGE: The age of the employee who received that salary.
4. DEPARTMENT_NAME: Name of the department against employee.

---------------------------------------------------END------------------------------------------------

You might also like