Now You Try It
Add another department to Commonwealth Auto. The only values you have right now are the department ID, 6060, the department name, Claims, and the division code, D09. The department name and division code columns contain character data. You do not know the ID of the head of the department.
Enter a SELECT statement to display all departments in order by department id and confirm your addition.
The result looks like this:
DEPT_ID DEPT_HEAD_ID DIV_CODE DEPT_NAME ------- ------------ -------- --------- 1100 2246 D02 PURCHASING - USED CARS 1110 1765 D04 PURCHASING - NEW CARS 1120 2004 D06 PURCHASING - SERVICE 2200 2180 D02 SALES - USED CARS 2210 2010 D04 SALES - NEW CARS 3510 3082 D02 APPRAISAL - USED CARS 3520 3769 D04 APPRAISAL NEW CARS 3530 2209 D06 APPRAISAL - SERVICE 4040 1234 D09 Audit 4200 1003 D04 LEASING - NEW CARS 4500 3222 D09 HUMAN RESOURCES 4600 2096 D06 MAINTENANCE 4900 2466 D09 MIS 5000 2466 D09 CORPORATE ACCOUNTING 5100 2598 D06 BILLING 5200 2894 D09 CORPORATE MARKETING 6000 1003 D09 LEGAL 6060 <null> D09 Claims 6200 2461 D09 CORPORATE ADMINISTRATION 19 rows processed
If your results do not match what you see above, check Review Answers for Chapter 8 for the correct SQL syntax. Remember that result tables may be shortened in this guide.
Using INSERT with Column Names
If you want to insert only one or a few columns in a row, specify the column names.
How It's Done
Suppose you want to add yet another department to the company, but you have only a department ID, department name, and division code. Enter:
insert into department (dept_id, dept_name, div_code)
values (5050, 'Research', 'D09');
The column names are in parentheses and separated by commas.
The values must be given in the correct column order. You can use the word NULL when you don't have a value in a column as long as the column accepts null values.
If you do not specify a column and a value for that column, a null value will be inserted for you. If the column does not accept null values, the insert is rejected.
Enter a SELECT statement to display the DEPARTMENT table in Department id order to confirm the insertion.
The result looks like this:
DEPT_ID DEPT_HEAD_ID DIV_CODE DEPT_NAME ------- ------------ -------- --------- 1100 2246 D02 PURCHASING - USED CARS 1110 1765 D04 PURCHASING - NEW CARS 1120 2004 D06 PURCHASING - SERVICE 2200 2180 D02 SALES - USED CARS 2210 2010 D04 SALES - NEW CARS 3510 3082 D02 APPRAISAL - USED CARS 3520 3769 D04 APPRAISAL NEW CARS 3530 2209 D06 APPRAISAL - SERVICE 4040 1234 D09 Audit 4200 1003 D04 LEASING - NEW CARS 4500 3222 D09 HUMAN RESOURCES 4600 2096 D06 MAINTENANCE 4900 2466 D09 MIS 5000 2466 D09 CORPORATE ACCOUNTING 5050 <null> D09 RESEARCH 5100 2598 D06 BILLING 5200 2894 D09 CORPORATE MARKETING 6000 1003 D09 LEGAL 6060 <null> D09 Claims 6200 2461 D09 CORPORATE ADMINISTRATION 20 rows processed
|
Copyright © 2014 CA.
All rights reserved.
|
|