Sql i
This presentation is the property of its rightful owner.
Sponsored Links
1 / 16

SQL -I PowerPoint PPT Presentation


  • 87 Views
  • Uploaded on
  • Presentation posted in: General

SQL -I. Reading: C&B, Chap 5. In this lecture you will learn. The basic concepts and principles of SQL How to use SQL to perform basic database queries The different components of SQL How SQL can be described by a meta-language (Backus Naur Form) The principles of Query By Example (QBE).

Download Presentation

SQL -I

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.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -

Presentation Transcript


Sql i

SQL -I

Reading: C&B, Chap 5


In this lecture you will learn

In this lecture you will learn

  • The basic concepts and principles of SQL

  • How to use SQL to perform basic database queries

  • The different components of SQL

  • How SQL can be described by a meta-language (Backus Naur Form)

  • The principles of Query By Example (QBE)

Dept. of Computing Science, University of Aberdeen


Relational tables can answer many queries

Relational Tables Can AnswerMany Queries

Enrolment

Course

Student

  • How many courses are there & what are their names?

  • Which students are enrolled for Java?

  • How many students take 3 or more courses?

Dept. of Computing Science, University of Aberdeen


Sql structured query language

SQL - Structured Query Language

  • SQL was developed at IBM around 1975...

  • Structured programming?

    No! - Structured English Query Language (from ‘SEQUEL’) which implements relational algebra

  • SQL is a declarative language - says what not how

  • SQL is an abstract & portable interface to RDBMS

  • Warning: different vendors have dialects (implementations)& extensions (additional features on top of standard SQL, bundled in packages) but same core features

  • This course: ANSI SQL, RDBMS: MSAccess & MySQL (American National Standards Institute)

  • only standard db language to gain wide acceptance

Dept. of Computing Science, University of Aberdeen


Sql syntax

SQL Syntax

  • SQL uses reserved keywords & user-defined names

    CREATE TABLE Staff ( StaffNo INTEGER, Salary FLOAT,

    Lname VARCHAR(20) );

    INSERT INTO Staff VALUES (32, 25000.0, 'Smith');

  • By convention, keywords are upper-case, though most SQL dialects are case-insensitive; user-defined names must be entered exactly as in tables

  • Text data is enclosed using single quotes (‘ ' ‘)

  • Round brackets (‘(‘) are used to group related items

  • Commas (‘,’) separate items in a list

  • Statements are terminated with a semicolon (‘;’)

  • | indicates choice; literals: non-numeric require single quotes, e.g., VALUES(‘Bob’,4); [] indicates optionality

Dept. of Computing Science, University of Aberdeen


Simple queries using select

Simple Queries Using SELECT

  • The SELECT statement retrieves data&formats output

  • SELECT is the most frequently used SQL statement

  • Performs relational algebra’s selection, projection and join operations in a single statement

  • SELECT * FROM Staff;

  • Here, asterisk (‘*’) acts as a ‘wild card’ - all columns

  • By default, SELECT outputs all the rows in the table

  • Use “SELECT DISTINCT target_list FROM Staff;” for avoiding duplicates

  • Outputs a table (so SELECT is a closed operation)

Dept. of Computing Science, University of Aberdeen


Select

SELECT

SELECT target-list

FROM relation-list

WHERE qualification;

GROUP BY columnList [HAVING condition]

ORDER BY columnList

  • relation-list- A list of relation (table) names.

  • target-list -A list of attributes of relations in relation-list; columnName [AS newName]; can also specify distinct (no duplicates) or all (*)

  • qualification – filters rows subject to some condition, e.g., comparisons (Attr op const or Attr1 op Attr2, where op is one of<,>,=,≠,<=,>=) combined using AND, OR and NOT.

  • GROUP BY filters groups of rows, HAVING some condition

Dept. of Computing Science, University of Aberdeen


Selecting specific columns

Selecting Specific Columns

  • Specific columns can be output by giving their names:

  • SELECT Lname, Position, Salary FROM Staff;

  • NB. must have a comma (‘,’) between column names

  • Can consider the output from SELECT as a new table

Dept. of Computing Science, University of Aberdeen


Selecting specific rows columns

Selecting Specific Rows & Columns

  • Specific rows can be selected with a WHERE clause:

    • SELECT Lname, Position, Salary

    • FROM Staff

    • WHERE Salary > 20000;

  • comparison operators: <; =; <=; >=; ! =; <>, can also use AND, OR and NOT, e.g., WHERE city=‘London’ OR city=‘Glasgow’; complex preds evaluated in set order (see p.123); () nest

  • can also test for range (BETWEEN, NOT BETWEEN), set membership (IN (‘Manager’), NOT IN), pattern match(LIKE ‘h%’, NOT LIKE ‘H_e’’) and whether null (IS NULL, IS NOT NULL)

  • The condition ‘Salary > 20000’ is called a predicate

  • For each row, if predicate is true, row is output

  • Salary/12 is a calculated field a.k.a. computed or derived field, but needs naming (AS monthlySalary)

Dept. of Computing Science, University of Aberdeen


Building up complex predicates

Building Up Complex Predicates

  • Predicates evaluate to either true or false

  • Predicates can be combined using AND, OR, and NOT

  • Use brackets to avoid ambiguity

  • The next two statements are different:

    • SELECT * FROM Staff WHERE

      (Position = 'Manager') OR

      (Position = 'Assistant' AND Salary > 10000);

      2. SELECT * FROM Staff WHERE

      (Position = 'Manager' OR Position = 'Assistant')

      AND NOT (Salary <= 10000);

  • In each case, whole WHERE clause is true or false

  • ORDER BY sorts results and can be ASC or DESC, e.g., ORDER BY salary DESC; ORDER BY 4 DESC

  • NULL is not equivalent to ‘’ or <>, can’t test equality; use IS

  • Dept. of Computing Science, University of Aberdeen


    Other types of predicate

    Other Types of Predicate

    • Other predicates include BETWEEN, IN, and LIKE

    • But they still evaluate to either true or false

      SELECT * FROM Staff

      WHERE

      (Salary BETWEEN 10000 AND 20000) AND

      (Position IN ('Manager', 'Assistant')) AND

      (Lname LIKE 'S%' OR Lname LIKE 'W____');

    • '%' matches zero or more characters

    • '_' matches exactly one character

    • NB. Some DMBSs use ‘*’ and ‘?’ for wildcards

    Dept. of Computing Science, University of Aberdeen


    Sql terminology

    SQL Terminology

    • SQL does not use formal relational terminology

      Formal (Relational Algebra)Informal (SQL)

      Relation Table

      Tuple Row

      Attribute Column

      Cardinality No. of rows

      Degree No. of columns

      Relationships Foreign keys

      Constraints Assertions

    Dept. of Computing Science, University of Aberdeen


    Sql components ddl dcl dml

    SQL Components: DDL, DCL, & DML

    • SQL is a transform-oriented language

    • SQL is a non-procedural language: specify what info you require rather than how you access the data

    • SQL is a very large and powerful language, but every type of SQL statement falls within one of three main categories (or sub-languages):

    • Data Definition Language (DDL) for creating a DB, defining & controlling access to data

      e.g. CREATE, DROP, ALTER

    • Data Control Language (DCL) for administering a DB

      e.g. GRANT, DENY, USE

    • Data Manipulation Language (DML) to access a DB by retrieving and updating data

      e.g. SELECT, INSERT, UPDATE, DELETE

    Dept. of Computing Science, University of Aberdeen


    Describing sql syntax using bnf notation

    Describing SQL SyntaxUsing BNF Notation

    • CB use a special ‘BNF’ notation to describe SQL syntax:

    • BNF (Backus-Naur form) is a meta language for context-free grammars...

    • meta language: a language that describes a language

      SELECT [ DISTINCT | ALL ]

      { * | [ Colexpr [ AS Newcol ] ] [, ...] }

      FROM TableName [ Alias ] [, ...]

      [ WHERE Predicate ] [ GROUP BY Columnlist ]

      [ HAVING Predicate ] [ ORDER BY Columnlist ] [;]

    • [ ] optional;

    • { } required;

    • | alternative;

    • ... zero or more

    Dept. of Computing Science, University of Aberdeen


    Query by example qbe

    Query By Example (QBE)

    Modern DBMSs often provide simple form-based methods of specifying queries (QBE). For example, MS-Access:

    • Generates the following SQL:

      • SELECT * FROM Staff

      • WHERE (Lname LIKE 'W%' AND Position = 'Manager')

      • OR (Salary > 15000);

    Dept. of Computing Science, University of Aberdeen


    Conclusion

    Conclusion

    • SQL is the standard query language for RDBMS

    • Three main categories of SQL

      • DDL, Data Definition Language

      • DCL, Data Control Language

      • DML, Data Manipulation Language

    • SELECT belongs to DML

    • SELECT retrieves & displays data from the database

    • We continue to explore DML

    Dept. of Computing Science, University of Aberdeen


  • Login