contoh program sql server

create database Figure

use Figure

create table EMPLOYEE(

      Ssn int primary key,

      Nama char(50),

      Sex char(1),

      Alamat char(50),

      Salary decimal,

      Bdate date)

     

      DROP TABLE EMPLOYEE

     

insert into EMPLOYEE values(01001,'Putra','L','Jl.Cempaka No.5 Medan',1500000,'09/12/1988')

insert into EMPLOYEE values(01002,'Messi','L','Jl.Setia Budi No.99 Barcelona',3500000,'08/08/1988')

insert into EMPLOYEE values(01003,'Xavi','L','Jl.Supomo No.109 Madrid' ,2570000,'02/02/1985')

insert into EMPLOYEE values(01004,'Carolin','P','Jl.Bromo No.33',2500000,'12/02/1986')

insert into EMPLOYEE values(01005,'Fabregas','L','Jl.Gajah Mada No.90 London',3000000,'09/05/1983')

insert into EMPLOYEE values(01006,'Shakira','P','Jl.Jamin Ginting No.88 Barcelona',3700000,'09/09/1985')

select*from EMPLOYEE

create table DEPARTMENT(

      Number int primary key,

      NumberOfEmployees int foreign key references EMPLOYEE(Ssn),

      Nama char(50),

      Locations char(50))

drop table DEPARTMENT

insert into DEPARTMENT values(01,01003,'Guardiola','Medan')

insert into DEPARTMENT values(02,01005,'Mourinho','Papua')

insert into DEPARTMENT values(03,01006,'Ferguson','Nias')

insert into DEPARTMENT values(04,01002,'Vilanova','Jakarta')

insert into DEPARTMENT values(05,01001,'Benitez','Padang')

insert into DEPARTMENT values(06,01004,'Van Basten','Aceh')

select*from DEPARTMENT

create table PROJECT(

      Number int foreign key references DEPARTMENT(Number),

      Ssn int foreign key references EMPLOYEE(Ssn),

      Nama char(50),

      Location char(50))

insert into PROJECT values(01,01004,'Bangunan','Jakarta')

insert into PROJECT values(04,01006,'Jembatan','Jambi')

insert into PROJECT values(02,01003,'Jalan Raya','Semarang')

insert into PROJECT values(06,01001,'Gedung sekolah','Palembang')

insert into PROJECT values(05,01002,'Perumahan','Makasar')

insert into PROJECT values(03,01005,'Pasar','Bali')

select*from PROJECT 

create table DEPENDENTS(

      Nama char(50),

      Sex char(1),

      BirthDate date,

      Relationship char(50))

insert into DEPENDENTS values('Putra','L','09/12/1988','Mandor')

insert into DEPENDENTS values('Xavi','L','02/12/1985','Ketua')

insert into DEPENDENTS values('Carolin','P','09/12/1986','Aggota')

insert into DEPENDENTS values('Shakira','P','09/09/1985','Aggota')