Scribd
Upload
17 views3 pages

dbms 2 assignments

The document contains a series of SQL queries that retrieve various data from a database, including item details, employee information, and customer records. The queries utilize subqueries to filter results based on conditions such as price, status, and relationships between tables. The document highlights operations like selecting distinct values, finding maximum and minimum prices, and deleting records based on specific criteria.

Uploaded by

mabos25623
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as TXT, PDF, TXT or read online on Scribd
17 views3 pages

dbms 2 assignments

The document contains a series of SQL queries that retrieve various data from a database, including item details, employee information, and customer records. The queries utilize subqueries to filter results based on conditions such as price, status, and relationships between tables. The document highlights operations like selecting distinct values, finding maximum and minimum prices, and deleting records based on specific criteria.

Uploaded by

mabos25623
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as TXT, PDF, TXT or read online on Scribd
You are on page 1/ 3

CA93

SELECT DISTINCT i.itemcode, i.itemtype, i.descr, i.category


FROM item i , purchasebill pb
WHERE i.itemcode = pb.itemcode

94
SELECT itemcode, itemtype, descr, category
FROM item
WHERE price IN
(
SELECT min(quotedprice) FROM quotation WHERE qstatus = 'Rejected'
)

95
SELECT i.itemcode, i.descr
FROM item i, quotation q
WHERE i.itemcode = q.ITEMCODE AND q.qstatus IN ('Rejected','Closed') AND
q.quotedprice IN
(
SELECT max(q1.quotedprice)FROM quotation q1 WHERE q1.qstatus IN
('Rejected','Closed')
)

96
SELECT itemcode, descr, price FROM item WHERE price = (SELECT MAX(price) FROM item
WHERE price != (SELECT MAX(price) FROM item))
or

SELECT i.itemcode, i.descr,i.price


FROM item i, quotation q
WHERE i.itemcode = q.ITEMCODE AND q.qstatus IN ('Rejected','Closed') AND
q.quotedprice IN
(
SELECT max(q1.quotedprice)FROM quotation q1 WHERE q1.qstatus IN
('Rejected','Closed')
)

97
SELECT empid, empname, designation
FROM empdetails
WHERE empid IN
(
SELECT managerid FROM retailoutlet)

98
SELECT e.ename,e.job FROM emp e
WHERE empno IN
(SELECT ev.empno FROM empvehicle ev)

99
SELECT e1.ename FROM emp e1
WHERE e1.sal =
(SELECT max(e2.sal) FROM emp e2)

100
SELECT emp.empno, emp.ename FROM emp
WHERE emp.empno IN
(
SELECT e1.EMPNO FROM empvehicle e1,empvehicle e2
WHERE e1.vehicleid = e2.vehicleid AND e1.EMPNO != e2.EMPNO
GROUP BY e1.empno
)

101
SELECT e1.empno, e1.ename, e1.deptno FROM emp e1,emp e2
WHERE e1.deptno = e2.deptno AND e2.ename = 'SMITH' AND e1.ename != e2.ename

102
SELECT q1.itemcode, i.descr, q1.qdate FROM quotation q1,item i
WHERE i.itemcode = q1.itemcode AND q1.quotedprice <
(
SELECT max(q2.quotedprice) FROM quotation q2
WHERE q2.qdate = q1.qdate AND q1.quotationid != q2.quotationid
GROUP BY q2.qdate
)

103
SELECT pb1.billid, pb1.itemcode FROM purchasebill pb1
WHERE pb1.BILLAMOUNT <=
(
SELECT sum(billamount)/count(roid) FROM purchasebill pb2
WHERE pb2.roid = pb1.roid
GROUP BY roid
)

104
SELECT DISTINCT sname, i.itemcode, descr
FROM item i, quotation q
WHERE i.itemcode = q.ITEMCODE
AND q.quotedprice <
(SELECT max(q1.quotedprice) FROM quotation q1 WHERE q.itemcode = q1.itemcode AND
q.sname <> q1.sname)

105
SELECT empid, empname, designation, salary FROM empdetails e2
WHERE e2.salary =
(
SELECT max(e1.salary) FROM empdetails e1
WHERE e1.designation = e2.designation
GROUP BY e1.designation
)

106
SELECT c.custid, c.custname FROM customer c
WHERE NOT EXISTS (SELECT 1 FROM purchasebill WHERE c.CUSTID = custid)

107
SELECT BillId, PB.ItemCode, PB.CustId, BillAmount, BillDate, Quantity
FROM PurchaseBill PB
WHERE pb.custid = (SELECT custid FROM customer c WHERE custtype = 'Privileged' AND
c.CUSTID = pb.custid)
AND pb.itemcode = (SELECT itemcode FROM item i WHERE itemtype = 'FMCG' AND
i.itemcode = pb.itemcode)

108
SELECT c.custid, c.custname FROM customer c
WHERE NOT EXISTS (SELECT 1 FROM purchasebill WHERE c.CUSTID = custid)

109
SELECT e2.empno, e2.ename FROM emp e2
WHERE e2.sal >
(
SELECT avg(sal) FROM emp e1
WHERE e1.deptno = e2.deptno
GROUP BY deptno
)

110
DELETE FROM empvehicle where empno IN
(
SELECT empno FROM emp WHERE sal in
(SELECT max(sal) FROM emp
GROUP BY deptno )
)

You might also like