amz_authors

The amz_authors table is a set of data that describes information about Amazon content authors. It has the following columns:

• id: A unique identifier for each Amazon content author that consists of 24 characters to 26 characters in length and begins with the character ‘a’.

• name: The name of the Amazon content author, which can be any alphanumeric string up to 99 symbols long. It is case sensitive and should not contain periods, commas or spaces. New name entries create a primary key for this table.

• description: An optional text field that contains a short description of the Amazon content author. This field provides additional context about the author’s role or expertise in relation to Amazon content. Values in this column consist only of ASCII characters with an unlimited number of bytes except for one character, which is the string ‘\n’.

• author_id: An alias for the key ‘id’ and is case sensitive and provides a unique identifier for each author that is used for reference purposes as well as to create secondary keys, such as salespeople or partners.

• status: A text field that describes Amazon’s status of an Amazon Content Author. It can be one of the following values: ‘submitted’, ‘published’ or ‘deleted’.

• status_code: An alias for the key (‘status’) and is case sensitive. Value for this record is unique identifier used to represent a value in both the data model and data stores.

• updated_at: Contains a timestamp that indicates when the Amazon Content Author’s status was last updated. This field consists of a sequence of characters representing the date and time in UTC timezone. The format should be YYYY/MM/DDTHH:MM:SS±HHMM, where ‘T’ is the delimiter between year-month and day-time components, “Z” is for UTC time offset (GMT).

• created_at: Contains a timestamp of when Amazon Content author was created. The format should be YYYY/MM/DDTHH:MM:SS±HHMM, where ‘T’ is the delimiter between year-month and day-time components, “Z” is for UTC time offset (GMT).

• media_url_id: Contains the URL of content created by Amazon Content Author.

Assume you are a Robotics Engineer who needs to fetch certain data from this table. The conditions are: 1) You need all records with status = ‘submitted’, updated at most 1 week ago. 2) The selected entries should have no space or character that isn’t an ASCII character in them. 3) If two authors have the same name, keep one of them; disregard the other. 4) Keep the record for which id is smallest to maintain efficiency of your data entry system.

Question: What SQL query would you write?

You need to use WHERE clause in order to specify the requirements in the conditions mentioned. Also, because this information comes from a table with an unspecified amount of items, a sub-queries might be needed for efficient querying.

The first requirement requires you only want ‘submitted’ status and records updated at most 1 week ago. This can be written using WHERE clause as SELECT * FROM amz_authors WHERE (status = ‘submitted’) AND ((updated_at - CURRENT_TIME)/3600/24 <= 7)

The second requirement will require you to use the ISKIND keyword and a subquery, where each of those is going to check that there can be no spaces or characters outside ASCII. We’re checking both because the second condition could cause the SQL query to return false even if it matches the other conditions due to non-ASCII strings. The use of ISKIND keyword is intended for use with string data which has a certain length, not necessarily the same length across all records in the table. The subquery will provide you with an array whose elements need to check this condition, and if any of the array components passes through this query, they all do. The SELECT statement on the ISKIND keyword will return records where string data with a known length is smaller than the character size in bytes of each record - which in turn means it’s not a non-ASCII content.

In order to address requirement for sorting records: ORDER BY id ASC sorts the table from smallest to largest ‘id’ values, ensuring the selected records are ordered by their ID number. This will result in efficiency of your data entry system as you can manage small datasets more easily. It is an important step if the ‘id’ field also has non-numerical values which could