Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
SQL For creating the database for the JDBC 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;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment