Some interview tests for software developers

I. SQL Database

Table: transaction_flows

id (int, PK)	date (date)	amount (bigint)	cash_flow_file_id (int, allow null, FK)	created_at (datetime, allow null)
1	2013-01-01	1000	1	Null
2	2013-01-02	12000	2	Null
3	2013-01-03	8000	Null	2013-01-05 10:00:08

Table: cash_flow_files

Id (int, PK)	created_at (datetime)	body (text)
1	2013-01-06 23:59:59	'Some text A'
2	2013-01-11 00:00:01	'Some text B'

Question 1.1:

Write a SQL statement to list out all records in transaction_flows table and it's corresponding cash_flow_files body.  For records that do not associated with cash_flow_files, the body is expected to be null.

Question 1.2:

Write a SQL statement to update transaction_flows.created_at if it is null, from the value of corresponding cash_flow_files.created_at value.

II. Programming

Question 2.1

Let fn(int x) be a function that

            returns 'buzz' if x is dividable by 3 only,

            returns 'fuzz' if x is dividable by 5 only,

            returns 'wizz' if x is dividable by 7 only. 

            returns a combination of 'buzz', 'fuzz', 'wizz' if x is dividable by 3 and/or 5 and/or 7.  

            Otherwise return 'x' string.

Write unit test(s) for fn(int x) so that if all test(s) passed, the function should be implemented correctly.

(May use any programming language and syntax on any unit test framework)

Question 2.2

Implement fn(int x) described in Question 2.1 in any programming language.

Question 2.3

Implement a function bool IsSymmetric(string s) such that if reverse of s is equal to s, return true, otherwise, return false.

III. System Design

Background Story

            Peter is a movie enthusiast such that he got lots of VCDs, DVDs and BlueRay discs at home.  From time to time he buys new discs and loves to share with his friends.  Now he want to build a system to manage he discs and trace the discs borrowed to his friends.  From his non-technical perspective, he list out his requirements:

• Keep a movie database.  Including the following attributes.

◦       Name of movie

◦       Cast

◦       Movie category

◦       Movie release year

◦       Disc bought date

◦       Disc price

• The movie database is searchable from UI.
• Keep track of the discs borrowed to his friends

◦       When was a disc borrowed.

◦       When a disc is returned.

◦       An email alert will be send to Peter if a disc was borrowed for over 1 month.

• Only Peter can create new login account for his friends.

◦       Peter's friend will receive an invitation email once account created.

◦       His friend can login the system by their Facebook or Gmail login.

• What Peter's friend can do on the system:

◦       Browse the movie collections.

◦       Movie reservation

▪       Select a movie.  And select when they want to get it from Peter's home.

▪       When reserved, Peter will receive an email.

Question 3.1

Create a system design includes hardware spec, software spec, system architecture, ER-diagram, and Page UI draft.