SQL Unit 11 Inserting, Updating, and Deleting Data; Changing the Database Design

1. SQL Unit 11Inserting, Updating, and Deleting Data; Changing the Database Design Kirk Scott

2. 11.1 The Keywords INSERT, UPDATE, and DELETE11.2 Changing the Design of a Database with the Keywords ALTER, ADD, and DROP

3. 11.1 The Keywords INSERT, UPDATE, and DELETE

4. 11.1.1 The Keywords INSERT INTO and VALUES for Inserting Data into Tables • Microsoft Access has a simple graphical interface for inserting data into tables. • It's essentially the same as inserting data into a spreadsheet. • You can type new data into a row, field by field. • This will be mentioned off and on in this unit and again in the next unit.

5. It is also possible to insert a single row of data into a table using an SQL command. • It's easier to insert a single row using the graphical user interface • The SQL commands are very useful because they make it possible insert, update, and delete multiple rows at a time.

6. Just like with the CREATE commands of the previous unit, the commands in this unit will be saved as queries. • These commands do not retrieve data from table. • They change the contents of the table. • When you try to run the queries, you will be prompted whether or not you want to change the table.

7. The Keywords for Inserting • This is the keyword phrase for the insertion itself: • INSERT INTO TableName • In simple insertions it is paired with this: • VALUES(fields or values here) • For more complicated insertions, the INSERT INTO will be followed by a query

8. Inserting a single row • This query will insert a single row into the table • It isn't necessary to list all of the fields in the table because a value for each is provided in the right order in the list of values. • INSERT INTO Car • VALUES('a1b2c', 'Ford', 'Mustang', '2007', 24000, 22000)

9. For all practical purposes, new records are appended at the end of existing records • This is where data entry is done in the graphical user interface. • Remember that the order of records in a relational table is of no theoretical meaning • Therefore, it is of no real interest where, in fact, an inserted record goes.

10. 11.1.2 Inserting with NULL Values • The previous example provided a value for every field in the table. • Assuming that fields do not have the constraint NOT NULL applied to them, it is possible to insert new records with null values.

11. In this example, the nulls come in the fields at the end • They could be used for any of the field values (although preferably not for the primary key field). • INSERT INTO Car • VALUES('3d4e5', 'Dodge', 'Caravan', '2000', NULL, NULL)

12. The system will also accept a list of values shorter than the list of fields in a table. • If the number of values given is shorter, those values will be put into the table's fields, beginning with the first • The fields at the end, for which no values are provided, will be null by default. • In other words, the following example has the same effect as the previous one.

13. Notice that this approach is only practical when the nulls occur in the fields at the end: • INSERT INTO Car • VALUES('3d4e5', 'Dodge', 'Caravan', '2000')

14. 11.1.3 Specifying the Fields to Insert Into • If you specify just the name of the table in an insert query, by default, the query applies to all of the fields of the table, in order. • It is also possible to explicitly list just the fields of interest. • Then it is not necessary to insert NULL into fields of no interest. • Instead, the list of values only has to contain values for the fields of interest. • NULL will be inserted into the other fields by default.

15. The following example will by default provide null values for fields that aren't specified, and those unspecified fields could be anywhere among the fields of a table: • INSERT INTO Car(vin, make, year, dealercost) • VALUES('f6g7h', 'Chevrolet', '2002', 6000)

16. 11.1.4 Inserting Multiple Records by Including SELECT with INSERT • The following examples show the power of a language like SQL over a graphical user interface for inserting records into tables. • The examples will be artificially simple so that there is no confusion about the tables and fields in question. • The syntax also turns out to be simple, but this is not artificial.

17. To accomplish the same thing using the graphical user interface would not be as easy as using SQL. • You might observe that what is being accomplished here is reminiscent of some of the steps in the unit on temporary tables. • In these examples, tables exist, and it’s possible to move subsets of their records from one to the other.

18. Let this table be created for the following example. • Mfr stands for manufacturer and coname stands for company name. • It would appear that coname, the only field, would be the primary key field. • For the time being, do not be concerned that the primary key field of the table isn’t designated. • CREATE TABLE Mfr • (coname TEXT(18))

19. Assume that a set of records has been entered into the Car table. • Then the following command will insert all of the values of the make field from the Car table into the coname field of the Mfr table. • Notice that this insert command now truly takes the form of a query. • INSERT INTO Mfr(coname) • SELECT make • FROM Car

20. This example can be expanded into a full-scale query by adding a WHERE clause to the SELECT. • Anything you can do in a regular query you can do in an insertion query: • INSERT INTO Mfr(coname) • SELECT make • FROM Car • WHERE year > '2000'

21. Notice that what goes into the Mfr table are the results of a query, and the results of a query might not consist of a set of unique rows. • Since the Mfr table has only one field, presumably coname would have to be the primary key field. • On the other hand, coname wasn't designated as the primary key in the table definition.

22. In this example we're relying on the system to maintain a hidden primary key field. • The system does have the ability to do this. • Under that condition, duplicate values could be inserted into the coname field if the SELECT returns duplicate values.

23. 11.1.5 Matching Fields on Insertions with SELECT • For the sake of a few more illustrative examples, let the definition of the Mfr table be expanded. • The fields prodid, product id, and purchcost, purchase cost have been added to the table. • They are of the same type and size as corresponding fields in the Car table.

24. This table design still doesn't have a primary key, but that will not be important for the examples: • CREATE TABLE Mfr • (coname TEXT(18) • prodid TEXT(18) • purchcost CURRENCY)

25. Assume that some data has been entered into both the Car and Mfr tables. • It would not be possible to make a query like this one work, because the number of fields in the two tables doesn't match: • INSERT INTO Mfr • SELECT * • FROM Car

26. However, this will work: • INSERT INTO Mfr • SELECT make, model, dealercost • FROM Car

27. Going in the other direction, this will also work: • INSERT INTO Car(make, model, dealercost) • SELECT * • FROM Mfr

28. It is also possible to SELECT a subset of fields from both tables. • For example: • INSERT INTO Mfr(coname, purchcost) • SELECT make, dealercost • FROM Car

29. Going in the other direction, you could also do this: • INSERT INTO Car(make, dealercost) • SELECT coname, purchcost • FROM Mfr

30. These kinds of insert queries handle nulls like the earlier, one-row examples. • Suppose that in the query a source field or value isn't specified for a field in the destination table. • Suppose also that that the destination field isn’t defined with the “not null” constraint. • Then the destination field will take on the value null by default.

31. 11.1.6 Mismatching Fields on Insertions with SELECT • The database management system doesn't know the "meaning" of fields, and can't protect you against mistakes like this one: • INSERT INTO Mfr(coname, prodid) • SELECT model, make • FROM Car

32. The order of the respective fields is reversed in the INSERT and the SELECT. • Since the reversed fields are of the same type, the system will not complain. • Even if the fields are of different sizes, some systems might not complain • And if they are of radically different types, like text and numeric, it's hard to say whether you'll get an error message or garbage data in your tables.

33. 11.1.7 The Keywords UPDATE and SET for Updating the Data in Tables • Data in tables can be updated using the graphical user interface. • As with insertion, if the update applies to a single record, the graphical user interface is easy to use. • However, it's also possible to update multiple records at a time using SQL.

34. The Keywords for Updating • This is the keyword for the update itself: • UPDATE TableName • It is paired with this: • SET field = value • For more complicated updates, this can be followed by a WHERE clause which specifies a subset of rows to update • Without the WHERE clause, a simple update query affects all of the rows in a table

35. Here is an example of an update query that would affect all of the records in a table: • UPDATE Mfr • SET purchcost = 0

36. Which records are affected by an update can be controlled by adding a WHERE clause to the SET. • For example: • UPDATE Mfr • SET purchcost = 0 • WHERE coname = 'Ford'

37. Before executing any queries of this form, you should make sure that this is what you want to do. • In a system like Microsoft Access there is no rollback (i.e., no "undo" option), so any old data is completely wiped out by an update. • When running a query like this, Access will prompt you to see if you really want to make the changes.

38. 11.1.8 The Keyword DELETE for Deleting Records from Tables • Deleting records is like inserting and updating. • It can be done from the graphical user interface, but it is also possible to write SQL queries that apply to multiple records at the same time.

39. The Keywords for Deleting • This is the keyword for the update itself: • DELETE * • The * signifies all columns—namely a complete row • It is paired with this: • FROM TableName • For more complicated updates, this can be followed by a WHERE clause which specifies a subset of rows to be deleted • Without the WHERE clause, a simple deletion query deletes all of the rows from a table

40. If you wanted to completely clear a whole table, you could run this query. • Notice the use of *. • It is not possible to run deletion query and pick only a subset of columns: • DELETE * • FROM Mfr

41. You can also pick which rows to delete by adding a WHERE clause: • DELETE * • FROM Mfr • WHERE coname = 'Dodge' AND purchcost < 10000

42. Just like updates, it is not possible to undo deletions, so you should be careful when using this. • The system will prompt you for a confirmation before doing the deletion. • Keep in mind that DELETE eliminates records from a table • You will see shortly that the keyword DROP is used to eliminate elements like fields and tables from a database design.

43. 11.2 Changing the Design of a Database with the Keywords ALTER, ADD, and DROP

44. 11.2.1 The Keywords ALTER TABLE and ADD for Adding New Fields to Tables • If the design of a database is done carefully, it should not be necessary to change the designs of any of its tables after they have been created—but sometimes this happens. • Designs can be changed using the graphical user interface, and they can also be changed using SQL syntax. • A relatively trouble-free change would be the inclusion in a table of a new field that wasn't in the original design.

45. The Keywords for Changing the Design of a Table • This keyword phrase identifies the table to be changed: • ALTER TABLE TableName • It is paired with this: • ADD fieldname

46. For example, it might be of interest to record the color of a car. • A new field, named color, can be added to the Car table with the following command. • Notice that the data type of the new field has to be specified. • If there are already records in the table, the value of the new field for those records will be NULL:

47. ALTER TABLE Car • ADD color TEXT(6)

48. 11.2.2 The Keywords ALTER COLUMN for Changing Existing Fields in Tables • It is also possible to change the type of a field that already exists in a table. • Some changes are relatively simple • For example, you could make a text field wider without complications • Or you could change an integer type numeric field to one that can hold decimal places without complications

49. If you are making radical changes in type, from text to numeric or vice-versa, this can be problematic • Also, if you are changing to a size or type which can hold less data, the change can be problematic. • If there are already records in the table with data in the field in question, this data may be damaged or lost.

50. The Keywords for Changing the Specifications for a Column in the Table • This keyword phrase identifies the table to be changed: • ALTER TABLE TableName • It is paired with this: • ALTER COLUMN fieldname and specification