Answer:
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.
