Database midterm

create table user_profile(
			username varchar(45) not null primary key,
			pass varchar(70) not null,
			first_name varchar(50),
			last_name varchar(50),
			gender char(1),
			email varchar(30),
			hire_date date
			);

create table messages(
			messaged_id int not null,
			from_user varchar(45) references user_profile,
			to_user varchar(45) references user_profile,
			m_subject varchar(70),
			m_message varchar(500)
			);

alter table messages
	add msg_date date;

alter table messages
	alter column m_message varchar(650);

INSERT INTO user_profile values ('admin','admin','administrator','administrator','m','admin@mycomp.com','5-may-04');
INSERT INTO user_profile values ('adam3','ad_390','adam','sighfield','m','as@mycomp.com','23-jun-05');
INSERT INTO user_profile values ('alan','alan_pas','alan rey','smith','f','as@mycomp.com','9-jan-05');
INSERT INTO user_profile values ('leny','leny_23','leny','bright','f','lb@mycomp.com','23-jan-04');
INSERT INTO user_profile values ('rudy','me_rudy','rudy','cortez','m','rc@mycomp.com','2-mar-05');
INSERT INTO user_profile values ('melanie','melan','melanie','green','f','mg@mycomp.com','4-mar-05');

UPDATE user_profile
set username = 'adamson'
where pass = 'ad_390';

UPDATE user_profile
	set email = 'ars@mycomp.com' , gender ='m'
	where pass = 'alan_pas';

SELECT * from user_profile;

SELECT username
from user_profile
where gender ='m';

CREATE TABLE logdata(
				lognumber int not null primary key,
				logdate date,
				username varchar(45)not null references user_profile
				);

INSERT INTO logdata values ('1','3/15/2011','admin');
INSERT INTO logdata values ('2','4/21/2011','adamson');
INSERT INTO logdata values ('3','4/21/2011','admin');
INSERT INTO logdata values ('4','6/2/2011','leny');
INSERT INTO logdata values ('5','6/2/2011','alan');
INSERT INTO logdata values ('6','8/11/2011','rudy');
INSERT INTO logdata values ('7','9/11/2011','admin');
INSERT INTO logdata values ('8','9/11/2011','melanie');
INSERT INTO logdata values ('9','9/11/2011','leny');
INSERT INTO logdata values ('10','9/11/2011','adamson');


SELECT username
	from logdata
where logdate ='9/11/2011';


SELECT logdate
	from logdata
where username = 'admin';