ashish raghute, it director, fleetwood enterprises introduction to rdbms and sql
Post on 29-Dec-2015
220 Views
Preview:
TRANSCRIPT
Ashish Raghute, IT Director, Fleetwood Enterprises
Introduction to RDBMS and SQL
Introduction to RDBMS and SQL 7:30 am – 9 am
1. General RDBMS Concepts - Relational Vs. File Systems, normalization, ERD, extensibility
2. SQL DDL (Data definition language) Create, Alter, Drop Tables and Indexes, typical data types, space implications, referential integrity
9.15 am – 10.30 am
3. SQL DML (Data manipulation language) - selection, projection, aggregates, inner/outer/self/recursive/nested/co-related, updates, inserts, index use, statistics use, best practices and tips
4. Vendor enhancements to SQL - example SQL*PLUS and iSQL, functions (string, math, date etc.)
5. Examples of some tough SQLs for selects, inserts, updates and deletes
10.35 am – 11.30 am
6. Setting up ODBC
7. Exercises for the week
1. General RDBMS Concepts - Relational Vs. File Systems, normalization, ERD, extensibility
RDBMS – Relational Database Management System
Chronology:• Simple file based system• Sophisticated file based systems like VAX/RMS with COBOL • DBMS – Database • Next generation of DBMS – e.g. Paradox, DBaseIII..• DBMS typically had an engine and 4GL tool set• Relational DBMS is the most popular data access technology today• OODBMS not so popular
Typical RDBMS Features:• Normalization• Strong 4GL language support• Optimization – Indexing, Caching, execution planning etc.
Introduction to RDBMS and SQL
1. General RDBMS Concepts - Relational Vs. File Systems, normalization, ERD, extensibility
Flat Files vs. Relational Databases
Flat file is "A relatively simple database system in which each database is contained in a single table (or two). In contrast, relational database systems use multiple tables to store information, and each table can have a different record format.” - ZDNet’s Definition.
Examples Scenario – Order Entry System
File Database design:Cust Name, Cust Address, Order Date, Order Header, Item 1, Qty 1, Price1, Item 2, Qty 2, Price2, Item 3,………
RDBMS
Customer Table : Name, Address ……Order Table: Link to Customer Table, Order Header ….Order Item Table: Link to Order Table , Qty, Link to Product Table ….Product Table: Product Name, Cost, Price, On Hand Qty …..
Suggested Reading on Normalization
Introduction to RDBMS and SQL
1. General RDBMS Concepts - Relational Vs. File Systems, normalization, ERD, extensibility
Customer Order Order Item
Product
Entity Relationship Diagram or ERD for 3RD Normal Form
Entity
Customer.Customer_Num = Order.Customer_Num
Primary Key Foreign Key
1 to Many (1:M)Customer can have minimum zero and max many/unlimited ordersOrder can have minimum one and maximum one Customer
Attributes are all non-Key fields of an entity (typically one table)Hierarchical table design example, employee-manager
Introduction to RDBMS and SQL
1. General RDBMS Concepts - Relational Vs. File Systems, normalization, ERD, extensibility
What is Normalization ?
• Arranging data into logical groupings such that each group describes a small part of the whole;
• minimizing the amount of duplicate data stored in a database; • organizing the data such that, when you modify it, you make the
change in only one place; • building a database in which you can access and manipulate the
data quickly and efficiently without compromising the integrity of the data in storage.
Introduction to RDBMS and SQL
1. General RDBMS Concepts - Relational Vs. File Systems, normalization, ERD, extensibility
Example of poorly normalized table
Introduction to RDBMS and SQL
1. General RDBMS Concepts - Relational Vs. File Systems, normalization, ERD, extensibility
Example of well normalized tables in 3NF
Introduction to RDBMS and SQL
1. General RDBMS Concepts - Relational Vs. File Systems, normalization, ERD, extensibility
Extensibility - Commonly observed poorly designed tables:
1. Employee, Manager1, Manager2, Manager3
What will happen if a fourth manager comes into picture? What if 99% of employees have only one manager?
2A. Order Number, Part Number, Part Description2B. Part Number, Part Description
What if Part Description needs to be changed?
3A. Candidate Name, Address ….3B. Masters Degree College, Graduation Year, Under-grad college,
under-grad year, high school, high school year
Several candidates will have only high-school though a few will have Masters
Introduction to RDBMS and SQL
1. General RDBMS Concepts - Relational Vs. File Systems, normalization, ERD, extensibility
Getting started quickly on SQL:
SQL*Plus is the most commonly used program to work with Oracle Database
Another commonly used program is SQLPLUS Worksheet
We will use SQL*Plus for this session
Introduction to RDBMS and SQL
1. General RDBMS Concepts - Relational Vs. File Systems, normalization, ERD, extensibility
Starting SQL*Plus
Introduction to RDBMS and SQL
2. SQL DDL
Before we begin, common, useful commands for Oracle (NON-ANSI):
•Select * from tab;
•Describe <TableName>
•Select sysdate from DUAL;
Introduction to RDBMS and SQL
Data Definition Language is used to create Tables, Indexes, Views, Sequences, DB Links, Tablespaces etc..
• Creating a table (click here for suggested reading):
Create table TableName ( ColumnName1 int, ColumnName2 char(10), ColumnName3 varchar2(20), ColumnName4 Long);
Create table TableName as Select ColumnName1, ColumnName2….. from ExistingTable;
2. SQL DDL
Exercise – Create a set of tables in 3rd Normal Form (3 or 4 tables). Use as many data-types as you can. Note that only one Long field is allowed per table (how will you solve this problem, if you need more than 1?).
Suggested Reading click (here)
Introduction to RDBMS and SQL
Some tips
• After any DDL, transactions are always committed• Advance table creation options with tablespaces, extent sizes etc. • Indexes – guidelines on creation, statistics, sizing, composite index
use• Sequence Object – its use, avoidable problems• Database Links• Referential Integrity
2. SQL DDL
Recreate the tables you created to add a few referential integrity checks.
Suggested Reading click (here)
Introduction to RDBMS and SQL
9.15 am – 10.30 am
3. SQL DML (Data manipulation language) - selection, projection, aggregates, inner/outer/self/recursive/nested/co-related, updates, inserts, index use, statistics use, best practices and tips
4. Vendor enhancements to SQL - example SQL*PLUS and iSQL, functions (string, math, date etc.)
5. Examples of some tough SQLs for selects, inserts, updates and deletes
Introduction to RDBMS and SQL
Employee Table = Emp_Id, Emp_Fname, Emp_Lname, Age, Salary
3. SQL DML (Data manipulation language)
Select Emp_Id, Age, Salary From EmployeeWhere Salary < 2000 and Age > 30;
SELECTION
PROJECTION
SELECTION & PROJECTION
To select all fields, we can use “*”. For example, Select * from Employee
To select all rows, just drop the WHERE clause
Introduction to RDBMS and SQL
Select Emp_Id, Age, Salary From EmployeeWhere Salary < 2000 and Age > 30Order by Salary, Age Descending;
Employee Table = Emp_Id, Emp_Fname, Emp_Lname, Age, Salary
3. SQL DML (Data manipulation language)
ORDER BY
Introduction to RDBMS and SQL
Select A.Emp_Id, A.Age, B.Company_Name From Employee A, Company BWhere A.Company_id = B.Company_Id and A.Age > 30Order by A.Age Descending;
Employee Table = Emp_Id, Emp_Fname, Emp_Lname, Age, Company_IDCompany Table = Company_Id, Company_Name
3. SQL DML (Data manipulation language)
SIMPLE (PLAIN or INNER) JOIN
Note: Result set is a Cartesian product of number of rows from each table
Be careful to ensure all tables are joined correctly
Introduction to RDBMS and SQL
SOME COMMON EXAMPLES ARE COUNT, SUM, AVG etc.
Employee Table = Emp_Id, Emp_Fname, Emp_Lname, Age, Company_IDCompany Table = Company_Id, Company_Name
AGGREGATE FUNCTIONS
Common mistake – Group by is used even if there is no aggregate function in select
Select AVG(A.Age), B.Company_Name From Employee A, Company BWhere A.Company_id = B.Company_Id andGroup by B.Company_NameOrder by 1;
3. SQL DML (Data manipulation language)
Introduction to RDBMS and SQL
SOME COMMON EXAMPLES ARE COUNT, SUM, AVG etc.
Employee Table = Emp_Id, Emp_Fname, Emp_Lname, Age, Company_IDCompany Table = Company_Id, Company_Name
DISTINCT
Note – Distinct should be used very rarely. Need to use Distinct often is indicative of a bad application design
Select distinct Company_Name From Company;
Select count (distinct Company_Name) From Company;
3. SQL DML (Data manipulation language)
Introduction to RDBMS and SQL
SOME COMMON EXAMPLES ARE COUNT, SUM, AVG etc.
Employee Table = Emp_Id, Emp_Fname, Emp_Lname, Age, Company_IDCompany Table = Company_Id, Company_Name
“HAVING” CLAUSE
Having clause is like a Where clause for Aggregate attributes
Select AVG(A.Age), B.Company_Name From Employee A, Company BWhere A.Company_id = B.Company_Id andGroup by B.Company_NameHaving AVG(A.Age) > 20
3. SQL DML (Data manipulation language)
Introduction to RDBMS and SQL
Significance of NULL value
To compare a column to NULL, use “IS NULL” or “IS NOT NULL” instead of “=NULL” or “!=NULL” because comparison with NULL will always evaluate to FALSE
Example – Select * from Employee where Age is NULL;
NVL Function is another way to compare NULLS
Example – Select * from Employee where NVL(Age,0) = 0;
3. SQL DML (Data manipulation language)
Introduction to RDBMS and SQL
Operators and Expressions in SQL
Suggested Reading
3. SQL DML (Data manipulation language)
Introduction to RDBMS and SQL
Self referencing joins
Suggested Reading
select employee_name, mgr_name, level from employee connect by prior employee_name= Mgr_name
Note to myself: Demo this in conv@siebel3_fltux5
3. SQL DML (Data manipulation language)
Introduction to RDBMS and SQL
Standard ANSI SQL and Oracle Compliance
Suggested Reading
3. SQL DML (Data manipulation language)
Introduction to RDBMS and SQL
Sub Queries
Select a.last_name, a.fst_name from s_contact a where a.last_name in (select b.last_name from s_prospect)
Co-related Sub Queries
Select a.last_name, a.fst_name from s_contact a where a.pr_addr_id in (select b.row_id from s_addr_per b where b.per_id = a.row_id)
3. SQL DML (Data manipulation language)
Introduction to RDBMS and SQL
Exists clause
Select * from contacts A where exists (select 1 from Orders B where B.contact_id=a.contact_id)
Not Exists
Select * from contacts A where not exists (select 1 from Orders B where
B.contact_id=a.contact_id)
3. SQL DML (Data manipulation language)
Introduction to RDBMS and SQL
InsertUpdate
Insert examples insert into Contact (Fst_name, Last_name) values (‘John’, ‘Smith’);
insert into Contact values (‘John’, ‘Smith’….);
insert into Contact (Fst_name, Last_name) select First, Last from AnotherContactTable;
Update examples Update Contact set Fst_Name=Last_Name where Fst_Name is
null;
Update Contact set Fst_Name = ‘Cliff’ where last_name=‘Luchsinger’;
Update Contact A set A. Company_id = (select b.company_id from
Company B where A.phone_number=b.phone_number);
3. SQL DML (Data manipulation language)
Introduction to RDBMS and SQL
DeleteTruncate
Delete examples Delete from Contact where last_upd < sysdate – 5*365 ;
Delete Contact where last_upd < sysdate – 5*365 ;
Delete Contact A where not exists (select 1 from Orders B where B.contact_id=A.contact_id);
Truncate example Truncate table Contact
3. SQL DML (Data manipulation language)
Introduction to RDBMS and SQL
“Decode” function in Oracle
Select Event, Year, decode(Month, 1, ‘Jan’, 2, ‘Feb’,….11,’Nov’,’Dec’) from History;
Decodes can be nested.
4. Vendor enhancements to SQL – Some Example
SQL Server has an equivalent function called iff.
Introduction to RDBMS and SQL
Problem – You forgot to create unique indexes on a table and by mistake, duplicate records got loaded. Even the primary keys are dups. You need to clean up the table such that only one record is left behind.
Delete DupTable A where A.Rowid < (select max(B.Rowid) from DupTable B where b.primary_key=a.primary_key);
5. Example of a tough SQL
Introduction to RDBMS and SQL
Setup one data source. Connect to this datasource using MS-ACCESS
6. Set-up ODBC
Introduction to RDBMS and SQL
See your training folder for the “exercises-RDBMS, SQL, PLSQL.ppt”
7. Exercises
Introduction to RDBMS and SQL
About the Author
Ashish Raghute currently works as the IT Director at Fleetwood
Enterprises, Inc. , USA’s leader in recreational vehicle sales and a leading producer and retailer of manufactured housing. Prior to joining Fleetwood, Ashish was a Principal at IBM Business Consulting Services and Principal Consultant at PricewaterhouseCoopers Consulting. For more than 15 years, Ashish has guided companies of various sizes from dot net startups to Fortune 1000 to successfully realize their IT vision in the areas of CRM, ERP, Data Warehousing and E-Business. Ashish can be contacted via email at raghute@yahoo.com.
top related