1.Introduction to SQL

SQL

Agenda

What is SQL – Structured Query Language
Database
DBMS – Database management System
RDBS – Relational Database Management System
Table/Column/Rows

What is SQL?

Structure – Description of the table
Query – Insert/Update/Delete/Retrieve the information from the table
Language – Software which allows to build object

Example: PURCHASE TABLE:

PONO ITEM QTY PRICE DOP

800 LP001 10 1500 10-JAN-12
801 LP002 20 2500 12-FEB-13

802 LP003 11 2200 15-MAR-15

Example: Structure of the table

PONO NUMBER
ITEM CHAR
QTY NUMBER
PRICE NUMBER
DOP DATE

Example: Data in the table PURCHASE

PONO ITEM QTY PRICE DOP

800 LP001 10 1500 10-JAN-12

Database – software which allows to Creating databases, maintain databases

RDBS – Relational Database Management System (Build relation between tables)
1. One-to-one
2. One-to-many
3. Many-to-many

One-to-One

Supplier Purchase

Vendor_Id Vendor_Name PONO Item Qty Price Vendor_ID
————————— ——————————————
1001 Dell 800 lp001 10 1500 1001
1002 Hp 801 lp002 15 2500 1001
1003 HCL 802 lp002 25 1100 1002
—————————- ——————————————

One-to-Many

Supplier Purchase Invoice

Vendor_Id Vendor_Name PONO Item Qty Price Vendor_ID InvNo Amt PONO
————————— —————————————— ——————–
1001 Dell 800 lp001 10 1500 1001 771 15000 800
1002 Hp 801 lp002 15 2500 1001
1003 HCL 802 lp002 25 1100 1002
—————————- —————————————— ———————

Many-to-Many

Supplier Purchase Invoice Payments

Vendor_Id Vendor_Name PONO Item Qty Price Vendor_ID InvNo Amt PONO Payid AmtPaid Invno Vendor_id
————————— —————————————— ——————– ———————————
1001 Dell 800 lp001 10 1500 1001 771 15000 800 990 14000 771 1001
1002 Hp 801 lp002 15 2500 1001
1003 HCL 802 lp002 25 1100 1002
—————————- —————————————— ——————— ———————————

Supplier PONO Item InvAmt AmtPaid

Dell 800 lp001 15000 14000

Example of Query to get above result.
SELECT a.vendor_name,
b.pono,
b.item,
c.amt,
d.amtpaid
from supplier a,
purchase b,
invoice c,
payments d
where a.vendor_id = b.vendor_id
b.pono = c.pono
c.invno = d.invno
d.vendor_id = a.vendor_id


Next session

Oracle SQL Commands

  1. DDL – Data definition Language
  2. DML – Data Manipulation Language
  3. DCL – Data Control Language
  4. TCL – Transactional Control Language
  5. DQL – Data Query Language

Next topic:
Data Types
Constraints