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

Discuss the database features

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

Postby vignuol » Fri Mar 03, 2017 7:14 pm

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
vignuol
 
Posts: 1
Joined: Fri Mar 03, 2017 7:06 pm

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

Postby Sliderule » Fri Mar 03, 2017 7:43 pm

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
User avatar
Sliderule
Volunteer
 
Posts: 1188
Joined: Thu Nov 29, 2007 9:46 am

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

Postby eremmel » Sun Mar 05, 2017 9:41 pm

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

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

Postby keme » Mon Mar 06, 2017 10:04 am

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 3.4.1/4.1.2/4.1.3, on Ms Windows 7/10 and Mac OS-X 10.8.5 and 10.11.
LibreOffice 4.3.3.2 on Mepis Antix MX 14
User avatar
keme
Volunteer
 
Posts: 3234
Joined: Wed Nov 28, 2007 10:27 am
Location: Egersund, Norway

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

Postby Nocton » Mon Mar 06, 2017 12:16 pm

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.2.0 on Windows 10
Nocton
Volunteer
 
Posts: 505
Joined: Fri Nov 05, 2010 10:27 am
Location: UK


Return to Base

Who is online

Users browsing this forum: No registered users and 7 guests