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

Info icon This preview shows pages 1–13. 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.
Image of page 1

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

View Full Document Right 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.
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
Image of page 3

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

View Full Document Right 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
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
Image of page 5

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

View Full Document Right 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
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
Image of page 7

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

View Full Document Right 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
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
Image of page 9

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

View Full Document Right 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.
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 Baker Baker Russell Rye Select statement with an Not In clause: Select Name from Student where Major not in [‘Math’, ‘Acct’] => Jones Glass Jones
Image of page 11

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

View Full Document Right Arrow Icon
01/11/12 Select statement with LIKE conditions
Image of page 12
Image of page 13
This is the end of the preview. Sign up to access the rest of the document.

{[ snackBarMessage ]}

What students are saying

  • Left Quote Icon

    As a current student on this bumpy collegiate pathway, I stumbled upon Course Hero, where I can find study resources for nearly all my courses, get online help from tutors 24/7, and even share my old projects, papers, and lecture notes with other students.

    Student Picture

    Kiran Temple University Fox School of Business ‘17, Course Hero Intern

  • Left Quote Icon

    I cannot even describe how much Course Hero helped me this summer. It’s truly become something I can always rely on and help me. In the end, I was not only able to survive summer classes, but I was able to thrive thanks to Course Hero.

    Student Picture

    Dana University of Pennsylvania ‘17, Course Hero Intern

  • Left Quote Icon

    The ability to access any university’s resources through Course Hero proved invaluable in my case. I was behind on Tulane coursework and actually used UCLA’s materials to help me move forward and get everything together on time.

    Student Picture

    Jill Tulane University ‘16, Course Hero Intern