7+-+Intro+to+SQL

7+-+Intro+to+SQL - Intro to SQL Douglas Havelka Miami...

Info iconThis preview shows pages 1–15. Sign up to view the full content.

View Full Document Right Arrow Icon

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full DocumentRight Arrow Icon

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full DocumentRight Arrow Icon

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full DocumentRight Arrow Icon

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full DocumentRight Arrow Icon

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full DocumentRight Arrow Icon

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full DocumentRight Arrow Icon

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full DocumentRight Arrow Icon
This is the end of the preview. Sign up to access the rest of the document.

Unformatted text preview: Intro to SQL Douglas Havelka Miami University SQL or sequel Standard, portable DDL and DML Non-procedural Not standalone Query = statement executed Data Definition Creates files and structures Authentication Schema, related objects Tables, indexes, views, reports Data types Nature Use Numbers as text Phone, zip Characters Faster, no math, sorting Supported data types: Number(L,D), Integer, Smallint, Decimal(L,D) Char(L), Varchar(L), Varchar2(L) Date, Time, Timestamp Real, Double, Float Interval day to hour Many other types Create Table DROP TABLE P; DROP TABLE V; CREATE TABLE V ( V_CODE INTEGER PRIMARY KEY, V_NAME VARCHAR(35) NOT NULL, V_CONTACT VARCHAR(15) NOT NULL, V_AREACODE CHAR(3) NOT NULL, V_PHONE CHAR(8) NOT NULL, V_STATE CHAR(2) NOT NULL, V_ORDER CHAR(1) NOT NULL); CREATE TABLE P ( P_CODE VARCHAR2(10) PRIMARY KEY, P_DESCRIPT VARCHAR2(35) NOT NULL, P_INDATE DATE NOT NULL, P_QOH NUMBER NOT NULL, P_MIN NUMBER NOT NULL, P_PRICE NUMBER(8,2) NOT NULL, P_DISCOUNT NUMBER(5,2) NOT NULL, V_CODE NUMBER); Creating Tables If you have two tables related in a 1:M relationship (say Customer to Orders), which one would you create first and why? Constraints PK FK Default value Not null Unique Customized (check) CREATE TABLE P ( P_CODE VARCHAR2(10) CONSTRAINT PRODUCT_P_CODE_PK PRIMARY KEY, P_DESCRIPT VARCHAR2(35) UNIQUE, P_INDATE DATE NOT NULL, CONSTRAINT PROD_CK1 CHECK (P_INDATE > TO_DATE(‘01-JAN-2010’,’DD-MON-YYYY’)), P_PRICE NUMBER(8,2) DEFAULT 0.00 NOT NULL, P_DISCOUNT NUMBER(5,2) NOT NULL, V_CODE NUMBER, CONSTRAINT PRODUCT_V_CODE_FK FOREIGN KEY (V_CODE) REFERENCES VENDOR ) Indexes Creates an index An ordered list with pointers To speed searches For report presentation (ordering) Adds overhead to the system Data Manipulation Actions Add data View data Change data Delete data Save, write to disk “rollback” Commands INSERT INSERT INTO V VALUES(21225,'Bryson, Inc.' ,'Smithson','615','223- 3234','TN','Y'); Inserting data into our V an P tables INSERT INTO V VALUES(21225,'Bryson, Inc.' ,'Smithson','615','223-3234','TN','Y'); INSERT INTO V VALUES(21226,'SuperLoo, Inc.' ,'Flushing','904','215-8995','FL','N'); INSERT INTO V VALUES(21231,'D\&E Supply' ,'Singh' ,'615','228-3245','TN','Y'); INSERT INTO V VALUES(21344,'Gomez Bros.' ,'Ortega' ,'615','889-2546','KY','N'); INSERT INTO V VALUES(22567,'Dome Supply' ,'Smith' ,'901','678-1419','GA','N'); INSERT INTO V VALUES(23119,'Randsets Ltd.' ,'Anderson','901','678-3998','GA','Y'); INSERT INTO V VALUES(24004,'Brackman Bros.' ,'Browning','615','228-1410','TN','N');INSERT INTO V VALUES(24004,'Brackman Bros....
View Full Document

This note was uploaded on 04/01/2012 for the course MIS 245 taught by Professor Havelka during the Spring '12 term at Miami University.

Page1 / 78

7+-+Intro+to+SQL - Intro to SQL Douglas Havelka Miami...

This preview shows document pages 1 - 15. Sign up to view the full document.

View Full Document Right Arrow Icon
Ask a homework question - tutors are online