HP NonStop SQL/MX Quick Start Abstract This Quick Start describes basic techniques for using HP NonStop™ SQL/MX. The Quick Start is designed to enable you to get immediate access to your database without extensive preparation. Product Version NonStop SQL/MX Releases 2.0, 2.1, and 2.2 (SPR identifier for Release 2.2: H22^ACF) Supported Release Version Updates (RVUs) This publication supports H06.04 and all subsequent H-series RVUs until otherwise indicated by its replacement publication.
Document History Part Number Product Version Published 540438-001 NonStop SQL/MX Release 2.0, 2.1, and 2.2 February 2006 523724-002 NonStop SQL/MX Release 2.0 August 2004 523724-001 NonStop SQL/MX Release 2.0 April 2004 523399-001 NonStop SQL/MX Release 1.
HP NonStop SQL/MX Quick Start Glossary Index What’s New in This Manual v Manual Information v New and Changed Information v About This Manual vii Audience vii Related Documentation viii Notation Conventions xi 1.
2. Selecting Information From a Table (continued) Contents 2. Selecting Information From a Table (continued) Displaying Descriptions of Columns 2-4 Selecting Distinct Rows 2-5 Displaying Information in an Ordered Sequence 2-6 3. Stating Conditions for Selecting Data Using Predicates to Select Data 3-1 Using the LIKE Predicate 3-2 Specifying More Than One Condition 3-3 Using AND or OR 3-3 Specifying What Not to Select 3-4 4.
Index Contents Index HP NonStop SQL/MX Quick Start—540438-001 iii
Contents HP NonStop SQL/MX Quick Start—540438-001 iv
What’s New in This Manual Manual Information HP NonStop SQL/MX Quick Start Abstract This Quick Start describes basic techniques for using HP NonStop™ SQL/MX. The Quick Start is designed to enable you to get immediate access to your database without extensive preparation. Product Version NonStop SQL/MX Releases 2.0, 2.1, and 2.2 (SPR identifier for Release 2.2: H22^ACF) Supported Release Version Updates (RVUs) This publication supports H06.
New and Changed Information What’s New in This Manual • In all other sections, changed SQL/MX Release 2.0 references to SQL/MX Release 2.x.
About This Manual This Quick Start is designed to help you learn some basic techniques for using NonStop SQL/MX and to allow you to get immediate access to your database without extensive preparation. Audience The Quick Start is useful if you: • • • Want a quick introduction to using NonStop SQL/MX. Want to retrieve information from your database or to add or modify data. Plan to use the SQL/MX conversational interface (MXCI).
Related Documentation About This Manual Related Documentation This manual is part of the HP NonStop SQL/MX library of manuals, which includes: Introductory Guides SQL/MX Comparison Guide for SQL/MP Users SQL/MX Quick Start Reference Manuals SQL/MX Reference Manual SQL/MX Messages Manual Installation Guides SQL/MX Installation and Management Guide NSM/web Installation Guide Connectivity Manuals SQL/MX Connectivity Service Manual Migration Guides SQL/MX Database and Application Migration Guide
Related Documentation About This Manual Introductory Guides SQL/MX Comparison Guide for SQL/MP Users Describes SQL differences between NonStop SQL/MP and NonStop SQL/MX. SQL/MX Quick Start Describes basic techniques for using SQL in the SQL/MX conversational interface (MXCI). Includes information about installing the sample database. Reference Manuals SQL/MX Reference Manual Describes the syntax of SQL/MX statements, MXCI commands, functions, and other SQL/MX language elements.
Related Documentation About This Manual Data Management Guides SQL/MX Data Mining Guide Describes the SQL/MX data structures and operations to carry out the knowledge-discovery process. SQL/MX Report Writer Guide Describes how to produce formatted reports using data from an SQL/MX database. DataLoader/MX Reference Manual Describes the features and functions of the DataLoader/MX product, a tool to load SQL/MX databases.
Notation Conventions About This Manual These manuals are part of the SQL/MP library of manuals and are essential references for information about SQL/MP Data Definition Language (DDL) and SQL/MP installation and management: Related SQL/MP Manuals SQL/MP Reference Manual Describes the SQL/MP language elements, expressions, predicates, functions, and statements. SQL/MP Installation and Management Guide Describes how to plan, install, create, and manage an SQL/MP database.
General Syntax Notation About This Manual [ ] Brackets. Brackets enclose optional syntax items. For example: TERM [\system-name.]$terminal-name INT[ERRUPTS] A group of items enclosed in brackets is a list from which you can choose one item or none. The items in the list can be arranged either vertically, with aligned brackets on each side of the list, or horizontally, enclosed in a pair of brackets and separated by vertical lines. For example: FC [ num ] [ -num ] [ text ] K [ X | D ] address { } Braces.
General Syntax Notation About This Manual Item Spacing. Spaces shown between items are required unless one of the items is a punctuation symbol such as a parenthesis or a comma. For example: CALL STEPMOM ( process-id ) ; If there is no space between two items, spaces are not permitted. In this example, no spaces are permitted between the period and any other items: $process-name.#su-name Line Spacing.
General Syntax Notation About This Manual HP NonStop SQL/MX Quick Start—523724-002 xiv
1 Welcome to MXCI and the Sample Database This section describes how to use MXCI and how to install the sample database that is used in the Quick Start tutorial. What Is MXCI? MXCI is the conversational interface to the SQL/MX relational database management system. Use MXCI to perform data manipulation operations without any programming. Use SQL (the structured query language) within MXCI to display and modify information in your database.
Welcome to MXCI and the Sample Database Entering a Command 4. Start MXCI by entering the mxci command at the OSS prompt: >mxci /G/SYSTEM/SYSTEM: mxci Hewlett-Packard NonStop(TM) SQL/MX Conversational Interface 2.1 (c) Copyright 2005 Hewlett-Packard Development Company, LP. >> You have started an MXCI session. Entering a Command To enter commands at the MXCI prompt (>>), you must indicate the end of an MXCI command by typing a semicolon (;).
PARTS Table Welcome to MXCI and the Sample Database The SQL/MX Release 2.x sample database uses SQL/MX tables. To create a sample database using SQL/MP tables, see one of these topics: • • Using a SQL/MX Release 1.8 setmxdb Script in a SQL/MX Release 2.x Environment on page 1-9 Installing the SQL/MP Sample Database on page 1-10 Note. Release 2.x refers to all Release 2.0-based product versions, including Release 2.0, Release 2.1, Release 2.2, and so on.
ORDERS Table Welcome to MXCI and the Sample Database ORDERS Table Order/Num --------100210 100250 110220 ... Order/Date ---------2003-04-10 2003-01-23 2003-07-21 ... Deliv/Date ---------2003-04-10 2003-06-15 2003-12-15 ... Sales/Rep --------220 220 221 ... Cust/Num -------1234 7777 5635 ... ODETAIL Table Order/Num --------100210 100210 100210 100210 100250 100250 ... Part/Num -------244 2001 2403 5100 244 5103 ... Unit/Price ---------3000.00 1100.00 620.00 150.00 3500.00 400.00 ...
DEPT Table Welcome to MXCI and the Sample Database DEPT Table Dept/Num -------1000 1500 2000 2500 3000 3100 3200 3300 3500 4000 4100 9000 Dept/Name --------FINANCE PERSONNEL INVENTORY SHIPPING MARKETING CANADA SALES GERMNY SALES ENGLND SALES ASIA SALES RESEARCH PLANNING CORPORATE Mgr --23 213 32 234 29 43 39 72 111 65 87 1 Rpt/Dept -------9000 1000 9000 2000 9000 3000 3000 3000 3000 9000 4000 9000 Location ------------CHICAGO CHICAGO LOS ANGELES PHOENIX NEW YORK TORONTO FRANKFURT LONDON HONG KONG NEW
Welcome to MXCI and the Sample Database Installing or Removing the Sample Database For more information about the pax command, see the Open System Services Shell and Utilities Reference Manual. Installing or Removing the Sample Database Use the setmxdb script to install or remove the sample database for NonStop SQL/MX. You do not need to have super user ID privileges, but you must have permissions to create tables in the volume where you plan to put the database.
Welcome to MXCI and the Sample Database Installing or Removing the Sample Database The supported operations are: -iall Installs the SQL/MX, Data Mining, and Pub/Sub sample databases. This is the default operation. -imx Installs the SQL/MX sample database. -idm Installs the Data Mining sample database. -ips Installs the Pub/Sub sample database. -rall Removes all three sample databases. -rmx Removes the SQL/MX sample database. -rdm Removes the Data Mining sample database.
Welcome to MXCI and the Sample Database -part Installing or Removing the Sample Database Specifies additional or alternate volume names on which partitioned tables in the sample database are to be created. Only one table in the SQL/MX sample database, the PARTLOC table, may be partitioned. If the -part option is specified, then the PARTLOC table is created with two partitions, using the volumes designated via this option.
Using a SQL/MX Release 1.8 setmxdb Script in a SQL/MX Release 2.x Environment Welcome to MXCI and the Sample Database Extra help options: • -v Prints the version and date of the script. -mult Prints advice on creating multiple sample databases. If you use the -v option, setmxdb displays a message with its version and date: Version 2.
Welcome to MXCI and the Sample Database • Installing the SQL/MP Sample Database Before running the SQL/MX Release 1.8 setmxdb in a SQL/MX Release 2.x environment, you must create the catalog and schemas required by the script. (The default schemas are PERSNL, SALES, and INVENT.) Otherwise, the script will generate an error. Creating the catalog and schemas in advance is NOT required when running the SQL/MX Release 2.x setmxdb. Alternately, you can run the SQL/MX Release 1.
2 Selecting Information From a Table This section describes how to select specific rows and columns and how to use the INVOKE command. Working With SQL/MP Tables NonStop SQL/MX supports DML operations on SQL/MP tables as well as SQL/MX tables. At the beginning of each new MXCI session, specify if you will use three-part logical names or Guardian physical file names to refer to SQL/MP tables. (You must use logical names to refer to SQL/MX tables.
Displaying Selected Columns Selecting Information From a Table Tip • • Using SELECT * is a short way of specifying all the columns in a table. The FROM clause is always required in the SELECT statement. Displaying Selected Columns You can display a specific set of columns and rows of data by providing additional information in the SELECT statement. Select columns by specifying the column names.
Displaying Calculated Values Selecting Information From a Table Example Display the parts where part description is ”PRINTER CONTROLLER”: SELECT PARTNUM, QTY_AVAILABLE FROM PARTS WHERE PARTDESC = 'PRINTER CONTROLLER'; The selected row is: Part/Num -------6603 Qty/Avail ----------430 --- 1 row(s) selected. Tip • To continue a long statement or command on the next line, press Enter. MXCI displays a continuation prompt (+>), and you can continue your statement.
Displaying Descriptions of Columns Selecting Information From a Table Example Using the data in the ODETAIL table, calculate the total price for the number of units of each part in an order. Select parts from order number 100210: SELECT ORDERNUM, PARTNUM, QTY_ORDERED*UNIT_PRICE FROM ODETAIL WHERE ORDERNUM = 100210; The selected rows are: Order/Num ---------100210 100210 100210 100210 Part/Num -------244 2001 2403 5100 (EXPR) --------------------10500.00 3300.00 3720.00 1500.00 --- 4 row(s) selected.
Selecting Information From a Table Selecting Distinct Rows Example Display the column definitions of the PARTS table: INVOKE PARTS; The displayed column definitions are: -- Definition of table SAMDBCAT.SALES.
Selecting Information From a Table Displaying Information in an Ordered Sequence Example Determine which sales representatives have orders in the ORDERS table and display each identification number only once: SELECT DISTINCT SALESREP FROM ORDERS; The selected rows are: SALESREP -------220 221 222 223 226 227 229 231 568 --- 9 row(s) selected. Displaying Information in an Ordered Sequence To display the selected rows in a sorted order, include the ORDER BY clause in the SELECT statement.
Displaying Information in an Ordered Sequence Selecting Information From a Table Example Display customer numbers, order numbers, and delivery dates arranged in ascending order by customer number. Select only orders of sales representatives identified by numbers less than 300: SELECT CUSTNUM, ORDERNUM, DELIV_DATE FROM ORDERS WHERE SALESREP < 300 ORDER BY CUSTNUM; Some selected rows are: Cust/Num ORDERNUM ------- ---------30 200378 111 200562 131 300756 156 700694 ... ...
Selecting Information From a Table Displaying Information in an Ordered Sequence HP NonStop SQL/MX Quick Start—523724-002 2 -8
3 Stating Conditions for Selecting Data This section describes how to use predicates in a WHERE clause. Using Predicates to Select Data In some of the previous examples, one value is compared to another value by using a comparison operator: for example, the equal sign (=). A comparison operator is one type of SQL predicate.
Stating Conditions for Selecting Data Using the LIKE Predicate Using the LIKE Predicate Sometimes you want to select data that you are not able to specify completely. For example, suppose you want to know something about a particular printer, but you cannot remember the exact description of the printer.
Specifying More Than One Condition Stating Conditions for Selecting Data Specifying More Than One Condition You can use the logical operators AND and OR to connect two conditions. Notice that the AND operator is more restrictive because both conditions must be true. The OR operator requires that only one condition be true. Operator Example Meaning AND PARTDESC LIKE '%PRINTER%' AND PRICE < 500 If values in a row satisfy both of the conditions, the row appears in the result.
Specifying What Not to Select Stating Conditions for Selecting Data The selected rows are: PARTNUM ------2001 2002 2003 3103 PARTDESC -----------------GRAPHIC PRINTER,M1 GRAPHIC PRINTER,M2 GRAPHIC PRINTER,M3 LASER PRINTER, X1 PRICE -----------1100.00 1500.00 2000.00 4200.00 --- 4 row(s) selected. Tip You can combine any number of conditions with AND and OR to express conditions. The sequence of evaluation is AND before OR unless you specify otherwise by using parentheses.
Stating Conditions for Selecting Data Specifying What Not to Select Rows with part numbers between 3000 and 5999 and the row with part number 2001 are not selected. In this example, AND is part of the BETWEEN predicate. It does not connect two search conditions. • You can specify NOT BETWEEN, NOT IN, and NOT LIKE, but you cannot specify NOT with a comparison operator. For example, you must use <> to indicate not equal.
Stating Conditions for Selecting Data HP NonStop SQL/MX Quick Start—540438-001 3 -6 Specifying What Not to Select
4 Displaying Information About Groups of Rows This section describes aggregate functions and how to group rows. Selecting Values by Using Aggregate Functions In addition to displaying information from each row that satisfies conditions in a WHERE clause, you can use an aggregate function to combine rows with like values in the same column into a group and display one row of information about the group.
Displaying Information About Groups of Rows Grouping and Ordering Rows Grouping and Ordering Rows Sometimes you want to display information about ordered groups of rows. For example, you might want to display group information ordered by the result of an aggregate function. Example Determine the earliest delivery date of all orders from the same customer.
Grouping and Ordering Rows Displaying Information About Groups of Rows Example Find the earliest delivery date for the complete set of customers. If you do not specify a GROUP BY clause, all rows belong to the same group (the entire table). In this case, you must also omit CUSTNUM from the select list because there is no single customer for the entire table: SELECT MIN(DELIV_DATE) FROM ORDERS; The selected value is: (EXPR) ----------2003-01-10 --- 1 row(s) selected.
Displaying Information About Groups of Rows Counting Rows Counting Rows Use the COUNT(*) function to count the rows in a group. Use the COUNT function with a selected column to count the values of that column in a group. Example Count the number of orders for each sales representative. Use COUNT(*) to specify that you want to count the number of rows in each group: SELECT SALESREP, COUNT(*) FROM ORDERS GROUP BY SALESREP ORDER BY SALESREP; Some of the selected rows are: Sales/Rep --------220 ... 226 ...
Displaying Information About Groups of Rows Computing Averages for Groups Tip The keyword DISTINCT causes each unique customer number for a particular sales representative to be counted only once. Sales representative 226 has three orders, but two of the orders are from the same customer. Computing Averages for Groups For some queries, you might want to get information about one or two specific groups. The next example uses the ODETAIL table to illustrate this technique.
Displaying Information About Groups of Rows Computing Averages for Groups HP NonStop SQL/MX Quick Start—540438-001 4 -6
5 Selecting Data From More Than One Table This section describes how to join tables and use correlation names. Joining Tables Some queries require information from more than one table. You can select data from two or more tables by effectively joining the tables. For example, the PARTS and ODETAIL tables contain related data. Each table has a PARTNUM column. PARTS Table For part 5100, the PARTS table has this row, PARTNUM ------5100 PARTDESC -------------------MONITOR BW, TYPE 1 PRICE --------150.
Qualifying Ambiguous Column Names Selecting Data From More Than One Table Qualifying Ambiguous Column Names Because the PARTNUM column has the same name in both the PARTS and ODETAIL tables, always qualify PARTNUM with a table name or a correlation name. For example, PARTS.PARTNUM uses the table name. Example Compare the quantity available of part number 5100 with the quantity ordered.
Using Correlation Names Selecting Data From More Than One Table Using Correlation Names You can define explicit correlation names for the PARTS and ODETAIL tables. For example, use the letter P for PARTS and the letter O for ODETAIL. Using correlation names shortens the length of the text you type. Example Compute the sum of the quantity ordered for each part number in the PARTS table. Group by the columns in the PARTS table: SELECT P.
Selecting Data From More Than One Table HP NonStop SQL/MX Quick Start—540438-001 5 -4 Using Correlation Names
6 Changing Information in a Table This section describes how to insert, update, and delete table rows. Inserting a Row Into a Table To insert rows in a table, use the INSERT statement. Example In the PARTS table, insert a row that describes a data modem with part number 9999: INSERT INTO PARTS (PARTNUM, PARTDESC, PRICE) VALUES (9999, 'DATA MODEM', 200.00); --- 1 row(s) inserted. Tip The definition of the PARTS table specifies a default value of zero for the QTY_AVAILABLE column.
Changing Information in a Table Updating an Existing Row Updating an Existing Row To change values in one or more columns and one or more rows, use the UPDATE statement. Example In the PARTS table, you have inserted a row that describes a data modem with part number 9999. Suppose you determine that the quantity available of part number 9999 is 12 units.
Deleting Rows From a Table Changing Information in a Table Deleting Rows From a Table The DELETE statement deletes entire rows from a table. Example Delete the two rows that you inserted in the previous examples. First, display the rows with part numbers 9998 and 9999: SELECT * FROM PARTS WHERE PARTNUM IN (9998, 9999); Part/Num -------9998 9999 Part/Desc -----------------300 BD DATA MODEM 1200 BD DATA MODEM Price -----------120.00 200.00 Qty/Avail ----------3 12 --- 2 row(s) selected.
Changing Information in a Table HP NonStop SQL/MX Quick Start—540438-001 6 -4 Deleting Rows From a Table
7 Using the Same SELECT Statement Repeatedly This section describes how to prepare and execute statements and use parameter values. Preparing to Execute a Statement Repeatedly You can prepare a statement (assigning a name to it) and execute it repeatedly during your MXCI session. However, when you end your session, the prepared statement is no longer available. Prepared statements are most useful if they contain parameters. A parameter is a name preceded by a question mark.
Providing the Parameter Values Using the Same SELECT Statement Repeatedly Providing the Parameter Values You have prepared the statement CKO for execution. The parameters are ?order and ?part. You must provide the values for the parameters before execution.
8 Protecting Database Integrity This section describes how to begin and end a transaction. Starting a Transaction NonStop SQL/MX enables you to define the beginning of a transaction. A transaction can consist of multiple SQL statements. You must end your transaction either by rolling back or by committing the changes you have made. Example Define the beginning of a transaction: BEGIN WORK; --- SQL operation complete. Example Enter the statements that perform the transaction.
Ending a Transaction Protecting Database Integrity Ending a Transaction If a problem occurs during the transaction, you can roll back, or undo, the transaction to cancel the changes you made up to that point. If no problems occur, you can commit the changes to the database. Example Suppose you realize that you did not want prices between $1000 and $2000 doubled. You can enter the ROLLBACK WORK statement to roll back all changes since you entered BEGIN WORK: ROLLBACK WORK; --- SQL operation complete.
9 Creating Database Objects This section describes how to create and drop objects in your database. You use the Data Definition Language (DDL) statements of NonStop SQL/MX to create database objects. For more information about DDL statements, see the SQL/MX Reference Manual. Creating a Catalog Before you can create an SQL/MX table, you must first create a catalog and a schema in which to store the definition of the table. You can use MXCI to create these objects.
Creating a Table Creating Database Objects Creating a Table At a minimum, when creating a table, you must supply: • • A table name The name and data type of each column Example Suppose you want to create a table to organize attendees of a conference into teams. The table contains four columns. To create this table in the catalog MYCAT, enter: CREATE TABLE (EMPNUM FIRST_NAME LAST_NAME TEAMNUM PRIMARY KEY MYCAT.MYSCH.
Creating a View Creating Database Objects Creating a View A view provides an alternate way of looking at data in one or more tables. A view does not store the data separately but retrieves it from the underlying tables. You must have SELECT privileges on the underlying tables. Create a view and then select data from the view by using a SELECT statement: Example Suppose you often want to know the names of department managers. You can create a view that includes this information.
Dropping Objects Creating Database Objects Dropping Objects You might want to delete the objects you created. Example To delete the view, at the MXCI prompt, enter: DROP VIEW MYCAT.MYSCH.MGRLIST; Example To delete the table, at the MXCI prompt, enter: DROP TABLE MYCAT.MYSCH.MYTABLE; Example To delete the schema, at the MXCI prompt, enter: DROP SCHEMA MYCAT.
Index A DESC keyword 2-7 Displaying calculated values 2-3 descriptions of columns 2-4 information in an ordered sequence 2-6 selected rows 2-2 DISTINCT keyword 2-5 Aggregate functions 4-1 Ambiguous table names correlation name 5-2 table name 5-2 AND operator 3-3 Arithmetic operators 2-4 AS clause 2-4 Audited tables 8-2 AVG function 4-5 E B BEGIN WORK statement 8-1 BETWEEN predicate 3-1, 3-5 F C Calculated values 2-3 Character data 2-5 Column definitions 2-5 COMMIT WORK statement 8-1, 8-2 Comparison op
L Index L Q LIKE predicate 3-1, 3-2, 3-3 Logical operators AND 3-3 NOT 3-4, 3-5 OR 3-3 sequence of evaluation 3-4 Qualifying table names 5-2 M MXCI entering command 1-2 starting session 1-1 stopping session 1-2 NO DEFAULT 2-5 NOT NULL 2-5 NOT operator 3-1, 3-4 NULL predicate 3-1 Numeric data 2-5 O ODETAIL table 1-3 OR operator 3-3 ORDER BY 1 4-2 ORDER BY clause 2-6 Ordered groups of rows 4-2 ORDERS table 1-3 Parameter names 7-2 values 7-2 PARTS table 1-3 Predicates 3-1 PREPARE statement 7-1 Primary
T Index SELECT statement (continued) retrieving data with 2-1 rows in ascending order 2-7 in descending order 2-7 SELECT * 2-1, 2-2 WHERE clause 2-2 Selected columns 2-2 Selected rows 2-2 Selecting all data 2-1 Set functions AVG 4-1 COUNT 4-1 COUNT(*) 4-1 MIN 4-1 SUM 4-1 SET list 6-2 SET PARAM command 7-2 setmxdb script 1-9 Single quotes 2-3 SQL 1-1 SQLCI 1-1 SQL/MP tables, working with 2-1 SQL/MX conversational interface (MXCI) 1-1 SQL/MX Release 1.8 1-9 SQL/MX Release 2.
Special Characters Index HP NonStop SQL/MX Quick Start—540438-001 Index -4
