SQL question

Question

I was given a quiz to do by an employer but my knowledge of SQL is very limited so if anyone can explain these to me it would help me alot

1.
Given the following table definition, select all city name in descending order

// TABLE cities
// id INTEGER NOT NULL PRIMARY KEY
// name VARCHAR(30) NOT NULL

2.
Given the following table definitions, write a select statement to get the following values ClaimantName, PolicyNumber, UMR, InsuredType, Insurer using joins

// TABLE Claims
// ID INTEGER NOT NULL PRIMARY KEY
// ClaimantName VARCHAR(30) NOT NULL
// InsurerID INTEGER NOT NULL FOREIGN KEY
// BrokerID INTEGER NOT NULL FOREIGN KEY
// PolicyID INTEGER NOT NULL FOREIGN KEY

// TABLE Policies
// ID INTEGER NOT NULL PRIMARY KEY
// PolicyNumber VARCHAR(30) NOT NULL
// BinderID INTEGER NOT NULL FOREIGN KEY
// UMR VARCHAR(30) NOT NULL
// Insured Type VARCHAR(30) NOT NULL

// TABLE Insurers
// ID INTEGER NOT NULL PRIMARY KEY
// CompanyName VARCHAR(30) NOT NULL

// TABLE Binders
// ID INTEGER NOT NULL PRIMARY KEY
// BinderName VARCHAR(30) NOT NULL
// CarrierID INTEGER NOT NULL FOREIGN KEY

// TABLE Carrier
// ID INTEGER NOT NULL PRIMARY KEY
// CarrierName VARCHAR(30) NOT NULL

Answer 1

1.
SELECT name
FROM cities
ORDER BY name DESC;

2.
SELECT C.ClaimantName, P.PolicyNumber, P.UMR, P.InsuredType, I.CompanyName
FROM Claims C
INNER JOIN Policies P
ON C.PolicyID = P.ID
INNER JOIN Insurers I
ON C.InsurerID = I.ID

There is a little problem here with quiz #2;

● In the field selection list you have "InsuredType", but the structure of Policies table says it is "Insured Type".

● In the field selection list you have "Insurer", but the structure of Insurers table says that it doesn't have any such a field, so I figured it was supposed to be "CompanyName" field.

I don't know who made a typo here, but I think giving an instruction with typos such as this is kinda misleading and unfair : )

Answer 2

Given the following data definition, select all city names in descending order:

TABLE cities id INTEGER NOT NULL PRIMARY KEY name VARCHAR(30) NOT NULL

See the example case for more details.