Database SQL Q&A 1

First COMP 353 database Q&A

Database SQL question:

The database schema for a community operated barter, announcement and giveaway system,
contains among others, the following relations:

• Member (ID, Name, Address, City, Prov, PCode, Country, PhoneNo, Email)
• CreditCard (MemberID, CardNo, CardID, ExpDate)
• CardHolder (CardNo, HolderName, Address, City, Prov, PCode, Country, PhoneNo, Email)
• Listing (ListingID, MemberID, ListingType, PostingDate, Duration)

Here the relation Member contains the details of a member of the system. Each member register information about a credit card (credit card number and the card identification number and the expiry date) for charging the annual membership fees and other charges. The credit card may belong to the member or some other person; details of the holder are given in the relation CardHolder.

A member may make one of more listing to offer a service or article for barter, sell or giveaway (ListingType). Each such Listing has a posting date and Duration(days being 7, 15, 30, or 60). (Note: Associated with each listing is description. These details should be stored in a separate tables which are not included here. )

Q: Creating tables corresponding to these relations with appropriate domains and populate them with representative data.
Use these tables to write SQL query to answer the following queries.

1. Create a database called barter, then use it:

--create a database barter
create database barter;

--use the database barter
use barter;

2. Create table Member

CREATE TABLE Member(
	 ID INT NOT NULL,
	 Name VARCHAR(30) NOT NULL,
	 Address VARCHAR(80) NOT NULL,
	 City VARCHAR(30) NOT NULL,
	 Prov VARCHAR(30) NOT NULL,
	 PCode VARCHAR(10) NOT NULL,
	 Country VARCHAR(30) NOT NULL,
	 PhoneNo VARCHAR(30) NOT NULL,
	 Email VARCHAR(40),
	 PRIMARY KEY (ID)
 );

  desc Member;

insert 5 to 10 tuples into table Member

insert into member values(11111, 'Jack', 'address 1', 'Montreal', 'Quebec', 'QC', 'Canada', '514-111-1111', '11111@gmail.com');
insert into member values(22222, 'Tom', 'address 2', 'Laval', 'Quebec', 'QC', 'Canada', '514-222-2222', '22222@gmail.com');
insert into member values(33333, 'Peter', 'address 3', 'Montreal', 'Quebec', 'QC', 'Canada', '514-333-3333', '33333@gmail.com');
insert into member values(44444, 'Frank', 'address 4', 'Brossard', 'Quebec', 'QC', 'Canada', '514-444-4444', '44444@gmail.com');
insert into member values(55555, 'John', 'address 5', 'Toronto', 'Ontario', 'ON', 'Canada', '555-555-5555', '55555@gmail.com');
insert into member values(66666, 'Mike', 'address 6', 'Laval', 'Quebec', 'QC', 'Canada', '514-666-6666', '66666@gmail.com');

--for test credit card's name is not the same as the member's name

insert into member values(77777, 'Tony', 'address 7', 'Laval', 'Quebec', 'QC', 'Canada', '514-777-7777', '77777@gmail.com');
insert into member values(88888, 'Trudeau', 'address 8', 'Ottawa', 'Ontario', 'ON', 'Canada', '555-888-8888', 'Trudeau@gmail.com');

--for test the member without listing

insert into member values(99999, 'nolisting', 'address 9', 'Sherbrook','Quebec', 'QC', 'Canada', '450-999-9999', '99999@gmail.com');

3. Create table CreditCard

CREATE TABLE CreditCard(
   MemberID INT NOT NULL,
   CardNo INT NOT NULL,
   CardID INT NOT NULL,
   ExpDate VARCHAR(10),
   PRIMARY KEY (CardNo),
   FOREIGN KEY (MemberID) REFERENCES Member(ID)
);

desc creditcard;

insert 5 to 10 tuples into table CreditCard

insert into creditcard values(11111, 112345, 1, '01/20');
insert into creditcard values(22222, 122222, 2, '02/20');
insert into creditcard values(33333, 133333, 3, '03/20');
insert into creditcard values(44444, 144444, 4, '04/20');
insert into creditcard values(55555, 155555, 5, '05/20');
insert into creditcard values(66666, 166666, 6, '06/20');
insert into creditcard values(77777, 177777, 7, '07/20');
insert into creditcard values(88888, 188888, 8, '08/20');

4. Create table CardHolder

CREATE TABLE CardHolder(
  CardNo INT NOT NULL,
  HolderName VARCHAR(30) NOT NULL,
  Address VARCHAR(80) NOT NULL,
  City VARCHAR(30) NOT NULL,
  Prov VARCHAR(30) NOT NULL,
  PCode VARCHAR(10) NOT NULL,
  Country VARCHAR(30) NOT NULL,
  PhoneNo VARCHAR(30) NOT NULL,
  PRIMARY KEY (CardNo)
);

desc CardHolder;

insert 5 to 10 tuples into table CardHolder

--insert 5 to 10 tuples per table

insert into CardHolder values(112345, 'Jack', 'address 1', 'Montreal', 'Quebec', 'QC', 'Canada', '514-111-1111');
insert into CardHolder values(122222, 'Tom', 'address 2', 'Laval', 'Quebec', 'QC', 'Canada', '514-222-2222');
insert into CardHolder values(133333, 'Peter', 'address 3', 'Montreal', 'Quebec', 'QC', 'Canada', '514-333-3333');
insert into CardHolder values(144444, 'Frank', 'address 4', 'Brossard', 'Quebec', 'QC', 'Canada', '514-444-4444');
insert into CardHolder values(155555, 'John', 'address 5', 'Toronto', 'Ontario', 'ON', 'Canada', '555-555-5555');
insert into CardHolder values(166666, 'Mike', 'address 6', 'Laval', 'Quebec', 'QC', 'Canada', '514-666-6666');

--for test credit card's name is not the same as the member's name

insert into CardHolder values(177777, 'not_Tony', 'address 7', 'Laval', 'Quebec', 'QC', 'Canada', '514-777-7777');
insert into CardHolder values(188888, 'not_Trudeau', 'address 8', 'Ottawa', 'Ontario', 'ON', 'Canada', '555-888-8888');

5. Create table Listing

CREATE TABLE Listing(
   ListingID INT NOT NULL,
   MemberID INT NOT NULL,
   ListingType VARCHAR(30) NOT NULL,
   PostingDate VARCHAR(30) NOT NULL,
   Duration INT NOT NULL,
   PRIMARY KEY (ListingID),
   FOREIGN KEY (MemberID) REFERENCES Member(ID)
);

desc Listing;

insert 5 to 10 tuples into table Listing

insert into Listing values(00001, 11111, 'sell', '2020/10/01', 7);
insert into Listing values(00002, 22222, 'sell', '2020/10/02', 15);
insert into Listing values(00003, 33333, 'giveaway', '2020/10/03', 15);
insert into Listing values(00033, 33333, 'giveaway', '2020/08/03', 30);
insert into Listing values(00004, 44444, 'giveaway', '2020/10/04', 7);

--for test if one person has more listings

insert into Listing values(00005, 55555, 'giveaway', '2020/10/05', 30);
insert into Listing values(000051, 55555, 'giveaway', '2020/09/05', 7);
insert into Listing values(00006, 66666, 'sell', '2020/10/06', 30);
insert into Listing values(00007, 77777, 'sell', '2020/10/071', 7);
insert into Listing values(00008, 88888, 'sell', '2020/10/08', 15);

---

SQL query:

1. Find the names of Members from Montreal.

   select name from member where city like ('Montreal');
   
   /* Another solution:
   select distinct(name) from member where city like ('Montreal');
   select name from member where city = 'Montreal';
   */

2. List the Names and Cities of members along with the listings they have posted.

   select name, city, listing.* from member, listing where memberid = id;

3. For all giveaway type listing from members in Montreal, list the members name and dates of the listings.

   select m.name, L.postingdate from member m, listing L where m.city='Montreal' and m.id=L.memberid and L.listingtype='giveaway';

4. List the pairs of names where the first name is the holder of the credit card and the second name is that of a member who uses this credit card and these names are not the same.

   select h.HolderName, m.name from CardHolder h, Member m, CreditCard c where m.id=c.memberid and c.cardno=h.cardno and h.HolderName != m.name;

5. Repeat above where the names are the same, limiting the list to the members who live in Laval.

   select h.HolderName, m.name from CardHolder h, Member m, CreditCard c where m.id=c.memberid and c.cardno=h.cardno and h.HolderName=m.name and m.city='Laval';

6. Find the members with the largest number of listings.

   select * from member where id in ( select MemberID from Listing group by MemberID having COUNT(*) >= all(select COUNT(*) as count from Listing group by MemberID));
   
   --we can test the other special case by inserting more tuples to listing table

7. Find the members with no listings.

   select * from member where ID NOT IN (select DISTINCT(MemberID) from Listing);

---

We can add more tuples into these tables to test them in MySQL.

---