# Comparison of Structured Query Language (SQL) and Query By Example (QBE)

The major differences of SQL and QBE are as follows:

#### SOLVED PROBLEMS

Problem 1. Consider  the following  relational database

Employee (employee-name, street, city)                                                               [B.e. (CSe) m.D.U.]

Works (employee-Name,  company-name, salary)

Company (company-name, city)

Manages (employee-name, manager-name)

1. find the company that has  the most
2. find all employees in the database who live in the same city as the companies they work
3. find the names of all employees  who work for  first corporation
4. find all the employees who don’t work for  first corporation
5. find all those employees who work for first corporation bank and earn more than 10,000.

For the  above queries  give an  expression in  SQL.

Solution.

• SELECT company-name

FROM works

GROUP BY company-name

HAVING COUNT(DISTINCT employee-name) >= all

(SELECT COUNT (DISTINCT employee-name)

FROM works

GROUP BY company-name);

• SELECT employee-name

FROM employee e, works w, company c

WHERE e.employee-name = w.employee-name AND e.city = c.city

AND w.company-name = c.company-name

• SELECTemployee-name

FROM works

WHER Ecompany-name = ‘First corporation bank’;

• SELECT employee-name

FROM works

WHERE company-name <> ‘First corporation bank’;

• SELECT employee-name

FROM employee e, works w

WHERE w.company-name = ‘First corporation bank’

AND w.salary > 10000;

Problem 2.  Let the  following relation  schema be  given:

R  =  (A, B, C)

S  =  (D, E, F)

Let relations r(R) and s(S) be given. Give an expression in SQL that is equivalent to each of the following queries:

Problem 3.
Consider the following database schema and write expression for the queries given below using SQL.                                                                                                                                 [B.e. (CSe) m.D.U.]

Schema

Emp(eid, ename, age, salary)

Works(eid, did, pct-time)

Dept(did, dname, managerid)

Queries

1. List the names and ages of each employee who works in both the hardware department and the software department.
2. List the name of each employee whose salary exceeds the budget of all the departments that he or she works in.
3. Find the managerid of managers who manage only departments with budgets greater than 1 lac.
4. Find the employee names of managers who manage the departments with largest budget.

Solution.

Problem 4. Consider the insurance database and answer the following queries in SQL. Person(driver-id, name, address)                                                                                                                               [B.e. (CSe) m.D.U.]

accident(report-number, date, location)

participated(driver-id, car, report-number, damage-amount)

• Find the total number of people who owned cars that were involved in accidents in
• Find the number of accidents in which the cars belonging to “John Smith” were
• Add a new accident to the  database; assume any  values for  required
• Delete the Mazada belonging to “John Smith”.
• Update the damage amount for the car with license number “AADD 2000” in the accident with report  number  “XRZ197”  to  \$3000.

Solution.

• SELECT count (pe.driver-id)

FROM person pe, accident a, participated p

WHERE a.report-number =  p.report-number AND

p.driver-id = pe.driver-id AND

a.date BETWEEN DATE ‘1989-00-00’ AND DATE ‘1989-12-31’;

• SELECT count (DISTINCT *)

FROM accident

WHERE exists

(                select *

FROM person pe, participated p, accident a

WHERE p.driver-id = pe.driver-id AND

pe.name = ‘John-Smith’ AND

a.report-number = p.report-number);

• INSERT INTO accident

VALUES (2006, ‘2006-01-30’, ‘Paris’);

INSERT INTO participated

VALUES (35, ‘Ferrari’, 2006, 507);

• Delete Car

FROM person p, owns o

WHERE p.name = ‘John Smith’ AND

p.driver-id = o.driver-id);

• Update participated

SET damage-amount = 3000

WHERE report-number  = ‘XRZ197’ AND  driver-id IN

( SELECT driver-id

FROM owns

Problem 5. The following database design is given to you and you are expected to answer the queries given in (a) to ( j) using SQL.

Opening (Account Number, open date, Opening Balance, Total Deposit,

Total Withdrawl, Closing Balance, Closing Balance Date, Last deposit Date, Last Withdrawal Date)

Deposit (Account Number, Data, Amount, Mode)

Withdrawl (Account Number, Date, Amount, Mode)

Account Holder (Account Number, Name, Building Number, Area Number, Street Number, City Code, Pin Code, State Code)

Cities (City Code, City Name, State Code)

State (State Code, State Name)

• List of all Account Number having no deposits in Jan, 2000.
• List of all Account Number having total withdrawal more than 10,000 in Jan, 2000.
• List of all Account Number having neither any  depositor   any   withdrawal   in Jan, 2000.
• List of Account Number and Name of Account holders from city ROHTAK whose opening balance is not less than 9,999.
• List of all cities of the state HARYANA
• ) List of all Account Number and Total Deposites in   Feb,   2000   for   those Account Holders who  belongs to state HARYANA
• List of all city Names from which  there is no Account Holders.
• List of all states from which more than 999 Account Holders are there.
• List of all Account Number which do not have any transactions after opening.
• List of all city name and their pin code for cities of state RAJASTHAN.

Solution.

• SELECT Account Number FROM Opening o, Deposit d

WHERE (o.Account Number = d.Account Number AND

( NOT (d.date BETWEEN DATE ‘2000-01-01’

AND DATE ‘2000-01-31’

))) OR o.Last Deposit Date IS NULL;

• SELECT Account Number

FROM Withdrawal

WHERE sum(Amount) > 10000 AND

(date BETWEEN DATE ‘2000-01-01’ AND DATE ‘2000-01-31’);

• SELECT Account Number

FROM opening o, Deposit d, withdrawal w

WHERE ((o.Account Number = d.Account Number AND

(NOT (d.date BETWEEN DATE ‘2000-01-01’

AND DATE ‘2000-01-31’)))

OR o.Last Deposit Date IS NULL)

AND

((o.Account Number = w.Account Number AND

(NOT (w.date BETWEEN DATE ‘2000-01-01’

AND DATE ‘2000-01-31’)))

OR o.Last withdrawal Date);

• SELECT a.Account Holder, a.Name

FROM Account Holder a, Cities c, Opening o

WHERE a.Account Number = o.Account Number AND

a.City Code = C.City Code AND

c.City Name = ‘ROHTAK’ AND

o.Opening Balance > = 9999;

• SELECT c.City Name

FROM State s, Cities c

WHERE c.state code = s.state code AND

s.statename = ‘HARYANA’;

• SELECT a.Account Number, SuM (d.Amount)

FROM Account Holder a, Deposit d, State s

WHERE a.Account Number = d.Account Number AND

a.State Code = s.State Code AND

s.State Name = ‘HARYANA’ AND

d.date BETWEEN DATE ‘2000-02-01’ AND DATE ‘2000-02-29’;

• SELECT c.City Name

FROM Cities c, Account Holder a

WHERE c.City Code <> a.City Code;

• SELECT s.State Name

FROM State s, Account Holder a

WHERE s.State Code = a.State Code

AND COUNT (a.Account Number) > 999;

• SELECT Account Number

FROM Opening

WHERE Last Deposit Date IS NULL LAST WITHDRAWAL DATE IS NULL;

• SELECT c.City Name, c.City Code

FROM Cities c, State s

WHERE c.State Code = s.State Code AND

s.State Name = ‘RAJASTHAN’;

Problem 6. Given the relational schemas R(ABC) and S(CDE), let r(R) and s(S) be the relations corresponding  to R  and  S respectively  as  the following:

• Give the result of the Cartesian product of r and s, r x s.

• Give the result of r join s, r |X| C = s.C s.

• Give the result of r natural join s, r * s.

• Give the result of r semi-join s, r |X s.

• Give the result of s semi-join r, s |X r.

• Give the result of  r left  outer join  s.

• Give the result of  r right  outer join s.

• Give the result of s left outer join r.

• Give the result of s right outer join r.

• Give the result of  r full  outer join s.

Problem 7. Write the SQL for the following queries:

1. Find the name  of the  artist  who has  created  the Artwork  titled  ‘SS2’
2. Find the ids of customers who have bought more than two different
3. Find the titles of  the Artworks  created by  ‘Sachin’.
4. Find the titles of the artwork bought by customers who live in ‘Jaipur’.
5. Find the names of customers who have not bought an artwork priced more than \$200.
1. Find the names  of customers  who  did not  buy  artworks  created by  ‘Manoj’.
2. Find the names of  customers  who have  spent  more than  \$1500.
3. Find the ids of customers who have bought all artworks created by ‘Shobha Singh’.
4. Find the names of the artists whose work is priced 2nd
5. Find the names of customers who have bought artwork created by an artist from their own

Solution.

• SELECT Name

FROM Artist AT,Artwork AW, Creates CR

WHERE AT.Artist_Id=CR.Artist_Id AND CR.Art_ID=AW.Art_ID AND AW.Title=’SS2’;

• SELECT Cust_Id

FROM Purchase P

Group by P.cust_Id

Having Count(*)>2

• SELECT Title

FROM Artwork AW,Artist AT,Creates CR

WHERE AW.Art_Id=CR.Art_Id AND AT.Artist_Id=CR.Artist_Id AND

AT.Name=’Sachin Rembrandt’

• SELECT Title

FROM Artwork AW,Customer CU,Purchase P

WHERE AW.Art_Id=P.Art_Id AND P.Cust_Id=CU.Cust_Id AND CU.City=’Jaipur‘

• SELECT Cust_Id

FROM Purchase P

WHERE P.Cust_Id NOT IN (SELECT P.Cust_Id FROM Artwork AW,Purchase P2

WHERE Aw.Art_Id=P2.Art_Id AND AW.Price > 200)

• SELECT Name FROM Customer CU1

WHERE CU1.Name NOT IN

(SELECT CU.Name

FROM Customer CU,Purchase P,Artwork AW,Creates CR, Artist AT

WHERE CU.Cust_Id=P.Cust_Id AND P.Art_Id=AW.Art_Id AND

AW.Art_Id=CR.Art_Id AND CR.Artist_Id=AT.Artist_Id AND AT.Name=’ Manoj‘)

• SELECT Name

FROM Customer CU WHERE CU.Cust_Id IN

(SELECT P2.Cust_Id

FROM Purchase P2,Artwork AW

WHERE P2.Art_Id=AW.Art_ID

GROUP BY P2.Cust_Id

Having SUM(P2.Quantity*AW.Price)>1500)

• SELECT Cust_Id

FROM Purchase P1

WHERE NOT EXISTS

(SELECT *

FROM Creates CR,Artist AT

WHERE CR.Artist_Id=AT.Artist_Id AND AT.Name=’Shobha Singh‘ AND

NOT EXIXTS

(SELECT * FROM Purchase P2

WHERE P2.Cust_Id=P1.Cust_Id AND P2.Art_Id=CR.Art_Id)

)

• SELECT Name

FROM Artist AT,Creates CR,Artwork AW

WHERE AT.Artist_Id=CR.Artist_Id AND CR.Art_Id=AW.Art_Id

AND AW.Price=(SELECT Max(Aw2.price) FROM Artwork AW2

WHERE AW2.price  <>  SELECT Max(Aw3.price)  FROM Artwork AW3))

• SELECT Name FROM Artwork AW, Customer CU, Artist AT, Creates CR, Purchase P

WHERE CU.Cust_Id=P.Cust_Id AND P.Art_Id=CR.Art_Id AND CR.Art_id=AW.

Art_Id

AND AT.Artist_Id= CR.Artist_Id AND AT.City=CU.City

Problem 8. Write SQL queries for the following queries.

1. Find author ids for authors who do not live in (CA)
2. Find author names in ascending order that live in California, Florida or New
3. Select titles, price for all the business books, as well as any book with a price higher than \$20.00 and any book with type starting with
4. Find the type and the number of titles for that type such that the average price for such type is  more  than
5. Find the author name who live in the same city as some
6. Find the author names of all books of type ‘Cook’.
7. Find all authors and editors who live in Oakland or
8. Find publisher names that have published business
9. Find the authors who live in that same city as
10. Find publishers for which  there have  been no
11. Find cities where an  author  lives but  no publisher  is
12. Find the names of authors who have participated in writing at least one computer
13. Find the authors who have written all computer
14. Create view hiprice, which includes all  the titles with,  price greater than  \$30.00.
###### Solution.
1. SELECT au_id FROM Authors A WHERE A.state<> ‘CA’
2. SELECT au_name

FROM Authors A

WHERE A.state IN (‘CA’, ‘FL’, ‘NJ’)

ORDER BY A.name

1. SELECT title, T.price

FROM Titles T

T.price>20.00 OR

T.type like ‘C%’

1. SELECT type, count(T.type)

FROM Titles

GROUP BY T.type

HAVING avg(T.price) > 25.00

1. SELECT au_name

FROM Authors A, Publishers P

WHERE A.city=P.city

1. select au_name

from Authors A, Titles T, AuthorTitles AT

where A.au_id=AT.au_id AND T.title_id= AT.title_id AND T.type=’Cook’

1. SELECT au_name

FROM Authors

WHERE city in (‘Oakland’, ’Berkeley’) UNION ALL

SELECT pub_name

FROM Publishers

WHERE city in (‘Oakland’, ’Berkeley’)

1. SELECT pub_name

FROM Publishers

WHERE pub_id IN (SELECT pub_id

FROM Titles

OR

SELECT P.pub_name

FROM Publishers P

WHERE EXISTS (SELECT *

FROM Titles

WHERE pub_id=P.pub_id  AND

1. SELECT au_name

FROM Authors A1, Authors A2

WHERE A1.au_name = ‘Mehta’ AND

A1.city=A2.city

1. SELECT pub_name

FROM Publishers P

WHERE NOT  EXISTS  (SELECT FROM  Titles

WHERE pub_id=P.pub_id)

1. SELECT city

FROM Authors A

WHERE A.city NOT IN (SELECT P.city FROM Publishers P)

1. SELECT au_name

FROM Authors A

WHERE A.au_id IN (SELECT TA.au_id

FROM AuthorTitles AT

WHERE AT.title_id IN (SELECT T.title_id FROM Titles T

WHERE T.type=’Computer’))

1. SELECT au_name FROM Authors A

WHERE NOT  EXISTS  (SELECT FROM  Titles  T

WHERE T.type=’Computer’ AND

NOT EXISTS (SELECT * FROM AuthorTitles AT

WHERE AT.au_id=A.au_id AND

At.title_id=T.title_id))

1. CREATE VIEW hiprice

AS

SELECT * FROM Titles

WHERE price>30.00

Source: Gupta Satinder Bal, Mittal Aditya (2017), Introduction to Basic Database Management System, 2nd Edition-University Science Press (2017)