1> /* Homework assignment, 9/30/1999 */
2> /* */
3> /* Make sure I start in my database */
4>
5> use evelyn
1>
2> /* Create table */
3> /* Note assumptions made about which fields can be null */
4> /* Need to create table before inserting values - "go" */
5>
6> create table courseinfo
7> (courseID nchar(7) not null
8> constraint cid primary key,
9> ctitle varchar(40) not null,
10> instructor varchar(30) null,
11> place varchar(6) null,
12> time smalldatetime
13> default '6:30 pm' null,
14> day varchar(10) check
15> (day in
16> ('Monday', 'Tuesday', 'Wednesday', 'Thursday', 'Friday')),
17> tuition smallmoney
18> default 345 null,
19> enroll tinyint null)
1>
2>
3> /* Insert some values - can set up indiv. rows and "go" at end. */
4>
5> insert into courseinfo
6> values ('CISC222', 'Data Structures', 'Ada Lovelace', 'SMI106',
7> '9 am', 'Tuesday', $250.00, 25)
8> insert into courseinfo (courseID, ctitle, day)
9> values ('BIOL345', 'Evolution and Genetics', 'Thursday')
10> insert into courseinfo (courseID, ctitle, day)
11> values ('CISC240', 'Intro to Databases', 'Sunday')
12> insert into courseinfo (courseID, ctitle, day)
13> values ('CISC222', 'Advanced Data Structures', 'Tuesday')
(1 row affected)
(1 row affected)
Msg 548, Level 16, State 1:
Line 10:
Check constraint violation occurred, dbname = 'evelyn', table name =
'courseinfo', constraint name = 'courseinfo_day_1680009016'.
Command has been aborted.
(0 rows affected)
Msg 2601, Level 14, State 3:
Line 12:
Attempt to insert duplicate key row in object 'courseinfo' with unique index
'cid'
Command has been aborted.
(0 rows affected)
1>
2> /* Show data entered in the table. */
3>
4> select * from courseinfo
courseID ctitle
instructor place time
day tuition enroll
-------- ----------------------------------------
------------------------------ ------ --------------------------
---------- ------------------------ ------
BIOL345 Evolution and Genetics
NULL NULL Jan 1 1900 6:30PM
Thursday 345.00 NULL
CISC222 Data Structures
Ada Lovelace SMI106 Jan 1 1900 9:00AM
Tuesday 250.00 25
(2 rows affected)
1>
2>
3> /* Use stored procedures to get information about the table. */
4>
5> sp_tables
table_qualifier table_owner
table_name table_type
remarks
------------------------------ ------------------------------
------------------------------ ------------
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
evelyn dbo
sysalternates SYSTEM TABLE
NULL
evelyn dbo
sysattributes SYSTEM TABLE
NULL
evelyn dbo
syscolumns SYSTEM TABLE
NULL
evelyn dbo
syscomments SYSTEM TABLE
NULL
evelyn dbo
sysconstraints SYSTEM TABLE
NULL
evelyn dbo
sysdepends SYSTEM TABLE
NULL
evelyn dbo
sysgams SYSTEM TABLE
NULL
evelyn dbo
sysindexes SYSTEM TABLE
NULL
evelyn dbo
syskeys SYSTEM TABLE
NULL
evelyn dbo
syslogs SYSTEM TABLE
NULL
evelyn dbo
sysobjects SYSTEM TABLE
NULL
evelyn dbo
syspartitions SYSTEM TABLE
NULL
evelyn dbo
sysprocedures SYSTEM TABLE
NULL
evelyn dbo
sysprotects SYSTEM TABLE
NULL
evelyn dbo
sysreferences SYSTEM TABLE
NULL
evelyn dbo
sysroles SYSTEM TABLE
NULL
evelyn dbo
syssegments SYSTEM TABLE
NULL
evelyn dbo
systhresholds SYSTEM TABLE
NULL
evelyn dbo
systypes SYSTEM TABLE
NULL
evelyn dbo
sysusermessages SYSTEM TABLE
NULL
evelyn dbo
sysusers SYSTEM TABLE
NULL
evelyn dbo
authors TABLE
NULL
evelyn dbo
courseinfo TABLE
NULL
evelyn dbo
courseinfo1 TABLE
NULL
evelyn dbo
courses TABLE
NULL
evelyn dbo
decimals TABLE
NULL
evelyn dbo
fromtemp TABLE
NULL
evelyn dbo
littlebit TABLE
NULL
evelyn dbo
managers TABLE
NULL
evelyn dbo
newtable TABLE
NULL
evelyn dbo
nullprobs TABLE
NULL
evelyn dbo
one TABLE
NULL
evelyn dbo
onecopy TABLE
NULL
evelyn dbo
players TABLE
NULL
evelyn dbo
schedule TABLE
NULL
evelyn dbo
schedule3 TABLE
NULL
evelyn dbo
showme TABLE
NULL
evelyn dbo
supplies TABLE
NULL
evelyn dbo
test_mytypes TABLE
NULL
evelyn dbo
testcon TABLE
NULL
evelyn dbo
testcon2 TABLE
NULL
evelyn dbo
testdate TABLE
NULL
evelyn dbo
two TABLE
NULL
evelyn dbo
union1 TABLE
NULL
evelyn dbo
union2 TABLE
NULL
evelyn dbo
authview VIEW
NULL
evelyn dbo
canIseethis VIEW
NULL
evelyn dbo
supplyview VIEW
NULL
(48 rows affected)
(return status = 0)
1>
2>
3> sp_help courseinfo
Name Owner
Type
------------------------------ ------------------------------
----------------------
courseinfo dbo
user table
(1 row affected)
Data_located_on_segment When_created
------------------------------ --------------------------
default Oct 7 1999 5:32PM
Column_name Type Length Prec
Scale Nulls Default_name
Rule_name Identity
------------------------------ ------------------------------ ------ ----
----- ----- ------------------------------
------------------------------ --------
ctitle varchar 40 NULL
NULL 0 NULL
NULL 0
instructor varchar 30 NULL
NULL 1 NULL
NULL 0
place varchar 6 NULL
NULL 1 NULL
NULL 0
time smalldatetime 4 NULL
NULL 1 courseinfo_time_1664008959
NULL 0
day varchar 10 NULL
NULL 0 NULL
courseinfo_day_1680009016 0
tuition smallmoney 4 NULL
NULL 1 courseinfo_tuitio_1696009073
NULL 0
enroll tinyint 1 NULL
NULL 1 NULL
NULL 0
courseID nchar 7 NULL
NULL 0 NULL
NULL 0
index_name index_description
index_keys
index_max_rows_per_page
-------------------- --------------------------------------------------------
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-----------------------
cid clustered, unique located on default
courseID
0
(1 row affected)
No defined keys for this object.
Msg 18085, Level 16, State 1:
Procedure 'sp_helpartition', Line 83:
Object is not partitioned.
(return status = 0)
1>
2> /* Last comment line will never show - add some command to */
3> /* force it to appear. */
4>
5> select getdate()
--------------------------
Oct 7 1999 5:32PM
(1 row affected)