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

The `DELETE` command is a Data Manipulation Language (DML) command used to remove one or more rows from a table. If used without a `WHERE` clause, it removes all rows but leaves the table structure intact. The `DROP TABLE` command, on the other hand, is a Data Definition Language (DDL) command that completely removes the table's structure, including all its data and indexes, from the database.

Wildcard characters are used in SQL with the `LIKE` operator to search for values that match a specified pattern within a column. They allow for flexible pattern matching when the exact value is unknown. The two primary wildcard operators are:

• %: Represents zero, one, or multiple characters.
• _: Represents a single character.

To add a new column to an existing table, the `ALTER TABLE` command is used. The specific command to add the `total_price` column to the `product` table is:

$$ALTER\; TABLE\; product\; ADD\; total\_price\; numeric(10,\; 2);$$

The `ALTER TABLE` command is used to add a new column to an existing table. To add the `price` column with the specified data type to the `customer` table, the following command should be used:

$$ALTER\; TABLE\; customer\; ADD\; price\; numeric(10,\; 2);$$

To remove all rows from a table while preserving its structure, the `DELETE` command is used. The specific command to remove all data from the `BANK` table is:

$$DELETE\; FROM\; BANK;$$

The problem with Sonal's query is the incorrect use of wildcard characters. The characters `$` and `?` are not standard SQL wildcards. The correct wildcards to use are `_` for a single character and `%` for zero or more characters. To find names where the third character is '0', we need two characters before '0' and any characters after it. The corrected query is:

$$SELECT\; name\; FROM\; teacher\; WHERE\; name\; LIKE\; \text{'}\_\_0\%\text{'};$$

1. Query: Select Designation, Count(*) From Admin Group By Designation Having Count(*) <2;

   Explanation: This query counts the number of entries for each designation in the `Admin` table and then filters to show only those designations that appear less than 2 times. In the given `Admin` table, 'VICE PRINCIPAL' appears once and 'COORDINATOR' appears once. Both counts are less than 2. Output:

   DESIGNATION     COUNT(*)
   VICE PRINCIPAL  1
   COORDINATOR     1

2. Query: SELECT max (EXPERIENCE) FROM SCHOOL;

   Explanation: This query finds the maximum value in the `EXPERIENCE` column of the `SCHOOL` table. Looking at the `EXPERIENCE` column (10, 12, 5, 15, 3, 5, 16), the highest value is 16. Output:

   MAX(EXPERIENCE)
   16

3. Query: SELECT TEACHERNAME FROM SCHOOL WHERE EXPERIENCE >12 ORDER BY TEACHER NAME;

   Explanation: This query selects the names of teachers from the `SCHOOL` table where their `EXPERIENCE` is greater than 12. The teachers with experience greater than 12 are YASHRAJ (15) and UMESH (16). The results are then ordered alphabetically by teacher name. Output:

   TEACHERNAME
   UMESH
   YASHRAJ

4. Query: SELECT COUNT (*), GENDER FROM ADMIN GROUP BY GENDER;

   Explanation: This query counts the number of records for each gender present in the `Admin` table. There is one entry for 'MALE' and one entry for 'FEMALE'. Output:

   GENDER  COUNT(*)
   MALE    1
   FEMALE  1