Transcript Document

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.
1
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]
2
SQL-Structured Query Language
inserting, 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’;
3
SQL-Structured Query Language
inserting, 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;
4
SQL-Structured Query Language
expressions
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.
5
SQL-Structured Query Language
expressions
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;
6
SQL-Structured Query Language
expressions
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.
7
SQL-Structured Query Language
using 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 ORDER_LINE;
5) SELECT CITY FROM CUSTOMER
GROUP BY CITY
HAVING AVG(UNIT_PRICE)<760
ORDER BY CITY;
8
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 different 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.
9