1 / 15

Intro to SQL

Intro to SQL. CS-422 Dick Steflik. Structured Query Language. Developed by C. Date for Relational Data Base Management Systems (RDBMS) Simple Declarative Language has no program control statements. SQL. Two categories of commands Data Manipulation Commands deal with: RETRIEVING DATA

Download Presentation

Intro to SQL

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript


  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/

More Related