Fatworm 2014:Code Review 1

Code Review 1

Code Review 1 requirement

1.Print the common tree of one of the given statements.

2.Print your logical plan (logical intermediate result) of one of the given statements.

3.Answer two of the given questions.

Statements

1.

  select min(A.a), max(B.b), C.c
  from A, B, C
  where A.a > B.b
  group by C.c
  having C.c > 0

2.

  select * from
  (select a.ch*10000000+b.ch*1000000+c.ch*100000+d.ch*10000+e.ch*1000+f.ch*100+g.ch*10+h.ch as ans 
   from chars as a, chars as b, chars as c, chars as d, chars as e,chars as f,chars as g,chars as h order by ans) as tab
  where ans <21 or ans > 99999978 
  order by ans

3.

  select distinct from_airport.time_zone_code,to_airport.time_zone_code,
                 (arrival_time/100*60+arrival_time%100-departure_time/100*60-departure_time%100-time_elapsed)/60 AS time_zone_diff 
  FROM flight,airport AS from_airport,airport AS to_airport 
  WHERE flight.from_airport=from_airport.airport_code AND flight.to_airport=to_airport.airport_code

4.

  select a, count(b) as countB
  from A
  where b in (select b from B where b > 0 )
  group by a
  order by countB

Questions

1.How do you handle alias?

2.How do you handle aggregate functions？

 Explain how do you plan to execute the following statement: 
   select min(a) + max(b) from test1 as t1, test2 as t2 where t1.a = t2.b or a > b

3.How do you handle group_by?

 Explain how do you plan to execute the following statement: 
   select min(a) from t group by t.b

4.How do you handle sort by?

 Explain how do you plan to execute the following statement:
  select * from
  (select a.ch*10000000+b.ch*1000000+c.ch*100000+d.ch*10000+e.ch*1000+f.ch*100+g.ch*10+h.ch as ans 
   from chars as a, chars as b, chars as c, chars as d, chars as e,chars as f,chars as g,chars as h order by ans) as tab
  where ans <21 or ans > 99999978 
  order by ans

5.What is the difference between where and having?

 Explain how do you plan to execute the following statement:
   select t1.a, count(t2.b) from test1 as t1, test2 as t2, test1 as t3, test2 as t4 where t1.a = t3.a and t2.b = t4.b group by t1.a having t1.a > 3

6.How to deal with null?

7.Explain how do you plan to execute the following statement:

   select name, number, score from Classes 
   where number > ANY(select number from Classes)

8.Explain how do you plan to execute the following statement:

   select distinct customer_name from borrower 
   where customer_name in (select customer_name from depositor)

9.Explain how do you plan to execute the following statement:

  update account set balance = (select amount  from loan where branch_name = 'Mianus') where balance >= 900

10.How do you store your schema

11.How do you store your tuples, how to handle length-variant variables? How to find a field by its name according to the schema?

12.How do you maintain your file system?

13.How do you plan to support database reconnection?

14.How do you store and update schema when executing a query?

15.Describe your overall design and architecture.