Common SQL Misconceptions

1
Common SQL Misconceptions
2
Name of customers if there exists an order with
p02

• This is not necessarily the name of customers
  that ordered p02. It is printing a name of a
  customer if somebody ordered p02.

• SELECT cname
• FROM customers,
• orders
• WHERE
• pid p02

3
No rows selected

• FOR EACH ROW OF
• IF pid p02 and pid p04
• display cid

• SELECT cid FROM orders WHERE
• pid p02 and
• pid p04

4
NOT

• Select cid from orders where pid ltgt p02

• Cid of customers that one of their orders was not
  p02
• Tiptop (c001) satisfies this criteria

5
name of customers who ORDERED ALL PRODUCTS
 select ALL customers (cid) where not exists a
product that he has not ordered 
DIVISION IN SQL
6
DIVISION IN SQL

• SELECT cname FROM customer c
• WHERE NOT EXISTS (SELECT from PRODUCTS
  p
• WHERE NOT EXISTS
• (SELECT FROM orders o
• WHERE c.cid o.cid AND
• p.pid o.pid ))

7
3 most expensive products

• Name of products where not exists 3 other
  products that are cheaper than that product
• SELECT pname from products
• WHERE NOT EXISTS
• (SELECT ..)