Different types of joins in SQL

Inner Join - Inner join creates a new result table by combining column values of two tables (A and B) based upon the join-predicate. The query compares each row of A with each row of B to find all pairs of rows which satisfy the join-predicate. When the join-predicate is satisfied, column values for each matched pair of rows of A and B are combined into a result row. The result of the join can be defined as the outcome of first taking the Cartesian product (or Cross join) of all records in the tables (combining every record in table A with every record in table B)—then return all records which satisfy the join predicate

Inner joins is further classified as equi-joins, as natural joins, or as cross-joins.

Equi-join
An equi-join, also known as an equijoin, is a specific type of comparator-based join, or theta join, that uses only equality comparisons in the join-predicate. Using other comparison operators (such as <) disqualifies a join as an equi-join


Natural join
A natural join offers a further specialization of equi-joins. The join predicate arises implicitly by comparing all columns in both tables that have the same column-names in the joined tables. The resulting joined table contains only one column for each pair of equally-named columns.

Cross join
CROSS JOIN returns the Cartesian product of rows from tables in the join. In other words, it will produce rows which combine each row from the first table with each row from the second table

Outer Join
An outer join does not require each record in the two joined tables to have a matching record. The joined table retains each record—even if no other matching record exists. Outer joins subdivide further into left outer joins, right outer joins, and full outer joins, depending on which table(s) one retains the rows from (left, right, or both).

Left outer join
The result of a left outer join (or simply left join) for table A and B always contains all records of the "left" table (A), even if the join-condition does not find any matching record in the "right" table (B). This means that if the ON clause matches 0 (zero) records in B, the join will still return a row in the result—but with NULL in each column from B. This means that a left outer join returns all the values from the left table, plus matched values from the right table (or NULL in case of no matching join predicate). If the right table returns one row and the left table returns more than one matching row for it, the values in the right table will be repeated for each distinct row on the left table.

Right outer join
A right outer join (or right join) closely resembles a left outer join, except with the treatment of the tables reversed. Every row from the "right" table (B) will appear in the joined table at least once. If no matching row from the "left" table (A) exists, NULL will appear in columns from A for those records that have no match in B. A right outer join returns all the values from the right table and matched values from the left table (NULL in case of no matching join predicate). Result of right outer joins can also be produced with left outer joins by switching the table order

Full outer join
A full outer join combines the effect of applying both left and right outer joins. Where records in the FULL OUTER JOINed tables do not match, the result set will have NULL values for every column of the table that lacks a matching row. For those records that do match, a single row will be produced in the result set (containing fields populated from both tables).

Self-join
A self-join is joining a table to itself.


Refer to http://en.wikipedia.org/wiki/Join_%28SQL%29 for detailed understanding with examples

incompatible character encodings: ASCII-8BIT and UTF-8 in Ruby 1.9.2

While migrating from Ruby 1.8.7 to Ruby 1.9.2 getting error:
incompatible character encodings: ASCII-8BIT and UTF-8 in Ruby 1.9.2

While running rake task, getting error:

rake aborted!

C:/projects/restaurant/lib/tasks/setup.rake:158: invalid multibyte char (US-ASCII)

C:/projects/restaurant/lib/tasks/setup.rake:158: invalid multibyte char (US-ASCII)

C:/projects/restaurant/lib/tasks/setup.rake:158: syntax error, unexpected $end, expecting ')'

     Cuisine.create(:name=>"Caf├⌐")

                                 ^

C:/Ruby192/lib/ruby/gems/1.9.1/gems/activesupport-3.0.5/lib/active_support/dependencies.rb:235:in `load'

C:/Ruby192/lib/ruby/gems/1.9.1/gems/activesupport-3.0.5/lib/active_support/dependencies.rb:235:in `block in load'

C:/Ruby192/lib/ruby/gems/1.9.1/gems/activesupport-3.0.5/lib/active_support/dependencies.rb:225:in `block in load_dependency'

C:/Ruby192/lib/ruby/gems/1.9.1/gems/activesupport-3.0.5/lib/active_support/dependencies.rb:596:in `new_constants_in'

C:/Ruby192/lib/ruby/gems/1.9.1/gems/activesupport-3.0.5/lib/active_support/dependencies.rb:225:in `load_dependency'

C:/Ruby192/lib/ruby/gems/1.9.1/gems/activesupport-3.0.5/lib/active_support/dependencies.rb:235:in `load'

C:/Ruby192/lib/ruby/gems/1.9.1/gems/railties-3.0.5/lib/rails/engine.rb:131:in `block in load_tasks'

C:/Ruby192/lib/ruby/gems/1.9.1/gems/railties-3.0.5/lib/rails/engine.rb:131:in `each'

C:/Ruby192/lib/ruby/gems/1.9.1/gems/railties-3.0.5/lib/rails/engine.rb:131:in `load_tasks'

C:/Ruby192/lib/ruby/gems/1.9.1/gems/railties-3.0.5/lib/rails/application.rb:141:in `load_tasks'

C:/Ruby192/lib/ruby/gems/1.9.1/gems/railties-3.0.5/lib/rails/application.rb:77:in `method_missing'

C:/projects/restaurant/Rakefile:7:in `<top (required)>'

C:/Ruby192/lib/ruby/1.9.1/rake.rb:2373:in `load'

C:/Ruby192/lib/ruby/1.9.1/rake.rb:2373:in `raw_load_rakefile'

C:/Ruby192/lib/ruby/1.9.1/rake.rb:2007:in `block in load_rakefile'

C:/Ruby192/lib/ruby/1.9.1/rake.rb:2058:in `standard_exception_handling'

C:/Ruby192/lib/ruby/1.9.1/rake.rb:2006:in `load_rakefile'

C:/Ruby192/lib/ruby/1.9.1/rake.rb:1991:in `run'

C:/Ruby192/bin/rake:31:in `<main>'

At some other places getting error :

invalid multibyte char (US-ASCII)

Solution:
Solved it by following steps:

• Make sure 'config.encoding = "utf-8"' is there in application.rb file.
• Make sure you are using 'mysql2' gem
• Putting '# encoding: utf-8' on top of rake file.
• Above 'Starter::Application.initialize!' line in environment.rb file, add following two lines:

Encoding.default_external = Encoding::UTF_8
Encoding.default_internal = Encoding::UTF_8

Questions really bothering then you ask them, we will have a discussion for their solutions

You will find a list of Interview Questions on link -
 http://rorguide.blogspot.com/search/label/Interview Questions.

If you want to clarify your doubts, post them and we will try to find their solutions.

Difference between InnoDB and MyISAM storage engine in MySQL

InnoDB: A transaction-safe (ACID compliant) storage engine for MySQL that has commit, rollback, and crash-recovery capabilities to protect user data. InnoDB row-level locking (without escalation to coarser granularity locks) and Oracle-style consistent nonlocking reads increase multi-user concurrency and performance. InnoDB stores user data in clustered indexes to reduce I/O for common queries based on primary keys. To maintain data integrity, InnoDB also supports FOREIGN KEY referential-integrity constraints. InnoDB is the default storage engine as of MySQL 5.5.5.

MyISAM: The MySQL storage engine that is used the most in Web, data warehousing, and other application environments. MyISAM is supported in all MySQL configurations, and is the default storage engine prior to MySQL 5.5.5.

For example, customer bank records might be grouped by customer in InnoDB but by transaction date with MyISAM, so InnoDB would likely require fewer disk seeks and less RAM to retrieve and cache a customer account history

The major differences between these two storage engines are :

• InnoDB supports transactions which is not supported by tables which use MyISAM storage engine.
• InnoDB has row-level locking, relational integrity i.e. supports foreign keys, which is not possible in MyISAM.
• InnoDB ‘s performance for high volume data cannot be beaten by any other storage engines available.

Tables created in MyISAM are known to have higher speed compared to tables in InnoDB. But since InnoDB supports volume, transactions, integrity it’s always a better option which you are dealing with a larger database. A single database can have tables of different storage engines.

To check engines you can use command:

mysql> Show engines;

You can change the engine while creating the table by command:

CREATE TABLE test name varchar(30) ENGINE = InnoDB;

It is also possible to convert from one engine to the other by command:

ALTER TABLE my_table ENGINE=new_engine;

When you will execute the above command, complete process will be as such - the table will get locked, dumped to a tmp space, then rebuilt with the new engine. Also you will be losing innodb-only info (foreign keys, etc.) and features if you’re going to MyISAM.

Key ideas taken from Merb into Rails 3

Merb is a lightweight and modular framework that is designed to give users more flexibility and better performance than Rails. Like Ruby on Rails, Merb is an MVC framework. Unlike Rails, Merb is ORM-agnostic, JavaScript library agnostic, and template language agnostic, preferring plugins that add in support for a particular feature rather than trying to produce a monolithic library with everything in the core. The core code in Merb is kept simple and well organised meaning it’s easier to understand, maintain, extend and lightweight.

On 23rd Decemember, 2008 announcement was done that Merb is Rails and Rails is Merb, i.e., Merbs 2 is Rails 3. Merb and Rails are joining forces and merging the two code bases for Rails-3.0

For Merb
This does not mean that Merb folks will not be left out in the cold. They will continue to support bug and security fixes for the merb 1.0.x line. And will provide a clear upgrade path to Rails 3.0 for merb apps.

For Rails
There are many key ideas that Merb team will bring into Rails 3. Some of them are:

• Modularity – 

Rails will become more modular, starting with a rails-core, and including the ability to opt in or out of specific components. It will focus on reducing coupling across Rails, and making it possible to replace parts of Rails without disturbing other parts.

• Performance optimizations – 

Rails 3 will get all the performance attention that the Merb guys are known for. Rails 3 will benefit from the performance optimizations that the Merb developers have made to some of the components that are shared between the two frameworks.

• Rigorous API – 

Another critical characteristic of Merb is the strong API stability guarantee, which will make the extensibility of the framework more robust. This will allow users and plugin developers to have a clearer, more stable API to build against. It should also significantly reduce plugin breakage from release to release.

• Framework agnosticism -

Rails will always have a default answer to every question within the stack. If you don’t care about testing frameworks, you’ll get test/unit. If you don’t care about which ORM, you’ll get Active Record. But some people do care and want something else. Some people want RSpec for testing, others want to use Sequel or Data Mapper for ORM, others again prefer Haml for templating, and some might prefer jQuery for Ajax. All these people should feel like Rails is welcoming them with open arms. Yes, Rails have a default, but it shouldn’t have any form of discrimination against alternatives.

• Rails core  -

Rails will make it easy to start with a “core” version of Rails (like Merb’s core generator), that starts with all modules out, and makes it easy to select just the parts that are important for your app. Think “rails myapp—core” (and “rails myapp—flat”).

Active Record - finder Methods : Query finder Method (Generalized Finder)

Query finder is a methodology where the user finds information from the database by use of SQL queries. The developer uses standard SQL Queries in multiple form and executes them using the find_by_sql method. find_by_sql provides you with a simple way of making custom calls to the database and retrieving instantiated objects.

The find_by_sql method will return an array of objects even if the underlying query returns just a single record.

Examples :
Retrieving complete information:
@loggedinusers = User.find_by_sql(“select * from users where loggedin=true;”)

Retrieving complete information with unknown inputs:
@loggedinusers =User.find_by_sql(["select * from users where loggedin=? and isActive=?",@isloggedin,@isactive]

Retrieving partial information as required:
@loggedinusers = User.find_by_sql(["SELECT username, lastloggedindate, isActive FROM users"])

find_by_sql has a close relative called connection#select_all. select_all will retrieve objects from the database using custom SQL just like find_by_sql but will not instantiate them. Instead, you will get an array of hashes where each hash indicates a record.

Client.connection.select_all("SELECT * FROM clients WHERE id = '1'")

Active Record - finder Methods : Custom finder Method (Customized Finder)

This method uses writing custom find* methods which could be either un-conventional method or override the methods declared by the ActiveRecord::Base. Custom finders are declared in the Model objects and are generally tagged by self keyword.

Custom finders are nothing but writing find methods within the Model and call the ActiveRecord::Base::Find* methods from these methods. Custom finders generally are self methods declared in the Model object.

#Product.rb — Model file

def self.find_product_by_status(status)
if status != :o utofshelf
Product.find_by_sql ["SELECT * FROM product WHERE status=?",status]
else
Product.find_by_sql ["SELECT * FROM product WHERE status= 'outofshelf' AND availability=false"]
end
end

The example above identifies a custom finder where it checks if the status is not out of shelf, then get all the products based on the status and if it is out of shelf, then check the status and also is not available. Also if you see the name of the find method it is an un-conventional name find _product_by_status instead of find_by_status.

Developers can utilize custom finder to name methods by thier own way or even custom finders can also be used to over write the method names which exists in the ActiveRecord::Base class. An example can be found as follows:

#Product.rb — Model file

def self.find_by_status(status)
if status != 'outofshelf'
Product.find_by_sql ["SELECT * FROM product WHERE status=? AND availability=false"]
else
super
end
end

In this example, you see that the custom finder has the same name as a find_by_status method which is a standard ActiveRecord::Base method and has been overwritten by the find method in the Model file. In the example, we have also dealt in re-directing the custom finder method back to its parent method by using the super keyword.

Active Record - finder Methods : Dynamic finder Method (Specialized Finder)

Dynamic Finders Method (Specialized Find )

Dynamic finders are a method of finding information based on particular database field of a table. For every field (also known as an attribute) you define in your table, Active Record provides a finder method

There are 4 types of dynamic finders which can be used:

• find_by_XXX methods: Finds the first row for a given database field name(s).
• find_all_by_XXX methods: Finds all the rows that satisfies a given database field name(s).
• find_or_initialize_by_XXX: Finds a row with a given parameter(s) and if it does not exist will act similarly to calling new with the arguments you passed in.
• find_or_create_by_XXX methods: Finds a row with particular parameters and if not available, then will be created in the database with the input parameters and then returned.

You can do find_last_by_* methods too which will find the last record matching your argument.

You can specify an exclamation point (!) on the end of the dynamic finders to get them to raise an ActiveRecord::RecordNotFound error if they do not return any records, like Product.find_by_name!("Ror")

Difference between Argument and Parameter in ruby on rails

A parameter represents a value that the method expects you to pass when you call it.
An argument represents the value you pass to a method parameter when you call the method. The calling code supplies the arguments when it calls the method.

In simple words, parameters appear in method definitions; arguments appear in method calls.

For example, in below method, variables param1 and param2 are the parameters
def foo_method(param1, param2):
    .......
end

while calling the method, arg1 and arg2 are the arguments
foo_method(arg1, arg2)

Class: ActiveRecord::Base - Find method with Arguments and Parameters

find(*args) public

Find operates with four different retrieval approaches:

Find by id - This can either be a specific id (1), a list of ids (1, 5, 6), or an array of ids ([5, 6, 10]). If no record can be found for all of the listed ids, then RecordNotFound will be raised.

Find first - This will return the first record matched by the options used. These options can either be specific conditions or merely an order. If no record can be matched, nil is returned. Use Model.find(:first, *args) or its shortcut Model.first(*args).

Find last - This will return the last record matched by the options used. These options can either be specific conditions or merely an order. If no record can be matched, nil is returned. Use Model.find(:last, *args) or its shortcut Model.last(*args).

Find all - This will return all the records matched by the options used. If no records are found, an empty array is returned. Use Model.find(:all, *args) or its shortcut Model.all(*args).
All approaches accept an options hash as their last parameter.

Parameters

• :conditions - An SQL fragment like "administrator = 1", [ &quot;user_name = ?&quot;, username ],   or [&quot;user_name = :user_name&quot;, { :user_name =&gt; user_name }].
• :order - An SQL fragment like "created_at DESC, name".
• :group - An attribute name by which the result should be grouped. Uses the GROUP BY SQL-clause.
• :having - Combined with :group this can be used to filter the records that a GROUP BY returns. Uses the HAVING SQL-clause.
• :limit - An integer determining the limit on the number of rows that should be returned.
• :offset - An integer determining the offset from where the rows should be fetched. So at 5, it would skip rows 0 through 4.
• :joins - Either an SQL fragment for additional joins like "LEFT JOIN comments ON comments.post_id = id" (rarely needed), named associations in the same form used for the :include option, which will perform an INNER JOIN on the associated table(s), or an array containing a mixture of both strings and named associations. If the value is a string, then the records will be returned read-only since they will have attributes that do not correspond to the table’s columns. Pass :readonly =&gt; false to override.
• :include - Names associations that should be loaded alongside. The symbols named refer to already defined associations.
• :select - By default, this is "*" as in "SELECT * FROM", but can be changed if you, for example, want to do a join but not include the joined columns. Takes a string with the SELECT SQL fragment (e.g. "id, name").
• :from - By default, this is the table name of the class, but can be changed to an alternate table name (or even the name of a database view).
• :readonly - Mark the returned records read-only so they cannot be saved or updated.
• :lock - An SQL fragment like "FOR UPDATE" or "LOCK IN SHARE MODE". :lock =&gt; true gives connection’s default exclusive lock, usually "FOR UPDATE".

# find by id
  Person.find(1)       # returns the object for ID = 1
  Person.find(1, 2, 6) # returns an array for objects with IDs in (1, 2, 6)
  Person.find([7, 17]) # returns an array for objects with IDs in (7, 17)
  Person.find([1])     # returns an array for the object with ID = 1
  Person.find(1, :conditions => "administrator = 1", :order => "created_on DESC")

  # find first
  Person.find(:first) # returns the first object fetched by SELECT * FROM people
  Person.find(:first, :conditions => [ "user_name = ?", user_name])
  Person.find(:first, :conditions => [ "user_name = :u", { :u => user_name }])
  Person.find(:first, :order => "created_on DESC", :offset => 5)

  # find last
  Person.find(:last) # returns the last object fetched by SELECT * FROM people
  Person.find(:last, :conditions => [ "user_name = ?", user_name])
  Person.find(:last, :order => "created_on DESC", :offset => 5)

  # find all
  Person.find(:all) # returns an array of objects for all the rows fetched by SELECT * FROM people
  Person.find(:all, :conditions => [ "category IN (?)", categories], :limit => 50)
  Person.find(:all, :conditions => { :friends => ["Bob", "Steve", "Fred"] }
  Person.find(:all, :offset => 10, :limit => 10)
  Person.find(:all, :include => [ :account, :friends ])
  Person.find(:all, :group => "category")

how to find out data type of a column in a table through RoR code

In one of my application, there is a table, Product with more than 100 columns. I want to find out
- the data type of "product_owner" column
- to iterate over all columns and collect array of boolean / tinyint(1) data type columns.

Here is the code to do that:
Product.columns_hash["product_owner"].sql_type
Above code will give you the data type of the column, in this case its "varchar(255)"

If you know that "product_owner" is the 9th column of the table, then you can also find the data type by command:
Product.content_columns[9].sql_type

To iterate over all columns and collect array of boolean / tinyint(1) data type columns:

b= []

Product.content_columns.each do |p|

  if p.sql_type == "tinyint(1)"

    b << p.human_name

  end

end

Now, when you will print 'b', you will get array of all the fields of data type boolean / tinyint(1)

How to iterate over table columns and get values

In one of my application, there is a table, Product with more than 100 columns. I wanted to iterate over all the columns and display their values on product show page.

This is how I did it:
<table border="1">
    <% for column in Product.content_columns %>
        <tr>
          <th><%= column.human_name %></th> 
          <td><%= h @product.send(column.name) %> <td>
        </tr>
    <% end %>
</table>

column.human_name - Returns the human name of the column name. If your column is "product_owner", it will return "Product owner".

column.name will return the name of the column (product_owner)

@product.send(column.name) will return the value of the column.