Chapter 13 Sumita Arora Solutions | Class 12 Computer Science

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.

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');

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');

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;

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;

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)
);

A 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.

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

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.

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

UPDATE EMPLOYEE
SET SALARY = SALARY+(SALARY*0.10);

CREATE TABLE Empl(
    ID int ,
    Name char(20) ,
    PersonID int,
    Gender char ,
    Grade char NOT NULL
);

DROP TABLE Empl;

(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.

SELECT Name FROM Students
WHERE Stream_Id = NULL OR Stream_name =”%computers”

Solution:

(i) Sanjeev

(ii)Deepak
Sanjeev

SELECT * FROM Class WHERE Grade IS Null;

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.