is420exam1review

Card Set Information

Author:
tttran1
ID:
176961
Filename:
is420exam1review
Updated:
2012-10-11 03:07:38
Tags:
is420 exam1 review
Folders:

Description:
Review for is420 exam 1
Show Answers:

Home > Flashcards > Print Preview

The flashcards below were created by user tttran1 on FreezingBlue Flashcards. What would you like to do?


  1. How do we test for NULLs in the database?
    • –WHERE columnname IS NULL
    • –WHERE columnname IS NOT NULL
  2. Like Operator
    WHERE columnname LIKE 'string'
  3. outer join
    • select rental_no, apartment.apt_no, apt_type
    • from apartment, rental
    • where apartment.apt_no = rental.apt_no (+)
    • and apt_utility = 'Y';
  4. left/right joins
    • SELECT field1, field2, …
    • FROM table1 LEFT JOIN table2
    • ON join condition1… WHERE search_conditions
  5. full join
    • select rental.rental_no, complaint_no
    • from rental full join complaints
    • on rental.rental_no = complaints.rental_no;
  6. ¨What is a database view?
    Database views are sort of pseudo tables that are created to present a particular “display” of database content
  7. Create database view
    • 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);
  8. Materialized Views
    • CREATE MATERIALIZED VIEW  view-name
    • [REFRESH COMPLETE]
    • [START WITH start-date NEXT refresh-date]
    • AS database-query
  9. what is the effect of DISTINCT in a SELECT statement?
    no more than one of the same
  10. ¨Which are the built-in functions in SQL?
    • avg
    • count(*)
    • count
    • max
    • min
    • sum
  11. Create and/or use a sequence
    • create sequence club_id_sequence
    • start with 100
    • increment by 10;
  12. insert into sequence
    • –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');
  13. Dual
    • simple table in system user schema
    • SELECT sequence_name.CURRVAL FROM DUAL;
    • SELECT sequence_name.NEXTVAL FROM DUAL;
  14. SQL number functions
    • abs
    • ceil
    • floor
    • mod
    • power
    • round
    • sign
    • trunc
  15. SQL character functions
    • concat
    • initcap
    • length
    • lpad
    • rpad
    • replace
    • substr
    • upper
    • lower
  16. date format masks
    • month, mon, m
    • d, dd, day
    • yyyy, yy, y
    • hh, hh24, mi, ss
  17. Give and/or revoke privileges on database objects
    • GRANT/REVOKE privilege1, privilege2, …
    • ON object_name
    • TO user1, user2, …;

    • Grant select, insert on customer to thomas;
    • Revoke insert on customer from thomas;
  18. Write SQL queries against the data dictionary
    SELECT table_name FROM user_tables;
  19. Explain what %rowtype means
    –provide a way to reference a table row or a row of output from a SQL query.
  20. pl/sql display statement
    dbms_output.put_line('Row Number '||count_loop);
  21. if else statement
    • 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;
  22. Iteration statement
    • 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;
  23. What is a major limitation of implicit cursors, and how we get around it?
    The implicit cursor has the limitation that it can handle only one row of result returned by the database server.
  24. PL/SQL code using implicit cursor
    • 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;
  25. Write PL/SQL code using an explicit cursor to get something from the database
    • 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;
  26. PL/SQL code explicit cursor with for loop
    • 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;
  27. Write a PL/SQL procedure
    • 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;
  28. Call procedure
    • 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;
  29. Write a PL/SQL function
    • 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;
  30. Pre-defined exception
    • . . .
    • exception
    •   when no_data_found then
    •   dbms_output.put_line('Error Message.');
    • . . .
  31. Undefined exception
    • create or replace procedure exception_test3 is
    • e_foreign_key_error exception;
    • pragma exception_init(e_foreign_key_error, -2291);
    • . . .
  32. User-defined exception
    RAISE exception-name;

What would you like to do?

Home > Flashcards > Print Preview