Structured Query Language

1. Structured Query Language and its components

2. SQL • SQL stands for Structured Query Language. • There is a standard SQL called the American National Standards Institute’s 2003 Standard SQL (ANSI:2003 SQL) • Most database vendors cover much of the standard, but do not adhere to it completely. • Note, when you start using Oracle SQL, you will see that many of the functions are different.

3. Database Vendors • While there is a standard, there are many providers of database systems. • Some of the providers you may find: • Oracle • Microsoft SQL Server • Microsoft Access (very limited) • MySQL (open source – popular for projects) • PostGres (open source)

4. Some definitions • Before explaining SQL, let’s look at some definitions: • Metadata: This is data about data, i.e. table definitions, column definitions, etc. • Data: This is the value that is held in the database, that must follow the rules of the metadata. • Transaction: A transaction is a unit of work, passed to the database for processing. • Session: A process that connects to the database, relating an individual user to a specific database (or schema) allowing the user to interact with the database, ending when the user disconnects from the database.

5. SQL – What’s in it? • SQL is made up of different categories of commands: • Data Definition Language • Data Manipulation Language • Transaction control statements • Session or data control statements

6. Data Definition Language (DDL) • This consists of commands that enable the database administrator, in association with the application developer, to manipulate the infrastructure of the database. • This infrastructure is known as the conceptual schema. It enables definition of the metadata. • The commands used to do this are: • CREATE • DROP • ALTER • TRUNCATE

7. Data Manipulation Language (DML) • This consists of commands that enable the application developer to manipulate the data in the tables. • The commands used are: • SELECT • INSERT • UPDATE • DELETE • MERGE

8. Transaction Control Statements • These statements allow the application developer to group DML statements, in order to conduct a transaction. • A transaction is a unit of work, passed to the database for processing. • A transaction will often require: • Selection from one or more tables or views • Insertion to one or more tables or views • Update to one or more tables or views • Deletion from one or more tables. • Examples: • Place an order for several items • Register as a student • Pay a phone bill.

9. Transaction Control Statements • Most of the statements used during a transaction are DML statements (some DDL statements may be used). • There are also transaction safeguard statements. These are: • Commit • Rollback

10. Data Control Language (DCL) • These consist of statements that allow the database (schema) owner to control either his / her own sessions, or sessions of other users trying to access his / her data. • The statements are: • GRANT • REVOKE • And various SET commands (Session control)

11. So far we have met… • SELECT • INSERT • CREATE • DROP • Let’s look back at them.

12. SELECT • This statement is very versatile and is the single retrieval mechanism. • Its basic components are • SELECT • Field-list • FROM • Table-list • We have tried these in the labs, and will continue to do so.

13. SELECT field-list • The field-list in a SELECT statement can be: • A wildcard character * to denote all eligible fields. • A column name that is unique to one of the tables in the table list. • A table-name.column-name to specify a column from a specific table from the table list. • A database.table-name.column-name to specify a column in a specific table in a specific database • Please note: Oracle calls the domain in which the tables reside a schema. SQL Server calls this domain a database. • A derived field

14. Derived fields in the SELECT field-list • Fields can be derived by: • Performing calculations on column-fields from the table-list. • E.g. unitprice * quantity as linecost • Using functions on the column-fields from the table list. • E.g. day(orderdate) • Using database provided functions. • E.g. SQL Server: getdate() gets the current date from the system, not from any table. • Oracle: Select sysdate from dual; (Dual is a working storage area for use in sessions connected to the database).

15. Table-list • Initially, the queries we do will be on single tables, but as we get more fluent with SQL, we will start to do multi-table selects. • The tables must belong to the same database / schema. • Sometimes the database/schema name is required to qualify the table name. • See later.

16. Additions to Select • If you think of a table (e.g. Dog) as a 2D grid, the column-list manipulates the columns: I’ve shortened the column names here to fit it in the slide.

17. Select name, age from dog • This query picks out specific columns from the table.

18. To pick out rows: • This is called projection and is done using the WHERE clause. • This clause goes after the basic select: • SELECT column-list FROM table-list WHERE condition • The condition usually relates to a value in one or more of the columns from the column list.

19. Conditions • The conditions can include: • >,<,<>,<=,>=, !=, NOT, between, IS NULL, IS LIKE. • The IS NULL returns a true if the value in the column is null, and a false otherwise. • You will NEVER get anything if you use the condition WHERE column = NULL • Null means undefined. You cannot equate to undefined!

20. LIKE • LIKE allows us to match patterns in strings. • Wildcard characters can be used to represent • A character from a string _ • A variable length substring from a string %.

21. Manipulating ROWS • The WHERE clause allows to choose from specific ROWS in our query: • SELECT dogname, dogage FROM dog WHERE dogage BETWEEN 2 and 4 • Look back at the exercises we did last week.

22. Formatting output • We looked at date formatting last week in the lab. What other formatting may we want? • String formatting • Concatenation: • SQL Server + • Oracle || • Renaming • AS clause SELECT corder.unitprice as Price or • SELECT corder.unitprice as ‘Unit Price’ • Numeric formatting (use cast, convert (SQL Server) and to_char(Oracle).

23. Ordering your data • To sort the output by a particular column, add the suffix ORDER BY column-name • E.g. SELECT dogname, dogage FROM dog ORDER BY dogage

24. Ordering your data • You can reverse the order: • SELECT dogname, dogage FROM dog ORDER BY dogage DESC

25. Ordering your data • You can also order it by two columns: • SELECT dogname, dogage FROM dog ORDER BY dogage, dogname DESC