answers.pks 3 KB
Newer Older
michael lundquist's avatar
michael lundquist committed
1
2
3
4
5
6
7
8
9
10
11
12
/*
problem1

Create a new table using the CREATE TABLE command. Use your first name
and your last name for the name of the table 
(for instance, for John Smith the name of the table will be JOHN_SMITH).
Make sure to include at least four different data types 
(CHAR, VARCHAR2, NUMBER, DATE) for the columns and appropriate
constraints (PK, NOT NULL, CHECK, UNIQUE). Use the DESCRIBE command 
to verify that the columns have been defined correctly.
*/

13
14
15
16
17
CREATE TABLE MICHAEL_LUNDQUIST (ID integer, firstName VARCHAR2(20) NOT NULL, lastName VARCHAR2(20) NOT NULL, addDay DATE DEFAULT SYSDATE, zip CHAR(5) NOT NULL, CONSTRAINT MICHAEL_LUNDQUIST_ID_pk PRIMARY KEY (ID));

DESC MICHAEL_LUNDQUIST;


michael lundquist's avatar
michael lundquist committed
18
19
20
21
22
23
24
25
26
27
28
29
/*
problem2

Use the INSERT command to insert a new record into the table created in 
Problem 1. When inserting a record, provide values for all columns. 
Use the default format for the date. Use the 
SELECT * FROM your_table_name; 
command to display the content of the table 
(make sure to substitute your_table_name with the actual name of your 
table).
*/

30
31
INSERT INTO MICHAEL_LUNDQUIST values (1, 'Michael', 'Lundquist', DEFAULT, '20194');

michael lundquist's avatar
michael lundquist committed
32
33
34
35
36
37
38
39
40
41
42
/*
problem3

Use the INSERT command to insert a new record into the table created in 
Problem 1. When inserting a record, provide values for selected columns 
only. Use the default format for the date. Use the 
SELECT * FROM your_table_name; 
command to display the content of the table 
(make sure to substitute your_table_name with the actual name of your table).
*/

43
44
INSERT INTO MICHAEL_LUNDQUIST (ID, firstName, lastName, addDay, zip) values (0, 'Michael', 'Lundquist', DEFAULT, '20194');

michael lundquist's avatar
michael lundquist committed
45
46
47
48
49
50
51
52
53
54
55
/*
problem4

Use the UPDATE command to update multiple values in the table created in 
Problem 1. Use the 
SELECT * FROM your_table_name; 
command to display the content of the table 
(make sure to substitute your_table_name with the actual name of your
table).
*/

56
57
UPDATE MICHAEL_LUNDQUIST SET LASTNAME = 'coolGuy' where LASTNAME = 'Lundquist';

michael lundquist's avatar
michael lundquist committed
58
59
60
61
62
63
64
65
66
67
68
/*
problem5

Use the DELETE command to delete one specific row from the table created 
in Problem 1. Use the 
SELECT * FROM your_table_name;
command to display the content of the table 
(make sure to substitute your_table_name with the actual name of your
table).
*/

69
70
DELETE FROM MICHAEL_LUNDQUIST WHERE ID = 0;

michael lundquist's avatar
michael lundquist committed
71
72
73
74
75
76
77
78
79
/*
problem6

Use the COMMIT statement to save changes in the table created in 
Problem 1. Use the SELECT * FROM your_table_name; command to display the 
content of the table (make sure to substitute your_table_name with the 
actual name of your table).
*/

80
81
COMMIT;

michael lundquist's avatar
michael lundquist committed
82
83
84
85
86
87
88
89
90
91
92
/*
problem7

Use the ROLLBACK command to undo changes in the table created in Problem 1.
What happened? Why? Use the 
SELECT * FROM your_table_name; 
command to display the content of the table 
(make sure to substitute your_table_name with the actual name of your 
table).
*/

93
94
95
96
97
98
99
100
/*
DDL commands can't be rolled back
uncommitted DML commands are deleted
we just committted, so nothing
*/

ROLLBACK;

michael lundquist's avatar
michael lundquist committed
101
102
103
/*
problem8
Use the DROP TABLE command to drop the table created in Problem 1.
104
105
106
*/

DROP TABLE MICHAEL_LUNDQUIST;