Postgres Db Developer Candidate Exercises V10 Please Provide

Postgres Db Developer Candidate Exercises V10please Provide Candidate

Postgres DB Developer Candidate Exercises v1.0 Please provide candidates interested in our database developer position with the following exercises to complete. This might take as long as 2 hours. They can provide their work to you and you can forward it to us. Any phone screen we have will be largely based upon talking through their answers and the decisions they made. 1.

Schemas I create a table as follows: [local] some_user@some_user=> select current_user; current_user -------------- some_user (1 row) [local] some_user@some_user=> DROP TABLE IF EXISTS some_user.product_lk; DROP TABLE [local] some_user@some_user=> [local] some_user@some_user=> CREATE TABLE some_user.product_lk [more] - > ( [more] ( > product_id INT NOT NULL, [more] ( > product_code TEXT NOT NULL [more] ( > ); CREATE TABLE [local] some_user@some_user=> [local] some_user@some_user=> INSERT INTO some_user.product_lk (product_id, product_code) [more] - > SELECT a.id, 'some_user-' || abs(5 - a.id)::TEXT [more] - > FROM generate_series(1, 5) a(id); INSERT 0 5 After this runs successfully, I notice that when I try and do a select I am not seeing the results I expect.

Instead I see the following: [local] some_user@some_user=> select current_user; current_user -------------- some_user (1 row) [local] some_user@some_user=> select * from product_lk; product_id | product_code ------------+-------------- 1 | public-4 2 | public-3 3 | public-2 4 | public-1 5 | public-0 (5 rows) Can you tell me why I am seeing the results I am seeing and what I could do to have that SQL statement return the rows I expect? 2. Procedural Logic and pl/pgsql Write a pl/pgsql function that: 1. Uses only numeric datatypes and functions—no VARCHAR, CHAR, TEXT, etc. · In other words, character/string functions such as REVERSE, LEFT, RIGHT, SUBSTR, etc are not to be used in your solution. 2.

Accepts a INTEGER parameter 3. If the provided value is less than zero the function should error out with a good error message 4. If the provided value is zero the function should return successfully but report back to the client a message indicating zero was passed 5. Returns a INTEGER value that has the digits from the input parameter in reverse order. If the input value ends in one or more zeroes those zeroes will not appear in the returned numeric value, since they would be leading zeroes 3.

Data Model Based solely on the description of library operations below (without making up any additional facts/requirements) design a data model that avoids update anomalies. You can use text to describe your model if you want. If so be clear as to what columns have primary and foreign keys, as well as what table a FK refers to. You can sketch it out on paper and scan it. You can use Visio.

You can use some other tool if you can export it as a PDF. If you have to make assumptions to complete the exercise, list the assumptions you are making. Employees at a library check out books to patrons. Books have an ISBN and a name. The library sometimes has multiple copies of the same book.

Books have one or more authors. A patron is an individual who has an active (non-expired) library card. For each library card, we store the person’s first and last names and their address. For each employee, we store their employee ID, current salary, first and last name and their address. We also store the employee ID of their current manager.

Each time we check out a book to a patron we need to store the date of the transaction, the employee who checked out the book to the patron, and the library card of the patron. Some employees have library cards. If an employee patron turns in a book late, the fine that they pay is a percentage of their salary. Some employees are authors who have library cards—they are allowed to check out as many books as they like. 4.

SQL Write a query that would return the rows representing days that were hotter than average for the zip code, given a table named ZIP_TEMPS with a primary key {ZIP_CODE, MEASUREMENT_DATE} and a third non-key value NOON_TEMP. NOON_TEMP is the temperature being compared. Example of data follows. ZIP_CODE (PK) MEASUREMENT_DATE (PK) NOON_TEMP /1//2//3//1//2//3/ Give the data above, this should be the output. ZIP_CODE MEASUREMENT_DATE NOON_TEMP /3//1//2/ If you had heard the presidential debate on radio, which candidate would have “wonâ€?

1. If you had viewed the presidential debate on TV, which candidate would have “wonâ€? 2. List two major advances in media technology that benefited JFK. 3.

What is a “Hail Mary†in this trade? 4. For what purpose did Kennedy hire Jac Lowe? 5. What public relations event changed the way Americans view the Whitehouse?

6. What in JFK’s background helped him to be so media savvy? (List two, please.) 7. What in Mrs. Kennedy’s background helped her to be media savvy? 8. Based on the Code of Ethics of the Society for Professional Journalists, what two ethical issues should be considered relative to JFK and the media reporting on his presidency?