Friday, August 19, 2016

SQL Joins

SQL Joins are used to relate information in different tables.

A Join query retrieves rows from two or more tables.

A SQL Join condition is used in the SQL 
WHERE Clause of select, update, delete statements.


 Joins in SQL

The SQL Syntax for joining two tables is:
SELECT col1, col2, col3...
FROM table_name1, table_name2 
WHERE table_name1.col2 = table_name2.col1; 
If a sql join condition is omitted or if it is invalid the join operation will result in a Cartesian product. The Cartesian product returns a number of rows equal to the product of all rows in all the tables being joined. For example, if the first table has 20 rows and the second table has 10 rows, the result will be 20 * 10, or 200 rows. This query takes a long time to execute.

Joins Example

Lets use the below two tables to explain the sql join conditions.
Database table "product";
product_idproduct_namesupplier_nameunit_price
100CameraNikon300
101TelevisionOnida100
102RefrigeratorVediocon150
103IpodApple75
104MobileNokia50
Database table "order_items";
order_idproduct_idtotal_unitscustomer
510010430Infosys
51011025Satyam
510210325Wipro
510310110TCS
SQL Joins can be classified into Equi join and Non Equi join.
1) SQL Equi joins
It is a simple sql join condition which uses the equal sign as the comparison operator. Two types of equi joins are SQL Outer join and SQL Inner join.
For example: You can get the information about a customer who purchased a product and the quantity of product.
2) SQL Non equi joins
It is a sql join condition which makes use of some comparison operator other than the equal sign like >, <, >=, <=
1) SQL Equi Joins:
An equi-join is further classified into two categories:
a) SQL Inner Join
b) SQL Outer Join 

a) SQL Inner Join:

All the rows returned by the sql query satisfy the sql join condition specified.

SQL Inner Join Example:

If you want to display the product information for each order the query will be as given below. Since you are retrieving the data from two tables, you need to identify the common column between these two tables, which is the product_id.
The query for this type of sql joins would be like,
SELECT order_id, product_name, unit_price, supplier_name, total_units 
FROM product, order_items 
WHERE order_items.product_id = product.product_id; 
The columns must be referenced by the table name in the join condition, because product_id is a column in both the tables and needs a way to be identified. This avoids ambiguity in using the columns in the SQL SELECT statement.
The number of join conditions is (n-1), if there are more than two tables joined in a query where 'n' is the number of tables involved. The rule must be true to avoid Cartesian product.
We can also use aliases to reference the column name, then the above query would be like,
SELECT o.order_id, p.product_name, p.unit_price, p.supplier_name, o.total_units 
FROM product p, order_items o 
WHERE o.product_id = p.product_id; 

b) SQL Outer Join:

This sql join condition returns all rows from both tables which satisfy the join condition along with rows which do not satisfy the join condition from one of the tables. The sql outer join operator in Oracle is ( + ) and is used on one side of the join condition only.
The syntax differs for different RDBMS implementation. Few of them represent the join conditions as "sql left outer join", "sql right outer join".
If you want to display all the product data along with order items data, with null values displayed for order items if a product has no order item, the sql query for outer join would be as shown below:
SELECT p.product_id, p.product_name, o.order_id, o.total_units 
FROM order_items o, product p 
WHERE o.product_id (+) = p.product_id; 
The output would be like,
product_idproduct_nameorder_idtotal_units
----------------------------------------------------
100Camera
101Television510310
102Refrigerator51015
103Ipod510225
104Mobile510030
NOTE: If the (+) operator is used in the left side of the join condition it is equivalent to left outer join. If used on the right side of the join condition it is equivalent to right outer join.

SQL Self Join:

A Self Join is a type of sql join which is used to join a table to itself, particularly when the table has a FOREIGN KEY that references its own PRIMARY KEY. It is necessary to ensure that the join statement defines an alias for both copies of the table to avoid column ambiguity.
The below query is an example of a self join,
SELECT a.sales_person_id, a.name, a.manager_id, b.sales_person_id, b.name
FROM sales_person a, sales_person b 
WHERE a.manager_id = b.sales_person_id;

2) SQL Non Equi Join:

A Non Equi Join is a SQL Join whose condition is established using all comparison operators except the equal (=) operator. Like >=, <=, <, >

SQL Non Equi Join Example:

If you want to find the names of students who are not studying either Economics, the sql query would be like, (lets use student_details table defined earlier.)
SELECT first_name, last_name, subject 
FROM student_details 
WHERE subject != 'Economics' 
The output would be something like,
first_namelast_namesubject
---------------------------------------
AnajaliBhagwatMaths
ShekarGowdaMaths
RahulSharmaScience
StephenFlemingScience

SQL Integrity Constraints

Integrity Constraints are used to apply business rules for the database tables.

The constraints available in SQL are Foreign Key, Not Null, Unique, Check.
Constraints can be defined in two ways 
1) The constraints can be specified immediately after the column definition. This is called column-level definition.

2) The constraints can be specified after all the columns are defined. This is called table-level definition. 


1) Primary key:

Defines a column or combination of columns which uniquely identifies each row in the table.
Syntax to define a Primary key at column level:
column name datatype [CONSTRAINT constraint_name] PRIMARY KEY
Syntax to define a Primary key at table level:
[CONSTRAINT constraint_name] PRIMARY KEY (column_name1,column_name2,..)
  • column_name1, column_name2 are the names of the columns which define the primary Key.
  • The syntax within the bracket i.e. [CONSTRAINT constraint_name] is optional.
For Example: To create an employee table with Primary Key constraint, the query would be like.
Primary Key at column level:
CREATE TABLE employee 
( id number(5) PRIMARY KEY, 
name char(20), 
dept char(10), 
age number(2), 
salary number(10), 
location char(10) 
);
or
CREATE TABLE employee
( id number(5) CONSTRAINT emp_id_pk PRIMARY KEY, 
name char(20),
dept char(10),
age number(2),
salary number(10),
location char(10)
);
Primary Key at column level:
CREATE TABLE employee 
( id number(5), 
name char(20),
dept char(10),
age number(2),
salary number(10),
location char(10),
CONSTRAINT emp_id_pk PRIMARY KEY (id)
);
Primary Key at table level:
CREATE TABLE employee 
( id number(5), NOT NULL,
name char(20),
dept char(10),
age number(2),
salary number(10),
location char(10),
ALTER TABLE employee ADD CONSTRAINT PK_EMPLOYEE_ID PRIMARY KEY (id)
);

2) Foreign key or Referential Integrity :

This constraint identifies any column referencing the PRIMARY KEY in another table. It establishes a relationship between two columns in the same table or between different tables. For a column to be defined as a Foreign Key, it should be a defined as a Primary Key in the table which it is referring. One or more columns can be defined as Foreign key.
Syntax to define a Foreign key at column level:
[CONSTRAINT constraint_name] REFERENCES Referenced_Table_name(column_name)
Syntax to define a Foreign key at table level:
[CONSTRAINT constraint_name] FOREIGN KEY(column_name) REFERENCES referenced_table_name(column_name);
For Example:
1) Lets use the "product" table and "order_items". 

Foreign Key at column level:
CREATE TABLE product 
( product_id number(5) CONSTRAINT pd_id_pk PRIMARY KEY, 
product_name char(20),
supplier_name char(20),
unit_price number(10)
);
CREATE TABLE order_items
( order_id number(5) CONSTRAINT od_id_pk PRIMARY KEY,
product_id number(5) CONSTRAINT pd_id_fk REFERENCES, product(product_id),
product_name char(20),
supplier_name char(20),
unit_price number(10)
);
Foreign Key at table level:
CREATE TABLE order_items
( order_id number(5) ,
product_id number(5),
product_name char(20),
supplier_name char(20),
unit_price number(10)
CONSTRAINT od_id_pk PRIMARY KEY(order_id),
CONSTRAINT pd_id_fk FOREIGN KEY(product_id) REFERENCES product(product_id)
);
2) If the employee table has a 'mgr_id' i.e, manager id as a foreign key which references primary key 'id' within the same table, the query would be like,
CREATE TABLE employee
( id number(5) PRIMARY KEY,
name char(20),
dept char(10),
age number(2),
mgr_id number(5) REFERENCES employee(id),
salary number(10),
location char(10) 
);

3) SQL Not Null Constraint :

This constraint ensures all rows in the table contain a definite value for the column which is specified as not null. Which means a null value is not allowed.
Syntax to define a Not Null constraint:
[CONSTRAINT constraint name] NOT NULL
For Example: To create a employee table with Null value, the query would be like
CREATE TABLE employee
( id number(5),
name char(20) CONSTRAINT nm_nn NOT NULL,
dept char(10),
age number(2),
salary number(10),
location char(10) 
);

4) SQL Unique Key:

This constraint ensures that a column or a group of columns in each row have a distinct value. A column(s) can have a null value but the values cannot be duplicated.
Syntax to define a Unique key at column level:
[CONSTRAINT constraint_name] UNIQUE
Syntax to define a Unique key at table level:
[CONSTRAINT constraint_name] UNIQUE(column_name)
For Example: To create an employee table with Unique key, the query would be like,
Unique Key at column level:
CREATE TABLE employee
( id number(5) PRIMARY KEY,
name char(20),
dept char(10),
age number(2),
salary number(10),
location char(10) UNIQUE 
);
or
CREATE TABLE employee
( id number(5) PRIMARY KEY,
name char(20),
dept char(10),
age number(2),
salary number(10),
location char(10) CONSTRAINT loc_un UNIQUE 
);
Unique Key at table level:
CREATE TABLE employee
( id number(5) PRIMARY KEY,
name char(20),
dept char(10),
age number(2),
salary number(10),
location char(10),
CONSTRAINT loc_un UNIQUE(location) 
);

5)  Check Constraint :

This constraint defines a business rule on a column. All the rows must satisfy this rule. The constraint can be applied for a single column or a group of columns.
Syntax to define a Check constraint:
[CONSTRAINT constraint_name] CHECK (condition)
For Example: In the employee table to select the gender of a person, the query would be like
Check Constraint at column level:
CREATE TABLE employee 
( id number(5) PRIMARY KEY, 
name char(20), 
dept char(10), 
age number(2), 
gender char(1) CHECK (gender in ('M','F')), 
salary number(10), 
location char(10) 
); 
Check Constraint at table level:
CREATE TABLE employee 
( id number(5) PRIMARY KEY, 
name char(20), 
dept char(10), 
age number(2), 
gender char(1), 
salary number(10), 
location char(10), 
CONSTRAINT gender_ck CHECK (gender in ('M','F')) 
); 

Sunday, July 10, 2016

ශිල්ප සයුර A/L ICT e Learning ව්‍යාපෘතිය සාර්ථකයි!

A/L ICT e Learning ව්‍යාපෘතියට අදහස වැටුනේ 2012 ජනවාරියේ. Chemistry කර කර හිටපු අපේ දුව හදිසියේම ICT කරන්න තීරණය කරලා මට ICT  උගන්වන්න කිව්වා. මාස 7 ක් විභාගයට තියෙද්දි අවුරුදු 2ක විශය නිර්දේශයක් උගන්වන්න මම තෝරාගත්තේ e Learning. තාක්‍ෂණය දැන ගත්තට උසස් පෙළ ගුරුවරයෙක් ලෙස වැඩකර පුරුද්දක් නොතිබූණ නිසා හැම පාඩමක්ම සරළව හදලා online share කළා. 



නරක නැහැ. එයා B එකක් ගත්තා.  ටියුෂන් කරන අදහසක් මට නොතිබුණත් අපේ දුවට වගේ ප්‍රශ්න තිබුණ අය ගණනකටම ඒ පාඩම් උදව් වුනා. 2015 AppSMART ව්‍යාපෘතියේදී උසස් පෙළ ICT e Learning සඳහා app එකක් හදන්න යෝජනා කලෙත් අපේ දුවමයි. 2015 ඉඳලා වසරකට වඩා වැඩ කරලා සම්පූර්ණ විශය නිර්දේශය ආවරණය වෙන විදියට A/L ICT e Learning app එක හැදුවා. අන්තිම හරියට එනකොට එපාත් උනා, මොකද උසස් පෙළ ICT විෂය නිර්දේශයේ වපසරිය හරිම වැඩියි. වසර 2කදී ප්‍රායෝගික දැනුමත් එක්කඑය උගන්වන්න ගුරුවරු විශාල මහන්සියක් වෙන්න ඕනේ.

සරළ ඉංග්‍රීසියෙන් සාදන ලද මේ ඉසවි අධ්‍යන පද්ධතිය සිංහල බසින් නිමවීම පිළබඳව සිතුවත් බොහෝ පාසල් සිසුන් ඉංග්‍රීසි භාෂාවෙන් ICT ඉගන ගත්තේ නැතිනම් ඔවුන්ට උසස් පෙළින් පසුව ICT කරන්න අපහසු වෙනවා. සියලූම උපාධි ඉංග්‍රීසියෙන් තියෙන්නේ. මංහසුරුවක් හෝ උපලැකියානක් ගැන සම්මුඛ පරීක්‍ෂණ වලදී අහන්නේ නෑ.  ඒ එක්කම සිංහල ICT පාරිභාෂුක වචන මාලාව පිළිබඳ නියම එකඟතාවයක් නොමැති වීමත් තාක්‍ෂණික ප්‍රශ්නයක්. දැනට මෙය භාවිතා කරන සිසුන්ට සරළ ඉංග්‍රීසි ගැටළුවක් උනේ නෑ.  ඔවුන් A/L ICT ඉගනුම් උපකරණයක්  මෙය භාවිතා කරනවා.


මෙය නිර්මාණය කිරීමේදී වැඩියෙන්ම සිත යොමුකලේ Smart Phone එකෙන් ඉගනීම කරන්නට පහසුකම් සපයන්න. සියලුම පාඩම් mobile එකෙන් බලන්න පුළුවන්. Mobile Learning තුලින් සිසුන්ට ඕනෑම තැනක සිට ඕනෑම වෙලාවක A/L ICT ඉගන ගන්න පුළුවන්.

සිසුන්ට  පියවරෙන් පියවර තමන්ගේ කාලය අනුව සම්පුර්ණ විශය  නිර්දේෂයම ඉගනීම කරන්නට පුළුවන් විදියටයි මෙය නිර්මාණය කළේ. රණබිම යසස්, කිංස්වුඩ් චිරාත් හා හර්ෂණ, ආනන්දේ රවිඳු, නාලන්දේ කවිඳු  A/L ICT e Learning කරලා පද්ධතිය Test කලාට පස්සේ තමා හැමෝටම එය ලබාදෙන්න තීරණය කළේ. මේ සඳහා භාවිතා කරන server එකත්, එය නඩත්තු කිරීමට යන වියදමත් ,  අනිකුත් මානව සහ භෞතික සම්පත් විදයමුත් සළකා වසරකට රැ. 6600/= කට එය ලබාදෙන්න තීරණය කළා. ඉතින් ශිල්ප සයුර වගේ තවත් වැඩක් සාර්ථක ලෙස අවසාන කළා. සතුටුයි!

Friday, July 1, 2016

46 Simple Python Programming Exercises

This 46  simple Python exercises created and  collected by Torbjörn Lager  involve characters, words and phrases, rather than numbers,  therefore suitable for students interested in learning lpython language.

    Very simple exercises

  1. Define a function max() that takes two numbers as arguments and returns the largest of them. Use the if-then-else construct available in Python. (It is true that Python has the max() function built in, but writing it yourself is nevertheless a good exercise.)
  2. Define a function max_of_three() that takes three numbers as arguments and returns the largest of them.

  3. Define a function that computes the length of a given list or string. (It is true that Python has the len() function built in, but writing it yourself is nevertheless a good exercise.)
  4. Write a function that takes a character (i.e. a string of length 1) and returns True if it is a vowel, False otherwise.

  5. Write a function translate() that will translate a text into "rövarspråket" (Swedish for "robber's language"). That is, double every consonant and place an occurrence of "o" in between. For example, translate("this is fun") should return the string "tothohisos isos fofunon".
  6. Define a function sum() and a function multiply() that sums and multiplies (respectively) all the numbers in a list of numbers. For example, sum([1, 2, 3, 4]) should return 10, and multiply([1, 2, 3, 4]) should return 24.
  7. Define a function reverse() that computes the reversal of a string. For example, reverse("I am testing") should return the string "gnitset ma I".
  8. Define a function is_palindrome() that recognizes palindromes (i.e. words that look the same written backwards). For example, is_palindrome("radar") should return True.
  9. Write a function is_member() that takes a value (i.e. a number, string, etc) x and a list of values a, and returns True ifx is a member of aFalse otherwise. (Note that this is exactly what the in operator does, but for the sake of the exercise you should pretend Python did not have this operator.)
  10. Define a function overlapping() that takes two lists and returns True if they have at least one member in common, False otherwise. You may use your is_member() function, or the in operator, but for the sake of the exercise, you should (also) write it using two nested for-loops.
  11. Define a function generate_n_chars() that takes an integer n and a character c and returns a string, n characters long, consisting only of c:s. For example, generate_n_chars(5,"x") should return the string "xxxxx". (Python is unusual in that you can actually write an expression 5 * "x" that will evaluate to "xxxxx". For the sake of the exercise you should ignore that the problem can be solved in this manner.)
  12. Define a procedure histogram() that takes a list of integers and prints a histogram to the screen. For example,histogram([4, 9, 7]) should print the following:

    ****
    *********
    *******
  13. The function max() from exercise 1) and the function max_of_three() from exercise 2) will only work for two and three numbers, respectively. But suppose we have a much larger number of numbers, or suppose we cannot tell in advance how many they are? Write a function max_in_list() that takes a list of numbers and returns the largest one.
  14. Write a program that maps a list of words into a list of integers representing the lengths of the correponding words.
  15. Write a function find_longest_word() that takes a list of words and returns the length of the longest one.
  16. Write a function filter_long_words() that takes a list of words and an integer n and returns the list of words that are longer than n.
  17. Write a version of a palindrome recognizer that also accepts phrase palindromes such as "Go hang a salami I'm a lasagna hog.", "Was it a rat I saw?", "Step on no pets", "Sit on a potato pan, Otis", "Lisa Bonet ate no basil", "Satan, oscillate my metallic sonatas", "I roamed under it as a tired nude Maori", "Rise to vote sir", or the exclamation "Dammit, I'm mad!". Note that punctuation, capitalization, and spacing are usually ignored.
  18. pangram is a sentence that contains all the letters of the English alphabet at least once, for example: The quick brown fox jumps over the lazy dog. Your task here is to write a function to check a sentence to see if it is a pangram or not.
  19. "99 Bottles of Beer" is a traditional song in the United States and Canada. It is popular to sing on long trips, as it has a very repetitive format which is easy to memorize, and can take a long time to sing. The song's simple lyrics are as follows:
    99 bottles of beer on the wall, 99 bottles of beer.
    Take one down, pass it around, 98 bottles of beer on the wall.
    The same verse is repeated, each time with one fewer bottle. The song is completed when the singer or singers reach zero.
    Your task here is write a Python program capable of generating all the verses of the song.
  20. Represent a small bilingual lexicon as a Python dictionary in the following fashion {"merry":"god", "christmas":"jul", "and":"och", "happy":gott", "new":"nytt", "year":"år"} and use it to translate your Christmas cards from English into Swedish. That is, write a function translate() that takes a list of English words and returns a list of Swedish words.
  21. Write a function char_freq() that takes a string and builds a frequency listing of the characters contained in it. Represent the frequency listing as a Python dictionary. Try it with something likechar_freq("abbabcbdbabdbdbabababcbcbab").
  22. In cryptography, a Caesar cipher is a very simple encryption techniques in which each letter in the plain text is replaced by a letter some fixed number of positions down the alphabet. For example, with a shift of 3, A would be replaced by D, B would become E, and so on. The method is named after Julius Caesar, who used it to communicate with his generals. ROT-13 ("rotate by 13 places") is a widely used example of a Caesar cipher where the shift is 13. In Python, the key for ROT-13 may be represented by means of the following dictionary:
    key = {'a':'n', 'b':'o', 'c':'p', 'd':'q', 'e':'r', 'f':'s', 'g':'t', 'h':'u', 
           'i':'v', 'j':'w', 'k':'x', 'l':'y', 'm':'z', 'n':'a', 'o':'b', 'p':'c', 
           'q':'d', 'r':'e', 's':'f', 't':'g', 'u':'h', 'v':'i', 'w':'j', 'x':'k',
           'y':'l', 'z':'m', 'A':'N', 'B':'O', 'C':'P', 'D':'Q', 'E':'R', 'F':'S', 
           'G':'T', 'H':'U', 'I':'V', 'J':'W', 'K':'X', 'L':'Y', 'M':'Z', 'N':'A', 
           'O':'B', 'P':'C', 'Q':'D', 'R':'E', 'S':'F', 'T':'G', 'U':'H', 'V':'I', 
           'W':'J', 'X':'K', 'Y':'L', 'Z':'M'}
    
    Your task in this exercise is to implement an encoder/decoder of ROT-13. Once you're done, you will be able to read the following secret message:
       Pnrfne pvcure? V zhpu cersre Pnrfne fnynq!
    Note that since English has 26 characters, your ROT-13 program will be able to both encode and decode texts written in English.
  23. Define a simple "spelling correction" function correct() that takes a string and sees to it that 1) two or more occurrences of the space character is compressed into one, and 2) inserts an extra space after a period if the period is directly followed by a letter. E.g. correct("This   is  very funny  and    cool.Indeed!") should return "This is very funny and cool. Indeed!" Tip: Use regular expressions!
  24. The third person singular verb form in English is distinguished by the suffix -s, which is added to the stem of the infinitive form: run -> runs. A simple set of rules can be given as follows:
    1. If the verb ends in y, remove it and add ies
    2. If the verb ends in ochsshx or z, add es
    3. By default just add s
    Your task in this exercise is to define a function make_3sg_form() which given a verb in infinitive form returns its third person singular form. Test your function with words like trybrushrun and fix. Note however that the rules must be regarded as heuristic, in the sense that you must not expect them to work for all cases. Tip: Check out the string method endswith().
  25. In English, the present participle is formed by adding the suffix -ing to the infinite form: go -> going. A simple set of heuristic rules can be given as follows:
    1. If the verb ends in e, drop the e and add ing (if not exception: beseefleeknee, etc.)
    2. If the verb ends in ie, change ie to y and add ing
    3. For words consisting of consonant-vowel-consonant, double the final letter before adding ing
    4. By default just add ing
    Your task in this exercise is to define a function make_ing_form() which given a verb in infinitive form returns its present participle form. Test your function with words such as lieseemove and hug. However, you must not expect such simple rules to work for all cases.
  26. Higher order functions and list comprehensions

  27. Using the higher order function reduce(), write a function max_in_list() that takes a list of numbers and returns the largest one. Then ask yourself: why define and call a new function, when I can just as well call the reduce() function directly?
  28. Write a program that maps a list of words into a list of integers representing the lengths of the correponding words. Write it in three different ways: 1) using a for-loop, 2) using the higher order function map(), and 3) using list comprehensions.
  29. Write a function find_longest_word() that takes a list of words and returns the length of the longest one. Use only higher order functions.
  30. Using the higher order function filter(), define a function filter_long_words() that takes a list of words and an integer n and returns the list of words that are longer than n.
  31. Represent a small bilingual lexicon as a Python dictionary in the following fashion {"merry":"god", "christmas":"jul", "and":"och", "happy":gott", "new":"nytt", "year":"år"} and use it to translate your Christmas cards from English into Swedish. Use the higher order function map() to write a function translate() that takes a list of English words and returns a list of Swedish words.
  32. Implement the higher order functions map()filter() and reduce(). (They are built-in but writing them yourself may be a good exercise.)
  33. Simple exercises including I/O

  34. Write a version of a palindrome recogniser that accepts a file name from the user, reads each line, and prints the line to the screen if it is a palindrome.
  35. According to Wikipedia, a semordnilap is a word or phrase that spells a different word or phrase backwards. ("Semordnilap" is itself "palindromes" spelled backwards.) Write a semordnilap recogniser that accepts a file name (pointing to a list of words) from the user and finds and prints all pairs of words that are semordnilaps to the screen. For example, if "stressed" and "desserts" is part of the word list, the the output should include the pair "stressed desserts". Note, by the way, that each pair by itself forms a palindrome!
  36. Write a procedure char_freq_table() that, when run in a terminal, accepts a file name from the user, builds a frequency listing of the characters contained in the file, and prints a sorted and nicely formatted character frequency table to the screen.
  37. The International Civil Aviation Organization (ICAO) alphabet assigns code words to the letters of the English alphabet acrophonically (Alfa for A, Bravo for B, etc.) so that critical combinations of letters (and numbers) can be pronounced and understood by those who transmit and receive voice messages by radio or telephone regardless of their native language, especially when the safety of navigation or persons is essential. Here is a Python dictionary covering one version of the ICAO alphabet:

    d = {'a':'alfa', 'b':'bravo', 'c':'charlie', 'd':'delta', 'e':'echo', 'f':'foxtrot',
         'g':'golf', 'h':'hotel', 'i':'india', 'j':'juliett', 'k':'kilo', 'l':'lima',
         'm':'mike', 'n':'november', 'o':'oscar', 'p':'papa', 'q':'quebec', 'r':'romeo',
         's':'sierra', 't':'tango', 'u':'uniform', 'v':'victor', 'w':'whiskey', 
         'x':'x-ray', 'y':'yankee', 'z':'zulu'}
    
    Your task in this exercise is to write a procedure speak_ICAO() able to translate any text (i.e. any string) into spokenICAO words. You need to import at least two libraries: os and time. On a mac, you have access to the system TTS (Text-To-Speech) as follows: os.system('say ' + msg), where msg is the string to be spoken. (Under UNIX/Linux and Windows, something similar might exist.) Apart from the text to be spoken, your procedure also needs to accept two additional parameters: a float indicating the length of the pause between each spoken ICAO word, and a float indicating the length of the pause between each word spoken.
  38. hapax legomenon (often abbreviated to hapax) is a word which occurs only once in either the written record of a language, the works of an author, or in a single text. Define a function that given the file name of a text will return all its hapaxes. Make sure your program ignores capitalization.
  39. Write a program that given a text file will create a new text file in which all the lines from the original file arenumbered from 1 to n (where n is the number of lines in the file).
  40. Write a program that will calculate the average word length of a text stored in a file (i.e the sum of all the lengths of the word tokens in the text, divided by the number of word tokens).
  41. Write a program able to play the "Guess the number"-game, where the number to be guessed is randomly chosen between 1 and 20. This is how it should work when run in a terminal:

    >>> import guess_number
    Hello! What is your name?
    Torbjörn
    Well, Torbjörn, I am thinking of a number between 1 and 20.
    Take a guess.
    10
    Your guess is too low.
    Take a guess.
    15
    Your guess is too low.
    Take a guess.
    18
    Good job, Torbjörn! You guessed my number in 3 guesses!
    
    
  42. An anagram is a type of word play, the result of rearranging the letters of a word or phrase to produce a new word or phrase, using all the original letters exactly once; e.g., orchestra = carthorseA decimal point = I'm a dot in place. Write a Python program that, when started 1) randomly picks a word w from given list of words, 2) randomly permutes w (thus creating an anagram of w), 3) presents the anagram to the user, and 4) enters an interactive loop in which the user is invited to guess the original word. It may be a good idea to work with (say) colour words only. The interaction with the program may look like so:
    >>> import anagram
    Colour word anagram: onwbr
    Guess the colour word!
    black
    Guess the colour word!
    brown
    Correct!
    
    
  43. In a game of Lingo, there is a hidden word, five characters long. The object of the game is to find this word by guessing, and in return receive two kinds of clues: 1) the characters that are fully correct, with respect to identity as well as to position, and 2) the characters that are indeed present in the word, but which are placed in the wrong position. Write a program with which one can play Lingo. Use square brackets to mark characters correct in the sense of 1), and ordinary parentheses to mark characters correct in the sense of 2). Assuming, for example, that the program conceals the word "tiger", you should be able to interact with it in the following way:
    >>> import lingo
    snake
    Clue: snak(e)
    fiest
    Clue: f[i](e)s(t)
    times
    Clue: [t][i]m[e]s
    tiger
    Clue: [t][i][g][e][r]
  44. Somewhat harder exercises

  45. sentence splitter is a program capable of splitting a text into sentences. The standard set of heuristics for sentence splitting includes (but isn't limited to) the following rules:
    Sentence boundaries occur at one of "." (periods), "?" or "!", except that
    1. Periods followed by whitespace followed by a lower case letter are not sentence boundaries.
    2. Periods followed by a digit with no intervening whitespace are not sentence boundaries.
    3. Periods followed by whitespace and then an upper case letter, but preceded by any of a short list of titles are not sentence boundaries. Sample titles include Mr., Mrs., Dr., and so on.
    4. Periods internal to a sequence of letters with no adjacent whitespace are not sentence boundaries (for example, www.aptex.com, or e.g).
    5. Periods followed by certain kinds of punctuation (notably comma and more periods) are probably not sentence boundaries.
    Your task here is to write a program that given the name of a text file is able to write its content with each sentence on a separate line. Test your program with the following short text: Mr. Smith bought cheapsite.com for 1.5 million dollars, i.e. he paid a lot for it. Did he mind? Adam Jones Jr. thinks he didn't. In any case, this isn't true... Well, with a probability of .9 it isn't. The result should be:
    Mr. Smith bought cheapsite.com for 1.5 million dollars, i.e. he paid a lot for it.
    Did he mind?
    Adam Jones Jr. thinks he didn't.
    In any case, this isn't true...
    Well, with a probability of .9 it isn't.
    
    
  46. An anagram is a type of word play, the result of rearranging the letters of a word or phrase to produce a new word or phrase, using all the original letters exactly once; e.g., orchestra = carthorse. Using the word list athttp://www.puzzlers.org/pub/wordlists/unixdict.txt, write a program that finds the sets of words that share the same characters that contain the most words in them.
  47. Your task in this exercise is as follows:
    • Generate a string with N opening brackets ("[") and N closing brackets ("]"), in some arbitrary order.
    • Determine whether the generated string is balanced; that is, whether it consists entirely of pairs of opening/closing brackets (in that order), none of which mis-nest.
    Examples:
       []        OK   ][        NOT OK
       [][]      OK   ][][      NOT OK
       [[][]]    OK   []][[]    NOT OK
    
    
  48. A certain childrens game involves starting with a word in a particular category. Each participant in turn says a word, but that word must begin with the final letter of the previous word. Once a word has been given, it cannot be repeated. If an opponent cannot give a word in the category, they fall out of the game. For example, with "animals" as the category,
    Child 1: dog 
    Child 2: goldfish
    Child 1: hippopotamus
    Child 2: snake
    ...
    
    Your task in this exercise is as follows: Take the following selection of 70 English Pokemon names (extracted fromWikipedia's list of Pokemon) and generate the/a sequence with the highest possible number of Pokemon names where the subsequent name starts with the final letter of the preceding name. No Pokemon name is to be repeated.
    audino bagon baltoy banette bidoof braviary bronzor carracosta charmeleon
    cresselia croagunk darmanitan deino emboar emolga exeggcute gabite
    girafarig gulpin haxorus heatmor heatran ivysaur jellicent jumpluff kangaskhan
    kricketune landorus ledyba loudred lumineon lunatone machamp magnezone mamoswine
    nosepass petilil pidgeotto pikachu pinsir poliwrath poochyena porygon2
    porygonz registeel relicanth remoraid rufflet sableye scolipede scrafty seaking
    sealeo silcoon simisear snivy snorlax spoink starly tirtouga trapinch treecko
    tyrogue vigoroth vulpix wailord wartortle whismur wingull yamask
    
    
  49. An alternade is a word in which its letters, taken alternatively in a strict sequence, and used in the same order as the original word, make up at least two other words. All letters must be used, but the smaller words are not necessarily of the same length. For example, a word with seven letters where every second letter is used will produce a four-letter word and a three-letter word. Here are two examples:
      "board": makes "bad" and "or".
      "waists": makes "wit" and "ass".
    

     Using the word list at http://www.puzzlers.org/pub/wordlists/unixdict.txt, write a program that goes through each word in the list and tries to make two smaller words using every second letter. The smaller words must also be members of the list. Print the words to the screen in the above fashion.