Data Base Management System | Week 2
Session: JAN-APR 2024
Course Name: Data Base Management System
Course Link: Click Here
For answers or latest updates join our telegram channel: Click here to join
These are Data Base Management System Week 2 Assignment Answers
Q1. Consider the following table Collections :
How many tuples will be returned by the following query?
SELECT item, price FROM Collections
WHERE price>(SELECT MIN(price) FROM Collections);
a) 2
b) 3
c) 4
d) 5
Answer: d) 5
Q2. Consider the following table Collections:
Which of the following options will NOT be present in the output produced by
SELECT MAX(cid) FROM COLLECTIONS GROUP BY item;?
a) 11
b) 13
с) 23
d) 2
Answer: a) 11
For answers or latest updates join our telegram channel: Click here to join
These are Data Base Management System Week 2 Assignment Answers
Q3. Consider the following table Delivery:
What will be the output of the following SQL query?
SELECT COUNT (purchaseid) FROM Delivery
WHERE deliverydate LIKE ‘%2011′ AND deliverydate NOT LIKE ’30/’;?
a) 2
b) 3
c) 5
d) 0
Answer: b) 3
Q4. Consider the following instance of table Employee :
Identify the correct, “CREATE” statement for this table.
a) CREATE TABLE Employee (
id int NOT NULL,
lastname varchar(255) NOT NULL,
firstname varchar (255),
age int,
PRIMARY KEY (ID));
b) CREATE TABLE Employee (
id int NOT NULL,
lastname varchar(255) NOT NULL,
firstname varchar (255),
age int,
PRIMARY KEY (ID,lastName));
c) CREATE TABLE Employee (
id int,
lastname varchar(255) NOT NULL,
firstname varchar (255),
age int,
PRIMARY KEY (lastName));
d) CREATE TABLE Employee (
id int NOT NULL,
lastname varchar(255) NOT NULL,
firstname varchar (255),
age int,
PRIMARY KEY (firstname, lastName));
Answer: b)
For answers or latest updates join our telegram channel: Click here to join
These are Data Base Management System Week 2 Assignment Answers
Q5. Consider the two instances:
Which of the following operations will generate the following outputs:
a) BRAND INNER JOIN STATIONARY
b) STATIONARY NATURAL JOIN BRAND
c) BRAND NATURAL RIGHT OUTER JOIN STATIONARY
d) STATIONARY NATURAL LEFT OUTER JOIN BRAND
Answer: a) BRAND INNER JOIN STATIONARY
For answers or latest updates join our telegram channel: Click here to join
These are Data Base Management System Week 2 Assignment Answers
Q6. Consider the following relation instance:
Both attributes Num1 and Num2 are integers and do not have null values. Num is the primary key of the table and Num2 is the foreign key of the same table, Number Tab and references with on delete cascade constraints. A tuple (Num1, Num2) will be in the table only if Num1 ≤ Num2. Which of the following is possible if the tuple (5, 7) is deleted from the table?
a) The deletion of (5, 7) will be prohibited.
b) Tuple (4, 5) and (3, 4) also will be deleted.
c) Tuple (6, 7) and (7, 8) also will be deleted.
d) Only tuple (7, 8) will be deleted.
Answer: b) Tuple (4, 5) and (3, 4) also will be deleted.
For answers or latest updates join our telegram channel: Click here to join
These are Data Base Management System Week 2 Assignment Answers
Q7. Suppose a bank wants to make a view consisting of the names of customers having loan in
‘MUMBAI’ branch with the loan amount being more than equal to 50000 but less than equal to
70000.
• loan (loan number, branch name, amount)
• borrower (customer name, loan number)
Identify the correct query from the following. Primary keys are underlined in the schema.
a) CREATE VIEW v1 AS
SELECT customer_name
FROM loan, borrower
WHERE branch_name = ‘MUMBAI’
AND loan.loan_number = borrower.loan_number
AND amount >= 50000 AND amount <= 70000;
b) CREATE VIEW V1 AS
SELECT customer_name
FROM loan WHERE branch_name = “MUMBAI’
AND amount >= 50000 AND amount <= 70000;
c) CREATE VIEW V1 AS
SELECT customer_name
FROM loan, borrower
WHERE branch_name = “MUMBAI’
AND loan.loan_number = borrower.loan_number
AND amount BETWEEN 50000 AND 70000;
d) CREATE VIEW V1 AS
SELECT customer_name
FROM loan, borrower
WHERE branch_name = “MUMBAI’
AND amount >= 50000, amount <= 70000;
Answer: a), c)
For answers or latest updates join our telegram channel: Click here to join
These are Data Base Management System Week 2 Assignment Answers
Q8. Consider the following instance of StudentDetails(StudName, DeptName, Address, Age) relation.
Identify the correct statements) to get the following output:
a) SELECT * FROM StudentDetails
WHERE Age>=28;
b) SELECT * FROM StudentDetails
WHERE DeptName=^IT’ ;
c) SELECT * FROM StudentDetails
WHERE Age>=28 AND DeptName=^IT’ ;
d) SELECT * FROM StudentDetails
WHERE Age>=28 OR DeptName=^ IT’ ;
Answer: d) SELECT * FROM StudentDetails
WHERE Age>=28 OR DeptName=^ IT’ ;
For answers or latest updates join our telegram channel: Click here to join
These are Data Base Management System Week 2 Assignment Answers
Q9. Consider the following instance of StudentDetails (StudName, DeptName, Address, Age) relation.
Identify the correct SQL command to find the average age of students in the CSE department.
a) SELECT avg(Age) from StudentDetails;
b) SELECT avg(Age) from StudentDetails where DeptName=’CSE’ ;
c) SELECT * from StudentDetails where DeptName=’CSE’ AND avg (Age) ;
d) SELECT * from StudentDetails where DeptName=’CSE’ OR avg(Age) ;
Answer: b) SELECT avg(Age) from StudentDetails where DeptName=’CSE’ ;
For answers or latest updates join our telegram channel: Click here to join
These are Data Base Management System Week 2 Assignment Answers
Q10. Consider the following instance of StudentDetails(StudName, DeptName, Address, Age) relation.
Identify the correct statements) to find the StudName and Address whose Age is greater than the Age of all students in the ‘IT’ department.
a) SELECT StudName, Address from StudentDetails
where Age > (select Age
from StudentDetails
where DeptName = ‘IT’);
b) SELECT StudName, Address from StudentDetails
where Age > all (select Age
from StudentDetails
where DeptName = ‘IT’);
c) SELECT StudName, Address from StudentDetails
where Age > (select max (Age)
from StudentDetails
where DeptName = ‘IT’);
d) SELECT StudName, Address from StudentDetails
where Age > for (select Age
from StudentDetails
where DeptName = ‘IT’);
Answer: b)
For answers or latest updates join our telegram channel: Click here to join
These are Data Base Management System Week 2 Assignment Answers
More Solutions of Data Base Management System: Click Here
More NPTEL Solutions: Click Here
Session: JAN-APR 2023
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
More Solutions of Data Base Management System: Click Here
| The content uploaded on this website is for reference purposes only. Please do it yourself first. |
