Solved

sybase sql questions...

Posted on 2006-06-14
6
491 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

[Webinar] Code, Load, and Grow

Managing multiple websites, servers, applications, and security on a daily basis? Join us for a webinar on May 25th to learn how to simplify administration and management of virtual hosts for IT admins, create a secure environment, and deploy code more effectively and frequently.

Question has a verified solution.

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

Not everyone has adapted to a rapid advancement in technology; there are people who are reluctant or afraid to delve into this brave new world of IT. If you have a friend or a family member who suffers from the so-called technophobia, here is how yo…
Had a business requirement to store the mobile number in an environmental variable. This is just a quick article on how this was done.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

739 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