Marketing

Topics: SQL, Articles with example SQL code, Foreign key Pages: 7 (856 words) Published: June 18, 2013

Tugas mandiri
Andre nanda pratama jaelani, 112400161, MBTIe

CREATE TABLE DEPARTMENT
(dept_id VARCHAR (5) NOT NULL,
Nama_Dept VARCHAR (20) NOT NULL,
PRIMARY KEY (DEPT_id));

CREATE TABLE PEGAWAI
(ID_peg INT (5) NOT NULL,
Nama_peg VARCHAR (20) NOT NULL,
Alamat VARCHAR (20) NOT NULL,
ID_Dept VARCHAR (5) NOT NULL,
PRIMARY KEY (ID_peg),
FOREIGN KEY (ID_Dept) references pegawai (ID_peg));

INSERT INTO Department VALUES
('B001','HRC'),
('B002','IT'),
('B003','FINANCE'),
('B004','LOGISTIC');

INSERT INTO Pegawai VALUES
(100,'abas','madura','B001'),
(101,'bejo','magelang','B002'),
(102,'cecev','cicaheum','B001'),
(103,'ratu','ciawi_tali','B003'),
(104,'monalisa','cigiringsing','B002'),
(105,'sari','antapani','B004'),
(106,'rian','cicaheum','B003'),
(107,'bruno','magelang','B001'),
(108,'asep','cibiru','B002'),
(109,'dadang','cicaheum','B004'),
(110,'iis','magelang','B003');

select ID_peg,nama_peg,alamat,ID_dept
from pegawai
where (alamat='cicaheum');

select Nama_peg,nama_Dept
from pegawai,Department
where (alamat='magelang')
and (ID_dept=dept_id);

Select ID_peg, Nama_Peg
from Pegawai, department
where (id_dept=dept_id) and (dept_id='B001');

Tugas individu.

CREATE TABLE EMPLOYEE
(FnameVARCHAR(15) NOT NULL,
MinitCHAR,
LnameVARCHAR(15) NOT NULL,
SsnCHAR(9)NOT NULL,
BdateDATE,
AddressVARCHAR(30),
SexCHAR,
SalaryDECIMAL(10,2),
Super_ssn CHAR(9),
DnoINT NOT NULL,
PRIMARY KEY(Ssn),
FOREIGN KEY(Super_ssn) REFERENCES EMPLOYEE(Ssn));

CREATE TABLE DEPARTEMENT
(DnameVARCHAR(15)NOT NULL,
DnumberINTNOT NULL,
Mgr_ssnCHAR(9)NOT NULL,
Mgr_start_dateDATE,
PRIMARY KEY(Dnumber),
FOREIGN KEY(Mgr_ssn) REFERENCES EMPLOYEE(Ssn));

CREATE TABLE DEPT_LOCATIONS
(Dnumber INT NOT NULL,
Dlocation VARCHAR(15) NOT NULL,
PRIMARY KEY(Dnumber, Dlocation),
FOREIGN KEY(Dnumber) REFERENCES DEPARTMENT(Dnumber));

CREATE TABLE PROJECT
(PnameVARCHAR(15)NOT NULL,
PnumberINTNOT NULL,
PlocationVARCHAR(15),
DnumINTNOT NULL,
PRIMARY KEY(Pnumber),
FOREIGN KEY(Dnum) REFERENCES DEPARTMENT(Dnumber));

CREATE TABLE WORKS_ON
(EssnCHAR(9)NOT NULL,
PnoINTNOT NULL,
HoursDECIMAL(3,1)NOT NULL,
PRIMARY KEY(Essn,Pno),
FOREIGN KEY(Essn) REFERENCES EMPLOYEE(Ssn),
FOREIGN KEY(Pno) REFERENCES PROJECT(Pnumber));

CREATE TABLE DEPENDENT
(EssnCHAR(9)NOT NULL,
Dependent_nameVARCHAR(15)NOT NULL,
SexCHAR,
BdateDATE,
RelationshipVARCHAR(8),
PRIMARY KEY(Essn,Dependent_name),
FOREIGN KEY(Essn) REFERENCES EMPLOYEE(Ssn));

INSERT INTO employee
(Fname,Minit,Lname,Ssn,Bdate,Address,Sex,Salary,Dno)
VALUES ('John','B', 'Smith','123456789','1965-01-09','731 Fondren, Houston, TX','M','30000','5'), ('Franklin', 'T','Wong','333445555','1955-12-08','638 Voss, Houston, TX','M','40000','5'), ('Alicia','J','Zelaya','999887777','1968-07-19','3321 Castle, Spring, TX','F','25000','4'), ('Jennifer','S','Wallace','987654321','1941-06-20','291 Berry, Bellaire, TX','F','43000','4'), ('Ramesh','K','Narayan','666884444','1962-09-15','975 Fire Oak, Humble, TX','M','38000','5'), ('Joyce','A','English','453453453','1972-07-31','5631 Rice, Houston, TX','F','25000','5'), ('Ahmad','V','Jabbar','967967967','1969-03-29','980 Dallas, Houston, TX','M','25000','4'), ('James','A','Borg','888665555','1937-11-10','450 Stone, Houston, TX','M','55000','1');

INSERT INTO departement
(Dname, Dnumber, Mgr_ssn, Mgr_start_date)
VALUES ('Research', '5', '333445555', '1988-05-22'),
('Administration','4','987654321','1995-01-01'),
('Headquarters','1','888665555','1981-06-19');

INSERT INTO dept_locations
(Dnumber, Dlocation)
VALUES ('1', 'Houston'),
('4','Stafford'),
('5','Bellaire'),
('5','Sugarland'),
('5','Houston');

INSERT INTO project
(Pname, Pnumber, Plocation, Dnum)
VALUES ('ProductX','1','Bellaire','5'),...
Continue Reading

Please join StudyMode to read the full document

You May Also Find These Documents Helpful

  • Essay on marketing
  • Marketing Essay
  • Green Marketing Essay
  • Essay on marketing research
  • Principles of Marketing Research Paper
  • mobile marketing Essay
  • Essay about Marketing Article Review Branding in Marketing
  • Pampers Marketing Essay

Become a StudyMode Member

Sign Up - It's Free