QUICK GUIDE TO SQL WITH DB2 ON WINDOWS NT

1. QUICK GUIDE TO SQL WITH DB2 ON WINDOWS NT By Timothy Paul Cronan, Jennifer Kreie, and Lori Komp Leonard

2. In response to recent technological advances and changes in corporate computing options, IBM released a version of DB2/5.0 which runs under the Windows NT operating system. DB2/5.0 for Windows NT is functionally the same as the traditional mainframe DB2/2. Databases can be created and accessed directly through the DB2 command line processor or with SQL commands embedded in COBOL or other higher level languages. The focus of the PC-based version of DB2/5.0 is to provide distributed database capability in a client/server environment.

3. Logging on to the network and DB2/5.0 If the regular Windows NT screen is displayed when you sit down at a computer, then you should log off (see the following section) and log back on. Why? Because it is important that you know for certain how the client is currently logged on the network. It is possible for someone using a different user ID to have left without logging off the system. If you work in DB2/2 under their user ID you may have problems. Logging on: A small window should be displayed that says “Press Ctrl + Alt + Del.” Press the Ctrl+Alt+Del keys. This will bring up a logon dialog box. Enter your username (assigned by Dr. Cronan) and your password. The password is case-sensitive so enter uppercase or lowercase letters exactly as they are given to you for your password. A message will appear that the logon was successful.

4. Logging off the network and DB2/2 It is important to log off before you leave the computer you’re working on. If you don’t log off the next person that uses that computer can access your database. 1. Exit any current application, such as DB2/5.0. 2. Close all the open windows. 3. Press the Start button on the main menu bar. 4. Select the option Shut down the computer, select the Yes button.

5. Connecting to a database and creating tables (EXERCISE 1) 1. Open the IBM DB2 menu (under the list of Programs in the Start menu). Click on the DB2 Client Configuration Assistant option. 2. A list of the databases will appear. Highlight your database, then press the Test button. 3. The IBM DB2 ODBC Driver window will appear. Enter your username and password, then click on OK. The message connection tested successfully will appear indicating that the client has established connectivity with the server. 4. Close the IBM DB2 ODBC Driver window and close the DB2 Client Configuration Assistant window.

6. Accessing the DB2 command line and creating TABLES 1. Click on the DB2 command line processor option under IBM DB2 menu option. 2. The DB2 - CLP window will appear. At the command prompt type in CONNECT TOdatabasenameUSERusername; (Don’t forget the semi-colon.) 3. Enter you password when prompted. 4. The screen will display the following information. Indicating that the database is connected successfully. Database product = DB2/NT 5.0.0 SQL authorization ID = username Local database alias = databasename 5. At the DB2 command line prompt enter the command CREATE TABLEtablename ( column_name type,......);

7. Entering data in a table (EXERCISE 2) At the DB2 command line prompt use the following command format to insert data in the tables you created. INSERT INTOtable_nameVALUES (value1,value2,.....);

8. Data Manipulation Language (DML) functions SELECT [DISTINCT] [* / item1, item2, ...] • FROM userid.tablename • WHERE search-condition • GROUP BY colname1, colname2,... • HAVING search-condition • ORDER BY colname1 ASC, colname2 DESC ... * is a wildcard indicating all columns (attributes)

9. SELECT * FROM STUDENT WHERE AGE < 20 ORDER BY NAME ASC A list of all attributes (ordered or sorted in ascending order by names of students) of students who are younger than 20 years.

10. Relational Algebra PROJECT (Attributes/Columns) SELECT colname1, colname2,... FROM STUDENT SELECT * FROM STUDENT (This in effect is listing the entire table.) SELECT NAME FROM STUDENT (to list only one attribute, i.e., NAME from the table STUDENT) SELECT DISTINCT NAME FROM STUDENT (to list without duplication names of all students from the table STUDENT)

11. Relational Algebra SELECT (Tuples/Rows) SELECT * FROM STUDENT WHERE NAME='GLASS' (to list all attributes for a student named GLASS. SELECT NAME FROM STUDENT WHERE AGE > 20 (to list names of all students who are over 20 years of age) SELECT DISTINCT NAME FROM STUDENT WHERE AGE > 20 (to list names of students over the age of 20 without any duplication)

12. Relational Algebra JOIN SELECT NAME FROM STUDENT WHERE SID IN SELECT STUNUM FROM ENROLL WHERE CLASSNAME ='BD445') SELECT STUDENT.NAME FROM STUDENT, ENROLL WHERE STUDENT.SID = ENROLL.STUNUM AND ENROLL.CLASSNAME = 'BD445'

13. Select ENROLL where CLASSNAME='BD445' giving RESULTA. Join RESULTA, STUDENT over STUNUM, SID giving RESULTB. Project RESULTB over NAME giving ANSWER.

14. Other Useful SQL SELECT * FROM STUDENT DROP TABLE tablename (Be careful! Once you run this command the table is gone.) INSERT INTO tablename VALUES (item1, item2...) INSERT INTO STUDENT VALUES (500,'SMITH','GEOGRAPHY','FR',18)

15. Other Useful SQL Continued INSERT INTO tablename (Colname1, Colname2,...)VALUES(item1, item2,...) INSERT INTO STUDENT (AGE,SID,NAME,MAJOR,GRADELVL) VALUES (24,550,'JOHNSON','MARKETING','SN') INSERT INTO tablename (Colname1, Colname2,...) SELECT Colname1, Colname2,… FROM other-tablename WHERE search-condition

16. Other Useful SQL Continued DELETE FROM tablename WHERE search-condition DELETE FROM STUDENT WHERE NAME = 'BAKER’ ALTER TABLE tablename ADD colname data-type ALTER TABLE STUDENT ADD SEX CHAR(1)

17. Other Useful SQL Continued UPDATE tablename SET colname1 = expression 1, colname2 = expression 2, ... WHERE search-condition UPDATE STUDENT SET SEX = 'M' WHERE NAME='JONES'