1-1
Vocabulary
a. Procedural programming
b. 3GL
Try It/Solve It
1.
3GL -> PL/SQL
4GL -> SQL
ORACLE -> SQL
Nonprocedural -> SQL
Procedural -> PL/SQL
ANSI -> PL/SQL
[Link] write multiple queries
[Link], FOR , IF
[Link], begin, if, v_first_letter := get_first_letter(v_last_name)
///////////////////////////////////////////////////////////////////////////////////
///////////////////////////////////////////////////////////////
1-2
Vocabulary
a. portability
b. block
c. exception
Try It/Solve It
[Link] performance
2. portability
[Link]/sql logic
pl/sql dB most efficient
pl/sql runs on every operating system if you have ORACLE database
[Link]
custom reports
DML
///////////////////////////////////////////////////////////////////////////////////
//////////////////////////////////////////////////////////////
1-3
Vocabulary
a. anonymous blocks
b. function
c. subprograms
d. compiler
e. procedure
Try It/Solve It
1. Declare : variabile, exceptii, cursor
Begin : sql statements, pl/sql statements
exception : apare la runtime , eroare la compile time
2.
a. fail
b. fail
c. fail
d. succes
3. functii si proceduri
[Link]
DBMS_OUTPUT.PUT_LINE('Hello World!');
END
5. DECLARE
calendar date;
BEGIN
SELECT ADD_MONTHS(sysdate,6) INTO calendar
FROM DUAL;
DBMS_OUTPUT.PUT_LINE(calendar);
END
///////////////////////////////////////////////////////////////////////////////////
//////////////////////////////////////////////////////////////////
2-1
Vocabulary
a. variables
b. parameters
Try It/Solve It
[Link]
[Link]
[Link]
2.number_of_copies PLS_INTEGER; --valid
printer_name CONSTANT VARCHAR2(10); --invalid, must have a default value
deliver_to VARCHAR2(10) := Johnson; --invalid, the name must have ''
by_when DATE := SYSDATE+1; -- valid, I tried in sql commands
3.A
[Link] or replace function country (country_n in VARCHAR2)
return INTEGER
is
v_low_elev INTEGER;
v_high_elev integer;
begin
select lowest_elevation, highest_elevation into v_low_elev, v_high_elev
from wf_countries
where country_name = country_n;
dbms_output.put_line(v_high_elev);
return v_low_elev;
end;
begin
dbms_output.put_line(country('United States of America'));
end;
///////////////////////////////////////////////////////////////////////////////////
///////////////////////////////////////////////////////////////////
2-2
Vocabulary
a. literal
b. delimiters
c. reserved words
d. comments
e. lexical units
f. identifiers
Try It/Solve It
1.
Today -- valid
Last name -- invalid, contains a space
today’s_date -- contains invalid symbol " ' "
number_of_days_in_february_this_year -- invalid, contains more than 30 chr
Isleap$year -- valid
#number -- invalid, begins with a symbol
NUMBER# -- valid
Number1to7 -- valid
2.
create yes
make no
table yes
seat no
alter yes
rename yes
row yes
number yes
web no
3.
SELECT -- reserved word
:= -- delimiter
'TEST' -- literal
FALSE -- literal
-- new process -- comment
FROM -- reserved word
/* select the country with the highest elevation */ -- comment
v_test -- identifier
4.09 -- literal
///////////////////////////////////////////////////////////////////////////////////
/////////////////////////////////////////////////////////////////
2-3
Vocabulary
a. nclob
b. lob
c. scalar
d. blob
e. composite
f. bfile
g. references
h. object
i. clob
Try It/Solve It
[Link] data type of an object it's a feature of that object. This feature helps you
to know what kind of operations you can apply on that obj
2.-scalar
-composite
-reference
3.-number
-varchar2
-date
[Link]
[Link] (binary file)
6.
CLOB -- LOB
VARCHAR2 -- Scalar
BLOB -- LOB
NUMBER -- Scalar
BFILE -- LOB
TIMESTAMP -- Scalar
NCLOB -- LOB
RECORD -- Composite
PLS_INTEGER -- Scalar
LONG -- Scalar
TABLE -- Composite
BOOLEAN -- Scalar
7.
a. scalar, varchar2
b. LOB, CLOB
c. scalar, number
d. LOB, BLOB ?
e. scalar, pls_integer
f. scalar, date
g. scalar, varchar2
h. composite, table
i. LOB, BFILE
j. LOB, BLOB ?
k. scalar, boolean
///////////////////////////////////////////////////////////////////////////////////
///////////////////////////////////////////////////////////////
2-4
Vocabulary
a. boolean
b. %type
Try It/Solve It
1.
[Link]
[Link] (encountered the symbol "=" when expecting ":=")
[Link] (the declaration of a constant must contain an initialization
assignement)
[Link]
[Link] should avoid using column names as identifiers or use v_name for variables :
country_name => v_country_name
median_age => v_median age
3.
DECLARE
country_name wf_countries.country_name%TYPE;
median_age wf_countries.median_age%TYPE;
BEGIN
SELECT country_name, median_age INTO country_name, median_age
FROM wf_countries
WHERE country_name = 'Japan';
DBMS_OUTPUT.PUT_LINE('The median age in '|| country_name || ' is '
|| median_age || '.');
END;
[Link] to hard-coding data types, using the %TYPE attribute is better because:
-we can avoid errors caused by data type mismatch or wrong precision
-we don't need to change the variables declaration if the table column definition
changes
By hard-coding the data types we can run into problems like: if we have already
declared some variables for the country_name and median_age columns without using
the %TYPE attribute, then the PL/SQL block can return errors if the table column is
altered.
5.
DECLARE
v_today DATE := SYSDATE;
v_tomorrow v_today%TYPE;
BEGIN
v_tomorrow := SYSDATE+1;
DBMS_OUTPUT.PUT_LINE('Hello World');
DBMS_OUTPUT.PUT_LINE(v_today);
DBMS_OUTPUT.PUT_LINE(v_tomorrow);
END;
///////////////////////////////////////////////////////////////////////////////////
///////////////////////////////////////////////////////////////////////////////////
2-5
Vocabulary
a. explicit conversion
b. implicit conversion
Try It/Solve It
1.
a.579
b.579
c.VARCHAR2 a fost convertit la NUMBER
[Link]
v_name VARCHAR2(50):='Christian';
v_length_name PLS_INTEGER
BEGIN
v_length_name:=LENGTH(v_name);
DBMS_OUTPUT.PUT_LINE(v_length_name);
END;
[Link]
my_date DATE := SYSDATE;
v_last_day DATE;
BEGIN DBMS_OUTPUT.PUT_LINE(TO_CHAR(my_date, 'Month dd, yyyy'));
v_last_day := LAST_DAY(my_date);
DBMS_OUTPUT.PUT_LINE(v_last_day); END;
[Link] my_date DATE := SYSDATE;
new_date DATE;
v_months_between NUMBER;
BEGIN new_date := my_date + 45;
v_months_between := MONTHS_BETWEEN(new_date,my_date);
DBMS_OUTPUT.PUT_LINE(v_months_between);
END;
5.A.11
B.11
[Link] e facuta inainte de adunare deci 3*2 e inainte de a aduna 5.
[Link] pentru ca v_number>30 e falsa si negata.
[Link] pot sa nu se intample mereu asa cum vrem noi daca nu suntem atenti.
Conversiile pot sa afecteze eficienta programului.
///////////////////////////////////////////////////////////////////////////////////
///////////////////////////////////////////////////////////////////////////////////
///////
2-6
Vocabulary
a. Qualifier
b. Variable scope
c. Variable visibility
Try It/Solve It
1.A. 2
B. Western Europe
C. 601
D. Product 10012is in stock
E. new_locn e declarat local, nu e visibil petru Position 2
2.A. Deoarece se vor executa comenzile din blocul interior, numele variabilelor
fiind aceleasi cu cele din blocul exterior
B. Deoarece afisarea se realizeaza in afara blocului interior
C. Folosim eticheta outer. Pentru afisare punem outer.v_employee_id pentru
afisarea valorii din blocul exterior
<<outer>>
DECLARE
v_employee_id employees.employee_id%TYPE;
v_job employees.job_id%TYPE;
BEGIN
SELECT employee_id, job_id INTO v_employee_id, v_job
FROM employees
WHERE employee_id = 100;
DECLARE
v_employee_id employees.employee_id%TYPE;
v_job employees.job_id%TYPE;
BEGIN
SELECT employee_id, job_id INTO v_employee_id, v_job
FROM employees
WHERE employee_id = 103;
DBMS_OUTPUT.PUT_LINE(outer.v_employee_id || ' is a(n) ' || v_job);
END;
DBMS_OUTPUT.PUT_LINE(v_employee_id || ' is a(n) ' || v_job);
END;
///////////////////////////////////////////////////////////////////////////////////
///////////////////////////////////////////////////////////////////////////////
2-7
Try It/Solve It
1. DECLARE
v_country_name VARCHAR2(20);
v_nr number(4);
BEGIN
SELECT country_name INTO v_country_name
FROM wf_countries
WHERE country_id = 421;
v_nr :=1234;
v_nr := v_nr * 2;
DBMS_OUTPUT.PUT_LINE(v_country_name);
End;
///////////////////////////////////////////////////////////////////////////////////
///////////////////////////////////////////////////////////////////////////////////
/
3-1
Vocabulary
a. delete
b. insert
c. merge
d. update
e. ddl
f. dml
Try It/Solve It
1.C
2.B
3. create table grocery_items (product_id number(3,0) not null, brand varchar(255),
description varchar(255))
4.
insert into grocery_items (product_id, brand, description)
values (110, 'Colgate', 'Toothpaste')
insert into grocery_items (product_id, brand, description)
values (111, 'Ivory', 'Soap')
insert into grocery_items (product_id, brand, description)
values (112, 'Heinz', 'Ketchup')
5.
insert into grocery_items (product_id, brand, description)
values (113, 'Pepsi', 'Juice')
6.
update grocery_items
set description='tomato catsup'
where brand='Heinz'
[Link] into grocery_items
values (114 , 'Milka', 'Chocolate')
[Link] grocery_items
set brand='Dove'
where brand='Ivory'
///////////////////////////////////////////////////////////////////////////////////
/////////////////////////////////////////////////////////
3-2
Try It/Solve It
1. not valid
not valid
not valid
valid
not valid
valid
not valid
not valid
valid
2. DECLARE v_max_deptno departments.department_id%TYPE;
BEGIN
SELECT MAX(departmentno_id) INTO v_max_deptno FROM departments;
DBMS_OUTPUT.PUT_LINE (' departamentul este : ' || v_max_deptno);
END
3. DECLARE
v_country_name wf_countries.country_name%TYPE
:= ‘United States of America’;
v_lowest_elevation wf_countries.lowest_elevation%TYPE;
v_highest_elevation wf_countries.highest_elevation%TYPE;
BEGIN
SELECT lowest_elevation, highest_elevation
INTO v_lowest_elevation, v_highest_elevation
FROM wf_countries;
DBMS_OUTPUT.PUT_LINE('The lowest elevation in
'||country_name||' is '||v_lowest_elevation
||’ and the highest elevation is '||
v_highest_elevation||'.');
END;
4. a)Pentru ca exista mai multi agenti cu agelasi id
b) Pentru ca ID_PRAG nu exista
5. eroare : WHERE last_name = last_name
a) CREATE TABLE emp_dup AS
SELECT * FROM employees;
b) SELECT first_name, last_name
FROM emp_dup;
e) drop table emp_dup;
f) DECLARE
V_LAST_NAME VARCHAR2(25) := ‘FAY’;
BEGIN
UPDATE EMP_DUP SET FISRT_NAME = ‘JENNIFER’
WHERE LAST_NAME = V_LAST_NAME;END;
6) CREATE TABLE GROCERY_ITEM(
PRODUCT_ID NUMBER(6,0) PRIMARY KEY,
BRAND VARCHAR2(100),
GROCERY_ITEM VARCHAR(100)
);
INSERT INTO GROCERY ITEM (PRODUCT_ID, BRAND, GROCERY_ITEM) VALUES
(100,’COLGATE’, ‘PASTA DENTARA’);
Da este posibi;
7. CREATE TABLE GRPCERY_ITEM(
GROCERY_ITEM NUMBER(6,0) PRIMARY KEY,
GROCERY_ITEM VARCHAR2(100),
GROCERY_ITEM VARCHAR(100)
);
Nu este posibil
///////////////////////////////////////////////////////////////////////////////////
//////////////////////////////////////////////////////////////////////////////
3-3
Vocabulary
a. implicit cursors
b. explicit cursors
c. merge
d. insert
e. delete
f. update
Try It/Solve It
1)True
2)cursor attributes, implicit
3)190
4)
DECLARE
v_max_deptno new_depts.department_id%TYPE;
v_dept_name new_depts.department_name%TYPE := 'A New Department';
v_dept_id new_depts.department_id%TYPE;
BEGIN
SELECT MAX(department_id) INTO v_max_deptno
FROM new_depts;
DBMS_OUTPUT.PUT_LINE('The maximum department id is: ' || v_max_deptno);
END;
5)
DECLARE
v_max_deptno new_depts.department_id%TYPE;
v_dept_name new_depts.department_name%TYPE := 'A New Department';
v_dept_id new_depts.department_id%TYPE;
BEGIN
SELECT MAX(department_id) INTO v_max_deptno
FROM new_depts;
v_dept_id :=v_max_deptno + 10;
DBMS_OUTPUT.PUT_LINE('The maximum department id is: ' || v_max_deptno);
DBMS_OUTPUT.PUT_LINE('v_dept_id:' || v_dept_id);
END;
6)
DECLARE
v_max_deptno new_depts.department_id%TYPE;
v_dept_name new_depts.department_name%TYPE := 'A New Department';
v_dept_id new_depts.department_id%TYPE;
BEGIN
SELECT MAX(department_id) INTO v_max_deptno
FROM new_depts;
v_dept_id :=v_max_deptno + 10;
INSERT INTO new_depts(department_id, department_name, location_id, manager_id)
VALUES (v_dept_id, v_dept_name, NULL, NULL)
END;
7)
DECLARE
v_max_deptno new_depts.department_id%TYPE;
v_dept_name new_depts.department_name%TYPE := 'A New Department';
v_dept_id new_depts.department_id%TYPE;
BEGIN
SELECT MAX(department_id) INTO v_max_deptno
FROM new_depts;
v_dept_id :=v_max_deptno + 10;
INSERT INTO new_depts(department_id, department_name, location_id, manager_id)
VALUES (v_dept_id, v_dept_name, NULL, NULL);
DMBS_OUTPUT.PUT_LINE(SQL%ROWCOUNT);
END;
8)
DECLARE
v_max_deptno new_depts.department_id%TYPE;
v_dept_name new_depts.department_name%TYPE := 'A New Department';
v_dept_id new_depts.department_id%TYPE;
BEGIN
SELECT MAX(department_id) INTO v_max_deptno
FROM new_depts;
v_dept_id :=v_max_deptno + 10;
UPDATE new_depts SET location_id=1400
WHERE location_id=1700;
DMBS_OUTPUT.PUT_LINE(SQL%ROWCOUNT);
END;
///////////////////////////////////////////////////////////////////////////////////
///////////////////////////////////////////////////////////////////////////////////
/////////////////
3-4
Vocabulary
a. transaction
b. rollback
c. savepoint
d. end
e. commit
Try It/Solve It
1.
Attribute - Column
Entity - Table
ER Model - Physical design
Instance - Row
Primary UID - Primary key
Relationship - Foreign key
Secondary UID - Unique key
2.
[Link] key
[Link] key
[Link] key
[Link]
[Link]
3.
[Link]
[Link]
[Link]
4.
Title - song
description - song,event
venue - event
first name - customer
phone number - customer
release date - song
last name - customer
type - song,event
email adress - customer
Exercise 2:
1.
tab+randuri:
Parent information: parent1 first name, parent1 last name,parent2 firstname,
parent2 lastname,id
Student: id, firstname,lastname,registration year, email
student attendance: number of working days, number of days off, eligibility for
exam
academic session: id, name
course: id, name
student course detail: grade
department: id,name,head
online:logon id, password
seated: building,room,date/time
exam result:grade
exam:id, start date
exam type: type,name,description
faculty course detail: contact hours
faculty login detail: login date time
faculty: id, first name,last name, email, salary, insurance, hourly rate
keys:id,logonID,Building,Room,Date/Time,Type