Basic HTML Version

|  | Table of Contents |  | View Full Version

Page 53 - Modular_V2.0_SQL_Flipbook
P. 53

    Let us consider another example of Table - FRESH

                                                                  Table-FRESH

                              O_number            O_amt            O_name             O_date        Customer_code
                                 5100             100000        Okra                2025-01-21             101
                                 5101             125000        Brinjals            2025-01-19             107
                                 5102              90000        Tomatoes            2025-01-10             106
                                 5103              88000        Peas                2024-12-11             103

                         SELECT O_number, O_amt, O_date, Customer_code FROM FRESH

                         WHERE O_amt = (SELECT MAX(O_amt) FROM FRESH WHERE O_date >'2025-01-10');
                     The above query shows the use of MAX function and relational operator in a subquery. The given
                     query will generate the output for the customer having maximum O_amt, when purchase is made
                     after 2025-01-10.

                     Output:
                                       +----------+--------+------------+---------------+
                                       | O_number | O_amt  | O_date     | Customer_code |
                                       +----------+--------+------------+---------------+
                                       | 5101     | 125000 | 2025-01-10 | 107           |
                                       +----------+--------+------------+---------------+
                         JOINS

                     A JOIN clause is used to combine rows from two or more tables based on a related column between
                     them. SQL joins are essentially the tools that efficiently allows us to extract meaningful information
                     from multiple tables. They help the programmer by simplifying the process of making sense of
                     many interlinked database tables and enable them to extract the required information based on the
                     situation, thus producing the desired solution. Let’s understand how joins work with the following
                     example:

                     Let us consider the following tables:
                                                               Table-STUDENTS

                                       Stu_id      Stu_name            Age            Class         Marks
                                        101       Rahim                 18              XII           87
                                        102       Raja                  19              XII           66
                                        103       Riya                  18              XII           55

                                                                   Table-FEE
                                              Stu_id         Fees             Fine         Transport

                                               101       12000                100             2000
                                               102       13000                500             3000
                                               103       12000                100             5000

                         SELECT Stu_name,  Age, Fees, Transport  FROM STUDENTS,  FEE WHERE STUDENTS.
                         Stu_id = FEE.Stu_id;
                                                        JOIN Condition

                                                                                                                         51
                                                                                            Advanced Features of MySQL

| 48 49 50 51 52 53 54 55 56 57 58 |