Page 273 - PYTHON-12
P. 273

Resultant table: student

              Rollno  Name                Marks        DOB
                 3.   Ankit Sharma          76     02-Feb-2000
                 6.   Diksha Sharma         80     17-Dec-1999
              2 rows in a set (0.02 sec)

              This command shall display the Rollno, Name, Marks and DOB of all the students whose Name
              contains the letter ‘r’ from the third last position.

              12.18 SORTING IN SQL—ORDER BY


              The SQL ORDER BY clause is used to sort the data in ascending or descending order based on one
              or more columns. The ORDER BY keyword is used to sort the result-set by one or more fields in a
              table. This clause sorts the records in the ascending order (ASC) by default. Therefore, in order to
              sort the records in descending order, DESC keyword is to be used. Sorting using ORDER BY clause
              can be done on multiple columns, separated by comma.
                                                                          Rollno  Name               Marks
              Syntax for ORDER BY clause:                                   8.   Akshay Dureja         90

                                                                            3.   Ankit Sharma          76
                 SELECT  <column-list>  FROM  <table_name>  [WHERE
                                                                            2.   Deep Singh            98
                 <condition>] ORDER BY <column_name> [ASC|DESC];
                                                                            6.   Diksha Sharma         80
                 Here, WHERE clause is optional.
                                                                            7.   Gurpreet Kaur        NULL
              For example,                                                  5.   Payal Goel            82
                                                                           10.   Prateek Mittal        75
                To display the roll number, name and marks of students
                                                                            4.   Radhika Gupta         78
                 on the basis of their marks in the ascending order.
                                                                            1.   Raj Kumar             93
                mysql> SELECT  Rollno, Name, Marks FROM  student
                                                                            9.   Shreya Anand          70
                 ORDER BY Name;

                To display the roll number, name and marks of all the students in the descending order of their
                 marks and ascending order of their names.
                mysql> SELECT Rollno, Name, Marks FROM student ORDER BY Marks DESC, Name;
                       Sorting on Column Alias

                       If a column alias is defined for a column, it can be used for displaying rows in ascending
                       or descending order using ORDER BY clause.

                       For example, SELECT Rollno, Name, Marks AS Marks_obtained
                       FROM student
                                                                                Alias name
                       ORDER BY Marks_obtained;
              AGGREGATE FUNCTIONS
                                                                                                                  Relational Database and SQL
              Till  now, we have studied  about  single-row  functions which  work  on a single value.  SQL also
              provides multiple-row functions which work on multiple values. So, we can apply SELECT query
              on a group of records rather than the entire table. Therefore, these functions are called Aggregate
              functions or Group functions.






                                                                                                            12.43
   268   269   270   271   272   273   274   275   276   277   278