B using the above schema write the following queries

Info iconThis preview shows pages 2–5. Sign up to view the full content.

View Full Document Right Arrow Icon
b. Using the above schema, write the Following queries in SQL. i. ±ind the names oF all employees who have a child born on or aFter January 1, 2000. ii. ±ind those employees who took an examination For the skill type typing in the city Dayton . iii. List all skill types in the relation Emp . Answer: a. No Answer. b. Queries in SQL. i. Program: select ename from emp as e , e.ChildrenSet as c where ’March’ in ( select birthday . month from c ) ii. Program:
Background image of page 2

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full Document Right Arrow Icon
Practice Exercises 3 select e.ename from emp as e , e.SkillSet as s , s.ExamSet as x where s.type = ’typing’ and x.city = ’Dayton’ iii. Program: select distinct s.type from emp as e , e.SkillSet as s 22.3 Consider the E-R diagram in Figure 22.5, which contains composite, multivalued, and derived attributes. a. Give an SQL schema defnition corresponding to the E-R diagram. b. Give constructors ±or each o± the structured types defned above. Answer: a. The corresponding SQL:1999 schema defnition is given below. Note that the derived attribute age has been translated into a method. create type Name ( frst name varchar(15) , middle initial char , last name varchar(15) ) create type Street ( street name varchar(15) , street number varchar(4) , apartment number varchar(7) ) create type Address ( street Street , city varchar(15) , state varchar(15) , zip code char(6) ) create table customer ( name Name , customer id varchar(10) , address Adress , phones char(7) array[10] , dob date ) method integer age () b. create function Name ( F varchar (15), m char , l varchar (15)) returns Name begin set frst name = F ; set middle initial = m ; set last name = l ; end create function Street ( sname varchar (15), sno varchar (4), ano varchar (7))
Background image of page 3
4 Chapter 22 Object-Based Databases returns Street begin set street name = sname ; set street
Background image of page 4

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full Document Right Arrow Icon
Image of page 5
This is the end of the preview. Sign up to access the rest of the document.

{[ snackBarMessage ]}

Page2 / 6

b Using the above schema write the Following queries in SQL...

This preview shows document pages 2 - 5. Sign up to view the full document.

View Full Document Right Arrow Icon
Ask a homework question - tutors are online