Introduction to Structured Query Language (SQL)

1. Introduction to Structured Query Language (SQL)

2. RA Exercise • SALESPERSON(SSN, Name, Start_Year, Dept_No) • TRIP(SSN, From_City, Departure_Date, Return_Date, Trip_ID) • EXPENSE(Trip_ID, Account#, Amount) • Give the details (all attributes of TRIP relation) for trips that exceeded $2000 in expense • Print the SSN of salesman who took trips to Honolulu • Search the Name & Dept_No of salesman who took trips from ‘Jakarta’ and expensed no more than $1000

3. BarInfo( bar beer price addr ) Joe’s Bud 2.50 Maple St. Joe’s Milller 2.75 Maple St. Sue’s Bud 2.50 River Rd. Sue’s Coors 3.00 River Rd. Join Example Sells( bar beer price ) Bars( bar addr ) Joe’s Bud 2.50 Joe’s Maple St. Joe’s Miller 2.75 Sue’s River Rd. Sue’s Bud 2.50 Sue’s Coors 3.00 BarInfo := Sells JOIN Bars

4. Database

5. Why SQL? • SQL is a very-high-level language, in which the programmer is able to avoid specifying a lot of data-manipulation details that would be necessary in languages like C++. • What makes SQL viable is that its queries are “optimized” quite well, yielding efficient query executions.

6. SQL Components • DDL – Data definition language • Defining the relational schema – relations, attributes, domains (the meta-data) • Dropping/altering the relational schema • DML – Data manipulation language • Defining the queries against the schema • Inserting, deleting and modifying data • Others • Integrity (allowable values/referential) • Catalog and dictionary facilities • Transaction control (long-duration and batch) • Authorization (who can do what when)

7. Our Running Example • All our SQL queries will be based on the following database schema. • Underline indicates key attributes. SCHEMA BeerWorld Beers(name, manf) Bars(name, addr, license) Drinkers(name, addr, phone) Likes(drinker, beer) Sells(bar, beer, price) Frequents(drinker, bar)

8. Data Definition Language - DDL • A Pre-Defined set of Primitive Types • Numeric • Character-string • Bit-string • Additional Types • Defining Domains • Defining Schema • Defining Tables • Defining Views • Note: Each DBMS May have their Own DBMS Specific Data Types

9. Primitive Types • Numeric • INTEGER (or INT), SMALLINT • REAL, DOUBLE PRECISION • FLOAT(N) Floating Point with at Least N Digits • DECIMAL(P,D) (DEC(P,D) or NUMERIC(P,D)) have P Total Digits with D to Right of Decimal • Note that INTs and REALs are Machine Dependent (Based on Hardware/OS Platform)

10. Primitive Types • Character-String • CHAR(N) or CHARACTER(N) - Fixed • VARCHAR(N), CHAR VARYING(N), or CHARACTER VARYING(N) Variable with at Most N Characters • Bit-Strings • BIT(N) Fixed • VARBIT(N) or BIT VARYING(N) Variable with at Most N Bits

11. Primitive Types • These Specialized Primitive Types are Used to: • Simplify Modeling Process • Include “Popular” Types • Reduce Composite Attributes/Programming • DATE : YYYY-MM-DD • TIME: HH-MM-SS

12. What are Domains? • Domains are Similar in Concepts to Programming Language Type Definitions • A Domain can be Defined as Follows: CREATE DOMAIN DrinkerName CHAR(25) DEFAULT ‘Joe’; CREATE DOMAIN BeerName CHAR(20); • Advantage of Using Domains • Changing a Domain Definition in One Place Changes it Consistently Everywhere it is Used • Default Values Can Be Defined for Domains • Constraints Can Be Defined for Domains

13. Dropping a Domain • A Domain is Dropped As Follows: DROP DOMAIN DrinkerName RESTRICT; DROP DOMAIN BeerName CASCADE; • Restrict: • Drop Operation Fails If the Domain is Used in Column Definitions • Cascade: • Drop Operation Causes Columns to be Defined Directly on the Underlying Data Type

14. What is a SQL Schema? • A Schema in SQL is the Major Meta-Data Construct • Supports the Definition of: • Relation - Table with Name • Attributes - Columns and their Types • Identification - Primary Key • Constraints - Referential Integrity (FK) • Two Part Definition • CREATE Schema - Named Database or Conceptually Related Tables • CREATE Table - Individual Tables of the Schema

15. Create/Drop Schema • For schema BeerWorld SQL: CREATE SCHEMA BeerWorld Authorization Indra; • Drop schema BeerWorld SQL: DROP SCHEMA BeerWorld RESTRICT; DROP SCHEMA BeerWorld CASCADE; • Restrict: • Drop Operation Fails If Schema is Not Empty • Cascade: • Drop Operation Removes Everything in the Schema

16. Create Table • Create table Drinkers Drinkers(name, addr, phone) CREATE TABLE Drinkers( name VARCHAR(25), addr VARCHAR(40), phone CHAR(10), PRIMARY KEY(name) );

17. Create Table • Default value: gives the default value for a column/attribute Example: name VARCHAR(25) DEFAULT ‘Joe’ • Constraint: gives the constraint to the column/attribute. It can be used for: • Specifying the primary key/foreign key • Specifying column that can’t have the null value • Preserving the integrity of the database • Deleting the record • Modifying the value of primary key SET DEFAULT, SET NULL, CASCADE

18. Create Table Constraint name • Create table Drinkers(name, addr, phone) CREATE TABLE Drinkers( name VARCHAR(25) NOT NULL, …, CONSTRAINT pkDrinkers PRIMARY KEY(name) ); • Create table Sells(bar, beer, price) CREATE TABLE Sells( bar VARCHAR(25) NOT NULL, beer VARCHAR(25) NOT NULL, price DECIMAL(10,2) NOT NULL, CONSTRAINT pkBar FOREIGN KEY(bar) REFERENCES Bars(name) ON UPDATE CASCADE, CONSTRAINT pkBeer FOREIGN KEY(beer) REFERENCES Beers(name) ON UPDATE CASCADE );

19. Drop Tables • Command: DROP TABLE Drinkers RESTRICT; DROP TABLE Sells CASCADE; • Restrict: • Drop Operation fails if the Table is Referenced by View and/or Constraint Definitions • Cascade: • Drop Operation Removes Referencing View and Constraint Definitions

20. Alter Table • Change the table structures: • Adding or dropping column • Changing column definition • Adding or dropping table constraint • Adding column “job” in table “Drinkers”: ALTER TABLE Drinkers ADD job VARCHAR(20); • Dropping column “job” in table “Drinkers”: ALTER TABLE Drinkers DROP job CASCADE; ALTER TABLE Drinkers DROP job RESTRICT; • Restrict: Drop Operation Fails if Column is Referenced • Cascade: Drop Operation Removes Referencing View and Constraint Definitions

21. Basic Query In SQL

22. Basic Query in SQL • The principal form of a query is: SELECT desired attributes FROM one or more tables WHERE condition about tuples of the tables GROUP BY attribute list HAVING condition ORDER BY attribute list Basic Query

23. Example • Using Beers(name, manf), what beers are made by Anheuser-Busch? SELECT name FROM Beers WHERE manf = ‘Anheuser-Busch’;

24. Result of Query name ‘Bud’ ‘Bud Lite’ ‘Michelob’ The answer is a relation with a single attribute, name, and tuples with the name of each beer by Anheuser-Busch, such as Bud.

25. Meaning of Single-Relation Query • Begin with the relation in the FROM clause. • Apply the selection indicated by the WHERE clause. • Apply the extended projection indicated by the SELECT clause.

26. Operational Semantics • To implement this algorithm think of a tuple variable ranging over each tuple of the relation mentioned in FROM. • Check if the “current” tuple satisfies the WHERE clause. • If so, compute the attributes or expressions of the SELECT clause using the components of this tuple.

27. * In SELECT clauses • When there is one relation in the FROM clause, * in the SELECT clause stands for “all attributes of this relation.” • Example using Beers(name, manf): SELECT * FROM Beers WHERE manf = ‘Anheuser-Busch’;

28. Result of Query: name manf ‘Bud’ ‘Anheuser-Busch’ ‘Bud Lite’ ‘Anheuser-Busch’ ‘Michelob’ ‘Anheuser-Busch’ Now, the result has each of the attributes of Beers.

29. Renaming Attributes • If you want the result to have different attribute names, use “AS <new name>” to rename an attribute. • Example based on Beers(name, manf): SELECT name AS beer, manf FROM Beers WHERE manf = ‘Anheuser-Busch’

30. Result of Query: beer manf ‘Bud’ ‘Anheuser-Busch’ ‘Bud Lite’ ‘Anheuser-Busch’ ‘Michelob’ ‘Anheuser-Busch’

31. Expressions in SELECT Clauses • Any expression that makes sense can appear as an element of a SELECT clause. • Example: from Sells(bar, beer, price): SELECT bar, beer, price*8000 AS prcInRupiah FROM Sells;

32. Result of Query bar beer prcInRupiah Joe’s Bud 40000 Sue’s Miller 56000 … … …

33. Another Example: Constant Expressions • From Likes(drinker, beer): SELECT drinker, ‘likes Bud’ AS whoLikesBud FROM Likes WHERE beer = ‘Bud’;

34. Result of Query drinker whoLikesBud ‘Sally’ ‘likes Bud’ ‘Fred’ ‘likes Bud’ … …

35. Complex Conditions in WHERE Clause • From Sells(bar, beer, price), find the price Joe’s Bar charges for Bud: SELECT price FROM Sells WHERE bar = ‘Joe’’s Bar’ AND beer = ‘Bud’;

36. Important Points • Two single quotes inside a string represent the single-quote (apostrophe). • Conditions in the WHERE clause can use AND, OR, NOT, and parentheses in the usual way boolean conditions are built. • SQL is case-insensitive. In general, upper and lower case characters are the same, except inside quoted strings.

37. Patterns • WHERE clauses can have conditions in which a string is compared with a pattern, to see if it matches. • General form: <Attribute> LIKE <pattern> or <Attribute> NOT LIKE <pattern> • Pattern is a quoted string with % = “any string”; _ = “any character.”

38. Example • From Drinkers(name, addr, phone) find the drinkers with exchange 555: SELECT name FROM Drinkers WHERE phone LIKE ‘%555-_ _ _ _’;

39. NULL Values • Tuples in SQL relations can have NULL as a value for one or more components. • Meaning depends on context. Two common cases: • Missing value : e.g., we know Joe’s Bar has some address, but we don’t know what it is. • Inapplicable : e.g., the value of attribute spouse for an unmarried person.

40. Comparing NULL’s to Values • The logic of conditions in SQL is really 3-valued logic: TRUE, FALSE, UNKNOWN. • When any value is compared with NULL, the truth value is UNKNOWN. • But a query only produces a tuple in the answer if its truth value for the WHERE clause is TRUE (not FALSE or UNKNOWN).

41. UNKNOWN UNKNOWN UNKNOWN Surprising Example • From the following Sells relation: bar beer price Joe’s Bar Bud NULL SELECT bar FROM Sells WHERE price < 2.00 OR price >= 2.00;

42. Exercise Select name From Drinkers • List the name of all drinkers • List the name of beers started by ‘Bin’ Select name From Beers Where name Like ‘Bin%’ • Is the syntax of this query correct or not? Select 1 as tag, drinker From Likes Where beer <> ‘Anheuser-Busch’

43. Exercise • Find the name of bars selling ‘Anheuser-Busch’ with price less than $5 Select bar From Sells Where beer = ‘Anheuser-Busch’ and price < 5 • Find the name of bars started with ‘Zanz’ selling ‘Bir Bintang’ Select bar From Sells Where bar like ‘Zanz%’ and beer = ‘Bir Bintang’