# 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`](mailto:johndoe@example.com)`',1,85.5,'Delhi',1), ('Jane Smith', '`[`janesmith@example.com`](mailto:janesmith@example.com)`',2,92.0,'Mumbai',2), ('Alex Johnson', '`[`alexjohnson@example.com`](mailto:alexjohnson@example.com)`',3,78.9,'Bangalore',1), ('Sarah Davis','`[`sarahdavis@example.com`](mailto:sarahdavis@example.com)`',4,95.2,'Delhi',1), ('Michael Brown','`[`michaelbrown@example.com`](mailto:michaelbrown@example.com)`',5,88.7,'Mumbai',2), ('Emily Wilson','`[`emilywilson@example.com`](mailto:emilywilson@example.com)`',6,91.3,'Bangalore',1), ('Davis Lee','`[`davidlee@example.com`](mailto:davidlee@example.com)`',7,82.4,'Delhi',2), ('Olivia Taylor','`[`oliviataylor@example.com`](mailto:oliviataylor@example.com)`',8,79.1,'Mumbai',2), ('William Martinez','`[`williammartinez@example.com`](mailto:williammartinez@example.com)`',9,87.6,'Bangalore',1), ('Sophia Anderson','`[`sophiaanderson@example.com`](mailto:sophiaanderson@example.com)`',10,93.8,'Delhi',2);`

count is a aggregator method

![](https://cdn.hashnode.com/res/hashnode/image/upload/v1710786244988/b0ab9be1-1d00-45bf-b4fd-e81f559022cc.png align="center")

current table data

![](https://cdn.hashnode.com/res/hashnode/image/upload/v1710787878113/ca1a3151-2c51-42fb-84ef-e6da4c5e86ef.png align="center")

  

**Q1. count how many students are there in the table**

![](https://cdn.hashnode.com/res/hashnode/image/upload/v1710786586281/a9255a75-4dd4-49cd-9d43-260dc0b9a079.png align="center")

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

![](https://cdn.hashnode.com/res/hashnode/image/upload/v1710786695799/20c5aba9-581b-4382-a21b-383d98809025.png align="center")

**  
Q2. Find the Average score in this table.**

![](https://cdn.hashnode.com/res/hashnode/image/upload/v1710786976827/4e42aeb6-b326-4da8-9593-9ac86c0c6763.png align="center")

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

![](https://cdn.hashnode.com/res/hashnode/image/upload/v1710787046202/d88c7e21-c853-4aa7-96a5-040a2161f619.png align="center")

Different aggregator methods.  
\-- SUM, MIN, MAX

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

![](https://cdn.hashnode.com/res/hashnode/image/upload/v1710787280242/c09062b8-b305-4ef9-9c99-86e1c128d3c6.png align="center")

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

![](https://cdn.hashnode.com/res/hashnode/image/upload/v1710787399709/18ffde09-73f1-4711-b2d3-47a002f9508e.png align="center")

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

![](https://cdn.hashnode.com/res/hashnode/image/upload/v1710787451105/bc2128b3-933f-4cfa-8d44-e7457046824b.png align="center")

**GROUP BY  
Q6. Find Unique places in the table**

![](https://cdn.hashnode.com/res/hashnode/image/upload/v1710787818562/6a6db276-517a-4c29-9409-1341eae33650.png align="center")

**Q7. count the number of students in each place**

![](https://cdn.hashnode.com/res/hashnode/image/upload/v1710788709357/45db8438-6c05-49e1-a981-f9bc7dd029ce.png align="center")

  
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**

![](https://cdn.hashnode.com/res/hashnode/image/upload/v1710790463874/f662993a-713a-479f-a3c3-cdd2a89c62bc.png align="center")

**Q9. Find the places where the average is > 86**

![](https://cdn.hashnode.com/res/hashnode/image/upload/v1710791683731/dce9449b-a894-42a6-bb40-fee930e06b19.png align="center")

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.`