Marketing

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 (Fname VARCHAR(15) NOT NULL, Minit CHAR, Lname VARCHAR(15) NOT NULL, Ssn CHAR(9) NOT NULL, Bdate DATE, Address VARCHAR(30), Sex CHAR, Salary DECIMAL(10,2), Super_ssn CHAR(9), Dno INT NOT NULL,
PRIMARY KEY(Ssn),
FOREIGN KEY(Super_ssn) REFERENCES EMPLOYEE(Ssn));

CREATE TABLE DEPARTEMENT (Dname VARCHAR(15) NOT NULL, Dnumber INT NOT NULL, Mgr_ssn CHAR(9) NOT NULL, Mgr_start_date DATE,
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 (Pname VARCHAR(15) NOT NULL, Pnumber INT NOT NULL, Plocation VARCHAR(15), Dnum INT NOT NULL,
PRIMARY KEY(Pnumber),
FOREIGN