SCHEMAS: 1) Member (member ID, name, email, username, password, phone, Favorite,

Question

SCHEMAS:

1) Member(member ID, name, email, username, password, phone, Favorite, Address_ID )

2)Address( address ID, city, zip code, district, street, neighborhood, apartment_ number, country, Member_ID)

3)Book( Ispn, Title, Author, Publisher, Price, number_of_ page, Language, Category, type_ID )

4)Book Types(type_ID, type_name)

5)Payment Method(Pm_ID, CH_name, CH_sn, C_number, CH_type, C_expiryDate, C_securityCode, total_price)

6)Gift cheque ( G_number,expiryDate, Amount)

7)order(order_ID, Pm_ID, G_number, date_of_order, member_ID,address_ID, Paid_Amount) 8)Basket(Basket_ID, order_ID , Ispn , Cost, Quantity, Total)

8)Basket(Basket_ID, order_ID , Ispn , Cost, Quantity, Total)

Give the SQL queries :

1. Find the name of all members who have placed an order with a total above 500$
2. Find the zip code of all member who used the gift cheque to pay for their orders.
3. Find the categories of all the books that listed under john’s favorite books
4. Find the title, author, and publisher of all books that cost more than 200$ with page# less than 300 pages.

Explanation / Answer

1. SELECT Member.name FROM Member, order WHERE Orders.member_ID = Member.member_ID GROUP BY Member.name, Orders.member_ID HAVING SUM( Orders.Paid_Amount ) >500

2.Select Address.zip_code from order,Address where Address.address_ID=order.address_ID and order.G_number!=NULL

3. select Book.Category from Boook,Member where Member.Favorite=Book.Title and Member.name='john'

4. select Title, Author, Publisher from Book where Price>200 and number_of_ page<300

Related Questions

Navigate

• Browse (All)
• Browse S
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.