SQL Overview

1. SQL Overview Introduction to SQL for the MultiValue Developer

2. Why are we here? • Many MultiValue developers now are working with the various relational databases • We want to look at common commands and data structures for SQL databases • We want to see how they relate to the MultiValue

3. eris • Database to Web integration since 1992 • Customers with up to $2 Billion in annual revenue • Clients throughout North America • Education, Medical, Manufacturing, EDI, Distribution, Sales Force Automation, Help Desk, and Reporting Systems • e-Commerce and database product development, e.g., WebWizard, DataReady, mv://e-Store • Los Angeles and Chicago offices

4. Agenda • About eris • SQL Overview • Basics • Reporting • Database maintenance

5. What is SQL? • Standard Query Language • Used to communicate with the database • Get data • Maintain data • Create data

6. Standard SQL? • Not really • Similar related languages (think Latin) • You need to understand the dialect • Be able to compare what yours has vs others • Hmm…Think MultiValue

7. Basic parts of SQL • Data Definition Language (DDL) • Database Manipulation Language (DML) • Database Control Language (DCL) • Database Stored Procedure Language (DSPL)

8. Data Definition Language (DDL) • Defines the database environment • Varies most among the various SQL flavors • Covers • Database • Design Structures • Related System Tables • Metadata • Schemas • Catalogs • Stored Procedures • Other structural elements

9. Database Manipulation Language (DML) • Reasonably standardized. • We'll look at DML later on • Problems arise not in what they offer but what they might not have • Types of joins • Sub-queries

10. Database Control Language (DCL) • How to maintain and configure the database • Permission, roles, and referential integrity • Grant • Check • Constraint • Primary Key • Foreign Key • Fairly dissimilar among the various flavors

11. Database Stored Procedure Language (DSPL) • All the previous variations compound when you get to this section • Triggers • Event handler • Stored procedures • Can barely compare • Oracle's PL/SQL • SQL Server's Transact SQL • DB2's Stored Procedures

12. Reporting – Which SQL? • Microsoft SQL Server • mySQL • Oracle • DB2 • SQLite • SQLAnywhere • MultiValue, such as in uniVerse/UniData

13. What does a table look like? Weather

14. Lingo • Row • Record • Column • Attribute / Field

15. How do I list? • Select • select high, low from Weather where city like “%Pasa%”; • select * from Weather where city = “California”;

16. Selecting Data • “select” is comparable to “LIST” • Syntax:select column1[,column2,etc or * for all] from tablename[where condition] ; • Compare with • LIST "tablename" "column1" "column2" WITH "condition"

17. Roster database

18. Sample queries • select first, last, city from roster; • select last, city, age from roster where age > 30; • select first, last, city, state from roster where first like 'J%'; • select * from roster; • select first, last, from roster where last like '%s'; • select first, last, age from roster where last like '%on%'; • select * from roster where first = 'Eric';

19. Sample operators in Joins, Where and Having • And, Or, Not • itemWidth > 11 and itemHeight < 14 • not (region = "E" or sales < quota) • Is Null, Is Not Null • Nulls occur when no data value has been entered into a field. Different from MultiValue • Between • a shorthand way to refer to a range • totalSales between 1500 and 1800

20. More operators • In, Not In - allows table lookups • vehicleType in ("sedan", "roadster", "2-door") • sportPlayed not in ("tennis","squash", "badminton", "raquetball") • Like • one of the most powerful for searches within a text or varchar field • Think “…” or “[“ and “]” in MultiValue • Supports text search patterns which work like but not identically the same in syntax as regular expressions from Unix and XML

21. Like, Even More Operators • "%" is any length (think "*" in DOS) • "_" is a single character (think "?" in DOS) • [] lists valid characters at the point: [mp]a[nt] can be map, mat, pan, pat [0-9] means a numeric character • [^] lists excluded characters

22. Inner Joins • This is what makes SQL "relational“ • It joins tables • Similar to translates in MultiValue • Syntax:select list-of-columns from tablename1,tablename2 where search-condition(s) ;

23. Imagine an Inner Join customers table customer_id firstname lastname address city state zip sales table sale_id cust_id date Item price select customers.firstname, customers.lastname, sales.itemfrom customers, saleswhere customers.customer_id = sales.cust_id;

24. No need for a new dictionary element • The columns have the table name in front • A period separates the table name from the column name • This is opional but is very recommended. It is required if the name column names are the same between the two tables.

25. Alternate Syntax select customers.firstname, customers.lastname, sales.itemfrom customers, saleswhere customers.customer_id = sales.cust_id; select customers.firstname, customers.lastname, sales.itemfrom customersinner join saleson customers.customer_id = sales.cust_id;

26. Database Maintenance • Creating tables • Adding records • Editing records • Deleting records

27. Creating tables • Analogous to CREATE-FILE / CREATE.FILE • Syntax:create table tablename (column1 datatype, …) ; • Table and Column names • must start with a letter • can be followed by letters, numbers, or underscores • not to exceed a total of 30 characters in length • Do not use any SQL reserved keywords

28. Common Data types

29. Add records - Insert • Syntax:insert into tablename (first_column, ... ,last_column) values (‘first_value’, ... ,’last_value’); • Strings need single-quotes! • Example:insert into customers (first, last, age, address, city, state) values ('Joe', 'Johnson', 21, '199 Los Robles', 'Pasadena', 'California');

30. Updating Records - Update and where • Syntax: update tablenameset first_column = new_value [,next_column = newvalue2 …]where condition [and|or condition2 …];

31. Examples • update phone_book set area_code = 323 where prefix = 213; • update phone_book set prefix=555, suffix=9292 where last_name = 'Jones' and first_name = 'John' ; • update employee set age = age+1 where first_name='Mary' and last_name='Williams';

32. Deleting records • Syntax:delete from tablenamewhere condition [and|or condition2 …]; • Warning! if you leave off the where clause, all records will be deleted!

33. Bottom Line • SQL has the very same intent, functionality, and general purpose across database vendors - users have had to get used to working in dialects. • SQL interoperability is a problem. • It provides the Rosetta Stone of a standard, common syntax for data interchange not just between relational databases; but also structured data in general.

34. Contact us Main Office: 199 S. Los Robles Ave, Suite 860 Pasadena, CA 91101 Tel: (626) 535-9658 Fax: (626) 628-3229 www.eriscorp.com info @ eriscorp.com