Sanjay's Technical and Philosophical Blog

  A common problem that database engineers face is the occurrence of duplicate records. There are a few reasons why this can happen and one must investigate it in the case of exact duplicates vs. partial duplicates. One problem that I’ve encountered is the existence of partial and exact duplicate records based on a specific key that we’ll call ‘unique_id’. 

After running some queries on the table to identify if the ‘unique_id’ was indeed a real unique id, I found that there were rows that matched exact values for every column and then conversely there were rows that matched values only for a set of columns. I used this query to remove ONLY exact duplicates with the DISTINCT keyword:

CREATE TABLE newtable AS (SELECT DISTINCT *

from originaltable);

​SELECT unique_id, COUNT(*)

FROM (

SELECT DISTINCT *

from newtable

)

GROUP BY unique_id

Databases collect and organize data to be collected easily. The data is organized into tables and  in tables, the data is organized in cells (or fields of data).Tables are also organized into rows and a row represents an entire data record within each table. Columns have descriptive names and the unique identifiers (primary keys) are the IDs.

Tables usually have less columns than rows, and the search function is from left to right and then from top to bottom. 

SQL Statements allow for interaction with a database on a server (A semicolon; must be used at the end of the statement to execute) :

SELECT Recipe: SELECT column (can select more than 1 column) FROM table; SELECT column FROM table WHERE ; The asterisk (*) corresponds to all. SELECT * FROM movies WHERE title = ‘The Kid’; => this returns a string of data Sorting Data: ORDER BY Recipe: SELECT FROM ORDER BY; (Sorts data by columns based on how it is to be viewed) this is by default ASC, so must say DESC Comparing Data using Comparison Operators Comparison Operator Recipe SELECT FROM WHERE _ > _; <> -> Not Equal to, or != SELECT FROM WHERE AND ; (all the conditions of the SELECT statement must be met, if not the query will return no records), thus the AND statement is used to select precise data OR Recipe: SELECT FROM WHERE OR ; (The OR operator is included to cover more range with our SELECT statement) ORDER BY must be last

Normalization: process of reducing duplication in databases 

First Normal Form Rule:Tables must not contain repeating groups of data in 1 column such as a genre column with ‘adventure, fantasy' Second Normal Form Rule:Tables must not contain redundancy (unnecessary repeating information) 

Inner Joins used for matching values between tables

SELECT * FROM Movies INNER JOIN Reviews ON Movies.id=Reviews.movie_id SELECT * FROM Reviews INNER JOIN Movies On Reviews.movie_id=Movies.id ______ SELECT Movies.title, Reviews.review INNER JOIN on multiple tables SELECT Movies.title, Genres.name FROM Movies INNER JOIN Movies_Genres ON Movies.id = Movies_Genres.movie_id INNER JOIN Genres ON Movie_Genres.genre_id = Genres.id WHERE Movies.title = “Peter Pan”;

Identify Constraints:

NOT NULL column constraint: constraints add validations, ensure values are unique, and tell us about null values How to prevent unwanted duplicates? UNIQUE constraint: example of column constraint CREATE TABLE example ( id in, name text NOT NULL UNIQUE, category varchars(15) ); Creating a UNIQUE table constraint:( CONSTRAINT unique_name UNIQUE (column,column,etc) ) In relational databases, most tables should have a primary key (usually the ID column, should be unique and not null), so CREATE TABLE Promotions (     id int PRIMARY KEY, name varchar(50), category varchar(15) ); PRIMARY KEY vs. NOT NULL + UNIQUE: Primary Key can only be defined once per table, while not null unique can be used many times Value Constraints: Introducing Foreign Key: Foreign Key is a column in one table that references the primary key column in another table Creating a FK constraint: CREATE TABLE Movies ( id int PRIMARY KEY, title varchar(20) NOT NULL UNIQUE ); CREATE TABLE Promotions ( id int PRIMARY KEY, movie_id int REFERENCES movies(id), [this is the FK constraint!] name varchar(50), category varchar(15) ); must reference the ID first though, FOREIGN KEY (id) REFERENCES Movies Orphan Records -> prevent this w/ FK constraints! CHECK constraints duration int CHECK (duration > 0) Common Aggregate Functions:

//blah

What is AJAX used for?

Prompt: 

There are 10 Villages that each pay a tax to the king in the form of a gold bar that measures to 1000 grams except for 1 village that gives a gold bar that measures to 999 grams. Devise a strategy to figure out which village is cheating the king - you have a digital scale and you can  measure as many blocks as you'd like but you only get 1 reading to determine the cheater. There is a history of the gold bars from each respective village.

#First Case: 1 cheating village

village_array = ["Village 1","Village 2", "Village 3", "Village 4", "Village 5", "Village 6", "Village 7", "Village 8", "Village 9", "Village 10"]

#Convert the Array to a hash so that the villages correspond to incremented values from 0.

village_hash = Hash[village_array.each_with_index.map {|key, value| [key, value]}]

#sum the values village_sum_of_values = village_hash.values.inject {|a,b| a + b}

ideal_sum = 10*1000 the variable => scale_village_sum = some integer calculated_sum = ideal_sum - scale_village_sum calculated_sum = ideal_sum if calculated_sum == ideal_sum - 9  return village_array[9]

This past weekend, David and I hacked on a project together at Spotify HQ where we essentially tried to model music theory. 

The first step was to declare a hash:

notes = {:c => 0, :csharp => 1,:d => 2, :dsharp => 3,:e => 4, :f => 5, :fsharp=> 6, :g => 7, :gsharp => 8, :a => 9, :asharp => 10, :b => 11}

We then defined what “major” and “minor” scale were in terms of half and whole steps where a half step equated to +1 and a whole step equated to +2.

def major(note)    major = [0, 2, 4, 5, 7, 9, 11, 12]    major.map do |value|       note + value    end end

similarly we were able to declare the structure for the natural and harmonic minors:

def natural_minor(note)    natural_minor = [0,2,3,5,7,8,10,12]    natural_minor.map do |value|        note + value    end end

def harmonic_minor(note)    harmonic_minor = [0,2,3,5,7,8,11,12]    harmonic_minor.map do |value|        note + value    end end

To get the actual output of midi we had to require ‘unimidi’

output = UniMIDI::Output.gets

output.open do |output|

We then made this output to the actual midi and had it run through the major and relative minor for whatever starting note we decided on:

notesarray = major(note)    20.times do        note = notesarray.sample        output.puts(0x90, note, 100)        sleep(rand(3)/10.to_f)        output.puts(0x80, note, 100)

   end

   notesarray = natural_minor(note)    20.times do        note = notesarray.sample        output.puts(0x90, note, 100)        sleep(rand(3)/10.to_f)        output.puts(0x80, note, 100)

Websockets? (thnx lewaa and brian)

The purpose of a web socket is to provide a way for communication between the client side of things and the server side. 

This is usually done with **ajax, **which is a client side script that enables communication between the server or database and the client.

How were messages usually sent without web sockets? With HTTP protocol. 

With web sockets, they use their own protocol and their own API called the Websockets API. 

What’s the process?

Open a connection (the client sends out a connection request and the server side accepts it)

var connection = new WebSocket('ws://example.org:12345/myapp'); connection.send('Hey server, whats up?');   console.log(server_message); } connection.onmessage = function(e){   var server_message = e.data;