The kings and queens of england are listed in a relation kings(name, nickname, house, beginreign, endreign). their name is unique, either using a roman numeral to distinguish them, e. g., 'edward i' or 'edward ii', or in a few cases using their nickname, e. g., 'edward the confessor'. the attribute nickname is an additional appelation, if they have one and it is not used in their name to distinguish them, e. g., 'the unready'. the value of nickname is null if there is no nickname. the attribute house is the dynasty, e. g., 'tudor'. attribute beginreign and endreign are integers, the first and last years, respectively, that the king or queen was on the throne. there is also a relation parents(child, parent). both attributes are the names of kings or queens, with the obvious connection that the first is a child of the second. write the following queries: who was king in the year 1000? give the name and nickname. find all the pairs of kings or queens (a, b) such that a was the great grandchild of b. find the name and nickname of all kings or queens that have a nickname that does not begin with "the". find the names of those kings or queens that were the parent of two or more kings or queens. list each such person only once. find for each house the number of kings or queens of that house. several times in british history, kings or queens have deposed one another, so that their reigns overlapped. find all such pairs, listing the pairs in both orders; i. e., list both (a, b) and (b, a). however, be careful not to list pairs a and b where the only overlap is that a's reign ended in the same year that b's began, or vice-versa.

The answers to each question are explained below

Explanation:

Who was king in the year 1000? Give the name and nickname.

Query:

       SELECT name, nickname FROM Kings

      WHERE beginReign <=1000 AND endReign >=1000;

Explanation:

SELECT is used to query the database and get back the specified fields.

name and nickname are columns of Kings table.

FROM is used to query the database and get back the preferred information by specifying the table name.

Kings is a table name.

WHERE is used to specify a condition based on which the data is to be retrieved. The conditions are as follows:

beginReign <=1000

endReign >=1000

AND clause between the conditions indicate that both the conditions must be true.

Find all the pairs of kings or queens (A,B) such that A was the great grandchild of B.

Query:

       SELECT p.child, ggp.parent

       FROM Parents p, Parents gp, Parents ggp

       WHERE p.parent = gp.child AND gp.parent = ggp.child;

Explanation:

SELECT is used to query the database and get back the specified fields.

child is a column of Parents table aliased as p(parent).

parent is a column of Parents table aliased as ggp (great grand father).

FROM is used to query the database and get back the preferred information by specifying the table name.

parents is a table name. Three aliases named p(parent), gp(grand father) and ggp(great grand father) are created.

WHERE is used to specify a condition based on which the data is to be retrieved. The conditions are as follows:

p.parent = gp.child

gp.parent = ggp.child;

AND clause between the conditions indicate that both the conditions must be true.

Find the names of those kings or queens that were the parent of two or more kings or queens. List each such person only once.

Query:

       SELECT parent FROM Parents

       GROUP by parent

       HAVING COUNT(*) > 1;

Explanation:

SELECT is used to query the database and get back the specified fields.

parent is a column of Parents table .

FROM is used to query the database and get back the preferred information by specifying the table name.

parents is a table name.

Group by clause is used to group the data on column parent.

Having clause specifies the condition based on which the data is to be retrieved.

Find for each house the number of kings or queens of that house.

Query:

       SELECT house, COUNT(name)

       FROM Kings

       GROUP by house;

Explanation:

SELECT is used to query the database and get back the specified fields.

house is a column of Kings table .

FROM is used to query the database and get back the preferred information by specifying the table name.

Kings is a table name.

Group by clause is used to group the data on column house.

Several times in British history, kings or queens have deposed one another, so that their reigns overlapped. Find all such pairs, listing the pairs in both orders; i.e., list both (A,B) and (B,A). However, be careful not to list pairs A and B where the only overlap is that A's reign ended in the same year that B's began, or vice-versa.

Query:

      SELECT k1.name, k2.name FROM Kings k1, Kings k2

      WHERE k1.name <> k2.name

      AND k1.beginReign < k2.beginReign

      AND k2.beginReign < k1.beginReign;

Explanation:

SELECT is used to query the database and get back the specified fields.

name is a column of Kings table.

FROM is used to query the database and get back the preferred information by specifying the table name.

Kings is a table name. Two aliases named k1 and k2 are created.

WHERE is used to specify a condition based on which the data is to be retrieved. The conditions are as follows:

k1.name <> k2.name

k1.beginReign < k2.beginReign

k2.beginReign < k1.beginReign;

AND clause between the conditions indicate that both the conditions must be true.