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), 
ADDRESS 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 
WHERE ADDRESS =’NOIDA’ 

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 
SET STUDENT_NAME = ’RITA’, ADDRESS=’DELHI’ 
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 

My Personal Notes arrow_drop_up

Save