Hey Sajha guys,

This thread is dedicated to all SQL professonals/Learners where we can discuss about SQL problems. you can post any tips related to SQL. Hopefully you guy will post ur poblems n tips to share with other sajha pps

thanks

Tips 1 list all database sizes in megabytes:

EXEC sp_MSforeachdb @command1="select '?', 0.0078125 * sum(size) from ?..sysfiles"

Tips 2 list all the primary keys, including composite primary keys, in the AdventureWorks database:

USE AdventureWorks;

SELECT

TableName = o.name,

PrimaryKey = co.name,

Rows = i.rows

FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS c

INNER JOIN sysindexes i

ON c.constraint_name = i.name

and CONSTRAINT_TYPE = 'PRIMARY KEY'

INNER JOIN sysindexkeys k

ON i.id = k.id

AND i.indid = k.indid

INNER JOIN sys.columns co

ON i.id = co.object_id AND

k.colid = co.column_id

INNER JOIN sys.objects o

ON co.object_id = o.object_id

WHERE o.type = 'U'

AND i.rows > 0

order by TableName, PrimaryKey

Tips 3 How to find the nth highest price by color?

use AdventureWorks

select Color, ListPrice

from

(select Color,

ListPrice,

SequenceNo=row_number() over (partition by Color order by ListPrice desc)

from Production.Product

where ListPrice > 0)a

where SequenceNo=5

 

will be continue........

If u guys are really so eager to discuss, cud u pls start teaching SQL from beginners level. I am sure that there are lots of guys in Sajha who are even more eager to learn from you guys. Truely speaking I am one of them... pls help me learn SQL and I really know nothing about it..... Pls start for the beginners..............

Hey Hariyo Kagaj, thanks for showing interest on SQL. Its not possible to learn thro. Sajha. but i do suggest how to start:

1) download trail version of SQL server from Microsoft site.

2) Get one book for begining SQL and practice.

If you have problem on Practice, then we can discuse here.

beside that, i have lots of ebook and video tutorial on SQL. if you need it just email me. and by the way am gonna post video tutorials from begining of sql .

any way all the best . welcome to sql world.

Rawbee

Thanks buddy

Hey Rawbee ,

          When u going to post the video tutorial. i'm also interted in learning basic sql.

 

Great Job rawbee!!

Hey guys, I know this thread is not meant for this, but if u guys really want to start learning from scratch follow this link, its really helpful. This is for the beginnners.

http://www.w3schools.com/sql/default.asp

Khusbhu am gonna post some begining vedio tutorial this weekend.

and thanks for techguy. really appriciate for ur support.

keep posting....

some other small tip for starters are

NULL is not equal to '' (blank)some time you save '' in your rows thru apps and web apps so to help with that try

SELECT * FROM SOMETABLE WHERE ISNULL(COLUMNNAME, '')<>''

i like to play with isnull a lot
lets say u have a proc with @value1 int=NULL lets say you want to filter the rows when the @value1 is not null

fastest way to do this is

SELECT * FORM SOME TABLE WHERE ISNULL(@value1, COLUMNYOUWHATTOFILTERBY)=COLUMNYOUWHATTOFILTERBY

similarly another ISNULL trick to substitute CASE is

lets say you have a query

SELECT
     COLUMN1,
    CASE COLUMN2 IS NOT NULL THEN
          COLUMN2
     ELSE
           COLUMN3
     END AS SOMECOLUMNNAME

You can do the same with

SELECT COLUMN1, ISNULL(COLUMN2, COLUMN3) AS SOMECOLUMNNAME

 

Thanks Cyberdude..keep posting ur tips...\

TIPS : How to backup database with datetimestamp in file name?

USE master;

DECLARE @FileName NVARCHAR(1024)

SET @FileName = 'F:\data\AdventureWorks2008\backup\AW2008'+REPLACE(REPLACE(CONVERT(varchar(100), GETDATE(), 100),':','_'),' ','_')+'.BAK'

SET @FileName = REPLACE(@FileName,'__','_')

PRINT @FileName

BACKUP DATABASE AdventureWorks2008 TO DISK = @FileName WITH NOFORMAT, INIT, NAME = N'AdventureWorks2008-Full Database Backup'

GO

Is that helpful for you????????

Continue..............

Tips :How to count Sundays between two dates?

USE AdventureWorks

DECLARE @StartDate DATETIME, @EndDate DATETIME

SET @StartDate = '2009-01-01'

SET @EndDate = '2010-06-01'

SELECT Sundays=COUNT(*)

FROM (SELECT TOP (DATEDIFF (DAY, @StartDate, @EndDate) +1)

[Date] = DATEADD(DAY, ROW_NUMBER()

OVER(ORDER BY c1.name, c2.name), CONVERT(char(10),@StartDate-1,110))

FROM sys.columns c1

CROSS JOIN sys.columns c2) x

WHERE DATEPART(dw,[Date]) = 1;

 

Result:

Sundays
74

 

Continue...................

रवी त खतराको SQL गुरु रहेछ यार।

 

hehe  suman² . testo खतरा ni chhunia just working this fields from couples of years. so just sharing my experiances and trying to get some tips from others too.....

Hello guys,

Sorry for unable to post video turotrials last weekend. actually those file are in WMV and huge in size. I am trying to convert into another format but dint get any free software for that. Do you guys have any idea for those software that converts WMV file and can split files?

Hi rawbee,

Its nice you have started the thread for SQL tips..really good.. I am a beginner and wanted to work as a DBA rather than a developer coz i dont have dev skills.. but want to learn. I know all the things like scipts all the things but the problem is like i have no confidence in writing scripts or you can say i do not know to create logics.. Can you give me tips what kind of job will be there and what you have to do?? Your project experience I mean....

Thanks

Stylish:

It is always good to start with Developer. I started my career as a BA and then Oracle Dev/DBA and then now in both SQL and Oracle.

there are few standard things that you have to do as a DBA:

1. Writing Scripts such as restore db, backup db, sps to generate reports of several jobs in the server, script to monitor the performacne of the database, finding size, growth of the databases to find the bottlenecks, and etc.

2. SPs and sheel script to maintain, manage and tune the database.

3. Backup the DBs.

4. Restore the DB.

5. Check the jobs every day to make sure they are running properly and fix them if failed..

6. Make database up and running.

7. Tune and optimize the database.

 

These are standard jobs i do as a DBA. There will be a whole list.

Let me know if you have any specific questions from Database perspective, i will be more than happy to answer or help you out whatever i know. Thank you and good luck

 

stylish ji:

Yes I agree with acbel ji. Lots of guys think DBA is easier then Developer  considering only using GUI tools and wizards, in fact it’s true in some way but when you get real problems in DBA, you have to solve it immediately but as far for Developer they get certain deadlines for specific task. And DBA jobs are more responsible and risky job too.

 

If you have developer experience you can move into DBA easily DBA-Dev is not so easy. In MS SQL the main Tasks are:

• Installing and upgrading SQL Server
• Monitoring the database server's health and tuning it accordingly
• Using storage properly
• Backing up and recovering data
• Managing database users and security
• Establishing and enforcing standards
• Performing data transfer
• Setting up and maintaining data replication
• Setting up server scheduling
• Providing 24-hour access
• Working with development teams

But if you chose as MS SQL developer you have 3 major fields make you carrier:

-         Report Developer

-         ETL(Integration service) developer

-         Data Analyst

 

The main Responsible for developer as vary on their job but basically the following main tasks are:

-         Write a function and store procedures.

-         Quarry tuning

-         Develop , maintain and deploy packages(for ETL dev)

-         Develop , maintain and deploy Cubes and dimension (for Analyst)

-         Develop , maintain and deploy Reports( for Reporter)

 

I would suggest you go for reporting. Its best way to start of SQL developer. And job market for Report developer is really good.

 

So all the best….

 

साथीहरु प्रती घण्टा कती पाउनु हुन्छ SQL सर्भरको जागिरमा? म आहेले प्रती घण्टा $३५.00  मा काम गर्दै छु। आहेले बजार तेती राम्रो छैन। तीन बर्ष अगाडि मैले $४५.00 पाएको थिए। 
.....................................................................................

सुमन अनवर सुमन

suman, are you sure are you working on $35? If you are a DBA(Production Support) this is way too less. I do not want to say how much i get but the billing rate for the DBAs (SQL or Oracle or Sybase), should be more than $70 ( at least 70). Market is good as far as i know.

Any way good luck and if you guys want to know each other, contact me in here in Sajha and we will do formal chinjan.

Life is SQLized dudes....

if you going thro dhoti consultancy ur range will be $30-40 without realtime experiance but if you find by urself ur range will be $60-80depending upon ur experiance...