SQL Query Extras

1. SQL Query Extras MIS 433

2. Rerunning the last Query • Type the forward slash “/” to rerun the last query that was entered

3. 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

4. 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.

5. 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 Commission from sales_rep FIRSTNAM LASTNAME COMMISSION -------- ---------- ---------- Mary Jones 2150 William Smith 4912.5 Miguel Diaz 2150 Note: Spaces are not allowed and the width of the column is defined by the field width not the alias

6. 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

7. 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

8. 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

9. 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

10. 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

11. 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.

12. 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

13. 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

14. 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 an field’s length. SQL> select substr(city,1,7) as City from customer CITY ------- Lansing Grant etc.

15. Prompting for user Input • Use the PROMPT command to request input from a user. • Use the Accept command to accept input PROMPT Enter a title up to 30 characters long. ACCEPT MYTITLE PROMPT 'Title: ' TTITLE LEFT MYTITLE SKIP 2 SELECT * FROM CUSTOMER WHERE BALANCE>1000

16. Prompts for Variable Input • You can also pass information to a query: SQL>SELECT last, city, state FROM CUSTOMER where city = ‘&city’

17. 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

18. 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 is NOT allowed

19. 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.

20. Options for Altering a Table ALTER TABLE Table_name ADD column-definition MODIFY column-name DEFAULT value DROP DEFAULT ADD primary-key-definition foreign-key-definition null check-constraint DROP CONSTRAINT column-name CASCADE RESTRICT

21. 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

22. 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

23. 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

24. 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}

25. 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');

26. Renaming a table • You can change the name of a table RENAME oldtablename TO newtablename