Wednesday, 30 October 2019

Write a SQL query to order the (student ID, marks) table in descending order of the marks.


create table student
     (student_id int(5) primary key,
     name char(20),
     class char(3),
     DOB date,
     gender char(1),
    city char(10),
    marks int(3)
    );
Query OK, 0 rows affected, 2 warnings (1.42 sec)

desc student;
+------------+----------+------+-----+---------+-------+
| Field      | Type     | Null | Key | Default | Extra |
+------------+----------+------+-----+---------+-------+
| student_id | int(5)   | NO   | PRI | NULL    |       |
| name       | char(20) | YES  |     | NULL    |       |
| class      | char(3)  | YES  |     | NULL    |       |
| DOB        | date     | YES  |     | NULL    |       |
| gender     | char(1)  | YES  |     | NULL    |       |
| city       | char(10) | YES  |     | NULL    |       |
| marks      | int(3)   | YES  |     | NULL    |       |
+------------+----------+------+-----+---------+-------+
7 rows in set (0.02 sec)

insert into student values(12301,"abhinav","X","1995/06/06","M","Agra",551);
insert into student values(12302,"abhineet","XII","1993/05/07","M","Mumbai",462);
insert into student values(12303,"aniket","XI","1994/05/06","F","Delhi",400);
insert into student values(12304,"apar","XII","1995/08/08","F","Mumbai",450);
insert into student values(12305,"brij","XII","1995/10/08","M","Delhi",369);
insert into student values(12306,"bhuvnesh","XI","1994/12/12","F","Dubai",250);
insert into student values(12307,"devesh","X","1995/12/08","F","Moscow",377);
insert into student values(12308,"dhruva","X","1995/06/12","M","Moscow",489);

select * from student;
+-------------+----------+-------+------------+--------+--------+-------+
| student_id | name     | class |   DOB        | gender | city       | marks |
+-------------+----------+-------+------------+--------+--------+-------+
|      12301    | abhinav    | X       | 1995-06-06 | M        | Agra      |   551 |
|      12302    | abhineet   | XII     | 1993-05-07 | M        | Mumbai |   462 |
|      12303    | aniket       | XI      | 1994-05-06 | F         | Delhi      |   400 |
|      12304    | apar          | XII     | 1995-08-08 | F         | Mumbai |   450 |
|      12305    | brij            | XII     | 1995-10-08 | M        | Delhi      |   369 |
|      12306    | bhuvnesh | XI      | 1994-12-12 | F         | Dubai     |   250 |
|      12307    | devesh      | X       | 1995-12-08 | F         | Moscow |   377 |
|      12308    | dhruva      | X       | 1995-06-12 | M        | Moscow  |   489 |
+------------+----------+-------+------------+--------+--------+-------+
8 rows in set (0.00 sec)

 select student_id,marks from student order by marks;
+------------+-------+
| student_id | marks |
+------------+-------+
|      12306 |   250 |
|      12305 |   369 |
|      12307 |   377 |
|      12303 |   400 |
|      12304 |   450 |
|      12302 |   462 |
|      12308 |   489 |
|      12301 |   551 |
+------------+-------+
8 rows in set (0.00 sec)

mysql> select student_id,marks from student order by marks desc;
+------------+-------+
| student_id | marks |
+------------+-------+
|      12301 |   551 |
|      12308 |   489 |
|      12302 |   462 |
|      12304 |   450 |
|      12303 |   400 |
|      12307 |   377 |
|      12305 |   369 |
|      12306 |   250 |
+------------+-------+
8 rows in set (0.00 sec)

No comments:

Post a Comment