صفحه 1:
Structured Query Language
(SQL)
OG 990 )۰ Gystews Por Cayjoeeriog ood Ooonpewect
®rpad Worvats
Oppartved oP Oi cod Caviroavectd Bagicerriccy
Oniversip vP OulPoraa, Berkeley
Geptewber OO, COOP
صفحه 2:
Sources for this
Presentation
e @roPessvr Duciel Rekok oad Rebecca @uckkei,
Orpurtvedt oP Cid od Govirvevedtdl 0) كج وجوه
جلج-ه0) Oetoa Ouversity, Coppriht, Ol rights reserved
© Rawokrishean, R, ood U. Gehrke, “Outsbuse
Oucagqewet (Spstexs.” ex ed., Ov@rav-Wil, SOOO.
صفحه 3:
Internal Structure of DBMS
© OO exces
© Bie worse weokwuisws
© Pie stonne
صفحه 4:
External Views of DBMS
® Oats wouipulativd looquage provesspr (e-4., G@L
© Qeew interPuce
® سس مر
© Report اا صوصل
صفحه 5:
SQL
© Ba BVGWIGO sterrcard hacer
« 2084 را60 09, 9, 8
» Oost sustews iwplewwedt port or extrusions
مها ری و Used ty اوه wit مهو
> splot (chopee data Brow a toble)
» crete tables
» fosert dota
> pdhite (chacye) date
» delete tables ood data
فا eiva (cowbiae tubles)
» oer
© Queres dows wooipulote poe tuble ty produce a we table
صفحه 6:
MS Access
t7 DG det ابا User 0 رن و
DG Uetis recy the DOOS ©
© wow people صل even keow these ore OGL
تا تن لت سونو
» مما كم
رنه من له اوه رین و
حلت مما
Rieke oe a
» setert data
© sil weed GOL Por advouved queries
> proent
ممم دغ
صفحه 7:
SELECT - Basic statement for data
query
GELECT GELECT
[O1IEMOCT | CLL] predicate
[* | حلاص | <expressiva>] + عم
PROD PROD
[<table>] + اور
[<سصهاه مک
[<where chnse>] اجه وه وان
]> وب by cknuse>] qouped by atributes:
[<heviey اه و ما [<عصماء
sorted ia soe order [<عصهاه برط عطحو>]
صفحه 8:
Query Categories
e Dileyonies خام ۷۲۲ (۳/۳۶
واه مورک و ی 00/۲۳۵۸۷۱۵۰ «
« 609) 0/
PROBOGPORDOMOOD: uses aa GAL
رل و موی( data
» DBCREGOVE: ues va GGL >سقمع یمه
Puurtion (SOO, OOX, et.)
» cop vowbicaicd oP these three
صفحه 9:
SELECT: [DISTINCT | ALL]
© wiresuired, dePouls to LL
© Queries sowetves resulta duplicate mws
» priery keys dePice uoiquedrss oP rack row
> Pf privary keps ore ot tockided, duplicates result
© O16MOCT
» retuce ody poe vopy oF euck duplicate
© Ow
«( مه اه سور oP duplicates
صفحه 10:
SELECT: [* | <nela> |
<expression>]
© چم نت ot least poe oP the choices
عم ان موی و
ی
- ۰
۳۵۵۵ سم
© <لاويع>
راما مت سل تمه رات و(«
عم تن صمي مله الس ل
© > <ممووص وج
» apples « Puawtiva (وقميصري لدمتدووسن ”اعدو الوصدل_جادص) لاإصذا د صا
= 00۱ ۵00۵) [orece]) DG [Pord_Crecte]
PROO cose}
= GELEOT COOWT ([تساسم] [سسس]) OG [ruxrber_oP_vowses]
۳00 تسه
صفحه 11:
SELECT: <table>
© bist DP tables thot pou Loot ookicves Pow
© separated by vine
© se brackets, but doa't have to
» [bourse], [prudent]
صفحه 12:
SQL Aggregation Functions
® SOO
«( the suey oP the vdlues foo ooh
GELECT GOO ([owse].[aredie]) BG [Nord_Crecs]
00 مه
© 01“
> the word volue to poh
GELECT OX ([rawve] [oredis]) OG [evuxirany_oredi]
00 مه
صفحه 13:
SQL Aggregation Functions
» 6
>» the were of ماه و
> valrlate the overage price of books:
GELECT POC ((bork]..[vPrive])
ROO [book];
٠» 0000001“
« جمسامم د ذا وصنامن خام عصحادصي جوز خأ امتحص م
ELECT COV (frrurve].[suswber]) 3085 [سصعحم_خات_وحاصى]
۳00 تسه
©» 010
«( موی علا volve too ooh
SELECT 0100 ([#مسخاس]. [«اسسط])
ROO [bork]
صفحه 14:
SELECT: <where clause>
© species whick مسحو وا جر
® based oa sowe اه
e ons 5771255175
© 11,2050: <مموشد>
GELEOT O10 (bork) [urstPrive])
ROO [book]
OUORE [book]. frakPrive] > CO WOO [bork]. [pubtok_ctte] > (899;
© subqueries
» OWERE <coredtiica> 1D <selert query>
» OWERE <coredica> DOT 1D <setert query>
— OLERE [beck]. [1600] = 09-POSSPO-K 10 (GELEOT * PROD [berk])
صفحه 15:
SELECT: <group by>
© ای مسر
© pws Wik حمه ان سا “orouped toyether”
® GROOP OY [<expressivu> | >
00
GELECT [bovk]. [authors]
ROO [bork]
GROOEP CY [bovk].[sukors];
صفحه 16:
SELECT: <order by
clause>
© Sort resis va the voles oP par pr wore
ار
® ORDER ۷۲ > verve> [BEC |
OCC]
» OGOC is dePout
—GELECT *
ROO [book]
ORDER BY [bovk].[suikors] DEGO
Structured Query Language
(SQL)
CE 169B Database Systems for Engineering and Management
Arpad Horvath
Department of Civil and Environmental Engineering
University of California, Berkeley
September 30, 2004
Sources for this
Presentation
Professor Daniel Rehak and Rebecca Buchheit,
Department of Civil and Environmental Engineering,
Carnegie Mellon University, Copyright, All rights reserved
Ramakrishnan, R, and J. Gehrke, “Database
Management Systems.” 2nd ed., McGraw-Hill, 2000.
Internal Structure of DBMS
DB engines
File access mechanisms
File storage
External Views of DBMS
Data manipulation language processor (e.g., SQL
interpreter or compiler)
Query interface
Form generator
Report generator
SQL
An ANSI/ISO standard language
» ANSI SQL ’86, ’89, ’92
» Most systems implement part or extensions
query language used to interact with database
»
»
»
»
»
»
»
select (choose data from a table)
create tables
insert data
update (change) data
delete tables and data
union (combine tables)
other
queries always manipulate one table to produce a new table
MS Access
provides a user interface to MS Jet
MS Jet is really the DBMS
many people don’t even know these are SQL
» create and drop tables
» insert data
query interface and wizards simplify
» join tables
» update and delete data
» select data
still need SQL for advanced queries
» project
» union
SELECT – Basic statement for data
query
SELECT
[DISTINCT | ALL]
[* | <field> | <expression>] +
FROM
[<table>] +
[<join clause>]
[<where clause>]
[<group by clause>]
[<having clause>]
[<order by clause>]
SELECT
predicate
columns
FROM
tables
where conditions are true
grouped by attributes
having group criteria
sorted in some order
Query Categories
categories of SELECT queries
» CRITERIA: uses a <where clause>
» CALCULATION/
TRANSFORMATION: uses an SQL
function to modify data
» AGGREGATE: uses an SQL aggregation
function (SUM, MAX, etc.)
» any combination of these three
SELECT: [DISTINCT | ALL]
not required, defaults to ALL
queries sometimes result in duplicate rows
» primary keys define uniqueness of each row
» if all primary keys are not included, duplicates result
DISTINCT
» return only one copy of each duplicate
ALL
» return all instances of duplicates
SELECT: [* | <field> |
<expression>]
must have at least one of the choices
separate with commas
* returns all columns in all tables in table list
– SELECT *
FROM student
<field>
» returns only specified fields from tables
» syntax: [table name].[field name]
<expression>
» applies a function to a field (calculation/transformation/aggregation)
– SELECT SUM([course].[credits]) AS [Total_Credits]
FROM course;
– SELECT COUNT([course].[number]) AS [number_of_courses]
FROM course;
SELECT: <table>
list of tables that you want columns from
separated by commas
use brackets, but don’t have to
» [course],[student]
SQL Aggregation Functions
SUM
» the sum of the values in a column
SELECT SUM([course].[credits]) AS [Total_Credits]
FROM course;
MAX
» the maximum value in a column
SELECT MAX([course].[credits]) AS [maximum_credit]
FROM course;
SQL Aggregation Functions
AVG
» the average of a column
» calculate the average price of books:
SELECT AVG([book].[unitPrice])
FROM [book];
COUNT
» a count of the number of values in a column
SELECT COUNT([course].[number]) AS [number_of_courses]
FROM course;
MIN
» the minimum value in a column
SELECT MIN([book].[unitPrice])
FROM [book]
SELECT: <where clause>
specifies which rows to return
based on some criteria
can use expressions
WHERE <criteria>
SELECT MIN([book].[unitPrice])
FROM [book]
WHERE [book].[unitPrice] < 18 AND [book].[publish_date] > 1999;
subqueries
» WHERE <condition> IN <select query>
» WHERE <condition> NOT IN <select query>
– WHERE [book].[ISBN] = 13-785543-X IN (SELECT * FROM [book])
SELECT: <group by>
aggregates columns
rows with like values are “grouped together”
GROUP BY [<expression> | <column
name>]
SELECT [book].[authors]
FROM [book]
GROUP BY [book].[authors];
SELECT: <order by
clause>
sort results on the values of one or more
columns
ORDER BY <column name> [ASC |
DESC]
» ASC is default
– SELECT *
FROM [book]
ORDER BY [book].[authors] DESC
