is420exam1review

• –WHERE columnname IS NULL
• –WHERE columnname IS NOT NULL

WHERE columnname LIKE 'string'

• select rental_no, apartment.apt_no, apt_type
• from apartment, rental
• where apartment.apt_no = rental.apt_no (+)
• and apt_utility = 'Y';

• SELECT field1, field2, …
• FROM table1 LEFT JOIN table2
• ON join condition1… WHERE search_conditions

• select rental.rental_no, complaint_no
• from rental full join complaints
• on rental.rental_no = complaints.rental_no;

Database views are sort of pseudo tables that are created to present a particular “display” of database content

• CREATE VIEW rental_details AS
• (SELECT rental_no, rental.apt_no, apt_type, apt_rent_amt
• FROM rental, apartment
• WHERE rental.apt_no = apartment.apt_no);

• CREATE MATERIALIZED VIEW  view-name
• [REFRESH COMPLETE]
• [START WITH start-date NEXT refresh-date]
• AS database-query

no more than one of the same

• avg
• count(*)
• count
• max
• min
• sum

• create sequence club_id_sequence
• start with 100
• increment by 10;

• –INSERT INTO location(LOC_ID) VALUES (loc_id_sequence.NEXTVAL);
• –insert into mountain_clubs (club_id, name)  values(club_id_sequence.nextval, 'Hillside Mountain Club');

• simple table in system user schema
• SELECT sequence_name.CURRVAL FROM DUAL;
• SELECT sequence_name.NEXTVAL FROM DUAL;

• abs
• ceil
• floor
• mod
• power
• round
• sign
• trunc

• concat
• initcap
• length
• lpad
• rpad
• replace
• substr
• upper
• lower

• month, mon, m
• d, dd, day
• yyyy, yy, y
• hh, hh24, mi, ss

• GRANT/REVOKE privilege1, privilege2, …
• ON object_name
• TO user1, user2, …;

• Grant select, insert on customer to thomas;
• Revoke insert on customer from thomas;

SELECT table_name FROM user_tables;

–provide a way to reference a table row or a row of output from a SQL query.

dbms_output.put_line('Row Number '||count_loop);

• declare
• t_cost number(2);
• t_desc varchar2(20);
• total number(1);
• begin
• total := 8;
• if total >= 8 then
•    t_cost := 10;
• else
•    t_desc := 'condition not met';
• end if;
• end;

• declare
• tcount number(2) :=10;
• begin
• loop
•   insert into abc values (tcount);
•   tcount := tcount + 1;
•   exit
• when tcount > 15;
•   dbms_output.put_line('Value inserted '||tcount);
• end loop;
• end;

The implicit cursor has the limitation that it can handle only one row of result returned by the database server.

• declare
• first_name customer.first_name%type;
• last_name customer.last_name%type; 
• begin
• select first_name, last_name
• into first_name, last_name
• from customer
• where customer_id = &custid; 
• dbms_output.put_line('Name is '||first_name||' '||last_name);
• end;

• declare
• cursor cust_cursor is 
• select first_name, last_name from customer;
• cust_row cust_cursor%rowtype;
• begin
• open cust_cursor;
• loop
•  fetch cust_cursor into cust_row;
•  exit when cust_cursor%notfound;
•  dbms_output.put_line(cust_row.first_name||'  '||cust_row.last_name);
• end loop;
• close cust_cursor;
• end;

• declare
• cursor auto_cursor is
• select auto_make, auto_model from tenant_auto
• where auto_year > 1999;
• auto_row auto_cursor%rowtype;
• begin
• for auto_row in auto_cursor
• loop
• dbms_output.put_line('Make is '||auto_row.auto_make||
•                      ' and model is '||auto_row.auto_model);
• end loop;
• end;

• create or replace procedure auto_list is
• cursor auto_cursor is 
• select auto_make, auto_model from tenant_auto
• where auto_year > 1999;
• Auto_row auto_cursor%rowtype;
• begin
•  open auto_cursor;
•  loop
• fetch auto_cursor into auto_row;
• exit when auto_cursor%notfound;
• dbms_output.put_line('Make is ' || auto_row.auto_make ||' model is '||auto_row.auto_model);
•  end loop;
•  close auto_cursor;
• end;

• create or replace procedure calling_procedure IS auto_out1 tenant_auto.auto_make%type;
• auto_out2 tenant_auto.auto_model%type;
• begin
•  dbms_output.put_line('Start auto list proc');
• Auto_list;
•  dbms_output.put_line('Start auto list2 proc');
• Auto_list2(1999);
• end;

• create or replace function tenant_function (ss_in IN number)
• RETURN varchar2 IS
• name_in tenant.tenant_name%type;
• begin
•  select tenant_name  into name_in from tenant
•  where tenant_ss = ss_in;
•  RETURN name_in;
• end;

• . . .
• exception
•   when no_data_found then
•   dbms_output.put_line('Error Message.');
• . . .

• create or replace procedure exception_test3 is
• e_foreign_key_error exception;
• pragma exception_init(e_foreign_key_error, -2291);
• . . .

RAISE exception-name;