Employee=(ID, name, position, officeN, phoneN, age)// assume the name is unique
ID: 3710319 • Letter: E
Question
Employee=(ID, name, position, officeN, phoneN, age)// assume the name is unique.
Committee =( title, meetingDate, startTime, endTime, location)// You may use <,>,=,!= between dates and times.
Membership= ( ID, title, task) // task = ‘member ‘or ‘chair’
3. List the name of every employee and the tile of the every committee he or she is serving on as a member ordered by the name of employee.
4. List the name of every employee who is serving a on committee that meet in location starts with the letter S and ends with the integer
Explanation / Answer
3. List the name of every employee and the title of the every committee he or she is serving on as a member ordered by the name of employee.
SELECT E.NAME, C.title
from
Employee E, Committee C, Membership M
where
E.ID=M.ID
and C.title=M.title
ORDER BY E.NAME
4. List the name of every employee who is serving a committee that meet in location starts with the letter S and ends with the integer
SELECT E.NAME
from
Employee E, Committee C, Membership M
where
E.ID=M.ID
and C.title=M.title
and REGEXP_LIKE (C.location,'^S(S|s)*[0-9]+$');
Description: REGEXP_LIKE functon is used to match pattern.
^S means - starts with S
(S|s)* means - Any number of character or spaces
[0-9]+$ means - any digit at the end.
If you want to match case insensitive then use 'i' with REGEXP_LIKE function.
SELECT E.NAME
from
Employee E, Committee C, Membership M
where
E.ID=M.ID
and C.title=M.title
and REGEXP_LIKE (C.location,'^S(S|s)*[0-9]+$','i');
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.