General:
PostgreSQL vs. MySQL: Which Is the Best Open Source Database?
Interview:
Database developer interview questions
Questions to ask a SQL Server database developer applicant
Data-Warehousing
Friday, March 21, 2014
Articles
General:
PostgreSQL vs. MySQL: Which Is the Best Open Source Database?
Interview:
Database developer interview questions
Questions to ask a SQL Server database developer applicant
Data-Warehousing
Resource
*******************************************************************************
Relational Database:
coursera-Stanford University-Introduction to Databases
*******************************************************************************
Relational Database:
:Schema - database structure, relation of different attributes.)
:Data type
:DDL - Data Definition Language (Create,)
: DML - Data Manipulation Language(Select,Insert,Update ,)
:Querying relational databases
Indexes:
Constraints & Triggers:
Transactions:
Views:
Authorization:
On-Line Analytical Processing (OLAP)
XML Data:
Querying XML:
JSON Data:
NoSQL Systems
Source Article : Hadoop-NoSQL Software and Services Market Forecast 2012-2017
*******************************************************************************
Treehouse: Learn Web Design, Web Development, and More
Learn HTML, CSS, iPhone apps & more.
Learn how to build websites & apps, write code or start a business.
Source Article: Facebook’s Profit Propelled by Mobile
...
Mobile devices accounted for nearly two-thirds of Facebook’s revenue, which at this point mostly comes from ads shown on the Facebook website and apps. However, the company is beginning to supplement that with ads on other sites.
...
*******************************************************************************
Business Intelligence for SQL Server 2012
Beginner SQL Tutorial
*******************************************************************************
Learn .NET in 60 days
Oracle Architecture in less than 10 minutes
*******************************************************************************
Queries:
SQL 2008 Retrieving parent-child hierarchy from query
SQL Server forums > Transact-SQL
;WITH EmpCTE(empid, empname, mgrid, lvl)
AS
(
SELECT empid, empname, mgrid, 0
FROM Employees
WHERE empid = 14 --input the leaf empid
UNION ALL
SELECT E.empid, E.empname, E.mgrid, M.lvl+1
FROM Employees AS E
JOIN EmpCTE AS M
ON E.empid = isnull(M.mgrid,0)-- search the mgrid according the mgrid on leaf empid
)
SELECT * FROM EmpCTE
--result
empid empname mgrid lvl
----------- ------------------------- ----------- -----------
14 James 11 0
11 David 7 1
7 Robert 3 2
3 Janet 1 3
1 Nancy NULL 4
----------------------
Departures from Origins and Arrivals at Destinations
*******************************************************************************
Unix Command Summary
Java Basic Syntax
*******************************************************************************
Erwin Data Modeler:
Erwin Data modeler
CA ERwin Data Modeler: Soup to Nuts Demo
ERwin Data Modeler WorkSpace
Metadata Integration from CA ERwin Data Modeler to Informatica PowerDesigner
Toad World:
ERWin models to Toad Data Modeler
Importing Erwin Data Model
Data Modelers - Alternatives to Erwin
What are the TOAD & ERWIN? What are the difference between them?
posted in BusinessObjects Reporting Tool (BO)
- TOAD is a software tool to connect to Oracle database (Oracle Client side tool)
- Erwin is a tool to do data modeling in logical and physical level
SWITCHING DATA MODELING TOOLS? OUCH!
*******************************************************************************
ETL Tools:
Datastage Tutorials-Datastage ETL Tool Datastage Frequently asked questions, Datastage Interv
*******************************************************************************
Programming Language:
The Python Tutorial
PL/SQL Tutorial
*******************************************************************************
How to improve LIKE Operator performance
Source : How to improve LIKE Operator performance
I have a ZIPCode list in a temp table (10,000 records) . I need to get all records where the zip code MATCH with the zip codes in the list.
If I do exact match, I get result in 1-2 second ( first query) but if I do a LIKE as in second query it takes more than a minute.
First query use Hash Join where other do Nested Loop.
Any way we can improve the performance of second query?
SELECT count(*) FROM Panelist (NOLOCK)
WHERE EXISTS (SELECT TOP 1 1 FROM ##ZIPLIST WHERE Panelist.PostalCodeVal = VALUE )
go
SELECT count(*) FROM Panelist (NOLOCK)
WHERE EXISTS (SELECT TOP 1 1 FROM ##ZIPLIST WHERE Panelist.PostalCodeVal like VALUE +'%')
go
SQL Server Performance Monitoring Tools - Microsoft's SQL Server Profiler - Query Performance Tuning
************************************************************************
Query Performance Tuning Performance tuning is half science and half art form
Performance Tuning Tips
SQL Server Performance Monitoring Tools - Microsoft's SQL Server Profiler
Optimize SQL Server queries with these advanced tuning techniques
Thursday, February 20, 2014
SQL:Group By
All column names in SELECT list must appear in GROUP BY clause unless name is used only in an aggregate function
http://stackoverflow.com/questions/5986127/do-all-columns-in-a-select-list-have-to-appear-in-a-group-by-clause
Include non-aggregate column in group by clause (with a slight wrinkle)
http://stackoverflow.com/questions/8849552/include-non-aggregate-column-in-group-by-clause-with-a-slight-wrinkle
Getting summarizing values
http://www.sql-ex.ru/help/select4.php
----------
Column is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause
http://stackoverflow.com/questions/6456727/column-is-invalid-in-the-select-list-because-it-is-not-contained-in-either-an-ag
;WITH cte AS
(
SELECT *
FROM GREScores g
WHERE g.applicationID = 1
)
SELECT
g.applicationId,
-- (another 100 or so columns just like above)
AScore =(select max(g2.AScore) FROM cte g2) ,
APercentile =(select max(g2.APercentile) FROM cte g2)
FROM cte g
----------
T-SQL GROUP BY: Best way to include other grouped columns
http://stackoverflow.com/questions/626788/t-sql-group-by-best-way-to-include-other-grouped-columns
You can get it to work with something around these lines:
select e.empID, fname, lname, title, dept, projectIDCount
from
(
select empID, count(projectID) as projectIDCount
from employees E left join projects P on E.empID = P.projLeader
group by empID
) idList
inner join employees e on idList.empID = e.empID
This way you avoid the extra group by operations, and you can get any data you want. Also you have a better chance to make good use of indexes on some scenarios (if you are not returning the full info), and can be better combined with paging.
~~~
select e.empID, e.fname, e.lname, e.title, e.dept, p.projectIDCount
from employees e
inner join ( select projLeader, count(*) as projectIDCount
from projects
group by projLeader
) p on p.projLeader = e.empID
http://stackoverflow.com/questions/5986127/do-all-columns-in-a-select-list-have-to-appear-in-a-group-by-clause
Include non-aggregate column in group by clause (with a slight wrinkle)
http://stackoverflow.com/questions/8849552/include-non-aggregate-column-in-group-by-clause-with-a-slight-wrinkle
Getting summarizing values
http://www.sql-ex.ru/help/select4.php
----------
Column is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause
http://stackoverflow.com/questions/6456727/column-is-invalid-in-the-select-list-because-it-is-not-contained-in-either-an-ag
;WITH cte AS
(
SELECT *
FROM GREScores g
WHERE g.applicationID = 1
)
SELECT
g.applicationId,
-- (another 100 or so columns just like above)
AScore =(select max(g2.AScore) FROM cte g2) ,
APercentile =(select max(g2.APercentile) FROM cte g2)
FROM cte g
----------
T-SQL GROUP BY: Best way to include other grouped columns
http://stackoverflow.com/questions/626788/t-sql-group-by-best-way-to-include-other-grouped-columns
You can get it to work with something around these lines:
select e.empID, fname, lname, title, dept, projectIDCount
from
(
select empID, count(projectID) as projectIDCount
from employees E left join projects P on E.empID = P.projLeader
group by empID
) idList
inner join employees e on idList.empID = e.empID
This way you avoid the extra group by operations, and you can get any data you want. Also you have a better chance to make good use of indexes on some scenarios (if you are not returning the full info), and can be better combined with paging.
~~~
select e.empID, e.fname, e.lname, e.title, e.dept, p.projectIDCount
from employees e
inner join ( select projLeader, count(*) as projectIDCount
from projects
group by projLeader
) p on p.projLeader = e.empID
Monday, February 10, 2014
SQL - IF statements
Refer Source Video: SQL - IF statements
************************
The Basic Syntax of IF:
************************
DECLARE @NumFilmsCategory1 INT
DECLARE @NumFilmsCategory2 INT
SET @NumFilmsCategory1 = (SELECT Count(*) FROM tblFilm WHERE FilmGenreID = 1)
SET @NumFilmsCategory2 = (SELECT Count(*) FROM tblFilm WHERE FilmGenreID = 2)
IF @NumFilmsCategory1 >0
BEGIN
PRINT 'message here'
PRINT 'message here'
IF @NumFilmsCategory2 >0
BEGIN
PRINT 'message here'
END
ELSE
BEGIN
PRINT 'message here'
END
END
ELSE
BEGIN
PRINT 'message here'
PRINT 'message here'
END
************************
Stored Procedure:
************************
USE Movies
Go
CREATE PROC spVariableData( @InfoType VARCHAR(9) )
AS
BEGIN
IF @InfoType='ALL'
BEGIN
(SELECT * FROM tblFilm)
RETURN
END
IF @InfoType='AWARD'
BEGIN
(SELECT FilmName FROM tblFilm)
RETURN
END
--if other than above ones
SELECT 'You must choose ALL or AWARD'
END
--
EXEC spVariableData @InfoType='ALL'
--
EXEC spVariableData @InfoType='AWARD'
************************************************************************
How to Use Variables, IF and CASE in Database Interactions with TransactSQL
SET NOCOUNT (Transact-SQL)
Stops the message that shows the count of the number of rows affected by a Transact-SQL statement or stored procedure from being returned as part of the result set.
************************
The Basic Syntax of IF:
************************
DECLARE @NumFilmsCategory1 INT
DECLARE @NumFilmsCategory2 INT
SET @NumFilmsCategory1 = (SELECT Count(*) FROM tblFilm WHERE FilmGenreID = 1)
SET @NumFilmsCategory2 = (SELECT Count(*) FROM tblFilm WHERE FilmGenreID = 2)
IF @NumFilmsCategory1 >0
BEGIN
PRINT 'message here'
PRINT 'message here'
IF @NumFilmsCategory2 >0
BEGIN
PRINT 'message here'
END
ELSE
BEGIN
PRINT 'message here'
END
END
ELSE
BEGIN
PRINT 'message here'
PRINT 'message here'
END
************************
Stored Procedure:
************************
USE Movies
Go
CREATE PROC spVariableData( @InfoType VARCHAR(9) )
AS
BEGIN
IF @InfoType='ALL'
BEGIN
(SELECT * FROM tblFilm)
RETURN
END
IF @InfoType='AWARD'
BEGIN
(SELECT FilmName FROM tblFilm)
RETURN
END
--if other than above ones
SELECT 'You must choose ALL or AWARD'
END
--
EXEC spVariableData @InfoType='ALL'
--
EXEC spVariableData @InfoType='AWARD'
************************************************************************
How to Use Variables, IF and CASE in Database Interactions with TransactSQL
SET NOCOUNT (Transact-SQL)
Stops the message that shows the count of the number of rows affected by a Transact-SQL statement or stored procedure from being returned as part of the result set.
Subscribe to:
Posts (Atom)