QueryExample.dvi
Query Examples in Relational Algebra and SQL
Consider the relation schemas as follows.
works(person name, company name, salary);
lives(person name, street, city);
located in(company name, city);
managers(person name, manager name);
where manager name refers to person name.
a Find the names of the persons who work for company ’FBC’ (company name=’FBC’).
Relational algebra: ��������� ��
������������� �����������������������! ��������”$# %�&(‘(#��*),+�- .�/�0(0
SQL:
Select person_name
From works
Where company_name = ’FBC’
b List the names of the persons who work for company ’FBC’ along with the cities they live in.
Relational algebra:
1�2�3 ���54�67�84:9�;<
=������������� �������>�������������! ��������” # %�&(‘ # �*),+�- .�/�0
?:@ �7A�B�C�D��FEHGI
1�2�3 ���54�67�84:9�;KJ�LI��C�D����
��������� �M
=������������� ��������N ��O�P( 5� ?:@ �7A�B�C�D��FEHG:0
SQL:
Select lives.person_name, city
From works, lives
Where company_name = ’FBC’ and
works.person_name = lives.person_name
c Find the persons who work for company ’FBC’ with a salary of more than 10000. List the names of these
persons along with the streets and cities where they live.
Relational algebra:
Q �7���76 GR�:S7T:4:9�;<
=�������������! ��������" # %�&(' # �VUF67�8W��50
1�2�3 � XYC�� @ 9YC�Z�[ 2 �\
������������� �������>������]����( >^$_�`�`�`�`5� Q �7���76 GR�:S7T:4:9�;50
��������� �M
1�2�3 � XYC�� @ 9YC�Z�[ 2 �aJ�LI��C�D����
SQL:
Select lives.person_name, stree, city
From lives, works
Where lives.person_name = works.person_name and salary > 10000
and works.company_name = ’FBC’
1
d Find the names of the persons who live and work in the same city.
Relational algebra:
XF67�8W�B�6�� 2 �RC 6 GI
������������� ��������N ��O�P( 5�VUF67�8W��,J�LI�:6�� 2 ����� CHG�0
��������� �M
=������������� �������>����O�P( (“���� ��O�P( 5�VXF67�8W�B�6�� 2 �RC 6 G ��
� ����C�D���� 0(0
SQL:
Select person_name
From works, lives, locates_in
Where works.person_name = lives.person_name and
works.company_name = located_in.company_name and
located_in.city = lives.city
e Find the names of the persons who live in the same city and on the same street as their managers.
Relational algebra:
�
� �
��� � �
�
�8���������(��� ��������”���� ����������� ����������
�,����C�D����50 J�L 3R2 G 2 Z��7�:0���
�� ����C�D����50
[ 2�3 �8[7�8���8�7��;�C��8A<
=����� ��P��!����P!"���� ��P��!����P������ ��O�P( ("���� ��O�P( �� �
� �
��� � �
�70
��������� �M
=����� ����������� ����������[ 2�3 �8[7�8���8�7��;�C��8A 0
SQL:
Select e.person_name
From lives e, lives m, managers
Where e.person_name = managers.person_name and
m.person_name = managers.manager_name and
e.street = m.street and e.city = m.city
f Find the names of the persons who do not work for company ’FBC’.
Relational algebra:
Q �7���76 G�4�67�84:9�;<
������������� ���������������������(�( ��������" # %�&(' # �VUF67�8W��50
XF67�8W Q �7���76 GR�\
=������������� �������5�VUF67�8W��50
��������� �M
XF67�8W Q �7���76 GR�� Q �7���76 G�4�67�84:9�;
SQL:
Select person_name
From works
Where person_name not in (Select person_name
From works
Where company_name = ’FBC’)
g Find the persons whose salaries are more than the salary of everybody who work for company ’SBC’.
Relational algebra:
[ 2 � 2 �RC ���:S7T�[ 9�;K
=�R����]����( �������������! ��������" # ��&(' # �VUF67�8W��50
XF67�8W���XYC�� @ [ 2 � 2 �8A�S7T�[59�;,
UF67�8W��!��
�" ��[ 2 � 2 �RC ���:S7T�[ 9�;50
��2 �:GR� B����8� ?:@F2 GF[86 3 �8[59�;,
=������������� �������>������]����( �#��$� ����]����( 5�VXF67�8W���XYC�� @ [ 2 � 2 �8AFS T�[59�;�0
��������� �M
=������������� �������5�VUF67�8W��50% ��2 �:GR� B����8� ?:@F2 GF[86 3 �8[59�;
2
SQL:
Select person_name
From works
Where salary > all (Select salary
From works
Where company_name = ’SBC’)
h Find the names of the companies that is located in every city where company ’SBC’ is located in.
Relational algebra:
� �8�:[ 9�;8;�C��RC ���\
�R��O�P( �������������! ��������” # ��&(‘ # ���:6�� 2 ����� CHG�0
E 3R2 ZRCHG � �8�7;�6 3��F2 G8A � � � �:�:[59�;8B86�� 2 �RC56 G��
=�R�����������! �������5����+���� ��
� ��� 0 � � �8�:[ 9�;8;�C��RC ���
1 67����� 2 �8� A ? �:�F�,
E 3R2 ZRCHG � �8�7;�6 3��F2 G�A � � � �8�7[ 9�;8B�6�� 2 �RC56 GR� �:6�� 2 ����� CHG
��������� �M
=�R�����������! �������5���:6�� 2 ����� CHG�0% �R�����������! �������5� 1 67����� 2 �8� A ? �:�F��0
Relational algebra (another solution):
� �8�:[ 9�;8;�C��RC ���\
�R��O�P( �������������! ��������” # ��&(‘ # ���:6�� 2 ����� CHG�0
��������� �M
�:6�� 2 ����� CHG�� � �8�:[ 9�;8;�C��RC ���
SQL:
Select company_name
From located_in t
Where (Select city
From located_in s
Where t.company_name = s.company_name)
contains (Select city
From located_in s1
Where s1.company_name = ’SBC’)
SQL (another solution):
Select company_name
From located_in t
Where not exists
(Select *
From located_in s
Where s.company_name = ’SBC’ and
s.city not in
(Select city
From located_in l
Where l.company_name = t.company_name))
3