Page 1 of 1

EASY SQL Problems I can't solve...!

PostPosted: Fri Mar 03, 2017 7:14 pm
by vignuol
Hi guys,

I've an important test today :crazy: about few basic SQL questions. I studied SQL in the past and now and I don't feel 100% confident :ucrazy: about these 3 questions:

1 Write a query showing for every author that sold any books how many titles (book_id) and how many units (quantity ordered) they sold
2 Write a query showing the authors that did not sell any books
2 Write a query that shows all pairs of customers who bought the same book and the book they bought

The tables are:
Author (autor_id, book_id)
Customer (customer_id, book_id, quantity_ordered)

Thanks in advance!! :super:

Re: EASY SQL Problems I can't solve...!

PostPosted: Fri Mar 03, 2017 7:43 pm
by Sliderule
vignuol wrote:I've an important test today :crazy: about few basic SQL questions.

Welcome to the OpenOffice / LibreOffice Base forum.

This forum is to help users that have issues is OpenOffice / LibreOffice Base.

It is NOT a forum to do your homework.

Sliderule

Re: EASY SQL Problems I can't solve...!

PostPosted: Sun Mar 05, 2017 9:41 pm
by eremmel
Sliderule is correct, but personal I think that we can honor some effort you put self into your assignment. So, the best advice I can give you is to define those two tables in OpenOffice / LibreOffice Base and add data such that you can validate your SQL, try to solve the questions with SQL. When you have still trouble finding the right answer, or you want to check them, feel free to post your SQL statements.
Then we can give hints to improve your SQL. In this way you will learn something more than copy/past only.
P.S.
Also simple questions should be good examples. Your two table examples is not that good.
A question to you is: The Customer table feels not right. What is a better name for this table, or what do you like to change to the data model to get it improved.

Re: EASY SQL Problems I can't solve...!

PostPosted: Mon Mar 06, 2017 10:04 am
by keme
Additional pointers, elaborating a bit on eremmel's thoughts.

Prerequisite:
Determine what types of entity, physical and abstract, you need to relate to. (Hint: make a list of the nouns present in your questions. Remember that there may be several nouns referring to the same type of entity...)

If you are supposed to define the data model (set up tables with relationships) yourself:
Make a table for each type of entity. With the input data you indicate, this should give 3NF/BCNF without further ado. (You have been taught basic normalizing, right?)

If you are locked to the data model as presented
:
Use subqueries to isolate content for each type of entity. You can use subqueries inline, just like table names, as source data for other queries.

Re: EASY SQL Problems I can't solve...!

PostPosted: Mon Mar 06, 2017 12:16 pm
by Nocton
Have you tried using the Query Wizard or the Query Design View? These should be sufficient for your purposes and you can then view the underlying SQL code. By the way, it is perhaps a good idea to update to the latest version of OO.