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
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));
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
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.
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;
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’ ;
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’ ;
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’);
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
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
