SQL 101

1. SQL 101

2. Fast and furious • 7 classes, one hour in length • Homework? Let’s talk • Oracle SQL exam • Too hard?

3. Code examples will come from This book:

4. Database Checkout: • Can you login? • Install the STUDENT schema: • \\newmoon\public\SQL_101 • Online Reference: • \\newmoon\public\SQL_101\Oracle10g_Comp_Ref\book.pdf

5. Syntax: Examples:

7. Relational Database Management System (RDBMS): • An RDBMS typically contains data in tables, essentially a two-dimensional matrix consisting of columns and rows. • Tables: A table typically contains data about a single subject. Each table has a unique name that signifies the contents of the data. • Columns: Columns in a table organize the data further. Each column represents a single, low-level detail about a particular set of data. The name of the column is unique within a table and identifies the data you find in the column. • Rows: Each row usually represents one unique set of data within a table. Each intersection of a column and row in a table represents a value, and some do not which are said to be null. Null is an unknown value… it cannot be evaluated or compared because a null is unknown.

8. Column Row

9. Primary Key: The purpose of the primary key (PK) is to uniquely identify data within a table. A table may have only ONE PK which consists of one or more columns. If the PK contains multiple columns, it is referred to as a composite primary key. Oracle does not require every table to have a PK, however, it is strongly recommended. • Foreign Keys: If you store students and the students zip code information in one table, each student’s name would have the address information (zip, city, state) repeated for each student in that zip code. If however, the data is split into two tables (STUDENT & ZIPCODE), then when an update is made to the location of the student, only one column needs to be updated vice 3. Eliminating redundancy is one of the key concepts in relational databases and this process is called normalization. The foreign key column (ZIP) happens to have the same column name in the STUDENT table. This makes it easier to recognize the fact that the tables share common column values… this the preferred method, but not mandatory.

10. Primary Key

11. Foreign Key

12. What is SQL? • An acronym for Structured Query Language • Pronounced “sequel” • It is the primary interface with the database and it’s commands allow you to query, insert, update, and delete data. • DML: Data Manipulation Language (Insert, Update, Merge & Delete data) • DDL: Data Definition Language (Create, Alter, Drop, Truncate & Rename) • DCL: Data Control Language (Grant, Revoke) • Transaction Control: Commit, Rollback, Savepoint

13. Capabilities of a SQL SELECT statement: • A SELECT statement retrieves information from the database. You can do the following when using a SELECT statement: • Projection: You can use the projection capability in SQL to choose the columns in a table that you want returned by your query • Selection: You can use the selection capability in SQL to choose the rows in a table that you want returned by a query • Joining: You can use the join capability in SQL to bring together data that is stored in different tables by creating a link between them

14. So…. Using SQL, how would I see all the data in the COURSE table?

15. So…. Using SQL, how would I see all the data in the COURSE table? SQL> select * from course;

16. How to limit the rows returned by your query: Query the COURSE table and return the rows with a cost of 1095 only

17. How to limit the rows returned by your query: Query the COURSE table and return the rows with a cost of 1095 only Use the WHERE clause (otherwise known as the “predicate”)

18. How to sort the rows returned by your query: Query the COURSE table and sort the rows by COURSE_NO

19. How to sort the rows returned by your query: Query the COURSE table and sort the rows by COURSE_NO Now, reverse the order of the sort

20. How to sort the rows returned by your query: Query the COURSE table and sort the rows by COURSE_NO Now, reverse the order of the sort

21. Using variables in your SQL statement: