General Enquiries 8592495 Computer Operations 8592496 (Hardware and operational problems) 'Help Desk' 8592480 (Software and general problems) Fax 5597904 Hardware Maintenance 8592774 Dial-up modem pools 24-hr service line : 8592595 Night service line : 8597997 8597992 (half-hr/14.4k baud; from July 1, 1993)
Summer Schedule From June 13, 1992 to September 12, 1993 Monday (8:00 a.m.) to Saturday (9:00 p.m.) Closed on Sundays and Holidays Normal Schedule From September 14, 1992 to June 13, 1993 From Monday (8:00 a.m.) to Saturday (9:00 p.m.) Sunday from 12:00 noon to 7:00 p.m. Closed on Holidays. VAX 6420 (HKUCC) IBM9375 (HKUVMI) IBM4361 (HKUVM) DECsystem 5500s (HKUXA/HKUXB) SUN SPARCserver 670 (HKUSUB) Service hours(VAX 6420, IBM 9375, DECsystem 5500s and SUN SPARCserver 670 will be running unattended on all holidays)
24 hours Daily. Running unattended on all Sundays and Holidays Terminal Room Opening Hours
Remote Terminal Rooms Rm 218, Knowles Building Rm LG-108, KK Leung Bldg. SunOS & Ultrix Workstation Rooms Rm 113, Run Run Shaw Bldg. Rm 210, Run Run Shaw Bldg. Microcomputer Laboratories Rm 101, 103, 104, Run Run Shaw Building Summer/Normal Schedule
Monday to Friday: 8:30 a.m. - 10:00 p.m. Saturday: 8:30 a.m. - 3:30 p.m. Closed on Sundays & Holidays Summer Schedule Monday (8:30 a.m.) - Saturday (3:30 p.m.) Closed on Sundays & Holidays Normal Schedule Monday (8:30 a.m.) - Saturday (8:30 p.m.) Sunday from 12:00 noon to 7:00 p.m. Closed on Sundays & Holidays Holiday Schedule for June 1993
The Birthday of Her Majesty the Queen June 12, 1993 (Sat) to June 14, 1993 (Mon) Computer Centre closed All terminal rooms closed. June 15, 1993 (Tue) Computer Centre open at 8:00 a.m. All computer systems resume service at 8:30 a.m. Tuen Ng Festival June 24, 1993 (Thr) Computer Centre closed All terminal rooms closed June 25, 1993 (Fri) Computer Centre open at 8:00 a.m. All computer systems resume service at 8:30 am 'Help Desk' Consultation Hours
Rm 109, Run Run Shaw Building (8592480) Monday - Friday: 9:00 a.m. - 12:30 p.m. 2:00 p.m. - 5:30 p.m. Saturday: 9:00 a.m. - 12:30 p.m. Closed on Sundays & Holidays
The University's library automation system (DRA System) has been running on a VAX6310 system since it was first installed in 1989. Since then, many useful functions and services have been continually added, and more and more users are delighted with the useful and convenient services provided. However, many users should appreciate that the terminal response of the VAX6310 was sometimes rather slow due to the heavy load condition. To improve the terminal response and further enhance the system services, the Libraries have worked together with Computer Centre in upgrading the library computer to a more powerful VAX6510 system which is configured as follows:
Processor : 6510 Memory : 128MB Tape Drive : TF85 Disk : 2 RF73s; 2 RA92s - transferred from the old VAX6310 (total capacity: 7GB)We are pleased to announced that the VAX6510 machine has been delivered, and it has been set up by the DEC engineers and staff of our Centre in early May. A 3-step exercise for moving the library software and databases from the old VAX6310 system to the new VAX6510 system has been adopted so as to minimise the interruption that might be caused to the library services.
We have already gone through the first two steps smoothly and successfully. The first step of the exercise was essentially preliminary software installation and testings which were jointly carried out by the Libraries and Computer Centre staff to ensure that the library software can operate properly in the new system environment. The second step took place in the morning of May 14. This step involved the transplant of most of the functions and services to the new VAX6510 system. The only function remained on the old VAX6310 was the Wilson databases which were linked to the new system through a network connection.
The last step has been scheduled to take place on May 29, and is for moving the two RA92 disks and the Wilson databases associated with the old VAX6310 system to the VAX6510 system. When you are reading this article, we should hopefully have completed the whole upgrade process, and our users should be enjoying the enhanced services, particularly the faster terminal response, supported by the new VAX6510 system.
T.Y. Lui Tel.: 8592455 E-mail:email@example.com
As announced in the last (March/April) issue of the Computer News, a pilot release of the Campus-Wide Information Service (CWIS) has come on life on 1 May. The Centre of Media Resources and Electronic Services Unit have joined Computer Centre as the pioneer departments in starting the pilot CWIS. On the other hand, the Medical Library which was also mentioned last time has decided to join the CWIS at a later stage.
To recapitulate, the CWIS is conceived as a platform for distributing information electronically to all departmental users who have access to the Campus Network. From the user's viewpoint, it appears as another item on the HKUMENU of the user's departmental LAN-server. Connection to the CWIS server, where the information resides, is automatic, without requiring users to identify themselves by logging in. Successful connection to the server brings further menu-pages for accessing the information base. Information is grouped under the respective providers. At present, the information provided by the three CWIS information providers are summarised as follows:
Computer Centre :
- Terminal Room Opening Hours - PC Tender Price Information (UPCC Tender # 138) (Prices of various configurations of 386, 486 models and miscellaneous PC accessories) - Price List of Consumables from Computer Centre (Prices of floppy diskettes, printer ribbons, computer paper, user handbooks, laser printer cartridges and others, with purchase procedure) - In-house Maintenance Service (Types of equipment and their maintenance charges) - Computer Centre Training Courses (Description of a whole range of training courses offered by Computer Centre , with course objectives, contents, prerequisites and duration) - Computer Centre Library Catalogue (Catalogue of books available for loan from Computer Centre ) - Computer Centre Scanned Notices (Notices scanned by Computer Centre , in image format) - University of Hong Kong Communications Directory (Section C of the 'Communications Directory', in electronic form, equipped with convenient keyword-searching facility)Centre for Media Resources :
- Centre for Media Resources General Information (Contains topics on the nature and location of CMR, types of services provided and charges, etc.)Electronic Services Unit :
- List of Calibration and Precision Measurement Equipment (A list of measurement equipment indicating usage, model, range, accuracy and their distribution in the University)Most of the information currently available is text-based. The CWIS can also distribute scanned image information, e.g. Computer Centre Scanned Notices mentioned above. In either case, users should not find much difficulty in reading/viewing the text/image information, as the operation normally involves only a few keys. Help information are provided either as a separate item on the menu or incorporated in the browsing-software itself (The function key F1 generally brings up on- line help). Before you start using CWIS for the first time, we would advise you to spend a few minutes to read the help information and familiarise yourself with the basic keys for smooth navigation of the information provided by CWIS.
At present, CWIS is available to University staff only. However, we are working to extend the service to the student population too. Various student-oriented information, either provided by student societies or by offices of the University, could then be disseminated to students through CWIS.
As departmental users of the service, you are encouraged to explore on what is now provided. Getting a feel of the service may inspire you how CWIS can help your department to distribute information widely and efficiently in the campus. Your participation in CWIS as information providers are most welcome. Any feedbacks on the pilot service would be much appreciated and are essential to our refinement of the service.
T.Y. Shen Tel.: 8592482 E-mail:firstname.lastname@example.org
Since the announcement of the availability of the UPGC-funded DECmpp system located at the Chinese University of Hong Kong in our last issue of Computer News, quite a few users have shown keen interest in this facility. As the US and HK authorities require certain security measures to be followed by each user site, Computer Centre was still in the process of setting up the required measures at the time of the announcement and hence no details could be released then.
We have finalised the required procedures as given in the following:
I. Account application
Application form for an account on the DECmpp 12000 system can be obtained from Computer Centre's General Office, Room 223, Run Run Shaw Building. The form must be returned to Computer Centre for checking before it is forwarded to the Chinese University of Hong Kong for processing.
If you intend to use the registered workstation HKUX15 (see section III below) in Computer Centre to access the DECmpp system, you need to have a HKUXA/HKUXB account. Application form for HKUXA/HKUXB account can be obtained from our General Office, Room 223, Run Run Shaw Building.
II.Proof of nationality
In compliance with the security procedure specified by the US and HK authorities, applicants must submit a proof of nationality together with their account applications. Such a proof of nationality should generally consist of the photocopies of those pages of the applicant's passport stating the applicant's nationality. The applicant will be required to produce his passport for the purpose of verification of the proof. Without such a proof, the application could not be processed. Procedure for using the DECmpp 12000 System
III.Accessing DECmpp system through registered workstations
Access to the DECmpp system can be gained through a DECstation 5000/125 workstation (nodename HKUX15) installed in Room RR-108 outside the Computer Room. At present, this is the only registered workstation for accessing to the DECmpp system in the University. It is the security requirement that access to the DECmpp system through this registered workstation has to be controlled. We implement such security requirement through the control of the communication link between the DECmpp system and the workstation HKUX15. When access to the DECmpp system is not required, this workstation will work normally with all systems inside the HKU campus. In addition, the security procedure requires us to record the usage of the registered workstation. Thus a user has to sign in and sign out a Usage Logbook at the Computer Room counter for any DECmpp session so that our Operations staff can activate and deactivate the communication link between the DECmpp system and the workstation accordingly.
Computer Centre is also in charge of the co-ordinations for setting up the acceptable security measures for registered workstations for access to the DECmpp system at our University. Any department wishing to set up its own registered workstation should contact the undersigned for details.
IV.Reservation for the registered workstation HKUX15
A booking form is posted on the notice board of Room RR-108 outside the Computer Room. A user can book workstation HKUX15 one day ahead by filling his name on the form. A user can also book the workstation HKUX15 two weeks in advance by contacting Mr. Stephen K K Wong on 8592496.
In addition, to assist interested users in getting started to work on the DECmpp 12000 system, a training course has been scheduled in June. For details of this training course, please refer to the Training News section of this newsletter.
If you have queries about this DECmpp 12000 system, please don't hesitate to contact our Help Desk at Room RR-109 or the undersigned.
C M Mak Tel.:8592491 E-mail: email@example.com
The use of modems for access to Centre's computer systems, especially during the evening and night periods, is now very common among our users. However, the limited resources available, which include a pool of ten 24-hour service lines and a pool of five night-service lines, are far from adequate to meet the heavy demand of the facilities. It is often difficult and frustrated for our users to get access to Centre's computers through dial-up lines during the evening and night periods. We have been looking for solutions to improve the dial-up line availability. However, we are not able to provide a large number of additional lines due to the capacity constraint of the University's existing PABX system. One solution that we shall implement is to regulate the use of the lines by limiting each dial-up session to a prescribed maximum period, so that the limited resources can be shared among a larger user population.
With the help of the Estates Office, we are pleased to announced that three more night-service dial-up lines will be put into service starting from July 1, 1993. (We hope that we can further add a few more lines a short time later.) These lines will form a new pool of dial-up lines which can be accessed through the number 8597992. The modems of these new lines support line speeds (and standards) of 14400 baud (V.32bis), 9600 baud (V.32), 2400 baud (V.22bis) and 1200 baud (V.22). The modems also support the MNP5 and V.42bis compression. Other than introducing the new modem pool, we are also preparing to upgrade the existing modems to this high-speed model.
Like the existing night-service lines, the new facilities will only be available from 6 p.m. to 8 a.m. next morning. Furthermore, there will be a maximum connection time of 30 minutes for each connection session. Unlike the existing dial-up lines which are connected to the Develcon Data Switch (D.S.), the new lines are directly connected to Centre's VAX 6420 (HKUCC) system. When a user has successfully dialled for a session and got the "Username" prompt of the HKUCC system, he has to enter "HKUDIAL30" and then hit the "Return" or "Enter" key. The system will then display on the screen a menu of computer systems with which terminal session can be made. When the maximum connection time expires, the system will send the user a warning message and disconnect the dial-up session in 5 minutes.
To facilitate users to work with the new dial-up lines, our Centre's tailored Kermit Installation Package has been updated with a new command - HKUDIAL30. Entering this command at the "Kermit-MS>" prompt when using Centre's tailored Kermit program will automatically lead the user to the stage of selecting his desirable system to work with. The Kermit Installation Package can be borrowed from Centre's General Office, Room 223, Run Run Shaw Building. For detailed information on the tailored Kermit package, please refer to the User Document No. 144 "Running MS-Kermit V3.10 on IBM PC and Compatibles"
Summary of the new dial-up facilities:
Access number : 8597992 Standard supported: V.32bis (14,400 baud) V.32 ( 9,600 baud) V.22bis ( 2,400 baud) V.22 ( 1,200 baud) MNP5, V.42, V.42bis (error correction, data compression) Command/Username: HKUDIAL30 C Y W Cheung Tel.:8592497 E-mail: firstname.lastname@example.org
Computer Centre is pleased to announce that with effect from July 1, 1993, there will be a reduction in maintenance charge for PC systems which are serviced by the in-house Maintenance Team of Computer Centre . The price reduction will have effect on all models of PCs which include the PC/XT, PC/AT, 386 and 486 systems. In comparison with the old rate, the new rate is about 20% to more than 50% lower, depending on the system model and configuration. The aim of Computer Centre's in-house maintenance service is to provide cost effective and efficient hardware maintenance support to the large and ever growing population of PC equipment of the University. We can keep the maintenance charges at a low level because
(a) we could reclaim sufficient spare parts from PC/XT systems which are declared obsolete by user departments,
(b) there has been price reduction for some spare parts, particularly the 486 CPU boards and large hard-disks (100 MB or above), and
(c) more users are joining the in-house maintenance scheme.
The price reduction will lead to substantial savings to the user departments and we believe that more will be attracted to join in the scheme this year.
We would like to explain to our users here that the separate service charges for the individual components of a PC system (e.g. monitors, hard-disks, etc.) are primarily provided to facilitate the calculation of the maintenance charge for a complete PC system which may vary in its configuration (a complete system should include at least a CPU system and a display unit) and that we would only provide the maintenance service on per system basis.
The new prices for the maintenance of those common PC systems (which have been recommended by Computer Centre ) are summarised as follows:
Equipment type Annual Charge XT Compatible (Commodore PC-10/PC-10 II/PC-10 III,Sigma XT) 140.00 640K memory +monochrome display +1 printer port +1 or 2 serial port +2 Floppy drives & controller XT Compatible with 30MB & below Hard- disk 200.00 386SX/AT Compatible (Sigma AT, MBA 286, Quantum 286, C&D 386SX) 270.00 2M or below memory +monochrome display card +1 printer port +1 or 2 serial port +2 floppy drives & controller 386SX/AT Compatible with 80MB Hard Disk & below 380.00 386SX/AT Compatible with 120MB Hard Disk & below 400.00 386SX/AT Compatible with 240MB Hard Disk & below 470.00 386DX/486SX Compatible (Comtech, Sigma, Connect, Imagineering) 64KB Cache memory + 4M or below memory + 450.00 monochrome display card + 1 printer port + 1 or 2 serial port + 2 floppy drives & controller + 80MB Hard Disk & below 386DX Compatible with 120MB Hard Disk & below 470.00 386DX Compatible with 240MB Hard Disk & below 540.00 486DX Compatible-33MHz (Comtech) 580.00 256KB Cache memory + 4M or below memory + monochrome display card + 1 printer port + 1 or 2 serial port +2 floppy drives & controller + 80MB Hard Disk & below 486DX Compatible with 120MB Hard Disk & below 600.00 486DX Compatible with 240MB Hard Disk & below 670.00 Monochrome Monitor 50.00 CGA/EGA/VGA Monitor 140.00 EGA/VGA display card with 1MB or below 30.00 (for substituting monochrome display card)Computer Centre's in-house Maintenance Team also provides maintenance service for some other popular PC systems and printers. For comprehensive information on the maintenance charges, including those for the other items which are not covered above, please make use of the CWIS service (discussed in another article). Users can also call our General Office at 8592491 for the required information. Should any user department be interested in joining the maintenance service, please contact our General Office for the Maintenance Request Form.
T.Y. Shen Tel.: 8592482 E-mail:email@example.com
The University's current financial year will end on June 30, 1993. For any department requisitions on computer related products that have to be submitted through Computer Centre and to be included in the 1992-93 accounts, we will expect the requisitions to arrive at our Centre on or before June 11, 1993. These include purchases of diskettes, ribbons, paper and other consumables. Any late submissions will have to be charged to the 1993-94 accounts.
We have installed the latest BMDP Release 7.0 Available on HKUCC release of BMDP Statistical Software, Release 7.0, on the HKUCC system. The existing version of BMDP, Version 1990, will continue to be functional until June 30,1993.
However, users are encouraged to use the latest release of BMDP now so that we may have more time to identify and solve any problems that might occur with the new software.
To run BMDP Release 7.0, you have to type the following commands at system prompt:
$ @HKU:BMDP7 $ BMDP xx IN=infile OUT=outfile where xx is one of the BMDP program namesFor the old version, use the following commands instead:
$ @HKU:BMDP $ BMDP xx IN=infile OUT=outfilehere is a summary of the new features and enhancements of BMDP release 7.0.
5M - Linear and quadratic discriminant function analysis
Mentor is an interactive menu-driven system that uses a question and answer format to generate BMDP instructions for 1D, 2D, 3D, 6D, 7D, 4F, 1R, 2R, 2V and LR programs. It features pop-up menus that prompt user for specific information regarding the analysis and is designed to assist novice and occasional users, but also serves as a powerful tool for even experienced users.
2. Graphical BMDP(GBMDP)
It is a newly designed graphical user interface that features menus for running the BMDP system. Users can choose options such as selecting a program, editing files, getting help, working with plots and accessing Mentor quickly. It also allows direct access to the new interactive high-resolution graphics available in Release 7.0.
3. General enhancements in Release 7
- Many of the programs on VAX/VMS now produce high resolution plots, which can be saved in either HPGL or PostScript formats.
- many programs have new print features which supply printout of means, standard deviations, z-scores etc.
- the instructions for grouping variables have been made uniform throughout all BMDP programs.
4 Specific program enhancements
1D - Now works interactively 2D - Now works interactively 2L - Competing risk analysis was added 4M - Now prints matrix of eigenvalues, goodness-of-fit chi-square test, Cronbachs' alpha, and log-likelihood at each iteration. 7M - Additional interactive capabilities have been added 1R - Durbin-Watson test is added 2R - Durbin-Watson test and serial correlations are added 3R - Durbin-Watson test was added 4R - Now prints coefficients for all variables, including those not included in the model LR - Receiver Operator Characteristic (ROC) plots are now available 3S - Kolmogorov-Smirnov test is added 1T - Supports additional SAVE features References 1. BMDP Statistical Software Manual, Volume 1, (Version 7.0), 1992. 2. BMDP Statistical Software Manual, Volume 2, (Version 7.0), 1992. 3. Graphical BMDP, User's Guide, 1993. 4. BMDP Statistical Software Manual, The Data Manager, Volume 3, (Version 7.0), 1992. S.L. Lee Tel.: 8592485 E-mail: firstname.lastname@example.org
The Standard Version of Minitab Release 9 includes everything that was provided in Release 7. (Release 8 is available for PC or Macintosh computers only.) To run Minitab Release 9.0, you have to type the following commands at system prompt:
$ @HKU:MINITAB9 $ MINITABFor the old version, use the following commands instead:
$ @HKU:MINITAB $ MINITABThe new commands and features of Minitab Release 9 are summarized as follows :
Minitab's new macro language includes control structures such as DO- loops, IF and WHILE statements, GOTO statements, subroutines, debugging tools etc. Old macros are fully supported in Release 9. Besides, old and new macros are compatible with each other so that old macros can call new ones and vice versa.
Multiple Comparison Procedures
You can perform any of the four multiple comparison procedures, namely Tukey, Fisher, Dunnett and MCB, with ONEWAY.
SRESIDUALS and FITS which are new subcommands for REGRESS allow you to store standard residuals and fitted values using subcommands.
The new command FACTOR is for factor analysis. Initial factor extraction may be done using principal components or maximum likelihood. The initial solution may be rotated using varimax, quartimax, equamax or orthomax rotations.
Multivariate Analysis of Variance
A new subcommand MANOVA, which you may use with both ANOVA and GLM, includes four multivariate tests Wilk's test, Lawley-Hotelling test, Pillai's test and Roy's maximum root test.
Input and Output of data
TAB is a new subcommand for READ and WRITE that gives you two capabilities : it allows you to import and export tab-delimited data files, and it enables you to enter any combination of alphanumeric data from the keyboard without using the FORMAT command.
If you have made changes to your worksheet without saving it, and you enter the command STOP, RESTART, or RETRIEVE, Minitab gives you a chance to save your worksheet before you lose it.
You can name stored constants and matrices. Thus, keeping track of the variables in your worksheet is much easier.
MINITAB Reference Manual, Release 9, December 1992.
S.L. Lee Tel.: 8592485 E-mail: email@example.com
Owing to the increased use of SPSS/PC+ licences by the UPCC member institutions over the past years, the UPCC has recently reduced the cost that an institution has to contribute for the installation of each SPSS/PC+ module. Thus, we are pleased to announce that with effect from 1 July, 1993, the charge for installing SPSS/PC+ modules for users of the University will be revised as follows:
Modules Unit Price (HK$) SPSS/PC+ V4.0 Base, Statistics 500 and Advanced Statistic SPSS/PC+ V4.0 Data Entry II 500 SPSS/PC+ V4.0 Tables program 500 SPSS/PC+ V4.0 Graphics program 500 SPSS/PC+ V4.0 Trends program 500 SPSS/PC+ V4.0 Mapping program 500While the charge for the 3 basic modules, namely Base, Statistics and Advanced Statistics, remains unchanged, the charges for all other modules are reduced from the original $1,000 to $500.
To request installation of the SPSS/PC+ software, interested users should issue departmental requisition forms to Computer Centre according to the above schedule.
T Y Shen Tel.: 8592482 E-mail: firstname.lastname@example.org
The UPCC has recently arranged with the local distributor of SPSS software to provide site licences for 4 modules of SPSS for Windows. The modules available and the prices set for installing them are:
Modules Unit Price (HK$) SPSS for Windows Base 600 SPSS Professional Statistics 600 SPSS Advanced Statistics 600 SPSS Tables 600Interested users may request the installation of SPSS for Windows by issuing departmental requisition forms to Computer Centre .
To run SPSS for Windows, the PC should meet the following specifications:
- 286-, 386- and 486- based PC system; 386 or better strongly recommended.
- 4MB RAM minimum.
- Hard-disk with at least 15MB of storage space (for Base module only, additional modules require more space.)
- PC or MS-DOS 3.0 or above.
- Microsoft Windows 3.0 or higher.
- Windows-compatible mouse strongly recommended.
- Math co-processor strongly recommended for significant graphics work.
- EGA or VGA monitor or Hercules graphics card.
SPSS for Windows features a long list of advantages over SPSS/PC+, some of the more notable ones are (unless otherwise stated, all the followings apply to the Base module) :
Faster learning and working
- Full Windows interface.
- View several different windows of different types (data, output, charts) simultaneously.
- Get hypertext help on menus and commands, on-line glossary for explanations of statistical terms.
More extensive data and file management capability
- Expand the number of variables from 500 up to a logical limit of 32K, depending on the system configuration and makeup of the data file.
- Import data from Oracle and SQLServer through SQL queries.
- Read and write SPSS/PC+ files.
- Translate files to and from Excel, data and dictionary files from SAS.
Expanded statistical procedures and utilities
- Support multidimensional scaling and unfolding (SPSS Professional Statistics).
- Compute measures of similarity, dissimilarity or distance (SPSS Advanced Statistics).
- Handle multiple response data, calculate nonparametric correlations and partial correlation coefficients.
Fully integrated, editable graphics
- Produce full-colour, high-resolution charts automatically from statistical procedures.
- Edit charts interactively using object-oriented editing tools and WYSIWYG display.
Breaking DOS restrictions
- Use extended memory to break 640KB RAM limit.
- Access virtual memory in 386-enhanced mode.
T Y Shen Tel.: 8592482 E-mail: email@example.com
Relational Database Management Systems (RDBMSs) are the integral and essential parts of Information Systems of the 1990s. To keep up with the technology in this area, we have installed the ORACLE RDBMS on several systems, one of which (the one on HKUSUB) is to serve the potential academic or research users who are interested in employing the software for the implementation of their systems.
As the follow-up of describing the SQL*Loader module of the ORACLE RDBMS on HKUSUB in the last issue of Computer News, we consider it to be appropriate to put up a series of articles in this column for the various ORACLE software modules that may be of interest to our readers.
SQL*Plus is one of the components of the ORACLE RDBMS software installed on the HKUSUB system. It provides you with an environment for using SQL (Structured Query Language) commands interactively. It supports the ANSI SQL & ISO SQL standards with some other extensions.
The main advantage of using SQL commands is that you need to remember only a few basic commands and clauses in order to formulate your own queries and obtain information from databases. You don't have to rely on programmers to write/modify programs for reports unless complex layout and format or special processing are required. Many database software, like DB2 & SQL/DS of IBM, Ingres, Informix, Sybase and ORACLE, support SQL which is considered the de facto standard database query language. Even PC DBMS package like Dbase IV also has the features for supporting SQL.
We intend to highlight here the important features of SQL commands through the discussions of SQL*Plus of ORACLE. Examples are used for illustration where appropriate. For detailed information, please refer to the ORACLE manuals or related user guides/references.
In order to use the ORACLE SQL*Plus in the SPARCserver 670 MP (HKUSUB), you must apply separately for an ORACLE account in addition to the HKUSUB account. To get into the SQL*Plus environment after you have logged on the system, you have to issue the following command:
sqlplus usernamethen the ORACLE system will prompt you for password.
enter password:You can change the password for your ORACLE account by issuing the following SQL command at the SQL*Plus prompt (SQL>) :
GRANT CONNECT TO username IDENTIFIED BY newpassword;where username, password and newpassword are values that you have to supply.
2 Using SQL*Plus
The most important SQL command is the SELECT command which lets you query information from the underlying relational databases which consist of relations (tables). You only need to specify what you want but not how to retrieve the information.
A database retrieval is called a query and you can use the SELECT command to issue a query. The basic SELECT command consists of the following two clauses:
SELECT some data (column name(s)) FROM a table or some tables (table name(s))The SELECT clause is always immediately followed by the FROM clause.
Example: Select all columns and rows from the tables DEPT and EMP
SELECT * FROM DEPT; DEPTNO DNAME LOC ------ ----- --- 10 ACCOUNTING NEW YORK 20 RESEARCH DALLAS 30 SALES CHICAGO 40 OPERATIONS BOSTON SELECT * FROM EMP; EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ----- ----- --- --- -------- --- ---- ------ 7369 SMITH CLERK 7902 17-DEC-80 800 20 7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30 7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30 7566 JONES MANAGER 7839 02-APR-81 2974 20The sequence of the columns displayed is determined by the order in which you defined them in creating the table.
You can select specific columns by naming them in the SELECT clause.
You can select specific rows by using the WHERE clause which instructs the ORACLE to return only those rows which meet the selection criteria. You can deal with conditions using the OR, AND and NOT operators.
Example: List the employee number, name and salary of all salesmen with salary more than $1,300
SELECT EMPNO, ENAME, SAL FROM EMP WHERE JOB = 'SALESMAN' AND SAL > 1300; EMPNO ENAME SAL ----- ----- --- 7499 ALLEN 1600You can specify conditions by using:
- the BETWEEN operator which allows you to select the rows that contain values within a specified range
- the IN operator which allows you to select the rows containing any of the values given in a specified value list
- the LIKE operator which allows you to select the rows that match a pattern of characters or numbers specified by you
4 Example: Select information for those employees whose names contain an 'A' or consist of 5 characters, the last 2 of which are 'EN'
SELECT * FROM EMP WHERE ENAME LIKE '%A%' OR ENAME LIKE '___EN';where the symbol % stands for 0 or more characters and the symbol _ stands for a single character.
You can arrange the row ordering of a query result by using an ORDER BY clause.
Example: List all employees of department No. 30 by salary order
SELECT * FROM EMP WHERE DEPTNO=30 ORDER BY SAL;The default is in ascending order; for descending order use the clause ORDER BY SAL DESC instead of the clause ORDER BY SAL for the above example.
You can eliminate duplicated values by specifying DISTINCT in your SELECT clause.
Example: SELECT DISTINCT JOB FROM EMP
More complicated queries:
a) Querying multiple tables (JOIN query)
Example: Find out where the employee ALLEN works
SELECT ENAME , LOC FROM EMP , DEPT WHERE ENAME = 'ALLEN' AND EMP.DEPTNO = DEPT.DEPTNO; ENAME LOC ----- --- ALLEN CHICAGOIn the above "JOIN" query, you list the names of the tables for which you are querying in the FROM clause, and the names of the columns to be matched in the query in the WHERE clause.
b) Select summary information from groups of rows (GROUP BY query)
Example: Group the employees according to their department numbers and then find the maximum salary in each department group
SELECT DEPTNO, MAX(SAL) FROM EMP GROUP BY DEPTNO; DEPTNO MAX(SAL) ------ -------- 20 2974 30 1600In a "GROUP BY" query like the one shown above, each row of the query result represents a group of rows stored in your table.
Example: Find out how many employees are working in each department and what are the sums and averages of the employee salaries of each department
SELECT DNAME,LOC, SUM(SAL), COUNT(*), AVG(SAL) FROM EMP, DEPT WHERE EMP.DEPTNO = DEPT.DEPTNO GROUP BY DNAME,JOB; DNAME LOC SUM(SAL) COUNT(*) AVG(SAL) ----- --- -------- -------- -------- RESEARCH DALLAS 3774 2 1887 SALES CHICAGO 2850 2 1425c) Subquery The WHERE clause of a query may contain another query which is called subquery. Thus, you can use the subquery to dynamically build a search-condition for your main query.
Example List out all the employees who have the same job as "WARD"
SELECT ENAME, JOB FROM EMP WHERE JOB = (SELECT JOB FROM EMP WHERE ENAME = 'WARD'); ENAME JOB ----- --- ALLEN SALESMAN WARD SALESMANAfter examining the few examples discussed above, we should appreciate the power and merits of SQL and RDBM. It is hard to imagine what coding would be involved if you were to generate the above reports by using Dbase IV or COBOL !
We shall cover some other SQL commands for data definition and manipulations in the coming issue of Computer News.
S L Lee & K T Wan Tel.: 8592485 & 8597971 E-mail: firstname.lastname@example.org email@example.com
We have scheduled several seminars and workshops in June and July. The Seminar for DEC MPP is organised to assist users to make full use of the new facility/service offered on the DEC MPP 1200 system (please refer to the respective article in the General News section). Also, a workshop on Microsoft Windows is newly organised for introducing this popular PC user interface software to the interested users.
Two Campus Network User Training Workshops will also be held (one in June and one in July) for staff members to find out how to use the various network services. This course proves to be quite successful and useful as we always receive more than 20 registration forms for each course. Interested users should hurry up in registering for the course. Otherwise, they will be placed in the waiting list.
Course 22/92 or 23/92 - Campus Network User Training Workshop
This is a 3-hour seminar in 2 sessions (1.5 hours each) which covers all the services provided on the University campus network, especially those related to the departmental LAN's (e.g. HKUMENU, network printing, file sharing etc.) and the host central systems (e.g. E- mail, DRA Library System, SENDFAX, UIS, and the access to Internet, CWIS etc.). There will be hands-on practice for some of the facilities discussed in the form of a workshop.
Course 24/92 - Seminar on DEC MPP
DEC MPP is a massively parallel computer. It has 8192 processors that operate under a Single Instruction stream Multiple Data stream (SIMD) mode, i.e. all processors execute the same instruction from a single serial instruction stream simultaneously to operate on independent data. Such a system is most suitable for applications in which the same operations are being performed on thousands of data points at the same time.
The purpose of this seminar is to introduce the DEC MPP which is located in CUHK and the facilities setup in the HKU Computer Centre for accessing the DEC MPP system, and to provide hints on converting programs to the MPP system for improved performance.
Content: - Introduction to massively parallel computer - The DEC MPP system overview - DEC MPP parallel programming development tools - MasPar Fortran (Fortran 90 compiler) - MasPar Language (Superset of the original Kernighan-Ritchie "C") - Libraries - Hints on converting FORTRAN programs to the DEC MPP system for improved performance - Facilities in HKU Computer Centre for accessing the DEC MPP system - Procedures of getting access to the systemPre-requisite: Knowledge of programming using FORTRAN and/or C
Duration: 1 hour
Course 25/92 - Workshop on MS Windows
This workshop is to help the users who are new to MS Windows to understand its basic concepts and operations. Emphasis is placed on explaining and practising the skill you need to work with MS Windows. However, the use of specific application software run under MS Windows, e.g. MS Word for Windows, WordPerfect for Windows, etc., will not be covered.
Upon completion of this workshop, users will be able to get started working in the MS Windows environment and have the basic understanding of the following areas:
Registration: Please complete and return the attached Registration Form to Computer Centre . Successful applicants will be notified by telephone. For any enquiries, please contact Miss Heti Cheng (tel.: 8592491).
Course Title: Date/Time Venue: No: 22/92 Campus Network Jun 21, 1993 Registration required. User Training 3:30m - 5:00 pm Max. 20 persons. Workshop Jun 23, 1993 Staff Only. 3:30 pm - 5:00 pm Conducted in English. RR201, Run Run Shaw Bldg. 23/92 Campus Network Jul 26, 1993 Registration required. User Training 3:30m - 5:00 pm Max. 20 persons. Workshop Jul 29, 1993 Staff Only. 3:30 pm - 5:00 pm Conducted in English. RR201, Run Run Shaw Bldg. 24/92 Seminar for DEC MPP Jun 22, 1993 Registration required. 3:00 pm - 4:00 pm Conducted in English. RR207, Run Run Shaw Bldg. 18/92 Workshop on Jun 23, 1993 Registration required. MS Windows 2:00 pm - 5:00 pm Max. 20 persons. RR101, Staff Only. Run Run Shaw Bldg. Conducted in English.