|
21 Days:
Wk1
. Wk1Ref
. Applts
. Swing
. Grph
. D13-14
. D15-21
. D22-28
cc12: Miscl . SQL . EJB . Vocab . (NotesHelp) . J2EE . Web misl: 21 Days(src) . Dictionary(foldoc) . Unicode . Tables . Print . html misl: F.Allimant . Swing (J.H.University) |
Notes.
(New Notes at TOP of this Section) -----Apr/04/2005 MON ===QUESTION TO PRADIP - Q: In the following example code... String query = "SELECT COFFEES.COF_NAME FROM COFFEES, SUPPLIERS " + "WHERE SUPPLIERS.SUP_NAME LIKE 'Acme, Inc.' " + "and SUPPLIERS.SUP_ID = COFFEES.SUP_ID"; ... Is use of LIKE here (with no wildcard characters) the same as using an '=' equals comparison? - A: Yes, if you don+t supply any wildcard it is the same as +=+ expression. -----Sep/13/2004 MON - Sep/17/2004 FRI ===SQL in a Nutshell - "There are three common Binding styles: (p.7.middle) 1) SQL Module Language 2) Enabled SQL Syntax 3) Direct SQL Invocation - These varry in when the SQL command is put together, and when it is run. - Finished Ch1. History of SQL. - Half of Ch2. Foundational Concepts. - SQL's syntax is made of three things: KEYWORDS, IDENTIFIERS, and LITERALS. - 2nd Half of [ ]:Ch2. Foundational Concepts. ===SQL in 10 Minutes - Finished Ch.1,2,3,4,5,6,7,8. - Scalability of an application mentioned. (Ch.4.p.27) - Functions are often not portable across different SQL implementations. (Ch.8.pg.57) - Comment your SQL code so it is clear exacly which SQL your code was written for. (Ch.8.pg.58) ===(Java: Reviewed Day18 notes. And updated some notes.) ===RECEIVED BOOK Sep/13: Teach Yourself SQL in 10 Minutes. ===EXAMPLE from one of the Licsenceing reports. SELECT LICENSEMST.SERIALNO ,LICENSEMST.STARTDT ,LICENSEMST.ENDDT ,LICENSEMST.CONCURRENTUSERS ,LICENSEMST.LICENSEKEY ,ITEMMST.DESCRIPTION ,CUSTMST.NAME ,CUSTADDR.ADDRLINE1 ,CUSTADDR.ADDRLINE2 ,CUSTADDR.ADDRLINE3 ,CUSTADDR.CITY ,CUSTADDR.STATE ,CUSTADDR.ZIP ,ITEMMST.ID ,CUSTMST.ID ,LICENSEDTL.CREATEDATE FROM LICENSEMST ,ITEMMST , CUSTMST, CUSTADDR, LICENSEDTL WHERE LICENSEMST.ID = LICENSEDTL.LICENSEID AND ITEMMST.ID = LICENSEDTL.ITEMID AND CUSTMST.ID = LICENSEMST.CUSTID AND CUSTADDR.PARENTID = CUSTMST.ID **** Older Notes **** -----Jan/23/2004 FRI (from Vinod) ===Strings and char are the same. And they are enclosed with single quotes. -----Jan/22/2004 THU: SQL> create table addr ( name VARCHAR2(30), city VARCHAR2(30), state VARCHAR2(10), distance number(10,2) ) / SQL> insert into addr values ( 'Steve Brown', 'Lancaster', 'PA', 1.2 ) / SQL> commit; // Saves the data -----Jan/21/2004 WED: ===Three Major Characteristics of each Table: 1) columns, 2) rows, and 3) table name. -----Jan/21/2004 WED: Oracle is an example of an Relational Data-Base Management System (RDBMS). It does three basic things: 1) IN: Put data into it. 2) KEEP: Hold the data. 3) OUT: View and/or work with the data. ===Create an Oracle Data-Base. SQL> create table dgrad1 ===Command Files. - Put oracle commands in a file, such as: K:\TReXDev\07.60_20031003\rndProjects\Licensing\src\dave\test2004Jan21.sql SQL> @K:\TReXDev\07.60_20031003\rndProjects\Licensing\src\dave\test2004Jan21 -----Jan/20/2004 TUE: Starting to practiccccce queries from sql prompt.
(New Notes at BOTTOM of this Section) -----Mar/09/2005 WED ===From: JDBC Tutorial - Setting Up Tables - There are, "generic SQL types (also called JDBC types) that are defined in the class java.sql.Types ." (link) - "For a SELECT statement, the method to use is executeQuery(). For statements that create or modify tables, the method to use is executeUpdate()." - Statement statemt = con.createStatement(); // con is an instance of Conection. - Statements that update a table, create a table, alter a table, or drop a table are executed with the method executeUpdate(). - The executeQuery() method is used to execute SELECT statements. - When nesting quotation marks, alternate double and single quotes. - For multiple statement executions, we can reuse a Statement object. ===From: JDBC Tutorial - Retrieving Values from Result Sets - ResultSet rs = stmt.executeQuery("SELECT COF_NAME, PRICE FROM COFFEES"); - "Since the cursor is initially positioned just above the first row of a ResultSet object, the first call to the method next() moves the cursor to the first row and makes it the current row." - "Note that with the JDBC 2.0 API, covered in the next section, you can move the cursor backwards", and other places. - while (rs.next()) { String s = rs.getString("VARNAME1"); float n = rs.getFloat("VARNAME2"); System.out.println(s + " " + n); } - "The second way is to give the column index (number of the column), with 1 signifying the first column ... ." - "JDBC allows a lot of latitude as far as which getXXX methods you can use to retrieve the different SQL types." But this does not work for all combinations. Between numeric and string seem to be okay. getString() can retrieve "any of the basic SQL types". - An "x" indicates that the getXXX method may legally be used to retrieve the given JDBC type. An "X" indicates recomended calls. getXXX table -----Apr/04/2005 MON ===Using Joins ===Using Transactions - "A transaction is a set of one or more statements that are executed together as a unit, so either all of the statements are executed, or none of the statements is executed." = "Using Transactions to Preserve Data Integrity" (Apr/12) = "When to Call the Method rollback" (Apr/13) ===Stored Procedures ===SQL Statements for Creating a Stored Procedure (Apr/15) - Q: Once a "Stored Procedure" is created, does it stay in the DBMS for latter use (perhaps until deleted by a user)? Or is it available only for the ap that created it? A: "The procedure ... will be compiled and stored in the database as a database object that can be called, similar to the way you would call a method." - Q: Same question lingers on. - Statement stmt = con.createStatement(); stmt.executeUpdate(createProcedure); // param is string containing SQL procedure. CallableStatement cs = con.prepareCall("{call SHOW_SUPPLIERS}"); ResultSet rs = cs.executeQuery(); // In the case, "where there are multiple results, the method execute() should be used // to execute the CallableStatement." (used rarely) - CallableStatement is a subclass of the PreparedStatement interface. ===Creating Complete JDBC Applications - import java.sql.*; // (the JDBC API) - Note the wording: "If a class is to be executed, it must contain a static public main method." ==="Retrieving Warnings" sample code (Jun/01/2005) - java.sql.SQLWarning objects are a subclass of java.sql.SQLException. - "If getWarnings returns a warning, you can call the SQLWarning method getNextWarning on it to get any additional warnings. Executing a statement automatically clears the warnings from a previous statement, so they do not build up." - Warnings are uncommon. By far the most common warning is a DataTruncation warning, a subclass of SQLWarning." ===Running the Sample Applications -----Lesson: New Features in the JDBC 2.0 API
===[*]Jul/05/2005: Getting Set Up to Use the JDBC 2.0 API
===[*]Jul/22/2005: Moving the Cursor in Scrollable Result Sets
===[*]Jul/22/2005: Making Updates to Updatable Result Sets
- "Another new feature in the JDBC 2.0 API is the ability to update rows in a result set
using methods in the Java programming language rather than having to send an SQL command.
... you need to create a ResultSet object that is updatable (ResultSet.CONCUR_UPDATABLE)."
===[*]Jul/25/2005: Updating a Result Set Programmatically
- uprs.last();
uprs.updateFloat("PRICE", 10.99f);
uprs.updateRow();
- "If you had moved the cursor to a different row before calling the method updateRow,
the update would have been lost."
===[*]Jul/25/2005: Inserting and Deleting Rows Programmatically
- "Your first step will be to move the cursor to the insert row", with the moveToInsertRow
method. "The next step is to set a value for each column in the row (same as usual).
"Finally, you call the method insertRow. ... This one method simultaneously inserts the
row into both the ResultSet object and the database table from which the result set was
selected."
===[ ]: Code Sample for Inserting a Row
===[ ]: Deleting a Row
===[ ]: Making Batch Updates
===[ ]: Using SQL3 Datatypes
===[ ]: Standard Extension Features
Commands. - Sample SQL commands.
(New Notes at TOP of this Section)
-----Mar/24/2005 THU
===NOTE FOR SQL. HOW TO LIST ALL FIELDS IN A TABLE.
SQL> SELECT column_name,data_type from user_tab_columns where table_name='WORKTICKET';
SQL> SELECT column_name from user_tab_columns where table_name='WORKTICKET';
===NOTE FOR SQL. HOW TO LIST ALL TABLES.
SQL> SELECT * FROM TAB;
Terminology. - SQL and related terms.
- metadata - data about the database. True relational databases contain this.
- relational database -
- schema - the set of information that describes a table.
- SQL - Structured Query Language - a language to comunicate with databases.
- table - a database file for storage of a structured list of data.
SQL Links.