Lecture_3_BasicSQL_print.pptx - Data Management Visual...

  • No School
  • AA 1
  • 49

This preview shows page 1 - 12 out of 49 pages.

Data Management & Visual Analytics MSCI:6050 FALL 2019 LECTURE 3: INTRODUCTION TO SQL 1
Image of page 1

Subscribe to view the full document.

Reminder HW2 Due HW3 Out Available on ICON 2
Image of page 2
Introduction to SQL S tructured Q uery L anguage (SQL) Most common and standard language for creating and querying relational databases Set-based declarative programming language Run on machines of all sizes from personal computers to large mainframes Many variations from vendor to vendor 3
Image of page 3

Subscribe to view the full document.

Types of SQL Commands Data Definition Language (DDL) commands Create, alter, and drop tables, views, and indexes Data Manipulation Language (DML) commands Core commands of SQL Insert, update, and query the data Issued interactively or embedded in a PL (e.g. Java, PHP) Data Control Language (DCL) commands Set up access and controls for DB administration Grant/revoke privileges and commit data 4 COMMIT; Make all changes permanent and visible to other users
Image of page 4
Common Data Types Type Description Example CHAR(x) Fixed length string with x characters char(2): ‘NY’, ‘IA’, ‘CA’, ‘FL’ … VARCHAR2(x) Text string with varying length up to x characters varchar2(20): ‘abc’, ‘John Smith’ … NUMBER A fixed and floating-point number up to 38 digits of precision 1,2,3, 105.2, 16384.995 … NUMBER(p, s) A number with precision p and scale s (p/s: number of digits before/after the decimal) NUMBER(3, 1): 123.4, 456.9… NUMBER(5, 0): 1, 12, 123, 12345 … DATE Date and Time information formatted. Default format: DD-MON-YY HH:MI:SS AM/PM Today is : ’13-SEP-2016’ ’27-JAN-2016 02:00:00 PM’ BOOLEAN Truth values TRUE, FALSE, or UNKNOWN 5 What data type do you use for the phone number or zip code?
Image of page 5

Subscribe to view the full document.

Create Tables (1) 6
Image of page 6
Create Tables (2) 7 Table Name Column Definition Table Constraint Note: no space is permitted in a table name or a column name
Image of page 7

Subscribe to view the full document.

Create Table (3) 8 Table Constraint Name Table Constraint Type Table Constraint Attribute Default value Referential integrity is enforced by the FK constraint
Image of page 8
Exercise Create Customer_T and Order_T CREATE TABLE Customer_T ( CustomerID NUMBER(3,0) NOT NULL, CustomerName VARCHAR2(25) NOT NULL, CustomerAddress VARCHAR2(30), CONSTRAINT Customer_PK PRIMARY KEY (CustomerID)); CREATE TABLE Order_T ( OrderID NUMBER(5,0) NOT NULL, OrderDate DATE DEFAULT SYSDATE, CustomerID NUMBER(3,0), CONSTRAINT Order_PK PRIMARY KEY (OrderID)); We will add the FK constraint shortly
Image of page 9

Subscribe to view the full document.

Alter Tables Syntax ALTER TABLE table_name alter_table_action; Some alter_table_actions ADD [COLUMN] column_definition MODIFY [COLUMN] column_definition ALTER [COLUMN] column_name SET DEFAULT default_value ALTER [COLUMN] column_name DROP DEFAULT DROP [COLUMN] column_name [ RESTRICT ] [ CASCADE ] ADD table_constraint 10
Image of page 10
Exercise Modify the length of CustomerAddress from 30 to 50 ALTER TABLE Customer_T MODIFY CustomerAddress VARCHAR2(50); Add 3 columns to the Customer_T table ALTER TABLE Customer_T ADD (CustomerCity VARCHAR2(20), CustomerState CHAR(2), CustomerPostalCode VARCHAR2(9)); Add FK constraint to the Order_T table ALTER TABLE Order_T ADD CONSTRAINT Order_FK FOREIGN KEY (CustomerID) REFERENCES Customer_T(CustomerID);
Image of page 11

Subscribe to view the full document.

Image of page 12
  • Fall '19

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

Ask Expert Tutors You can ask 0 bonus questions You can ask 0 questions (0 expire soon) You can ask 0 questions (will expire )
Answers in as fast as 15 minutes