Section 1 - Introduction to SQL

1. Section 1 - Introduction to SQL • SQL is an abbreviation for Structured Query Language. • It is generally pronounced “Sequel” • SQL is a unified language for... defining, querying, modifying, and controlling the data in a Relational Database.

2. SQL Standards • SQL standards were originally developed in academia by Dr. E.F. Codd • Commercial institutions now lead the standard by extending SQL to meet the needs of business. • The main commercial database management systems (DBMS) in the industry today are: Oracle, Sybase, Informix, Microsoft SQL Server

3. Interactive SQL • The class exercises in use interactive SQL (i.e. SQL in entered on a command line and the results are seen automatically in the data window) • Embedded SQL, which is SQL that is run inside of a computer program, is not covered in the book, but will be a special topic covered in the class

4. SQL as a Second Language • SQL is not a “Natural” Language • SQL is Non Procedural • SQL has a Definite Syntax • SQL is freeform, but individual clauses must be in the proper order • SQL can not do everything in a single statement

5. Relational Database • What is a Relational Database Management System (RDBMS)? • All data is stored in Tables (i.e. Relations)(grid-like format, similar to a spreadsheet) • The logical representation of data is separate from its physical storage • One high-level language is provided for structuring, querying, and changing information. This, of course, is SQL

6. What is RDBMS? - cont. • Supports Selection, Projection and Joins • Selection: What kind of information you see • Projection: The query criteria • Joins: How you connect related information from different tables • Supports the concept of NULL values • Allows VIEWS into the data • Provides Mechanisms for Integrity, Recovery, Authorization, and Transactions

7. What are Tables? • They have Rows and Columns (like Files or Spreadsheets) • Rows (like Records) • Columns (like Fields) • A Set of Related Tables is called a Database • Tables are separate, but equal in that... • They have no Hierarchical Ranking • They have no necessary Physical Relationship to each other

8. What is an Entity? • An entity is a person, place, or thing for which you wish to hold information • A table is a collection of separate occurrences of an Entity • E.g. the “Employees” table contains information about individual employees • Separate Characteristics are stored for each Occurrence of an Entity • E.g. An individual employee has a name, address, phone number, etc.

9. Rows & Columns • A Row is a single occurrence of an Entity • Each Column describes one Characteristic of the Entity

10. Example Table • Last Name CityPerry San DiegoSmith Los AngelesJones Los Angeles • In the above table "Last Name" and "City" are the columns • Each different person and their represent a row of data

11. Question • What is a table?

12. Answer • A table is a collection of separate occurrences of an Entity

13. Question • What is a row?

14. Answer • A Row is a single occurrence of an Entity

15. Question • Characteristics of an entity are described with ___________?

16. Answer • Characteristics of an entity are described with COLUMNS ?

17. Primary Key • Each Row is uniquely identified using the Primary Key. • The Primary Key is defined as any Column (or combination of columns) that can be used to uniquely identify a particular row.

18. Example • Last Name CityPerry San DiegoSmith Los AngelesJones Los Angeles • In the above example the Last Name column acts as the PRIMARY key. (Note: names are not usually a good choice, but this is a simple example)

19. Question • What is used to distinguish between rows in a table?

20. Answer • Rows are distinguished from either other by using a PRIMARY KEY

21. Values • A Value can be determined by the intersection of the Row and Column for the row identified by the Primary Key.

22. Types of Tables • User Tables hold the data of the system • System Tables hold information about the structure and objects of the database

23. Question • What is needed to find a specific value in a table?

24. Answer • The Primary Key and a Column

25. Physical vs. Logical • The User’s View of the data is independent of the physical storage of the data • Physical storage can change without affecting the logical representation of the data.

26. SQL is a High-Level Language • SQL statements can logically be broken in to three high-level sets... • Data Manipulation DML|which can query and update the data • Data Definition DDLwhich defines the objects in a database • Data Administration DCLwhich controls access to the data

27. Data Manipulation Statements • The SELECT statement displays information you want to see from the database • The INSERT statement allow you to add rows to the database • The UPDATE statement allows you to change existing column information • The DELETE statement deletes rows of data

28. Data Definition Statements • The CREATE statement allows you create tables, views, and indexes • The DROP statement allows you to remove tables, views, and indexes

29. Data Administration Statements • The GRANT statement allows you to define what userids have access to tables/columns • The REVOKE statement allows you to remove userid access to tables/columns

30. Select Statement Introduction • The next series of pages will show you some sample tables and data that we will use to illustrate the Select statement

31. Example Tables • Employees Table • Last_name • First_name • City • ZipCodes Table • City • Zip_code

32. Data in Example Tables • Employees -Last_nameFirst_nameCityPerry Steve San DiegoSmith Will Los AngelesJones Tommy Lee Los Angeles • ZipCodes -CityZip_codeSan Diego 92001Los Angeles 90211

33. Selection • SELECT last_name, cityFROM employees • Results:Last Name CityPerry San DiegoSmith Los AngelesJones Los Angeles

34. Projection • SELECT last_name, cityFROM employeesWHERE city = 'San Diego' • Results:Last Name CityPerry San Diego

35. Joins • SELECT last_name, city, zip_codeFROM employees, zipcodesWHERE city = 'San Diego'AND employees.city = zipcodes.city • Results:Last Name City Zip CodePerry San Diego 92001

36. Introducing NULL Values • NULL means Unknown, Missing, or Not Applicable • NULL does NOT mean Zero or Blank

37. Question • What kind of SQL statement is used to Query information in a database?

38. Answer • The SELECT statements querys information from the database

39. SQL VIEWs • Views are 'Derived Tables' that allow a different view of the data stored in existing tables • They are not actual copies of the data • User may SELECT against them in the same way as a table • Also known as 'Virtual tables'

40. Integrity, Security, Transactions • Integrity insures the consistency and accuracy of the data • Security insures proper authorization to view and/or update the data • Transactions allow data to be saved to the database as a logical unit of work

41. Question • TRUE or FALSE?A NULL value means an empty string forcharacter based data

42. Answer • FALSEa NULL value in numeric, date, or character based data means Unknown, Missing, or Not Applicable

43. Section 1 - Last Page • Study Chapters 2-3 (thru Create statements only) for Section 2 • There is no Test for Section 1