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 * retrieves all columns from the Articles table.
- ORDER BY Price DESC sorts the results by price in descending order, placing higher-priced articles first.

(ii) SQL Query:

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

Explanation:
- YEAR(DOC) extracts the year from the DOC (Date of Creation) column.
- The WHERE clause filters for records where the year is 2020.
- This query selects articles created in the year 2020.

(iii) SQL Query:

DESC Artists;

Explanation:
- The DESC or DESCRIBE command displays the structure of the Artists table.
- It shows details such as column names, data types, nullability, keys, and default values.
- This is useful for understanding the table schema and for debugging join operations.

(iv-a) SQL Query:

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

Explanation:
- This query performs an Equi Join between the Artists and Articles tables, matching on the A_Code column.
- It then filters the results to include only rows where Article is 'Painting'.
- The artist's Name is selected from the matching records.

(iv-b) SQL Query:

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

Explanation:
- A Natural Join automatically joins the Artists and Articles tables based on columns with identical names and data types, specifically A_Code in this case.
- The join condition does not need to be specified explicitly.
- The query then filters for rows where Article is 'Painting' and returns the artist's Name.