Lec7 dml.ppt - Lecture 4 Intro to SQL DML Outline DML Data Manipulating Language and Transactions Views(Virtual Tables in SQL Triggers in SQL Lecture

Lec7 dml.ppt - Lecture 4 Intro to SQL DML Outline DML Data...

This preview shows page 1 out of 17 pages.

Unformatted text preview: Lecture 4 Intro to SQL DML Outline DML Data Manipulating Language and Transactions Views (Virtual Tables) in SQL Triggers in SQL Lecture Resources Jeffrey A. Hoffer, Modern Database Management- Ch6 + Ch7 Ramez El masri, Fundamentals Of Database Systems- Ch4+Ch5 Data manipulation language (DML) Is a family of SQL statements used for inserting, deleting and updating data in a database. Performing read-only queries of data is sometimes also considered a component of DML. Data manipulation languages have their functional capability organized by the initial word in a statement, which is almost always a verb. In the case of SQL, these verbs are: INSERT INTO ... VALUES ... UPDATE ... SET ... WHERE ... DELETE … FROM ... WHERE … 3 Insert Statement Adds one or more rows to a table INSERT INTO Table VALUES Inserting into a table (value-list) (value-list) • VALUE – “s” Sequence!!! Inserting a record that has some null attributes requires identifying the fields that actually get data (field-list) VALUES (value- 4 Insert Statement (cont) Inserting from another table • A SUBSET from another table 5 5 Insert Statement (cont) Specify the relation name and a list of values for the tuple 6 Update Statement Modifies data in existing rows UPDATE Table-name SET Attribute = Value WHERE Criteria-to-apply-the-update Note: the WHERE clause may be a subquery 7 7 Update Statement(cont) Modify attribute values of one or more selected tuples Additional SET clause in the UPDATE command Specifies attributes to be modified and new values 8 8 Addendum: Comparison of ALTER, INSERT, and UPDATE ALTER: changing the columns of the table ALTER TABLE CUSTOMER_T ADD COLUMN… COLUMN INSERT: adding records based on the existing table INSERT INTO CUTTOME_T VALUES (… ) UPDATE: changing the values of some fields in existing records UPDATE PRODUCT_T SET …WHERE… 9 Delete Statement Removes rows from a table Delete certain rows DELETE FROM CUSTOMER_T WHERE CUSTOMERSTATE = ‘HI’; Delete all rows DELETE FROM CUSTOMER_T; Careful!!! 10 10 Delete Statement(cont) Includes a WHERE clause to select the tuples to be deleted 11 Merge Statement •Makes updating a table easier •allows combination of Insert and Update in one statement •Useful for updating master tables with new data •Many database applications need to update master tables with new data. •Example: •A Purchases_T table, might include rows with data about new products and rows that change the standard price of existing products. • Updating Product_T can be accomplished by using INSERT to add the new products and UPDATE to modify Standard Price. •DBMSs can accomplish the update and the insert in one step by using MERGE: 12 Interpretation? Merge Statement 1 Views (Virtual Tables) in SQL Concept of a view in SQL Single table derived from other tables Considered to be a virtual table Syntax of CREATE VIEW: CREATE VIEW view-name AS SELECT (provides the rows &columns of view) Example: Example CREATE VIEW ORDER_TOTALS_V AS SELECT PRODUCT_ID PRODUCT, SUM(STANDARD_PRICE*QUANTITY) TOTAL FROM INVOICE_V GROUP BY PRODUCT_ID; 1 Advantages of Views Simplify query commands Assist with data security (but don't rely on views for security, there are more important security measures) Enhance programming productivity Contain most current base table data Use little storage space Provide customized view for user Establish physical data independence Disadvantages of Views Use processing time each time view is referenced May or may not be directly updateable 1 Specification of Views in SQL CREATE VIEW command Give table name, list of attribute names, and a query to specify the contents of the view 1 Specification of Views in SQL (cont’d.) Specify SQL queries on a view View always up-to-date Responsibility of the DBMS and not the user DROP VIEW command Dispose of a view 1 ...
View Full Document

  • 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 You can ask ( soon) You can ask (will expire )
Answers in as fast as 15 minutes