?
Solved

sybase sql questions...

Posted on 2006-06-14
6
Medium Priority
?
501 Views
Last Modified: 2008-03-06
Hi,

have some SQL questions please just to get more understanding...

1). I have a EMP table -- I want to get all the rows which are equal .. what is the query for this?

2). I want to get a employee which is 3rd in the salary range?

3). I want to insert a column into this table which is a primary key and has distinct values (many be just count from 1......)

so basically

emp has

empName   empSalary

I want to insert a column

empId empName empSalary

and update table by populating empId column with distinct numbers...

4). In sybase -- there is something like internal deleted and inserted tables -- what are those? and how to read them?

5). what are temp tables in sybase? where do we use them?

thanks,
0
Comment
Question by:hunky_sh
  • 2
  • 2
4 Comments
 
LVL 10

Expert Comment

by:bret
ID: 16903923
1). I have a EMP table -- I want to get all the rows which are equal .. what is the query for this?

select * from EMP group by col1, col2, col3, etc. having count(*) > 1

2). I want to get a employee which is 3rd in the salary range?

select name, salaryrank = identity(8) into #foo order by salary
select name from #foo where salaryrank = 3


3). I want to insert a column into this table which is a primary key and has distinct values (many be just count from 1......)

so basically

emp has

empName   empSalary

I want to insert a column

empId empName empSalary

and update table by populating empId column with distinct numbers...


ALTER TABLE emp add empId numeric(12,0) identity


4). In sybase -- there is something like internal deleted and inserted tables -- what are those? and how to read them?

These are internal views into the transaction log, they can only be accessed in triggers, but you
can select and join them as you would any other table.  inserted contains the new values for inserts and updates, deleted contains the old values for deletes and updates.

You normally wouldn't just select from them, but you can.  Usually you join them with the base table to identify the rows that were just affected by the command.

create trigger foo on emp for insert as select * from inserted
go


5). what are temp tables in sybase? where do we use them?

temp tables are used for scratch data.  They are specified by a "#" as the first character
of a table name.  Space for them is allocated in tempdb (or a user tempdb in recent versions).
They are automatically deallocated when the session (or procedure it was created in) exits.


0
 

Author Comment

by:hunky_sh
ID: 16905503
I could not get
select name, salaryrank = identity(8) into #foo order by salary
select name from #foo where salaryrank = 3

to work..

this is my test script

select "CREATING TABLE"
go

IF OBJECT_ID('dbo.EMP') IS NOT NULL
    DROP TABLE dbo.EMP
go

CREATE TABLE dbo.EMP
(
    EmpName        varchar(30)     NOT NULL,
    EmpSalary   INT    NOT NULL,
)
LOCK DATAROWS
go
IF OBJECT_ID('dbo.EMP') IS NOT NULL
    PRINT '<<< CREATED TABLE dbo.EMP >>>'
ELSE
    PRINT '<<< FAILED CREATING TABLE dbo.EMP >>>'
go

select "select * from EMP"
go

select * from EMP
go

select "INSERTING VALUES"
go

INSERT INTO EMP
VALUES
("ABC",10000)
GO

INSERT INTO EMP
VALUES
("DEF",430000)
GO

INSERT INTO EMP
VALUES
("ABC",15000)
GO

INSERT INTO EMP
VALUES
("HNK",130000)

GO

INSERT INTO EMP
VALUES
("ABC",10000)
GO

select "select * from EMP"
go

select * from EMP
go

select "select * from EMP group by EmpName, EmpSalary having count(*) > 1"
go


select * from EMP group by EmpName, EmpSalary having count(*) > 1
go

select "select * from EMP group by EmpName having count(*) > 1"
go


select * from EMP group by EmpName having count(*) > 1
go
0
 
LVL 10

Accepted Solution

by:
bret earned 300 total points
ID: 16905769
Your sample code doesn't seem to include use of "salaryrank = identity(8)" at all....

Do you get an error?

If so - what is your output for "select @@version"?

Also, I realized the code could possibly be better- though it depends on just what you want for output.  It is possible that more than one employee is earning the third highest salary amount.
If you want to see all the employees earning the third highest salary:

select salary, salaryrank = identity(8) into #foo from EMP order by salary

select * from EMP e, #foo f where e.salary = f.salary and f.salaryrank = 3
0
 

Author Comment

by:hunky_sh
ID: 17546959
thanks!

it seems to be working fine,

could you please let me know how is it working..

what does identity(8) means here??

and it doesn't cover a scenario if

we have say 6 rows

emp salary
a       1
b       1
c       3
d       4
e       5
f        6

in this, basically third largest salary is for (d --- 4).

but I guess this query doesn't cover this scenario.
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Tech giants such as Amazon and Google have sold Alexa and Echo to such an extent that they have become household names. And soon they are expected to be used by commoners in their homes, ordering takeout, picking out a song, answering trivia questio…
Social messanging services like WhatsApp and Facebook can help businesses in ways that many owners don't even imagine, giving new opportunities to connect with customers. Discover some of the most innovative things they can do for your company.
This Micro Tutorial will teach you how to add a cinematic look to any film or video out there. There are very few simple steps that you will follow to do so. This will be demonstrated using Adobe Premiere Pro CS6.
Screencast - Getting to Know the Pipeline
Suggested Courses

864 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question