Manipulating Data in Sybase

Implementing Database in Sybase – Part 9

Forward: In this part of the series, we see how to manipulate data in a Sybase SQL Anywhere 12 database table.

By: Chrysanthus Date Published: 27 Aug 2012

Introduction

This is part 9 of my series, Implementing Database in Sybase. This series is part of the fourth division of my larger series, Database. I assume you have read all the different parts of the big series (or equivalent tutorials from elsewhere) up to this point. In this part of the series, we see how to manipulate data in a Sybase SQL Anywhere 12 database table.

Note: If you cannot see the code or if you think anything is missing (broken link, image absent), just contact me at forchatrans@yahoo.com. That is, contact me for the slightest problem you have about what you are reading.

Data Manipulation
Data manipulation in database means to insert data rows into a table, update (modify the data) the rows and to delete the rows. Referential integrity comes into effect when you are doing these things. If you do any of them wrongly an error message should appear indicating that referential integrity has not been respected. Such error message will only appear if you enforced referential integrity as we did in one of the previous parts of the series.

Inserting a Row
To insert a row of data, you have to be connected to the database first, and you need to have been given the permission to do that. In simple terms, the syntax is:

INSERT INTO table-name (column-name, column-name, column-name, …) VALUES (value, value, …)

A string value should be in quotes (single quotes). We shall put in values into the Products table of the wholesale database.

- Use the following commands to start and connect to the wholesale database:

cd c:
dbeng12 -n wholesalesrv c:\wholesale\wholesale.db
dbisql -c "server=wholesalesrv;DBF=c:\sholesale\wholesale.db;UID=DBA;PWD=sql"

Read and type the following commands into the SQL Statements pane of the Interactive SQL window; avoid making a mistake (maybe you can copy and paste). Execute the commands using SQL>>Execute or the Play Button.

INSERT INTO Products (ProductName, Category, Number, CostPrice, SellingPrice) VALUES ('Pen', 'Office', '150', '0.5', '0.75');
INSERT INTO Products (ProductName, Category, Number, CostPrice, SellingPrice) VALUES ('Books', 'Office', '500', '3', '4');
INSERT INTO Products (ProductName, Category, Number, CostPrice, SellingPrice) VALUES ('Bowl', 'Household', '175', '2', '3');
INSERT INTO Products (ProductName, Category, Number, CostPrice, SellingPrice) VALUES ('Spoon', 'Household', '300', '.1', '.2');
INSERT INTO Products (ProductName, Category, Number, CostPrice, SellingPrice) VALUES ('Plate', 'Household', '450', '1', '1.25');

Any SQL statement must end with a semicolon. There should be many lines in the result (read them). Note that the ProductID column is autonumber and so it is not in the INSERT statements. The money is in dollars, but the dollar sign is not type. Amount given is for unit price. As you can see, it is possible to execute more than one statement in Interactive SQL tool.

You must be given the permission to update a row in a table before you can do so. You could insert the above rows since you are the DBA with password, “sql”. You must also be given the permission to insert or delete a row in a table. As DBA that you are, you will be able to do these as shown below.

Updating a Table
To update a table means to modify the rows. We shall modify just one row in the Products table. We shall change the string, “Office” in the Category column to “School” in the second row of the table, where the ProductID is 2.

A Syntax for the UPDATE SQL Statement is (do not worry about its complexity for now):

UPDATE [ row-limitation ] table-list
SET set-item, ...
[ FROM table-expression [,...] ]
[ WHERE search-condition ]
[ ORDER BY expression [ ASC | DESC ] , ... ]
[ OPTION( query-hint, ... ) ]

The words in uppercase are reserved words. In this tutorial, we shall not use the last two lines of this syntax. We shall not also use the FROM line. We want to set Category (column) to “School” where ProductID = 2. So, read and type and execute the following Update statement in the SQL Statement pane in the Interactive SQL window; first erase whatever is in the pane:

UPDATE Products SET Category = 'School' WHERE ProductID = 2;

Result should appear in the Results pane. Any string value of a SQL statement should be in quotes.

Deleting Rows
Let us delete the fourth row of the Products table, where the ProductID = 4. A DELETE SQL syntax is (do not worry about its complexity for now):

DELETE [ row-limitation ]
[ FROM ] [ owner.]table-or-view [ [ AS ] correlation-name ]
[ WHERE search-condition ]
[ ORDER BY { expression | integer } [ ASC | DESC ], ... ]
[ OPTION( query-hint, ... ) ]

DELETE means delete at least one row. We want to delete from the table, Products, where ProductID = 4; so we do not need the last two lines of the syntax. So, read and type and execute the following SQL statement in the SQL Statement pane and execute it.

DELETE FROM Products WHERE ProductID = 4;

You should see the result in the Results pane. Note: when an autonumber such as 4 above is deleted, it is generally not replaced the next time you add (insert) rows into the table. Assume that you insert a new row to the above table using the INSERT statement as above, the new autonumber will be 6 and not 4.

- Now go top the command prompt window and drop the connection, stop the database and stop the database server with the following command:

Do not forget to close the Interactive SQL window.

That is it for this part of the series. Let us stop here and continue in the next part.

Chrys

Broad Network

Related Articles

Manipulating Data in Sybase

Implementing Database in Sybase – Part 9

Introduction

Related Courses

Comments