Question

Consider the following table definitions

create table node(
node_id integer primary key,
node_color varchar(10));

create table edge(
edge_id integer primary key,
origin_id integer,
destination_id integer,
foreign key (origin_id) references node(node_id),
foreign key (destination_id) references node(node_id));

What is the result of the following query?
select node_id, node_color, destination_id
from node, edge;

		An inner join of the tables node and edge that lists origin node_id and node_color together with the node_id of the destination node for all those nodes that have outgoing edges.
		An outer join of the tables node and edge that lists origin node_id and node_color together with the node_id of the destination node and includes nodes that do not have outgoing edges.
		An outer join of the tables node and edge that lists origin node_id and node_color together with the node_id of the destination node and includes nodes that do not have incoming edges.
		An outer join of the tables node and edge that lists origin node_id and node_color together with the node_id of the destination node and includes both nodes that do not have outgoing edges and nodes that do not have incoming edges.
		None of the above

Answer 1

I have inserted values into the tables and then written SQL queries for the same, output has been attached below.

Answer: An inner join of the tables node and edge that lists origin node_id and node_color together with the node_id of the destination node for all those nodes that have outgoing edges.

select node_id,node_color,destination_id
from node join edge

here join refers to inner join

node_id	node_color	destination_id
1	red	4
1	red	5
1	red	6
2	green	4
2	green	5
2	green	6
3	Blue	4
3	Blue	5
3	Blue	6
4	purple	4
4	purple	5
4	purple	6
5	magenta	4
5	magenta	5
5	magenta	6
6	cyan	4
6	cyan	5
6	cyan	6

select node_id,node_color,destination_id
from node,edge;

node_id	node_color	destination_id
1	red	4
1	red	5
1	red	6
2	green	4
2	green	5
2	green	6
3	Blue	4
3	Blue	5
3	Blue	6
4	purple	4
4	purple	5
4	purple	6
5	magenta	4
5	magenta	5
5	magenta	6
6	cyan	4
6	cyan	5
6	cyan	6