How to work with database objects

A statement that creates a new user for a database

 

CREATE USER ap IDENTIFIED BY ap;

 

 

A statement that grants privileges to a user

 

GRANT ALL PRIVILEGES TO ap;

 

 

A statement that creates a new table

 

CREATE TABLE invoices
(
	invoice_id            NUMBER, 
	vendor_id             NUMBER          NOT NULL,
	invoice_number        VARCHAR2(50)    NOT NULL,
	invoice_date          DATE            NOT NULL,
	invoice_total         NUMBER(9,2)     NOT NULL,
	payment_total         NUMBER(9,2)                 DEFAULT 0,
	credit_total          NUMBER(9,2)                 DEFAULT 0,
	terms_id              NUMBER          NOT NULL,
	invoice_due_date      DATE            NOT NULL,
	payment_date          DATE,
	CONSTRAINT invoices_pk 
		PRIMARY KEY (invoice_id),
	CONSTRAINT invoices_fk_vendors
		FOREIGN KEY (vendor_id) 
		REFERENCES vendors (vendor_id),
	CONSTRAINT invoices_fk_terms
		FOREIGN KEY (terms_id) 
		REFERENCES terms (terms_id)
);

 

A statement that adds a new column to a table

 

ALTER TABLE invoices
ADD balance_due NUMBER(9,2);

 

A statement that deletes the new column

 

ALTER TABLE invoices
DROP COLUMN balance_due;

 

 

A statement that creates an index on the table

 

CREATE INDEX invoices_vendor_id_index
ON invoices(vendor_id);

 

A statement that deletes the new index

 

DROP INDEX invoices_vendor_id_index;

 

A statement that creates a sequence for generating invoice_id values

 

CREATE SEQUENCE invoice_id_seq START WITH 115 INCREMENT BY 1;