November 26, 2016
No Comments
Create Oracle Database Table and Specify Some Constraints
The following are the main constraints which can be defined when you are creating a TABLE. I will be using the ORACLE DBMS . Primary key – The primary key of the table to be created. Foreign key – The primary key of some other table which is being referenced in this table to be
November 26, 2016
No Comments
Data Warehousing
Websites to Learn and Practice SQL for Free
You can practise SQL live online for Free. Here are a list of some websites in no particular order. Oracle Live SQL. This site also has lots of examples and tutorials which you can see the codes right there and then SQL Fiddle: You can practise various SQL by major players here as well. w3schools:
November 23, 2016
No Comments
November 20, 2016
No Comments
How To Track The Effectiveness of Email Campaign – Some Points to Consider
Sometimes, you would want to track the effectiveness of your email marketing campaign. How do you start ? What do you look out for ? and What are some of the key things to consider. This lists some key things to look out for when tracking the effectiveness if your email campaigns Get access Access
November 16, 2016
No Comments
Quick Example and Walk-Through JSON with Python
This is a quick to create a dictionary object , write it to a JSON file and then read back the file and convert it to a dictionary and access the items in the dictionary. Credit to : codebasics. You can check him out on Youtube In [3]:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
# lets create a dictionary of books book = {} book['Tom'] = { 'name':'Tom', 'address':'1 red street, NY', 'phone':98988988 } book['Bob'] = { 'name':'Bob', 'address':'1 green street, NY', 'phone':252525885 } |
In [6]:
|
1 2 3 4 |
#import json and pass the dictionary to it import json s = json.dumps(book) s |
Out[6]:
|
1 |
'{"Bob": {"phone": 252525885, "name": "Bob", "address": "1 green street, NY"}, "Tom": {"phone": 98988988, "name": "Tom", "address": "1 red street, NY"}}' |
In [14]:
November 13, 2016
No Comments
Using Subqueries To Join 2 More Tables Which Do Not Have Unique Primary and Foreign Keys
Using Subqueries to join 2 more tables which do not have distinct values in a column We want to find the number of Flight bookings by Country, number of Car bookings by Country and the number of Hotel bookings by Country all appearing in one table with same country on one row. We want to
November 12, 2016
No Comments
Rank a Place Visited By a Customer- Microsoft SQL Server – T-Transact
This is a short guide to getting ranking or rating from Microsoft Sql Server customers. Ranking is taken by checking how many times a customer has visited a particular country divided by the total number of travels the customer has made within the database
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 |
/*make a back up of the recommender rankings table*/ truncate table antrak.dbo.recommender_rankings_copy insert into antrak.dbo.recommender_rankings_copy select * from antrak.dbo.recommender_rankings /*select total number of times a customer has travelled to a specific country*/ select [First Name] ,Country , count(*) as [# times visited] into #travelpercountry FROM [Antrak].[dbo].[FlightBookingsMain] group by [First Name] ,Country order by [First Name] /*total number of times each customer has travelled to all destinations*/ select [First Name] , count(*) as [# times visited] into #totaltravel FROM [Antrak].[dbo].[FlightBookingsMain] group by [First Name] ----,Country order by [First Name] -- lets view by the total bookings select * from #totaltravel order by [First Name] -- lets view bookings per country select * from #travelpercountry order by [First Name] /*get rating*/ drop table antrak.dbo.recommender_rankings select tt.[First Name] ,Country ,cast(round((cast(tpc.[# times visited] as decimal(3,0)) / cast(tt.[# times visited] as decimal(3,0)) * 100), 1) as float) as rating into antrak.dbo.recommender_rankings from #totaltravel as tt inner join #travelpercountry as tpc on tt.[First Name] = tpc.[First Name] order by [First Name] select * from antrak.dbo.recommender_rankings /*lets refresh the temporary tables*/ drop table #totaltravel drop table #travelpercountry |
November 9, 2016
No Comments
Automatically Open Excel and Send Report Using Batch and VBA
We can use Visual Basic codes to automate some reports in Excel. Below are some simple codes to do this. First this is a batch file to open our Excel report called “data report.xlsm”, assuming the report file is in the same folder as the batch file. You can schedule the time this batch file is
November 6, 2016
No Comments
Splitting Values in one Column to Multiple Columns using CASE Expression Statement SQL Server
Sometimes, you would want to split values in a column into separate rows in SQL server. This is a simple guide. Lets check the fields in the table we will be working with.
|
1 2 3 4 5 6 7 |
select [orderID] ,[itemType] ,[price] ,[quantity] from [Antrak].[dbo].[ClothingSale] |
Let’s check the result: We can see that the itemType field has different values wheres as the price and quantity
November 3, 2016
No Comments
DataPandas LTS
An experienced DevOps and Cloud Training Company to meet your DevOps and Cloud needs