Database Queries - Revision history

Bacchas at 20:06, 5 November 2017

2017-11-05T20:06:21Z

← Older revision		Revision as of 20:06, 5 November 2017
Line 14:		Line 14:
	:SELECT title AS "Title of Book", category FROM books;		:SELECT title AS "Title of Book", category FROM books;
	; This adds the column alias "Title of Book" to the results instead of using just the title as the column heading		; This adds the column alias "Title of Book" to the results instead of using just the title as the column heading

			== DISTINCT VALUES ==
			SELECT DISTINCT SALES_DATE, ORDER_ID FROM SALES

	== Select all tables owned by the user ==		== Select all tables owned by the user ==

Bacchas: /* Select all tables owned by the user */

2017-10-31T16:32:39Z

Select all tables owned by the user

← Older revision		Revision as of 16:32, 31 October 2017
Line 17:		Line 17:
	== Select all tables owned by the user ==		== Select all tables owned by the user ==
	SELECT * FROM CAT;		SELECT * FROM CAT;

			SELECT * FROM CAT
			WHERE TABLE_NAME LIKE '%ACCT%';

	==select table_name FROM user_tables ==		==select table_name FROM user_tables ==

Bacchas at 16:28, 31 October 2017

2017-10-31T16:28:45Z

← Older revision		Revision as of 16:28, 31 October 2017
Line 1:		Line 1:
	==Select Commands==		==Select Commands==

	SELECT * from tableName		SELECT * from tableName
	- SELECT all columns FROM the table		- SELECT all columns FROM the table
Line 13:		Line 14:
	:SELECT title AS "Title of Book", category FROM books;		:SELECT title AS "Title of Book", category FROM books;
	; This adds the column alias "Title of Book" to the results instead of using just the title as the column heading		; This adds the column alias "Title of Book" to the results instead of using just the title as the column heading

			== Select all tables owned by the user ==
			SELECT * FROM CAT;

	==select table_name FROM user_tables ==		==select table_name FROM user_tables ==

Bacchas at 12:52, 13 October 2017

2017-10-13T12:52:04Z

← Older revision		Revision as of 12:52, 13 October 2017
Line 24:		Line 24:
	== Using Concatenation ==		== Using Concatenation ==


			SELECT firstname \|\| lastname FROM customers;
			this will display the info but last and first names will be together eg. firstlast
			to add a space between the names :
			SELECT firstname \|\| ' , ' \| \|\| lastname as "Customer Name" FROM customers;
			this will add the , between the names and give it an alias heading "Customer name"

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

Bacchas at 00:35, 13 October 2017

2017-10-13T00:35:39Z

← Older revision		Revision as of 00:35, 13 October 2017
Line 24:		Line 24:
	== Using Concatenation ==		== Using Concatenation ==

	~~== Creating a table based on another table (subquery) ==~~
	~~CREATE TABLE cust_mkt AS (SELECT customers, city, state, zip FROM customers);~~


	~~SELECT firstname \|\| lastname FROM customers;~~
	~~this will display the info but last and first names will be together eg. firstlast~~
	~~to add a space between the names :~~
	~~SELECT firstname \|\| ' , ' \| \|\| lastname as "Customer Name" FROM customers;~~
	~~this will add the , between the names and give it an alias heading "Customer name"~~

	~~== Adding Primary Key Constraints ==~~
	~~'''This makes customer# the primary key the constraint name is used so you can easily '''~~
	~~'''identify an error if you try to enter a duplicate customer'''~~
	~~'''If you don’t assign constraint names, the error message displays the'''~~
	~~'''system-generated constraint name, which isn’t as helpful'''~~
	~~ALTER TABLE customers ADD CONSTRAINT customers_customer#_pk PRIMARY KEY(customer#);~~

	~~== Foreign Key Constraint ==~~
	~~'''This command instructs Oracle 11g to add a FOREIGN KEY constraint on the'''~~
	~~'''Customer# column of the ORDERS table. The name chosen for the constraint is'''~~
	~~'''orders_customer#_fk. This constraint makes sure an entry for the Customer# column of'''~~
	~~'''the ORDERS table matches a value stored in the Customer# column of the CUSTOMERS'''~~
	~~'''table. When the command executes, a message indicates the table was altered successfully'''~~
	~~ALTER TABLE orders ADD CONSTRAINT orders_customer#_fk FOREIGN KEY(customer#) REFERENCE customers (customer#)~~
	~~== Unique Constraints ==~~
	~~ALTER TABLE tablename ADD CONSTRAINT constraintname UNIQUE(columnname);~~
	~~== Check Constraint ==~~
	~~ALTER TABLE orders ADD CONSTRAINT orders_shipdate_ck CHECK (orderdate <= shipdate);~~

	~~== Not Null Constraint ==~~
	~~ALTER TABLE orders MODIFY (customer# CONSTRAINT orders_customer#_nn NOT NULL);~~

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

Bacchas at 21:01, 11 October 2017

2017-10-11T21:01:14Z

← Older revision		Revision as of 21:01, 11 October 2017
Line 50:		Line 50:
	== Unique Constraints ==		== Unique Constraints ==
	ALTER TABLE tablename ADD CONSTRAINT constraintname UNIQUE(columnname);		ALTER TABLE tablename ADD CONSTRAINT constraintname UNIQUE(columnname);
			== Check Constraint ==
			ALTER TABLE orders ADD CONSTRAINT orders_shipdate_ck CHECK (orderdate <= shipdate);

			== Not Null Constraint ==
			ALTER TABLE orders MODIFY (customer# CONSTRAINT orders_customer#_nn NOT NULL);

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

Bacchas at 20:52, 11 October 2017

2017-10-11T20:52:50Z

← Older revision		Revision as of 20:52, 11 October 2017
Line 48:		Line 48:
	'''table. When the command executes, a message indicates the table was altered successfully'''		'''table. When the command executes, a message indicates the table was altered successfully'''
	ALTER TABLE orders ADD CONSTRAINT orders_customer#_fk FOREIGN KEY(customer#) REFERENCE customers (customer#)		ALTER TABLE orders ADD CONSTRAINT orders_customer#_fk FOREIGN KEY(customer#) REFERENCE customers (customer#)
			== Unique Constraints ==
			ALTER TABLE tablename ADD CONSTRAINT constraintname UNIQUE(columnname);

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

Bacchas: /* Foreign Key Constraint */

2017-10-11T20:40:33Z

Foreign Key Constraint

← Older revision		Revision as of 20:40, 11 October 2017
Line 42:		Line 42:

	== Foreign Key Constraint ==		== Foreign Key Constraint ==
	'''This command instructs Oracle 11g to add a FOREIGN KEY constraint on the'''		'''This command instructs Oracle 11g to add a FOREIGN KEY constraint on the'''
	'''Customer# column of the ORDERS table. The name chosen for the constraint is'''		'''Customer# column of the ORDERS table. The name chosen for the constraint is'''
	'''orders_customer#_fk. This constraint makes sure an entry for the Customer# column of'''		'''orders_customer#_fk. This constraint makes sure an entry for the Customer# column of'''
	'''the ORDERS table matches a value stored in the Customer# column of the CUSTOMERS'''		'''the ORDERS table matches a value stored in the Customer# column of the CUSTOMERS'''
	'''table. When the command executes, a message indicates the table was altered successfully'''		'''table. When the command executes, a message indicates the table was altered successfully'''

	ALTER TABLE orders ADD CONSTRAINT orders_customer#_fk FOREIGN KEY(customer#) REFERENCE customers (customer#)		ALTER TABLE orders ADD CONSTRAINT orders_customer#_fk FOREIGN KEY(customer#) REFERENCE customers (customer#)


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

Bacchas: /* Foreign Key Constraint */

2017-10-11T20:39:36Z

Foreign Key Constraint

← Older revision		Revision as of 20:39, 11 October 2017
Line 47:		Line 47:
	'''the ORDERS table matches a value stored in the Customer# column of the CUSTOMERS'''		'''the ORDERS table matches a value stored in the Customer# column of the CUSTOMERS'''
	'''table. When the command executes, a message indicates the table was altered successfully'''		'''table. When the command executes, a message indicates the table was altered successfully'''

	ALTER TABLE orders ADD CONSTRAINT orders_customer#_fk FOREIGN KEY(customer#) REFERENCE customers (customer#)		ALTER TABLE orders ADD CONSTRAINT orders_customer#_fk FOREIGN KEY(customer#) REFERENCE customers (customer#)


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

Bacchas at 20:39, 11 October 2017

2017-10-11T20:39:23Z

← Older revision		Revision as of 20:39, 11 October 2017
Line 42:		Line 42:

	== Foreign Key Constraint ==		== Foreign Key Constraint ==
			'''This command instructs Oracle 11g to add a FOREIGN KEY constraint on the'''
			'''Customer# column of the ORDERS table. The name chosen for the constraint is'''
			'''orders_customer#_fk. This constraint makes sure an entry for the Customer# column of'''
			'''the ORDERS table matches a value stored in the Customer# column of the CUSTOMERS'''
			'''table. When the command executes, a message indicates the table was altered successfully'''
	ALTER TABLE orders ADD CONSTRAINT orders_customer#_fk FOREIGN KEY(customer#) REFERENCE customers (customer#)		ALTER TABLE orders ADD CONSTRAINT orders_customer#_fk FOREIGN KEY(customer#) REFERENCE customers (customer#)


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