17.1 INSERT Data

  • INSERT INTO and DELETE FROM statement are used to add or delete rows of data to a table.

  • The VRG database sample data is on text pages 322-326.

17.1.1 INSERT INTO & DELETE FROM

Sample INSERT and DELETE statement:

ARTIST TABLE

-- DELETE THE ARTIST IF WE WANT TO REPEAT RUNNING INSERT INTO STATEMENT
DELETE FROM ARTIST WHERE artistid = 1;

-- INSERT A NEW ARTIST ROW INTO ARTIST TABLE
INSERT INTO ARTIST
    (ArtistID, LastName, FirstName, Nationality, DateOfBirth, DateDeceased)
    VALUES (
    1, 'Miro', 'Joan', 'Spanish', 1893, 1983);

-- TEST IF THE NEW ROW HAS BEEN INSERTED SUCCESSFULLY   
select * from artist;   

17.1.2 INSERT MULTIPLE ROWS IN ONE STATEMENT

-- BULK INSERT
INSERT INTO ARTIST
    (ArtistID, LastName, FirstName, Nationality, DateOfBirth, DateDeceased)
    VALUES 
    (2, 'Kandinsky', 'Wassily', 'Russian', 1866, 1944), 
    (3, 'Klee', 'Paul', 'German', 1879, 1940);
    
-- TEST IF THE NEW ROWs HAVE BEEN INSERTED SUCCESSFULLY 
select * from artist;

17.1.3 ENCRYPT IN CUSTOMER TABLE

-- DELETE THE CUSTOMER IF WE WANT TO REPEAT RUNNING INSERT INTO STATEMENT
-- SET SQL_SAFE_UPDATES=0;
DELETE FROM CUSTOMER WHERE customerid = 1000;
-- SET SQL_SAFE_UPDATES=1;

-- INSERT A NEW CUSTOMER ROW INTO CUSTOMER TABLE
INSERT INTO CUSTOMER
    (CustomerID, LastName,  FirstName, EmailAddress, EncryptedPassword,
     Street, City, State, ZIPorPostalCode, Country,
     AreaCode, PhoneNumber)
    VALUES (
    1000, 'Janes', 'Jeffrey', 'Jeffrey.Janes@somewhere.com', DES_ENCRYPT('new'),'123 W. Elm St', 'Renton', 'WA', '98055', 'USA',
    '425', '543-2345');

-- TEST ENCRYPTION OF PASSWORD  
select * from customer where EncryptedPassword = DES_ENCRYPT('new');    

17.1.4 Insert a new interest between a customer and an artist

The customer “David Smith” has expressed his interest in the artist “Joan Miro”. Write the new fact into the CUSTOMER_ARTIST_INT table.

select artistid from artist where lastname='Miro' and firstname='Joan';
select customerid from customer where lastname='Smith' and firstname='David';
INSERT INTO CUSTOMER_ARTIST_INT VALUES (1, 1001);