Lecture4 - Lecture 4 on Structural Query Language To study...

Info iconThis preview shows pages 1–12. Sign up to view the full content.

View Full Document Right Arrow Icon
01/11/12 Lecture 4 on Structural Query Language To study Structural Query Language (SQL) as a non-procedural computer language to access relational database in data definitional language such as Create, Drop and Alter statements and in data manipulation language such as Insert, Update and Delete statements.
Background image of page 1

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full DocumentRight Arrow Icon
01/11/12 Structural Query Language (SQL) SQL is a transform oriented relational language. SQL can be run as a query/update language by itself or SQL command can be embedded in application programs. SQL include commands for data definition, data manipulation and data control. It is non-procedural and includes no reference to access paths, links or navigation such that the user only need to specify what he wants to retrieve/update rather than how to do it. It has a high level of independence from physical data storage.
Background image of page 2
01/11/12 SQL syntax SELECT [Distinct] select-list FROM from-list WHERE qualification GROUP BY grouping-list HAVING group-qualification ORDER attribute-list
Background image of page 3

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full DocumentRight Arrow Icon
01/11/12 Where The Where clause limits the rows that are returned from Query: For example: Select * from users where userid = 2
Background image of page 4
01/11/12 Order by This command can sort by any column type: alphabetical, chronological or numeric in either ascending or descending order by placing asc or desc: For example: Select * from users order by lname, fname
Background image of page 5

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full DocumentRight Arrow Icon
01/11/12 Having The having predicate restricts the rows displayed by a group by (whereas the where clause restricts the rows that are used in the group by). For example: Select avg(contribution) as avg_contrib, state from contributions group by state having avg(contribution) > 500
Background image of page 6
01/11/12 Sample database for SQL SID NAME MAJOR GRADE _LEVEL AGE 100 JONES HIST GR 21 150 PARKS ACCT SO 19 200 BAKER MATH GR 50 250 GLASS HIST SN 50 300 BAKER ACCT SN 41 350 RUSELL MATH JR 20 400 RYE ACCT FR 18 450 JONES HIST SN 24 Student Relation
Background image of page 7

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full DocumentRight Arrow Icon
01/11/12 STUDENT_ NUMBER CLASS_NA ME POSITION_ NUMBER 100 BD445 1 150 BA200 1 200 BD445 2 200 CS250 1 300 CS150 1 400 BA200 2 400 BF410 1 400 CS250 2 450 BA200 3 Class_ NAME TIME ROOM BA200 MF9 SC110 BD445 MWF3 SC213 BF410 MWF8 SC213 CS150 MWF3 EA304 CS250 MWF1 2 EB210 Class Relation Enrollment Relation
Background image of page 8
01/11/12 SQL Projections The projection Student[Sid, Name, Major] is created by Select Sid, Name, Major from Student 100 Jones Hist 150 Parks Acct 200 Baker Math 250 Glass Hist 300 Baker Acct 350 Russell Math 400 Rye Acct 450 Jones Hist
Background image of page 9

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full DocumentRight Arrow Icon
01/11/12 Select statement with Unique: Select Unique Major from Student => Hist Acct Math Select statement with where clause: Select * from student where Major = ‘Math’ 200 Baker Math Gr 50 350 Russell Math Jr 20 Note: The * in the SQL statement means that all attributes of the relation are to be obtained.
Background image of page 10
01/11/12 Select statement with several conditions: Select Name, Age from Student where Major = ‘Math’ and Age > 21 Baker 50 Select statement with an In clause: Select Name from Student where Major in [‘Math’, ‘Acct’] => Parks
Background image of page 11

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full DocumentRight Arrow Icon
Image of page 12
This is the end of the preview. Sign up to access the rest of the document.

Page1 / 42

Lecture4 - Lecture 4 on Structural Query Language To study...

This preview shows document pages 1 - 12. Sign up to view the full document.

View Full Document Right Arrow Icon
Ask a homework question - tutors are online