New Arrivals task solution help request

Question

You are working with the library books database.

The Books table has the columns id, name, year.

The library has new books whose information is stored in another table called "New", however they do not have a year column.

Write a query to select the books from both tables, Books and New, combining their data. For the year column of the New books use the value 2022.

Also, select only the books that are released after the year 1900.

The result set should contain the name and year columns only, ordered by the name column alphabetically.

Answer 1

Dariusz Jenek ,

your post is not complete:

> please add the programming language to the tags.

> do a try by yourself and show your code here, so that people can see what your issue is.

> if your question refers to a sololearn tutorial, please mention the tutorial and modul name, also the lesson name.

Answer 2

SoloLearn has a SQL course. 😉

https://www.sololearn.com/learn/courses/sql-introduction

Answer 3

The task ask you to...
The result set should contain the name and year columns only, ordered by the name column alphabetically.
In other words, the result has 2 columns, but your solutions only return 1 column.

Answer 4

Please add "SQL" in the tag so we know which language we are talking.

I believe it is the intermediate course, under UNION section.

Please post your code so we can help you better.

Answer 5

SELECT name FROM Books UNION SELECT year FROM New WHERE year > 1900;

Answer 6

Maybe you should re-read page 6 and the provided code for inspiration.

Answer 7

Let's get to the basic.
The expected output contains TWO columns (you can see the final result since it is not locked).

Because UNION is use to combine two SQL statements, let's take a look on your first part of your SQL.
SELECT name FROM New
Only one field is selected...
Second part
SELECT CAST(year AS VARCHAR) FROM Books WHERE year > 1900 ORDER BY name ASC
It also select one field only.
The first hint: make your SQL select TWO fields

Another hint.
Writing SQL in one-liner is bad, difficult to read and debug.
I prefer this writing style when writing UNION.
SELECT something
FROM somewhere
UNION
SELECT something
FROM somewhere

It is find to write like this when the it is short, but more filed you selected (imagine there are 20 fields), it will get more difficult to debug.
SELECT something FROM somewhere
UNION
SELECT something FROM somewhere

Answer 8

Re-read page 6 AND change the code.
If you need more hint...

Change NULL to something else

Answer 9

You are getting close.
This time re-read page 6 again.
Not the code but the hint at the bottom.

Answer 10

As the hint says...
We can also use other 'constant' values for the extra columns.

Answer 11

SELECT name FROM Books UNION SELECT year FROM New WHERE year > 1900;

Answer 12

My wrong solution:

<sql>SELECT name FROM Books WHERE year > 1900 UNION SELECT CAST(year AS VARCHAR) AS VARCHAR FROM Books ORDER BY name ASC</sql>

Answer 13

Could You help me, please? I do really not know how to do it...

Answer 14

Any further hints and tips, please?

Answer 15

SELECT name FROM New UNION SELECT CAST(year AS VARCHAR) FROM Books WHERE year > 1900 ORDER BY name ASC? I do not know...

Answer 16

"For the year column of the New books use the value 2022." I do not know and understand this sentence. What does it mean? How to do it?

Answer 17

SELECT name, (2002 AS year)
FROM New
UNION
SELECT name, year
FROM Books
WHERE year > 1900
ORDER BY name ASC

For the year column of the New books use the value 2022. How to do it?

Answer 18

Should I change data type of '2022' in the SELECT clause? For wghat data type?

Answer 19

correct code is

SELECT name, year
FROM (
SELECT name, year
FROM Books
WHERE year > 1900
UNION ALL
SELECT name, 2022 AS year
FROM New
) AS CombinedBooks
ORDER BY name ASC;

Answer 20

Considering it is an SQL question, here is the possible solution:

SELECT name,
CASE WHEN year is not null THEN year ELSE 2022
END as year
from ( SELECT name, year from Books UNION SELECT name, null as year from New) AS new_table
WHERE COALESCE ( year, 2022) > 1900
ORDER BY name;