7+-+Intro+to+SQL - IntrotoSQL DouglasHavelka...

Info icon This preview shows pages 1–16. Sign up to view the full content.

View Full Document Right Arrow Icon
Intro to SQL Douglas Havelka Miami University
Image of page 1

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

View Full Document Right Arrow Icon
SQL or sequel Standard, portable DDL and DML Non-procedural Not standalone Query = statement executed
Image of page 2
Data Definition Creates files and structures Authentication Schema, related objects Tables, indexes, views, reports
Image of page 3

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

View Full Document Right Arrow Icon
Data types Nature Use Numbers as text Phone, zip Characters Faster, no math, sorting
Image of page 4
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
Image of page 5

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

View Full Document Right Arrow Icon
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);
Image of page 6
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);
Image of page 7

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

View Full Document Right Arrow Icon
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?
Image of page 8
Constraints PK FK Default value Not null Unique Customized (check)
Image of page 9

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

View Full Document Right Arrow Icon
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 )
Image of page 10
Indexes Creates an index An ordered list with pointers To speed searches For report presentation (ordering) Adds overhead to the system
Image of page 11

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

View Full Document Right Arrow Icon
Data Manipulation Actions Add data View data Change data Delete data Save, write to disk “rollback”
Image of page 12
Commands INSERT INSERT INTO V VALUES(21225,'Bryson,  Inc.'    ,'Smithson','615','223- 3234','TN','Y');
Image of page 13

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

View Full Document Right Arrow Icon
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(24288,'ORDVA, Inc.'     ,'Hakford' ,'615','898-1234','TN','Y'); INSERT INTO V VALUES(25443,'B\&K, Inc.'      ,'Smith'   ,'904','227-0093','FL','N'); INSERT INTO V VALUES(25501,'Damal Supplies'  ,'Smythe'  ,'615','890-3529','TN','N'); INSERT INTO V VALUES(25595,'Rubicon Systems' ,'Orton'   ,'904','456-0092','FL','Y');
Image of page 14
INSERT INTO P VALUES('11QER/31','Power painter, 15 psi., 3-nozzle'     ,'03-NOV-2009',  8,  5,109.99,0.00,25595);
Image of page 15

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

View Full Document Right Arrow Icon
Image of page 16
This is the end of the preview. Sign up to access the rest of the document.

{[ snackBarMessage ]}

What students are saying

  • Left Quote Icon

    As a current student on this bumpy collegiate pathway, I stumbled upon Course Hero, where I can find study resources for nearly all my courses, get online help from tutors 24/7, and even share my old projects, papers, and lecture notes with other students.

    Student Picture

    Kiran Temple University Fox School of Business ‘17, Course Hero Intern

  • Left Quote Icon

    I cannot even describe how much Course Hero helped me this summer. It’s truly become something I can always rely on and help me. In the end, I was not only able to survive summer classes, but I was able to thrive thanks to Course Hero.

    Student Picture

    Dana University of Pennsylvania ‘17, Course Hero Intern

  • Left Quote Icon

    The ability to access any university’s resources through Course Hero proved invaluable in my case. I was behind on Tulane coursework and actually used UCLA’s materials to help me move forward and get everything together on time.

    Student Picture

    Jill Tulane University ‘16, Course Hero Intern