Solved

sybase sql questions...

Posted on 2006-06-14
6
475 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

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

Performance in games development is paramount: every microsecond counts to be able to do everything in less than 33ms (aiming at 16ms). C# foreach statement is one of the worst performance killers, and here I explain why.
We have come a long way with backup and data protection — from backing up to floppies, external drives, CDs, Blu-ray, flash drives, SSD drives, and now to the cloud.
This video discusses moving either the default database or any database to a new volume.
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …

759 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now