Download HOTS questions and answers for Class 12 Informatics Practices. Read CBSE Class 12 Information Practices HOTs Relational Database Management System below and download in pdf. High Order Thinking Skills questions come in exams for Informatics Practices in Standard 12 and if prepared properly can help you to score more marks. You can refer to more chapter wise Class 12 Informatics Practices HOTS Questions with solutions and also get latest topic wise important study material as per NCERT book for Class 12 Informatics Practices and all other subjects for free on Studiestoday designed as per latest CBSE, NCERT and KVS syllabus and pattern for Grade 12
Relational Database Management System
Oracle SQL Revision Tour
109 Convert the given relation in to 2 NF: 2
R(A,B,C,D,E) AB→C, AB→D, AB→E, B→C, A→D
110 Convert the following relation in to 3 NF: 2
R(A,B,C,D,E) A→B, A→C,A→D,A→E, B→C, D→E
111 Assume that the following relation has possible two sets of candidate keys (Prof_code, Dept) and 4 (Prof_code, Hod). The functional dependency is shown as:
(Prof_code, Dept) →Hod, (Prof_code, Dept)→Ptime, (Prof_code, Hod)→Dept, (Prof_code, Hod)→Ptime and Dept→Hod
Answer the following questions based on above scenario:
a) What is the amount of normalization already achieved.
b) Give a situation for deletion anamoly in the above relation.
c) Normalize the above relation in the next higher normal form.
112 It is required to create the following tables in Oracle database:
Chicken: (CID INTEGER PRIMARY KEY, EID INTEGER FOREIGN KEY)
Egg: (EID INTEGER PRIMARY KEY, CID INTEGER FOREIGN KEY)
The structure of the table states that the primary key of Chicken is a foreign key in Egg and vise
versa.
Give a possible solution to create the two tables in oracle database.
113 Write the result of the follwoting queries: 4
a) SELECT ROUND(150.79, -2) from DUAL
b) SELECT LTRIM(‘NATASHA’, ‘NAT’) from DUAL
c) SELECT ADD_MONTHS(’30-DEC-2007’, 2) FROM DUAL
d) SELECT NEXT_DAY(SYSDATE, 'MONDAY') FROM DUAL
a) What level of normalization does the above table achieved?
b) Write the situation when an insertion anamoly arises in the above table.
c) Normailze the above table in to next higher normal form.
TrainNo,StationFr→Train, TrainNo,StationFr→StationFr, TrainNo,StationFr→SationTo,
TrainNo,StationFr→WeekDay, TrainNo,StationFr→Superfast
TrainNo→Train, TrainNo→Superfast
a) What level of normalization the above table achieved?
b) Write the situation when an updation anamoly arises in the above table.
c) Normailze the above table in to next higher normal form.
117 Create a sequence studseq in Oracle to generate an autonumber field for the ID attribute of student. 4
The sequence starts from 100 with no upper limit and there is no repetition. The student table has the structure STUDENT(ID,NAME, DOB, CLASS). Insert a row in the student table using the sequence.
118 The EMPLOYEE table with structure EMPLOYEE(ID, NAME, DOB, DEPTNO, SAL, PANNO) 2 has the PANNO field with NOT NULL constraint. Change the structure of EMPLOYEE table so that NULL values may be accepted in the PANNO field.
Write the SQL queries asked below referring the following tables:
119 To count the number of students in each subject with an average stipend more than 350.
120 To display the name and stipend of all the students who are receiving highest stipend in each subject.
121 To display the name, subject and average of every student whose average is less than the maximum average in each subject.
122 To display the names of all the students who are having the same average.
123 To display the name subject and advisor for each student whose average is greater than the average of JOHN.
124 To display the subject and advisor that are not referenced in Student table.
125 To display the student name and advisor all the students who are offering either Physics or Chemistry.
126 Display the department name of employee KING.
127 Display the department name of highest paid employee.
128 Display the department name of second highest paid employee.
129 Display the details of all the employees who work in the same department in which SCOTT works.
130 Display the details of highest paid employee in each department.
131 Display department names in DEPT table that are not referenced in EMP table.
132 Display the name and the job of all the employees who has same job but works for different departments.
133 Display the details of the employees who are drawing more than the average salary in each department.
134 Display the employee names and hire date of those employee who were hired during the same month.
135 Display the emp no and ename of all the employees who are also acting as managers.
136 Display the employee name and location for all the employees who are managers in same department.
137 Display the employee details who is drawing the second highest salary in the emp table.
138 Display the employee names and their manager names in the employee tables.
139 Display the employee names and their hire dates who have joined prior to the joining date of their managers.
Given the following tables, write the queries for questions 140 to 150
140 To display the Cities from Supplier table those are not available in Product table. 2
141 To display the product names from Supplier table those are not available in the Product table. 2
142 To display the product names from product table whose quantity is more than their average 2 quantity.
143 To display the product names those are supplied by the same supplier. 2
144 To display the products whose total price (price*Qty) is the highest in the supplier table. 2
145 To display the product names those are supplied in minimum quantities for each group. 2
146 Create a table SUPPLIER_AUDIT with the details of all the suppliers who have registered supplies more than the average supplies. (Supply= qty*price)
147 What is the difference between the two operations stated below: 2
DELETE FROM SUPPLIER;
TRUNCATE TABLE SUPPLIER;
148 As it is seen from the Supplier table there is no supplier for S9. Write a query to make the 2
SNAME for S9 to be HLL.
149 To display the product names and supplier names who are either operating in DELHI, 2
MUMBAI or KOLKATA
150 To display the number of product names for each supplier with more than one supplier.
151 To display the details of all the suppliers who are not supplying at KOLKATA in the 2 descending