hazman2

create table CUSTOMER
(
	CUST_ID int not null,
	CUST_FNAME varchar(50),
	CUST_LNAME varchar(50),
	CUST_BD date,
	primary key (CUST_ID)
)

select * from CUSTOMER

create table ORDER
(
	ORDER_ID varchar(50) not null,
	ORDER_ITEM int,
	ORDER_DATE date,
	ORDER_CUSTID int,
	primary key (ORDER_ID),
	foreign key (ORDER_CUSTID) references CUSTOMER (CUST_ID)
)

select * from ORDER

insert into CUSTOMER
(
	CUST_ID,
	CUST_FNAME,
	CUST_LNAME,
	CUST_BD
)
values
(
	101,
	'NURUL DIANA',
	'ROSLAN',
	'1991-03-18'
)

insert into CUSTOMER
(
	CUST_ID,
	CUST_FNAME,
	CUST_LNAME,
	CUST_BD
)
values (
	102,
	'NURUL QISTINA',
	'ROSLAN',
	'1995-09-18'
)

select * from CUSTOMER

alter table CUSTOMER
add CUST_AGE int

update CUSTOMER
SET CUST_FNAME = 'DIANA', CUST_AGE = 20
where CUST_ID = 101

delete from CUSTOMER
where CUST_ID = 101

alter table CUSTOMER
alter column CUST_AGE set data type varchar(10)

drop table ORDER

//////////

create table AUTHOR
(
	Au_Id int not null,
	Au_Name varchar ( 50 ),
	Au_Dob date,
	Au_Phone varchar ( 50 ),
	primary key ( Au_Id )
)

create table NOVEL
(
	N_Id int not null,
	Title varchar ( 50 ),
	Genre varchar ( 50 ),
	Isbn varchar ( 50 ),
	Nau_Id int,
	foreign key ( Nau_Id ) references AUTHOR (Au_Id)
)

insert into AUTHOR
(
	Au_Id,
	Au_Name,
	Au_Dob,
	Au_Phone
)
values
(
	1000,
	'Ahmad Salman',
	'1965-08-19',
	'0124565678'
)

insert into AUTHOR
(
	Au_Id,
	Au_Name,
	Au_Dob,
	Au_Phone
)
values
(
	1001,
	'Fatimah Rejab',
	'1969-02-28',
	'0102222644'
)

insert into AUTHOR
(
	Au_Id,
	Au_Name,
	Au_Dob,
	Au_Phone
)
values
(
	1002,
	'Hassan Basri',
	'1965-07-11',
	'0136357766'
)

insert into AUTHOR
(
	Au_Id,
	Au_Name,
	Au_Dob,
	Au_Phone
)
values
(
	1003,
	'Aminah Atan',
	'1970-01-31',
	'0122459900'
)

insert into AUTHOR
(
	Au_Id,
	Au_Name,
	Au_Dob,
	Au_Phone
)
values
(
	1004,
	'Zulnassri Isa',
	'1981-12-18',
	'0142432211'
)

insert into NOVEL
(
	N_Id,
	Title,
	Genre,
	Isbn,
	Nau_Id
)
values
(
	10,
	'One Hundred Years of Solitude',
	'Westerns',
	'ISBN 983-99557-1-3',
	1000
)

insert into NOVEL
(
	N_Id,
	Title,
	Genre,
	Isbn,
	Nau_Id
)
values
(
	11,
	'A Message to China',
	'Westerns',
	'ISBN 983-99558-1-4',
	1001
)

insert into NOVEL
(
	N_Id,
	Title,
	Genre,
	Isbn,
	Nau_Id
)
values
(
	12,
	'Invisible Man',
	'Westerns',
	'ISBN 983-995560-1-8',
	1002
)

insert into NOVEL
(
	N_Id,
	Title,
	Genre,
	Isbn,
	Nau_Id
)
values
(
	13,
	'Don Quixote',
	'History',
	'ISBN 983-995560-1-8',
	1003
)


insert into NOVEL
(
	N_Id,
	Title,
	Genre,
	Isbn,
	Nau_Id
)
values
(
	14,
	'Tumbangnya Kota A Famosa',
	'History',
	'ISBN 983-995561-2-9',
	1003
)

select *
from AUTHOR
where Au_Name like 'A%'

delete from AUTHOR
where Au_Id = '1004'

alter table AUTHOR
add Au_Email varchar ( 50 )

drop table NOVEL


select * from AUTHOR
select * from NOVEL

///////
//create database MPM

create table CUSTOMER
(
	CUSTOMER_IC varchar(20) not null,
	CUSTOMER_NAME varchar(50),
	CUSTOMER_PHONENO varchar(11),
	primary key(CUSTOMER_IC)
)

create table HALL
(
	HALL_ID int not null,
	HALL_NAME varchar(50),
	HALL_SIZE varchar(20),
	HALL_DESC varchar(50),
	primary key(HALL_ID)
)

create table BOOKING
(
	BOOK_ID int not null,
	CUSTOMER_IC varchar(20),
	HALL_ID int,
	EVENT_DESC varchar(100),
	START_BOOK date,
	END_BOOK date,
	PRICE decimal,
	primary key(BOOK_ID),
	foreign key(CUSTOMER_IC) references CUSTOMER(CUSTOMER_IC),
	foreign key(HALL_ID) references HALL(HALL_ID)
)

insert into CUSTOMER
(
	CUSTOMER_IC,
	CUSTOMER_NAME,
	CUSTOMER_PHONENO
)
values
(
	881107014534,
	'MAZLAN NORDIN',
	'010-2343888'
)

insert into CUSTOMER
(
	CUSTOMER_IC,
	CUSTOMER_NAME,
	CUSTOMER_PHONENO
)
values
(
	870110018113,
	'IBRAHIM',
	'010-2643211'
)

insert into CUSTOMER
(
	CUSTOMER_IC,
	CUSTOMER_NAME,
	CUSTOMER_PHONENO
)
values
(
	911218016543,
	'FAUZAN ALI',
	'014-2654678'
)

insert into HALL
(
	HALL_ID,
	HALL_NAME,
	HALL_SIZE,
	HALL_DESC
)
values
(
	1001,
	'Dewan Jubli Intan',
	'14700 kp',
	'Dewan (PERDANA)'
)

insert into HALL
(
	HALL_ID,
	HALL_NAME,
	HALL_SIZE,
	HALL_DESC
)
values
(
	1002,
	'DEWAN TUN SILA',
	'14700 kp',
	'DEWAN (PERDANA)'
)

select * from HALL;

SELECT E.LASTNAME, E.FIRSTNME, E.JOB, D.DEPTNAME
FROM EMPLOYEE E, DEPARTMENT D
WHERE E.WORKDEPT = D.DEPTNO
AND E.WORKDEPT BETWEEN 'A02' AND 'D22'
AND E.JOB <> 'MANAGER'
ORDER BY E.FIRSTNME, E.LASTNAME, E.JOB, D.DEPTNAME;

SELECT D.DEPTNAME, E.LASTNAME, E.FIRSTNME
FROM EMPLOYEE E, DEPARTMENT D
WHERE E.EMPNO = D.MGRNO
ORDER BY D.DEPTNAME;

SELECT D.DEPTNAME, E.LASTNAME, E.FIRSTNME
FROM EMPLOYEE E, DEPARTMENT D
WHERE E.WORKDEPT = D.DEPTNO
AND E.JOB = 'MANAGER'
ORDER BY D.DEPTNAME;

SELECT DISTINCT P.PROJNO, P.PROJNAME, A.ACTNO
FROM PROJECT P, EMP_ACT A
WHERE P.PROJNO = A.PROJNO
AND P.PROJNO LIKE 'AD%'
ORDER BY P.PROJNO, A.ACTNO;

SELECT DISTINCT E.EMPNO, E.LASTNAME, A.PROJNO
FROM EMPLOYEE E, EMP_ACT A
WHERE E.EMPNO = A.EMPNO
AND PROJNO = 'AD3113'
ORDER BY E.EMPNO, A.PROJNO;

SELECT A.EMPNO, P.PROJNO, P.PROJNAME, A.ACTNO, A.EMSTDATE
FROM PROJECT P, EMP_ACT A
WHERE P.PROJNO = A.PROJNO
AND A.EMSTDATE >= '01.08.1982'
ORDER BY P.PROJNO, A.EMPNO, A.ACTNO;

SELECT E.WORKDEPT, E.LASTNAME, P.PROJNAME, A.ACTNO
FROM EMPLOYEE E, EMP_ACT A, PROJECT P
WHERE E.EMPNO = A.EMPNO
ORDER BY P.PROJNO, A.ACTNO;

SELECT E.WORKDEPT, E.LASTNAME, P.PROJNAME, A.ACTNO
FROM EMPLOYEE E, EMP_ACT A, PROJECT P
WHERE E.EMPNO = A.EMPNO
AND E.WORKDEPT BETWEEN 'A00' AND 'C01'
ORDER BY E.WORKDEPT, E.LASTNAME, A.ACTNO;