How to Print the List of Employees Who Have Joined on a Particular Month from an Employee List

display details of employees who joined in first march of each year

First of all, in your question you say you are looking for employees on the 23rd of March, but in the where clause of your example query, you are searching for employees hired on the 23rd of December.

To prevent any differentiating spellings of months in different languages and compare using one filter in your where clause instead of two, you can use a query like this one.

SELECT FIRST_NAME, LAST_NAME, EMAIL
  FROM EMPLOYEES
 WHERE TO_CHAR (hire_date, 'MMDD') = '0323';

SQL Query employees who joined in x year y month not giving desired result

While converting to a varchar appears a straightforward solution, its performance will not be optimal, because its not sargable - indexes can't be used once you start transforming columns i.e. by converting them, when used in a where clause.

Using a window compare removes the need for a function and performs better as follows:

-- Find all employees hired in 2020
SELECT * 
FROM dbo.Employees 
WHERE Hiredate >= '01 Jan 2020' and HireDate < '01 Jan 2021'

-- Find all employees hired in May 2020
SELECT * 
FROM dbo.Employees 
WHERE Hiredate >= '01 May 2020' and HireDate < '01 Jun 2020'

How to fetch the records from a table where joining date of an employee is less than 15th of every month?

Use EXTRACT on the HIREDATE and check if it is less than 15:

SELECT * FROM EMP WHERE EXTRACT(day FROM HIREDATE) < 15

More about EXTRACT function: http://docs.oracle.com/cd/B19306_01/server.102/b14200/functions050.htm

Print details of an employee by entering the name of employee in Python

a lots of bug in your code, the correct code will be,

n = int(input('Enter the number of employee: '))
employees = {}
    
for i in range(n):
    name = input('Enter the name of the employee: ')
    emp_id = input("Enter employee Id: ")
    sal = int(input("Enter the employee salary: "))
    address = input('Enter the employee address: ')
    employees[name] = [emp_id, sal, address]

while True:
    name = input('Enter employee name: ')
    info = employees.get(name, -1)
    if info == -1:
        print('Employee does not exist')
    else:
        print(f'Employee details are: \n Employee Id: {info[0]}\n Salary: {info[1]}\n Address: info[2])')
    
    exit_choice = input('Do you want to exit [Yes|No]: ')
    if exit_choice == 'Yes' or exit_choice == 'yes':
        break

SQL Query to fetch number of employees joined over a calender year, broken down per month

SELECT   Trunc(EMP_JN_DT,'MM') Emp_Jn_Mth,
         Count(*)
FROM     EMP_REG
WHERE    EMP_JN_DT between date '2009-01-01' AND date '2009-12-31'
GROUP BY Trunc(EMP_JN_DT,'MM')
ORDER BY 1;

If you do not have anyone join in a particular month then you'd get no row returned. To over come this you'd have to outerjoin the above to a list of months in the required year.

Find an Employee by Name and throw a custom Exception if the given name was not Found

it will throw an exception every time if condition does not meet the requirement

No, that's not correct. An exception will be thrown only once if the very first employee in the list has a name which is different from the target name.

The method will be deallocated from the stack, i.e. its execution will be terminated. And exception will be propagated to the calling method. If the caller will not be able to handle it, the exception will be propagated further.

To prevent it, you need to place the throws clause outside the loop:

public Employee findEmployee(String Name) throws emplyeeNotFoundException {
    
    Employee result = null;
    
    for (Employee value : employee) {
        if (value.getName().equals(Name)) {
            result = value;
            break;
        }
    }
    if (result == null) {
        throw new emplyeeNotFoundException(Name);
    }

    return result;
}

In my personal opinion, it's better to avoid returning from the middle of the method. It makes the code more structured and easier to read.

And since you've specified the tag java-stream here another solution using Stream API.

Stream-based solution

You can use apply filter() with your condition and then findFirst() as a terminal operation, which will produce an optional result Optional<Employee>.

And finally, apply orElseThrow() on the optional object, that will either retrieve the employee or will throw an exception if optional is empty.

One of the flavors on orElseThrow() expects a supplier as an argument, so we can the exception that be thrown if result wasn't found.

return employee.stream()
    .filter(emp -> emp.getName().equals(name))
    .findFirst()
    .orElseThrow(() -> new EmplyeeNotFoundException(name));

Sidenote:

• adhere to the Java naming conventions when you're giving names to your classes, methods, etc. Names of classes should start with a capital letter - EmplyeeNotFoundException, and variable-name with a lower case letter - name.

Display employee name who were hired on Sunday of October Month

The MON date format is the abbreviated month name, in the current sesison's NLS date language. You can can compare the abbreviation with the short name, or the full month name:

to_char(hiredate,'MON')='OCT'

or

to_char(hiredate,'FMMONTH')='OCTOBER'

The 'FM' format modifier stops it adding trailing spaces, which it does by default, up to the length of the longest possible value. If you don't do that you either have to pad your fixed value, or trim the result. (Note that for some languages the abbreviations need FM for comparison too as even their length can change; not in English as the MON and DY values are all 3 characters; but French month abbreviations, for example, can be 3 or 4 characters).

It's generally better to use month numbers for this though:

to_char(hiredate,'MM')='10'

That will get you October in any year; if you want this October you can include the year:

to_char(hiredate,'YYYYMM')='201510'

Or if you want the previous month of the current year you can use:

trunc(hire_date, 'MM') = add_months(trunc(sysdate, 'MM'), -1)

The same thing happens with the day; the DAY format pads to the longest day name by default, so you need:

to_char(hiredate,'DY')='SUN'

or

to_char(hiredate,'FMDAY')='SUNDAY'

You can use day numbers here but they are locale-dependent too. If you're gong to use names it's safer to specify the language:

to_char(hire_date,'FMDAY','NLS_DATE_LANGUAGE=''ENGLISH''')='SUNDAY'

Oracle does have the Q date format model, fixed to calendar months - so January-March is Q1. You may want financial quarters; or may want the previous three months; or something else.

---

Related Topics