0

SQL Intermediate Union module

You're working with the library books database and the Books table has columns id, name, year and author_id which is not mentioned and maybe null or empty. The library has new books whose information is stored in another table called New, however they don't have a year column but does have a name and id 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 book that are released after the year 1900. The result set should contain the name and year columns, only ordered by the name column alphabetically. The New book table will not permit me to alter or change each New file via manual entering each column, i.e., such as Alter keyword and it's already auto numbered. I'm speaking on how to enter data in what's an existing file. I would go into more detail since I left SQL Intermediate alone for months and this module bugged many. Why didn't anyone tell me about the author_id column?

6th Dec 2023, 11:38 PM
Michael
13 odpowiedzi
+ 1
There is no need to alter the table. You don't need the author_id and it is not mentioned in the task. Use the union to combine 2 SQL statements to produce the result.
7th Dec 2023, 3:00 AM
Wong Hei Ming
Wong Hei Ming - avatar
+ 1
It can be done with 5 lines of code. If you are good enough it can reduced to 3 lines. Here is the hint: Re-read slide 6 and mess around with the given code.
7th Dec 2023, 2:58 PM
Wong Hei Ming
Wong Hei Ming - avatar
+ 1
Michael Suppose you are at the beginning of the module, you are at slide 1. Click "continue" on pc 5 times, or swipe 5 times on mobile takes you to slide 6.
7th Dec 2023, 3:13 PM
Wong Hei Ming
Wong Hei Ming - avatar
+ 1
Try to mess around the code on slide 6 and replace null with something else.
7th Dec 2023, 3:25 PM
Wong Hei Ming
Wong Hei Ming - avatar
+ 1
Still at the same slide, look for the hint at bottom, it suggests to use a constant with union. I purposely reply answer in a "not so direct" fashion, sometimes like a riddle too.
7th Dec 2023, 4:36 PM
Wong Hei Ming
Wong Hei Ming - avatar
+ 1
Glad you made it. Don't forget to take a look at the comments. Sometimes there are valuable information in there.
8th Dec 2023, 4:53 AM
Wong Hei Ming
Wong Hei Ming - avatar
0
The problem is not only getting the value of 2022 in a year column which doesn't exist in the New books table but manually altering the New books table. That means I have to re-enter everything that is already in the New books table including making the non-existent year column have the value of 2022. The re-entering everything and making the year column in the New book table has been the biggest problem. I've had queries that were correct but the New book year column was non-existing. Try, try, again left me with getting to physically change with using SQL reserved to manually alter the existing New book table.
7th Dec 2023, 2:53 PM
Michael
0
I'm not good enough and I'm doing it the long way. Now, where is this so-called slide 6? I'm heading back to the Union module.
7th Dec 2023, 3:02 PM
Michael
0
I've written the code for the code coach. It's about getting past the not existing year column with the value of 2022 for the New books table. That value is missing in the result set
7th Dec 2023, 3:18 PM
Michael
0
Thank you. You sound worse than me when it comes to messing around with the code. I've tried but I'm getting column headings instead of adding row data.
7th Dec 2023, 4:26 PM
Michael
0
I understand the constant concept but I don't remember constants in any of the lessons. I'm taking for instance that the Book table year is greater than 1900. My question is the New book table, not existing year get the value of 2022. A crazy example I'll try is if New book table name then '2022'. I'm sure that the syntax is wrong but I don't know the syntax of CONST (constant).
7th Dec 2023, 5:17 PM
Michael
0
I had to use SELECT name, '2022' FROM New. It accepted the query. Thank you for your time, help, and patience.
7th Dec 2023, 5:34 PM
Michael
0
Thank you.
8th Dec 2023, 11:47 AM
Michael