Tag Archives: emp

scott.emp in Sybase

As part of my dba job, I have a few Sybase DBs around. I created the well known Oracle Scott tables to play with

$ isql -U sa -P ""
1> sp_addlogin "scott","tiger7"
2> go
Password correctly set.
Account unlocked.
New login created.
(return status = 0)
1> create database lsc01
2> go
CREATE DATABASE: allocating 1536 logical pages (6.0 megabytes) on disk 
  'data_dev' (1536 logical pages requested).
Database 'lsc01' is now online.
1> use lsc01
2> go
1> sp_adduser scott
2> go
New user added.
(return status = 0)
1> grant create table to scott
2> go
1> exit
$ isql -U scott -P tiger7 -D lsc01
1> create table dept(deptno int constraint pk_dept primary key, dname varchar(14), loc varchar(13))
2> go
1> insert into dept values(10,'ACCOUNTING','NEW YORK')
2> go
(1 row affected)
1> insert into dept values(20,'RESEARCH','DALLAS')
2> go
(1 row affected)
1> insert into dept values(30,'SALES','CHICAGO')
2> go
(1 row affected)
1> insert into dept values(40,'OPERATIONS','BOSTON')
2> go
(1 row affected)
1> create table emp(empno int constraint pk_emp primary key, ename varchar(10), job varchar(9), mgr int null, hiredate date, sal float, comm float null, deptno int constraint fk_dept references dept)
2> go
1> insert into emp values(7369,'SMITH','CLERK',7902,'1980-12-17',800,null,20)
2> go
(1 row affected)
1> insert into emp values(7499,'ALLEN','SALESMAN',7698,'1981-02-20',1600,300,30)
2> go
(1 row affected)
1> insert into emp values (7521,'WARD','SALESMAN',7698,'1981-02-22',1250,500,30)
2> go
(1 row affected)
1> insert into emp values (7566,'JONES','MANAGER',7839,'1981-04-02',2975,NULL,20)
2> go
(1 row affected)
1> insert into emp values (7654,'MARTIN','SALESMAN',7698,'1981-09-28',1250,1400,30)
2> go
(1 row affected)
1> insert into emp values (7698,'BLAKE','MANAGER',7839,'1981-05-01',2850,NULL,30)
2> go
(1 row affected)
1> insert into emp values (7782,'CLARK','MANAGER',7839,'1981-06-09',2450,NULL,10)
2> go
(1 row affected)
1> insert into emp values (7788,'SCOTT','ANALYST',7566,'1987-07-13',3000,NULL,20)
2> go
(1 row affected)
1> insert into emp values (7839,'KING','PRESIDENT',NULL,'1981-11-17',5000,NULL,10)
2> go
(1 row affected)
1> insert into emp values (7844,'TURNER','SALESMAN',7698,'1981-09-08',1500,0,30)
2> go
(1 row affected)
1> insert into emp values (7876,'ADAMS','CLERK',7788,'1987-07-13',1100,NULL,20)
2> go
(1 row affected)
1> insert into emp values (7900,'JAMES','CLERK',7698,'1981-12-03',950,NULL,30)
2> go
(1 row affected)
1> insert into emp values (7902,'FORD','ANALYST',7566,'1981-12-03',3000,NULL,20)
2> go
(1 row affected)
1> insert into emp values (7934,'MILLER','CLERK',7782,'1982-01-23',1300,NULL,10)
2> go
(1 row affected)
1> select ename,dname,d.deptno,empno from dept d left join emp e on (d.deptno=e.deptno)
2> go
 ename                dname                        deptno      empno       
 -------------------- ---------------------------- ----------- ----------- 
 KING                 ACCOUNTING                            10        7839 
 CLARK                ACCOUNTING                            10        7782 
 MILLER               ACCOUNTING                            10        7934 
 FORD                 RESEARCH                              20        7902 
 SCOTT                RESEARCH                              20        7788 
 JONES                RESEARCH                              20        7566 
 ADAMS                RESEARCH                              20        7876 
 SMITH                RESEARCH                              20        7369 
 WARD                 SALES                                 30        7521 
 BLAKE                SALES                                 30        7698 
 ALLEN                SALES                                 30        7499 
 JAMES                SALES                                 30        7900 
 TURNER               SALES                                 30        7844 
 MARTIN               SALES                                 30        7654 
 NULL                 OPERATIONS                            40        NULL 

(15 rows affected)
1> commit
2> go

Sql file to download : emp-dept.sql

There are obviously a few differences, the password is associated with a login that belongs to the “db server”, and the user is created in the database “lsc01″ and associated to the login. In Sybase, one database server (typically one Unix Process), contains more than one database. There is also a master database, which is as sensitive as your system tablespace. Still in the official Sybase administrator course, they already teach you how to manually update the dictionary…

There is no varchar2 or number, and the default for a column is not null. The equivalent of DESC EMP is sp_help emp, but it is more verbose. The columns are case-sensitive, create table EMP is not the same as create table emp.

Not all “ANSI” code will work, like DATE ’2000-01-01′, FULL OUTER JOIN, USING and all those Oracle “ANSI” things are not meant “COMPATIBLE WITH SYBASE” but more “CONFORM TO ANSI”

ok, that said, it was pretty straightforward