Examples Adding a new item INSERT INTO items itemID description price VALUES

Examples adding a new item insert into items itemid

This preview shows page 66 - 70 out of 82 pages.

Examples: -Adding a new item INSERT INTO items (itemID, description, price) VALUES (290, book – Architecture, 15 000) ; Or INSERT INTO items VALUES (290, book – Architecture, 15 000) ; Adding to the list small_purchases the itemID and item quantities purchased in less than five copies INSERT INTO small_purchases (itemID, quantity) SELECT itemID, quantity, FROM purchases WHERE quantity < 5 ; Forbidden query: the duplication of a table elements by an instruction INSERT with sub-select on the same table INSERT INTO items SELECT * FROM items ; Principles of Database Systems 66
Image of page 66
Unit 7. SQL Operations on Databases 5.Modification The instruction UPDATE allows to modify the rows of a table. The expression relative to the modification can be a constant, or an arithmetic expression, or the result of a nested SELECT query. Examples: -Make a promotion of 10% on the items number 210 UPDATE items SET price = price*0.90 WHERE itemID = 210 ; -Increase the price by 3% of all items with Jonathan SOGOBA is author UPDATE items SET price = price*1.03 WHERE authorID = (SELECT authorID FROM (SELECT authorID FROM authors WHERE name = ‘Jonathan SOGOBA’ ) ); Because of integrity issues, it is not advised to make UPDATE on columns used in a primary key. Some DBMS verify the database integrity after each SQL instruction. In this case, update on columns involved in a primary key does not cause problems. But, in DBMS that do check the database integrity after each row modification, a key unicity problem. 6.Deletion The instruction DELETE allows to remove the rows in a table according to a given criterion. Examples: -Delete the items which price is more than 1 500 F DELETE FROM items WHERE price > 1 500 ; -Delete the items which number exists in the table ITEMS2 DELETE FROM items WHERE itemID IN (SELECT itemID FROM ITEMS2) ; -Clear the table purchases DELETE FROM purchases ; The table purchases will exist even after above instruction. But, it is emptied. 67
Image of page 67
Unit 8.Implementing Databases using SQL Code Activity Objective By the end of the activity, the learner should be able to: -Design a simple database using SQL code Summary of the learning activity This activity gives the learner hands-on skill in implementing database using SQL Activity Using tables provided below, implement SQL code. Examples of table design from ERD Order table Field Data type size example Order_ID Alphanumeric 10 OR_0001_13 eg OR for order_number_year Customer_ID Alphanumeric 6 Eg Cu_001 Order_Date Date Fill the other details for the entities as shown aboveCustomer table (create it) Field Data type size example Customer_ID Alphanumeric 6 CU-001 Customer_fname Alphanumeric 6 John Customer_Lname Alphanumeric 6 M Customer_Oname Alphanumeric 6 Otieno Customer_City Alphabetic 15 Nairobi Principles of Database Systems 68
Image of page 68
Unit 8.Implementing Databases using SQL Code Order_Item Table (create it) Field Data type size example Item_ID Alphanumeric 10 IT_0001_13 eg OR for It_number_year Order_ID Alphanumeric 10 OR_0001_13 eg OR for order_number_year Item_Qnty numeric 3 Eg 23 Item table (create) Field Data type size example Item_ID Alphanumeric 10 IT_0001_13 eg OR for It_number_year Item_Description Alphanumeric 12 Books Item_price Numeric 3 25 Customer Table **************
Image of page 69
Image of page 70

  • Left Quote Icon

    Student Picture

  • Left Quote Icon

    Student Picture

  • Left Quote Icon

    Student Picture