E xercise 6.3.2 : Write the following queries, based on the database schema Clas

Question

E xercise 6.3.2 : Write the following queries, based on the database schema
Classes(class, type, country, numGuns, bore, displacement)
Ships(name, class, launched)
Battles(name, date)
Outcomes(ship, battle, result)
of Exercise 2.4.3. You should use at least one subquery in each of your answers
and write each query in two significantly different ways (e.g., using different
sets of the operators EXISTS, IN, ALL, and ANY).

! b) Find the classes of ships, at least one of which was sunk in a battle.

d) Find the battles in which ships of the Kongo class participated.

Exercise 6.5.1: Write the following database modifications, based on the
database schema
Product(maker, model, type)
PC(model, speed, ram, hd, price)
Laptop(model, speed, ram, hd, screen, price)
Printer(model, color, type, price)
of Exercise 2.4.1. Describe the effect of the modifications on the data of that
exercise.
a) Using two INSERT statements, store in the database the fact that PC
model 1100 is made by manufacturer C, has speed 3.2, RAM 1024, hard
disk 180, and sells for $2499.
! b) Insert the facts that for every PC there is a laptop with the same manufacturer, speed, RAM, and hard disk, a 17-inch screen, a model number
1100 greater, and a price $500 more.

Explanation / Answer

Please note that the answer for exercise 6.5.1 (database modificartions) are not given since there is no information on the current configuration of database.

Screenshots:

Answers:

Exercise 6.3.2:

Question b: Please note that the following query assumes that Outcomes.Ship and Ships.name contain the same data and is the common columns among tables Outcomes and Ships. Also, the results column in table Outcomes contain the value "Sunk" for the ones which are sunk.

--Find the classes of ships, at least one of which was sunk in a battle.

--Subquery using EXISTS
Select Ships.Class from Ships where EXISTS (Select * from Outcomes where Outcomes.Ship = Ships.name and result = 'Sunk')

--Subquery using IN

Select Ships.Class from Ships where Ships.name in (Select Ship from Outcomes where result = 'Sunk')

--Find the battles in which ships of the Kongo class participated.

Select * from battles where exists (Select * from Outcomes INNER JOIN Ships ON Outcomes.Ship = Ships.name where
Outcomes.battle = Battles.name and Ships.Class = 'Kongo')

Select * from battles where name in (Select battle from Outcomes INNER JOIN Ships ON Outcomes.Ship = Ships.name where Ships.Class = 'Kongo')

EXERCISE 6.5.1:

Question A: Here it is assumed that, the type column of table Product stores the value as either 'PC' for PC's and 'Laptop' for Laptops.

--Using two INSERT statements, store in the database the fact that PC
--model 1100 is made by manufacturer C, has speed 3.2, RAM 1024, hard
--disk 180, and sells for $2499.
Insert into Product(maker, model, type) values ('C','1100','PC')
Insert into PC(model, speed, ram, hd, price) values ('1100',3.2,1024,180,2499)

--Insert the facts that for every PC there is a laptop with the same manufacturer, speed, RAM, and hard disk, a 17-inch screen, a model number
--1100 greater, and a price $500 more.
Insert into Product(maker, model, type) values ('C','1200','Laptop')
Insert into Laptop(model, speed, ram, hd,screen, price) values ('1200',3.2,1024,180,17.0,2999)

Related Questions

Navigate

• Browse (All)
• Browse E
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.