Chapter 7_2(2)

Chapter 7_2(2) - Chapter 7: SQL: DDL & DML Jinie Pak...

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

View Full Document Right Arrow Icon
Chapter 7: Jinie Pak Department of Information Systems UMBC 1
Background image of page 1

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

View Full DocumentRight Arrow Icon
D M   2 Learning Objectives Learn how to use the following SQL commands to manipulate a single table: Query (Read) Modification (Write) Single Table DML SELECT clause FROM clause WHERE clause GROUP BY, HAVING and ORDER AGGREGATE: SUM, MAX, MIN, COUNT, AVG
Background image of page 2
D M   3 Where are we? Logical  Design   Data Integrity   Data Manipulation Translate  ER into  relational  Model Normalization Understan Relational  Model How to translate? Data Structure Implementation SQL DDL DML DCL
Background image of page 3

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

View Full DocumentRight Arrow Icon
D M   4 Recap: SQL Data Definition Language (DDL) Define the database. CREATE, ALTER, or DROP a base TABLE or VIEW . Data Manipulation Language (DML) Manipulate the data in an existing database. SELECT, INSERT, UPDATE, or DELETE data. Data Control Language (DCL) Control user access to an existing database. GRANT or REVOKE user privileges.
Background image of page 4
D M   5 Recap So far, we know what is SQL, and how to create tables in SQL using DDL The most important operations are data manipulation operations. Relational algebra is the theoretical foundation of SQL DML
Background image of page 5

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

View Full DocumentRight Arrow Icon
D M   6 Relational Algebra: Operators Four traditional set operators (, , , ) Intersect () Union () Difference () Cartesian product () (or product ) Three special relational operators (, , ) Select () (also known as Restrict ) Project () Join ( )
Background image of page 6
D M   7 SQL DML Data Manipulation Language (DML) Manipulate the data in an existing database. SELECT, INSERT, UPDATE, or DELETE data. Query (read-only): SELECT Modification (write): INSERT, DELETE, UPDATE
Background image of page 7

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

View Full DocumentRight Arrow Icon
Summary: modification operations INSERT: INSERT INTO table_name [ attribute_list ] VALUES (. .) DELETE: DELEE table_name WHERE…… UPDATE UPDATE table_name SET attribute_name = new_value WHERE…
Background image of page 8
CASE: VIDEO SHOP CID CNAME CITY C1 C2 C3 C4 C5 Smith Jones Blake Clark Adams London Paris Paris London Athens FID FNAME PRICE F1 F2 F3 F4 F5 F6 Jurassic Park III The Others Senseless Dragon Heart 007 America Sweetheart 2.99 3.55 5.99 6.00 5.00 0.59 TYPE Action Horror Comedy Drama Action Comedy CID C1 C1 C1 C1 C1 C1 C2 C2 C3 C4 C4 C4 FID F1 F2 F3 F4 F5 F6 F1 F2 F2 F2 F4 F5 Date 1/3 10/2 9/4 1/2 1/1 8/1 1/3 1/4 12/2 1/2 1/3 11/4 RENT CUSTOMER FILM Date: 1/3: Jan. 3, 2005
Background image of page 9

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

View Full DocumentRight Arrow Icon
Practice 1 Create three tables in Oracle Customer Rent Film Integrity constraints Primary key Foreign key Constraint rent_fk_c foreign key cid references customer ON DELETE CASCADE; The price of a film ranges from 0.50 to 19.99 A film can only have the type of Action, Horror, Comedy, Drama, and Education
Background image of page 10
INSERT “insert” is used to add a new record to a table Q1. Add a new customer Mr. Brown, who lives in Columbia INSERT INTO customer VALUES ('C9', 'Brown', ‘Columbia'); Q2. Add a film “Harry Potter” with a price of 3.00
Background image of page 11

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

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

This note was uploaded on 02/28/2012 for the course IS 410 taught by Professor Chen,z during the Fall '08 term at UMBC.

Page1 / 60

Chapter 7_2(2) - Chapter 7: SQL: DDL & DML Jinie Pak...

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

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