1 / 30

SQL Review

SQL Review. Data Manipulation Language. Key Learning Points. Review SQL syntax for manipulating data Differentiate between interactive and non-interactive SQL Identify the techniques of including SQL in applications Differentiate between statement level interfaces and call level interfaces.

gail-fowler
Download Presentation

SQL Review

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. SQL Review Data Manipulation Language

  2. Key Learning Points • Review SQL syntax for manipulating data • Differentiate between interactive and non-interactive SQL • Identify the techniques of including SQL in applications • Differentiate between statement level interfaces and call level interfaces

  3. What is SQL? • Structured Query Language • The standard query language for creating and manipulating and controlling data in relational databases

  4. SQL Coverage • Data Definition Language • Data Manipulation Language • Data Control Language

  5. SQL DML Keywords • Select • Update • Insert • Delete

  6. Selecting Records SELECT [ ALL | DISTINCT ] * | table_name | view_name | table_alias .* | column_name | expression [ [AS] column_alias ]| column_alias = expression [,...n]FROM table_name | view_name [ [AS] table_alias ]

  7. Specifying Search Condition SELECT select_listFROM table_sourceWHERE search_condition

  8. SELECT Conditions • = equal to a particular value • >= greater than or equal to a particular value • > greater than a particular value • <= less than or equal to a particular value • <> not equal to a particular value • LIKE “*term*” (may be other wild cards in other systems) • IN (“opt1”, “opt2”,…,”optn”) • BETWEEN val1 AND val2 • IS NULL

  9. Sorting the Result SELECT select_list FROM table_source [WHERE search_condition] ORDER BY column_name | column_alias |expression[ASC | DESC]

  10. Grouping the Result Set SELECT select_listFROM table_source[WHERE search_condition]GROUP BY expression [,…n ][ORDER BY expression [,…n]]

  11. Selecting from Multiple Tables(Equijoin) SELECT select_list FROM table_source1, table_source2 [, table_sourceN] WHERE table_source1.column_name = table_source2.column_name [AND table_source2_column_name = table_sourceN_column_name…]

  12. Selecting from Multiple Tables(Outer Join) SELECT select_list FROM table_source1, table_source2 WHERE table_source1.column_name[(+)] = table_source2.column_name[(+)]

  13. Specifying Search Condition SELECT select_list FROM table_source1, table_source2 WHERE table_source1.column_name = table_source2.column_name AND search_condition

  14. Updating Specific Records UPDATE table_name | view_nameSET column_name = new_value[, column_name=new_value]WHERE search_condition

  15. Adding Records INSERT [INTO] table_name | view_name[ (column_list ) ] VALUES (DEFAULT | NULL | expression, DEFAULT | NULL | expression,…)

  16. Deleting Specific Records DELETE FROM table_name | view_name[WHERE search_condition ]

  17. Interactive vs. Non-Interactive SQL • Interactive SQL: SQL statements input from terminal; DBMS outputs to screen • Non-interactive SQL: SQL statements are included in an application program written in a host language, like C, Java, COBOL

  18. Processing an SQL Statement • Parses the SQL statement • Validates the statement • Generates an access plan • Optimizes the access plan • Executes the statement

  19. Buffer Mismatch • Problem: SQL deals with tables (of arbitrary size); host language program deals with fixed size buffers • Solution: Fetch a single row at a time

  20. Cursor cursor SELECT Result set (or pointers to it) application Base table

  21. Introducing SQL Into the Java Application • Statement Level Interface (SLI) • Call Level Interface (CLI)

  22. Statement Level Interface • Static or Embedded SQL • Dynamic SQL

  23. SQLJ • A statement-level interface to Java • A dialect of embedded SQL designed specifically for Java • Translated by precompiler into Java • SQL constructs translated into calls to an SQLJ runtime package, which accesses database through calls to a JDBC driver

  24. SQLJ Example #SQL { SELECT C.Enrollment INTO :numEnrolled FROM Class C WHERE C.CrsCode = :crsCode AND C.Semester = :semester };

  25. Call Level Interface • Written in host language • Similar to dynamic SQL

  26. JDBC • Call-level interface (CLI) for executing SQL from a Java program • SQL statement is constructed at run time as the value of a Java variable (as in dynamic SQL) • JDBC passes SQL statements to the underlying DBMS. Can be interfaced to any DBMS that has a JDBC driver

  27. JDBC Run-Time Architecture Oracle database Oracle driver application driver manager SQLServer driver SQLServer database DB/2 driver DB/2 database DBMS

  28. Executing a Query import java.sql.*; Class.forName (driver name); Connection con = DriverManager.getConnection(Url, Id, Passwd); Statement stat = con.CreateStatement (); String query = “SELECT T.StudId FROM Transcript T” + “WHERE T.CrsCode = ‘cse305’ ” + “AND T.Semester = ‘S2000’ ”; ResultSet res = stat.executeQuery (query);

  29. Handling Exceptions try { ...Java/JDBC code... } catch ( SQLException ex ) { …exception handling code... }

  30. Questions? • This is the end of the presentation. • What questions do you have?

More Related