Page 272 - Information_Practice_Fliipbook_Class11
P. 272

Table 9.16: First name and the city of employees whose salary is greater than 70000 and who do not live in Delhi

                                               +-------------+----------------+
                                               | FName       | City           |
                                               +-----------------+------------+
                                               | Raj         | Andhra Pradesh |
                                               | Dhiraj      | Guwahati       |
                                               | Hiten       | Jamshedpur     |
                                               | Anshul      | Noida          |
                                               +-------------+----------------+
        Query: To display the records of employees who either live in Delhi or in Noida.

        SELECT *

        FROM EMPLOYEE
        WHERE City = 'Delhi' OR City = 'Noida'
        On executing the above statement, SQL will produce output shown in  Table 9.17.
                                 Table 9.17: Details of employees who either live in Delhi or in Noida.
         +-------+--------+---------+--------+-------------------------+----------------+----------+------------+--------+---------+
         | ID    | FName  | LName   | Gender | Address                 | City           | Pin_Code | DOB        | Salary | Dept_No |
         +-------+--------+---------+--------+-------------------------+----------------+----------+------------+--------+---------+
         | 10003 | Muskan | Taneja  | F      | 8/33, Geeta Colony      | Delhi          | 110031   | 1990-01-25 | 100000 |       2 |
         | 10005 | Anshul | Verma   | M      | House 10,Sector 16,     | Noida          | 201304   | 1990-01-01 | 100000 |       1 |
                                               Gautam Budh Nagar
         | 10008 | Naval  | Dhingra | M      | E-14 Vivek Vihar        | Delhi          | 110095   | 1975-08-04 | 70000  |       2 |
         +-------+--------+---------+--------+-------------------------+----------------+----------+------------+--------+---------+
        Query: To display the department numbers of all employees.

        SELECT Dept_No

        FROM EMPLOYEE;
        On executing the above statement, SQL will produce output shown in Table 9.18.

                                        Table 9.18: Department numbers of all employees
                                                       +-----------+
                                                       | Dept_No   |
                                                       +-----------+
                                                       | 2         |
                                                       | 1         |
                                                       | 2         |
                                                       | 4         |
                                                       | 1         |
                                                       | 4         |
                                                       | 5         |
                                                       | 2         |
                                                       | 4         |
                                                       | 5         |
                                                       +-----------+


        9.6 DISTINCT Clause

        We see in Table 9.18 that the execution of the above queries yields  the department numbers, with multiple values.
        Table 9.19 will look more elegant if we remove the repeating tuples. For this purpose, the keyword DISTINCT is used
        in the SELECT clause to retrieve unique values of an attribute. The resulting SQL statement would be:

        SELECT DISTINCT Dept_No
        FROM EMPLOYEE;
        Execution of the above SELECT statement will produce the output shown in Table 9.18.




          258  Touchpad Informatics Practices-XI
   267   268   269   270   271   272   273   274   275   276   277