Ch-5 SQL 高级

Connection conn = DriverManager.getConnection(
    "jdbc:oracle:thin:@db.example.edu:2000:univdb",
    userid,
    passwd
);

Statement stmt = conn.createStatement();
ResultSet rset = stmt.executeQuery(
    "select dept_name, avg(salary) from instructor group by dept_name"
);

while (rset.next()) {
    System.out.println(rset.getString("dept_name") + " " + rset.getFloat(2));
}

rset.close();
stmt.close();
conn.close();

int a = rset.getInt("a");
if (rset.wasNull()) {
    System.out.println("Got null value");
}

conn.setAutoCommit(false);

try {
    // multiple updates
    conn.commit();
} catch (SQLException e) {
    conn.rollback();
}

PreparedStatement pStmt = conn.prepareStatement(
    "insert into instructor values (?, ?, ?, ?)"
);

pStmt.setString(1, "88877");
pStmt.setString(2, "Perry");
pStmt.setString(3, "Finance");
pStmt.setInt(4, 125000);
pStmt.executeUpdate();

pStmt.setString(1, "88878");
pStmt.executeUpdate();

select *
from instructor
where name = 'X' or 'Y' = 'Y';

ResultSetMetaData rsmd = rset.getMetaData();

for (int i = 1; i <= rsmd.getColumnCount(); i++) {
    System.out.println(rsmd.getColumnName(i));
    System.out.println(rsmd.getColumnTypeName(i));
}

DatabaseMetaData dbmd = conn.getMetaData();
ResultSet rs = dbmd.getColumns(null, "univdb", "department", "%");

SQLAllocEnv(&env);
SQLAllocConnect(env, &conn);
SQLConnect(conn, "db.example.edu", SQL_NTS, "user", SQL_NTS, "pass", SQL_NTS);

SQLAllocStmt(conn, &stmt);
SQLExecDirect(stmt, sqlquery, SQL_NTS);
SQLBindCol(stmt, 1, SQL_C_CHAR, deptname, 80, &lenOut1);
SQLBindCol(stmt, 2, SQL_C_FLOAT, &salary, 0, &lenOut2);

while (SQLFetch(stmt) == SQL_SUCCESS) {
    printf("%s %g\n", deptname, salary);
}

EXEC SQL BEGIN DECLARE SECTION;
    int credit_amount;
    char si[6];
    char sn[30];
EXEC SQL END DECLARE SECTION;

EXEC SQL declare c cursor for
    select ID, name
    from student
    where tot_cred > :credit_amount;

EXEC SQL open c;
EXEC SQL fetch c into :si, :sn;
EXEC SQL close c;

update instructor
set salary = salary + 1000
where current of c;

create function dept_count(dept_name varchar(20))
returns integer
begin
    declare d_count integer;

    select count(*)
    into d_count
    from instructor
    where instructor.dept_name = dept_count.dept_name;

    return d_count;
end;

select dept_name, budget
from department
where dept_count(dept_name) > 12;

create procedure dept_count_proc(
    in dept_name varchar(20),
    out d_count integer
)
begin
    select count(*)
    into d_count
    from instructor
    where instructor.dept_name = dept_count_proc.dept_name;
end;

call dept_count_proc('Physics', d_count);

create function instructors_of(dept_name char(20))
returns table (
    ID varchar(5),
    name varchar(20),
    dept_name varchar(20),
    salary numeric(8,2)
)
return table (
    select ID, name, dept_name, salary
    from instructor
    where instructor.dept_name = instructors_of.dept_name
);

select *
from table(instructors_of('Music'));

declare n integer default 0;

while n < 10 do
    set n = n + 1;
end while;

repeat
    set n = n - 1;
until n = 0
end repeat;

declare n integer default 0;

for r as
    select budget
    from department
    where dept_name = 'Music'
do
    set n = n - r.budget;
end for;

create procedure dept_count_proc(
    in dept_name varchar(20),
    out count integer
)
language C
external name '/usr/app/bin/dept_count_proc';

create trigger account_trigger
after update of balance on account
referencing new row as nrow
referencing old row as orow
for each row
when nrow.balance - orow.balance >= 200000
  or orow.balance - nrow.balance >= 50000
begin atomic
    insert into account_log
    values (nrow.account_number, nrow.balance - orow.balance, current_time);
end;

create trigger timeslot_check1
after insert on section
referencing new row as nrow
for each row
when nrow.time_slot_id not in (
    select time_slot_id
    from time_slot
)
begin atomic
    rollback;
end;

create trigger timeslot_check2
after delete on time_slot
referencing old row as orow
for each row
when orow.time_slot_id not in (
        select time_slot_id
        from time_slot
    )
  and orow.time_slot_id in (
        select time_slot_id
        from section
    )
begin atomic
    rollback;
end;

create trigger setnull_trigger
before update of grade on takes
referencing new row as nrow
for each row
when nrow.grade = ' '
begin atomic
    set nrow.grade = null;
end;

create trigger credits_earned
after update of grade on takes
referencing new row as nrow
referencing old row as orow
for each row
when nrow.grade <> 'F'
  and nrow.grade is not null
  and (orow.grade = 'F' or orow.grade is null)
begin atomic
    update student
    set tot_cred = tot_cred + (
        select credits
        from course
        where course.course_id = nrow.course_id
    )
    where student.ID = nrow.ID;
end;

create trigger grade_trigger
after update of grade on takes
referencing new table as new_table
for each statement
when exists (
    select avg(grade)
    from new_table
    group by course_id, sec_id, semester, year
    having avg(grade) < 60
)
begin atomic
    rollback;
end;

with recursive rec_prereq(course_id, prereq_id) as (
    select course_id, prereq_id
    from prereq

    union

    select rec_prereq.course_id, prereq.prereq_id
    from rec_prereq, prereq
    where rec_prereq.prereq_id = prereq.course_id
)
select *
from rec_prereq;

with recursive empl(employee_name, manager_name) as (
    select employee_name, manager_name
    from manager

    union

    select manager.employee_name, empl.manager_name
    from manager, empl
    where manager.manager_name = empl.employee_name
)
select *
from empl;

select ID,
       rank() over (order by GPA desc) as s_rank
from student_grades
order by s_rank;

select ID,
       dept_name,
       rank() over (
           partition by dept_name
           order by GPA desc
       ) as dept_rank
from dept_grades
order by dept_name, dept_rank;

select ID,
       ntile(4) over (order by GPA desc) as quartile
from student_grades;

select date,
       sum(value) over (
           order by date
           rows between 1 preceding and 1 following
       ) as moving_sum
from sales;

select account_number,
       date_time,
       sum(value) over (
           partition by account_number
           order by date_time
           rows unbounded preceding
       ) as balance
from transaction
order by account_number, date_time;

select item_name, color, size, sum(number)
from sales
group by cube(item_name, color, size);

select item_name,
       color,
       size,
       sum(number),
       grouping(item_name) as item_name_flag,
       grouping(color) as color_flag,
       grouping(size) as size_flag
from sales
group by cube(item_name, color, size);

select item_name, color, size, sum(number)
from sales
group by rollup(item_name, color, size);

select category, item_name, sum(number)
from sales, itemcategory
where sales.item_name = itemcategory.item_name
group by rollup(category, item_name);

select item_name, color, size, sum(number)
from sales
group by rollup(item_name), rollup(color, size);

merge into account as A
using (
    select *
    from funds_received
) as F
on A.account_number = F.account_number
when matched then
    update set balance = balance + F.amount;

merge into account as A
using funds_received as F
on A.account_number = F.account_number
when matched then
    update set balance = balance + F.amount
when not matched then
    insert (account_number, balance)
    values (F.account_number, F.amount);