SQL Explain basic SQL concepts Demonstrate SQL commands

SQL ▮ Explain basic SQL concepts ▮ Demonstrate SQL commands ▮ Results Sets Appendix B © copyright Janson Industries 2011 1

What is SQL? ▮ Standard relational data base data definition and manipulation language ▮ Supported by most relational database management systems (RDBMS) ▮ Has its own terminology but provides standard relational functions Appendix B © copyright Janson Industries 2011 2

SQL objects Tables - hold all stored data Views - provide an alternative look at data stored in tables (I. e. project and select functions) Index - provides an alternative access to table data (I. e. keys) Schema - directory of all SQL objects. Also contains SQL required information (e. g. field/table/view/index cross reference, data definition, data dictionary, table update journal, etc. ) Appendix B © copyright Janson Industries 2011 3

SQL commands ▮ Create XXXX allows you to create SQL objects: ▮ CREATE SCHEMA ▮ CREATE TABLE ▮ CREATE VIEW ▮ CREATE INDEX Appendix B © copyright Janson Industries 2011 4

Collections/Schemas ▮ Creating a schema may take some time ▮ A series of objects (indices, journal receivers) are created within the schema ▮ Also, you usually need a high level of authority to create a schema ▮ The schema name is one half of a fully qualified SQL table name ▮ YOURSCH 01/INV or YOURSCH 01. INV Appendix B © copyright Janson Industries 2011 5

Tables ▮ When creating a table you must define fields ▮ CREATE TABLE MYSCH 01/INV (ITEMNAM CHAR(10), ITEMNUM CHAR(6), PRICE DEC(8, 2), DESCR CHAR(25)) ▮ At least a single space between commands, keywords, and field names Appendix B © copyright Janson Industries 2011 6

Loading data ▮ Insert Into table name Values (‘char data’, numeric data, ‘char data’, . . . ) ▮ INSERT INTO MYSCH 01/INV VALUES (‘Hammer’, ‘ 111111 ’, 14. 95, ‘ 12 inch claw’) Appendix B © copyright Janson Industries 2011 7

Viewing data ▮ Select field names From table name Where select condition ▮ SELECT ITEMNAM, PRICE FROM MYSCH 01/INV WHERE PRICE >. 99 ▮ Returns those rows that satisfy the WHERE condition ▮ Select very powerful and can be used to enhance other commands Appendix B © copyright Janson Industries 2011 8

Subselects ▮ INSERT INTO MYSCH 01/INV SELECT * FROM MYLIB 01/INV ▮ Asterisk means all (in this case all fields) ▮ No WHERE clause defaults to all records ▮ Copies all records and fields from a table in one schema to a table in another schema Appendix B © copyright Janson Industries 2011 9

Modifying data ▮ Delete From table Where condition, allows you to delete records from a table ▮ Drop Table deletes the table and all data contained within ▮ Update allows you to modify field values Appendix B © copyright Janson Industries 2011 10

Update ▮ UPDATE MYSCH 01/INV SET PRICE = 17. 95 WHERE ITEMNAM = ‘Hammer’ ▮ Changes all Hammer records ▮ Update is a very powerful command so: Appendix B © copyright Janson Industries 2011 11

Views ▮ CREATE VIEW MYSCH 01/CHEAP AS SELECT ITEMNAM, PRICE FROM MYSCH 01/INV WHERE PRICE <. 99 ▮ SELECT * FROM MYSCH 01/CHEAP ▮ Shows item records that have a price less than 99 cents Appendix B © copyright Janson Industries 2011 12

Indices ▮ CREATE INDEX MYSCH 01/INVPRC ON MYSCH 01/INV (PRICE) ▮ SELECT * FROM MYSCH 01/INVPRC ▮ Shows item records in price order ▮ Using indices speeds up retrievals Appendix B © copyright Janson Industries 2011 13

Managing SQL objects Appendix B DROP SCHEMA or COLLECTION DROP VIEW DROP INDEX ©

Special SQL commands ▮ Small problem with SQL and Java (and most programming languages): ▮ SQL returns more then one record/row at a time (called a result set) ▮ Most PL’s work on one record at a time Uh–Oh ! ▮ Java solution: cursors and. next() method (see DB Access chapter for more info) Appendix B © copyright Janson Industries 2011 15