Data Base Management System | Week 2

Course Name: Data Base Management System

Course Link: Click Here

These are Data Base Management System Week 2 Assignment Answers

---

Q1. Table ACCOUNT is created using the following SQL query :
CREATE TABLE ACCOUNT(
AcNo INTEGER,
NAME VARCHAR (20),
PRIMARY KEY (AcNo),
CHECK (NAME LIKE ‘%Y’));
Which of the following SQL command(s) will result in an error?
a) INSERT INTO ACCOUNT VALUES (1, ‘AKSHAY’);
b) INSERT INTO ACCOUNT VALUES (NULL, ‘BIJAY’);
c) INSERT INTO ACCOUNT VALUES (3, ‘ABHAY’);
d) INSERT INTO ACCOUNT VALUES (4, ‘ANANDI’);

Answer: a, c

---

These are Data Base Management System Week 2 Assignment Answers

---

Q2. A designer maintains the following relations :
• StyleName (Element, Style)
• PrintStyle (Element, Color)
Which of the following option(s) is/are correct to find the names of the Element that has Style without having any color? Primary keys are underlined in the schemas.
a) SELECT DISTINCTElement
FROM StyleName
INTERSECT
SELECT DISTINCT Element
FROM PrintStyle;
b) SELECT Element
FROM StyleName
WHERE Element
NOT IN
(SELECT Element
FROM PrintStyle);
c) SELECT Element
FROM PrintStyle
WHERE Element
NOT IN
(SELECT Element
FROM StyleName);
d) SELECT DISTINCT Element
FROM PrintStyle
MINUS
SELECT DISTINCT Element
FROM StyleName;

Answer: b) SELECT Element
FROM StyleName
WHERE Element
NOT IN

---

These are Data Base Management System Week 2 Assignment Answers

---

Q3. Consider two relations Table1 (A1,A2) and Table2 (A1, A3) as following :

Suppose Table3 (A1, A2, A3) is the output of natural full outer join of Table1 and Table2. Which of the following statement(s) is/are correct?
a) Table3 does not contain (5, 7, null) and (2, 3, 7)
b) Table3 contains (5, 7, 4), (3, null, 7) and (2, 3, null)
c) Table3 contains (5, 7, 4) but not (2, 3, null) and (3, null, 7)
d) Table3 contains (2, 3, 7) and (5, 7, null)

Answer: b, c

---

These are Data Base Management System Week 2 Assignment Answers

---

Q4. A designer maintains the following instances:

What is a possible output if the following SQL Query is executed?
SELECT COUNT(LOC) FROM Project, Module GROUP BY LOC;
a) 8
4
b) 12
c) 2
1
d) 3

Answer: c) 2
1

---

These are Data Base Management System Week 2 Assignment Answers

---

Q5. Consider the following instance:

What is the output of the following SQL statement?
select distinct Experiment Location from Experiment
where Participant Count in(
select ParticipantCount from Experiment
where Experiment Location=’Lab2′
);
a) Lab2, Lab7, Lab5
b) Lab1, Lab7, Lab5
c) Lab1, Lab2, Lab5
d) Lab1, Lab2, Lab7

Answer: a) Lab2, Lab7, Lab5

---

Q6. Given the schema (primary key is underlined)
budget (month, expense)
describe the result obtained by the following query.
SELECT MAX(expense) AS exp
FROM budget
WHERE expense < (SELECT MAX (expense) FROM budget);
a) Finds the highest expense from budget.
b) Finds second highest expense from budget.
c) Finds all expenses from budget that are less than the highest expense.
d) Finds all expenses from budget that are equal to the highest expense.

Answer: b) Finds second highest expense from budget.

---

These are Data Base Management System Week 2 Assignment Answers

---

Q7. Consider the following instance:

Which of the following output (s) is (are) displayed by the following SQL query?
select NAME from Student
where NAME like ‘%A%’ AND NAME NOT like ‘%Rathod’;
a) Abhay Singh, Roshan Singh
b) Abhay Singh, Roshan Singh, Smriti
c) Smriti, Akshay Rathod
d) Arijit Ray, Abhay Singh, Roshan Singh

Answer: d) Arijit Ray, Abhay Singh, Roshan Singh

---

These are Data Base Management System Week 2 Assignment Answers

---

Q8. Consider the following instance of Universities Details (StateName, #Central Universities, #State Universities, #Faculties) relation.

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

a) SELECT * FROM UniversitiesDetails
WHERE #Faculties>=20000;
b) SELECT StateName, #State Universities, #Faculties FROM Universities Details
WHERE #Faculties>=20000;
c) SELECT StateName, #State Universities, #Faculties FROM UniversitiesDetails
WHERE #Faculties>=20000 AND #Central Universities=1;
d) SELECT StateName, #State Universities, #Faculties FROM UniversitiesDetails
WHERE #Faculties>=20000 OR #Central Universities=1;

Answer: b) SELECT StateName, #State Universities, #Faculties FROM Universities Details
WHERE #Faculties>=20000;

---

These are Data Base Management System Week 2 Assignment Answers

---

Q9. Consider the following instance of Universities Details (StateName, #Central Universities, #State Universities, #Faculties) relation.

Identify the correct SQL command that creates a view as Universities Info with StateName and #Central Universities that enlist members whose StateName has 2nd character ‘e’.
a) CREATE VIEW Universities Info (StateName, #Central Universities) OF
SELECT StateName, #Central Universities FROM UniversitiesDetails
WHERE StateName LIKE ‘_e%’;
b) CREATE VIEW Universities Info (StateName, #Central Universities) AS
SELECT StateName, #Central Universities FROM Universities Details
WHERE StateName LIKE ‘%e%’;
c) CREATE VIEW Universities Info (StateName, #Central Universities) ON
SELECT StateName, #Central Universities FROM UniversitiesDetails
WHERE StateName LIKE ‘_e%’;
d) CREATE VIEW Universities Info (StateName, #Central Universities) AS
SELECT StateName, #Central Universities FROM UniversitiesDetails
WHERE StateName LIKE ‘_e%’;

Answer: d) CREATE VIEW Universities Info (StateName, #Central Universities) AS
SELECT StateName, #Central Universities FROM UniversitiesDetails
WHERE StateName LIKE ‘_e%’;

---

These are Data Base Management System Week 2 Assignment Answers

---

Q10. Consider the following instance of UniversitiesDetails (StateName, #Central Universities, #State Universities, #Faculties) relation.

Identify the correct statement(s) to find the StateName whose #Central Universities is greater than or equal to 2 and #Faculties is greater than or equal to 18000 but less than or equal to 25000.
a) SELECT StateName FROM Universities Details
WHERE #Central Universities>=2 AND #Faculties IN (18000, 25000);
b) SELECT StateName FROM UniversitiesDetails
WHERE #Central Universities>=2 AND #Faculties BETWEEN 18000 AND 25000;
c) (SELECT StateName FROM Universities Details
WHERE #Central Universities>=2)
INTERSECT
(SELECT StateName FROM Universities Details
WHERE #Faculties BETWEEN 18000 AND 25000);
d) (SELECT StateName FROM UniversitiesDetails
WHERE #Central Universities>=2)
INTERSECT
(SELECT StateName FROM UniversitiesDetails
WHERE #Faculties AS (18000, 25000));

Answer: b, c

---

These are Data Base Management System Week 2 Assignment Answers

---

---

These are Data Base Management System Week 2 Assignment Answers

More Solutions of Data Base Management System: Click Here

More NPTEL Solutions: https://progiez.com/answers/nptel/

---