Chapter 13 Sumita Arora Solutions | Class 12 Computer Science

Here is class 12 computer science Unit 13 solutions for Sumita Arora back exercise assignment. Below includes both textual and video solutions wherever required. View all the answers in assignment for chapter 13 and for all chapters here.

Q1: How are SQL commands classified?

SQL (Structured Query Language) is a relational database oriented language. It derives the whole table data as single unit to operate on.

It is mainly classified as Three sub-language:
1. DDL (Data Definition Language) defining the structure of the database structure.
2. DML (Data Manipulation Language) enables the user to manipulate the database.
3. DCL (Data Control Language) provide control access to the database.

Q2: Differentiate between DDL and DML commands.

DDL (Data Definition Language)DML (Data Manipulation Language)
This is used to create the structure of the database according the entry and the demand need.It is used to populate and to manipulate the database according the encountered need and requirement.
It defines the type of data that database will accommodate in the future.It is a revocable process that enter the data to database and can alter in future.
It is not classified further.It is further classified as Procedural and Non-Procedural DML.
Commands: CREATE, ALTER, DROP, TRUNCATE AND COMMENT and RENAME, etc.Commands: SELECT, INSERT, UPDATE, DELETE, MERGE, CALL etc.

Q3: (a) What is the use of UPDATE statement in SQL?  How is it different from ALTER statement?
(b) Mr. Shankar created a table VEHICLE with 3 rows and 4 columns.  He added 1 more row to it and deleted one column.  What is the Cardinality and Degree of the Table VEHICLE?
(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).

(i) To display the names of all the items whose name starts with “A”.
(ii) To display ICODEs and INAMEs of all the items, whose Brandname is Reliable or Coscore.
(iii) To change the Brandname to “Fit Trend India” of the item, whose ICODE or “G101”
(iv) Add a new row for new item in GYM with the details :
“G107”, “Vibro exerciser”, 21000 “GTC Fitness”
3Chapter 13 Q
GYM

Solution:

(a) Update command is to update the data entry to the dataset while Alter command is used to change the structure of the dataset. 
Update is a DML command while Alter is a DDL command.
(b) Cardinality will be 4 and Degree will be 3.
(c) 
(i) Select iname From GYM
where iname LIKE "A%";

(ii) Select icode, iname From GYM
where brandname is 'Reliable' OR brandname IS 'Coscore';

(iii) UPDATE GYM
set brandname = 'Fit Trend India'
where icode is 'G101';

(iv) INSERT INTO GYM
VALUES ('G107', 'Vibro exercise', 21000, 'GTCFitness');
Q4: (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?
(b) Consider the following table FITNESS with details about fitness products being sold in the store.  Write command of SQL for (i) to (iv).

(i) To display the names of all the products with price more than 20000.
(ii) To display the names of all the 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”
Chapter 13 Q 4
FITNESS

Solution

(a) Cardinality is 4 and Degree is 3
(b)
(i)select PNAME from FITNESS
where PRICE > 20000;

(ii) select PNAME from FITNESS
where Manufacturer IS 'Aone';

(iii) UPDATE FITNESS
SET PRICE = PRICE-(0.25*PRICE);

(iv) INSERT INTO FITNESS
VALUES ('P7', 'Vibro Exerciser', 28000, 'Aone');
Q5: Write SQL commands for the following on the basis of given table CLUB.

(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.
Chapter 13 Q 5
Club

Solution:

(a) SELECT * FROM CLUB;

(b) SELECT COACHNAME, DATEOFAPP FROM CLUB
ORDER BY DATEOFAPP DESC;

(c) SELECT COACHNAME, PAY, AGE, 0.15*PAY AS BONUS FROM CLUB;
Q6: Write SQL commands for the following on the basis of given table STUDENT1.

(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.
Chapter 13 Q 6
STUDENT1

Solution:

(a) SELECT * FROM STUDENT1
WHERE Stream is 'Nonmedical';

(b) SELECT Name FROM STUDENT1
WHERE Class LIKE "12%" 
ORDER BY Stipend;

(c) SELECT * FROM STUDENT1
ORDER BY AvgMark DESC;
Q7: What is foreign Key?  How do you define a foreign key in your table?

A Foreign key is used to represent relation between two tables. A foreign key in current table is derived from the primary key of some other table.
Such as personID in Orders will be a foreign key that link it with PersonID in Persons table.

Example to define a foreign key 
CREATE TABLE Orders (
    OrderID int ,
    OrderNumber ,
    PersonID int,
    PRIMARY KEY (OrderID),
    FOREIGN KEY (PersonID) REFERENCES Persons(PersonID)
);
Q8: How is FOREIGN KEY commands different from PRIMARY KEY command?

primary key uniquely identifies a record in the relational database table, whereas a foreign key refers to the field in a table which is the primary key of another table.
It relates each other in hierarchy nature or derived nature.

Q9: How is FOREIGN KEY commands related to the PRIMARY KEY?

Both the keys ensures a unique identify tuples within their respective table or relational database and as add-on link both the database.

Q10: What are table constraints?  What are column constraints?  How are these two different?

A Constraint is a condition or check that is applicable on field or set of field.

A column constraint is applied on individual columns whereas the table constraint to a group or more than one column.

Q11: Insert all those records of table Accounts into table Pending where amt_outstanding is more than 10000.
INSERT INTO Pending
SELECT * FROM Accounts 
WHERE amt_outstanding > 10000;
Q12: Increase salary of employee records by 10% (table employee).
UPDATE EMPLOYEE
SET SALARY = SALARY+(SALARY*0.10);
Q13: Add a constraint (NN-Grade) in table Empl (given before assignment) that declares column Grade not null.
CREATE TABLE Empl(
    ID int ,
    Name char(20) ,
    PersonID int,
    Gender char ,
    Grade char NOT NULL
);
Q14: Drop the table Empl.
DROP TABLE Empl;
Q15: Differentiate between :
(i) DROP TABLE, DROP DATABASE
(ii)DROP TABLE, DROP clause of ALTER TABLE.

(i) DROP TABLE command will drop the entire table i.e. all the rows and respective columns with name provided in command while DROP DATABASE will drop the entire database and all the tables in the database completely.

(ii) DROP TABLE command will drop the entire table i.e. all the rows and respective columns with name provided in command while the ALTER TABLE command is a DDL command that is helpfully to change the structure of table already defined and specific drop functioning.

Q16: Mr. 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.
SELECT Name FROM Students
WHERE Stream_Id = NULL OR Stream_name =”%computers”
Q17: The Doc_name Column of a table Hospital is given below:

Based on the information, find the output of the following queries:
(i) SELECT doc_name FROM HOSPITAL WHERE Doc_name like “%x”:
(ii)SELECT doc_name FROM HOSPITAL WHERE doc_name like “%e%”:
Chapter 13 Q 17
HOSPITAL

Solution:

(i) Sanjeev

(ii)Deepak
Sanjeev
Q18: Sarthak, a student of class XII, created a table “Class”.  Grade is one of the columns of this table.  To find the details of students whose Grades have not been entered, he wrote the following MySql query, which did not give the desired result:

SELECT *FROM Class WHERE Grade = “Null”;

Help Sarthak to run the query by removing the errors from the query and write the correct query.
SELECT * FROM Class WHERE Grade IS Null;
Q19: What is the purpose of DROP TABLE command in MySql?  How is it different from DELETE command?

DROP TABLE command will drop the entire table i.e. all the rows and respective columns with name provided but in DELETE command will erase all the rows of table i.e. an empty table will be created.

Clear Doubts with Computer Tutor
In case you’re facing problems in understanding concepts, writing programs, solving questions, want to learn fun facts | tips | tricks or absolutely anything around computer science, feel free to join CTs learner-teacher community: students.computertutor.in

You cannot copy content of this page