SQL Query Extras

MIS 433

Rerunning the last Query

Type the forward slash “/” to rerun the last query that was entered

Aliases for Field Names You can replace the name of the field name with an Alias.

For example: SQL> Select first as FirstName, last as LastName, total_commission as “Total Commission”

from sales_rep

FIRSTNAM LASTNAME Total Commission

-------- ---------- ----------

Mary Jones 2150

William Smith 4912.5

Miguel Diaz 2150

Note: Spaces are not allowed unless a double quote is used. In addition, the width of the column is defined by the field width not the alias

Ordering Calculated Columns

If you want to use a calculated column to sort your output, you can use the column number to specify a column to order the output

SQL> select city, state, balance*1.05 from customer order by 3

CITY ST BALANCE*1.05

--------------- -- ------------

Grant MI 22.575

Ada MI 103.6875

Prompts for Variable Input

You can also pass information to a query:

SQL>SELECT last, city, state

FROM CUSTOMER

where city = ‘&city’

Prompts for Variable Input

The prompt will ask for the City Name

SQL> select last, city, state from customer

where city = '&city'

Enter value for city: Grant

old 2: where city = '&city'

new 2: where city = 'Grant'

LAST CITY ST

---------- --------------- --

Samuels Grant MI

Williams Grant MI

Martin Grant MI

Storing Query Statements

SQL statements may be saved and used over again. The commands used are SAVE, GET, and START– Run a query, then type SAVE FILENAME

• No extension is needed (it will be *.sql)SQL> get cust-bal

1 Select * from customer

2* where balance>500

SQL> save cust-bal

Created file cust-bal

Retrieving Query Statements

Saved SQL statements may be retrieved. The commands used are GET or START– Get: retrieves the saved statement and

displays it on the screen. SQL> get cust-bal

1 Select * from customer

2* where balance>500;

– Start: runs the saved statement.SQL> start cust-bal

Storing and Printing Query Results

Options– Send query results to a file to edit with a

word processor

– To store the results of a query in a file, enter the SPOOL command: SPOOL file_name

– SQL*Plus continues to spool information to the file until spooling is turned off: SPOOL OFF

Pausing Output

You can pause output at the end of each screen full of information.

SQL> set pause on

You can turn the pause off.SQL> set pause off

Changing Line Lengths

Normally your output will be set to 80 characters. You can change the line length to a longer setting using the following command:

SQL> set linesize 132

– This command will change the line size to 132 characters.

A similar command changes the length of the page(the default is 22 or 24 lines)

SQL> set pagesize 66

Changing Field Lengths in Output Sometimes you will find that the fields

for character strings are much longer than are needed in your output. You can use the substring function to shorted a field’s length.

SQL> select substr(city,1,7) as City from customerCITY

-------

Lansing

Grant

etc.

Spooling to the Printer

To print query results, spool the results to a file.

Instead of typing SPOOL OFF, enter the command to output to the system printer:

SPOOL OUT

SQL*Plus stops spooling to the file and copies the contents of the spooled file to default printer.

Altering tables: Do’s and Don’ts

Restricted Actions– Changing a column’s data type, size, and default values is

allowed only if there are no data in the columns being modified.– Adding a primary key is allowed only if current field values are

unique.– Adding UNIQUE and CHECK CONDITION constraints to a

column is allowed only if current field values match the added condition.

– Adding a foreign key is allowed only if current field values are NULL or exist in the referenced table.

– Changing a column name is NOT allowed.– Deleting a column (e.g.,

ALTER TABLE table_name DROP COLUMN column_name;

Altering tables: Do’s and Don’ts

Unrestricted Actions– Adding a new column to a table.– Deleting a primary key constraint (also removes any foreign

key references to the field in other tables).– Deleting a foreign key constraint.

Options for Altering a Table

ALTER TABLE Table_name ADD column-definition

MODIFY column-name DEFAULT valueDROP DEFAULT

ADD primary-key-definitionforeign-key-definitionnullcheck-constraint

DROP CONSTRAINT column-name CASCADERESTRICT

Examples of Altering Tables

ALTER TABLE Part MODIFY (warehouse_number INTEGER)

ALTER TABLE Part ADD CONSTRAINT item_class check in (‘HW’, ‘AP’, ‘SG’)

ALTER TABLE Part MODIFY (item_class DEFAULT ‘HW’;)

ALTER TABLE Part DROP CONSTRAINT item_class

Options for Alter Table

Use ALTER TABLE / ADD to…– Add a new column– Add a new foreign key or check condition

constraint Use ALTER TABLE / MODIFY to…

– Modify a column’s data type or size– modify a column to add a NOT NULL constraint– Modify a column to add a default value

Creating an Auto-Number Sequence Sequences are sequential lists of

numbers that are generated automatically by SQL.– Used to create a unique key value for a

table when no “natural” key value exists.– Used for numeric fields only

Sequence Syntax

CREATE SEQUENCE <sequence name>

[INCREMENT by <number>]

[START WITH <start number value>]

[MAXVALUE <maximum number limit>]

[MINVALUE <minimum number limit>] {for decreasing sequences}

[ORDER] {ensures proper ordering}

[CACHE] {sequence numbers are cached to

improve performance}

An Example of Sequence

SQL> CREATE SEQUENCE customer_sequence

start with 623

nomaxvalue

nocache;

SQL> INSERT INTO customer

VALUES

(customer_sequence.nextval,’Bolean',’Robert',

’4431 Sycamore', 'Lansing', 'MI', '49224', 438.75, 1322,'03');

Renaming a table

You can change the name of a tableRENAME oldtablename TO newtablename

You can Reformat Columns

Changing a Column Heading – To produce a report from sales_rep with new

headings specified for various columns, enter the following commands:

clear columns

COLUMN slsrep_number HEADING 'Sales Rep #' format a12

COLUMN Last HEADING 'Last Name' format a12

COLUMN First HEADING 'First Name' format a12

COLUMN Street HEADING 'Street Address' format a15

Select slsrep_number, first, last, street, total_commission as Commission

from sales_rep

Reformatting Columns

SQL*Plus displays the following output: Sales Rep # First Name Last Name Street Address COMMISSION

------------ ------------ ------------ --------------- ----------

03 Mary Jones 123 Main 2150

06 William Smith 102 Raymond 4912.5

12 Miguel Diaz 419 Harper 2150

– Note: The new headings will remain in effect until you enter different headings, reset each column's format, or exit from SQL*Plus.

Word Wrap for Text Output WRAP or WORD_WRAP can be used at the

end of a Column Format statement to shorten the column length but not lose information. SQL> column street heading 'ST' format a6 word_wrap

SQL> select city, street from customer

2 where city='Grant';

CITY ST

--------------- ------

Grant 215

Pete

Grant 519

Watson

Grant 419

Chip