peoplesoft sql basics

7/29/2019 Peoplesoft SQL Basics

1/11

http://www.vijaymukhi.com/pcode/pindex.htm

Getting Started

The secret of becoming an expert in PeopleSoft or in any of the other front-end products is inunderstanding very clearly what goes on in the background. Understand this well, any you will find that

there is a great deal that you can do with the product. And here, you will revise all that you need to know

about Structured Query Language (pronounced sequel). There is no real difference from one productsimplementation of SQL to another. At best, there are extensions to the language to give greater

functionality.

Any information is useless all by itself. Hence, to make sense of information, one needs to arrange it in

some meaningful manner or be able to order it somehow. And this is where computers can play a great

role. Computers are able to store information, and retrieve information pretty quick. However, there are

rules that one must observe in storing the data so that it can be accessed fast. There are also somecommonly used terms that one follows so as not to confuse others who work with computers as well.

Information when stored on the computer is put together as a collection of smaller individual units. One

could refer to each individual bit as a cell or a field, much like the cells and fields that one sees in

spreadsheets. Put these cells together in an orderly fashion and you have a whole set of rows and columns

that make up the worksheetterms that are also used when working with the database. A row or arecord contains different data types, all of which refer to a single item in the list. A column however, will

contain the same data type all throughout. For example, a row or record could contain a mix of character

information as well as fields that contain numeric information too. However, a column once defined will

only contain either character information or numeric information. (There are other data types that will be

explained as we go along.)

A collection of these rows and columns create what is called a Table in computerese. And a database can

have multiple tables just like an Excel or Lotus 1-2-3 spreadsheet file can have multiple worksheets. Each

sheet is unique but yet has some relation to the next which will why they are stored together. To preventeach table from growing too large in size, which would hamper handling and other operations, table

definitions are kept to a bare minimum with data that is likely to be repeated many times over pushed into

another table.

For example, if you had to write a letter that contained a list of names of students, ages and schools that

students came from, when the students came from just three schools, you would be better of doing a

substitution. Like

Jack, 12, B

Jill,15,A

Mahesh,8,C
http://www.vijaymukhi.com/pcode/pindex.htmhttp://www.vijaymukhi.com/pcode/pindex.htmhttp://www.vijaymukhi.com/pcode/pindex.htmhttp://www.vijaymukhi.com/pcode/pindex.htm


2/11

Tom,9,B

Angel,12,C

Umesh,13,C

Writing out just an A, B or C is far easier than writing the entire name of the school. Once the list is

complete, all one needs to do is to write out the name of the school once and replace the text accordingly.Hence, it wouldbe simplicity itself in locating all the As and replacing them with St.Marys High School,

all the Bs with Christ Church School and similarly for C. This is also possible in database operations. The

better the database design, the less space will it occupy when stored and the faster will work be done onany individual set of columns.

How to start and use SQLTalk

Creating tables

Making a table is not difficult at all. In fact, a single command can create a rather complex table.

However, being able to define a table that does not require major modification is another matter. Thiswould require one to analyse the situation in depth and design the overall database before starting. Butfor now, you will attempt to create a simple one field table.

In the SQL command window, key in the following command

CREATE TABLE TEST(VNO NUMBER);

Press Shift+F2 or click on Session and then Execute Command to execute the line of code. Do not forget

the semi-colon at the end of the line as this important. It lets the SQL engine know that the command is

completed. This will become clearer as you key in longer lines of code that will not fit in a single line or

that require to be spread over several lines simple make understanding easier.

In the feedback window, you will be informed that a table was created. You need to pay attention to whatyou are doing as you are not told which table was created. With multiple lines of code this could get a bit

more confusing.

What you did with the last statement was to request the SQL engine to create a table in the currently

active database which would be called or referred to as TEST. This table would only have a singlecolumn VNO, which will hold only numeric data.

To create multiple fields or columns in one go, all one needs to do is to separate each column definition

with a comma. Try the next command

CREATE TABLE SALES(VNO NUMBER, CCODE CHAR(2), PROD CHAR(2), TOTAL NUMBER);

To execute the statement, press Shift+F2 or Session | Execute Command. You will have created another

table with four fields this time. Two fields can store numerical values and two fields will be able to storenon-numerical values like text.


3/11

Filling up the table

Unlike Excel, where one can see the cells and directly enter values to each, the process of placing values

into a database table is slightly different. This is because in SQL, there is no direct graphicalrepresentation of each table as in the case of a spreadsheet. You will need to use some commands to

assign values to each cell. The simplest form of doing this is by using the INSERT command. This is

done so

INSERT INTO SALES VALUES (1, C1, P1, 10);

Do not forget the semi-colon at the end and to execute the command by pressing Shift+F2. The response

will inform you that the values have been successfully entered. Do note the method in which the

command was issued. You need to specify in which table to wish to add a row, and the values that shouldbe filled in. The values must match the specified data type for that cell. Hence, as the first and the last

fields of the SALES table have been defined as NUMBER, the values that are entered into these fields

are numeric. Where as the values inserted into the other two fields are character type.

Add a few more rows to the table so that there are sufficient records to experiment with. Use the

following statements so that you have the same results that you see on the screen.




You now have four records stored away in the SALES table. But this data is of no use unless it can be

used or displayed in some manner that makes sense to the user. To do this the programmer has to specify

the records that he requires. This is done by SELECTing. A SELECT command defines which records

should be pulled out from a table and which not. Once a selection is made, the data is then available forfurther processing. The simplest form of using this command is in the following manner.

SELECT * FROM SALES;

Execute the above statement and as there are no conditions set all the fields and all the rows of the

SALES table are selected. You will see them in the response window. This is obviously not the best wayto display the data required from a particular record or set of records. By adding on a couple of other

options to the SELECT statement, it is possible to narrow the search to locate what you want rather

quickly.

For instance, to display only the VNO, PROD and TOTAL fields, you will have to execute the followingcommand.

SELECT VNO, PROD, TOTAL FROM SALES;

As always in the interactive mode, you will get a feed back of all the records selected from the table

SALES. The only difference in this example is that the CUST field values for each record will not be

displayed at all. But this is not all that a programmer can do. Refining the SELECT command, one can


4/11

request the SQL engine to perform a small test on the data stored in a particular field on the table, before

selected the record. The next command shows you how this can be done by using the WHERE clause.

SELECT * FROM SALES WHERE VNO > 2;

All rows where the value of VNO is greater than two are selected. In our example, only the last tworecords have such a value and hence, they will be the only ones selected. SELECTing does not in any

way change the values stored in each field but it does make it easier to work with a huge database byeliminating records that one does not require.

Narrowing down the selection even further, requires a little bit more of work. A programmer can set anumber of conditions that have to be met before a record or row is chosen. Try the following:

SELECT * FROM SALES WHERE VNO >= 2 AND PROD = P1

Such a command will only select a single record from out sample table SALES. There are two conditions

that must be met for a row to be selected. The first condition defines that the value defined in the field

VNO must be equal to or greater than the value 2. This gives us three records which could potentially be

selected. The second condition further filters out from these three records any record that does notcontain the text P1 in the field PROD. This happens because the AND keyword requires both conditions

to be met before passing or selecting a row. If the AND was an OR, then either of the conditions requireto be met before a row is selected. In this case, all the rows are selected as the VNO >= 2 condition

passes the last three records, and the PROD = P1 selects the first and third records. There are no

limitations to the number of conditions that can be applied with the SELECT command except that thecondition must be equated as true.

A command like

SELECT * FROM SALES WHERE 2=2;

Is a valid command that will select all records in the table and

SELECT * FROM SALES WHERE 1=2;

Would select no records as the condition will never be fulfilled or equated as true.

Do remember while combining the different conditions, you will have to make use of brackets to definethe order in which the conditions must be applied. This will be explained in depth later.

Two or more

Being able to narrow down the records that one can see or use is what makes SQL so powerful. Therefore

, a database usually has multiple tables that can be linked together in some way. This allows the total size

of the database to be ultimately smaller than one large table that has all the values stored in it.

For example, in our SALES table, there are a couple of fields that are represented by code values i.e.

CCODE and PROD. These two refer to a customer code and a product code respectively.


5/11

VOUCHER CUSTOMER PRODUCT SALESMAN TOTAL

1 Jill Copper John 10

2 Jane Silver Tom 20

3 Mary Copper John 30

4 Jill Silver John 40

Even with such a brief description, one can see that the values Silver, Copper, John and Jill appear more

than once in the table. If for some reason, one required to store further details on each customer, productand salesman, the amount of repetition would automatically increase and so would the amount of space

wasted. SQL therefore gives the programmer the option of breaking up a single table into more

manageable bits and yet be able to present them all together.

To do this, you will need to create another three tables thus:

CREATE TABLE CUST(CCODE CHAR(2), CNAME CHAR(8), SCODE CHAR(2));

CREATE TABLE PRODUCT (PCODE CHAR(2), PNAME CHAR(8));

CREATE TABLE SALESMAN (SCODE CHAR(2), SNAME CHAR(8));

And fill in the following values:

INSERT INTO CUST VALUES(C1, Jill, S1);

INSERT INTO CUST VALUES(C2, Jane, S2);

INSERT INTO CUST VALUES(C3, Mary, S1);

INSERT INTO PRODUCT VALUES(P1, Copper);

INSERT INTO PRODUCT VALUES(P2, Silver);

INSERT INTO SALESMAN VALUES(S1, John);

INSERT INTO SALESMAN VALUES(S2, Tom);

You will now have three other tables in addition to SALES. While the number of bytes saved may not

seem much with this limited data available, multiplying into a few thousand records (which is what anormal database would contain) the number of bytes saved will keep increasing. Further, modifications

are also simplified. For instance, assigning another salesman to a customer, needs only a small

modification on one table CUST, rather than searching and modifying the entire database. This alsoallows one to keep the older transactions unchanged, as the SALES table is in no way modified. One will

be able to generate all transactions with the correct salesman being identified.


6/11

Learning to merge two or more tables together is therefore of prime importance in database

administration. The command set that one needs to use is no different than what one has already used so

far. What one needs to do is to pay attention to the manner in which the fields are merged. Try thefollowing command:

SELECT * FROM SALES, PRODUCT;

Such a command will result in a selection of rows which equals to the total number of rows of the tableSALES multiplied into the total number of rows in table PRODUCT, which in this case is 8. And thenumber of columns that the table would have is the sum of both tables SALES and PRODUCT. This

could be represented thus:

VNO CCODE PROD TOTAL PCODE PNAME

1 C1 P1 10 P1 Copper




1 C1 P1 10 P2 Silver




As you will have noticed, for every row table SALES is added to every row in table PRODUCT. This isobviously not what one would like to do. You will have noticed that there is an overlap as far as theproduct code is concerned and this is what one uses to link the two tables together. Using the same

commands that were used earlier, one now can modify the earlier command so that the rows selected

match the maximum number of records that are there in the table SALES. Try

SELECT * FROM SALES, PRODUCT WHERE PROD = PCODE;

In this form, the condition to be satisfied is defined. The value found in PROD must match the value in

PCODE. Therefore from the earlier set of eight rows or records that were selected, you now have only

those records where PROD=PCODE.

For this to work, each table must have at least one primary keywhere the contents of a field are not

repeated. In the table SALES, the VNO is the primary. In the table PRODUCT, PCODE is the primarykey. Since values in the field PROD of table SALES is not unique, it is called a Foreign key. Two or

more tables are always joined in this mannera foreign key of one table is set to match a primary key

of another table.


7/11

VNO CUST PROD TOTAL PCODE PNAME





Using this same method, it is possible to link or merge the other two tables as well. All one has to

remember to do is to identify the correct primary and secondary key in the tables to be joined. And in thesame manner, it is possible to display only the fields one wishes to see or use and not any other.

In the current example, displaying the product code is of little consequence and one could just as well

select only the following fields in the following manner:

SELECT VNO, CCODE, PNAME, TOTAL FROM SALES, PRODUCT WHERE PROD = PCODE;

While care has been taken not to repeat field or column names in these examples for clarity, it is possible

that each individual table uses the same field name. By default, each field name when used is preceded byits table name but separated by a period (.). Hence, the field name PROD is actually SALES.PROD. One

could therefore use PCODE in both the SALES and the PRODUCT tables. To identify the right PCODE

for each table, the previous command line would have to read as:

SELECT VNO, CCODE, PNAME, TOTAL FROM SALES, PRODUCT WHERE SALES.PCODE =

PRODUCT.PCODE;

Continuing to join

There is a lot more to the SELECT command than we have covered so far. Add in one more table thatwill take care of the individual items on the voucher that is stored in sales. This we will place in a table

called SALESI for sales items. Do this in the following manner

CREATE TABLE SALESI(VNO NUMBER, INO NUMBER, PCODE CHAR(2), ITOTAL NUMBER);

Insert the following records into the table

VNO INO PCODE ITOTAL

1 1 P1 6

1 2 P2 4

2 1 P1 10

2 2 P2 5


8/11

2 3 P3 5

3 1 P1 17

3 2 P2 12

3 3 P3 1

Displaying all the columns in a single statement would require the use of an asterisk (*).Using thiswithout actually qualifying how the rows are to be joined would display over 200 records. Yet the totalnumber of records from all tables just adds up to around sixteen records.

Try the following

SELECT * FROM SALES, SALESI, CUSTOMER, PRODUCT, SALESMAN;

The result will be a whole list of records each joined with every other record from every other table. To

keep the result more precise, one would first have to define how the records from different tables should

be matched up. One therefore requires to use the WHERE clause. This clause defines which fields fromone table are linked to which fields on another table. Modify the earlier statement so that the links

between one file and the next are clearly defined. You will find that using the next statement, you will

bring down the number of records listed.

SELECT * FROM SALES, SALESI, PRODUCT, CUSTOMER, SALESMAN WHERE

SALES.VNO=SALESI.VNO AND PROD=PCODE AND SALES.CCODE=CUSTOMER.CCODE;

Do remember to bear in mind the column names that you have used. While it may sound easier here to

use different column names in different tables, in the long run, program maintenance becomes simplertoo just because one will no longer have to scratch ones head to remember what different columns are

called. If you will notice, except for the different product code columns, all other fields which have

matching contents use the same field name. It is good programming practice to also keep the column typeand length of same named columns the same.

A short note on what each table contains will help you in not generating errors. Besides displaying all thecolumns by using the asterisk (*), one could also specify which columns should be displayed. A

command like

SELECT VNO, CNAME, SNAME FROM SALES, SALESMAN, CUSTOMER WHERE

SALES.CCODE=CUSTOMER.CCODE AND CUSTOMER.SCODE=SALESMAN.SCODE;

The above statement not only joins the different tables together, but it also display a column from the

each table in the order defined. This order is in no way affected by the order in which the table names are

specified. You could even use words and values that are not column names in the following manner:

SELECT X FROM SALES;


9/11

This will generate a result set that will only contain a single column labeled X and it will be repeated as

many times as there are records in the table SALES, which in this case is 3. One could do the same with

numerics, save that they do not required to be shielded by quotation marks. It is also possible to mixnumerics with columns to display a result directly. For instance,if 25 per cent of each sale was your profit

margin and you would like to see how much you made per bill, then the following would work without a

problem:

SELECT VNO, TOTAL*25/100 FROM SALES;

SQL will perform the calculation on the fly and the result is displayed at once. This would also work

across different tables, so that you can customise the output that is displayed. The arithmetic operators

can be used in any part of the command. There is nothing that prevents one from using the operators inthe WHERE clause as well. Hence the next command is also a valid one:

SELECT * FROM SALES WHERE TOTAL*12/100 >=15;

Not here or there

Another clause that makes selection easier is the BETWEEN clause. This clause helps the programmer toidentify a set of records that fall on or between to end values. To select all the rows in the table SALES

where TOTAL is equal to or greater than 11 and equal to or less than 30, one would normally code in the

following way:

SELECT * FROM SALES WHERE TOTAL BETWEEN 11 AND 30;

This option only applies to numeric information. The situation is slightly different for character strings.

One makes use of a lookup table against which a column is checked as in the next command

SELECT * FROM SALESI WHERE PCODE IN (P1, P3);

The command used this way will display only those rows of the table SALESI where the value in the

column PCODE matches any of the strings listed between the brackets. And as usual, you could also use

both these command together too by joining them with a AND or OR.

SELECT * FROM SALESI WHERE PCODE IN (P1, P3) AND ITOTAL BETWEEN 6 AND 20;

Adding it all up!

So far life was rather simple. All that we had covered till now was how to join to tables and then select a

particular set of records. However, a good deal of database operations has to do with numbers. SQL has a

number of functions and operators that allow a programmer to directly manipulate the data and displayresults. For example, displaying the total sales done to date, would require one to add up all the values in

the column TOTAL. To do this in SQL, one needs to issue a single command like so:

SELECT SUM(TOTAL) FROM SALES;

Used thus, SUM adds up all the values of the column TOTAL from the table SALES and then displays it

in a single line. Even if the table had another thousand records, there would be only a single line result.


10/11

It is not possible to display a column at while using the SUM function. One could display a text string

with the function so that the displayed line makes more sense.

SELECT Total sales to date is Rs, SUM(TOTAL) FROM SALES;

Another function that is used in the same manner as SUM is COUNT. All the rules for SUM applies toCOUNT. The only difference is that in this case, it counts the number of occurrences of a particular field

in a column.

SELECT COUNT(VNO) FROM SALES;

This gives a quick tally of the number of vouchers that have been entered into the SALES table. Mixing

this with the last function, one can generate more meaningful result sets as in the following manner.

SELECT Average sale has been, SUM(TOTAL)/COUNT(TOTAL) FROM SALES;

You will have noticed that COUNT was used with TOTAL. This makes no difference to the end result asneither of these functions exclude any record. They simply operate on the data sets that are presented to

them.

It is possible to qualify the data sets that get passes on by including a WHERE clause in the command

line.

SELECT Average sale has been, SUM(TOTAL)/COUNT(TOTAL)

FROM SALES WHERE TOTAL >= 20;

Functions abound in SQL. One, however, needs to learn to use them correctly. Some functions likeMIN(), MAX() need no explanation at all. Others like . Require you to try the functions so that you

fully understand what they can do.

Forming groups

With newer records always being entered into a table, it would be close to impossible to keep reordering

the database. What database management software does is to make some sense of the mess by applyingsimple commands. Every DBMS has some functions that allow the database to be reordered and then

displayed. It is possible to classify or group like rows together. And by joining two or more tables

together, it is also possible to display reports across tables.

With the sample tables for instance, a manager would like to know how much of which product was sold.

Or possibly how much revenue each salesman generated over a period. These results can be displayedusing the following command

SELECT SCODE, SUM(TOTAL) FROM SALES GROUP BY SCODE;

While this will display the amount each salesman made, the following join will display the name of thesalesman rather than let one guess the name from the salesmans code.


11/11

SELECT SNAME, SUM(TOTAL) FROM SALES, SALESMAN WHERE

SALES.SCODE=SALESMAN.SCODE GROUP BY SNAME;

When GROUPing rows together, there are a few rules that you have to bear in mind. The most important

of these is that you have to define the column that you would like to group on. The column name will

have to be specified as one of the columns to display and also will be the key on which the groupingtakes place. But this by itself is of no use. You will want to do something with the groups that you create.

Either you wish to count them, or sum up fields, or find the maximum and minimum values in a certaincolumn for that group or perform some other operation. This is what you can do now. You are notallowed to specify any options that requires SQL to list the entire contents of a table as except as a part of

the group row that will be displayed. Hence any attempt to say group products and display all the

customers would fail (unless one chooses to display the customers for a particular product as a sub group,

which we will come to later.)

SELECT SCODE, TOTAL FROM SALES GROUP BY SCODE;

The above statement would fail and generate an error message as TOTAL is not qualified in any manner.

peoplesoft sql basics

Documents