Challenges to Evaluate Programming Skills

The following challenges may help evaluate the solutions providers’ programming skills:

01.  SQL

There are three tables with data as displayed below.  TABLE_1 and TABLE_2 are lookup tables and TABLE_3 is the main table.  Their data types are as they appear in their respective values. 

The challenge is to create a view (VIEW_1 below), using all those three tables, with the following specifications:

1. Display all columns of TABLE_3 and include a new column COL_4 with the specifications as mentioned below
2. Display all records of TABLE_3
3. No modifications to any involved tables or creations of any intermediate tables are allowed

The specifications for the new column COL_4 of TABLE_3 are as follows:

1. The first two digits of COL_2 of TABLE_3 are replaced by COL_2 of TABLE_1
2. The second two digits of COL_2 of TABLE_3 stay the same
3. If exists, the fifth digit of COL_2 of TABLE_3 is replaced by COL_2 of TABLE_2
4. COL_4 should not contain any value if the value in COL_3 of TABLE_3 is less than 1

  

TABLE_1		TABLE_2		TABLE_3
COL_1	COL_2	COL_1	COL_2	COL_1	COL_2	COL_3
11	S	1	A	10	11121	1.29
31	SH	2	B	20	11120	2.00
53	HS	3	C	30	31322	2.55
75	H	4	D	40	31322	0.95
				50	53084	1.23
				60	5301	3.09
				70	40764	1.50

VIEW_1
COL_1	COL_2	COL_3	COL_4
10	11121	1.29	S12A
20	11120	2.00
30	31322	2.55	SH32B
40	31322	0.95
50	53084	1.23	HS08D
60	5301	3.09
70	40764	1.50

02. PL/SQL

Importing data from a logical backup of an existing database instance to a target instance requires the existing instance’s tablespaces to be created in the target instance to avoid overwriting of any datafiles of other instances on the target database server.  If there are many tablespaces in the existing instance and each contains many datafiles, creating tablespaces in the target instance can be time-consuming and error-prone.

The challenge is to write the following stored procedure that is able to generate CREATE TABLESPACE statements from an existing database instance, where each tablespace will contain a single datafile with a size equal to the total space used by all the datafiles of an existing tablespace.

           

genCreateTsStmts([<tmp_init_size>][,<tmp_next_size>])

           

            where,

·       <tmp_init_size> (optional) is the initial SIZE value for the tempfile of a temporary tablespace in MB

·       <tmp_next_size> (optional) is the NEXT size value for the tempfile of a temporary tablespace in MB

Sample Output:

CREATE TABLESPACE system

    DATAFILE '<datafile_path>\system01.dbf'

        SIZE 334M

        AUTOEXTEND ON NEXT 8M

    BLOCKSIZE 16K

    EXTENT MANAGEMENT LOCAL

    SEGMENT SPACE MANAGEMENT MANUAL

;

CREATE TABLESPACE sefs_ras_blk

    DATAFILE '<datafile_path>\sefs_ras_blk01.dbf'

        SIZE 6804M

        AUTOEXTEND ON NEXT 128M

  BLOCKSIZE 16K

  EXTENT MANAGEMENT LOCAL UNIFORM SIZE 4096K

  SEGMENT SPACE MANAGEMENT AUTO

;

CREATE TEMPORARY TABLESPACE sefs_tmp

    TEMPFILE '<tempfile_path>\sefs_tmp01.dbf'

        SIZE 512M

        AUTOEXTEND ON NEXT 32M

  EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1024K

;

CREATE UNDO TABLESPACE undotbs1

    DATAFILE '<undofile_path>\undotbs101.dbf'

        SIZE 15M

        AUTOEXTEND ON NEXT 1M

    EXTENT MANAGEMENT LOCAL

;

...

03. T-SQL

There is a requirement to copy all the tables of an SQL Server database to an Access or an Oracle database.  It also requires an option of prefixing the table schema with the name of the table. 

The challenge is to write the following stored procedure that is able to generate SQL INSERT Statements for tables/views of MS Access or any other Enterprise RDBMSs (e.g., Oracle, SQL Server etc.) databases and handle about a hundred columns.

genInsertStmts '<table_name>'  [,{0|1}] [,'<table_schema>']

            where,

·       <table_name> is the name of the table/view from which the script is to be generated

·       0|1 indicates whether to prefix the table/view with the schema. When omitted, the default is 0 to exclude the schema

·       <table_schema> is the name of the table/view schema.  When omitted, its default value is “dbo”

Sample Output:

INSERT INTO [HumanResources].[Employee] ([EmployeeID],[NationalIDNumber],[ContactID],[LoginID],[ManagerID],[Title],[BirthDate],[MaritalStatus],[Gender],[HireDate],[SalariedFlag],[VacationHours],[SickLeaveHours],[CurrentFlag],[rowguid],[ModifiedDate]) VALUES (1,'14417807',1209,'adventure-works\guy1',16,'Production Technician - WC60','1972-05-15 00:00:00','M','M','1996-07-31 00:00:00',0,21,30,1,'AAE1D04A-C237-4974-B4D5-935247737718','2004-07-31 00:00:00');

…

04. Python

GeoGratis (http://www.geogratis.ca) provides geospatial data free of charge.  Their data is categorized by mapsheets that contain several features (spatial layers) in ESRI Shapefile formats.  One category of features (e.g., river, road etc.) may be available in several mapsheets.  The data is available through their FTP site.  There was a need to collect all the mapsheets covering the province of Saskatchewan and its surrounding areas and store all the features available in the mapsheets in an Oracle database.

The challenge is to write Python scripts for each of the following tasks:

1. Download zipped files from the FTP site
2. Download zipped files of unsuccessful downloads by reading a Log file generated in the previous task
3. Unzip the zipped files to their corresponding mapsheets
4. List all categories of features available from all mapsheets
5. Create directories for all the feature categories
6. Separate all the feature categories, each containing 4 file types, from all the mapsheets into their respective directories
7. Create empty ArcSDE featureclass for each of the listed feature categories
8. Load all the feature categories into their corresponding ArcSDE featureclasses

05. Miscellaneous

The following challenge that appears to be very simple requires more programming knowledge (e.g., choice of programming languages) than the coding-only experience:

Convert a string with apostrophes to another string with apostrophes escaped, as follows:

From:

VALUES (‘Masud’s vision’,’Kris’ plan’, ‘Cheng’s implementation’);

To:

VALUES (‘Masud’’s vision’,’Kris’’ plan’, ‘Cheng’’s implementation’);

Note that:

·       Apostrophes in the From string are before and after the letter “s”

·       An apostrophe in the From string is escaped by another apostrophe (two apostrophes for one) in the To string.