• Home
• Exercises
  • Chapter 1
  • Chapter 2
  • Chapter 3
  • Chapter 4
  • Chapter 5
  • Chapter 6
  • Chapter 7
  • Chapter 8
  • Chapter 9
  • Chapter 10
  • Chapter 11
  • Chapter 12
  • Chapter 13
  • Chapter 14
  • Chapter 15
  • Chapter 16
  • Chapter 17
  • Chapter 18
  • Chapter 19
  • Chapter 20
  • Chapter 21
  • Chapter 22
  • Chapter 23
  • Chapter 24
  • Reference 1
  • Reference 2
• Classic Models
• Cases
  • Chapter 1
  • Chapter 2
  • Chapter 3
  • Chapter 4
  • Chapter 5
  • Chapter 6
  • Chapter 7
  • Chapter 8
  • Chapter 9
  • Chapter 10
  • Chapter 11
  • Chapter 12
  • Chapter 13
  • Chapter 14
  • Chapter 15
  • Chapter 16
  • Chapter 17
  • Chapter 18
  • Chapter 19
  • Chapter 20
  • Chapter 21
  • Chapter 22
  • Chapter 23
  • Chapter 24
• Test questions
  • Chapter 1
  • Chapter 2
  • Chapter 3
  • Chapter 4
  • Chapter 5
  • Chapter 6
  • Chapter 7
  • Chapter 8
  • Chapter 9
  • Chapter 10
  • Chapter 11
  • Chapter 12
  • Chapter 13
  • Chapter 14
  • Chapter 15
  • Chapter 16
  • Chapter 17
  • Chapter 18
  • Chapter 19
  • Chapter 20
  • Chapter 21
  • Chapter 22
  • Chapter 23
  • Chapter 24
  • Reference 1
  • Reference 2
• Lab exercises
  • Data integration
  • Exams

List the phone numbers of the departments of the fifth floor.

SELECT DEPTPHONE FROM QDEPT
   WHERE DEPTFLOOR = 5 

Find the items not supplied to the Books department.

SELECT DISTINCT ITEMNAME FROM QDEL
   WHERE ITEMNAME NOT IN
      (SELECT DISTINCT ITEMNAME FROM QDEL, QDEPT
         WHERE QDEL.DEPTNAME = QDEPT.DEPTNAME
         AND QDEPT.DEPTNAME = 'Books') 

List khaki items delivered by all suppliers.

SELECT DISTINCT ITEMNAME FROM QITEM
   WHERE ITEMCOLOR = 'Khaki'
      AND NOT EXISTS
         (SELECT * FROM QSPL
            WHERE NOT EXISTS
               (SELECT * FROM QDEL
                  WHERE QDEL.ITEMNAME = QITEM.ITEMNAME
                  AND QDEL.SPLNO = QSPL.SPLNO)) 

4.

Find the items sold by at least two departments.

SELECT ITEMNAME FROM QSALE, QDEPT
   WHERE QSALE.DEPTNAME = QDEPT.DEPTNAME
      GROUP BY ITEMNAME HAVING COUNT (DISTINCT QDEPT.DEPTNAME) > 1 

5.

Find the number of different khaki items delivered by Sao Paulo Manufacturing.

SELECT QITEM.ITEMNAME FROM QITEM, QDEL, QSPL
   WHERE ITEMCOLOR = 'Khaki'
   AND QITEM.ITEMNAME = QDEL.ITEMNAME
   AND QDEL.SPLNO=QSPL.SPLNO
   AND QSPL.SPLNAME = 'Sao Paulo Manufacturing' 

6.

How many people report directly to Alice?

SELECT BOSS.EMPNO, BOSS.EMPFNAME, COUNT (*) FROM QEMP WRK, QEMP BOSS
   WHERE WRK.BOSSNO = BOSS.EMPNO
   AND BOSS.EMPFNAME ='Alice'
      GROUP BY BOSS.EMPNO, BOSS.EMPFNAM 

 7.

Find all items that have been delivered to any other department then Equipment. In other words, do not include any item that has ever been delivered to the equipment department

Select distinct itemname from qdel
  where itemname not in
    (select itemname from qdel where deptname = 'Equipment')

8.

How many departments are on each floor? List out the floor numbers followed by the number of departments on each floor.

Select deptfloor, count(deptname) as '# dept on floor'
  From qdept
    Group by deptfloor

9.

How many employees work on each floor, above the third floor? List the floor numbers followed by

the number of employees on each floor.

Select deptfloor, count(empno) from qdept, qemp
  Where qemp.deptno=qdept.deptno
    Group by deptfloor Having deptfloor > 3

10.

Find the departments that have never sold a geo positioning system

SELECT deptname FROM qdept
  where eptdname not in
   (select deptdname from qsale
      where teminame = 'Geo Positioning System')

Date revised: 10-Dec-2021