SQL Lecture 2

Creating database and storing details

-- create database nxxm301;

-- use nxxm301;

CREATE TABLE Students( id int primary key, name varchar(100), email varchar(100), score float, place varchar(100), courseID int );

INSERT INTO Students (name, email, id, score, place,courseID) VALUES ('John Doe', 'johndoe@example.com',1,85.5,'Delhi',1), ('Jane Smith', 'janesmith@example.com',2,92.0,'Mumbai',2), ('Alex Johnson', 'alexjohnson@example.com',3,78.9,'Bangalore',1), ('Sarah Davis','sarahdavis@example.com',4,95.2,'Delhi',1), ('Michael Brown','michaelbrown@example.com',5,88.7,'Mumbai',2), ('Emily Wilson','emilywilson@example.com',6,91.3,'Bangalore',1), ('Davis Lee','davidlee@example.com',7,82.4,'Delhi',2), ('Olivia Taylor','oliviataylor@example.com',8,79.1,'Mumbai',2), ('William Martinez','williammartinez@example.com',9,87.6,'Bangalore',1), ('Sophia Anderson','sophiaanderson@example.com',10,93.8,'Delhi',2);

count is a aggregator method

current table data

Q1. count how many students are there in the table

You can also put id here (anything unique you can put at the place of star)
this way is also correct

Q2. Find the Average score in this table.

we can also write the same thing as , the difference is in the result we will see

Different aggregator methods.
-- SUM, MIN, MAX

Q3. Find the sum of score present in students' table

Q4. Find the minimum value of the score in students' table

Q5. Find the maximum value of the score in the students' table

GROUP BY
Q6. Find Unique places in the table

Q7. count the number of students in each place

we can also use this query

select place, COUNT(*) as No_OF_Students from students group by place;

Q8. Find the average score of each place

Q9. Find the places where the average is > 86

In SQL while writing a query we have to follow some orders
here is the order

These are only applied while doing Read operation

-- This order we should not break while writing SQL query

-- ORDER IN SQL
-- 1. Select
-- 2. where
-- 3. Group By
-- 4. Having
-- 5. ORDER BY

Important question

Difference between Where and having while writing SQL query

where and having in SQL both of them are the same thing. Both of them are used for filtering purposes.

so when should we use where clause and when we should use having

when filtering happens before group by (group by is used for getting unique data) in that case, we have to use where .

when filtering happens after group by in that case we have to use Having.