Entity Relationship Diagram:
SQL to Create Database and Populate Tables:
— creating database
–create database Test_D
use Test_D
— creating producer table
CREATE TABLE Producer (
producer_ID INT IDENTITY(101, 1) PRIMARY KEY,
Producer_NAME VARCHAR(50) NOT NULL,
tweet_message VARCHAR(280),
EMAIL VARCHAR(70)
)
GO
— creating Subscriber table
CREATE TABLE Subscriber (
Subscriber_ID INT IDENTITY(201, 1) PRIMARY KEY,
Subscriber_NAME VARCHAR(10) NOT NULL
)
GO
— Creating mapping table to join producer and subscriber table
CREATE TABLE TBL_MAPPING (
producer_ID INT REFERENCES Producer(producer_ID),
Subscriber_ID INT REFERENCES Subscriber(Subscriber_ID)
)
GO
— Inserting input into Producer table
INSERT INTO Producer VALUES
(‘Kallie Blackwood’, ‘Thank you for everything.’, ‘[email protected]’),
(‘Johnetta Abdallah’, ‘My last ask is the same as my first.’, ‘[email protected]’),
(‘Bobbye Rhym’, ‘Always in my heart’, ‘[email protected]’),
(‘Micaela Rhymes’, ‘the entire planet has gone batshit crazy.’, ‘[email protected]’),
(‘Tamar Hoogland’, ‘Twitter is the PvP of social media’, ‘[email protected]’)
GO
— all records from producer table
select * from Producer
— Inserting into subscriber values
INSERT INTO Subscriber VALUES
(‘Lee’),
(‘Zhang’),
(‘Wang’),
(‘nguyen’),
(‘Garcia’),
(‘tom’),
(‘ellen’),
(‘karan’),
(‘julia’),
(‘akram’)
GO
SQL Queries:
— All records from Subscriber Table
select * from Subscriber
— inserting values into mapping table
INSERT INTO TBL_MAPPING VALUES
(101, 201),
(101, 202),
(101, 203),
(102, 205),
(102, 208),
(105, 201),
(105, 202),
(105, 203),
(105, 205),
(105, 207),
(105, 208)
GO
— all records from mapping table
select * from TBL_MAPPING
–A minimum of the following queries:
— Get list of users;
Select * from Subscriber
— Get list of content producers;
Select * from Producer
— Get list of content subscribers (full);
SELECT
T1.Producer_NAME AS pro_NAME, T3.Subscriber_NAME AS subs_name
FROM
Producer T1
LEFT JOIN TBL_MAPPING T2 ON T1.producer_ID = T2.producer_ID
LEFT JOIN Subscriber T3 ON T2.Subscriber_ID = T3.Subscriber_ID
— Get list of content subscribers, subscribed on specific producer;
SELECT
T1.Producer_NAME AS pro_NAME, T3.Subscriber_NAME AS subs_name
FROM
Producer T1
LEFT JOIN TBL_MAPPING T2 ON T1.producer_ID = T2.producer_ID
LEFT JOIN Subscriber T3 ON T2.Subscriber_ID = T3.Subscriber_ID
where T1.Producer_NAME=‘Tamar Hoogland’
— Get all messages;
select tweet_message
from Producer
— Get all messages created by specific producer;
select tweet_message
from Producer
where producer_ID=101
— Get all messages for given subscriber (it may include messages from multiple producers).
SELECT
T1.Producer_NAME AS pro_NAME, T3.Subscriber_NAME AS subs_name
FROM
Producer T1
right JOIN TBL_MAPPING T2 ON T1.producer_ID = T2.producer_ID
right JOIN Subscriber T3 ON T2.Subscriber_ID = T3.Subscriber_ID
where T3.Subscriber_NAME=‘Garcia’
SQL Query Screen Shots with Results:
Get all messages for given subscriber ( it may include messages from multiple producers).