1 / 74

SQL in 21 Days

SQL in 21 Days. Mike Elms. Getting Started with SQL. Day 1. Getting Started with SQL. The history of SQL and databases Dr. Codd’s 12 rules for a relational database mode How to design a database structure What the popular SQL implementations are

issac
Download Presentation

SQL in 21 Days

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 in 21 Days Mike Elms

  2. Getting Started with SQL Day 1

  3. Getting Started with SQL • The history of SQL and databases • Dr. Codd’s 12 rules for a relational database mode • How to design a database structure • What the popular SQL implementations are • Why open database connectivity (ODBC) is important

  4. A Brief History of SQL • SQL began in an IBM laboratory in San Jose, California, where SQL was developed in the late 1970s. • SQL stands for Structured Query Language. • SQL was developed for IBM’s DB2 product. • SQL is a nonprocedural language or 4GL

  5. Dr. Codd’s 12 Rules for a Relational Database ModelA relational DBMS must be able to manage databases entirely through its relational capabilities • 1. Information Rule • 2. Guaranteed Access • 3. Systematic Null Value Support • 4. Active, Online Relational Catalog • 5. Comprehensive Data Sub-language • 6. View Updating Rule • 7. Set-level Insertion, Update, and Deletion • 8. Physical Data Independence • 9. Logical Data Independence • 10. Integrity Independence • 11. Distribution Independence • 12. Non-subversion

  6. Information Rule • All information in a relational database (including table and column names) is represented explicitly as a value in tabular format.

  7. Guaranteed Access • Every value in a relational database is guaranteed to be accessible by using a combination of table name, primary key value, and column name.

  8. Systematic Null Value Support • The DBMS provides systematic support for the treatment of null values (unknown or inapplicable data), which are distinct from default values, and independent of any domain.

  9. Active, Online Relational Catalog • The description of the database and its contents is represented at the logical level in tabular format and can therefore be queried using the database language.

  10. Comprehensive Data Sub-language • At least one supported language must have a well-defined syntax and be comprehensive. It must support data definition, manipulation, integrity rules, authorization, and transactions.

  11. View Updating Rule • All views that are theoretically updateable can be updated through the system.

  12. Set-level Insertion, Update, and Deletion • The DBMS supports not only set-level retrievals but also set-level inserts, updates, and deletes.

  13. Physical Data Independence • Application programs and add hoc programs are logically unaffected when physical access methods or storage structures are altered.

  14. Logical Data Independence • Application programs and ad hoc programs are logically unaffected, to the extent possible, when changes are made to the table structures.

  15. Integrity Independence • The database language must be capable of defining integrity rules. These rules must be stored in the online catalog, and they cannot be bypassed.

  16. Distribution Independence • Application programs and ad hoc requests are logically unaffected when data is first distributed or when it is redistributed.

  17. Non-subversion • It must not be possible to bypass the integrity rules defined through the database language by using lower-level languages.

  18. An Overview of SQL • Modify a database’s structure • Change system security settings • Add user permissions to databases or tables • Query a database for information • Update the contents of a database

  19. SQL in Application Programming • Module Language • Embedded SQL • Direct Invocation

  20. New features from SQL-92 • Connections to databases • Scrollable cursors • Dynamic SQL • Outer joins

  21. An Introduction to the Query Day 2

  22. An Introduction to the Query • Understand what a query is and how it is used • Understand the syntax and use of SELECT and FROM • Select and list all rows and columns from a table • Select and list selected columns from a table • Select and list columns from multiple tables

  23. Major SQL Keywords • SELECT • FROM • WHERE

  24. Your First Query • SQL> select * from tab;

  25. Expressions, Conditions, and Operators Day 3

  26. Expressions, Conditions, and Operators • Know what an expression is and how to use it • Know what a condition is and how to use it • Be familiar with the basic uses of the WHERE clause • Be able to use arithmetic, comparison, character, logical, and set operators • Have a working knowledge of some miscellaneous operators

  27. Arithmetic Operators • Plus (+) • Minus (-) • Multiply (*) • Divide (/) • Modulo (%)

  28. Comparison Operators • Equals (=) • Greater Than (>) • Greater Than or Equal To (>=) • Less Than (<) • Less Than or Equal To (<=) • Inequalities (< > or !=)

  29. Character Operators • Like (%) • Underscore (_) • Concatenation (||)

  30. Set Operators • Union • Union All • Intersect • Minus (Difference)

  31. Molding Data with Built-in Functions Day 4

  32. Molding Data with Built-in Functions • Aggregate Functions • Date and Time Functions • Arithmetic Functions • Character Functions • Conversion Functions • Miscellaneous Functions

  33. Clauses in SQL Queries Day 5

  34. Clauses in SQL Queries • WHERE • STARTING WITH • ORDER BY • GROUP BY • HAVING

  35. Joining Tables Day 6

  36. Joining Tables • Perform an equi-join • Perform a non-equi-join • Perform an outer join • Join a table to itself

  37. Subqueries: The Embedded SELECT Statement Day 7

  38. Subqueries: The Embedded SELECT Statement • Build a subquery • Use the keywords EXISTS, ANY, and ALL with your subqueries • Build and use correlated subqueries

  39. Data Normalization Day 8

  40. Database Normalization • Normalization defined • Benefits of normalization • Denormalization • Guidelines of normalization • The three normal forms

  41. Normalizing a Database • The RAW Database • Logical Database Design • What Are the Needs of the End User? • Data Redundancy

  42. The Normal Forms • The First Normal Form • The Second Normal Form • The Third Normal Form

  43. Benefits of Normalization • Greater overall database organization • The reduction of redundant data • Data consistency within the database • A much more flexible database design • A better handle on database security

  44. Creating and Maintaining Tables Day 9

  45. Creating and Maintaining Tables • Create key fields • Create a database with its associated tables • Create, alter, and drop a table • Add data to the database • Modify the data in a database • Drop databases

  46. Data Types in Oracle • CHAR • DATE • LONG • NUMBER • VARCHAR2 • BLOB • CLOB

  47. Controlling Data Integrity Day 10

  48. Controlling Data Integrity • What they are and how they are used • The different types of constraints • How to create constraints using SQL • The difference between data integrity and referential integrity • How to manage constraints

  49. Manipulating Data Day 11

  50. Manipulating Data • How to manipulate data using the INSERT, UPDATE, and DELETE commands • The importance of using the WHERE clause when you are manipulating data • The basics of importing and exporting data from foreign data sources

More Related