程序代写代做代考 SQL QueryExample.dvi

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