sql i
Download
Skip this Video
Download Presentation
SQL -I

Loading in 2 Seconds...

play fullscreen
1 / 16

SQL -I - PowerPoint PPT Presentation


  • 120 Views
  • Uploaded on

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).

loader
I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
capcha
Download Presentation

PowerPoint Slideshow about ' SQL -I' - cruz-raymond


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

ad