A: SQL for Relations Publisher, Book and Publish: 53

i. Find the name of the publisher which hasn't published any book.

Option #1: Using LEFT OUTER JOIN.

SELECT Publisher.name
FROM   Publisher
       LEFT OUTER JOIN Publish
                    ON Publisher.pid = Publish.pid
WHERE  Publish.bid IS NULL;  

 

Option #2: Using Nested Queries.

SELECT Publisher.name
FROM   Publisher
WHERE  NOT EXISTS (SELECT *
                   FROM   Publish
                   WHERE  Publish.pid = Publisher.pid);  

 

ii. Find the name of the publisher whose name contains at most 7 characters.

Option #1: Using LEN OR DATALENGTH. For Microsoft SQL Server (MSSQL).

SELECT name
FROM   Publisher
WHERE  LEN(name) <= 7;

---------- OR ------------ 

SELECT name
FROM   Publisher
WHERE  DATALENGTH(name) <= 7;

 

Option #2: Using LENGTH OR CHAR_LENGTH. For MySQL.

SELECT name
FROM   Publisher
WHERE  LENGTH(name) <= 7;

---------- OR ------------ 

SELECT name
FROM   Publisher
WHERE  CHAR_LENGTH(name) <= 7;

 

iii. Delete the attribute location from the publisher table.

ALTER TABLE Publisher
  DROP COLUMN location;

 

iv. Find the name of the publisher which has published the most expensive book.

Option #1: Using JOIN and Nested Queries.

SELECT Publisher.name
FROM   Publisher
       JOIN Publish
         ON Publisher.pid = Publish.pid
       JOIN Book
         ON Publish.bid = Book.bid
WHERE  price = (SELECT MAX(price)
                FROM   Book);

 

Option #2: Using JOIN and ORDER BY with LIMIT.

SELECT Publisher.name
FROM   Publisher
       JOIN Publish
         ON Publisher.pid = Publish.pid
       JOIN Book
         ON Publish.bid = Book.bid
ORDER  BY Book.price
LIMIT  1;  

 

v. Update page number of all books to 600 whose price is greater than 500.

UPDATE Book
SET    page = 600
WHERE  price > 500;