Week2 Ilab - WHERE m.movie_id = :g_movie_id GROUP BY...

Info iconThis preview shows pages 1–2. Sign up to view the full content.

View Full Document Right Arrow Icon
Sheet1 Page 1 -- step 1 DESC MM_MOVIE ALTER TABLE MM_MOVIE ADD STK_FLAG CHAR DESC MM_MOVIE -- step 2 SELECT * FROM MM_MOVIE DECLARE CURSOR c_stk IS SELECT * FROM MM_MOVIE FOR UPDATE OF STK_FLAG BEGIN FOR v_mm IN c_stk LOOP IF v_mm.movie_value * v_mm.movie_qty >= 75 THEN UPDATE MM_MOVIE SET STK_FLAG = '*' WHERE CURRENT OF c_stk ELSE UPDATE MM_MOVIE SET STK_FLAG = NULL WHERE CURRENT OF c_stk END IF END LOOP END SELECT * FROM MM_MOVIE -- step 3 SET SERVEROUTPUT ON VARIABLE g_movie_id NUMBER BEGIN :g_movie_id := 4 END / DECLARE v_count NUMBER := 0 v_title mm_movie.movie_title%TYPE BEGIN SELECT COUNT(m.movie_id) INTO v_count FROM mm_movie m
Background image of page 1

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full DocumentRight Arrow Icon
Background image of page 2
This is the end of the preview. Sign up to access the rest of the document.

Unformatted text preview: WHERE m.movie_id = :g_movie_id GROUP BY m.movie_id SELECT m.movie_title, COUNT(r.rental_id) INTO v_title, v_count Sheet1 Page 2 FROM mm_movie m, mm_rental r WHERE m.movie_id = r.movie_id AND m.movie_id = :g_movie_id GROUP BY m.movie_title DBMS_OUTPUT.PUT_LINE(v_title || ': ' || v_count) EXCEPTION WHEN NO_DATA_FOUND THEN IF v_count > 0 THEN DBMS_OUTPUT.PUT_LINE('exception - Movie with ID ' || :g_movie_id || ' has never been rented') ELSE DBMS_OUTPUT.PUT_LINE('exception - there is no movie ID for ' || :g_movie_id) END IF END /...
View Full Document

Page1 / 2

Week2 Ilab - WHERE m.movie_id = :g_movie_id GROUP BY...

This preview shows document pages 1 - 2. Sign up to view the full document.

View Full Document Right Arrow Icon
Ask a homework question - tutors are online