Querying Sybase Database
Implementing Database in Sybase – Part 10
Forward: In this part of the series, we start seeing how you can retrieve data from a Sybase SQL Anywhere 12 database. Retrieving data means querying the database.
By: Chrysanthus Date Published: 27 Aug 2012
Introduction
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.
What to Retrieve
In this part of the series we shall see how to retrieve all the data from a table. We shall take this as an opportunity to retrieve, (see) all the data in each of the table we have created. In the following parts, we shall see how to write queries to retrieve data of your choice from one table or from different tables.
The main SQL Statement
The main SQL statement to query a database is called the SELECT Statement. You retrieve all the data (in all columns and all rows) from a particular table with the following statement:
SELECT * FROM table-name;
The asterisk (*) in the statement means everything. Replace table-name with the name of the table.
Illustration
The rest of this tutorial feeds in data to the tables and then displays (retrieves) them. We shall feed in data to the tables that do not have foreign keys first before we feed in data to the table with foreign keys. This is because a foreign key must exist first as a primary key elsewhere before you can have it in a table, in order to respect referential integrity.
We shall begin by adding more rows to the Products table.
- Open the command prompt window and type the following commands to start the database server and the database and to connect to the database as you open Interactive SQL.
cd c:\
dbeng12 -n wholesalesrv c:\wholesale\wholesale.db
dbisql -c "server=wholesalesrv;DBF=c:\sholesale\wholesale.db;UID=DBA;PWD=sql"
- Type and execute the following sets of SQL Statements, deleting a previously typed set before you execute the next set (you may copy and paste); a complete set may take time to execute:
INSERT INTO Products (ProductName, Category, Number, CostPrice, SellingPrice) VALUES ('VCD', 'Entertainment', '50', '20', '25');
INSERT INTO Products (ProductName, Category, Number, CostPrice, SellingPrice) VALUES ('Clothe Box', 'Household', '45', '16', '21');
INSERT INTO Products (ProductName, Category, Number, CostPrice, SellingPrice) VALUES ('Perfume', 'Beauty', '100', '2', '3');
INSERT INTO Products (ProductName, Category, Number, CostPrice, SellingPrice) VALUES ('Banana', 'Fruit', '125', '5', '7');
INSERT INTO Products (ProductName, Category, Number, CostPrice, SellingPrice) VALUES ('Pear', 'Fruit', '135', '3', '4');
INSERT INTO Customers (Name, Address, City, State, Country, Phone) VALUES ('Great Lakes Food Shop', '2732 Baker Blvd', 'Eugene', 'OR', 'USA', '514 555-7555');
INSERT INTO Customers (Name, Address, City, State, Country, Phone) VALUES ('John Steel', 'Sing Terrace', 'Walla', 'WA', 'USA', '509-555-6221');
INSERT INTO Customers (Name, Address, City, State, Country, Phone) VALUES ('Start and Shop', '87 Polk St.Suite 5', 'San Francisco', 'CA', 'USA', '415-655-5938');
INSERT INTO Customers (Name, Address, City, State, Country, Phone) VALUES ('Lonesome Gold Restaurant', 'Chiaroscuro Rd.', 'Portland', 'OR', 'USA', '503-555-9573');
INSERT INTO Customers (Name, Address, City, State, Country, Phone) VALUES ('New World Delicatessen', 'Bering St.', 'Anchorage', 'AK', 'USA', '907-555-7584');
INSERT INTO Suppliers (Name, Address, City, State, Country, Phone) VALUES ('Specialty Biscuits, Ltd', '29 Kings Way', 'Manchester', 'M14', 'USA', '161-555-4448');
INSERT INTO Suppliers (Name, Address, City, State, Country, Phone) VALUES ('New England Cannery', '2200 Paul Revere Blvd.', 'Boston', 'MA', 'USA', '617-555-3267');
INSERT INTO Suppliers (Name, Address, City, State, Country, Phone) VALUES ('Bigfoot Breweries', '3400 - 8th Avenue Suite 210', 'Bend', 'OR', 'USA', '503-555-9931');
INSERT INTO Suppliers (Name, Address, City, State, Country, Phone) VALUES ('Grandma Kellys Homestead', '707 Oxford Rd.', 'Ann Arbor', 'MI', 'USA', '313-555-3349');
INSERT INTO Suppliers (Name, Address, City, State, Country, Phone) VALUES ('New Orleans Cajun Delights', 'P.O. Box 25934', 'New Orleans', 'LA', 'USA', '100-555-4822');
INSERT INTO Employee (Name, Address, City, State, Country, Phone, Manager) VALUES ('Mary Jones', '187 Suffolk Ln.', 'Boston', 'MA', 'USA', '208-555-8097', 1);
INSERT INTO Employee (Name, Address, City, State, Country, Phone, Manager) VALUES ('John Jackson', 'P.O. Box 555', 'Boston', 'MA', 'USA', '307-555-6525', 1);
INSERT INTO Employee (Name, Address, City, State, Country, Phone, Manager) VALUES ('Roy M Gild', '89 Jefferson Way Suite 2', 'Boston', 'MA', 'USA', '503-555-3612', 2);
INSERT INTO Employee (Name, Address, City, State, Country, Phone, Manager) VALUES ('Grace Peterson', '55 Grizzly Peak Rd.', 'Boston', 'MA', 'USA', '406-555-8083', 2);
INSERT INTO Sales (EmployeeID, CustomerID) VALUES (2, 1);
INSERT INTO Sales (EmployeeID, CustomerID) VALUES (1, 3);
INSERT INTO Sales (EmployeeID, CustomerID) VALUES (5, 1);
INSERT INTO Sales (EmployeeID, CustomerID) VALUES (3, 4);
INSERT INTO Sales (EmployeeID, CustomerID) VALUES (1, 2);
INSERT INTO SaleDetails (SaleID, ProductID, SoldPrice) VALUES (1, 1, 0.75);
INSERT INTO SaleDetails (SaleID, ProductID, SoldPrice) VALUES (1, 3, 3);
INSERT INTO SaleDetails (SaleID, ProductID, SoldPrice) VALUES (1, 5, 1.25);
INSERT INTO SaleDetails (SaleID, ProductID, SoldPrice) VALUES (2, 7, 21);
INSERT INTO SaleDetails (SaleID, ProductID, SoldPrice) VALUES (2, 9, 3);
INSERT INTO Orders (EmployeeID, SupplierID) VALUES (2, 1);
INSERT INTO Orders (EmployeeID, SupplierID) VALUES (1, 3);
INSERT INTO Orders (EmployeeID, SupplierID) VALUES (5, 1);
INSERT INTO Orders (EmployeeID, SupplierID) VALUES (3, 4);
INSERT INTO Orders (EmployeeID, SupplierID) VALUES (1, 2);
INSERT INTO OrderDetails (OrderID, ProductID, BoughtPrice, Discount) VALUES (1, 1, 0.5,0);
INSERT INTO OrderDetails (OrderID, ProductID, BoughtPrice, Discount) VALUES (1, 3, 2,0);
INSERT INTO OrderDetails (OrderID, ProductID, BoughtPrice, Discount) VALUES (1, 5, 1,0);
INSERT INTO OrderDetails (OrderID, ProductID, BoughtPrice, Discount) VALUES (2, 7, 20,0);
INSERT INTO OrderDetails (OrderID, ProductID, BoughtPrice, Discount) VALUES (2, 9, 2,0);
- Type and execute each of the following statements in Interactive SQL; erase the previously typed statement before you type a new one; the result is displayed in the Results Pane:
SELECT * FROM Products;
SELECT * FROM Customers;
SELECT * FROM Suppliers;
SELECT * FROM Employee;
SELECT * FROM Sales;
SELECT * FROM SaleDetails;
SELECT * FROM Orders;
SELECT * FROM OrderDetails;
- Now stop the database server at the command prompt with the following command; type y if asked; close the Interactive SQL window also.
dbstop wholesalesrv
Well, we have just known what the SELECT Statement is dealing with. We shall see more about it in the following parts of the series.
Chrys
Related Courses
C++ CourseRelational Database and Sybase
Windows User Interface
Computer Programmer – A Jack of all Trade – Poem
NEXT