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

Discuss the database features
Post Reply
vignuol
Posts: 1
Joined: Fri Mar 03, 2017 7:06 pm

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

Post 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:
OpenOffice 3.1 on Windows Vista
User avatar
Sliderule
Volunteer
Posts: 1278
Joined: Thu Nov 29, 2007 9:46 am

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

Post 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
eremmel
Posts: 1080
Joined: Tue Dec 30, 2008 1:15 am

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

Post 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.
It's Microsoft marketing that tells you computers are qualified for non-technicians
W11 22H2 (build 22621), LO 7.4.2.3(x64)
User avatar
keme
Volunteer
Posts: 3699
Joined: Wed Nov 28, 2007 10:27 am
Location: Egersund, Norway

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

Post 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.
Apache OO 4.1.12 and LibreOffice 7.5, mostly on Ms Windows 10
Nocton
Volunteer
Posts: 533
Joined: Fri Nov 05, 2010 10:27 am
Location: UK

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

Post 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.
OpenOffice 4.1.12 on Windows 10
Post Reply