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