Q: Question 4(c)Consider the following table named "GYM" with details about fitness items being sold in the store. Write command of SQL for (i) to (iv).Table : GYMICODEINAMEPRICEBRANDNAMEG101Power Fit Exerciser20000Power GymeaG102Aquafit Hand Grip1800ReliableG103Cycle Bike14000EcobikeG104Protoner Extreme Gym30000CoscoreG105Message Belt5000Message ExpertG106Cross Trainer13000GTC Fitness(i) To display the names of all the items whose name starts with "A".(ii) To display ICODEs and INAMEs of all items, whose Brandname is Reliable or Coscore.(iii) To change the Brandname to "Fit Trend India" of the item, whose ICODE as "G101".(iv) Add a new row for new item in GYM with the details :"G107", "Vibro exerciser", 21000, "GTCFitness"

Q: Question 5(a)Mr. James created a table CLIENT with 2 rows and 4 columns. He added 2 more rows to it and deleted one column. What is the Cardinality and Degree of the Table CLIENT ?

For the table CLIENT, the cardinality is 4 and degree is 3.Explanation — Initially, the table CLIENT created by Mr. James had 2 rows and 4 columns, with a cardinality of 2 and a degree of 4. After modifying the table by adding 2 rows and deleting 1 column, the new cardinality of the table CLIENT is 4 and the degree is 3.

Q: Question 5(b)Consider the following table FITNESS with details about fitness products being sold in the store. Write command of SQL for (i) to (iv).Table : FITNESSPCODEPNAMEPRICEManufacturerP1Treadmill21000CoscoreP2Bike20000AoneP3Cross Trainer14000ReliableP4Multi Gym34000CoscoreP5Massage Chair5500RegroseneP6Belly Vibrator Belt6500Ambawya(i) To display the names of all the products with price more than 20000.(ii) To display the names of all products by the manufacturer "Aone".(iii) To change the price data of all the products by applying 25% discount reduction.(iv) To add a new row for product with the details :"P7", "Vibro Exerciser", 28000, "Aone".

Question 1

Question 4Assertion. The PRIMARY KEY can be defined only once in the CREATE TABLE command.Reason. If the PRIMARY KEY is a composite key, then it is not defined with the individual columns but at the end of the table definition as a table constraint.Both A and R are true and R is the correct explanation of A.Both A and R are true but R is not the correct explanation of A.A is true but R is false.A is false but R is true.

Accepted Answer

Both A and R are true and R is the correct explanation of A.ExplanationIn SQL, when we define a table using the CREATE TABLE command, we can only specify one PRIMARY KEY constraint. This PRIMARY KEY can consist of a single column or multiple columns (composite key). When a PRIMARY KEY is a composite key (consisting of multiple columns), it's not defined with each individual column but rather as a table constraint at the end of the table definition.

Question 2

Question 5Assertion. INSERT, UPDATE, DELETE, SELECT are the DML commands.Reason. The DML commands manipulate the data stored in the database tables.Both A and R are true and R is the correct explanation of A.Both A and R are true but R is not the correct explanation of A.A is true but R is false.A is false but R is true.

Accepted Answer

Both A and R are true and R is the correct explanation of A.ExplanationINSERT, UPDATE, DELETE, and SELECT are the DML (Data Manipulation Language) commands in SQL. These commands manipulate the data stored in the database tables. INSERT adds new records, UPDATE modifies existing records, DELETE removes records, and SELECT retrieves data based on specified criteria.

Question 3

Question 2Differentiate between DDL and DML commands.

Accepted Answer

Data Definition Language (DDL)Data Manipulation Language (DML)DDL provides a set of definitions to specify the storage structure and access methods used by the database system.DML is a language that enables users to access or manipulate data as organized by the appropriate data model.DDL commands are used to perform tasks such as creating, altering, and dropping schema objects. They are also used to grant and revoke privileges and roles, as well as for maintenance commands related to tables.DML commands are used to retrieve, insert, delete, modify data stored in the database.Examples of DDL commands are CREATE, ALTER, DROP, GRANT, ANALYZE etc.Examples of DML commands are INSERT, UPDATE, DELETE, SELECT etc.

Question 4

Question 3Kabir has created the following table named exam :RegNoNameSubjectMarks1SanyaComputer Science1982SanchayIP1003VineshCS904SnehaIP995AkshitaIP100Help him in writing SQL queries to perform the following tasks :(i) Insert a new record in the table having following values : [6, 'Khushi', 'CS', 85](ii) To change the value "IP" to "Informatics Practices" in Subject column.(iii) To remove the records of those students whose marks are less than 30.(iv) To add a new column Grade of suitable datatype.(v) To display records of "Informatics Practices" subject.

Accepted Answer

(i)INSERT INTO exam(RegNo, Name, Subject, Marks)
VALUES(6, 'Khushi', 'CS', 85);(ii)UPDATE exam 
SET Subject = 'Informatics Practices' 
WHERE Subject = 'IP';(iii)DELETE FROM EXAM
WHERE MARKS < 30 ;(iv)ALTER TABLE EXAM
ADD COLUMN (Grade VARCHAR(1));(v)SELECT * FROM EXAM
WHERE Subject = "Informatics Practices";

Question 5

Question 4(c)Consider the following table named "GYM" with details about fitness items being sold in the store. Write command of SQL for (i) to (iv).Table : GYMICODEINAMEPRICEBRANDNAMEG101Power Fit Exerciser20000Power GymeaG102Aquafit Hand Grip1800ReliableG103Cycle Bike14000EcobikeG104Protoner Extreme Gym30000CoscoreG105Message Belt5000Message ExpertG106Cross Trainer13000GTC Fitness(i) To display the names of all the items whose name starts with "A".(ii) To display ICODEs and INAMEs of all items, whose Brandname is Reliable or Coscore.(iii) To change the Brandname to "Fit Trend India" of the item, whose ICODE as "G101".(iv) Add a new row for new item in GYM with the details :"G107", "Vibro exerciser", 21000, "GTCFitness"

Accepted Answer

(i)SELECT INAME 
FROM GYM 
WHERE INAME LIKE 'A%';Output+-------------------+
| INAME             |
+-------------------+
| Aquafit Hand Grip |
+-------------------+
(ii)SELECT ICODE, INAME 
FROM GYM
WHERE BRANDNAME = "Reliable" OR BRANDNAME = "Coscore";Output+-------+----------------------+
| ICODE | INAME                |
+-------+----------------------+
| G102  | Aquafit Hand Grip    |
| G104  | Protoner Extreme Gym |
+-------+----------------------+
(iii)UPDATE GYM
SET BRANDNAME = 'Fit Trend India'
WHERE ICODE = 'G101';(iv)INSERT INTO GYM 
VALUES("G107", "Vibro exerciser", 21000, "GTCFitness");

Question 6

Question 5(a)Mr. James created a table CLIENT with 2 rows and 4 columns. He added 2 more rows to it and deleted one column. What is the Cardinality and Degree of the Table CLIENT ?

Accepted Answer

For the table CLIENT, the cardinality is 4 and degree is 3.Explanation — Initially, the table CLIENT created by Mr. James had 2 rows and 4 columns, with a cardinality of 2 and a degree of 4. After modifying the table by adding 2 rows and deleting 1 column, the new cardinality of the table CLIENT is 4 and the degree is 3.

Question 7

Question 5(b)Consider the following table FITNESS with details about fitness products being sold in the store. Write command of SQL for (i) to (iv).Table : FITNESSPCODEPNAMEPRICEManufacturerP1Treadmill21000CoscoreP2Bike20000AoneP3Cross Trainer14000ReliableP4Multi Gym34000CoscoreP5Massage Chair5500RegroseneP6Belly Vibrator Belt6500Ambawya(i) To display the names of all the products with price more than 20000.(ii) To display the names of all products by the manufacturer "Aone".(iii) To change the price data of all the products by applying 25% discount reduction.(iv) To add a new row for product with the details :"P7", "Vibro Exerciser", 28000, "Aone".

Accepted Answer

Question 8

Question 6Write SQL commands for the following on the basis of given table CLUB.Table : CLUBCOACH_IDCOACHNAMEAGESPORTSDATOFAPPPAYSEX1KUKREJA35KARATE27/03/19961000M2RAVINA34KARATE20/01/19981200F3KARAN34SQUASH19/02/19982000M4TARUN33BASKETBALL01/01/19981500M5ZUBIN36SWIMMING12/01/1998750M6KETAKI36SWIMMING24/02/1998800F7ANKITA39SQUASH20/02/19982200F8ZAREEN37KARATE22/02/19981100F9KUSH41SWIMMING13/01/1998900M10SHAILYA37BASKETBALL19/02/19981700M(a) To show all information about the swimming coaches in the club.(b) To list names of all coaches with their date of appointment (DATOFAPP) in descending order.(c) To display a report, showing coachname, pay, age and bonus (15% of pay) for all the coaches.

Accepted Answer

(a)SELECT *
FROM CLUB 
WHERE SPORTS = 'SWIMMING' ;Output+----------+-----------+-----+----------+-----+-----+------------+
| COACH_ID | COACHNAME | AGE | SPORTS   | PAY | SEX | DATOFAPP   |
+----------+-----------+-----+----------+-----+-----+------------+
|        5 | ZUBIN     |  36 | SWIMMING | 750 | M   | 1998-01-12 |
|        6 | KETAKI    |  36 | SWIMMING | 800 | F   | 1998-02-24 |
|        9 | KUSH      |  41 | SWIMMING | 900 | M   | 1998-01-13 |
+----------+-----------+-----+----------+-----+-----+------------+
(b)SELECT COACHNAME, DATOFAPP
FROM CLUB
ORDER BY DATOFAPP DESC ;Output+-----------+------------+
| COACHNAME | DATOFAPP   |
+-----------+------------+
| KETAKI    | 1998-02-24 |
| ZAREEN    | 1998-02-22 |
| ANKITA    | 1998-02-20 |
| KARAN     | 1998-02-19 |
| SHAILYA   | 1998-02-19 |
| RAVINA    | 1998-01-20 |
| KUSH      | 1998-01-13 |
| ZUBIN     | 1998-01-12 |
| TARUN     | 1998-01-01 |
| KUKREJA   | 1996-03-27 |
+-----------+------------+
(c)SELECT COACHNAME, PAY, AGE, (PAY * 0.15) AS BONUS  
FROM CLUB ;Output+-----------+------+-----+--------+
| COACHNAME | PAY  | AGE | BONUS  |
+-----------+------+-----+--------+
| KUKREJA   | 1000 |  35 | 150.00 |
| RAVINA    | 1200 |  34 | 180.00 |
| KARAN     | 2000 |  34 | 300.00 |
| TARUN     | 1500 |  33 | 225.00 |
| ZUBIN     |  750 |  36 | 112.50 |
| KETAKI    |  800 |  36 | 120.00 |
| ANKITA    | 2200 |  39 | 330.00 |
| ZAREEN    | 1100 |  37 | 165.00 |
| KUSH      |  900 |  41 | 135.00 |
| SHAILYA   | 1700 |  37 | 255.00 |
+-----------+------+-----+--------+

Question 9

Question 7Write SQL commands for the following on the basis of given table STUDENT1.Table : STUDENT1No.NameStipendStreamAvgMarkGradeClass1Karan400.00Medical78.5B12B2Divakar450.00Commerce89.2A11C3Divya300.00Commerce68.6C12C4Arun350.00Humanities73.1B12C5Sabina500.00Nonmedical90.6A11A6John400.00Medical75.4B12B7Robert250.00Humanities64.4C11A8Rubina450.00Nonmedical88.5A12A9Vikas500.00Nonmedical92.0A12A10Mohan300.00Commerce67.5C12C(a) Select all the Nonmedical stream students from STUDENT1.(b) List the names of those students who are in class 12 sorted by Stipend.(c) List all students sorted by AvgMark in descending order.

Accepted Answer

(a)SELECT *
FROM STUDENT1
WHERE Stream = 'Nonmedical' ;Output+-----+--------+---------+------------+---------+-------+-------+
| No. | Name   | Stipend | Stream     | AvgMark | Grade | Class |
+-----+--------+---------+------------+---------+-------+-------+
|   5 | Sabina |     500 | Nonmedical | 90.6    | A     | 11A   |
|   8 | Rubina |     450 | Nonmedical | 88.5    | A     | 12A   |
|   9 | Vikas  |     500 | Nonmedical | 92.0    | A     | 12A   |
+-----+--------+---------+------------+---------+-------+-------+
(b)SELECT Name 
FROM STUDENT1
WHERE Class LIKE '12%'
ORDER BY Stipend ;Output+--------+
| Name   |
+--------+
| Divya  |
| Mohan  |
| Arun   |
| Karan  |
| John   |
| Rubina |
| Vikas  |
+--------+
(c)SELECT *
FROM STUDENT1
ORDER BY AvgMark DESC ;Output+-----+---------+---------+------------+---------+-------+-------+
| No. | Name    | Stipend | Stream     | AvgMark | Grade | Class |
+-----+---------+---------+------------+---------+-------+-------+
|   9 | Vikas   |     500 | Nonmedical | 92.0    | A     | 12A   |
|   5 | Sabina  |     500 | Nonmedical | 90.6    | A     | 11A   |
|   2 | Divakar |     450 | Commerce   | 89.2    | A     | 11C   |
|   8 | Rubina  |     450 | Nonmedical | 88.5    | A     | 12A   |
|   1 | Karan   |     400 | Medical    | 78.5    | B     | 12B   |
|   6 | John    |     400 | Medical    | 75.4    | B     | 12B   |
|   4 | Arun    |     350 | Humanities | 73.1    | B     | 12C   |
|   3 | Divya   |     300 | Commerce   | 68.6    | C     | 12C   |
|  10 | Mohan   |     300 | Commerce   | 67.5    | C     | 12C   |
|   7 | Robert  |     250 | Humanities | 64.4    | C     | 11A   |
+-----+---------+---------+------------+---------+-------+-------+

Question 10

Question 8What is foreign key ? How do you define a foreign key in your table ?

Accepted Answer

A non-key attribute, whose values are derived from the primary key of some other table, is known as foreign key in its current table. Defining a foreign key in a table involves specifying the relationship between the tables and setting up rules for data integrity. When two tables are related by a common column or set of columns, the related column(s) in the parent table (or primary table) should be either declared as a primary key or unique key. Meanwhile, the related column(s) in the child table (or related table) should have a foreign key constraint referencing the primary or unique key in the parent table.

Question 11

Question 9How is FOREIGN KEY commands different from PRIMARY KEY command ?

Accepted Answer

The PRIMARY KEY is a set of one or more attributes that can uniquely identify tuples within the relation. A primary key column cannot contain NULL values, and it must have unique values for each row. Only one primary key constraint can exist per table. Conversely, the FOREIGN KEY command establishes a relationship between two tables by linking a column or set of columns in one table to the primary key or a unique key in another table. It enforces referential integrity, ensuring that values in the foreign key column(s) of the referencing table match values in the referenced table's primary key or unique key column(s). A foreign key can allow NULL values, indicating that the relationship is optional. Multiple foreign key constraints can exist in a table, each referencing a different parent table.

Question 12

Question 10How is FOREIGN KEY commands related to the PRIMARY KEY ?

Accepted Answer

FOREIGN KEY commands establish relationships between tables by linking columns in one table to the PRIMARY KEY or a unique key in another table. This linkage ensures referential integrity, meaning that values in the FOREIGN KEY column(s) of the referencing table must match values in the PRIMARY KEY or unique key column(s) of the referenced table. Therefore, FOREIGN KEY commands are directly related to PRIMARY KEY commands as they rely on the unique identification provided by PRIMARY KEY constraints in other tables.

Question 13

Question 11What are table constraints ? What are column constraints ? How are these two different ?

Accepted Answer

Table constraints are rules or conditions applied to an entire table in a database. They are defined when creating or altering a table's schema.Column constraints are rules or conditions applied to individual columns within a database table. They are specified at the column level when creating or altering a table's schema.The difference between the two is that column constraints apply only to individual columns, whereas table constraints apply to groups of one or more columns.

Question 14

Question 12Insert all those records of table Accounts into table Pending where amt_outstanding is more than 10000.

Accepted Answer

INSERT INTO Pending
SELECT * FROM Accounts
WHERE amt_outstanding > 10000;

Question 15

Question 13Increase salary of employee records by 10% (table employee).

Accepted Answer

Table employeeIDFirst_NameLast_NameUser_IDSalary1DimJosephJdim50002JaganathMishrajnmishra40003SiddharthMishrasmishra80004ShankarGirisgiri70005GautamBuddhabgautam2000UPDATE employee
SET Salary = (Salary * 0.1) + Salary ;OutputTo view all the details (all columns and rows) of the "employee" table the below query is executed :SELECT * FROM employee ;+----+------------+-----------+----------+--------+
| ID | First_Name | Last_Name | User_ID  | Salary |
+----+------------+-----------+----------+--------+
|  1 | Dim        | Joseph    | Jdim     |   5500 |
|  2 | Jaganath   | Mishra    | jnmishra |   4400 |
|  3 | Siddharth  | Mishra    | smishra  |   8800 |
|  4 | Shankar    | Giri      | sgiri    |   7700 |
|  5 | Gautam     | Buddha    | bgautam  |   2200 |
+----+------------+-----------+----------+--------+

Question 16

Question 14Add a constraint (NN-Grade) in table Empl (given before assignment) that declares column Grade not null.

Accepted Answer

ALTER TABLE Empl
ADD CONSTRAINT NN_Grade 
(Grade NOT NULL) ;

Question 17

Question 15Drop the table Empl.

Accepted Answer

DROP TABLE IF EXISTS Empl;

Source: This question is from MySQL SQL Revision Tour, Informatics Practices &mdash; Class 12, CBSE Board.
Key Concepts CoveredThis question tests your understanding of the following concepts from the chapter MySQL SQL Revision Tour: Question, Drop, Table, Empl, MySQL SQL Revision Tour, Informatics Practices. These are fundamental topics in Informatics Practices that students are expected to master as part of the CBSE Class 12 curriculum.A thorough understanding of these concepts will help you answer similar questions confidently in your CBSE examinations. These topics are frequently tested in both objective and subjective sections of Informatics Practices papers. We recommend revising the relevant section of your textbook alongside practising these solved examples to build a strong foundation.
How to Approach This QuestionRead the question carefully and identify what is being asked. Break down complex questions into smaller parts. Use the terminology and concepts discussed in this chapter. Structure your answer logically — begin with a definition or key statement, then provide supporting details. Review your answer to ensure it addresses all parts of the question completely.
Key Points to RememberRead the question carefully and identify all parts before answering.
Use the terminology specific to this subject and chapter.
Review the textbook content for this chapter before attempting questions.
Practice writing concise, well-structured answers within time limits.
Related Questions from MySQL SQL Revision TourQ1: Question 1 What is SQL ? What are the different categories of SQL commands ?
Q10: Question 7 Write SQL commands for the following on the basis of given table STUDENT1. Table :...
Q11: Question 8 What is foreign key ? How do you define a foreign key in your table ?
Q12: Question 9 How is FOREIGN KEY commands different from PRIMARY KEY command ?
Q13: Question 10 How is FOREIGN KEY commands related to the PRIMARY KEY ?
Practice more questions from MySQL SQL Revision Tour &mdash; Informatics Practices, Class 12 CBSE

Question 18

Question 16(i)Differentiate between DROP TABLE, DROP DATABASE.

Accepted Answer

DROP TABLEDROP DATABASEThis command is used to delete a specific table from the database along with all its data, indexes, triggers, and constraints.This command is used to delete an entire database including all its tables, views, stored procedures, triggers, and other objects.The syntax is : DROP TABLE table_name;.The syntax is : DROP DATABASE database_name;.

Question 19

Question 16(ii)Differentiate between DROP TABLE, DROP clause of ALTER TABLE.

Accepted Answer

DROP TABLEDROP clause of ALTER TABLEThis command is used to delete a specific table from the database along with all its data, indexes, triggers, and constraints.This command is used to remove a specific component of a table, such as columns, constraints, or indexes.The syntax is : DROP TABLE table_name;The syntax is : ALTER TABLE table_name DROP COLUMN column_name;

Question 20

Question 17Mr. Mittal is using a table with following columns :Name, Class, Stream_Id, Stream_name
He needs to display names of students who have not been assigned any stream or have been assigned stream_name that ends with "computers".He wrote the following command, which did not give the desired result.SELECT Name, Class FROM Students
WHERE Stream_name = NULL OR Stream_name = "%computers" ;
Help Mr. Mittal to run the query by removing the error and write correct query.

Accepted Answer

The error in Mr. Mittal's original query lies in using "= NULL" instead of "IS NULL" to check for NULL values and using = '%computers' instead of "LIKE '%computers'" for pattern matching.The correct query is:SELECT Name
FROM Students
WHERE Stream_name IS NULL OR Stream_name LIKE '%computers';

MySQL SQL Revision Tour

Assertions And Reasons

Assignment

Checkpoint 51