Table Creation and Management - Revision history

Bacchas at 20:09, 12 November 2017

2017-11-12T20:09:24Z

← Older revision		Revision as of 20:09, 12 November 2017
Line 53:		Line 53:
	'''system-generated constraint name, which isn’t as helpful'''		'''system-generated constraint name, which isn’t as helpful'''
	ALTER TABLE customers ADD CONSTRAINT customers_customer#_pk PRIMARY KEY(customer#);		ALTER TABLE customers ADD CONSTRAINT customers_customer#_pk PRIMARY KEY(customer#);

			== DROP PRIMARY KEY ==
			ALTER TABLE table_name
			DROP CONSTRAINT constraint_name;

	== Foreign Key Constraint ==		== Foreign Key Constraint ==

2017-11-12T19:36:56Z

ADDING COMMENTS TO COLUMNS

← Older revision		Revision as of 19:36, 12 November 2017
Line 92:		Line 92:
	== ADDING COMMENTS TO COLUMNS ==		== ADDING COMMENTS TO COLUMNS ==
	''' PROBLEMS IS THE TABLE.DESCRIPTION IS THE COLUMN '''		''' PROBLEMS IS THE TABLE.DESCRIPTION IS THE COLUMN '''
	~~COMMENTS~~ ON COLUMN problems.description IS 'Short description of the problem';		COMMENT ON COLUMN problems.description IS 'Short description of the problem';

	[[#Select Command\|Back To Top]]-[[Main_Page\| Home]] - [[Oracle_SQL\|Category]]		[[#Select Command\|Back To Top]]-[[Main_Page\| Home]] - [[Oracle_SQL\|Category]]

2017-11-12T19:04:59Z

ADDING COMMENTS TO COLUMNS

← Older revision		Revision as of 19:04, 12 November 2017
Line 91:		Line 91:

	== ADDING COMMENTS TO COLUMNS ==		== ADDING COMMENTS TO COLUMNS ==
	~~comment on column~~ problems.description is 'Short description of the problem';		''' PROBLEMS IS THE TABLE.DESCRIPTION IS THE COLUMN '''
			COMMENTS ON COLUMN problems.description IS 'Short description of the problem';

	[[#Select Command\|Back To Top]]-[[Main_Page\| Home]] - [[Oracle_SQL\|Category]]		[[#Select Command\|Back To Top]]-[[Main_Page\| Home]] - [[Oracle_SQL\|Category]]

2017-11-12T19:03:38Z

← Older revision		Revision as of 19:03, 12 November 2017
Line 89:		Line 89:
	== Creating a table based on another table (subquery) ==		== Creating a table based on another table (subquery) ==
	CREATE TABLE cust_mkt AS (SELECT customers, city, state, zip FROM customers);		CREATE TABLE cust_mkt AS (SELECT customers, city, state, zip FROM customers);

			== ADDING COMMENTS TO COLUMNS ==
			comment on column problems.description is 'Short description of the problem';

	[[#Select Command\|Back To Top]]-[[Main_Page\| Home]] - [[Oracle_SQL\|Category]]		[[#Select Command\|Back To Top]]-[[Main_Page\| Home]] - [[Oracle_SQL\|Category]]

2017-10-27T19:26:06Z

← Older revision		Revision as of 19:26, 27 October 2017
Line 27:		Line 27:

	= Modifying Existing Tables =		= Modifying Existing Tables =
	==~~General Syntax~~ ==		==Drop (Delete a Column) ==
	ALTER TABLE tablename		ALTER TABLE tablename
	~~ADD\|MODIFY\|~~DROP COLUMN\| columnname ~~[definition]~~		DROP COLUMN columnname;

	== Adding a column to an existing Table ==		== Adding a column to an existing Table ==

2017-10-26T23:21:28Z

Creating NEW Tables

← Older revision		Revision as of 23:21, 26 October 2017
Line 76:		Line 76:
	adoption_id NUMBER(5),		adoption_id NUMBER(5),
	vaccination_date DATE CONSTRAINT ail_vaccination_date_nn NOT NULL,		vaccination_date DATE CONSTRAINT ail_vaccination_date_nn NOT NULL,
	CONSTRAINT ail_animal_id_pk PRIMARY KEY(animal_id),		CONSTRAINT ail_animal_id_pk PRIMARY KEY(animal_id),
	CONSTRAINT ail_license_pk UNIQUE(license_tag_number)		CONSTRAINT ail_license_pk UNIQUE(license_tag_number)
	);		);
	== Default Values ==		== Default Values ==

2017-10-26T23:21:16Z

Creating NEW Tables

← Older revision		Revision as of 23:21, 26 October 2017
Line 76:		Line 76:
	adoption_id NUMBER(5),		adoption_id NUMBER(5),
	vaccination_date DATE CONSTRAINT ail_vaccination_date_nn NOT NULL,		vaccination_date DATE CONSTRAINT ail_vaccination_date_nn NOT NULL,

	CONSTRAINT ail_animal_id_pk PRIMARY KEY(animal_id),		CONSTRAINT ail_animal_id_pk PRIMARY KEY(animal_id),
	CONSTRAINT ail_license_pk UNIQUE(license_tag_number)		CONSTRAINT ail_license_pk UNIQUE(license_tag_number)

	);		);
	== Default Values ==		== Default Values ==

2017-10-26T23:21:01Z

Creating NEW Tables

← Older revision		Revision as of 23:21, 26 October 2017
Line 77:		Line 77:
	vaccination_date DATE CONSTRAINT ail_vaccination_date_nn NOT NULL,		vaccination_date DATE CONSTRAINT ail_vaccination_date_nn NOT NULL,

	CONSTRAINT ail_animal_id_pk PRIMARY KEY(animal_id),		CONSTRAINT ail_animal_id_pk PRIMARY KEY(animal_id),
	CONSTRAINT ail_license_pk UNIQUE(license_tag_number)		CONSTRAINT ail_license_pk UNIQUE(license_tag_number)

	);		);
	== Default Values ==		== Default Values ==
	CREATE TABLE acctmgr(		CREATE TABLE acctmgr(

2017-10-26T23:20:25Z

Creating NEW Tables

← Older revision		Revision as of 23:20, 26 October 2017
Line 69:		Line 69:
	ALTER TABLE orders MODIFY (customer# CONSTRAINT orders_customer#_nn NOT NULL);		ALTER TABLE orders MODIFY (customer# CONSTRAINT orders_customer#_nn NOT NULL);
	=Creating NEW Tables =		=Creating NEW Tables =
			CREATE TABLE a_animals(
			animal_id NUMBER(6),
			name VARCHAR2(25),
			license_tag_number NUMBER(10),
			admit_date DATE CONSTRAINT ail_admit_date_nn NOT NULL,
			adoption_id NUMBER(5),
			vaccination_date DATE CONSTRAINT ail_vaccination_date_nn NOT NULL,

			CONSTRAINT ail_animal_id_pk PRIMARY KEY(animal_id),
			CONSTRAINT ail_license_pk UNIQUE(license_tag_number)

			);
	== Default Values ==		== Default Values ==
	CREATE TABLE acctmgr(		CREATE TABLE acctmgr(

2017-10-13T13:08:20Z

MODIFY COMMAND Change a column size, datatype or default value

← Older revision		Revision as of 13:08, 13 October 2017
Line 43:		Line 43:
	ALTER TABLE books		ALTER TABLE books
	MODIFY (title VARCHAR2(10));		MODIFY (title VARCHAR2(10));

			ALTER TABLE publisher
			MODIFY (rating DEFAULT 'N');

	== Adding Primary Key Constraints ==		== Adding Primary Key Constraints ==