Page 275 - Information_Practice_Fliipbook_Class11
P. 275

WHERE Salary BETWEEN 85000 AND 100000;
            Table 9.23 displays the output of the given query.

                                      Table 9.23: Employees having salary between 85000 and 100000
                                                     +-------+--------+--------+
                                                     | ID    | FName  | Salary |
                                                     +-------+--------+--------+
                                                     | 10001 | Raj    | 100000 |
                                                     | 10002 | Dhiraj |  85000 |
                                                     | 10003 | Muskan | 100000 |
                                                     | 10004 | Hiten  | 100000 |
                                                     | 10005 | Anshul | 100000 |
                                                     +-------+--------+--------+
            Note that Table 9.17 includes salary 85000 and 100000, specified for the BETWEEN operator. Thus,  BETWEEN is an
            inclusive operator as it includes the begin and end values of the specified range.
            To display the first name, last name, and date of birth of all employees born between the years 1985 to 1990.

            SELECT FName, LName, DOB

            FROM EMPLOYEE
            WHERE DOB BETWEEN '1985-01-01' AND '1990-12-31';
            Execution of the above statement will produce the output shown in Table 9.24.


                          Table 9.24: Employees whose date of birth is between '1985-01-01' and '1990-12-31'
                                                 +--------+----------+-------------+
                                                 | FName  | LName    |  DOB        |
                                                 +--------+----------+-------------+
                                                 | Muskan | Taneja   | 1990-01-25  |
                                                 | Hiten  | Oberoi   | 1985-06-24  |
                                                 | Anshul | Verma    | 1990-01-01  |
                                                 | Savita | Ambedkar | 1987-07-11  |
                                                 +--------+----------+-------------+

                   Note that in the above example, the date appears in YYYY-MM-DD format. We can display a date in DD/MM/YYYY
                   format as follows:

                   SELECT FName, LName, DATE_FORMAT(DOB,'%d/%m/%Y') AS DOB
                   FROM EMPLOYEE
                   WHERE DOB BETWEEN '1985-01-01' AND '1990-12-31';
                   Execution of the above statement will produce the output shown in Table 9.19.

                               Table 9.19: Employees whose date of birth is between '1985-01-01' and '1990-12-31'
                                                    +--------+----------+-------------+
                                                    | FName  | LName    |  DOB        |
                                                    +--------+----------+-------------+
                                                    | Muskan | Taneja   | 25-01-1990  |
                                                    | Hiten  | Oberoi   | 24-06-1985  |
                                                    | Anshul | Verma    | 01-01-1990  |
                                                    | Savita | Ambedkar | 11-07-1987  |
                                                    +--------+----------+-------------+

            Query: Display the first name, last name, and date of birth of all employees, except those who were born between the
            years other than 1985 to 1990.
            Solution: To answer the above query, we use NOT operator before BETWEEN operator as follows:

            SELECT FName, LName, DOB
            FROM EMPLOYEE
            WHERE DOB NOT BETWEEN '1985-01-01' AND '1990-12-31';
            Execution of the above statement will produce the output shown in Table 9.25.





                                                                Database Concepts and the Structured Query Language  261
   270   271   272   273   274   275   276   277   278   279   280