Q.Assume that you are working in the IT Department of a Creative Art Gallery (CAG), which sells different forms of art creations like Paintings, Sculptures etc. The data of Art Creations and Artists are kept in tables Articles and Artists respectively. Following are few records from these two tables : Table : Articles Code A_Code Article DOC Price PL001 A0001 Painting 2018-10-19 20000 SC028 A0004 Sculpture 2021-01-15 16000 QL005 A0003 Quilling 2024-04-24 3000 Table : Artists A_Code Name Phone Email DOB A0001 Roy 595923 r@CrAG.com 1986-10-12 A0002 Ghosh 1122334 ghosh@CrAG.com 1972-02-05 A0003 Gargi 121212 Gargi@CrAG.com 1996-03-22 A0004 Mustafa 33333333 Mf@CrAg.com 2000-01-01 Note : - The tables contain many more records than shown here. - DOC is Date of Creation of an Article. As an employee of CAG, you are required to write the SQL queries for the following :

Part (a)

The Articles and Artists tables share the column A_Code. Queries (i)-(iii) plus (iv) via an equi join:

-- (i) all Articles, most expensive first
SELECT * FROM Articles ORDER BY Price DESC;

-- (ii) Articles created in the year 2020
SELECT * FROM Articles WHERE YEAR(DOC) = 2020;

-- (iii) structure of the Artists table
DESCRIBE Artists;

-- (iv)(a) names of artists whose article is Painting, using an EQUI JOIN
SELECT Artists.Name
FROM Articles, Artists
WHERE Articles.A_Code = Artists.A_Code
  AND Articles.Article = 'Painting'; …

The two alternatives differ only in the last query (iv): part (a) does it with an equi join, part (b) with a natural join. Queries (i)-(iii) are common and shown once here in part (a).

Part (a)

(i) All records, highest price first — DESC gives descending order:

SELECT * FROM Articles ORDER BY Price DESC;

(ii) Articles created in 2020 — YEAR(DOC) extracts the year from the date column DOC:

SELECT * FROM Articles WHERE YEAR(DOC) = 2020;

(iii) The structure (columns, types, keys) of Artists — use DESCRIBE (or DESC); it shows the design, not the data:

DESCRIBE Artists;

(iv)(a) Equi join — the tables are joined by writing the equality of the common column A_Code in the WHERE clause, then filtered to Paintings:

SELECT Artists.Name
FROM Articles, Artists
WHERE Articles.A_Code = Artists.A_Code
  AND Articles.Article = 'Painting';
``` …