Scribd
Upload
129 views10 pages

DBMS Assignment 2 Solutions

The document contains a 9 question assignment on database management systems worth a total of 20 marks. The questions cover topics like different types of joins, foreign key constraints, deleting and truncating tables, aggregation, and updating records. Each question includes multiple choice or multiple selection answers to choose from, along with an explanation of the correct answer.

Uploaded by

balainsai
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
129 views10 pages

DBMS Assignment 2 Solutions

The document contains a 9 question assignment on database management systems worth a total of 20 marks. The questions cover topics like different types of joins, foreign key constraints, deleting and truncating tables, aggregation, and updating records. Each question includes multiple choice or multiple selection answers to choose from, along with an explanation of the correct answer.

Uploaded by

balainsai
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd

Database Management System: Assignment 2

Total Marks : 20

July 9, 2023

Question 1
Consider two relations StyleName and PrintStyle as follows:

StyleName PrintStyle
Element Style Element Color
Flower Floral Square Blue
Square Block Lines Multi

An operation Θ between StyleName and PrintStyle will generate the following output:

Style Print
Element Style Color
Flower Floral NULL
Square Block Blue
NULL Lines Multi

Identify the operation Θ. Marks: 2 MCQ

a) natural join

b) natural left outer join

c) natural right outer join

d) natural full outer join

Answer: d)
Explanation: For natural join, none of the tuples 1 and 3 would not be present in the output.
For left or right outer join, one of the tuples 1 and 3 would not be present in the output. Only
in full outer join all 3 tuples will be present in the output.
Hence, option d) is correct.

1
Question 2
Consider the following CREATE statements: Marks: 2 MCQ

CREATE TABLE faculty(


faculty_id INT PRIMARY KEY,
faculty_name VARCHAR(50) NOT NULL,
);

CREATE TABLE course(


course_id INT PRIMARY KEY,
faculty_id INT NOT NULL,
course_fees INT,
FOREIGN KEY (faculty_id)
REFERENCES faculty (faculty_id)
ON DELETE RESTRICT
);

Identify the correct statement(s) from the following options.

a) If a faculty id value is deleted from the faculty table, the corresponding records in the
course table that use this faculty id will not be deleted.

b) If a faculty id value is deleted from the faculty table, the corresponding records in the
course table that use this faculty id will also be deleted.

c) If a faculty id value is deleted from the faculty table, the foreign key constraint will become
invalid.

d) If a faculty id value is deleted from the faculty table, the corresponding records in the
course table that use this faculty id will be deleted and the foreign key constraint will
become invalid.

Answer: a)
Explanation: ON DELETE RESTRICT clause deletes the corresponding records in the parent
table only. Hence, option (a) is correct.

2
Question 3
Consider the following schema:

• loan(loan number, branch name, amount)

Identify the correct option(s) in the following to delete all rows from loan table.
Marks: 2 MSQ

a) DELETE * FROM loan;

b) DELETE FROM loan;

c) DROP TABLE loan;

d) TRUNCATE TABLE loan;

Answer: b), d)

Explanation: To delete all rows from a table we can use either DELETE or TRUNCATE command
and the syntax for these commands are:

DELETE FROM table name


[WHERE conditions];

Here, the condition is optional and another one is:

TRUNCATE TABLE table name;

So, options b) and d) are correct.

3
Question 4
Consider the following instance of the relation
BIDDINGTAB(PRODUCTID, HIGHESTBID, LOWESTBID, WINNER, BIDDERS)

PRODUCTID HIGHESTBID LOWESTBID WINNER BIDDERS


1EXP03 50000 2000 Chris L. 10
13HP05 45000 10000 Amara D. 50
S0W125 80000 1000 Lewis F.H 25
S0W128 80000 300 Lewis F.H 12
1EXP02 25000 100 John L. 7
Howe many tuples will be returned by the following SQL Query?
SELECT WINNER
FROM BIDDINGTAB
WHERE HIGHESTBID<=(
SELECT AVG(HIGHESTBID)
FROM BIDDINGTAB)
AND WINNER LIKE ‘ L.’ Marks: 2 MCQ

a) 0

b) 2

c) 3

d) 5

Answer: a)
Explanation: The average of HIGHESTBID from the given instance is 56000. Thus, SELECT
WINNER FROM BIDDINGTAB WHERE HIGHESTBID<=( SELECT AVG(HIGHESTBID) FROM BIDDINGTAB)
WINNER
Chris L.
results in 3 tuples . However, none of the WINNER’s name is ‘ L.’. Thus the in-
Amara D.
John L.
tersection of the two conditions in the query produces 0 tuples. Hence, option (a) is correct.

4
Question 5
Consider the following instance of the relation
BIDDINGTAB(PRODUCTID, HIGHESTBID, LOWESTBID, WINNER, BIDDERS)

PRODUCTID HIGHESTBID LOWESTBID WINNER BIDDERS


1EXP03 50000 2000 Chris L. 10
13HP05 45000 10000 Amara D. 50
S0W125 80000 1000 Lewis F.H 25
S0W128 80000 300 Lewis F.H 12
1EXP02 25000 100 John L. 7
If the following SQL Query is executed, which of the following options will be true?
SELECT MAX(BIDDERS), WINNER
FROM BIDDINGTAB
GROUP BY HIGHESTBID, WINNER
ORDER BY WINNER DESC; Marks: 2 MCQ

a) <50, Amara D.> will be the first tuple in the output.

b) <25, Lewis F.H> will be the first tuple in the output.

c) <12, Lewis F.H> will be the first tuple in the output.

d) <12, Lewis F.H> will be the last tuple in the output.

Answer: b)
Explanation: Based on the given SQL Query, the following will be the output
MAX(BIDDERS) WINNER
25 Lewis F.H
7 John L.
10 Chris L.
50 Amara D.
Hence, option (b) is the correct option.

5
Question 6
Consider the following instance of the relational schema PAYSCALE(POSITION, BASE SALARY,
EXPERIENCE)
POSITION BASE SALARY EXPERIENCE
MANAGER 75000 1
MANAGER 90000 5
CLERK 35000 5
DEVELOPER 50000 3
DEVELOPER 70000 5
SCIENTIST 90000 2
Which POSITION will NOT be present in the output generated by the following SQL Query?
SELECT DISTINCT(POSITION)
FROM PAYSCALE
WHERE BASE SALARY<SOME(
SELECT AVG(BASE SALARY)
FROM PAYSCALE
GROUP BY EXPERIENCE); Marks: 2 MCQ

a) CLERK

b) DEVELOPER

c) SCIENTIST

d) MANAGER

Answer: c)
Explanation: As per the syntax and semantics of SQL Queries. Refer to Week 2, slide 8.11.
Hence, option (c) is correct.

6
Question 7
A role Manager has the privilege to perform select, insert, update and delete operations on all
tables of database. A new role Software Engineer is created and the following statement is
executed.

grant Manager to Software Engineer;

Which rights will Software Engineer inherit? Marks: 2 MCQ

a) Only select

b) Only select and delete.

c) Only select, and update but not delete.

d) All rights - select, delete, update.

Answer: d)
Explanation: All the privileges of the role of Manager transferred to Software Engineer.

7
Question 8
Consider the following instance of MountainDetails(MountainName,Altitude,StateName)
relation. Marks: 2 MCQ
MountainDetails
MountainName Altitude StateName
Kangchenjunga 8586 Sikkim
Nanda Devi 7816 Uttarakhand
Trisul 7120 Uttarakhand
Kamet 7756 Uttarakhand
Sandakfu 3636 West Bengal
Saltoro Kangri 7742 Jammu and Kashmir
Reo Purgyill 7742 Himachal Pradesh

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

MountainDetails
MountainName Altitude StateName
Kangchenjunga 8586 Sikkim
Nanda Devi 7816 Uttarakhand
Trisul 7120 Uttarakhand
Kamet 7756 Uttarakhand

a) SELECT * FROM MountainDetails


WHERE StateName IN(‘Sikkim’,‘Uttarakhand’);

b) SELECT * FROM MountainDetails


WHERE StateName TO(‘Sikkim’,‘Uttarakhand’);

c) SELECT * FROM MountainDetails


WHERE StateName AS(‘Sikkim’,‘Uttarakhand’);

d) SELECT * FROM MountainDetails


WHERE StateName TO(‘Sikkim’,‘Uttarakhand’) OR Altitude>=7120;

Answer: a)
Explanation: Output table containing tuples whose StateName is either Sikkim or Uttarak-
hand. The IN operator allows to specify multiple values in a WHERE clause
Hence, option a) is correct.

8
Question 9
Consider the given relational schema: MountainDetails(MountainName, Altitude, StateName)
Marks: 2 MCQ

Identify the correct SQL command that updates the Altitude by 5% for all records whose
StateName ends with character ‘d’.

a) UPDATE MountainDetails
OF Altitude=Altitude*1.05
WHERE StateName LIKE ‘%d’;

b) UPDATE MountainDetails
SET Altitude=Altitude*1.05
WHERE StateName LIKE ‘%d’;

c) UPDATE MountainDetails
AS Altitude=Altitude*1.05
FROM MountainDetails
WHERE StateName LIKE ‘%d’;

d) UPDATE MountainDetails
SET Altitude=Altitude*1.05
WHERE StateName LIKE ‘%d%’;

Answer: b)
Explanation: As per SQL syntax, LIKE ‘%d’ matches StateName having last character as
‘d’. The percent sign represents zero, one, or multiple characters.
The underscore sign ( ) represents one, single character.
General syntax for upadte statement is:

UPDATE Tablename
SET column1 = value1, column2 = value2, ...
WHERE condition;

Hence, option b) is correct.

9
Question 10
Consider the given relational schema: MountainDetails(MountainName, Altitude, StateName)
Marks: 2 MCQ

Identify the correct statement to find the MountainName, Altitude whose Altitude is greater
than or equal to the average Altitude of all Mountains or Altitude in between 6500 and 8000.

a) SELECT MountainName, Altitude


FROM MountainDetails
WHERE Altitude>=(SELECT AVG(Altitude) from MountainDetails)
OR Altitude LIKE(6500, 8000);

b) SELECT MountainName, Altitude


FROM MountainDetails
WHERE Altitude>=(SELECT AVG(Altitude) from MountainDetails)
OR Altitude IN(6500, 8000);

c) SELECT MountainName, Altitude


FROM MountainDetails
WHERE Altitude>=(SELECT AVG(Altitude) from MountainDetails)
OR Altitude BETWEEN 6500 AND 8000;

d) SELECT MountainName, Altitude


FROM MountainDetails
WHERE Altitude>=(SELECT AVG(Altitude) from MountainDetails)
OR Altitude AS(6500, 8000);

Answer: c)
Explanation: The BETWEEN operator selects values within a given range. The values can be
numbers, text, or dates and begin and end values are included. AVG(Altitude) is used to
calculate average altitude of all mountains
Hence, option c) is correct.

10

Common questions

Powered by AI

The LIKE operator in SQL, used in queries such as UPDATE MountainDetails SET Altitude=Altitude*1.05 WHERE StateName LIKE '%d', is applicable when we need to filter records based on pattern matching. In this case, '%d' matches any StateName ending with 'd', where the percent sign denotes any preceding sequence of characters, and the logic targets records fulfilling this pattern .

Role inheritance, as illustrated by the grant Manager to Software Engineer statement, transfers all privileges associated with the role of Manager to the Software Engineer role. This means that the Software Engineer role inherits the ability to perform select, insert, update, and delete operations on all tables, effectively mirroring the Manager's access rights .

The BETWEEN operator in SQL selects values within a specified range, including the start and end values. In the query SELECT MountainName, Altitude FROM MountainDetails WHERE Altitude>=(SELECT AVG(Altitude) from MountainDetails) OR Altitude BETWEEN 6500 AND 8000, it efficiently identifies records meeting the range condition, offering a straightforward method of filtering results based on numerical, date, or textual extremes .

In the query SELECT MAX(BIDDERS), WINNER FROM BIDDINGTAB GROUP BY HIGHESTBID, WINNER ORDER BY WINNER DESC, the results are grouped by HIGHESTBID and WINNER, then the maximum bidders are selected. The order of the output tuples is determined by the ORDER BY clause, which organizes the results in descending order of winners' names, demonstrating the query's structuring affect on output .

The natural full outer join operation is chosen because it includes all tuples from both relations, filling with NULLs where there is no match. In the provided scenario of relations StyleName and PrintStyle, the output contains tuples that appear in only one of the relations with NULLs in the non-matching columns, which aligns with the behavior of a full outer join .

To delete all rows from a table, you can use either the DELETE or the TRUNCATE command. DELETE removes rows one at a time and logs each deletion, allowing for exceptions and triggers, whereas TRUNCATE is more efficient as it deallocates whole data pages, does not log individual row deletions, and resets identity columns but does not allow for WHERE conditions .

The query SELECT DISTINCT(POSITION) FROM PAYSCALE WHERE BASE SALARY<SOME(SELECT AVG(BASE SALARY) FROM PAYSCALE GROUP BY EXPERIENCE) will exclude any positions where the base salary is not less than the average base salary for any experience level. The SCIENTIST position is not included in the output because its base salary does not meet this criterion for any experience group .

The ON DELETE RESTRICT clause in SQL prevents the deletion of a parent row if it has any corresponding child rows in the foreign key relationship, thereby maintaining referential integrity. In the given example with tables faculty and course, deleting a faculty_id from the faculty table will not delete the corresponding records in the course table due to the restrict action, ensuring related records remain intact .

The IN operator in SQL, demonstrated by SELECT * FROM MountainDetails WHERE StateName IN('Sikkim','Uttarakhand'), allows for selecting records with specified column values, providing a compact and readable way to set multiple criterion in a WHERE clause. It simplifies the query for retrieving records when targeting multiple specific values, compared to using multiple OR conditions .

The SQL query SELECT WINNER FROM BIDDINGTAB WHERE HIGHESTBID<=( SELECT AVG(HIGHESTBID) FROM BIDDINGTAB) AND WINNER LIKE ‘ L.’ uses a subquery to calculate the average highest bid, then filters winners whose highest bid is less than or equal to this average while having specific patterns in their name. The query returns no results as none of the names end with ‘ L.' .

You might also like