Back to Home

Tree and Linked List Data Structure

July 10, 2015

SQL (Structured Query Language)

SQL is used to communicate with a relational database. The following are the most frequently used command.

  • SQL SELECT - retrive data (SELECT * FROM movies)
  • SQL WHERE - narrowing query(WHERE hire_date < '2014-02-10')
  • SQL AND OR
  • SQL IN
  • SQL BETWEEN
  • SQL Wildcard - WHERE first_name LIKE 'A%'
  • SQL LIKE
  • SQL ORDER BY - giving query condition (ORDER BY hiring_date DESC)
  • SQL GROUP BY
  • SQL HAVING
  • SQL ALIAS
  • SQL AS
  • SQL SELECT UNIQUE
  • SQL INSERT INTO
  • SQL INSERT INTO SELECT
  • SQL UPDATE
  • SQL DELETE FROM

    • Let's look at 'GROUP BY'

    Table Name: Store_Information

    Store_Name Sales Txn_Date
    Los Angeles 1500 Jan-05-1999
    San Diego 250 Jan-07-1999
    Los Angeles 300 Jan-08-1999
    Boston 700 Jan-08-1999
    SELECT "column_name1", SUM("column_name2") FROM "table_name" GROUP BY "column_name1"; SELECT Store_name, SUM(Sales) FROM Store_Information GROUP BY Store_name
    Los Angeles 1800
    San Diego 250
    Boston 700

    Inner Join(Common Element), Outer Join(Everything) and Left and Right

    Inner Join

    Let's say we have a two column a and b. a has 1, 2, 3, 4 and b has 3, 4, 5, 6. Inner join is to find the same element from the each column. So it will return 3, 4 the common element from the both column.

    SELECT * FROM a INNER JOIN b on a.a = b.b; SELECT a.*, b.* FROM a, b WHEWRE a.a = b.b; Note that there is no double or tripple equal sign for SQL.
    def inner_join(arr1, arr2) arr_hash = Hash.new(0) arr1.each do |num| arr_hash[num] = 1 end arr2.each do |num| arr_hash[num]+=1 end arr_hash.select { |k,v| puts k if v == 2 } end
    Left Outer Join

    First, don't be intimidated by 'left' side of it. Left always means that it is the anchor of the other columns. In our above example, 'a' is going to be an 'left' side of it. Left Outer Join returns everything from 'left' side (so it will be 'a' column) and any common element from other side of it. So it will return 1, 2, 3, 4 from a and 3, 4 from b.

    SELECT * FROM a INNER LEFT JOIN b on a.a = b.b; SELECT a.*, b.* FROM a, b WHERE a.a = b.b(+);
    Full Outer Join

    This is very generous joining. This will return everything from a and b. It is also notable that this will not only return every individual element but it will match the number of elements and return null value for the columns that empty. For instance, if a has null, null, 1, 2, 3, 4 and b has 3, 4, 5, 6, null, null, Full Outer Join will return null, null, 1, 2, 3, 4, 3, 4, 5, 6, null, null.

    SELECT * FROM a FULL OUTER JOIN b on a.a = b.b; SELECT * FROM a FULL OUTER JOIN b on a.null = b.null will return everythin except common element. So it will return null, null, 1, 2, 5, 6, null, null
    def full_outter_join(arr1, arr2) arr_hash = Hash.new(0) arr1.each do |num| arr_hash[num] = 1 end arr2.each do |num| arr_hash[num]+=1 end arr_hash.select { |k,v| puts k if v <= 1 } end
    Left and Right Join

    Left Join will return all elements from left and nothing from right. Right join will return every elements from right column. This join will naturally returns common elements from other side.

    SELECT * FROM a LEFT JOIN b on a.a = b.b; SELECT * FROM a RIGHT JOIN b on a.a = b.b; SELECT * FROM a LEFT JOIN b on a.a = b.null; will return uniq a elements only SELECT * FROM a RIGHT JOIN b on a.null = b.b; will return uniq b elements only
    def left_join(arr1, arr2) arr_hash = Hash.new(0) arr1.each do |num| arr_hash[num] = 1 end arr2.each do |num| arr_hash[num]-=1 end arr_hash.select { |k,v| puts k if v == 1 } end

    Full details about join sql command can be found in here