# SQL query using COUNT and HAVING clause

Consider a table STUDENT having the following schema:

`STUDENT (Student_id, Student_Name, Address, Marks) `

Student_id is the primary column of STUDENT table. Let first create the table structure with CREATE Command in SQL:

```CREATE TABLE STUDENT
(STUDENT_ID NUMBER (4),
STUDENT_NAME VARCHAR2 (20),
MARKS NUMBER (3),
PRIMARY KEY (STUDENT_ID));```

Now, insert values into the table using INSERT INTO Command in SQL:

```INSERT INTO STUDENT
VALUES (100, â€˜PUJAâ€™, â€™NOIDAâ€™, 10);

INSERT INTO STUDENT
VALUES (101, â€˜SUDOâ€™, â€™PUNEâ€™, 30);

INSERT INTO STUDENT
VALUES (102, â€˜BHALUâ€™, â€™NASHIKâ€™, 40);

INSERT INTO STUDENT
VALUES (103, â€˜CHETENAâ€™, â€™NOIDAâ€™, 20);

INSERT INTO STUDENT
VALUES (104, â€˜MOMOâ€™, â€™NOIDAâ€™, 40);```

Now display the content of STUDENT table:

```SELECT *
FROM STUDENT;```
```Student_id    Student_Name    Address    Marks
------------------------------------------------
100            PUJA            NOIDA      10
101            SUDO            PUNE       30
102            BHALU           NASHIK     40
103            CHETENA         NOIDA      20
104            MOMO            NOIDA      40```

Query-1: Print the marks and number of student having marks more than the average marks of student from NOIDA city. Explanation: To get the average marks of student from NOIDA city we use this query:

```SELECT AVG(MARKS)
FROM STUDENT

We use this above sub query using GROUP BY and HAVING clause :

```SELECT MARKS, COUNT (DISTINCT STUDENT_ID)
FROM STUDENT
GROUP BY MARKS
HAVING MARKS > (SELECT AVG(MARKS)
FROM STUDENT
WHERE ADDRESS = â€™NOIDAâ€™ ); ```

In the above query we use GROUP BY MARKS means it cluster the rows with same Marks and we also use SELECT MARKS, COUNT(DISTINCT STUDENT_ID) which prints the Marks of each cluster and the count of rows of respective clusters i.e.,

```MARKS    COUNT
10           1
20           1
30           1
40           2 ```

After that we use HAVING MARKS > (SELECT AVG(MARKS) FROM STUDENT WHERE ADDRESS =â€™NOIDAâ€™), which is used to filter the result with condition that marks must be greater than the avg marks of student from Noida city i.e., more than

```(10+20+40) / 3
= 23.3 ```

Output:

```MARKS       COUNT (DISTINCT STUDENT_ID)
30             1
40             2 ```

Query-2: Display the Names and Addresses of the students whose nameâ€™s second letter is U. Explanation: For matching the pattern of the STUDENT_NAME field we used LIKE string comparison operator with two reserved character % and _ . % replaces an arbitrary number of characters, and ‘_’ replaces a single arbitrary character. Here, we need to compare the second letter of STUDENT_NAME thus we use the pattern â€˜_U%â€™.

```SELECT Student_Name, Address
FROM STUDENT
WHERE STUDENT_NAME LIKE â€˜_U%â€™ ```

Output:

```STUDENT_NAME  ADDRESS
PUJA            NOIDA
SUDO            PUNE ```

Query-3: Print the details of the student obtaining highest marks (if there is more than one student getting highest marks then highest will be according to the alphabetical order of their names). Explanation: To get the highest marks from the MARKS field we use the MAX command i.e.,

```SELECT MAX(MARKS)
FROM STUDENT; ```

We use the above sub-query which returns â€˜40â€™ and it will be used with WHERE command. To arrange according to alphabetical order of STUDENT_NAME field we used ORDER BY clause and for getting the top row, LIMIT 1 will be used. Combining all these:

```SELECT *
FROM STUDENT
WHERE MARKS = (SELECT MAX (MARKS)
FROM STUDENT)
ORDER BY STUDENT_NAME LIMIT 1;  ```

Output:

```Student_id    Student_Name    Address     Marks
102            BHALU            NASHIK     40 ```

Query-4: Change the name and address of the student with ID 103 to RITA and DELHI respectively. Explanation: To change the value of any attributes we will use UPDATE command with SET clause to specify their new values.

```UPDATE STUDENT
WHERE STUDENT_ID=103 ; ```

Output:

`1 row updated `

To see the changes we will use,

```SELECT *
FROM STUDENT; ```

Output:

```Student_id    Student_Name    Address      Marks
100            PUJA               NOIDA       10
101            SUDO               PUNE        30
102            BHALU              NASHIK      40
103            RITA               DELHI       20
104            MOMO               NOIDA       40 ```

Query-5: DELETE the details from the STUDENT table those are getting lowest mark. Explanation: To find the lowest mark we will use,

```SELECT MIN(MARKS)
FROM STUDENT; ```

It will return â€˜10â€™ as a lowest marks. To delete rows we will use DELETE command with WHERE command for specify the condition.

```DELETE FROM STUDENT
WHERE MARKS = (SELECT MIN(MARKS)
FROM STUDENT); ```

Output:

`1 row affected `

To see the changes we will use,

```SELECT *
FROM STUDENT; ```

Output:

```Student_id    Student_Name    Address      Marks
101            SUDO               PUNE        30
102            BHALU              NASHIK      40
103            RITA               DELHI       20
104            MOMO               NOIDA       40 ```

Previous
Next