Sample queries

1
Sample queries

• Practice in using Oracle SQL (1 of 2)

2
Writing Basic SQL Statements

• Display a list of all attributes of all stock
  items.
• Restricting and Sorting Data
• Change the previous select statement, using
  SQLPLUS, to
• Display only the items supplied by supplier_id
  501
• Display only the stock code, stock description
  and unit price
• Display all stock items, sorted by supplier_id
  and stock_description.

3
Single-Row functions

• Display the day and date (dd-mmm-yy like
  10-Feb-06) of all customer orders.
• For the delivered date for supplier order number
  701, write the equivalent of-
• OrderNo Delivered on a at
• 701 20-Sep-2005 Tuesday 1205pm
• Then

4
Other single-row functions

• List all suppliers who have uppl in their
  names.
• List characters 4-6 of the names of all suppliers
  who dont have uppl in their names.
• For every supplier order that has not yet been
  delivered, display the latest date by which it is
  expected (i.e. 10 days after the ordereddate) as
  follows
• Expecting delivery...
• --------------------------------------------------
  ------------------------------
• Order number 501 should be delivered by 13-SEP-05
  as it was ordered on 03-SEP-05
• Order number 708 should be delivered by 13-SEP-05
  as it was ordered on 03-SEP-05

5
Displaying Data from Multiple tables

• List the stock code, description and supplier
  name of all stock items.
• List the names of all customers and the names of
  suppliers whose stock they have ordered.
• As above, but ordered by supplier name.
• List all the name of each staff member and the
  name of the staff member to which they report
  (see new Builder Schema model)

6

• Show the difference between the following
• List the names of all customers and the names of
  suppliers whose stock they have never ordered
• And
• List the names of all customers and the names of
  suppliers who did not supply each of the stock
  items they have ordered.

7
Aggregating Data using Group Functions

• Display the value (unitprice quantityinstock)
  of each stock item, giving a sub-total for each
  supplier and an overall total of stock value.

8
Subqueries

• Display the names of all customers who have spent
  more than 5,000.
• Display the names of suppliers from whom no
  orders were requested.
• Using a sub-query
• Using an Oracle-type join.