Class 13.
Section 3.10.
Insert, Update, and Delete statements perform data modifications to existing tables. Called as a group the "update statements". Need to tell difference between an "update statement" and the Update statement.
Need update privilege on a table to update it (look ahead to Section 7.3 to see privileges). You always have all privileges if you created the table.
3.10.1 The Insert statement. Basic SQL Standard format is this:
insert into tablename [(column {, column})]
[values (expression {expression})] | [subquery];
One of the two forms must be used. Columns named and expressions must match in number and type; any table column left out will insert as null.
Ex 3.10.1.
insert into orders (ordno, month, cid, aid, pid)
values('1107', 'aug', 'c006', 'a04', 'p01');
Notice, no qty or dollars, so on this new row they are null.
But if we are inserting ALL the columns in a table, can leave out column list (note list is optional).
See Example 3.10.2 for other form. I can create new table swcusts, like customers, then write:
insert into swcusts select * from customers where
city in ('Dallas', 'Austin');
I can insert a LOT at ONCE (set oriented insert), can build up arbitrarily from existing tables using select. This is an important capability!
3.10.2. The Update statement. Basic SQL Standard format is this.
update tablename [corr_name]
set column = (expression|null|(subquery))
{, column = (expression|null|(subquery)) . . .}
[where search_condition];
The search_condition in the Where clause determines rows in the updated table to change. The expressions used can reference only column values on the specific row of the table currently being updated.
update agents set percent = 1.1*percent where city = 'New York';
Note we can reference other tables in our Standard SQL to specify what values to put in place (as we could with Insert using Subquery).
update swcusts set discnt = (select discnt from customers c
where c.cid = swcusts.cid)
where city = 'Texas';
This Update statement is legal in Oracle and DB2 UDB, for example.
3.10.3. Delete statement.
delete from tablename [corr_name]
[where search_condition];
E.g., Fire all agents in New York.
delete from agents where city = 'New York';
Basic SQL allows the search condition to reference other tables.
Ex. Delete all agents with total orders less than $600.00
delete from agents where aid in (select aid from orders group by
aid having sum(dollars) < 600);
We’ll cover part of this: Power SQL!
Problem 3.18 a, solved on pg. 791, does it by creating a helper table called buckets with rows of one column 0, 1, 2 (no order dollars exceeds 1500)
|
bucket |
|
0 |
|
1 |
|
2 |
Then a join with orders with join condition floor(o.dollars/500) = b.bucket and group by bucket categorizes all the rows as desired. Neat trick.
Example 3.11.6 Create table called employees (see pg. 159 of text).
create table employees (eid char(4) not null, ename varchar(16),
mgrid char(4));
Table is on pg. 159 of text. Draw tree. Want to select everyone below some node, e.g. employee with eid = 'e001', in tree.
select e.eid from employees e where e.mgrid chain
goes up to 'e001';
Can't, can only retrieve employees one level down:
select e.eid from employees where e.mgrid = 'e001';
or two levels,
select e.eid from employees where e.mgrid in
(select f.eid from employees f where f.mgrid = 'e001');
or . . .. Can't leave number of levels flexible. In program, of course, can walk the tree. This was a classical problem, called "Transitive Closure" that couldn't be done in SQL 92, but we have a way to do this in SQL-99 with recursive queries of a new type. See http://pragmaticdata.com/content/view/26/53/
For a SQL Server example, see http://www.sqlmag.com/Files/09/46117
Oracle has long had this capability, but its way was not standardized. For an interesting article on the Oracle capability, see Phil Greenspun’s SQL for Web Nerds, specifically the chapter on Trees.
In Oracle, Create employees table as on page 159. Perform a depth first search of reports in tree starting with the President, Jacqueline:
select ename, eid, mgrid from employees
start with eid = 'e001'
connect by prior eid = mgrid;
Added to next homework: Try this on Oracle. See rows in DFS order.
Starting now with Chapter 5, ESQL, Embedded SQL Programming. Return later to Chapter 4 and talk about PL/SQL, Procedural Language SQL. Also doing Chap. 5 in Java JDBC.
HW 4. Part 1, Exercises 5.1, 5.3, due first, but after midterm.
Chapter 5. Programming using SQL: Embedded SQL, means SQL statements embedded in host language (C in our case). Java uses a library, not really “embedded” but similar.
The original idea was for end-users to access a database through SQL. Called casual users.
But this is not a good idea. It takes too much concentration. Can you picture airline reservation clerks doing their job with SQL? Customers waiting. Booking flight (update of seat table). Billing?
- Need to know all tables & columns, create complex syntax
- too much risk of mistakes, especially with updates
Instead, we have an Application Programmers create menu applications, perform selects and updates programmatically.
Programmers can spend a lot of time making sure the right SQL statement is used; programmers are temperamentally suited to this kind of work.
Of course it's nice for programmers to be able to use Ad-Hoc SQL to check quickly that they get data desired before putting SQL in program.
Aim for this chapter is to be able to implement ANY CONCEIVABLE ALGORITHM using a C program that can get at data through SQL statements.
Later move to PL/SQL-- Procedural Language SQL
5.1 SQL statements in C (or any general host language, like Java) have slightly different syntax than the SQL we've seen so far. For example:
exec sql select count(*) into :host_var from customers; ESQL/C
(This works only for Scalar Query)
Statement starts with exec sql phrase. Variable (host_var) meant to hold retrieved data, so need new Into clause; colon shows DBMS this is program variable (not a SQL variable).
C compiler doesn't recognize SQL statements. The "exec sql" phrase makes the SQL precompiler turn SQL into C calls to appropriate DBMS Library fns.
(Already have precompiler acting first when give gcc command with line prefix like #include, #define; exec sql uses a different prefix: pgm.pc).
In Oracle, start with pgm.pc (meaning has embedded SQL statements: exec sql . . .). You will be given a makefile for hw4 that will perform a series of precompilations, compilations, and linkage.
Fall 09: makefile and example code is in $cs630/esql, linked from class web page.
You can do everything in homework 4 using the makefile provided. Just create your own named pgm.pc ('pgm' can be any name you want) and type:
make E=pgm (NO spaces on either side of "="; pgm.pc must exist)
If your program can be compiled and linked, you will end up with 'pgm.ox' which is your executable runfile. (Type 'pgm.ox' on a UNIX line to run it.)
(This makefile is advanced stuff that uses the ProC command and library, so you probably shouldn't expect to understand it in detail. I am assuming everyone understands C. See UNIX Guide pointed to from course Web Page.)
In Java, we don’t need a precompiler. JDBC is an ordinary Java library, and its API is part of the standard Java library. However, each database supplier, like Oracle, has to implement the API for its own database, and that implementation is called a “JDBC driver”. Oracle’s 10g JDBC driver is supplied in ojdbc14.jar. To execute a JDBC program, we will need to make the driver available on the program’s classpath. This is set up by the supplied JDBC makefile in $cs630/jdbc and linked from the class web page.
make E=Ex511 compiles and runs Ex511.java
Example 5.1.1. pg 266-7. A simple program. To begin, need to write:
#include <stdio.h> (standard UNIX I/O header for printf, etc.)
#include "prompt.h" (our prompt.h header must be in directory)
exec sql include sqlca (memory struct SQLCA for msgs/errors from SQL)
Declare section. (pg 267) We must declare C variables so types are known to ORACLE (which may need to perform data conversion). Here is how we set a variable used in a search condition and select into two others.
char cust_id[ ] = "c001"; /* or prompt user for cust_id */
After Declare Section:
exec sql select cname, discnt into :cust_name, :cust_discnt
from customers where cid = :cust_id;
Note use of colon for variables in select statement Where clause so (dumb) SQL Precompiler can tell :cust_id isn't a constant or column name.
At runtime, cust_name and cust_discnt will be filled in with values "TipTop" and 10.00. Could now write in program:
printf("Customer name is %s, and discount is %5.1f\n",
cust_name, cust_discnt);
Note don't use colons here (used only in exec sql select statement above as hint to precompiler that the names are C program variables). In order to use C variables in SQL, must declare them in the Declare Section (which serves both for SQL and the C language declaration).
exec sql begin declare section;
char cust_id[5], cust_name[14], user_name[20], user_pwd[20];
float cust_discnt;
exec sql end declare section;
The character arrays need to contain enough characters to hold string values of columns PLUS ONE for terminal '\0' character. E.g., cname was declared as varchar(13) on pg 84.
In C when we specify a string such as "TipTop" (note double quotes), this includes a terminal ASCII null (zero) value ('\0') at the end: this serves as a signal to functions such as printf that the string has ended.
Conversion can also occur if you declare
int cust_discnt;
in Declare section. Then float value 10.00 will become 10, but 10.50 will also become 10; we will lose any fractional significance.
SQL connect and disconnect. As in interactive: sqlplus poneil to attach,
strcpy(username, "Scott"); /* set up username */
strcpy(password, "Tiger"); /* & password for Oracle login */
exec sql connect :username identified by :password;
This is what is used in ORACLE (Note: can't use literal names, must be in character string.)
The command to disconnect is:
exec sql disconnect;
or sometimes
exec sql commit release; (if committing at the same time; see below)
Now look at Example 5.1.1, Figure 5.1, pg 266-67. Task is to prompt repeatedly for customer cid and print out the customer name and discnt. Halt when user inputs a null line (just hits CR).
#include <stdio.h>
#include "prompt.h"
exec sql include sqlca;
char cid_prompt[ ] = "Please enter customer id: ";
Then start main( ) function. Follow with Declare section. Then:
whenever sqlerror goto report_error; /* go handle SQL error */
whenever not found go to notfound; /* empty retrieval, etc. */
Then Connect, Loop & prompt, select, commit, print. Commit releases locks. Loop is while prompt( ) = 0, return of 0 means line with appropriate tokens returned; <0 means failure. When user types CR, end loop.
Use C function strcpy( ) to copy into user_name array, for example.
char user_name[20]; (In declare section)
. . .
strcpy(user_name, "poneilsql");
strcpy will also copy the terminal '\0' of the string "c001".
See prompt function, Figure B.2, pg. 738. (Explained on page 269, used on page 267, Fig. 5.1 and 269, Fig 5.13). Expected to use in homework (made available on-line). Put out prompt[ ] string to terminal, return appropriate tokens. Don't need to understand logic, but it wouldn't hurt.
OK, up to now, retrieved only a single row into a variable:
exec sql select cname, discnt into :cust_name, :cust_discnt
from customers where cid = :cust_id;
We also looked at the handed out Ex511.java, available (in slightly corrected form) in $cs630/jdbc.