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)

find the company that has the most
find all employees in the database who live in the same city as the companies they work
find the names of all employees who work for first corporation
find all the employees who don’t work for first corporation
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

List the names and ages of each employee who works in both the hardware department and the software department.
List the name of each employee whose salary exceeds the budget of all the departments that he or she works in.
Find the managerid of managers who manage only departments with budgets greater than 1 lac.
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.]

car(license, model, year)

accident(report-number, date, location)

owns(driver-id, license)

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

WHERE model = ‘Mazada’ AND license IN ( Select licence

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

WHERE license = ‘AADD2000’);

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:

Find the name of the artist who has created the Artwork titled ‘SS2’
Find the ids of customers who have bought more than two different
Find the titles of the Artworks created by ‘Sachin’.
Find the titles of the artwork bought by customers who live in ‘Jaipur’.
Find the names of customers who have not bought an artwork priced more than $200.

Find the names of customers who did not buy artworks created by ‘Manoj’.
Find the names of customers who have spent more than $1500.
Find the ids of customers who have bought all artworks created by ‘Shobha Singh’.
Find the names of the artists whose work is priced 2^nd
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.

Find author ids for authors who do not live in (CA)
Find author names in ascending order that live in California, Florida or New
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
Find the type and the number of titles for that type such that the average price for such type is more than
Find the author name who live in the same city as some
Find the author names of all books of type ‘Cook’.
Find all authors and editors who live in Oakland or
Find publisher names that have published business
Find the authors who live in that same city as
Find publishers for which there have been no
Find cities where an author lives but no publisher is
Find the names of authors who have participated in writing at least one computer
Find the authors who have written all computer
Create view hiprice, which includes all the titles with, price greater than $30.00.

Solution.

SELECT au_id FROM Authors A WHERE A.state<> ‘CA’
SELECT au_name

FROM Authors A

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

ORDER BY A.name

SELECT title, T.price

FROM Titles T

WHERE T.type=’BUSINESS’ OR

T.price>20.00 OR

T.type like ‘C%’

SELECT type, count(T.type)

FROM Titles

GROUP BY T.type

HAVING avg(T.price) > 25.00

SELECT au_name

FROM Authors A, Publishers P

WHERE A.city=P.city

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’

SELECT au_name

FROM Authors

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

SELECT pub_name

FROM Publishers

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

SELECT pub_name

FROM Publishers

WHERE pub_id IN (SELECT pub_id

FROM Titles

WHERE type=’Business’)

SELECT P.pub_name

FROM Publishers P

WHERE EXISTS (SELECT *

FROM Titles

WHERE pub_id=P.pub_id AND

type=’Business’)

SELECT au_name

FROM Authors A1, Authors A2

WHERE A1.au_name = ‘Mehta’ AND

A1.city=A2.city

SELECT pub_name

FROM Publishers P

WHERE NOT EXISTS (SELECT * FROM Titles

WHERE pub_id=P.pub_id)

SELECT city

FROM Authors A

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

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’))

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))

CREATE VIEW hiprice

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)

SOLVED PROBLEMS

Solution.

Leave a Reply Cancel reply

Login