Create a new file named "exercise-n.txt", containing:
The SQL Statement used, when applicable
The SQL Statement results, when applicable
After the first exercise you commit, do a pull request from your master branch. Then, commit and push after each exercise so that we can see your progress.
Exercise 1
Reflect the data model shown in schema/addressbook_normalized.png within database decodemtl_addressbook
Account.id is a primary auto-increment key
AddressBook.id is a primary auto-increment key
Entry.id is a primary auto-increment key
Entry.type is an ENUM column permitting phone, address and electronic-mail
Address.id is a primary auto-increment key
Address.type is an ENUM column permitting home, work and other
Email.id is a primary auto-increment key
Email.type is an ENUM column permitting home, work and other
Phone.id is a primary auto-increment key
Phone.type is an ENUM column permitting home, work and other
Phone.subtype is an ENUM column permitting landline, cellular and fax
Exercise 2
Bulk import data from the source files into decodemtl_addressbook:
data/import-account.sql
data/import-addressbook.sql
data/import-entry.sql
Exercise 3
List all of the countries with respective occurence totals in DESC order
Country names should all appear lowercase
Exercise 4
List all of the first names for AddressBook.name="Pharetra Ut Limited"
The first and last letters should be capitalized
Exercise 5
List all of the emails associated to AddressBook.id = 100
Exercise 6
List all of the phone numbers for Jenkins, Charlotte
Exercise 7
List all possible domain name values for ElectronicMail (email@domain.name)
Exercise 8
List how many phones were landlines, cellular and fax for entries with birthdates between October 1950 and October 1960
Exercise 9
List all Account emails with AddressBook containing Phone numbers with a country code
Exercise 10
List all of the person names born between 8PM and 9PM but not in the month of February
Exercise 11
List all of the cities within the countries of Canada, Austria, Isle of Man, Ireland and Japan.
Exercise 12
List 100 phone numbers in separated parts: country code, area code and line number
Exercise 13
List the date difference, in days, between AddressBook creation and modification dates
Exercise 14
Reverse all fax phone numbers, keeping the phone numbers in a valid format
List all of the new fax phone number values
Exercise 15
Transform all work emails into home emails and vice versa
Exercise 16
Randomize the civic number of 10 other addresses whose primary key value ranges between 715 and 800
Exercise 17
Return a list of adresses ordered by length of the combined columns addressLine1 and city
Exercise 18
Create an exact copy of decodemtl_addressbook.Account and its data
The copy's modifiedOn column should reflect today's date
Exercise 19
Return a list of all accounts with columns createdOn in the format of Sept 20 2016 11:45 AM and modifiedOn in the format 20th 16 Tue 20 09 Sep 264