1 / 12

Creating a Non-Conditional List

Creating a Non-Conditional List. A- What are you going to do? You will “list” “all of the records” in a database. (it means you will not use any condition!) B- Design your database if it is not exist C- Add some records to your database (it must be some records to list them!)

fennell
Download Presentation

Creating a Non-Conditional List

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Creating a Non-Conditional List A-What are you going to do? You will “list” “all of the records” in a database. (it means you will not use any condition!) B-Design your database if it is not exist C-Add some records to your database (it must be some records to list them!) D-Create a sample design to determine how the data to be displayed... Exp 1: DVD Title: ICE AGE Time: 85 minutes Country: 1 Exp 2: E-Save the sample design web page with the extension of ASP!

  2. Creating a Non-Conditional List F-Necessary ASP codes must be written to the sample design web page • Create a Database Connection Object Open the Database Connection Object After the <BODY> Close the Database Connection Object Clear the Database Connection Variable Before the </BODY> • Create a record set Object: Set rs1 = server. CreateObject("ADODB.Recordset") Create a query set: sq1= "SELECT * FROM kayit" Fill record set with executed query result: set rs1 = conn341.execute(sq1)

  3. Creating a Non-Conditional List In order to read all of the records you must built a LOOP! - How many records will be listed? :( - Which of the fields must be listed? :) - In which presentation format? :) - Which part of the HTML code must be repeated with ASP Codes? :) do while not kayitseti.eof response.write kayitseti("kayitAdi") kayitseti.movenext loop

  4. Creating a Query Set: SQL-Structured Query Language SQL is the most common language used for creating and querying relational databases. Many users can access a database applications with no knowledge of SQL at all. Sites on the Web allow users to browse the catalog of the site being visited. The information about an item that is presented, such as size, color, etc. is stored in a database The information has been retrieved using an SQL query, but the user has not issued an SQL command. An SQL-based relational database application involves a user interface, a set of tables in the database.

  5. SQL-Structured Query Language Within the RDBMS, SQL will be used to create the tables, translate user requests, maintain the data dictionary and system catalog, update and maintain the tables, establish security and carry out backup and recovery procedures. General Syntax: SELECT [ALL|DISTINCT] column_list FROM table_list [WHERE conditional expressions] [GROUP BY group_by_column_list] [HAVING conditional expressions] [ORDER BY order_by_column_list]

  6. SQL-Structured Query Languageinserting, updating, and deleting data Once tables have been created, it is necessery to populate them with data and maintain those data. INSERT : is used to populate tables. exp.1) In order to enter data to each column of the table INSERT INTO CUSTOMER VALUES (001, ‘Cavit’, ‘Tüketir’, ’13.Sok. Cuma Apt. 1/A’, ‘Bahçelievler’, ‘Ankara’); exp.2) Data will not be entered into every column in the table; INSERT INTO CUSTOMER (CUST_ID, CUST_NAME, CUST_LNAME) VALUES (001, ‘Cavit’, ‘Tüketir’); exp.3)Populating a table by using a subset of another table with the same structure. INSERT INTO IST_CUSTOMER SELECT * FROM CUSTOMER WHERE CITY = ‘İST’;

  7. SQL-Structured Query Languageinserting, updating, and deleting data DELETE : rows can be deleted individually or in groups. exp.1) Deleting with a certain criteria DELETE FROM CUSTOMER WHERE CITY = ‘İST’ exp.2) All rows; DELETE FROM CUSTOMER UPDATE: To modify unit price in the product table to 775 UPDATE PRODUCT SET UNIT PRICE = 775 WHERE PRODUCT_ID = 7;

  8. SQL-Structured Query Languageexpressions Using Functions: count, min, max, sum and avg Exp 1): Select count (*) from order_line where order_id = 1004; Exp 2): Select order_id, count (*) from order_line where order id = 1004; Count (*) counts all rows, even null! But “count” counts only the rows that contain a value. Exp 3): Select (product_name) from product; alphabetically it shows the first product name in product table. Using Wildcards: (*) in a Select statement selects all records according to a given condition (%) is used to represent any collection of characters. Using LIKE ‘%Desk’ when searching product name will find all different types of desks.

  9. SQL-Structured Query Languageexpressions Comparison Operators: =, >, >=, <, <= ,<> or != Exp 4: Select order_id, order_date from order where order_date > ’24-OCT-98’; Exp 5: Select product_name from product where product_name != ‘cherry’; Using Boolean Operators: AND joins two or more conditions and returns results only when all conditions are true. OR joins two or more conditions and returns results when any condition is true. NOT negates an expression. Ranges: The comparison operators < and > are used to establish a range of values. The keyword BETWEEN or NOT BETWEEN can also be used. .....where unit_price >199 AND unit_price <300;

  10. SQL-Structured Query Languageexpressions Distinct: Sometimes when returning rows that don’t include the primary key, dublicate rows will be returned. Exp 8): Select distinct order_id, quantity from order IN and NOT IN Lists: To match a list of values, consider using IN. Exp 9): Select customer_name, city, state from customer where state IN (‘CA’, ’TX’) Sorting Results:The ORDER BY Clause ORDER BY sorts the final results rows in ascending or descending order. GROUP BY groups rows in an intermediate results table where the values in those rows are the same for one or more columns. HAVING can only be used following a GROUP BY and acts is a secondary WHERE clause, returning only those groups which meet a specified condition.

  11. SQL-Structured Query Languageusing expressions EXAMPLES: 1) SELECT PRODUCT_NAME, PRODUCT_FINISH, PRODUCT_UNIT_PRICE FROM PRODUCT WHERE PRODUCT_NAME LIKE ‘%desk’ OR PRODUCT_NAME LIKE ‘%table’ AND PRODUCT_UNIT_PRICE > 300; 2) SELECT PRODUCT_NAME, PRODUCT_UNIT_PRICE FROM PRODUCT WHERE PRODUCT_UNIT_PRICE >199 AND PRODUCT_UNIT_PRICE<300; 3) SELECT ORDER_ID FROM ORDER_LINE; 4) SELECT DISTINCT ORDER_ID FROM ORFER_LINE; 5) SELECT CITY FROM CUSTOMER GROUP BY CITY HAVING AVG(UNIT_PRICE)<760 ORDER BY CITY;

  12. SQL-statement processing order FROM identifies involved tables WHERE Finds all rows meeting stated condition(s) GROUP BY Organizes rows according to values in stated column(s) HAVING Finds all groups meeting stated condition(s) SELECT Identifies columns ORDER BY Sorts rows Results • Mandatory clauses are only FROM and SELECT • Processing order is differnt from the order of the syntax used to create statement. Each clause is processed an intermediate results table is produced that will be used for next clasuse. • Users do not see the intermediate results tables; only see the final results.

More Related