1 / 12

Data Manipulation Language (DML)

Data Manipulation Language (DML). Modification of the Database - Deletion. Delete all account records at the Nablus branch delete from account where branch-name = “Nablus” Conceptually, delete is done in two steps:

riley-mcdonald
Download Presentation

Data Manipulation Language (DML)

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Data Manipulation Language (DML)

  2. Modification of the Database - Deletion • Delete all account records at the Nablus branchdelete from accountwhere branch-name = “Nablus” • Conceptually, delete is done in two steps: • find the tuples you want to delete:select * from accountwhere branch-name = “Nablus” • delete the tuples you found.

  3. Modification of the Database - Deletion • Delete all accounts at every branch located in Needham.deletefrom accountwhere branch-name in (select branch-name from branchwhere branch-city = “Needham”)

  4. Example Query • Delete the records of all accounts with balances below the average at the bankdeletefrom accountwhere balance < (selectavg (balance)from account)

  5. Modification of the database - Insertion • Add a new tuple to accountinsertinto account values (“Perryridge”, A-9732, 1200)To reorder attributes, specify attribute names explicitly:insertinto account (branch-name, balance, account-number)values (“Perryridge”, 1200, A-9732) • Add a new tuple to account with balance set to nullinsertinto account values ( “Perryridge”, “A-777”, null)

  6. Modification of the database - Updates • Increase all accounts with balance over $10,000 by 6%, all other accounts receive 5%. • Write two update statements:update accountset balance = balance *1.06where balance >10000update account set balance = balance *1.05where balance  10000 • the order is important • Solution use case

  7. Case Statement for Conditional Updates • Same query as before: Increase all accounts with balances over $10,000 by 6%, all other accounts receive 5%. update accountset balance = casewhen balance <= 10000 then balance *1.05else balance * 1.06end

  8. Replace old pid with the pid of “AI” Find the tuple relating “Dik Lee” to the “database” project Correct Update Procedure update works-on set pid = ( select id from project where name=`AI’ ) where eid = ( select id from employee where name = `Dik Lee’ ) and pid = ( select id from project where name = ‘database’ )

  9. SQL Data Definition Language

  10. Data Definition Language(DDL) including: • The schema for each relation. • The domain of values associated with each attribute. • Integrity constraints. • Security and authorization information for each relation.

  11. Domain Types in SQL • char(n) Fixed length character string, with user-specified length n. • varchar(n) Variable length character string, with user-specified maximum length n. • int integer. • smallint Small integer (subset of the integer domain type). • Numeric(p,d) Fixed point number, with user-specified precision of p digits, with d digits to the right of decimal point.

  12. Domain types in SQL (cont.) • real, double precision Floating point and double-precision floating point numbers. • float(n) Floating point number, with user-specified precision of at least n digits. • date Dates, containing a (4 digits) year, month and date. • time Time of day, in hours, minutes and seconds. • Null values are allowed in all the domain types.

More Related