Data Base Management System | NPTEL 2022 | Week 2 Assignment Solutions

This set of MCQ(multiple choice questions) focuses on the Data Base Management System NPTEL 2022 Week 2 Assignment Solutions.

The course introduces relational data models; entity-relationship modeling, SQL, data normalization, and database design. Further it introduces query coding practices using MySQL (or any other open system) through various assignments. Design of simple multi-tier client / server architectures based and Web-based database applications is also introduced.

Course layout (Answers link)

Answers COMING SOON! Kindly Wait!

NOTE: You can check your answer immediately by clicking show answer button. Data Base Management System NPTEL 2022 Week 2 Assignment Solution” contains 10 questions.

Now, start attempting the quiz.

Data Base Management System NPTEL 2022 Week 2 Assignment Solutions

Q1. Consider the following instance of the relation PAPER(ID, TYPE, AREA, CITATION)

ID	TYPE	AREA	CITATION
145	JOURNAL	MACHINE LEARNING	567
146	JOURNAL	MACHINE LEARNING	167
1446	CONFERENCE	MACHINE LEARNING	43
986	CONFERENCE	NETWORKING	343
66	CONFERENCE	BLOCK CHAIN	243

Which of the following values will be present in all the tuples selected by the following query:
SELECT ‘CITATION’ FROM PAPER WHERE CITATION>200

a) 567
b) CITATION
c) 43
d) 167

Show Answer

Q2. Consider the following instance of the relation PAPER(ID, TYPE, AREA, CITATION)

ID	TYPE	AREA	CITATION
145	JOURNAL	MACHINE LEARNING	567
146	JOURNAL	MACHINE LEARNING	167
1446	CONFERENCE	MACHINE LEARNING	43
986	CONFERENCE	NETWORKING	343
66	CONFERENCE	BLOCK CHAIN	243

Select the correct query that displays the following output:

ID

72

73

723

33

a) SELECT ID/2 AS ID FROM PAPER WHERE AREA LIKE “% %”
b) SELECT ID/2 AS ID FROM PAPER WHERE AREA LIKE “%”
c) SELECT ID-73 AS ID FROM PAPER WHERE AREA LIKE “%_%”
d) SELECT ID-73 AS ID FROM PAPER WHERE AREA LIKE “%%”

Show Answer

Q3. Consider the following instance of the relation MAINTENANCE(WORK, DAYS, CHARGE, TEAM)

WORK	DAYS	CHARGE	TEAM
EXT. PAINT	7	100000	5
INT. PAINT	7	100000	8
FLOOR	1	5000	3
EXT. PAINT	7	50000	2
WALL REPAIR	10	80000	8

How many tuples will be returned by the following query:
SELECT AVG(CHARGE) FROM MAINTENANCE GROUP BY DAYS

a) 1
b) 2
c) 3
d) 4

Show Answer

Q4. Consider the following instance of the relation MAINTENANCE (WORK, DAYS, CHARGE, TEAM).

WORK	DAYS	CHARGE	TEAM
EXT. PAINT	7	100000	5
INT. PAINT	7	100000	8
FLOOR	1	5000	3
EXT. PAINT	7	50000	2
WALL REPAIR	10	80000	8

Which of the following queries will produce the following output (ordered):

WORK	TEAM
WALL REPAIR	8
EXT. PAINT	5
INT. PAINT	8

a) SELECT WORK, TEAM FROM MAINTENANCE WHERE CHARGE>50000 ORDER BY DAYS DESC, TEAM
b) SELECT WORK, TEAM FROM MAINTENANCE WHERE CHARGE>50000 ORDER BY DAYS DESC, TEAM DESC
c) SELECT WORK, TEAM FROM MAINTENANCE WHERE CHARGE>50000 ORDER BY DAYS, TEAM
d) SELECT WORK, TEAM FROM MAINTENANCE WHERE CHARGE>50000 ORDER BY DAYS, TEAM DESC

Show Answer

Q5. Consider the following schema. Primary keys are underlined.
Employees(E_name, E_street, E_city)
BankAccount(Ac_number, Branch_name, Balance)
AccountUser(E_name, Ac_number)
Which is the correct SQL command to create the table AccountUser having two foreign keys E_name and Ac_number

a) CREATE TABLE AccountUser {
E_name VARCHAR(20),
Ac_number NUMERIC(12, 2),
PRIMARY EKY(E_name, Ac_number),
FOREIGN KEY(E_name) REFERENCES Employees,
FOREIGN KEY(Ac_number) REFERENCES BankAccount);

b) CREATE TABLE AccountUser {
E_name VARCHAR(20),
Ac_number NUMERIC(12, 2),
PRIMARY EKY(E_name),
FOREIGN KEY(E_name) REFERENCES Employees,
FOREIGN KEY(Ac_number) REFERENCES BankAccount);

c) CREATE TABLE AccountUser {
E_name VARCHAR(20) PRIMARY KEY,
Ac_number NUMERIC(12, 2) PRIMARY KEY,
FOREIGN KEY(E_name) REFERENCES Employees,
FOREIGN KEY(Ac_number) REFERENCES BankAccount);

d) CREATE TABLE AccountUser {
E_name VARCHAR(20),
Ac_number NUMERIC(12, 2),
PRIMARY EKY(E_name, Ac_number),
FOREIGN KEY Employees(E_name),
FOREIGN KEY BankAccount(Ac_number));

Show Answer

Data Base Management System NPTEL Week 2 Assignment Solutions

Q6. Identify the correct SQL command(s) to create a new record for the Faculty table as given below. Primary key is underlined in the schema.

Faculty_name	Department	Degree
Charline B	English	M.A.

a) INSERT INTO FACULTY VALUES(‘Charline B’, ‘English’, ‘M.A.’);
b) INSERT INTO FACULTY VALUE(”Charline B”, ”English”, ”M.A.”);
c) INSERT INTO FACULTY VALUE(‘Charline B’, ‘English’, ‘M.A.’);
d) INSERT INTO FACULTY (Faculty_name, Department, Degree) VALUES(‘Charline B’, ‘English’, ‘M.A.’);

Show Answer

Q7. A role Admin has the privilege of select, insert, update and delete on all tables of database. A new role Users is created and the following statement is executed.
grant Admin to Users;
Which rights will Users inherit?

a) Only select
b) Only select and delete
c) Only select, and update but not delete
d) All rights – select, insert, delete, and update

Show Answer

Data Base Management System NPTEL Week 2 Assignment Solutions

Q8. Consider the following instance of EmployeeDetails(EmpName, Branch, Address, Salary) relation.

EmpName	Branch	Address	Salary
Raja	Kolkata	Kolkata	10000
Priyanka	Mumbai	Hyderabad	12000
Arindam	Bangalore	Kolkata	15000
Rumki	Hyderabad	Hyderabad	20000
Sumit	Hyderabad	Bangalore	10000
Sandip	Mumbai	Mumbai	15000

Identify the correct statement(s) to get the following output:

EmpName	Branch	Address	Salary
Raja	Kolkata	Kolkata	10000
Arindam	Bangalore	Kolkata	15000
Rumki	Hyderabad	Hyderabad	20000
Sandip	Mumbai	Mumbai	15000

a) SELECT * FROM EmployeeDetails WHERE Branch=Address OR Salary>=15000;
b) SELECT * FROM EmployeeDetails WHERE Branch=Address AND Salary>=15000;
c) (SELECT * FROM EmployeeDetails WHERE Branch=Address) UNION (SELECT * FROM EmployeeDetails WHERE Salary>=15000);
d) (SELECT * FROM EmployeeDetails WHERE Branch=Address) INTERSECT (SELECT * FROM EmployeeDetails WHERE Salary>=15000);

Show Answer

Data Base Management System NPTEL Week 2 Assignment Solutions

Q9. Consider the following instance of EmployeeDetails(EmpName, Branch, Address, Salary) relation.

EmpName	Branch	Address	Salary
Raja	Kolkata	Kolkata	10000
Priyanka	Mumbai	Hyderabad	12000
Arindam	Bangalore	Kolkata	15000
Rumki	Hyderabad	Hyderabad	20000
Sumit	Hyderabad	Bangalore	10000
Sandip	Mumbai	Mumbai	15000

Identify the correct SQL command that creates a view as EmployeeSalaryDetails in which columns EmpName and Salary for the tuples where the second character of EmpName is ‘r’ and the Salary is atleast 15000.

a) CREATE VIEW EmployeeSalaryDetails(EmpName, Salary) ON
SELECT EmpName, Salary FROM EmployeeDetails
WHERE EmpName LIKE ‘%r%’ AND Salary>=15000;
b) CREATE VIEW EmployeeSalaryDetails(EmpName, Salary) ON
SELECT EmpName, Salary FROM EmployeeDetails
WHERE EmpName LIKE ‘_r%’ AND Salary>15000;
c) CREATE VIEW EmployeeSalaryDetails(EmpName, Salary) AS
SELECT EmpName, Salary FROM EmployeeDetails
WHERE EmpName LIKE ‘_r%’ AND Salary>=15000;
d) CREATE VIEW EmployeeSalaryDetails(EmpName, Salary) AS
SELECT EmpName, Salary FROM EmployeeDetails
WHERE EmpName LIKE ‘_r%’ AND Salary>15000;

Show Answer

Q10. Consider the given relational schema: EmployeeDetails(EmpName, Branch, Address, Salary)
Identify the correct statement to find the EmpName, Branch and Salary of all tuples in which Salary is greater than or equal to the average Salary of all employees or Salary is between 15000 and 20000 including 15000 and 20000.

a) SELECT EmpName, Branch, Salary
FROM EmployeeDetails
WHERE Salary>=(SELECT AVG(Salary) from EmployeeDetails)
OR Salary LIKE(15000, 20000);
b) SELECT EmpName, Branch, Salary
FROM EmployeeDetails
WHERE Salary>=(SELECT AVG(Salary) from EmployeeDetails)
OR Salary IN(15000, 20000);
c) SELECT EmpName, Branch, Salary
FROM EmployeeDetails
WHERE Salary>=(SELECT AVG(Salary) from EmployeeDetails)
OR Salary AS(15000, 20000);
d) SELECT EmpName, Branch, Salary
FROM EmployeeDetails
WHERE Salary>=(SELECT AVG(Salary) from EmployeeDetails)
OR Salary BETWEEN 15000 AND 20000;

Show Answer

Data Base Management System NPTEL Week 2 Assignment Solutions

<< Prev- Data Base Management System Week 1 Assignment Solutions

>> Next- Data Base Management System Week 3 Assignment Solutions

---

NPTEL answers: Problem solving through programming in C

NPTEL answers: Principles of Management

Programming in Java NPTEL week 1 quiz answers

NPTEL – Python for Data Science assignment solutions

Nptel – Deep Learning assignment solutions

The above question set contains all the correct answers. But in any case, you find any typographical, grammatical or any other error in our site then kindly inform us. Don’t forget to provide the appropriate URL along with error description. So that we can easily correct it.

Thanks in advance.

For discussion about any question, join the below comment section. And get the solution of your query. Also, try to share your thoughts about the topics covered in this particular quiz.