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
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
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
(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
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
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
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
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
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
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
(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
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.
Question 21.
In a database school there are two tables EMPLOYEE and DEPT as shown as below:
Table EMPLOYEE
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:
Question 22.
In a database there are two tables STUDENT and STREAM as below:
Table STUDENT
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:
(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
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.