Answered step by step
Verified Expert Solution
Link Copied!

Question

1 Approved Answer

Express each of the following queries in SQL against the relational schema defined above. Each query should be expressed using a single SQL statement. 1.

image text in transcribed
image text in transcribed
Express each of the following queries in SQL against the relational schema defined above. Each query should be expressed using a single SQL statement.
1. For each customer who has done at least one purchase transaction, list his/her member id, name and contact information. There should not be duplicates in the result.
2. For each purchase transaction done in January 1st, 2020 or later, list its transaction time, name of the member who made the purchase and the name of each product purchased in the transaction.
3. List the barcode, name and price of each product that has never being returned.
4. List the name and contact information of each member who has purchased at least one product but has never returned any purchased product.
5. For each (purchase or return) transaction assisted by the employee with the employee id 'K00236', list the transaction's (purchase or return respectively) store name and the (purchase or return) time.
6. List the name and price of each product that has NEVER purchased by the customer with the member id 300790013328.
7. List the barcode, the name and the price of each product that has been purchased by EVERY customer at least once.
The Assignment Information The following relational schema (primary keys are underlined) are used by the security company, West Park, described in Assignment 1, with some minor modifications. Stores(sid, name, address, phone) Products barcode, name, description, price) Members(mid, name, contact Info, openAtStore, openTime, lastRenew Time) Transactions(sid, pid, transaction Time, customer, employee) TransactionContent(sid, pid, barcode) Return Transactions(returnStore, returnPid, sid, pid, barcode, return Time, returnComment, employee) This assignment is based on the above relational schema. Many details, including foreign keys, data types, etc, are captured in the SQL definition of these tables. You may use the supplied table definitions to create empty tables. You will need to populate the tables yourself in order to test your queries. CREATE TABLE Stores ( sid NUMBER(5) PRIMARY KEY, name VARCHAR2(100) NOT NULL, address VARCHAR2 (200), phone CHAR(10) ); CREATE TABLE Products barcode CHAR(10) PRIMARY KEY, name VARCHAR2 (30) NOT NULL, description VARCHAR2 (200), price NUMBER( 8, 2) NOT NULL CREATE TABLE Members mid NUMBER (12) PRIMARY KEY, name VARCHAR2 (30) NOT NULL, contact Info VARCHAR2 (100), openAt Store NUMBER(5) REFERENCES Stores, openTime DATE, lastRenewTime DATE CREATE TABLE Transactions sid NUMBER (5) REFERENCES Stores, pid NUMBER (10), transaction Time DATE, customer NUMBER (12) REFERENCES Members, employee CHAR(6), PRIMARY KEY (sid, pid) CREATE TABLE TransactionContent ( sid NUMBER(5), pid NUMBER(10), barcode CHAR(10) REFERENCES Products, PRIMARY KEY (sid, pid, barcode), FOREIGN KEY (sid, pid) REFERENCES Transactions CREATE TABLE ReturnTransactions returnStore NUMBER(5) REFERENCES Stores, returnPid NUMBER (10), sid NUMBER (5), pid NUMBER(10), barcode CHAR(10), return Time DATE, returnComment VARCHAR2 (200), employee CHAR(6), PRIMARY KEY (returnStore, returnPid), FOREIGN KEY (sid, pid, barcode) REFERENCES TransactionContent

Step by Step Solution

There are 3 Steps involved in it

Step: 1

blur-text-image

Get Instant Access to Expert-Tailored Solutions

See step-by-step solutions with expert insights and AI powered tools for academic success

Step: 2

blur-text-image

Step: 3

blur-text-image

Ace Your Homework with AI

Get the answers you need in no time with our AI-driven, step-by-step assistance

Get Started

Recommended Textbook for

Learning MySQL Get A Handle On Your Data

Authors: Seyed M M Tahaghoghi

1st Edition

0596529465, 9780596529468

More Books

Students also viewed these Databases questions

Question

5. Discuss the key components of behavior modeling training.

Answered: 1 week ago

Question

4. Develop a self-directed learning module.

Answered: 1 week ago