Basic HTML Version

|  | Table of Contents |  | View Full Version

Page 41 - Modular_V2.0_SQL_Flipbook
P. 41

    +-----------+
                                                                | New Marks |
                                                                +-----------+
                                                                | 17.40     |
                                                                +-----------+
                                                                | 13.20     |
                                                                +-----------+
                     This query will show the 20 percent of marks and display the result in New Marks column

                        Select 3.1.416 as pi;
                     This expression can help to set the value of pi as 3.1416.

                     Let's take some more examples.
                                                                Table-STUDENT

                                     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

                     Similarly, table aliases can be used to shorten the names of big table names if in use.
                        SELECT S.stu_name, F.Fee FROM STUDENT AS S, FEE AS F

                        WHERE S.Stu_id = F.Stu_id;
                     In the above example S and F are used instead of Student and Fee Table.

                         PERFORMING CALCULATIONS IN QUERIES

                     We can easily perform basic queries in MySQL using functions. The functions are called aggregate
                     functions or group functions.

                       1.  COUNT(): This function helps to count the number of records or values in a specified column/expression.

                       2.  AVG(): This function helps to find the average of any numeric field in the table.
                       3.  MAX(): This function is helpful in finding maximum value in the given set of values.

                       4.  MIN(): This function help in finding the minimum value in the given set of values.

                       5.  SUM(): This function finds the sum of values under a specific column.

                          Examples to show the working of MIN() and MAX() function.:
                              SELECT MIN(Fees) AS "min(fees)" FROM FEE;

                              SELECT MAX(Fees) AS "max(fees)" FROM FEE;

                                                                                                                         39
                                                                                                        More on MySQL

| 36 37 38 39 40 41 42 43 44 45 46 |