June 22, 2010 at 11:13 pm
I am trying to CREATE TABLE for the following:
Members table,
groups table,
members_groups table,
I need help with my syntax, do I need to include foreign key constraints as well?
Also want to write an INSERT statements that add two rows to the Members table for member IDs 1
and 2, two rows to the Groups table for group IDs 1 and 2, and three rows to the members_groups table: one row for member 1 and group 2; one for member 2 and group 1; and one for member 2 and group 2. then write a SELECT statements that joins the three tables and retrieves the group name, member last name, and member first name.
CREATE TABLE members
(
member_id NUMBER NOT NULL,
first_name VARCHAR2(50) NOT NULL,
last_name VARCHAR2(50) NOT NULL,
address VARCHAR2(50) NOT NULL,
city VARCHAR2(50) NOT NULL,
state CHAR(2) NOT NULL,
phone VARCHAR2(50),
CONSTRAINT members_pk
PRIMARY KEY (member_id)
);
CREATE TABLE groups
(
group_id NUMBER NOT NULL,
group_name VARCHAR2(50) NOT NULL,
CONSTRAINT groups_pk
PRIMARY KEY (group_id)
);
CREATE TABLE members_groups
(
group_id NUMBER NOT NULL,
member_id NUMBER NOT NULL,
CONSTRAINT members_groups_pk
PRIMARY KEY (group_id, member_id)
);
June 23, 2010 at 3:06 am
HI there,
Is this what your looking for?
SELECTg.group_name
,m.last_name
,m.first_name
FROM groups g
INNER JOIN members_groups mg ON (mg.group_id=g.group_id)
INNER JOIN members m ON (mg.member_id=m.member_id)
_____________________________________________
[font="Comic Sans MS"]Quatrei Quorizawa[/font]
:):D:P;):w00t::cool::hehe:
MABUHAY PHILIPPINES!
June 23, 2010 at 12:10 pm
Thanks for reply.
The thing is I don't know how to write the INSERT statements that add two rows to the Members Table
for member IDs 1 and 2, then two rows to the GROUPS Table for group IDs 1 and 2, and three rows
to the Members_groups table:
one row for member 1 and group 2
one row for member 2 and group 1
one for member 2 and group 2
then I guess I can write the select statement to join the three tables together.
Thank you
April 12, 2022 at 6:53 am
CREATE TABLE members
(
member_id NUMBER PRIMARY KEY,
first_name VARCHAR2(50) NOT NULL UNIQUE,
last_name VARCHAR2(50) NOT NULL UNIQUE,
address VARCHAR2(50) NOT NULL UNIQUE,
state VARCHAR2(50) NOT NULL,
phone VARCHAR2(50) NOT NULL UNIQUE
);
CREATE TABLE groups
(
group_id NUMBER,
group_name VARCHAR2(50) NOT NULL CONSTRAINT group_name_uq UNIQUE,
CONSTRAINT groups_pk PRIMARY KEY (group_id)
);
CREATE TABLE members_groups
(
member_id NUMBER REFERENCES members (member_id),
group_id NUMBER,
CONSTRAINT groups_fk FOREIGN KEY (group_id) REFERENCES groups (group_id)
);
--a)
INSERT INTO members (member_id, first_name ,last_name, address, state, phone)
VALUES ( 1, 'Jose' , 'Valdivia', '1212 american pie, Olney', 'MD', '(400)600-1001');
INSERT INTO members (member_id, first_name ,last_name, address, state, phone)
VALUES (2, 'Martin' , 'Americo', '1122 Ohara cir, Olney', 'MD', '(100)220-4040');
--b)
INSERT INTO groups (group_id, group_name)
VALUES (1, 'Pycrastinators');
INSERT INTO groups (group_id, group_name)
VALUES (2, 'Belle Juice');
--c)
INSERT INTO members_groups (member_id , group_id)
VALUES (1, 2);
INSERT INTO members_groups (member_id , group_id)
VALUES (2, 1);
INSERT INTO members_groups (member_id , group_id)
VALUES (2, 2);
--d)
SELECT g.group_name,
m.last_name,
m.first_name
FROM groups g
INNER JOIN members_groups mg ON (mg.group_id=g.group_id)
INNER JOIN members m ON (mg.member_id=m.member_id)
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy