From Database Systems (600.466) Lefture, Oct 11, 2001.
Copyright Ryan A. Brown, Johns Hopkins University. catalina@cs.jhu.edu

Using Oracle
============
 cd
 ~oracle/signup/signmeup
 ./sql

 ... enter username & password ...

 SQL> issue SQL commands followed by a ; or exit on its own line to quit.

CREATE TABLE carmodel (
  modelid NUMBER        NOT NULL,
  model VARCHAR(20)	NOT NULL,
  make VARCHAR(20)	NOT NULL,
  submodel VARCHAR(10),
  PRIMARY KEY (modelid),
  UNIQUE (modelid));

INSERT INTO carmodel VALUES (500, 'Accord', 'Honda', 'Coupe V6');
INSERT INTO carmodel VALUES (501, 'Accord', 'Honda', 'Sedan');
INSERT INTO carmodel VALUES (502, 'Insight', 'Honda', NULL);

CREATE TABLE car (
  vin VARCHAR(20)	NOT NULL,
  modelid NUMBER	NOT NULL,
  mileage NUMBER	NOT NULL,
  color VARCHAR(15)	NOT NULL,
  PRIMARY KEY (vin),
  UNIQUE (vin),
  FOREIGN KEY (modelid) REFERENCES carmodel (modelid));

INSERT INTO car VALUES ('XPO930842KCD', 500, 37500, 'Red');
INSERT INTO car VALUES ('KPEWLICI0843', 501, 13200, 'Green');
INSERT INTO car VALUES ('OIUEW098324K', 502, 0, 'Silver');
INSERT INTO car VALUES ('09834LKCLUEA', 501, 9000, 'Green');

SELECT *
FROM car;

SELECT *
FROM car
WHERE modelid = 501;

SELECT car.*
FROM car, carmodel
WHERE car.modelid = carmodel.modelid AND model = 'Accord';

SELECT AVG(car.mileage)
FROM car, carmodel
WHERE car.modelid = carmodel.modelid AND carmodel.model = 'Accord';

Administering Oracle
====================
Starting & Stopping Oracle
--------------------------
  hops> svrmgrl

  SVRMGRL> CONNECT INTERNAL

  SVRMGRL> startup mount		starts up without letting users in

  SVRMGRL> startup			starts up normally

  SVRMGRL> shutdown			shuts down

  SVRMGRL> exit				exits the svrmgrl program

Creating a Table Space
----------------------
  SQL> CREATE TABLESPACE myspace DATAFILE '/path/to/files/filename.dbf'
       SIZE 10M;

Creating Users
--------------
  SQL> CREATE USER username IDENTIFIED BY password
       DEFAULT TABLESPACE myspace QUOTA 1M ON myspace;

  SQL> GRANT connect, resource TO username;

Changing a Password
-------------------
  SQL> ALTER USER username IDENTIFIED BY password;

Removing a User
---------------
  SQL> DROP USER username CASCADE;

Removing a Tablespace
---------------------
  SQL> DROP TABLESPACE myspace INCLUDING CONTENTS;

  .. then delete the tablespace files by hand..

Special Tables
--------------
dba_tables: owner, table_name, tablespace_name, num_rows, etc.
  SQL> select owner, table_name FROM dba_tables;

dba_users: username, user_id, password, default_tablespace, created, profile
  SQL> select * from dba_users where username = 'XCATALINA';

SQL In Action
=============

Loading a file of SQL commands:

spool mylog
start make-banking.sql
start make-airline-DB.sql
spool off

----------------------------------------------------------------------------
-- Queries from blue book for Banking DB:

-- All customer data.
select *
from customer;

-- Names and cities of all borrowers
select distinct customer.customer_name, customer_city
from borrower, customer
where borrower.customer_name = customer.customer_name;

-- Set of names and cities of customers who have a loan at Perryridge branch
select distinct C.customer_name, customer_city
from customer C, borrower B, loan L
where C.customer_name = B.customer_name and 
	B.loan_number = L.loan_number and 
 	  branch_name = 'Perryridge';

-- Numbers of accounts with balances between 700 and 900.
select account_number
from account
where balance between 700 and 900;

-- Names of customers on streets with names ending in "Hill".
select customer_name
from customer
where customer_street like '%Hill';

-- Names of customers with both accounts and loans at Perryridge branch.
select distinct customer_name
from borrower, loan
where borrower.loan_number = loan.loan_number and
    branch_name = 'Perryridge' and
	customer_name in (select customer_name
				from account, depositor
				where account.account_number =
					depositor.account_number and
					   branch_name = 'Perryridge');

-- Names of customers with an account but not a loan at Perryridge branch.
select distinct customer_name
from account, depositor
where account.account_number = depositor.account_number and
	branch_name = 'Perryridge' and
	customer_name not in (select customer_name
				from loan, borrower
				where loan.loan_number = 
					borrower.loan_number and 
					   branch_name = 'Perryridge');

-- Set of names of customers with accounts at a branch where Hayes has
-- an account.
select distinct D.customer_name
from depositor D, account A
where D.account_number = A.account_number and
    branch_name in
	(select branch_name
	from depositor Dh, account Ah
	where Dh.account_number = Ah.account_number and
	    D.customer_name = 'Hayes');

-- Set of names of branches whose assets are greater than the assets of
-- some branch in Brooklyn
select distinct T.branch_name
from branch T, branch S
where T.assets > S.assets and
	S.branch_city = 'Brooklyn';

-- Names of customers with both accounts and loans at Perryridge branch
-- (using "exists").
select customer_name
from customer
where exists (select *
		from account, depositor
		where account.account_number = depositor.account_number and
			depositor.customer_name =
			customer.customer_name and
 			branch_name = 'Perryridge')
	and exists (select *
		from loan, borrower
 		where loan.loan_number = borrower.loan_number and
			borrower.customer_name =
			customer.customer_name and
 			branch_name = 'Perryridge');

-- Names of customers with an account but not a loan at Perryridge branch
-- (using "exists").
select customer_name
from customer
where exists (select *
		from account, depositor
		where account.account_number = depositor.account_number and
			depositor.customer_name = customer.customer_name and
			branch_name = 'Perryridge')
	and not exists (select *
		from loan, borrower
 		where loan.loan_number = borrower.loan_number and
			borrower.customer_name = customer.customer_name and
			branch_name = 'Perryridge');

-- The average balance of all accounts.
select avg(balance)
from account;

-- Names of branches having at least one account, with average account
-- balances.
select branch_name, avg(balance)
from account
group by branch_name;

-- Name(s) of branch(es) having largest average balance.
select branch_name
from account
group by branch_name
having avg(balance) >= all(select avg(balance)
				from account
				group by branch_name);

>=, >, <, <=, =   ALL | ANY

-- Average balance of all customers in Harrison having at least 2 accounts.
select avg(balance)
from depositor, account, customer
where depositor.customer_name = customer.customer_name and
	depositor.account_number = account.account_number and
	customer_city = 'Harrison'
group by depositor.customer_name
having count (distinct account.account_number) >= 2;

--------------------------------------------------------------------------
-- Queries for airline-DB:

-- List the number of planes leaving each Origin City
SELECT Flight.Origin, COUNT(Flight.Num)
FROM   Flight
GROUP BY Flight.Origin;

-- List all the kinds of aircraft that we actually fly
SELECT DISTINCT Plane.Maker, Plane.Model_No
FROM   Plane, Aircraft
WHERE  Plane.Model_No = Aircraft.Model_No;

-- Get the Name and salary of all pilots whose salary is between
-- 40,000 and 55,000, ranked lowest to highest

SELECT Employee.Name, Employee.Salary
FROM   Employee, Pilot
WHERE  Employee.Emp_No = Pilot.Emp_No AND
       (Employee.Salary BETWEEN 40000 AND 55000)
ORDER BY Employee.Salary;

-- Get the Name and Address of all Employees who have a salary that is
-- above average

SELECT Person.Name, Person.Address
FROM   Person,
       (SELECT Employee.Name
        FROM   Employee
	WHERE  Employee.Salary >
	       (SELECT AVG(Salary)
	        FROM Employee)) E
WHERE  Person.Name = E.Name;

-- Get the Name and salary of all Pilots that have a salary greater than the
-- salary of all non-pilots, ranked from highest to lowest salary

SELECT Employee.Name, Employee.Salary
FROM   Employee
WHERE  Employee.Salary > ALL
       (SELECT Employee.Salary
        FROM   Employee
	WHERE  Employee.Emp_No NOT IN
	       (SELECT Emp_No
	        FROM   Pilot))
       AND
       Employee.Emp_No IN
	       (SELECT Emp_No
	        FROM Pilot)
ORDER BY Employee.Salary DESC;		

-- Get the Flight Number and Origin and number of passengers for all
-- Flights going to Miami (MIA) that have at least 3 passengers

SELECT Flight.Num, Flight.Origin, COUNT(Booked_On.Name)
FROM   Flight, Booked_On
WHERE  Flight.Num = Booked_On.Num AND
       Flight.Dest = 'MIA'
GROUP BY Flight.Num, Flight.Origin
HAVING	 COUNT(Booked_On.Name) > 3;

-- Make a pink sheet that has each flight, its origin
-- and the Name & Phone of each person (passenger & crew) on the plane

((SELECT Flight.Num, Flight.Origin, Person.Name, Person.Phone
  FROM   Flight, Booked_On, Person
  WHERE  Flight.Num = Booked_On.Num AND
	 Booked_On.Name = Person.Name)
  UNION
 (SELECT Flight.Num, Flight.Origin, Person.Name, Person.Phone
  FROM	 Flight, Assigned_To, Employee, Person
  WHERE	 Flight.Num = Assigned_To.Num AND
	 Assigned_To.Emp_No = Employee.Emp_No AND
	 Employee.Name = Person.Name));

Links
=====
  Online SQL Tutorials: 
    http://fluffycat.com/sql/
    http://www.sqlcourse2.com/

  SQL/Database Management Information Databases
    http://www.dbasupport.com
    http://www.1sqlstreet.com/

  Oracle FAQ
    http://www.orafaq.org/faq2.htm

  Good Example Tables & Queries
    http://www.bell-labs.com/topic/books/db-book/supplements-dir/