Informatics Practices Class 12 Important Questions Chapter 12 Integrity Constraints and Tables

Informatics Practices Class 12 Important Questions Chapter 12 Integrity Constraints and Tables is part of Informatics Practices Class 12 Important Questions. Here we have given Informatics Practices Class 12 Important Questions Chapter 12 Integrity Constraints and Tables.

Informatics Practices Class 12 Important Questions Chapter 12 Integrity Constraints and Tables

1 Mark Questions

Question 1.
Write a SQL command to view the constraints to EMP table. (All India 2013)
Answer:
To Dispaly all the constraints in table ‘EMP’

SHOW TABLE EMP; 
or
SELECT * FROM 
INFORMATION_SCHEME.
TABLE_CONSTRAINTS 
WHERE TABLE_NAME = ‘EMP’;

Question 2.
Ms. Mirana wants to remove the entire content of a table “BACKUP” along with its structure to release the storage space. What MySQL statement should be used? (All India 2012)
Answer:
The command to delete the content of the BACKUP table along with its structure is as follows:

DROP TABLE BACKUP;

Question 3.
In an Item table columns include ItemCode, ItemName, Price and Qty. Which one of these columns should be set as primary key and why? (Delhi 2011C)
Answer:
The ItemCode field is most suitable for the primary key because ItemCode for every record will be different and no two records can have same ItemCode.

Question 4.
Sheela created a table in MySQL. Later on she found that she did not need a column “Fee”. Which command should she use to delete the column from the table? (Delhi 2011c)
Answer:
To delete column “Fee”, from a table, we have to use the following command:

ALTER TABLE table_name 
DROP COLUMN Fee;

Question 5.
Write a command to add a NOT NULL constraint on FEES column of a student table.
Answer:
To add a NOT NULL constraint on FEES column of a STUDENT Table.

ALTER TABLE STUDENT 
Modify FEES INTEGER NOT NULL;

2 Marks Questions

Question 6.
Distinguish between a primary key and a unique key in a table. (All India 2014C)
Answer:

Primary key can’t accept null values while unique key can accept only one null value,
A table can contain only one primary key but can have several unique keys.

Question 7.
Write MySQL command to create the table SHOP with given structure and constraint:
Table SHOP
Informatics Practices Class 12 Important Questions Chapter 12 Integrity Constraints and Tables 1
Answer:

CREATE TABLE SHOP
( Fno INT(10) PRIMARY KEY,
Fname VARCHAR!15),
Type CHAR(IO),
Stock INTO),
Price DECIMAL(8,2)
);

Question 8.
Write an SQL query to create the table SALESPERSON with the following structure: (All India 2014 C)
Table SALESPERSON
Informatics Practices Class 12 Important Questions Chapter 12 Integrity Constraints and Tables 2
Answer:

CREATE TABLE SALESPERSON
( SCODE DECIMALS) PRIMARY KEY, FIRSTNAME VARCHAROO) NOT NULL,
LASTNAME VARCHAROO) NOT NULL,
CITY VARCHAROO),
SALES DECIMAL(8)
);

Question 9.
In a database STUDENT, there is a Table RESULT with the following contents (All India 2013)
Table RESULT
Informatics Practices Class 12 Important Questions Chapter 12 Integrity Constraints and Tables 3
(i) Identify the attributes, which can be chosen as candidate keys in the table RESULT.
(ii) Write SQL Query to change the Marks of Mukta to 95 in the table RESULT. (All India 2013)
Answer:
(i) Regno and Admno
(ii)

UPDATE RESULT 
SET Marks = 95 
WHERE Name = ‘Mukta’;

Question 10.
Write SQL command to create the table VEHICLES with given constraint.
Table VEHICLES
Informatics Practices Class 12 Important Questions Chapter 12 Integrity Constraints and Tables 4
Answer:

CREATE TABLE VEHICLES (RegNo CHAR(10)
Primary Key, Regdate Date, Owner 
VARCHAR (30), Address VARCHAROO));

Question 11.
Write MySQL command to create the Table STOCK including its constraints.
Table STOCK
Informatics Practices Class 12 Important Questions Chapter 12 Integrity Constraints and Tables 5
Answer:
The SQL command to create a table as per given structure is as follows:

CREATE TABLE STOCK
(ID DECIMALS) PRIMARY KEY,
Name VARCHAROO),
Company VARCHAROO),
Price DECIMAL(8) NOT NULL);

Question 12.
Write MySQL command to create the Table PRODUCT including its constraints. (All India 2012)
Table PRODUCT
Informatics Practices Class 12 Important Questions Chapter 12 Integrity Constraints and Tables 6
Answer:
The SQL command to create a table as per given structure is as follows:

CREATE TABLE PRODUCT
(P ID DECIMAL(4) PRIMARY KEY,
P_Name VARCHAROO),
P_Company VARCHAROO),
Price DECIMALS) NOT NULL);

Question 13.
Mr. Sondhi created two tables with DEPTNO as primary key in Tableland foreign key in Table2. (Delhi 2012)
While inserting a row in Table2, Mr. Sondhi is not able to enter a value in the column DEPTNO. What could be possible reason for it? (HOTS; Delhi 2012)
Answer:
The possible reason for this situation is referential integrity constraint is imposed.

Question 14.
Mr. Sanghi created two tables with CITY as primary Key in Tableland foreign key in Table2. While inserting a row in Table2, Mr. Sanghi is not able to enter a value in the column CITY. What could be possible reason for it? (All India 2012)
Answer:
The possible reason for this situation is referential integrity constraint is imposed.

Question 15.
Write a SQL query to create the Table MOVIE with the following structure: (Delhi 2011C)
Table MOVIE
Informatics Practices Class 12 Important Questions Chapter 12 Integrity Constraints and Tables 7
Answer:
The SQL command to create a table as per given structure is as follows:

CREATE TABLE MOVIE
(Movie_Code VARCHAR(5) PRIMARY KEY, 
Name VARCHAROO),
Category VARCHAROO),
Date_Release DATE);

Question 16.
Write a MySQL command to create the Table BANK whose structure is given below: (All India 2011)
Table BANK
Informatics Practices Class 12 Important Questions Chapter 12 Integrity Constraints and Tables 8
Answer:
The SQL command to create a table as per given structure is as follows:

CREATE TABLE BANK
(Acct_Number INTEGER(4) PRIMARY KEY, 
Name VARCHAR(3),
BirthDate DATE,
Balance INTEGERS) 
NOT NULL);

Question 17.
Write a MySQL command to create the Table PAYMENT, where structure is given below: (HOTS; Delhi 2011)
Table PAYMENT
Informatics Practices Class 12 Important Questions Chapter 12 Integrity Constraints and Tables 9
Answer:
The SQL command to create a table as per given structure is as follows:

CREATE TABLE PAYMENT 
(Loan_Number INTEGERS) PRIMARY KEY, 
Payment_Number VARCHAR(3), 
Payment_Date DATE,
Payment_Amount INTEGER(8) NOT NULL);

Question 18.
Discuss the role of FOREIGN KEY constraints in MySQL table.
Answer:

It results into the rejection of insert or update, if a corresponding value does not currently exist in the primary table.
The foreign key column in the child table must reference a primary key or unique column in the parent table.
Both the related table’s column should have the same data type.

4 Marks Questions

Question 19.
Table PATIENT
Informatics Practices Class 12 Important Questions Chapter 12 Integrity Constraints and Tables 10
(i) Identify the primary key in the table given above.
(ii) Write MySQL query to add a column Department with data type Varchar and size 30 in the table PATIENT. (Delhi 2012)
Answer:
(i) A primary key can be P_No.
(ii) The command for the given problem will be as follows:

ALTER TABLE PATIENT 
ADD (Department VARCHAR(30));

Question 20.
Table CD DETAILS
Informatics Practices Class 12 Important Questions Chapter 12 Integrity Constraints and Tables 11
Table CDTYPES

(i) Which column can be set as the primary key in the CD DETAILS table?
(ii) Which column in the CD DETAILS table is the foreign key?
(iii) How many rows and columns will be there in the cartesian product of the above two tables?
(iv) Write commands in SQL to display the CDJD, CD_Title and corresponding types for all the CDs.
(v) Write the output of the following: (HOTS; Delhi 2011C)

SELECT CD_Title, Duration, Type 
FROM CD DETAILS, CDTYPES
WHERE CD DETAILS.CD_CODE=CDTYPES.CD_C0DE AND Type -‘Spiritual’;

Answer:
(i) The column CD_JD can be set as a primary key.
(ii) The column CD_CODE can be set as foreign key.
(iii) There will be 5 columns and 16 rows in cartesian product of the given two tables.

(iv) SELECT CD_ID,CD_Tit1 e, Type
FROM CD DETAILS, CDTYPES 
WHERE CD DETAILS. CD_C0DE 
= CDTYPES. CD_C0DE;

(v) The output of the given command will be as follow.
Informatics Practices Class 12 Important Questions Chapter 12 Integrity Constraints and Tables 13

Question 21.
In a database school there are two tables EMPLOYEE and DEPT as shown as below:
Table EMPLOYEE
Informatics Practices Class 12 Important Questions Chapter 12 Integrity Constraints and Tables 14
Table DEPT

Write MySQL queries for the following:
(i) Identify the foreign key in the table EMPLOYEE.
(ii) What output will you get when an equijoin query is executed to get the Name from EMPLOYEE table and corresponding DName from DEPT table? (HOTS; Delhi 2011)
Answer:
(i) The column Deptno can be the foreign key of the table EMPLOYEE.
(ii) The required command is as follows

SELECT E.Name, D.DName 
FROM EMPLOYEE E, DEPT D 
WHERE E.Deptno = D.Deptno:

and, the output of the above command will be:
Informatics Practices Class 12 Important Questions Chapter 12 Integrity Constraints and Tables 16

Question 22.
In a database there are two tables STUDENT and STREAM as below:
Table STUDENT
Informatics Practices Class 12 Important Questions Chapter 12 Integrity Constraints and Tables 17
Table STREAM

(i) What is the cardinality and degree of both tables?
(ii) Among both the tables which table has the foreign key and write the foreign key name.
Answer:
(i) Cardinality refers to number of records or rows in a given table and degree refers to number of colums in a given table. The cardinality and degree of both of the tables are as follows:
Informatics Practices Class 12 Important Questions Chapter 12 Integrity Constraints and Tables 19
(ii) Table Student has the foreign key and Strld is the name of foreign key.

Question 23.
In a database, there are two tables CUSTOMER and BILL as shown below
Table CUSTOMER
Informatics Practices Class 12 Important Questions Chapter 12 Integrity Constraints and Tables 20
Table BILL

(i) How many rows and how many columns will be there in the cartesian product of these two tables?
(ii) Which column in the Bill table is the foreign key? (HOTS)
Answer:
(i) There will be 15 rows and 6 columns in the Cartesian product of these two tables.
(ii) The column CustID will be the foreign key of the table Bill.

We hope the Informatics Practices Class 12 Important Questions Chapter 12 Integrity Constraints and Tables help you. If you have any query regarding Informatics Practices Class 12 Important Questions Chapter 12 Integrity Constraints and Tables, drop a comment below and we will get back to you at the earliest.