A Guide to SQL, Seventh Edition

1. A Guide to SQL, Seventh Edition

2. Objectives • Understand the concepts and terminology associated with relational databases • Create and run SQL commands in Oracle, Microsoft Access, and MySQL • Create tables using SQL A Guide to SQL, Seventh Edition

3. Objectives • Identify and use data types to define columns in SQL tables • Understand and use nulls • Add rows to tables • Describe a table’s layout using SQL A Guide to SQL, Seventh Edition

4. Introduction • Databases are found at school libraries, on the Internet, and other places where you retrieve data on a computer • Learn concepts and terminology associated with relational model for database management • Learn how to create a database by describing and defining tables and columns A Guide to SQL, Seventh Edition

5. Introduction • SQL (Structured Query Language) is a widely used language for retrieving and manipulating data • SQL was developed in the mid-1970’s for IBM under the name SEQUEL • Renamed SQL in 1980 • Most DBMSs use a version of SQL as their data manipulation language A Guide to SQL, Seventh Edition

6. Introduction • You will learn to assign data types to columns and the use of null values • You will learn how to load a database by creating tables and adding data to them • You will learn how to describe a table’s layout using SQL A Guide to SQL, Seventh Edition

7. Relational Databases • A relational database is a collection of tables • Tables are called relations • This term is the basis for the name relational database A Guide to SQL, Seventh Edition

8. A Guide to SQL, Seventh Edition

9. A Guide to SQL, Seventh Edition

10. Entities, Attributes, and Relationships • An entity is a person, place, object, event, or idea for which you want to store and process data • Premier Product’s entities are customers, orders, parts, and sales reps • Entities for a school would be students, faculty, and classes A Guide to SQL, Seventh Edition

11. Entities, Attributes, and Relationships • Attribute is a character or property of an entity • Premiere Products attributes for the entity “customer” are customer name, street, city, and so on • Also called a field or column in many database systems A Guide to SQL, Seventh Edition

12. Entities, Attributes, and Relationships • A relationship is the association between entities • At Premiere Products, there is a relationship between customers and sales reps • One-to-many relationship • Each sales rep has many customers • Each customer has only one sales rep A Guide to SQL, Seventh Edition

13. Entities, Attributes, and Relationships • Premiere Products database • One table for sales reps, one for customers, and so on for each entity • Attributes become columns in each table • Relationship is represented by using common columns in two or more tables A Guide to SQL, Seventh Edition

14. A Guide to SQL, Seventh Edition

15. Definitions • A relation is a two-dimensional table in which: • Entries are single-valued • Each column has a distinct name • All values of the same attribute • Order of columns and rows is immaterial • Each row is distinct A Guide to SQL, Seventh Edition

16. Definitions • A relational database is a collection of relations • A row in a table is called a record or a tuple • Multiple entries in one position in a table are called a repeating group A Guide to SQL, Seventh Edition

17. Shorthand Representation • After the name of the table, the columns are listed within a set of parentheses • REP (Rep_Num, Last_Name, First_Name, Street, City, State, ZIP, Commission_Rate) • CUSTOMER (Customer_Num, Customer_Name, Street, City, State, ZIP, Balance, Credit_Limit, Rep_Num) A Guide to SQL, Seventh Edition

18. Shorthand Representation • Not case-sensitive except when inserting character values in a table • Use a period to separate the table and column name to avoid confusion • The primary key uniquely identifies a row in a table • Indicate primary key by underlining the column or collection of columns A Guide to SQL, Seventh Edition

19. Database Creation • Describe the layout of each table in the database • Use CREATE TABLE command • TABLE is followed by the table name • Follow this with the names and data types of the columns in the table • Data types define type and size of data A Guide to SQL, Seventh Edition

20. Table and Column Name Restrictions • Names cannot exceed 18 characters • In Oracle, can be up to 30 characters in length • Must start with a letter • Can contain letters, numbers, and underscores (_) • Cannot contain spaces A Guide to SQL, Seventh Edition

21. Create Table Command A Guide to SQL, Seventh Edition

22. Create Table Command • Table name is REP • Contains nine columns: REP_NUM, LAST_NAME, FIRST_NAME, STREET, CITY, STATE, ZIP, COMMISSION, RATE • REP_NUM is limited to two characters and is the primary key • RATE column is limited to numbers, three digits with two decimal places A Guide to SQL, Seventh Edition

23. Create Table Command • Commands are free-format; no rules stating specific words in specific positions • Hit enter to move to the next line in a command • Indicate the end of a command by typing a semicolon A Guide to SQL, Seventh Edition

24. Running SQL Commands • Programs and individual preference determine method for running command • Oracle 10g use SQL*Plus or SQL*Plus Worksheet • Create queries in SQL view in Microsoft Access • Commands are typed at prompt in MySQL A Guide to SQL, Seventh Edition

25. A Guide to SQL, Seventh Edition

26. A Guide to SQL, Seventh Edition

27. A Guide to SQL, Seventh Edition

28. A Guide to SQL, Seventh Edition

29. Editing SQL Commands • Edit commands as you would in a word processor when using Oracle SQL*Plus Worksheet or Access SQL • After making edits, click the Execute or Run button • With Oracle SQL*Plus or MySQL the on-screen command must be edited to change it A Guide to SQL, Seventh Edition

30. Editing Oracle SQL*Plus • In Oracle SQL*Plus you must edit commands one line at a time • The most recent command is stored in the command buffer (the buffer) • Edit the command in the buffer by using editing commands A Guide to SQL, Seventh Edition

31. A Guide to SQL, Seventh Edition

32. A Guide to SQL, Seventh Edition

33. A Guide to SQL, Seventh Edition

34. Editing MySQL Commands • Most recent command is stored in memory area called statement history • Edit command in statement history by using specific editing commands A Guide to SQL, Seventh Edition

35. A Guide to SQL, Seventh Edition

36. A Guide to SQL, Seventh Edition

37. Editing MySQL Commands • Press Up arrow key to go to top line • Hit Enter key to move to next line if line is correct • Use Right and Left arrow keys to move to point needing correction • When line is correct hit Enter key • If Enter is not hit on a line, that line will not be part of the revised command A Guide to SQL, Seventh Edition

38. Dropping a Table • Another way to correct errors is to drop (delete) a table and start over • Useful when table is created before errors are discovered • Command is followed by the table to be dropped and a semicolon • Data is deleted when a table is dropped A Guide to SQL, Seventh Edition

39. Data Types • For each column, the type of data must be defined • Actual data types vary between SQL programs  there are some common types • CHAR(n), DATE, DECIMAL(p,q), INTEGER, SMALLINT A Guide to SQL, Seventh Edition

40. Nulls • A null is used as special value to represent situation when actual value is not known for a column • Specify whether to allow nulls in the individual columns • Nulls should not be allowed for primary key columns A Guide to SQL, Seventh Edition

41. Implementation of Nulls • NOT NULL clause is used in a CREATE TABLE command to exclude the use of nulls in a column • Default is to allow null values • If a column is defined as NOT NULL, system will reject any attempt to store a null value there A Guide to SQL, Seventh Edition

42. Loading a Table with Data • INSERT Command • Adds rows to a table • INSERT INTO followed by the table name • VALUES command follows with specific values in parentheses • Values for character columns are in single quotation marks A Guide to SQL, Seventh Edition

43. The Insert Command A Guide to SQL, Seventh Edition

44. Modifying the INSERT Command • To add new rows it is easier to modify previous insert command and execute it • Oracle SQL*Plus Worksheet and Access SQL view, select the text, modify and execute • Oracle SQL*Plus or MySQL, edit commands manually A Guide to SQL, Seventh Edition

45. A Guide to SQL, Seventh Edition

46. A Guide to SQL, Seventh Edition

47. A Guide to SQL, Seventh Edition

48. A Guide to SQL, Seventh Edition

49. A Guide to SQL, Seventh Edition

50. The INSERT Command with Nulls • Use a special format of INSERT command to enter a null value in a table • Identify the names of the columns that accept non-null values, then list only the non-null values after the VALUES command A Guide to SQL, Seventh Edition