Question

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:

(i) To display all the records from the Articles table in descending order of Price.
(ii) To display the details of Articles which were created in the year 2020.
(iii) To display the structure of the Artists table.
(iv-a) To display the name of all artists whose Article is Painting using Equi Join.
(iv-b) To display the name of all artists whose Article is 'Painting' using Natural Join

Hint:

Use ORDER BY ... DESC to sort descending,
YEAR(DATE) to extract year from a date field,
DESC tableName to check table schema,
Equi Join for manual matching,
and Natural Join for automatic matching on common columns.

Answer 1

(i) SQL Query:

SELECT * FROM Articles ORDER BY Price DESC;

Explanation:
- SELECT * fetches all the fields from the Articles table.
- ORDER BY Price DESC ensures that the records are sorted by price in decreasing order, so higher-priced articles appear first.

(ii) SQL Query:

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

Explanation:
- The YEAR(DOC) function extracts the year from the DOC (Date of Creation) column.
- The WHERE clause filters records where the year equals 2020.
- This allows only those articles to be selected which were created in that specific year.

(iii) SQL Query:

DESC Artists;

Explanation:
- The DESC or DESCRIBE command shows the structure of a table.
- It displays each column name, data type, nullability, keys, default values, etc.
- Very useful for understanding schema or debugging joins.

(iv-a) SQL Query:

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

Explanation:
- This is a classic Equi Join based on the equality of A_Code in both tables.
- After joining, it filters for rows where Article = 'Painting'.
- Only the artist Name field is selected from the final matched rows.

(iv-b) SQL Query:

SELECT Name FROM Artists NATURAL JOIN Articles
WHERE Article = 'Painting';

Explanation:
- A Natural Join automatically joins tables on columns with the same name and datatype — in this case, A_Code.
- No need to mention the join condition explicitly.
- Once joined, we filter rows where Article = 'Painting' and retrieve only the Name.