Intro to SQL

1. Intro to SQL CS-422 Dick Steflik

2. Structured Query Language • Developed by C. Date for Relational Data Base Management Systems (RDBMS) • Simple Declarative Language • has no program control statements

3. SQL • Two categories of commands • Data Manipulation Commands • deal with: • RETRIEVING DATA • MAINTAINING DATA • ADDING, UPDATING, DELETING • Data Definition Commands • DEAL WITH: • CREATING DATABASE OBJECTS (TABLES,VIEWS) • Object organization and attributes

4. Data Model • Database objects are modeled on the concept of a table (i.e. rows and columns) • Each row is a single table object • Columns are the attributes of that table • A database is a collection of related tables

5. Referential Integrity • Rules to insure that table data stays accurate and accessible • rows in a table should be unique • one column should contain no duplicate data • primary key • column values cannot contain repeating groups or arrays • null is different than space and zero, 2 null values are not considered equal

6. Data Manipulation Commands • Select - query and display data from a database • Insert - a new row into a table • Update - modify a column in a table • Delete a row from a table

7. Select • Select (column list) from (sources) where (conditions) order by (ocolumn list) • column list - comma separated list of names of columns to be in output • Ex. ssn , lastname , firstname,gpa • can contain literals to be included in output • Ex. Ssn,”~”,lastname,”~”,firstname • sources - name(s) of table(s) to retrieve data from • conditions (optional) - conditions for selections • lastname like “S%” • (lastname = “Steflik) and (firstname like “R%”) • ocolumn list (optional)- list of columns that output should be ordered by • Ex • select * from student • select lastname,fristname from student where gpa > 3.0 • select lastname,firstname from student order by lastname,firstname

8. Select - joined tables • Two tables may be joined and viewed as a single data source is the both have a common column • suppose we have 2 tables: Inventory and category and each has a column called catg_code • In category catg_code is unique and is the primary key

9. Select - joined tables (cont.) join Inventory Category P_no Catg_code qty Catg_code descr

10. Select - joined tables (cont.) • To retrieve all of the part numbers and the name of the category to which the part belongs: • select inventory.p_no category.descr from inventroy , category • select a.p_no,” “,b.descr from a inventory , b category

11. Insert • Add a row to a table • insert into category values(“IGN”,”Ignition System”) • use jdbc execute Update method • ex. Stmt.executeUpdate(“insert into Category values(‘IGN’,’Ignition System’)”);

12. Update • Modify an existing row in a table • update category set descr = “Ignition Subsystem” where catg_code = “IGN” • Use jdbc executeUpdate method • String s = “update category set descr = ‘Ignition Subsystem’ where catg_code = ‘IGN’ ”; • stmt.executeUpdate(s);

13. Delete • Remove a row from a table • delete from category where catg_code = “IGN” • remove the IGN category • delete from category where catg_code like “I%” • remove all rows where catg_code starts with an “I”

14. Data Definition Commands • Create table • add a table to a database • Drop Table • remove a table from a database • Alter Table • add or delete column(s)

15. A good reference • Phil Greenspun – SQL for Web Nerds • http://philip.greenspun.com/sql/