Complexity and Recursion

Complexity and Recursion



Complexity of Algorithms - how to find out how it is log



Table to calculate - which complexity



If there is a power: n^2




Logic of what? Chapters of logic.

Advanced Topics in SQL - SQL tutor / class in NYC New York Manhattan

SQL tutor / class in NYC New York Manhattan
Basic Topics:
Executing a Simple Query
Working with Functions
Organizing Data
Retrieving Data from Multiple Tables using Joins
Presenting Query Results
Querying Using Subqueries
Manipulating Table Data
Views
Indexing
Transactions
Decision
Format
Stored Procedures
Automation


Converting numbers to dates using timestamps. - this is topic of Orcale SQL, but I guess I will get something related to SQL Server.

Exceptions, error handling in the code
http://logicalread.solarwinds.com/sql-server-exception-handling-try-catch-throw-mc03/#.Vxj1I_l97IU
http://blog.sqlauthority.com/2007/04/11/sql-server-2005-explanation-of-trycatch-and-error-handling/

Handling duplicates

Date functions
http://www.w3schools.com/sql/sql_dates.asp
https://technet.microsoft.com/en-us/library/aa258265(v=sql.80).aspx
https://msdn.microsoft.com/en-us/library/ms186724.aspx

Intercept, except, union, union all - the differences between them

Inline Table-Valued Functions
https://technet.microsoft.com/en-us/library/ms189294(v=sql.105).aspx
https://www.youtube.com/watch?v=hs4mReAzESc

Recursive Queries,
This is done using CTE - where we will keep doing some recursion until we are done

APPLY Operator,



Offset Window

TOP and OFFSET-FETCH;


http://dbadiaries.com/new-t-sql-features-in-sql-server-2012-offset-and-fetch

Data Modification; Working with Date and Time;
http://sqlmag.com/t-sql/t-sql-classic-date-functions

Questions on Date:
http://www.sql-server-helper.com/faq/dates-p01.aspx
Many interesting questions are listed on:
http://stackoverflow.com/questions/tagged/sql-date-functions

Programmable Objects, Transactions and Concurrency, Exception Handling); In-Memory OLTP.

Along the course you will learn how to use T-SQL to solve practical problems such as:

Relational Division, Missing and Existing Ranges (Gaps and Islands),

Separating Elements, Pivoting and Unpivoting, Ranking and Offset, Running Totals, Moving

Averages, YTD, Custom Aggregations, Dynamic Analysis of Grouping Sets, TOP and

OFFSET-FETCH Problems,

Paging,

Data De-Duplication,

Handling Sequences,

Merging Data,

Treatment of Temporal Intervals (Intersection, Max Concurrent, Packing),

Dynamic Filtering,

Migrating On-Disk to Memory Optimized Data,

Internals and index tuning,

including coverage of Column store data, index access methods, temporary

tables,

set vs. cursors,
http://stackoverflow.com/questions/24168/why-are-relational-set-based-queries-better-than-cursors

Cursors enable manipulation of whole result sets at once. In this scenario, a cursor enables the rows in a result set to be processed sequentially. In SQL procedures, a cursor makes it possible to define a result set (a set of data rows) and perform complex logic on a row by row basis.

query tuning using query.

Triggers

HIERARCHYID datatype
https://www.simple-talk.com/blogs/2012/04/24/hierarchical-queries-with-common-table-expressions/

New York
New York city
Manhattan
SQL Tutor Trainer



Advanced SQL Topics

Functions, Stored Procedures, Packages
Pivoting data: CASE & PIVOT syntax
Hierarchical Queries
Cursors: Implicit and Explicit
Triggers
Dynamic SQL
Materialized Views
Query Optimization: Indexes
Query Optimization: Explain Plans
Query Optimization: Profiling
Data Modelling: Normal Forms, 1 through 3
Data Modelling: Primary & Foreign Keys
Data Modelling: Table Constraints
Data Modelling: Link/Corrollary Tables
Full Text Searching
XML
Isolation Levels
Entity Relationship Diagrams (ERDs), Logical and Physical
Transactions: COMMIT, ROLLBACK, Error Handling

Dynamic SQL

Dynamic SQL




Example from AdventureWorks:
http://www.sqlusa.com/bestpractices/dynamicsql/
http://sqlusa.com/bestpractices/training/scripts/dynamicsql/
https://www.mssqltips.com/sqlservertip/1160/execute-dynamic-sql-commands-in-sql-server/
http://stackoverflow.com/questions/2588013/dynamic-sql-to-query-an-adventureworks-table
http://www.sqlteam.com/article/using-dynamic-sql-in-stored-procedures
http://oakdome.com/programming/SQL_DynamicSQL_sp_executesql.php
http://www.kodyaz.com/articles/t-sql-pivot-tables-in-sql-server-tutorial-with-examples.aspx
https://www.brentozar.com/archive/2013/11/filtered-indexes-and-dynamic-sql/

Dynamic SQL and Procedures:
https://www.youtube.com/watch?v=MiAwOoelu9k

SQl Server Dynamic SQL:
https://www.youtube.com/watch?v=jS3kpNavKM8

Dynamic SQL / Adhoc SQL (6 min short video):
https://www.youtube.com/watch?v=aRr6q4V0sXY

Table names would vary at run time and schema would vary at runtime:
https://www.youtube.com/watch?v=n3odG4bl4LE

Advanced SQL

Advanced SQL consist of new queries in SQL

There is over 100 videos and interview questions on: https://www.youtube.com/user/kudvenkat

i want to master of joins, paritition, views, trigger, dynamicswql

what database do you practice?

functions, proc, in oracle pl sql

http://docs.oracle.com/cd/E11882_01/server.112/e41084/statements_10002.htm#i2077142

SELECT

The WITH query_name clause lets you assign a name to a subquery block. You can then reference the subquery block multiple places in the query by specifying query_name. Oracle Database optimizes the query by treating the query name as either an inline view or as a temporary table.

docs.oracle.com

i have both oracle sql and ms server 2008

try 2 question for today

1.

find the 2nd highest salary of employees table

2. list employees alongwith their manager in employees table

this was asked in interview with me in the currentjo

3. find information of those employees whose salary is greater than average salary of their respective departments

after that query that I sent you

it returns 1st row of each join no matter how many matches are there

if in two tables you only want to join first row and no other row

you need to use row_number, partition by and subquery

this question is trending now in interviews

select * from employees x, (select DEPARTMENT_ID,avg(salary) avg_salary from employees group by department_id) y where x.department_id=y.department_id and x.salary>y.avg_salary;

this is query to compare salary of each employee against avg salary of department and display only those with highest salary

can you write query to select max salary for each department?

using cte

WITH cteRowNum AS ( SELECT DeptID, EmpName, Salary, DENSE_RANK() OVER(PARTITION BY DeptID ORDER BY Salary DESC) AS RowNum FROM EmpDetails ) SELECT DeptID, EmpName, Salary FROM cteRowNum WHERE RowNum = 1;

These analytical functions are known as window function in sql server

CTE:
http://sql-beginner.blogspot.co.il/2012/09/difference-between-cte-and-sub-query.html
https://technet.microsoft.com/en-us/library/ms190766(v=sql.105).aspx

https://en.wikipedia.org/wiki/Hierarchical_and_recursive_queries_in_SQL
https://technet.microsoft.com/en-us/library/ms186243%28v=sql.105%29.aspx?f=255&MSPPError=-2147217396
https://www.youtube.com/playlist?list=PL08903FB7ACA1C2FB
http://docs.oracle.com/cd/E11882_01/server.112/e41084/statements_10002.htm#i2077142
dynamic sql

R code for Breaking X/Y axis

# http://stackoverflow.com/questions/19612348/break-x-axis-in-r
# http://www.statmethods.net/advgraphs/axes.html
# http://www.inside-r.org/packages/cran/plotrix/docs/axis.break


# Example 1
x <- c(9.45, 8.78, 0.93, 0.47, 0.24, 0.12)
y <- c(10.72, 10.56, 10.35, 10.10, 9.13, 6.72)
z <- c(7.578, 7.456, 6.956, 6.712, 4.832, 3.345)
plot(x, y, col='blue', pch=16, xlab= 'x', ylab='y, z')
points(x, z, col='red', pch=17)
library(plotrix)
axis.break(1,2,style="slash")



#Break X axis

xgap <- ifelse(x > 8, x-6, x)
#Possibly you'd want to check if there are values between 2 and 8.
plot(xgap, y, col='blue', pch=16, xlab= 'x', ylab='y, z', xaxt="n")
points(xgap, z, col='red', pch=17)
xat <- pretty(xgap)
xat <- xat[xat!=2]
xlab <- ifelse(xat>2, xat+6, xat)
axis(1,at=xat, labels=xlab)
library(plotrix)
axis.break(1,2,style="slash")


library(plotrix)
par(bty="n") # deleting the box
gap.plot(x,y, gap=c(2,7.5), gap.axis="x", pch=16,
         col="blue", ylim=range(c(y,z)),
         xtics=c(0:3,8:10), xticlab=c(0:3,8:10))

gap.plot(x,z, gap=c(2,7.5), gap.axis="x", pch=17,
         col="red", ylim=range(c(y,z)), add=TRUE); axis(2)

abline(v=seq(1.99,2.09,.001), col="white")  # hiding vertical lines
axis.break(1,2,style="slash")  



# Break Y Axis

ygap <- ifelse(y > 8, y-6, y)
#Possibly you'd want to check if there are values between 2 and 8.
plot(ygap, x, col='blue', pch=16, xlab= 'x', ylab='y, z', yaxt="n")
points(ygap, z, col='red', pch=17)
yat <- pretty(ygap)
yat <- yat[yat!=2]
ylab <- ifelse(yat>2, yat+6, yat)
axis(2,at=yat, labels=ylab)
library(plotrix)
axis.break(2,2,style="slash")

OLAP Cubes in SQL Server for Adventure Works

OLAP Cubes in SQL Server for Adventure Works





References:
https://techpunch.wordpress.com/2008/09/08/sql-server-2008-how-to-build-and-deploy-adventureworks-olap-cubes/

https://www.youtube.com/watch?v=ctUiHZHr-5M

To install Other Databases check out
https://www.youtube.com/watch?v=iKVbx5IeUvQ

Importing HTML file in Excel using VBA

This proc opens HTML files in excel, copy it into the sheet and then close the html without saving.
This ways you can import data from various html files into excel.
This can be modified to import values from other format like XML also.
This is a although very mundane way but VBA 6 is very old, VBA.NET might have something better.

For learning more VBA skills please check out our courses at Qcfinance.in

Sub Import()
Dim oExcel As Excel.Application
Dim ws, ws2, ws3 As Worksheet
Dim wb As Variant
Dim wkb As Variant

Set activewkb = ThisWorkbook
Path = ActiveWorkbook.Path

   Dim StrFile As String
    Path = ActiveWorkbook.Path
   ' MsgBox Path
    StrFile = Dir(Path & "\HTML\")
    Do While Len(StrFile) > 0
        MsgBox StrFile
        StrFile = Dir
   ' MsgBox StrFile
       
    Set wb = Workbooks.Open(Path & "\HTML\" & StrFile)
On Error Resume Next
'MsgBox ActiveWorkbook.Path

For Each sh In wb.Sheets
      sh.Copy after:=activewkb.Sheets(activewkb.Sheets.Count)
   Next sh

'MsgBox "The name of the active workbook is " & ActiveWorkbook.Name
wb.Close savechanges:=False

Loop
End Sub

Debug Command to print Files

Sub LoopThroughFiles2()
    Dim StrFile As String
    Path = ActiveWorkbook.Path
    StrFile = Dir(Path & "\")
    Do While Len(StrFile) > 0
        Debug.Print StrFile
        StrFile = Dir
    Loop
End Sub

Demand of Java and Javascript will create the most Jobs in 2015

Since we are in IT Boom the highest demand would still be in the same areas as it has been in last five years.

I am attaching below the images and charts of the most popular coding language which will pay you the best salary in 2015.

Python is used extensively in Big Data Analytics with the Panda library.

JavaScript and full stack developer, Angular JS,  are some terms that I see most often.

Big data boom is still be realized but most of the work still remains in labs.