SQL For creating the database for the JDBC project

JDBC SQL Database Project

---SQL CODE---


/* Create Our Tables */
CREATE TABLE recgroup
  (
  groupname VARCHAR(15) NOT NULL,
  leadsinger VARCHAR(15),
  yearformed  VARCHAR(20) NOT NULL,
  genre     VARCHAR(15),
  CONSTRAINT pk_recgroup PRIMARY KEY (groupname)
  );

CREATE TABLE albulms
  (
  atitle VARCHAR(50) NOT NULL,
  groupname VARCHAR(15) NOT NULL,
  sname VARCHAR(15) NOT NULL,
  daterec VARCHAR(15) NOT NULL,
  length  VARCHAR(20),
  numsongs    VARCHAR(15) NOT NULL,
  CONSTRAINT pk_albulm PRIMARY KEY (atitle)
  );

CREATE TABLE recstudio
  (
  sname VARCHAR(15) NOT NULL,
  saddress VARCHAR(50),
  sowner  VARCHAR(20),
  sphone    VARCHAR(12),
  CONSTRAINT pk_recstudio PRIMARY KEY (sname)
  );

/* Add Our Foreign Keys */
ALTER TABLE albulms
          ADD CONSTRAINT recgroup_fk
          FOREIGN KEY (groupname)
          REFERENCES recgroup (groupname);

ALTER TABLE albulms
          ADD CONSTRAINT recstudio_fk
          FOREIGN KEY (sname)
          REFERENCES recstudio (sname);

/* Insert values into them */
INSERT INTO recgroup VALUES
('Groovy Godzilla', 'Aaron Turner', '2014', 'FutureFunk');

INSERT INTO recgroup VALUES
('SOAD', 'Serj', '1999', 'Metal');

INSERT INTO albulms VALUES
('Godzillas Summer Vacation', 'Groovy Godzilla', 'Home', '2014-07-18', '17', '8');

INSERT INTO albulms VALUES
('Mesmerize', 'SOAD', 'Universal', '2000-05-10', '25', '11');

INSERT INTO recstudio VALUES
('Home', '533 Newport Ave.', 'Aaron Turner', '5625555555');

INSERT INTO recstudio VALUES
('Universal', '555 Sunset Blvd.', 'Some Rich Dude', '5555555555');


/* Display to test */
select * from recgroup;
select * from albulms;
select * from recstudio;


/* DRop the table for testing */

ALTER TABLE albulms
    DROP CONSTRAINT recstudio_fk;

ALTER TABLE albulms
    DROP CONSTRAINT recgroup_fk;


drop table recgroup;
drop table albulms;
drop table recstudio;