Scribd
Upload
2 views3 pages

Advanced Database SQL Code

The document outlines the creation of a database named MembershipDB, which includes three tables: mtype for membership types, member for member details, and mcontribution for tracking contributions. It provides SQL commands to insert data into these tables, perform updates, and execute various select and delete queries. The database structure supports managing membership information and financial contributions effectively.

Uploaded by

bruikgermew
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
2 views3 pages

Advanced Database SQL Code

The document outlines the creation of a database named MembershipDB, which includes three tables: mtype for membership types, member for member details, and mcontribution for tracking contributions. It provides SQL commands to insert data into these tables, perform updates, and execute various select and delete queries. The database structure supports managing membership information and financial contributions effectively.

Uploaded by

bruikgermew
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd

CREATE DATABASE MembershipDB;

USE MembershipDB;

CREATE TABLE mtype (

mtypec VARCHAR(20) PRIMARY KEY,

mtype VARCHAR(20),

mfee DECIMAL(10, 2)

);

CREATE TABLE member (

mid VARCHAR(20) PRIMARY KEY,

fname VARCHAR(20),

mname VARCHAR(20),

lname VARCHAR(20),

mdate DATE,

mtypecode VARCHAR(20),

FOREIGN KEY (mtypecode) REFERENCES mtype(mtypec)

);

CREATE TABLE mcontribution (

mid VARCHAR(20),

month VARCHAR(20),

pyear INT,

pdate DATE,

amountp DECIMAL(18, 2),

remark VARCHAR(255),

PRIMARY KEY (mid, month, pyear),

FOREIGN KEY (mid) REFERENCES member(mid)

);
INSERT INTO mtype (mtypec, mtype, mfee) VALUES

('STND', 'Standard', 50.00),

('PREM', 'Premium', 120.00),

('STUD', 'Student', 25.00),

('CORP', 'Corporate', 500.00);

INSERT INTO member (mid, fname, mname, lname, mdate, mtypecode) VALUES

('M001', 'James', 'Arthur', 'Smith', '2024-01-15', 'STND'),

('M002', 'Elena', 'Marie', 'Rodriguez', '2024-03-22', 'PREM'),

('M003', 'Kwame', 'Osei', 'Mensah', '2025-06-10', 'STUD'),

('M004', 'Sarah', 'Lynn', 'Chen', '2025-11-05', 'CORP'),

('M005', 'David', 'John', 'Taylor', '2026-01-20', 'STND');

INSERT INTO mcontribution (mid, month, pyear, pdate, amountp, remark) VALUES

('M001', 'January', 2026, '2026-01-05', 50.00, 'Regular Monthly Fee'),

('M001', 'February', 2026, '2026-02-04', 50.00, 'Regular Monthly Fee'),

('M002', 'January', 2026, '2026-01-10', 120.00, 'Paid via Credit Card'),

('M002', 'February', 2026, '2026-02-12', 120.00, 'Paid via Credit Card'),

('M003', 'January', 2026, '2026-01-15', 25.00, 'Student Discount Applied'),

('M004', 'January', 2026, '2026-01-02', 500.00, 'Annual Corporate Allocation'),

('M005', 'February', 2026, '2026-02-28', 55.00, 'Includes $5 late fee');

SELECT fname, lname, mdate FROM member;

SELECT * FROM member WHERE mtypecode = 'PREM';

SELECT mid, amountp, remark FROM mcontribution WHERE amountp > 100.00;

UPDATE member SET lname = 'Smith-regis' WHERE mid = 'M001';

UPDATE mtype SET mfee = mfee + 10.00 WHERE mtypec = 'STND';

SELECT [Link], [Link], [Link], [Link]


FROM member m

JOIN mtype t ON [Link] = [Link];

SELECT [Link], [Link], [Link], [Link], [Link]

FROM member m

JOIN mcontribution c ON [Link] = [Link];

DELETE FROM mcontribution WHERE mid = 'M005' AND month = 'February' AND pyear = 2026;

DELETE FROM member WHERE mid = 'M003';

You might also like