SQL QUERY

CHAPTER – 7
create table student (rollNo int primary key, sname varchar(20), marks float, grade
varchar(20), section varchar(20));
desc student;
+---------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| rollNo | int | NO | PRI | NULL |
| sname | varchar(20) | YES | | NULL |
| marks | float | YES | | NULL |
| grade | varchar(20) | YES | | NULL |
| section | varchar(20) | YES | | NULL |
+---------+-------------+------+-----+---------+-------+

insert into student values(101, "Ali", 32.00, "F","C"),(102, "Neha", 72.00, "B","A")(103,
"Meera", 97.00, "A","A"),(104, "Simran", 98.00, "A+","A"),(105, "Nisha", 58.00, "C","B"),
(106, "Sima", 32.00, "F","C"),(107, "annu", 61.00, "B","B"),(108, "Harshita", 81.00, "A","A")
(109, "rishita", 55.00, "C","C"),(110, "Kajal", 75.00, "B","B");
select * from student;
+--------+----------+-------+-------+---------+
| rollNo | sname | marks | grade | section |
+--------+----------+-------+-------+---------+
| 101 | Ali | 32 | F | C |
| 102 | Neha | 72 | B | A |
| 103 | Meera | 97 | A | A |
| 104 | Simran | 98 | A+ | A |
| 105 | Nisha | 58 | C | B |
| 106 | Sima | 32 | F | C |
| 107 | annu | 61 | B | B |
| 108 | Harshita | 81 | A | A |
| 109 | rishita | 55 | C | C |
| 110 | Kajal | 75 | B | B |

ORDER BY Clause:- The MYSQL ORDER BY Clause is used to sort the records in ascending or
descending order. Syntax:
SELECT expressions FROM tables
[WHERE conditions]
ORDER BY expression [ ASC | DESC ];

select * from student order by marks;

+--------+----------+-------+-------+---------+
| rollNo | sname | marks | grade | section |
+--------+----------+-------+-------+---------+
| 101 | Ali | 32 | F | C |
| 106 | Sima | 32 | F | C |
| 109 | rishita | 55 | C | C |
| 105 | Nisha | 58 | C | B |
| 107 | annu | 61 | B | B |
| 102 | Neha | 72 | B | A |
| 110 | Kajal | 75 | B | B |
| 108 | Harshita | 81 | A | A |
| 103 | Meera | 97 | A | A |
| 104 | Simran | 98 | A+ | A |
+--------+----------+-------+-------+---------+
select * from student order by marks ASC;
+--------+----------+-------+-------+---------+
| rollNo | sname | marks | grade | section |
+--------+----------+-------+-------+---------+
| 101 | Ali | 32 | F | C |
| 106 | Sima | 32 | F | C |
| 109 | rishita | 55 | C | C |
| 105 | Nisha | 58 | C | B |
| 107 | annu | 61 | B | B |
| 102 | Neha | 72 | B | A |
| 110 | Kajal | 75 | B | B |
| 108 | Harshita | 81 | A | A |
| 103 | Meera | 97 | A | A |
| 104 | Simran | 98 | A+ | A |

select * from student order by marks DESC;

+--------+----------+-------+-------+---------+
| rollNo | sname | marks | grade | section |
+--------+----------+-------+-------+---------+
| 104 | Simran | 98 | A+ | A |
| 103 | Meera | 97 | A | A |
| 108 | Harshita | 81 | A | A |
| 110 | Kajal | 75 | B | B |
| 102 | Neha | 72 | B | A |
| 107 | annu | 61 | B | B |
| 105 | Nisha | 58 | C | B |
| 109 | rishita | 55 | C | C |
| 101 | Ali | 32 | F | C |
| 106 | Sima | 32 | F | C |
+--------+----------+-------+-------+---------+

select * from student order by section ASC, marks DESC;

+--------+----------+-------+-------+---------+
| rollNo | sname | marks | grade | section |
+--------+----------+-------+-------+---------+
| 104 | Simran | 98 | A+ | A |
| 103 | Meera | 97 | A | A |
| 108 | Harshita | 81 | A | A |
| 102 | Neha | 72 | B | A |
| 110 | Kajal | 75 | B | B |
| 107 | annu | 61 | B | B |
| 105 | Nisha | 58 | C | B |
| 109 | rishita | 55 | C | C |
| 101 | Ali | 32 | F | C |
| 106 | Sima | 32 | F | C |
+--------+----------+-------+-------+---------+

select rollNo, sname, grade,section, marks*.35 from student

where marks>70
order by section ASC, marks*0.35 DESC;
+--------+----------+-------+---------+--------------------+
| rollNo | sname | grade | section | marks*.35 |
+--------+----------+-------+---------+--------------------+
| 104 | Simran | A+ | A | 34.3 |
| 103 | Meera | A | A | 33.949999999999996 |
| 108 | Harshita | A | A | 28.349999999999998 |
| 102 | Neha | B | A | 25.2 |
| 110 | Kajal | B | B | 26.25 |
+--------+----------+-------+---------+--------------------+

select rollNo, sname, grade,section, marks*.35 as Term1 from student

where marks > 70
order by section ASC, Term1 DESC;
+--------+----------+-------+---------+--------------------+
| rollNo | sname | grade | section | Term1 |
+--------+----------+-------+---------+--------------------+
| 104 | Simran | A+ | A | 34.3 |
| 103 | Meera | A | A | 33.949999999999996 |
| 108 | Harshita | A | A | 28.349999999999998 |
| 102 | Neha | B | A | 25.2 |
| 110 | Kajal | B | B | 26.25 |

AVG():- The AVG() function returns the average value of a numeric column.
select AVG(marks) "Average"
from student;
+---------+
| Average |
+---------+
| 66.1 |
+---------+

COUNT():- The COUNT() function returns the number of rows that matches a specified
criterion.
select COUNT(*) "Total" from student;
+-------+
| Total |
+-------+
| 10 |
+-------+

select COUNT(marks) "TotalMarks" from student;

+------------+
| TotalMarks |
+------------+
| 10 |
+------------+

select COUNT(DISTINCT marks) "TotalMarks" from student;

+-------+
| Total |
+-------+
| 9|

select MAX(marks) "Maximum Salary"

from student;
+----------------+
| Maximum Salary |
+----------------+
| 98 |
+----------------+
select MIN(marks) "Minimum Salary" from student;
+----------------+
| Minimum Salary |
+----------------+
| 32 |
+----------------+

SUM():- The SUM() function returns the total sum of a numeric column.
select SUM(marks) "TotalMarks" from student;
+------------+
| TotalMarks |
+------------+
| 661 |
+------------+

select SUM(marks) from student where grade="C";

+------------+
| SUM(marks) |
+------------+
| 113 |
+------------+

select SUM(marks) from student where grade="A";

+------------+
| SUM(marks) |
+------------+
| 178 |
+------------+

GROUP BY:- The GROUP BY statement is often used with aggregate functions (COUNT(),
MAX(), MIN(), SUM(), AVG()) to group the result-set by one or more columns.
select marks, count(*) from student
group by marks;
+-------+----------+
| marks | count(*) |
+-------+----------+
| 32 | 2|
| 72 | 1|
| 97 | 1|
| 98 | 1|
| 58 | 1|
| 61 | 1|
| 81 | 1|
| 55 | 1|
| 75 | 1|
+-------+----------+

select grade, count(grade) from student

group by grade;
+-------+--------------+
| grade | count(grade) |
+-------+--------------+
|F | 2|
|B | 3|
|A | 2|
| A+ | 1|
|C | 2|
+-------+--------------+

select sname, grade, count(grade) from student

group by sname, grade;
+----------+-------+--------------+
| sname | grade | count(grade) |
+----------+-------+--------------+
| Ali | F | 1|
| Neha | B | 1|
| Meera | A | 1|
| Simran | A+ | 1|
| Nisha | C | 1|
| Sima | F | 1|
| annu | B | 1|
| Harshita | A | 1|
| rishita | C | 1|
| Kajal | B | 1|
+----------+-------+--------------+

select marks, grade, count(grade) from student

group by marks, grade;
+-------+-------+--------------+
| marks | grade | count(grade) |
+-------+-------+--------------+
| 32 | F | 2|
| 72 | B | 1|
| 97 | A | 1|
| 98 | A+ | 1|
| 58 | C | 1|
| 61 | B | 1|
| 81 | A | 1|
| 55 | C | 1|
| 75 | B | 1|

HAVING Clause :- The HAVING clause was added to SQL because the WHERE keyword
cannot be used is used with GROUP BY clause. It always returns the rows where condition
is TRUE. with aggregate functions.
SELECT column_name(s) FROM table_name
WHERE condition
GROUP BY column_name(s)
HAVING condition
ORDER BY column_name(s);
select AVG(marks), sum(marks) from student
group by grade
HAVING grade = "B";
+-------------------+------------+
| AVG(marks) | sum(marks) |
+-------------------+------------+
| 69.33333333333333 | 208 |
+-------------------+------------+

select AVG(marks), sum(marks) from student

group by grade
HAVING count(*) <3;
+------------+------------+
| AVG(marks) | sum(marks) |
+------------+------------+
| 32 | 64 |
| 89 | 178 |
| 98 | 98 |
| 56.5 | 113 |
+------------+------------+

select sname, sum(marks) from student

group by rollno;
+----------+------------+
| sname | sum(marks) |
+----------+------------+
| Ali | 32 |
| Neha | 72 |
| Meera | 97 |
| Simran | 98 |
| Nisha | 58 |
| Sima | 32 |
| annu | 61 |
| Harshita | 81 |
| rishita | 55 |
| Kajal | 75 |