Last active
April 1, 2019 21:15
-
-
Save torch2424/fab39ed4e1cb20927edb to your computer and use it in GitHub Desktop.
SQL For creating the database for the JDBC project
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
---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