=====
"TODAY" We Will (in Week 2 Asynchronous Material)
=====
* announcements
* more intro-to-SQL basics
* prep for next class
=====
Reading:
=====
* for next MONDAY: DB Reading Packet 2 - More Database Fundamentals
* we'll be continuing, today and in Friday's lab exercise,
material from SQL Reading Packet 1
=====
Other announcements:
=====
* should be working on Homework 1!
* its problems are due by 11:59 pm on Friday, September 3rd
=====
more intro to SQL and SQL*Plus
=====
******
IMPORTANT NOTE!
******
* do NOT put a blank line *within* a SQL statement!!!
(please but them BETWEEN SQL statements, but not WITHIN them)
* sqlplus treats a blank line as if you are ending the current
SQL statement...!
-----
SQL*Plus command: prompt
-----
* just prints a message to the screen (or, say, to spooled output)
* JUST follow the command with the desired message (!)
* if you follow it with nothing? You get a blank line to the screen...!
prompt
prompt Howdy there CS 325
prompt
-----
SQL*Plus command: / command
-----
* if you have, in SQL*Plus, a command that is just:
/
vvv
...this redoes the previous SQL statement
^^^
-----
more discussion of a FEW more column types
(we'll call these column DOMAINS or attribute DOMAINS next week...)
available in SQL
-----
* reminder: basic SQL create table statement syntax:
create table new_table_name
(col_name col_type any_additional_constraints,
col_name col_type any_additional_constraints,
...
primary key (col_name, col_name, ...),
foreign key (col_name) references another_table
);
* here are a FEW of the more-common data types that the Oracle DBMS
supports for columns in its implementation of SQL:
* varchar2(n) - a varying-length character string up to n characters
long
* char(n) - a fixed-length character string of EXACTLY n characters
* yes this PADS on the right with blanks if you try to store a value
in such a column with fewer than n characters!
* (style!) ONLY use for TRULY fixed-length-strings!
REMEMBER: in SQL, a string literal is surrounded by SINGLE QUOTES!!!
(at least in column values...!)
* date - a true date type! even includes time!
* we'll talk about lovely date-related functions later
* in the meantime, here's the default way of typing a
date literal when used in an insert statement:
'DD-Mon-YYYY' or 'DD-Mon-YY'
'31-Aug-2021' or '31-Aug-21'
* fun fact: Oracle SQL also has a lovely no-argument
function sysdate, that returns the current date...!
* decimal(p, q) - a decimal number of up to p total places,
up to q of which are fractional
decimal(5, 2) - can hold values of up to 5 total places, 2 of
which are fractional -- up to 999.99
* integer - an integer in the range -2,147,482,648 to
2,147,483,647
* don't try to specify the number of digits for an integer,
Oracle SQL does not support that;
* smallint - an integer in the range -32,768 to 32,767
* want to play it safe?
number - can store fixed and floating point numbers,
virtually any magnitude, are guaranteed portable among
different system running Oracle, up to 38 digits of
precision...!
-----
a starting reminder about PRIMARY keys
-----
* COURSE STYLE - every table should have an explictly-defined
primary key
* a primary key is a SET of one or more columns whose values
UNIQUELY a row in a table (no two rows can have the same value(s)
for that set of columns!)
* many/most DBMS's will ENFORCE this, and refuse to allow a row
to be inserted that has the same primary key values as an
existing row!
-----
a few starting words about FOREIGN keys
-----
* these are what we use to RELATE the values in one table to
the values in another table -- why we can say a database is
made up of INTERRELATED tables
* a foreign key is a set of one or more columns that are in
another table, and the intent is that those columns in that
table and those in this table have the same "meaning"
foreign key(col_name, ...) references desired_table
...that means the columns (col_name, ...) in this table
reference those in desired_table
foreign key(col_name, ...) references desired_table(col_name, ...)
...use this form if the referenced column has a different name
(but make sure the contents have the same MEANING...)
IMPORTANT Oracle DBMS note:
the foreign ket column(s) MUST be the primary key of the
referenced table...
* NOW a proper DBMS will CHECK and make SURE a row being added
to a table with a foreign key has a value for the foreign key
column or columns that MATCH at least one row in the referenced
table;
* more on this later!
* once you have foreign keys, you can't drop a referenced table
if another table references it --
UNLESS your drop table statement ends in
...cascade constraints;
...so I'm adding that to the drop table for parts in 325lect02-2.sql
-----
SQL*Plus command: describe
-----
* use to describe the basic structure of the specified table
describe desired_table_name
-----
reminder and more about SQL insert command
-----
* remember: you can only insert one row per insert command
* basic SQL insert syntax #1:
-- you MUST give a value for every column,
-- in the order the columns appear in the create table statement
insert into desired_table
values
(col1_val, col2_val, ...);
* basic SQL insert syntax #2:
-- you don't have to give a value of every column!
(any you don't specify will be considered NULL, the special
thing meaning empty, lack of a value!
unless you specified a default value...
-- you need to specify the columns you ARE setting values to,
and then give the values in that order:
insert into desired_table(first_col_to_set, second_col_to_set, ...)
values
(first_col_to_set_val, second_col_to_set_val, ...);
-----
a FEW additional CONSTRAINTS, or limitations, you can make
when you declare a column in a table
-----
* not null
* you want this column to ALWAYS have a value -- don't
let it be null (don't let it be empty)
create table blah
(...
maxpoints integer not null,
* unique
* you want a (non-primary-key) attribute to also be
unique, not-repeated in any other row
* default desired_def_val
* if a row is inserted that does not explicitly specify a
value for this column, make that column have value
desired_def_value
create table blah
(...
quantity integer default 1,
* check clause
check(boolean_expr)
only allow a value in this column for which boolean_expr is true
create table blah
(...
opening_bid decimal(4, 2) check(opening_bid > 0 AND opening_bid < 50),
* Oracle SQL supports an IN operator!
val IN set
...is true if val is a member of set
create table blah
(...
car_color varchar2(5) check(car_color IN ('red', 'green', 'white')),
* Oracle SQL supports a BETWEEN operator!
val1 BETWEEN val2 AND val3
...is true if val1 >= val2 and val1 <= val3
create table blah
(...
quiz_grade decimal(5, 2) check(quiz_grade between 0.00 and 100.00),