CBSE Class 12 Computer Science Chapter 7: Structured Query Language (SQL) NCERT Solutions

Reviewed by · Updated on

NCERT Solutions PDF Class 12 PDF

This chapter provides comprehensive NCERT Solutions for Class 12 Computer Science, focusing on Structured Query Language (SQL). Students will learn to interact with databases using SQL commands. The solutions cover essential concepts like data retrieval, filtering, sorting, and aggregation. Key SQL clauses such as SELECT, FROM, WHERE, ORDER BY, GROUP BY, and HAVING are explained through practical examples. The exercises involve writing queries based on given table structures and data, as well as interpreting the output of provided SQL queries. These solutions are designed to help students understand database management principles and prepare effectively for their exams by reinforcing practical SQL skills.

Home

Quick info

BoardCBSE
ClassClass 12
SubjectComputer Science
Session2026
LanguageEnglish
TypeNCERT Solutions
Chapter7. Structured Query Language

Chapter summary

Chapter 7 on Structured Query Language (SQL) for CBSE Class 12 Computer Science provides essential NCERT Solutions. It covers fundamental SQL operations including selecting data, filtering records using WHERE clauses, sorting results with ORDER BY, and grouping data with GROUP BY and HAVING. The solutions address both writing SQL queries and understanding their outputs based on sample tables like VEHICLE and TRAVEL. This chapter is crucial for developing database querying skills.

Learning outcomes

  • Understand the basic syntax and purpose of SQL.
  • Write SQL queries to retrieve specific data from tables.
  • Filter data using WHERE clause with various conditions.
  • Sort query results using ORDER BY clause.
  • Use aggregate functions and GROUP BY clause for data summarization.
  • Interpret the output of SQL queries based on given tables.

Topics covered

Paper topics

  • Introduction to SQL
  • Database Tables (VEHICLE, TRAVEL)
  • SELECT Statement
  • FROM Clause
  • WHERE Clause
  • ORDER BY Clause
  • GROUP BY Clause
  • HAVING Clause
  • Aggregate Functions (COUNT)
  • DISTINCT Keyword
  • Date Filtering
  • Joining Tables (Implicit)

Important topics

  • Writing SELECT queries with WHERE and ORDER BY
  • Using GROUP BY and HAVING for data summarization
  • Filtering data based on date ranges
  • Understanding table joins for combined data retrieval
  • Interpreting SQL query outputs

PDF preview

Read page by page below. PDF is streamed from the official NCERT website — no download button on this page.

Loading document …
Page of
Loading page …

Questions and Solutions

Question 1

Write SQL queries for parts (i) to (iv) and find the outputs for SQL queries (v) to (viii), which are based on the following tables:

Table: VEHICLE

VCODE VEHICLETYPE PERKM
V01 VOLVO BUS 150
V02 AC DELUXE BUS 125
V03 ORDINARY BUS 80
V05 SUV 30
V04 CAR 18

Table: TRAVEL

CNO CNAME TRAVELDATE KM VCODE NOP
101 K. Niwal 2015-12-13 200 V01 32
103 Fredrick Sym 2016-03-21 120 V03 45
105 Hitesh Jain 2016-04-23 450 V02 42
102 Ravi Anish 2016-01-13 80 V02 40
107 John Malina 2015-02-10 65 V04 2
104 Sahanubhuti 2016-01-28 90 V05 4
106 Ramesh Jaya 2016-04-06 100 V01 25

Note: PERKM is Freight Charges per kilometer. KM is kilometers Travelled. NOP is number of passengers travelled in vehicle.

  1. To display CNO, CNAME, TRAVELDATE from the table TRAVEL in descending order of CNO.
  2. To display the CNAME of all customers from the table TRAVEL who are travelling by vehicle with code V01 or V02.
  3. To display the CNO and CNAME of those customers from the table TRAVEL who travelled between '2015-12-31' and '2015-05-01'.
  4. To display all the details from table TRAVEL for the customers, who have travel distance more than 120 KM in ascending order of NOP.
Solution:

  1. To display CNO, CNAME, TRAVELDATE from the TRAVEL table in descending order of CNO, we use the SELECT statement to specify the columns and the ORDER BY clause with DESC (descending) keyword for the CNO column.

    SELECT CNO, CNAME, TRAVELDATE FROM TRAVEL ORDER BY CNO DESC;

  2. To display the CNAME of customers travelling by vehicles with codes 'V01' or 'V02', we can use the WHERE clause with the OR operator or the IN operator. The IN operator is generally more concise when checking against multiple values.

    Using OR operator:

    SELECT CNAME FROM TRAVEL WHERE VCODE = 'V01' OR VCODE = 'V02';

    Using IN operator:

    SELECT CNAME FROM TRAVEL WHERE VCODE IN ('V01', 'V02');

  3. To find customers who travelled between '2015-12-31' and '2015-05-01', we need to be careful with the date range. Assuming the intention is to find records within this period, the BETWEEN operator is suitable. Note that the order of dates in the BETWEEN clause matters for some SQL dialects, but typically it means inclusive of both start and end dates. If the intention was to find dates *after* 2015-05-01 and *before* 2015-12-31, separate conditions would be used. Based on the provided sample answer, it seems the intention was to find dates within the calendar year 2015, or a specific range. Let's interpret it as dates from '2015-05-01' up to '2015-12-31'.

    Using BETWEEN operator (assuming start date is earlier than end date):

    SELECT CNO, CNAME FROM TRAVEL WHERE TRAVELDATE BETWEEN '2015-05-01' AND '2015-12-31';

    Alternatively, using comparison operators:

    SELECT CNO, CNAME FROM TRAVEL WHERE TRAVELDATE >= '2015-05-01' AND TRAVELDATE <= '2015-12-31';

    Note: The source provided multiple equivalent queries for this part, including one with dates in reverse order ('2015-12-31' and '2015-05-01'), which might yield different results depending on the SQL implementation if not handled carefully. The most standard interpretation for a date range is to list the earlier date first.

  4. To display all details for customers who travelled more than 120 KM, sorted by the number of passengers (NOP) in ascending order, we use the SELECT * to get all columns, a WHERE clause to filter by KM, and ORDER BY NOP ASC.

    SELECT * FROM TRAVEL WHERE KM > 120 ORDER BY NOP ASC;

Question 2

Find the outputs for the following SQL queries based on the provided tables:
  1. SELECT COUNT (*), VCODE FROM TRAVEL GROUP BY VCODE HAVING COUNT (*) > 1;
  2. SELECT DISTINCT VCODE FROM TRAVEL;
  3. SELECT A.VCODE, CNAME, VEHICLETYPE FROM TRAVEL A, VEHICLE B WHERE A. VCODE = B. VCODE and KM < 90;
  4. SELECT CNAME, KM*PERKM FROM TRAVEL A, VEHICLE B WHERE A.VCODE = B.VCODE AND A. VCODE 'V05';
Solution:

  1. This query counts the number of records for each VCODE in the TRAVEL table and then filters these groups to show only those VCODEs that appear more than once. Looking at the TRAVEL table, V01 appears twice, and V02 appears twice. V03, V04, and V05 appear once.

    Output:

    COUNT(*) VCODE
    2 V01
    2 V02

  2. This query selects all unique VCODE values present in the TRAVEL table. The DISTINCT keyword ensures that each VCODE is listed only once, even if it appears multiple times in the table.

    Output:

    VCODE
    V01
    V03
    V02
    V04
    V05

  3. This query joins the TRAVEL table (aliased as A) with the VEHICLE table (aliased as B) on their common column VCODE. It then selects the VCODE from TRAVEL, the CNAME from TRAVEL, and the VEHICLETYPE from VEHICLE for records where the KM travelled is less than 90.

    Let's trace the records:

    • Travel record (107, John Malina, 2015-02-10, 65 KM, V04, 2): KM is 65 (< 90). V04 in VEHICLE is CAR. Output: V04, John Malina, CAR.
    • Travel record (104, Sahanubhuti, 2016-01-28, 90 KM, V05, 4): KM is 90 (not < 90). This record is excluded.
    • Other records have KM >= 90.

    Output:

    VCODE CNAME VEHICLETYPE
    V04 John Malina CAR

  4. This query joins the TRAVEL table (A) and VEHICLE table (B) on VCODE. It selects the CNAME and calculates the total travel cost (KM * PERKM) for records where the VCODE in the TRAVEL table is NOT 'V05'.

    Let's trace the records where VCODE is not 'V05':

    • (101, K. Niwal, ..., 200 KM, V01, ...): V01 is not 'V05'. VEHICLE V01 has PERKM 150. Cost = 200 * 150 = 30000.
    • (103, Fredrick Sym, ..., 120 KM, V03, ...): V03 is not 'V05'. VEHICLE V03 has PERKM 80. Cost = 120 * 80 = 9600.
    • (105, Hitesh Jain, ..., 450 KM, V02, ...): V02 is not 'V05'. VEHICLE V02 has PERKM 125. Cost = 450 * 125 = 56250.
    • (102, Ravi Anish, ..., 80 KM, V02, ...): V02 is not 'V05'. VEHICLE V02 has PERKM 125. Cost = 80 * 125 = 10000.
    • (107, John Malina, ..., 65 KM, V04, ...): V04 is not 'V05'. VEHICLE V04 has PERKM 18. Cost = 65 * 18 = 1170.
    • (106, Ramesh Jaya, ..., 100 KM, V01, ...): V01 is not 'V05'. VEHICLE V01 has PERKM 150. Cost = 100 * 150 = 15000.
    • The record for Sahanubhuti (V05) is excluded by the condition `A.VCODE != 'V05'`.

    Output:

    CNAME KM*PERKM
    K. Niwal 30000
    Fredrick Sym 9600
    Hitesh Jain 56250
    Ravi Anish 10000
    John Malina 1170
    Ramesh Jaya 15000

Common mistakes

  • Incorrectly specifying date formats in WHERE clauses.
  • Confusing BETWEEN operator with inclusive/exclusive ranges.
  • Errors in joining tables or specifying join conditions.
  • Misunderstanding the scope of GROUP BY and HAVING clauses.
  • Syntax errors in SQL commands (e.g., missing commas, incorrect keywords).

Revision tips

  • Practice writing various SELECT statements for different data retrieval needs.
  • Pay close attention to the conditions used in WHERE clauses, especially for dates and ranges.
  • Understand how ORDER BY affects the presentation of results.
  • Work through each example query and manually determine the output before checking the solution.
  • Redraw the sample tables and try to generate the queries yourself.

Practice MCQs

Q1. Which SQL clause is used to display data in a specific order?

Q2. What does the DISTINCT keyword do in a SELECT statement?

Q3. Which clause is used to filter groups based on a specified condition after aggregation?

Q4. In the given tables, which SQL query would display the CNAME of customers who travelled by 'V01' or 'V02' vehicles?

Q5. What is the purpose of the `PERKM` column in the `VEHICLE` table?

Frequently asked questions

What is SQL and why is it important in Computer Science?

SQL (Structured Query Language) is a standard language for managing and manipulating databases. It's crucial for retrieving, inserting, updating, and deleting data, making it a fundamental skill for database management and application development.

How do I write a query to select specific columns from a table in SQL?

You use the SELECT statement followed by the column names you want to retrieve, and then the FROM clause specifying the table name. For example: SELECT column1, column2 FROM tablename;

What is the difference between WHERE and HAVING clauses in SQL?

The WHERE clause filters individual rows before they are grouped, while the HAVING clause filters groups based on a specified condition after the GROUP BY clause has been applied.

How can I filter records based on a date range in SQL?

You can use the WHERE clause with comparison operators (>, <, >=, <=) or the BETWEEN operator. For example: WHERE TravelDate >= '2015-05-01' AND TravelDate <= '2015-12-31' or WHERE TravelDate BETWEEN '2015-05-01' AND '2015-12-31'.

What does the DISTINCT keyword do in SQL?

The DISTINCT keyword is used in a SELECT statement to eliminate duplicate rows from the result set, returning only unique values for the specified columns.

How do these NCERT solutions help with exam preparation?

These solutions provide clear, step-by-step explanations for common SQL queries and their outputs, reinforcing concepts and helping students practice problem-solving for exams.

Important links

Content reviewed by the NCERT Help team. Editorial Team and update policy

NCERT Solutions PDF PDF on NCERT Help. URL unchanged for search indexing.