Day 57: SQL Queries – Select, Where, and Group By Clauses | Class 11, Chapter 6 - BSEB Class 11-12 Computer Science: 100-Day Bilingual Guide with MCQs, Solutions and Questions

Day 57 – SQL Queries: Select, Where, and Group By

Class 11 Computer Science – Chapter 6

Introduction to SQL Queries (SQL Queries का परिचय)

SQL queries are used to interact with the database to retrieve, filter, and aggregate data. Commonly used clauses include SELECT, WHERE, and GROUP BY.
SQL क्वेरीज का उपयोग डेटाबेस के साथ बातचीत करने, डेटा पुनः प्राप्त करने, फ़िल्टर करने और संक्षेप करने के लिए किया जाता है। सामान्य रूप से उपयोग किए जाने वाले क्लॉज में SELECT, WHERE, और GROUP BY शामिल हैं।

1. SELECT Clause (SELECT क्लॉज)

The SELECT clause is used to retrieve specific data from a table.
SELECT क्लॉज का उपयोग टेबल से विशिष्ट डेटा पुनः प्राप्त करने के लिए किया जाता है।

Syntax:

SELECT column1, column2, ...
FROM table_name;

Example:

SELECT Name, Age
FROM Students;

2. WHERE Clause (WHERE क्लॉज)

The WHERE clause is used to filter data based on specific conditions.
WHERE क्लॉज का उपयोग विशिष्ट शर्तों के आधार पर डेटा फ़िल्टर करने के लिए किया जाता है।

Syntax:

SELECT column1, column2, ...
FROM table_name
WHERE condition;

Example:

SELECT Name, Grade
FROM Students
WHERE Age > 18;

3. GROUP BY Clause (GROUP BY क्लॉज)

The GROUP BY clause is used to group rows that have the same values in specified columns.
GROUP BY क्लॉज का उपयोग पंक्तियों को एकत्रित करने के लिए किया जाता है जिनके पास निर्दिष्ट कॉलम में समान मान होते हैं।

Syntax:

SELECT column1, COUNT(*)
FROM table_name
GROUP BY column1;

Example:

SELECT Grade, COUNT(*)
FROM Students
GROUP BY Grade;

Examples of SQL Queries (SQL Queries के उदाहरण)

Example Table: Students

| StudentID | Name       | Age | Grade |
|-----------|------------|-----|-------|
| 1         | Alice      | 18  | A     |
| 2         | Bob        | 19  | B     |
| 3         | Charlie    | 18  | A     |
| 4         | Diana      | 20  | C     |

Example Queries:

1. Retrieve all student names:

SELECT Name FROM Students;
    

2. Retrieve students with age greater than 18:

SELECT Name, Age FROM Students WHERE Age > 18;
    

3. Count students in each grade:

SELECT Grade, COUNT(*) FROM Students GROUP BY Grade;
    

Advantages of SQL Queries (SQL Queries के लाभ)

• Efficiently retrieves data from large datasets.
  बड़े डेटा सेट से कुशलता से डेटा पुनः प्राप्त करता है।
• Allows flexible filtering and grouping of data.
  डेटा को फ़िल्टर करने और समूहित करने की लचीलापन प्रदान करता है।
• Supports complex conditions and aggregations.
  जटिल शर्तों और समेकनों का समर्थन करता है।

Practice Questions

Multiple Choice Questions (MCQs)

1. What is the purpose of the SELECT clause?
   (a) To update data | (b) To delete data | (c) To retrieve data | (d) None
2. Which clause filters data in SQL?
   (a) SELECT | (b) WHERE | (c) GROUP BY | (d) None
3. What does the GROUP BY clause do?
   (a) Sorts data | (b) Filters data | (c) Groups rows | (d) None
4. Which query retrieves students with grade A?
   

   SELECT Name
   FROM Students
   WHERE Grade = 'A';
           

   (a) SELECT Name FROM Students; | (b) SELECT Grade FROM Students; | (c) SELECT Name FROM Students WHERE Grade = 'A'; | (d) None
5. What will this query do?
   

   SELECT Age, COUNT(*)
   FROM Students
   GROUP BY Age;
           

   (a) Counts students by age | (b) Deletes students by age | (c) Updates student data | (d) None
6. Which SQL command is used to count rows?
   (a) COUNT | (b) SUM | (c) AVG | (d) None
7. What is the syntax to retrieve all columns in a table?
   (a) SELECT * FROM table_name; | (b) SELECT columns FROM table_name; | (c) SELECT ALL FROM table_name; | (d) None
8. Which SQL clause combines rows with the same values?
   (a) GROUP BY | (b) SELECT | (c) WHERE | (d) None
9. What does the WHERE clause require?
   (a) A condition | (b) A table | (c) A group | (d) None
10. What is the result of this query?
    

    SELECT Grade, COUNT(*)
    FROM Students
    GROUP BY Grade;
            

    (a) Counts students by grade | (b) Deletes students by grade | (c) Filters students by grade | (d) None

Answers to MCQs:

1: (c), 2: (b), 3: (c), 4: (c), 5: (a), 6: (a), 7: (a), 8: (a), 9: (a), 10: (a)

Short Answer Questions

1. What is the purpose of the SELECT clause in SQL?
   Answer: The SELECT clause retrieves specific data from a table.
2. Write a query to retrieve students with age greater than 18.
   Answer:

   SELECT Name, Age
   FROM Students
   WHERE Age > 18;
   

3. What does the GROUP BY clause do?
   Answer: It groups rows that have the same values in specified columns.
4. Write a query to count students in each grade.
   Answer:

   SELECT Grade, COUNT(*)
   FROM Students
   GROUP BY Grade;
   

5. What is the syntax for the WHERE clause in SQL?
   Answer:

   SELECT column1, column2
   FROM table_name
   WHERE condition;
   

Long Answer Questions

1. Explain the SELECT, WHERE, and GROUP BY clauses with examples.
2. Discuss the importance of SQL queries in database management.
3. Write queries to retrieve and group data from a table.