L06_SQL_part-2_v3.3.pptx - SQL | Part 2 Database Theory and...

This preview shows page 1 - 11 out of 32 pages.

SQL | Part 2 Database Theory and Design
LECTURE OUTLINE Data Manipulation Language (D M L) Basics: Querying a single table
SET UP ENVIRONMENT 1. Download is_6420_main_db_v1.1.sql from Canvas 2. Open database client 3. Create a new database, is_6420_main_db 4. Set new database as active 5. Open and run the file is_6420_main_db_v1.0.sql (tables are now created and populated with data)
DML | INSERT ROWS Two styles of insert statements: 1. INSERT INTO table_name VALUES ({list of values}) ; 2. INSERT INTO table_name ({list of attributes}) VALUES ({list of values}) ;
DML | INSERT ROWS | IMPLICIT COLUMNS INSERT INTO product VALUES (109, 'Dell D120 Laptop', 789.54) ; Note: the order of the values must the same as the table columns
DML | INSERT ROWS | EXPLICIT COLUMNS I NSERT INTO product (product_id, product_name) VALUES (110, 'Apple Keyboard') ; INSERT INTO product (product_name, product_id) VALUES ('Tablet',111) ;
DML | DELETE ROWS Two styles of delete statements: 1. DELETE FROM table_name; --deletes all rows 2. DELETE FROM table_name WHERE condition ; --deletes rows that match condition
DML | DELETE ROWS | STYLE 1 DELETE FROM product; --style 1; won’t work due to dependency
DML | DELETE ROWS | STYLE 2 DELETE FROM product WHERE product_id = 111 ; --style 2
DML | UPDATE ROWS Two styles of update statements: UPDATE table_name SET attribute = new value; UPDATE table_name SET attribute = new value WHERE condition ;

  • Left Quote Icon

    Student Picture

  • Left Quote Icon

    Student Picture

  • Left Quote Icon

    Student Picture