Author Archives: Admin

Oracle 11g Joins and Subqueries

Convert a subquery to a join in Oracle 11g

SELECT first_name, department_id
FROM employees
WHERE department_id IN (SELECT department_id
FROM departments
WHERE LOCATION_ID = 1700)

Answer:

SELECT first_name, department_id, location_id
FROM employees JOIN departments USING (department_id)
WHERE location_id = 1700;

Winner: Ambuj

Inheritance in C++

What is Inheritance?

a. Provides a way to create a new class from an existing class
b. The new class is a specialized version of the existing class
c. Inheritance establishes an “is a” relationship between classes.

i. A poodle is a dog
ii. A car is a vehicle
iii. A flower is a plant

d. Terminology:

i. Base class (or parent) – inherited from
ii. Derived class (or child) – inherits from the base class
iii. Notation: class Student // base class { . . . }; class UnderGrad : public student // derived class { . . . };
f. An object of a derived class ‘is a(n)’ object of the base class

g. Example:

i. an UnderGrad is a Student
ii. a Mammal is an Animal

h. A derived object has all of the characteristics of the base class

i. An object of the derived class has:
i. all members defined in child class
ii. all members declared in parent class

j. An object of the derived class can use:

i. all public members defined in child class
ii. all public members defined in parent class

k. Use inheritance for these properties (to save time/reduce work)

Protected Members and Class Access

a. protected member access specification: like private, but accessible by:
i. Member functions of the class that originally declared the member.
ii. Friends of the class that originally declared the member.
iii. Classes derived with public or protected access from the class that originally declared the member.
iv. Direct privately derived classes that also have private access to protected members.

b. Class access specification: determines how private, protected, and public members of base class are inherited by the derived class
c. Access Modifiers
i. public – object of derived class can be treated as object of base class (not vice-versa)
ii. protected – more restrictive than public, but allows derived classes to know details of parents
iii. private – prevents objects of derived class from being treated as objects of base class.

Class diagram for inheritance:

Consider the GradedActivity, FinalExam, PassFailActivity, PassFailExam hierarchy

inheritance-class-diagram

 

 

C++ Instance and Static Members

Definitions

  • instance variable: a member variable in a class. Each object has its own copy.
  • Default variable modifier for classes
  • static variable: one variable shared among all objects of a class
  • if you change a static variable in one object, it gets changed in all objects
  • static member function: can be used to access static member variable; can be called before any objects are defined
  • cannot access any instance variables

static member variable example:

staticMember-Variables

 

staticMember-Variables-Tree

 

i. In the Tree class, every time a new instance is created, it increments the objectCount variable
ii. objectCount is the same across all Tree objects

c. static member function

i. Declared with static before return type: static int getObjectCount() const { return objectCount; }
ii. Static member functions can only access static member data
iii. Can be called independent of objects: int num = Tree::getObjectCount(); // no object needed to invoke the method, as getObjectCount is static

d. Why use static variables?

i. Unique ID’s for every instance of a class (based off of number of objects)

ii. Internal lookup tables – making the var static allows memory savings by keeping a single version for all instances of the class

e. The this pointer

i. this: predefined pointer available to a class’s member functions
ii. Always points to the instance (object) of the class whose function is being called [to override local scope]
iii. Is passed as a hidden argument to all non-static member functions
iv. Can be used to access members that may be hidden by parameters with same name
v. Example: class SomeClass { private: int num; public: void setNum(int num) { this->num = num; } … };

C++ Conditional Statements (if else)

Relational Operators

a. Use operators to make comparisons

i. > greater than
ii. < less than
iii. >= greater than or equal to
iv. <= less than or equal to
v. == equal to
vi. != not equal to

b. Boolean expressions are true or false

i. 12 > 5  true
ii. If x=10, then x==10 is true

c. Expressions can be assigned to a variable

i. true typecasted to an int is 1, false typecasted to an int is 0
ii. = denotes assignment, == denotes comparison

The if Statement

a. Used to make decisions on what code to execute

i. if the condition is false, skip the block of code
ii. Multiple statements can be contained within the {} of the if statement

b. Syntax: if (expression) statement;

i. Do not place a semicolon after (expression)
ii. Place statement; on a separate line after expression, indented

c. To evaluate:

i. If expression returns true, execute the statement
ii. Be careful testing floats and doubles for equality
iii. If expression returns false, skip the statement
iv. In C++ 0 is false, any other value is true

d. Curly braces { } create a block of code: Use these curly braces to execute more than one statement as part of an if statement:

Syntax: if (score > 90) { grade = ‘A’; cout << “Good Job!\n”; }

The if/else statement

a. Provides two possible paths of execution
b. Performs the code inside the if statement if the expression is true, otherwise performs the code inside the else block
c. Syntax:

if (expression) statement 1;
     // or block; execute this if expression is true
else statement 2; // or block; execute this if expression is false

Nested if Statements

a. An if statement that is nested inside another if statement
b. Can be used to test more than one condition
c. Syntax:

 

Nested If Statement

 

The if/else if Statement

a. Tests a series of conditions until one is found to be true

b. Often simpler than using nested if/else statements
c. Can be used to model thought processes

i. If it is winter, wear a coat
ii. Else, if it is fall, wear a jacket
iii. Else, wear sunglasses

d. Syntax:

if (expression 1) statement 1; // or block else
if (expression 2) statement 2; // or block // other
else if else if (expression) statement; // or block

Flags

a. Variables that signal a condition
b. Usually implemented as a bool variable
c. Can also be an integer
i. Like if statements, 0 is considered false and any other nonzero value is considered true
d. Must be assigned an initial value before it can be used

Logical Operators

a. Used to create relational expressions from other relational expressions
b. Operators:

i. && / AND – new relational expression is true if both expressions are true
ii. || / OR – new relational expression is true if either expression is true
iii. ! / NOT – reverses the value of an expression – true becomes false, and false becomes true

c. Syntax (int x = 12, y = 5, z = -4):

i. (x > y) && (y > z) –> true
ii. (x <= z) || (y != z) –> true
iii. !(x >= z) –> false

d. in C++ ! has highest precedence, followed by &&, then || (order of operations)

e. If the value of an expression can be determined by evaluating just the sub-expression on left side of a logical operator, then the sub-expression on the right side will not be evaluated (short circuit evaluation)

i. E.g. int x, y; x = 0; (x && y)  false Will not give an error because it did not check the value of y, x is already false

Examples

Use to test if value falls inside a range: if (grade >= 0 && grade <= 100) cout << “Valid grade”;
if (grade <= 0 || grade >= 100) cout << “Invalid grade”;

Comparing Characters and Strings

a. Characters compared using their corresponding ASCII values
i. ‘A’ < ‘B’ is true because the ASCII value of A (65) is less than the ASCII value of B (66)
ii. Lowercase letters have higher ASCII codes than uppercase letters ‘a’ > ‘Z’ is true
b. Strings are also compared using their ASCII values
i. The characters in each string must match before they are equal (case-sensitive)
ii. “Mary” < “Mary Jane” is true “Mary” <= “Mark” is false

The Conditional Operator

a. Shorthand version of if/else statement
b. Syntax: expr1 ? expr2 : expr3; If expr1 is true, execute expr2, else execute expr3

The switch Statement

a. Shorthand version of if/else if statements with the same condition
b. Syntax:

switch (expression) //integer {

case exp1: statement1;
break;
case exp2: statement2; break;
case exp3: statement3; break; …
case expn: statementn; break;
default: statementn+1;

}

 

 

C++ Expressions and Working with User Data

cin object

Standard console input
b. Need to add #include <iostream> for it to work
c. Reads input from keyboard
d. Use >> to set input from cin to a variable, as in:
cin >> your_variable (like your_variable = “my cin input”)
e. Cin automatically converts input into your variable’s datatype
f. If assigning multiple variables, use the notation:
cin >> first_variable >> second_variable >> third_variable
Your input looks like: “first_val second_val third_val” (separated by
spaces); order is important

C++ Mathematical Expressions

a. You can create complex expressions in C++
b. Expressions follow the order of operations

i. Negation first (e.g. NOT / !)
ii. Multiplication, division, modulus second (in order from left to
right)
iii. Addition/Subtraction third (in order from left to right)

c. Parentheses can override order of operations
d. For exponents, use power function pow(var, power)

Type Conversion

a. Operations can only be performed between objects of the same type
b. C++ will try to convert one type to the other, based on hierarchy of types

i. Long double (highest)
ii. Double
iii. Float
iv. Unsigned long
v. Long
vi. Unsigned int
vii. Int (lowest)

c. Type conversion can impact the result of calculations (precision!)
d. When C++ automatically converts, this is called Type Coercion; Coercion
rules:

i. Char, short, unsigned short automatically promoted to int
ii. Lower datatype gets promoted to higher datatype
iii. The result of expression will be converted to the type of the
variable the result is assigned to (e.g. if you have double var =
int foo + int blah; then int will be typecasted to type double)

e. Promotion: conversion to a higher type; Demotion: conversion to a lower
type

Overflow and Underflow

a. Occurs when value cannot fit within the bounds of the type being casted

i. Overflow occurs when value too big
ii. Underflow occurs when value too small

b. Variable is wrapped around instead, resulting in incorrect value
c. Some systems may give an error when this occurs, others continue on

Type Casting

a. Manual data type conversion (e.g. double to int)
b. Good for keeping precision when doing division with ints
c. Can use to see int value of a char (ASCII)
d. Casting expressions

i. Datatype(variable) e.g. int(foo)
ii. Static_cast<datatype>(variable) e.g. static_cast<int>(foo)

Multiple Assignment and Combined Assignment

a. Multiple assignment: Use to assign a single value to multiple variables

i. x = y = z = 5 versus x=5; y=5; z=5;
ii. Associates right to left x=(y=(z=5));

b. Combined assignment: Use to shorten your x=x*5 -type statements

i. sum+=1 instead of sum = sum+1
ii. Works with all operands +,-,*,/,%

Formatting Output

a. Used to control how numeric or string data is printed (size, position, num. of digits)

b. Use #include <iomanip> to use manipulator functions
c. Stream Manipulators: Used to control how an output field is displayed

i. See Table 3-12 on slide 43 for the various manipulators

Working with characters and string objects

a. Cin can cause problems, ignores leading whitespace
b. Use getline() to get around this issue
c. Use cin.get(variable) to read next character entered (even whitespace)
d. Don’t use cin >> and cin.get() together, can cause problems
e. Use cin.ignore() to skip characters (refer to slide 49 for params)
f. Find the length of a string with .length() (e.g. mystring.length() )
i. When using anything.method() then an object is involved
g. Join (concatenate) multiple strings together with + (string3 = string1 + string2) – can use ombined assignment operator

More Mathematical Library Functions

a. Use #include <cmath> for additional math functions

i. Trig, sqrt, log, abs val

b. Takes double as input, returns double
c. Use #include <cstdlib> for:

i. Rand() – random number generator (same sequence each time)
ii. Srand(x) – random number generator using unsigned int x (different sequence because it uses x as a “seed”)

Abstract Factory vs. Factory Method Design Pattern

Differences between abstract factory pattern and factory method pattern .

Both Abstract Factory and Factory design pattern are creational design pattern and they are used to decouple clients from creating object they need, But there is a significant difference between Factory and Abstract Factory design pattern, Factory design pattern produces implementation of Products e.g. Garment Factory produce different kinds of clothes, On the other hand Abstract Factory design pattern adds another layer of abstraction over Factory Pattern and Abstract Factory implementation itself e.g. Abstract Factory will allow you to choose a particular Factory implementation based upon need which will then produce different kinds of products. In short Abstract Factory design pattern creates Factory and Factory design pattern creates Products.

Difference between Abstract Factory and Factory Method design patterns in Java in point form:

1) One more difference between Abstract Factory and Factory Method design patterns is that Abstract Factory pattern uses composition to delegate responsibility of creating object to another class while Factory design pattern uses inheritance and relies on derived class or sub class to create object.

2) Abstract Factory may use Factory design pattern for creating objects but they are not just limited to that they can also use Builder design pattern to build object by doing series of steps or Prototype pattern to build object by copying or customizing prototype of that object. It completely depends upon your implementation whether to use Factory pattern or Builder pattern for creating products.

3) Factory method design pattern are modern way of creating objects. It offers some notable advantages over new() operator to create Objects e.g. By using Factory method design pattern client is completely decoupled with object creation code, which enforces Encapsulation and result is loosely coupled and highly cohesive system. Any change e.g. a new product from Factory requires almost no change in existing clients. On the other hand if we need an additional level of abstraction over your Factory pattern than Abstract Factory is the right design pattern to use. Abstract Factory allows us to use different Factory implementation for different purpose. Abstract Factory pattern can be implemented using Factory method and Singleton design pattern in Java. One of the best example of Abstract Factory and Factory pattern In Java is DocumentBuilderFactory and DocumentBuilder javax.xml.parsers package.

In short Abstract Factory design pattern provides abstraction over Factory Method pattern itself while Factory Method design pattern provides abstraction over products[1].

References: 1. http://javarevisited.blogspot.com/2013/01/difference-between-factory-and-abstract-factory-design-pattern-java.html 

 

lateral clauses in SQL

What alternatives offered for lateral clauses in SQL server, MySQL and Oracle:

select name, salary, avg_salary
from instructor I1,
lateral (select avg(salary) as avg_salary
from instructor I2
where I2.dept_name= I1.dept_name);

 

SQL;2003 allows a subquery in the from clause that is prefixed by lateral key word to access the attributes of preceding tables or sub queries in the from clause. Example SQL is given in the question.  Without the lateral clause, the sub query cannot access the correlation variable I1 from the outer query. Currently only few SQL implementations such as IBM DB2 support the lateral clause.

The Apply operator invokes a table-valued function for every row in the outer record set. The table valued function is evaluated for each row of the outer record set. Rows returned from table valued function are joined to the row of the outer record set using UNION ALL operation. There are two types – Cross Apply or Outer Apply.
Syntax: 

SELECT <column list> FROM
left_table_source { CROSS | OUTER } APPLY right_table_source

CROSS APPLY returns only rows from the outer table (left_table_source) that produce a result set from the table-valued function (right_table_source). It is like the Left Outer
join. OUTER APPLY returns both rows that produce a result set, and rows that do not match, with NULL values in the columns produced by the table-valued function. It is like
the Full outer join operation.

MySQL – We can achieve the lateral functionality (eg: using correlated sub queries in the FROM clause) by using ON clause of a JOIN operation. But it would then fetch the
entire inner query row set to join against the outer row which may not very efficient. Correlated subquery is a subquery that uses the information from the outer query. In
where clause, the correlated sub query can be used which references columns of outer query and the inner query executes for every row of outer query.

Oracle – The LATERAL key word is supported and is used to designate sub query as a lateral inline view. In the lateral inline view, we can denote tables that appear on left of
lateral inline view in the FROM clause of a query. This kind of left correlation can be specified anywhere within the sub query (like SELECT, FROM, and WHERE clauses)
and at any nesting level.

Example:

SELECT * FROM employees e, LATERAL (SELECT * FROM departments d
WHERE e.department_id = d.department_id);

Also equivalent of the SQL server’s APPLY clause in Oracle is a lateral JOIN. A lateral join in Oracle is when you join a table A with a function F that outputs rows and this
function has columns of A as parameters.