Untitled

avatar
unknown
plain_text
2 years ago
18 kB
5
Indexable
mysql> select * from students order by marks ;
+----+------------+-----------+------+--------+-------+
| id | first_name | last_name | age  | gender | marks |
+----+------------+-----------+------+--------+-------+
|  7 | Daniel     | Miller    |   21 | Male   |  56.7 |
| 13 | William    | Jackson   |   22 | Male   |  62.4 |
| 30 | Aiden      | King      |   21 | Male   |  62.8 |
| 21 | Henry      | Clark     |   21 | Male   |  65.9 |
|  4 | Sarah      | Jones     |   22 | Female |  67.8 |
| 25 | Abigail    | Walker    |   21 | Female |  71.8 |
| 16 | Mia        | Martin    |   21 | Female |  73.6 |
|  9 | Matthew    | Anderson  |   22 | Male   |  74.5 |
| 23 | Alexander  | Lewis     |   19 | Male   |    76 |
| 19 | Benjamin   | Martinez  |   19 | Male   |  77.2 |
|  2 | Emily      | Johnson   |   19 | Female |    78 |
| 14 | Isabella   | White     |   19 | Female |  79.8 |
| 22 | Ella       | Hall      |   22 | Female |  81.4 |
| 10 | Olivia     | Wilson    |   19 | Female |  82.6 |
| 28 | Jackson    | Wright    |   19 | Male   |  84.6 |
| 18 | Charlotte  | Garcia    |   22 | Female |  88.3 |
|  6 | Jessica    | Davis     |   19 | Female |  88.9 |
| 27 | Sofia      | Allen     |   22 | Female |  89.7 |
|  3 | Michael    | Williams  |   21 | Male   |  92.3 |
| 12 | Ava        | Taylor    |   21 | Female |    95 |
+----+------------+-----------+------+--------+-------+
20 rows in set (0.00 sec)

mysql> select * from students order by marks desc;
+----+------------+-----------+------+--------+-------+
| id | first_name | last_name | age  | gender | marks |
+----+------------+-----------+------+--------+-------+
| 12 | Ava        | Taylor    |   21 | Female |    95 |
|  3 | Michael    | Williams  |   21 | Male   |  92.3 |
| 27 | Sofia      | Allen     |   22 | Female |  89.7 |
|  6 | Jessica    | Davis     |   19 | Female |  88.9 |
| 18 | Charlotte  | Garcia    |   22 | Female |  88.3 |
| 28 | Jackson    | Wright    |   19 | Male   |  84.6 |
| 10 | Olivia     | Wilson    |   19 | Female |  82.6 |
| 22 | Ella       | Hall      |   22 | Female |  81.4 |
| 14 | Isabella   | White     |   19 | Female |  79.8 |
|  2 | Emily      | Johnson   |   19 | Female |    78 |
| 19 | Benjamin   | Martinez  |   19 | Male   |  77.2 |
| 23 | Alexander  | Lewis     |   19 | Male   |    76 |
|  9 | Matthew    | Anderson  |   22 | Male   |  74.5 |
| 16 | Mia        | Martin    |   21 | Female |  73.6 |
| 25 | Abigail    | Walker    |   21 | Female |  71.8 |
|  4 | Sarah      | Jones     |   22 | Female |  67.8 |
| 21 | Henry      | Clark     |   21 | Male   |  65.9 |
| 30 | Aiden      | King      |   21 | Male   |  62.8 |
| 13 | William    | Jackson   |   22 | Male   |  62.4 |
|  7 | Daniel     | Miller    |   21 | Male   |  56.7 |
+----+------------+-----------+------+--------+-------+
20 rows in set (0.00 sec)

mysql> select * from students order by age desc;
+----+------------+-----------+------+--------+-------+
| id | first_name | last_name | age  | gender | marks |
+----+------------+-----------+------+--------+-------+
|  4 | Sarah      | Jones     |   22 | Female |  67.8 |
| 27 | Sofia      | Allen     |   22 | Female |  89.7 |
|  9 | Matthew    | Anderson  |   22 | Male   |  74.5 |
| 22 | Ella       | Hall      |   22 | Female |  81.4 |
| 13 | William    | Jackson   |   22 | Male   |  62.4 |
| 18 | Charlotte  | Garcia    |   22 | Female |  88.3 |
| 16 | Mia        | Martin    |   21 | Female |  73.6 |
| 30 | Aiden      | King      |   21 | Male   |  62.8 |
| 25 | Abigail    | Walker    |   21 | Female |  71.8 |
| 21 | Henry      | Clark     |   21 | Male   |  65.9 |
| 12 | Ava        | Taylor    |   21 | Female |    95 |
|  7 | Daniel     | Miller    |   21 | Male   |  56.7 |
|  3 | Michael    | Williams  |   21 | Male   |  92.3 |
|  2 | Emily      | Johnson   |   19 | Female |    78 |
| 14 | Isabella   | White     |   19 | Female |  79.8 |
| 19 | Benjamin   | Martinez  |   19 | Male   |  77.2 |
| 10 | Olivia     | Wilson    |   19 | Female |  82.6 |
| 23 | Alexander  | Lewis     |   19 | Male   |    76 |
|  6 | Jessica    | Davis     |   19 | Female |  88.9 |
| 28 | Jackson    | Wright    |   19 | Male   |  84.6 |
+----+------------+-----------+------+--------+-------+
20 rows in set (0.00 sec)

mysql> select * from students order by age,marks desc;
+----+------------+-----------+------+--------+-------+
| id | first_name | last_name | age  | gender | marks |
+----+------------+-----------+------+--------+-------+
|  6 | Jessica    | Davis     |   19 | Female |  88.9 |
| 28 | Jackson    | Wright    |   19 | Male   |  84.6 |
| 10 | Olivia     | Wilson    |   19 | Female |  82.6 |
| 14 | Isabella   | White     |   19 | Female |  79.8 |
|  2 | Emily      | Johnson   |   19 | Female |    78 |
| 19 | Benjamin   | Martinez  |   19 | Male   |  77.2 |
| 23 | Alexander  | Lewis     |   19 | Male   |    76 |
| 12 | Ava        | Taylor    |   21 | Female |    95 |
|  3 | Michael    | Williams  |   21 | Male   |  92.3 |
| 16 | Mia        | Martin    |   21 | Female |  73.6 |
| 25 | Abigail    | Walker    |   21 | Female |  71.8 |
| 21 | Henry      | Clark     |   21 | Male   |  65.9 |
| 30 | Aiden      | King      |   21 | Male   |  62.8 |
|  7 | Daniel     | Miller    |   21 | Male   |  56.7 |
| 27 | Sofia      | Allen     |   22 | Female |  89.7 |
| 18 | Charlotte  | Garcia    |   22 | Female |  88.3 |
| 22 | Ella       | Hall      |   22 | Female |  81.4 |
|  9 | Matthew    | Anderson  |   22 | Male   |  74.5 |
|  4 | Sarah      | Jones     |   22 | Female |  67.8 |
| 13 | William    | Jackson   |   22 | Male   |  62.4 |
+----+------------+-----------+------+--------+-------+
20 rows in set (0.00 sec)

mysql> select first_name,last_name from students order by age,marks desc;
+------------+-----------+
| first_name | last_name |
+------------+-----------+
| Jessica    | Davis     |
| Jackson    | Wright    |
| Olivia     | Wilson    |
| Isabella   | White     |
| Emily      | Johnson   |
| Benjamin   | Martinez  |
| Alexander  | Lewis     |
| Ava        | Taylor    |
| Michael    | Williams  |
| Mia        | Martin    |
| Abigail    | Walker    |
| Henry      | Clark     |
| Aiden      | King      |
| Daniel     | Miller    |
| Sofia      | Allen     |
| Charlotte  | Garcia    |
| Ella       | Hall      |
| Matthew    | Anderson  |
| Sarah      | Jones     |
| William    | Jackson   |
+------------+-----------+
20 rows in set (0.00 sec)

mysql> select first_name,last_name from students where first_name like "A%" order by age,marks desc;
+------------+-----------+
| first_name | last_name |
+------------+-----------+
| Alexander  | Lewis     |
| Ava        | Taylor    |
| Abigail    | Walker    |
| Aiden      | King      |
+------------+-----------+
4 rows in set (0.00 sec)

mysql> select first_name,last_name from students where first_name like "%A" order by age,marks desc;
+------------+-----------+
| first_name | last_name |
+------------+-----------+
| Jessica    | Davis     |
| Olivia     | Wilson    |
| Isabella   | White     |
| Ava        | Taylor    |
| Mia        | Martin    |
| Sofia      | Allen     |
| Ella       | Hall      |
+------------+-----------+
7 rows in set (0.00 sec)

mysql> select first_name,last_name from students where first_name like "%A%" order by age,marks desc;
+------------+-----------+
| first_name | last_name |
+------------+-----------+
| Jessica    | Davis     |
| Jackson    | Wright    |
| Olivia     | Wilson    |
| Isabella   | White     |
| Benjamin   | Martinez  |
| Alexander  | Lewis     |
| Ava        | Taylor    |
| Michael    | Williams  |
| Mia        | Martin    |
| Abigail    | Walker    |
| Aiden      | King      |
| Daniel     | Miller    |
| Sofia      | Allen     |
| Charlotte  | Garcia    |
| Ella       | Hall      |
| Matthew    | Anderson  |
| Sarah      | Jones     |
| William    | Jackson   |
+------------+-----------+
18 rows in set (0.00 sec)

mysql> select first_name,last_name from students wHERE marks between 85 and 95;
+------------+-----------+
| first_name | last_name |
+------------+-----------+
| Michael    | Williams  |
| Jessica    | Davis     |
| Ava        | Taylor    |
| Charlotte  | Garcia    |
| Sofia      | Allen     |
+------------+-----------+
5 rows in set (0.00 sec)

mysql> select * from students wHERE marks between 85 and 95;
+----+------------+-----------+------+--------+-------+
| id | first_name | last_name | age  | gender | marks |
+----+------------+-----------+------+--------+-------+
|  3 | Michael    | Williams  |   21 | Male   |  92.3 |
|  6 | Jessica    | Davis     |   19 | Female |  88.9 |
| 12 | Ava        | Taylor    |   21 | Female |    95 |
| 18 | Charlotte  | Garcia    |   22 | Female |  88.3 |
| 27 | Sofia      | Allen     |   22 | Female |  89.7 |
+----+------------+-----------+------+--------+-------+
5 rows in set (0.00 sec)

mysql> select * from students wHERE marks between 85 and 95 order by marks;
+----+------------+-----------+------+--------+-------+
| id | first_name | last_name | age  | gender | marks |
+----+------------+-----------+------+--------+-------+
| 18 | Charlotte  | Garcia    |   22 | Female |  88.3 |
|  6 | Jessica    | Davis     |   19 | Female |  88.9 |
| 27 | Sofia      | Allen     |   22 | Female |  89.7 |
|  3 | Michael    | Williams  |   21 | Male   |  92.3 |
| 12 | Ava        | Taylor    |   21 | Female |    95 |
+----+------------+-----------+------+--------+-------+
5 rows in set (0.00 sec)

mysql> select * from students wHERE marks between 85 and 95 order by marks desc;
+----+------------+-----------+------+--------+-------+
| id | first_name | last_name | age  | gender | marks |
+----+------------+-----------+------+--------+-------+
| 12 | Ava        | Taylor    |   21 | Female |    95 |
|  3 | Michael    | Williams  |   21 | Male   |  92.3 |
| 27 | Sofia      | Allen     |   22 | Female |  89.7 |
|  6 | Jessica    | Davis     |   19 | Female |  88.9 |
| 18 | Charlotte  | Garcia    |   22 | Female |  88.3 |
+----+------------+-----------+------+--------+-------+
5 rows in set (0.01 sec)

mysql> select * from students wHERE (marks between 85 and 95) AND age=19 order by marks desc;
+----+------------+-----------+------+--------+-------+
| id | first_name | last_name | age  | gender | marks |
+----+------------+-----------+------+--------+-------+
|  6 | Jessica    | Davis     |   19 | Female |  88.9 |
+----+------------+-----------+------+--------+-------+
1 row in set (0.00 sec)

mysql> select * from students wHERE (marks between 85 and 95) or age=19 order by marks desc;
+----+------------+-----------+------+--------+-------+
| id | first_name | last_name | age  | gender | marks |
+----+------------+-----------+------+--------+-------+
| 12 | Ava        | Taylor    |   21 | Female |    95 |
|  3 | Michael    | Williams  |   21 | Male   |  92.3 |
| 27 | Sofia      | Allen     |   22 | Female |  89.7 |
|  6 | Jessica    | Davis     |   19 | Female |  88.9 |
| 18 | Charlotte  | Garcia    |   22 | Female |  88.3 |
| 28 | Jackson    | Wright    |   19 | Male   |  84.6 |
| 10 | Olivia     | Wilson    |   19 | Female |  82.6 |
| 14 | Isabella   | White     |   19 | Female |  79.8 |
|  2 | Emily      | Johnson   |   19 | Female |    78 |
| 19 | Benjamin   | Martinez  |   19 | Male   |  77.2 |
| 23 | Alexander  | Lewis     |   19 | Male   |    76 |
+----+------------+-----------+------+--------+-------+
11 rows in set (0.01 sec)

mysql> select * from students wHERE not gender="male"  order by marks desc;
+----+------------+-----------+------+--------+-------+
| id | first_name | last_name | age  | gender | marks |
+----+------------+-----------+------+--------+-------+
| 12 | Ava        | Taylor    |   21 | Female |    95 |
| 27 | Sofia      | Allen     |   22 | Female |  89.7 |
|  6 | Jessica    | Davis     |   19 | Female |  88.9 |
| 18 | Charlotte  | Garcia    |   22 | Female |  88.3 |
| 10 | Olivia     | Wilson    |   19 | Female |  82.6 |
| 22 | Ella       | Hall      |   22 | Female |  81.4 |
| 14 | Isabella   | White     |   19 | Female |  79.8 |
|  2 | Emily      | Johnson   |   19 | Female |    78 |
| 16 | Mia        | Martin    |   21 | Female |  73.6 |
| 25 | Abigail    | Walker    |   21 | Female |  71.8 |
|  4 | Sarah      | Jones     |   22 | Female |  67.8 |
+----+------------+-----------+------+--------+-------+
11 rows in set (0.00 sec)

mysql> select * from students wHERE not gender="male"  order by marks desc limit 3;
+----+------------+-----------+------+--------+-------+
| id | first_name | last_name | age  | gender | marks |
+----+------------+-----------+------+--------+-------+
| 12 | Ava        | Taylor    |   21 | Female |    95 |
| 27 | Sofia      | Allen     |   22 | Female |  89.7 |
|  6 | Jessica    | Davis     |   19 | Female |  88.9 |
+----+------------+-----------+------+--------+-------+
3 rows in set (0.00 sec)

mysql> select * from students wHERE not gender="male"  order by marks desc limit 3 offset 3;
+----+------------+-----------+------+--------+-------+
| id | first_name | last_name | age  | gender | marks |
+----+------------+-----------+------+--------+-------+
| 18 | Charlotte  | Garcia    |   22 | Female |  88.3 |
| 10 | Olivia     | Wilson    |   19 | Female |  82.6 |
| 22 | Ella       | Hall      |   22 | Female |  81.4 |
+----+------------+-----------+------+--------+-------+
3 rows in set (0.00 sec)

mysql> select * from students wHERE not gender="male"  order by marks desc limit 3 offset 6;
+----+------------+-----------+------+--------+-------+
| id | first_name | last_name | age  | gender | marks |
+----+------------+-----------+------+--------+-------+
| 14 | Isabella   | White     |   19 | Female |  79.8 |
|  2 | Emily      | Johnson   |   19 | Female |    78 |
| 16 | Mia        | Martin    |   21 | Female |  73.6 |
+----+------------+-----------+------+--------+-------+
3 rows in set (0.00 sec)

mysql> select max(marks) from students;
+------------+
| max(marks) |
+------------+
|         95 |
+------------+
1 row in set (0.02 sec)

mysql> select *  from students where max(marks);
ERROR 1111 (HY000): Invalid use of group function
mysql> select count(first_name) from students;
+-------------------+
| count(first_name) |
+-------------------+
|                20 |
+-------------------+
1 row in set (0.00 sec)

mysql> select avg(marks) from students;
+-------------------+
| avg(marks)        |
+-------------------+
| 77.46500053405762 |
+-------------------+
1 row in set (0.00 sec)

mysql> select sum(marks) from students;
+--------------------+
| sum(marks)         |
+--------------------+
| 1549.3000106811523 |
+--------------------+
1 row in set (0.00 sec)

mysql> select *,max(marks) from students;
ERROR 1140 (42000): In aggregated query without GROUP BY, expression #1 of SELECT list contains nonaggregated column 'students.students.id'; this is incompatible with sql_mode=only_full_group_by
mysql> select first_name,max(marks) from students;
ERROR 1140 (42000): In aggregated query without GROUP BY, expression #1 of SELECT list contains nonaggregated column 'students.students.first_name'; this is incompatible with sql_mode=only_full_group_by
mysql> select first_name,max(marks) from students group by id;
+------------+------------+
| first_name | max(marks) |
+------------+------------+
| Emily      |         78 |
| Michael    |       92.3 |
| Sarah      |       67.8 |
| Jessica    |       88.9 |
| Daniel     |       56.7 |
| Matthew    |       74.5 |
| Olivia     |       82.6 |
| Ava        |         95 |
| William    |       62.4 |
| Isabella   |       79.8 |
| Mia        |       73.6 |
| Charlotte  |       88.3 |
| Benjamin   |       77.2 |
| Henry      |       65.9 |
| Ella       |       81.4 |
| Alexander  |         76 |
| Abigail    |       71.8 |
| Sofia      |       89.7 |
| Jackson    |       84.6 |
| Aiden      |       62.8 |
+------------+------------+
20 rows in set (0.00 sec)

mysql> select *,max(marks) from students group by id;
+----+------------+-----------+------+--------+-------+------------+
| id | first_name | last_name | age  | gender | marks | max(marks) |
+----+------------+-----------+------+--------+-------+------------+
|  2 | Emily      | Johnson   |   19 | Female |    78 |         78 |
|  3 | Michael    | Williams  |   21 | Male   |  92.3 |       92.3 |
|  4 | Sarah      | Jones     |   22 | Female |  67.8 |       67.8 |
|  6 | Jessica    | Davis     |   19 | Female |  88.9 |       88.9 |
|  7 | Daniel     | Miller    |   21 | Male   |  56.7 |       56.7 |
|  9 | Matthew    | Anderson  |   22 | Male   |  74.5 |       74.5 |
| 10 | Olivia     | Wilson    |   19 | Female |  82.6 |       82.6 |
| 12 | Ava        | Taylor    |   21 | Female |    95 |         95 |
| 13 | William    | Jackson   |   22 | Male   |  62.4 |       62.4 |
| 14 | Isabella   | White     |   19 | Female |  79.8 |       79.8 |
| 16 | Mia        | Martin    |   21 | Female |  73.6 |       73.6 |
| 18 | Charlotte  | Garcia    |   22 | Female |  88.3 |       88.3 |
| 19 | Benjamin   | Martinez  |   19 | Male   |  77.2 |       77.2 |
| 21 | Henry      | Clark     |   21 | Male   |  65.9 |       65.9 |
| 22 | Ella       | Hall      |   22 | Female |  81.4 |       81.4 |
| 23 | Alexander  | Lewis     |   19 | Male   |    76 |         76 |
| 25 | Abigail    | Walker    |   21 | Female |  71.8 |       71.8 |
| 27 | Sofia      | Allen     |   22 | Female |  89.7 |       89.7 |
| 28 | Jackson    | Wright    |   19 | Male   |  84.6 |       84.6 |
| 30 | Aiden      | King      |   21 | Male   |  62.8 |       62.8 |
+----+------------+-----------+------+--------+-------+------------+
20 rows in set (0.00 sec)

mysql>
Editor is loading...