SQL QUERY

So, which tables are you joining first?

what I did
a. A. SELECT WRITNAME,WRITAGE,WRITSUPERVISOR
FROM WRITER
ORDER BY WRITNAME ASC;

b. SELECT WRITNAME,WRITSUPERVISOR,WRITAGE
FROM WRITER
WHERE NOT PUBLISH
( SELECT *
WHERE DATEPUBLISHED BETWEEN ‘1/1/2018 AND 31/12/2018 AND WRITER.WRITID=PUBLISH.WRITID

d. D. CREATE VIEW WRITER_1 AS
SELECT WRITNAME, WRITSUPERVISOR, WRITAGE, WRITID,NOVSV, DATEPUBLISHED,NOVTITLE, NOVPRICEUNIT, NOVQUANTITY
FROM WRITER, PUBLISH, NOVEL
WHERE WRITNAME=WRITER_1
AND WRITER.WRITID=PUBLISH.WRITID
AND NOVEL.NOVSV=PUBLISH.NOVSV

please correct me if I'm wrong. Thank you

Looks like it should be Nov.SN and not Nov.SV. I'm also seeing (it's been a while since I worked with SQL, so I could be wrong here) a simple join but the assignment is calling for an outer join.

Can you explain more? I don't understand. Well this is suppose to be my assignment for my online learning and I have to learn it by myself so I don't understand this topic very much and how am I suppose to answer the question correctly? I have dozen of questions that need to be answered but I'm totally stucked and I need help.

Here's the deal with outer joins (personally, I prefer to call them uneven joins).

Let's create 2 tables in a database for addresses. One table is Family. The other is called Friends.

Let's give it a primary key of ID_number.

How do you find people in both tables? You just do a regular old even join of Friends.ID_number = Family.ID_number.

But!

The tables are a lot bigger than that. If you want to show everyone, but with no duplicate rows, then you need an outer join. Let's look at 2 rows of data for each table to explain this.

Friends
ID_number 1 Name Amy
ID_number 2 Name Bob

Family
ID_number 1 Name Amy
ID_number 3 Name Chris

If you just have a totally even join, then you only get the one record which is in both tables -- Amy. But if you want to see all three records, you are essentially accounting for blanks. Because in an even join, the records for Bob and Chris only exist on one side, if you will.

Now - I honestly do not recall how to show proper notation, so please look it up, okay?

But you want your report to return:
ID_number 1 Name Amy
ID_number 2 Name Bob
ID_number 3 Name Chris

This means you need an outer join from the Friends to the Family table to account for ID_number 2 (Bob), because he's only on the Friends side. And you also need an outer join from the Family to the Friends table to pick up ID_number 3 (Chris).

I hope this makes sense. It's been years since I used SQL. But joins are really key. Once you understand joins, that accounts for a big chunk of what you need to understand.

Okay for now I understand that but how can I differentiate whether it is left, right or full outer join? and can you show me the answer to your example for my reference.

I really have no way of knowing that and, like I wrote above, I can't recall the notations for outer joins so as of now, the answer is all you.

An left outer join guarantees that there will be at least one row for each row in the first table. A right outer join is the same for the other row. If you need to use an outer join, I would assume that is a left outer join.

Edit: This explains it (since you asked for a link)
https://www.w3schools.com/sql/sql_join.asp

So in the example, you can do a (left) outer join on WRITER and PUBLISH on writid (since both tables have the 'writid' field).

if you write

SELECT * from WRITER
LEFT JOIN PUBLISH on WRITER.writid = PUBLISH.writid;

You will get a row for each WRITER whether or not that writer has published anything (i.e. whether there is a row in PUBLISH for that writid). Writers that haven't published anything will have a row with a bunch of "nulls" for the fields in PUBLISH (which don't exist).

if you change LEFT JOIN to INNER JOIN, you will only get rows that are both in WRITER and PUBLISH.

Hopefully you have a database on your local computer that you can play with. That is the best way to learn this stuff.

Open up a real database and play around.

Thank you for your explanation. Now I understand much better in this. Thank you for helping me