Solved

sybase sql questions...

Posted on 2006-06-14
6
489 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
6 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 75 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: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

One of a set of tools we are providing to everyone as a way of saying 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

Suggested Solutions

Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
Although a lot of people devote their energy toward marketing for specific industries, there are some basic principles that can be applied to any sector imaginable. We’ll look at four steps to take and examine how those steps were put into action fo…
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an antispam), the admini…

713 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