Question

Consider the following three tables where keys are underlined: Graphs(graphid, graphname), Nodes(nodeid, nodename, nodeweight, graphid), Edges(parentid, childid, edgeweight, graphid), formulate a SQL statement for each query in the following.

7. Return all graphids of those graphs such that the graph has at least 1 node that is not connected to any other nodes (i.e. neither as a parent nor as a child).

Answer 1

SQL command:

SELECT graphid FROM Nodes WHERE nodeid NOT IN (SELECT DISTINCT Nodes.nodeid FROM Nodes JOIN Edges ON Nodes.nodeid = Edges.parentid OR Nodes.nodeid = Edges.childid);

Let me explain the query.

• SELECT DISTINCT Nodes.nodeid FROM Nodes JOIN Edges ON Nodes.nodeid = Edges.parentid OR Nodes.nodeid = Edges.childid
  • It joins tables Nodes and Edges on nodeid from the nodes table and parentid / childid from the edges table. From this joined table, it select distinct nodeid. These nodeid represents nodes which have an edge.
• SELECT graphid FROM Nodes WHERE nodeid NOT IN ( SUBQUERY )
  • ​​​​​​​It then select all graphids from the table Nodes which have a node other than those returned by the above subquery.