Q.1 Create following tables using create statement with appropriate constraint and do the followings.
Q : 1 Create following tables using create statement with appropriate constraint and do the
followings.
Artist(aid, fname, lname)
Paintings(pid,pname,aid,listed_price)
Solution.
CREATE TABLE Artist (
aid NUMBER PRIMARY KEY,
fname VARCHAR2(50) NOT NULL,
lname VARCHAR2(50) NOT NULL
);
CREATE TABLE Paintings (
pid NUMBER PRIMARY KEY,
pname VARCHAR2(100) NOT NULL,
aid NUMBER,
listed_price NUMBER(10,2),
CONSTRAINT fk_artist FOREIGN KEY (aid) REFERENCES Artist(aid) ON DELETE CASCADE
);
2. Insert 5 Records in Both Tables:
Solution.
-- Insert records into Artist table INSERT INTO Artist VALUES (1, 'Pablo', 'Picasso'); INSERT INTO Artist VALUES (2, 'Leonardo', 'da Vinci'); INSERT INTO Artist VALUES (3, 'Vincent', 'van Gogh'); INSERT INTO Artist VALUES (4, 'Claude', 'Monet'); INSERT INTO Artist VALUES (5, 'Salvador', 'Dali'); -- Insert records into Paintings table INSERT INTO Paintings VALUES (101, 'Guernica', 1, 2000000); INSERT INTO Paintings VALUES (102, 'The Last Supper', 2, 1800000); INSERT INTO Paintings VALUES (103, 'Starry Night', 3, 2200000); INSERT INTO Paintings VALUES (104, 'Mona Lisa', 2, 3000000); INSERT INTO Paintings VALUES (105, 'The Persistence of Memory', 5, 2500000);
3. Queries as per the Requirements:
(1) Display all details of the artist who painted ID = 104 .
Solution.
SELECT A.* FROM Artist A JOIN Paintings P ON A.aid = P.aid WHERE P.pid = 104;
3. Queries as per the Requirements:
(2) Display all details of the artist who painted ID = 104, 106, 108
Solution.
SELECT DISTINCT A.* FROM Artist A JOIN Paintings P ON A.aid = P.aid WHERE P.pid IN (104, 106, 108);
3. Queries as per the Requirements:
(3) Display total painting price artist-wise
Solution.
SELECT A.aid, A.fname, A.lname, SUM(P.listed_price) AS total_price FROM Artist A JOIN Paintings P ON A.aid = P.aid GROUP BY A.aid, A.fname, A.lname ORDER BY total_price DESC;
3. Queries as per the Requirements:
(4) Display artist names who are NOT having any paintings
Solution.
SELECT A.fname, A.lname FROM Artist A LEFT JOIN Paintings P ON A.aid = P.aid WHERE P.pid IS NULL;
3. Queries as per the Requirements:
(5) Display artist names who have the MAXIMUM number of paintings
Solution.
SELECT A.fname, A.lname
FROM Artist A
JOIN Paintings P ON A.aid = P.aid
GROUP BY A.aid, A.fname, A.lname
HAVING COUNT(P.pid) = (
SELECT MAX(painting_count)
FROM (SELECT aid, COUNT(pid) AS painting_count FROM Paintings GROUP BY aid)
);
3. Queries as per the Requirements:
(6) Display artist name whose painting has the HIGHEST price
Solution.
SELECT A.fname, A.lname FROM Artist A JOIN Paintings P ON A.aid = P.aid WHERE P.listed_price = (SELECT MAX(listed_price) FROM Paintings);
