-- A script for creating two tables for itec325 hw05
--   https://php.radford.edu/~itec325/2012fall-ibarland/Homeworks/hw05/hw05.html
-- You may need to tweak this file, if you want different column widths or names.
-- Works in both Oracle SQL and MySQL (php.radford.edu >... phpMyAdmin >... 'SQL' tab).

drop   table Questions;
create table Questions(
   qid      int
  ,author   varchar(50)
  ,question varchar(200)
  ,answer   varchar(200)
  ,hint     varchar(200)
  ,weight   int  -- should really have a check-constratint
  ,difficulty  varchar(10)  -- should really be a FK into a known-abilities table.
  -- category: See the Categories table, to associate multiple categories with this question.
  ,subcategory varchar(20)  -- should really be a FK into a known-abilities table.  Might be null, for non-science.
  );

create table Categories(
   qid int
  ,category varchar(20)  -- should really be a FK into a known-abilities table.
  )


insert into Questions(qid,question,answer,hint,weight,difficulty)
values(42,'ibarley','Istanbul was _______ (which, in turn, was Byzantium).','Constantinople','http://youtube.com/watch?v=x0X77OBJUg',100,'medium');

insert into Categories(qid,category)
values(42,'history');
insert into Categories(qid,category)
values(42,'geography');
insert into Categories(qid,category)
values(42,'entertainment');