-----
-- CIS 180 - Intro to SQL
-- Class 1 - 2-24-03
-----

-- We didn't actually build a SQL script in class for this day ---
-- this is an attempt to reconstruct a few similar examples, for
-- your future reference.
-- (if there's an example of some concept I've left out, please
-- let me know, and I'll be happy to add it.)

-- last modified: 2-24-03 

-----
-- SQL COMMENTS note
-----

-- As is illustrated in many of the lines above, a SQL comment
-- starts with two dashes and continues to the end of that line
-- (everything from two dashes to the end of a line is ignored)

-----
-- running a SQL script
-----

-- you can run a SQL script - a file containing SQL and SQL*Plus
-- commands - using the SQL*Plus command start, also abbreviated
-- by @. The file name is created outside of sqlplus, and should
-- have a name ending in .sql . For a SQL script file named 
-- my_script.sql that is placed in the same directory that you
-- started sqlplus in, then, any of the following would cause that
-- script to be run within sqlplus: (removing the comment marks,
-- of course! 8-) )
--
-- start my_script.sql
-- start my_script
-- @ my_script.sql
-- @ my_script

-----
-- writing a results file
-----

-- spool filename
-- ...will cause a file named filename to be created, and contain
-- all the results from running your SQL and SQL*Plus commands
-- from the time you type this UNTIL you type:
-- spool off

-- WARNING: do not forget to spool off! Otherwise, part of your
-- results will likely never make it to your results file!

-- so, to start spooling the REST of this script's results to a file
-- 180class1_results.txt:

spool 180class1_results.txt

-- (look at the bottom of this file for the spool off command!)

-----
-- prompt command
-----

-- this SQL*Plus command lets you simply print a message to the screen;

prompt SQL script results from 180class1.sql script:
prompt

-----
-- viewing table contents
-----

-- basic structure of a SQL select statement that simply lets you
-- see the contents of a table my_tbl:
--
-- select *
-- from   my_tbl;

-- view the contents of table dept

select * 
from   dept;

-- view the contents of table empl

select *
from   empl;

-----
-- simple projection using the SQL SELECT statement
-----

-- you PROJECT the desired columns for a result by typing
-- the expressions for what you want to see, separated by
-- commas, after the word select

-- for example, to see JUST the empl_last_name and salary attributes
-- of table empl, you would type:

select empl_last_name, salary
from   empl;

-- if you want to see just the hiredates and salaries, you would type:

select hiredate, salary
from   empl;

-----
-- how to get rid of duplicate rows in simple projections
-----

-- by default, you MIGHT get duplicate rows --- rows containing the very
-- same contents --- using projection as shown above.

-- you can REMOVE such duplicate rows, if any, by putting the word
-- DISTINCT after select, and before the expressions to be projected;

-- compare: this has duplicate rows:

select job_title, mgr
from   empl;

-- this has NO duplicate rows:

select distinct job_title, mgr
from   empl;

-----
-- computed columns
-----

-- you can project the results of a mathematical expression, too,
-- in addition to plain columns (SQL supports +, -, *, /, and probably
-- more...)

select salary * 2, hiredate + 365, empl_last_name
from   empl;

-----
-- column aliases
-----

-- you can RENAME a column resulting from a single select statement
-- by typing it AFTER the projected expression, separated by a blank
-- BEFORE the comma for the next expression to be projected;

-- if you surround the column re-name with double quotes, it can contain
-- blanks, and its case will be preserved:

select empl_last_name "Last Name", salary*2 "Doubled Salary"
from   empl;

-- without double quotes, it CANNOT contain blanks, and you'll see
-- it in all-caps:

select empl_last_name last_name, salary*2 doubled_salary
from empl; 

-----
-- DON'T FORGET to turn off spooling when you are done!
-----

spool off