SQL -I

1. SQL -I Reading: C&B, Chap 5

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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