Introduction to SQL

1. Introduction to SQL

2. Parts of a database • Attributes (fields) • An attribute or field is a component of a record that describes something about an item. • Records • A record is the representation of an individual item. • Table • A collection of records • Database • A collection of tables and rules for accessing the tables

3. Parts of a database Record Tables Attribute/Field • Records become “rows” • Attributes/fields become “columns” • Rules determine the relationship between the tables and tie the data together to form a database

4. What is a relational database? • Relational vs flat file vs other… • Organizes data into tables where each item is a row and the attributes of the item are in columns. • Different from “flat file” databases because you can define “relationships” between items in different tables.

5. I need a new database! • Many people ask for “new databases” when in fact they only need a new table within an existing database. • The data within the tables should be all related somehow. • By owner • By project

6. Creating a database • What information are we trying to store? • How do we describe the information? • Phone Book/Contact entries • Name • Address • Company • Phone Number • URL/Web Page • Age • Height (in meters) • Birthday • When we added the entry

7. Data Types • Binary • Database specific binary objects • Pictures, digital signatures, etc. • Boolean • True/False values • Character • Fixed width or variable size • Numeric • Integer, Real (floating decimal point), Money • Temporal • Time, Date, Timestamp

8. Phone Book/Contact Record Name Character Address Character Company Character Phone Number Character URL/Web Page Character Age Integer Height Real (float) Birthday Date When we added the entry Timestamp

9. “Normal Forms”

10. The Zero Form • No rules have been applied • Where most people start (and stop) • No room for growth • Usually wastes space

11. First Normal Form • Eliminate repeating columns in each table • Create a separate table for each set of related data • Identify each set of related data with a primary key Benefits: Now we can have infinite phone numbers or company addresses for each contact. Drawback: Now we have to type in everything over and over again. This leads to inconsistency, redundancy and wasting space. Thus, the second normal form…

12. Second Normal Form • Create separate tables for sets of values that apply to multiple records • Relate these tables with a “foreign key”.

13. Third Normal Form • Eliminate fields that do not depend on the primary key. Is this enough? Codd thought so… What about “many to many”?

14. Kinds of Relationships • “One to One” • One row of a table matches exactly to another • One person, one id number, one address • “One to Many” • One row of a table matches many of another • One person, many phone numbers • “Many to Many” • One row may match many of another or many rows match one row of another

15. Fourth Normal Form • In a “many to many” relationship, independent entities cannot be stored in the same table.

16. Fifth Normal Form • The “very esoteric” one that is probably not required to get the most out of your database…. So we are not looking at this one!

17. Why normalize? • Increases the integrity of the data • Reduces redundancy • Improves efficiency • Although normalization can be hard, it is worth it in the long run.

18. Basic SQL Commands • Creating tables with CREATE • Modifying tables ALTER • Adding data with INSERT • Viewing data with SELECT • Removing data with DELETE • Modifying data with UPDATE • Destroying tables with DROP

19. Creating tables with CREATE • Generic form CREATE TABLE tablename ( column_name data_type attributes…, column_name data_type attributes…, … ) • Table and column names can’t have spaces or be “reserved words” like TABLE, CREATE, etc.

20. Phone Book/Contact Table CREATE TABLE contacts ( Name VARCHAR(40), Address VARCHAR(60), Company VARCHAR(60), Phone VARCHAR(11), URL VARCHAR(80), Age INT, Height FLOAT, Birthday DATE, WhenEntered TIMESTAMP ); Plan your tables very carefully! Once created, they are difficult to change!

21. Phone Book/Contact Table CREATE TABLE contacts ( ContactID INT PRIMARY KEY, Name VARCHAR(40), Address VARCHAR(60), Company VARCHAR(60), Phone VARCHAR(11), URL VARCHAR(80), Age INT, Height FLOAT, Birthday DATE, WhenEntered TIMESTAMP ); If you are going to use the relational nature of a database,don’t forget you need to have a unique way to access records! There is a way to make the key automatically increment,so you don’t have to worry about which one is next.

22. Data Types • Binary • Database specific binary objects (BLOB) • Boolean • True/False values (BOOLEAN) • Character • Fixed width (CHAR) or variable size (VARCHAR) • Numeric • Integer (INT), Real (FLOAT), Money (MONEY) • Temporal • Time (TIME), Date (DATE), Timestamp (TIMESTAMP)

23. Adding data with INSERT • Generic Form INSERT INTO tablename (column_name,…) VALUES (value,…)

24. Inserting a record into ‘contacts’ INSERT INTO contacts (contactid,name,address,company,phone,url,age,height,birthday,whenentered) VALUES (1,‘Joe’,’123 Any St.’,’ABC’, ’800-555-1212’,‘http://abc.com’,30,1.9,’6/14/1972’,now());

25. Inserting a partial record INSERT INTO contacts (contactid,name,phone) VALUES (2,’Jane’,’212-555-1212’);

26. Automatic key generation • CREATE SEQUENCE contactidseq; • Change the ContactID line in the CREATE TABLE to: ContactID INT DEFAULT nextval(‘contactidseq’) PRIMARY KEY • Or when inserting into a table INSERT contacts (contactid,name,phone) VALUES (nextval(‘contactidseq’),’Jack’, ‘716-555-1212’);

27. Viewing data with SELECT • Generic Form SELECT column,… FROM table,… WHERE condition GROUP BY group_by_expressionHAVING condition ORDER BY order_expression • The most used command • Probably the most complicated also • If used improperly, can cause very long waits because complex computations

28. A few simple SELECTs • SELECT * FROM contacts; • Display all records in the ‘contacts’ table • SELECT contactid,name FROM contacts; • Display only the record number and names • SELECT DISTINCT url FROM contacts; • Display only one entry for every value of URL.

29. Refining selections with WHERE • The WHERE “subclause” allows you to select records based on a condition. • SELECT * FROM contacts WHERE age<10; • Display records from contacts where age<10 • SELECT * FROM contacts WHERE age BETWEEN 18 AND 35; • Display records where age is 18-35

30. Additional selections • The “LIKE” condition • Allows you to look at strings that are alike • SELECT * FROM contacts WHERE name LIKE ‘J%’; • Display records where the name starts with ‘J’ • SELECT * FROM contacts WHERE url LIKE ‘%.com’; • Display records where url ends in “.com”

31. Removing data with DELETE • Generic Form DELETE FROM table WHERE condition; DELETE FROM contacts WHERE age<13;

32. Modifying data with UPDATE • Generic Form UPDATE table SET column=expression WHERE condition; UPDATE contacts SET company=‘AOL’ WHERE company=‘Time Warner’;

33. Destroying tables with DROP • Generic Form DROP TABLE tablename; DROP TABLE contacts;

34. More about SELECT

35. “Normal Forms” and SELECT • Good database design using the normal forms requires data to be separated into different tables • SELECT allows us to join the data back together • We can use “views” to create virtual tables

36. Joining together tables • SELECT name,phone,zip FROM people, phonenumbers, address WHERE people.addressid=address.addressid AND people.id=phonenumbers.id;

37. Different types of JOINs • “Inner Join” • Unmatched rows in either table aren’t printed • “Left Outer Join” • All records from the “left” side are printed • “Right Outer Join” • All records from the “right” side are printed • “Full Outer Join” • All records are printed • Multiple Table Join • Join records from multiple tables

38. General form of SELECT/JOIN SELECT columns,… FROM left_table join_type JOIN right_table ON condition; SELECT name,phone FROM people JOIN phonenumbers ON people.id=phonenumbers.id;

39. Other versions SELECT name,phone FROM people LEFT JOIN phonenumbers ON people.id=phonenumbers.id; SELECT name,phone FROM people RIGHT JOIN phonenumbers ON people.id=phonenumbers.id; SELECT name,phone FROM people FULL JOIN phonenumbers ON people.id=phonenumbers.id;

40. “Theta style” vs. ANSI • Theta Style (used in most SQL books) SELECT name,phone,zip FROM people, phonenumbers, address WHERE people.addressid=address.addressid AND people.id=phonenumbers.id; • ANSI Style uses JOIN SELECT name,phone,zip FROM people JOIN phonenumbers ON people.id=phonenumbers.id JOIN address ON people.addressid=address.addressid;

41. Other SELECT examples • SELECT * FROM contacts WHERE name is NULL; • SELECT * FROM contacts WHERE zip IN (‘14454’,’12345’); • SELECT * FROM contacts WHERE zip IN ( SELECT zip FROM address WHERE state=‘NY’ );

42. GROUP BY/HAVING • The “GROUP BY” clause allows you to group results together with “aggregate functions” • AVG(), COUNT(), MAX(), MIN(), SUM() • COUNT DISTINCT • HAVING allows you to search the GROUP BY results

43. GROUP BY Examples SELECT company,count(company)FROM contactsGROUP BY company; SELECT company,count(company)FROM contactsGROUP BY companyHAVING count(company) > 5;

44. ORDER BY • The “ORDER BY” clause allows you to sort the results returned by SELECT. SELECT * FROM contacts ORDER BY company; SELECT * FROM contacts ORDER BY company, name;

45. Views • You can use “CREATE VIEW” to create a virtual table from a SELECT statement. CREATE VIEW contactview AS (SELECT name,phone,zip FROM people,phonenumbers,address WHERE people.id=phonenumbers.id AND people.addressid=address.addressid);