Can someone please help I have been struggling figuring the SLQ query on this figure can someone also explain how this works please I Dont want to fail

Question

Please provide how you completed the table I want to learn badly

Write the correct output for each SQL query. Put your answers in a .txt file (separate the columns properly with the use of the TAB key).

Consider the following tables (1-5):

Product Table

P_code	P_descript	P_indate	P_price	P_salecode
11QER/31	Power painter, 15 psi., 3-nozzle	2007-11-03	109.99	2
89-WRE-Q	Hicut chainsaw, 16 in.	2008-08-02	256.99	1
WR3-TT3	Steel Matting, 4'x8'x1/6”, .5 mesh"	2007-01-17	119.95	1
SH-18277	1.25 in., wd screw, 50	2004-02-28	6.99

 

Sale table

S_code	S_descript
1	Level 1 Sale
2	Level 2 Sale
3	Level 3 Sale

 

Example: SELECT p_code, p_descript, s_descript FROM product,sale

Answer:

- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - 

1) SELECT p_code, p_descript, s_descript FROM product,sale where s_code = '1'

2) SELECT p_code, s_code FROM product INNER JOIN sale on p_salecode = s_code

3) select * from product left join sale on p_salecode = s_code

4) select * from product join sale on p_salecode = s_code

5) select * from product right join sale on p_salecode = s_code

Answer 1

1. SELECT P_code, P_descript, S_descript FROM Product,Sale WHERE S_code = '1'
2. Notice the example is similar to #1 except it doesn't include the where statement
3. SELECT P_code, P_descript, S_descript FROM Product,Sale
4. In the example, all items from the product table are matched with all items of the Sale table: e.g. 11QER/31 shows up in the result table 3 times, 1 time each for Level 1, 2, and 3 Sale
5. Only the three selected columns (P_code, P_descript, P_descript) are shown in the result table
6. WHERE S_code = 1
7. WHERE statements provide a condition which indicates which portion of the data we are interested in displaying.
8. Taking a look at the Sale table, S_descript is "Level 1 Sale" when S_code = 1
9. Therefore, our result table will only contain the rows for "Level 1 Sale"
10. SELECT P_code, S_code FROM Product INNER JOIN Sale on P_salecode = S_code
11. INNER JOIN joins two tables only where the condition is met and ignores all other rows in either table
12. SH-18277 would not be included because it has no value in P_salecode.
13. Similarly, Level 3 Sale would not be included because it has an S_code of 3 and no value in P_Salecode is 3
14. In this case we are only selecting two columns (P_code and S_code)
15. SELECT * from Product LEFT JOIN Sale on P_salecode = S_code
16. LEFT JOIN will take all values from the left table (in this case Product) and only the values from the right table (in this case Sale) that match values in Product based on the condition (in this case P_salecode = S_code)
17. SELECT * selects all columns from both Product and Sale
18. SELECT * FROM Product JOIN Sale on P_salecode = S_code
19. JOIN will take all rows from both tables regardless of whether they have a match on the condition P_salecode = S_code
20. SELECT * FROM Product RIGHT JOIN Sale on P_salecode = S_code
21. RIGHT JOIN is the opposite of LEFT JOIN
22. RIGHT JOIN will take all values from the right table (Sale) and only the values from the left table (Product) that match with Sale under the condition P_salecode = S_code

See comment for result tables.

Answer 2

create table Product (P_code varchar(25),P_descript varchar(255),P_indate varchar(15), P_Price real, P_salecode integer);

Then populates the table using insert commands like the following:

insert into Product values ('11QER/31','Power painter,15 psi.,3-nozzle','2007-11-03',109.99,2);

Does the other records as such...

Next,

create table Sale (S_code integer, S_descript varchar(25));

THen populates the Sale table. Again, for example:

Insert into Sale values (1,'Level 1 Sale');

Populates the other records as such...

==============================================================

(1) 11QER/31 | Power painter,15 psi.,3 nozzle | Level 1 Sale

89-WRE-Q | Hicut chainsaw, 16 in. | Level 1 Sale

WR3-TT3 | Steel Matting:4 foot by 8 foot x 1/6 in: 0.5 mesh | Level 1 Sale

SH-18277 | 1.25in:wd screw:50 | Level 1 Sale

(2) 11QER/31 | 2

89-WRE-Q | 1

WR3-TT3 | 1

(3) 11QER/31 | Power painter,15 psi.,3 nozzle | 2007-11-03 | 109.99 | 2 | 2| Level 2 Sale

89-WRE-Q | Hicut chainsaw, 16 in. | 2008-08-02 | 256.99 | 1 | 1 | Level 1 Sale

WR3-TT3 | Steel Matting:4 foot by 8 foot x 1/6 in: 0.5 mesh | 2007-01-17 | 119.95 |1|1| Level 1 Sale

SH-18277 | 1.25in:wd screw:50 | 2004-02-28 | 6.99 | 1|1|Level 1 Sale

(4)

11QER/31 | Power painter,15 psi.,3 nozzle | 2007-11-03 | 109.99 | 2 | 2| Level 2 Sale

89-WRE-Q | Hicut chainsaw, 16 in. | 2008-08-02 | 256.99 | 1 | 1 | Level 1 Sale

WR3-TT3 | Steel Matting:4 foot by 8 foot x 1/6 in: 0.5 mesh | 2007-01-17 | 119.95 |1|1| Level 1 Sale

(5)

89-WRE-Q | Hicut chainsaw, 16 in. | 2008-08-02 | 256.99 | 1 | 1 | Level 1 Sale

WR3-TT3 | Steel Matting:4 foot by 8 foot x 1/6 in: 0.5 mesh | 2007-01-17 | 119.95 |1|1| Level 1 Sale

SH-18277 | 1.25in:wd screw:50 | 2004-02-28 | 6.99 | 1|1|Level 1 Sale