Untitled
plain_text
a month ago
18 kB
0
Indexable
Never
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>