SQL = Sequel = Structured Query Language The Standard Language for Relational Databases

SQL = Sequel = Structured Query Language The Standard Language for Relational Databases

SQL: DDL, DML, DCL

SQL: I) Go to SQL and Relational Algebra II) Go to Database Courseware -> SQL Query II.1) Construction II.2) Animating SQL Query with Procedural Code II.3) Advanced -> Animating Complex SQL with Join with Minus II.4) SQL Visual Basic -> Join with 1 Table

Unary Relationship in SQL(Join with 1 Table) EMP e EMP m Select e.ename, m.ename From emp e, emp m Where e.mgr = m.empno List name of employees and their managers

Select e.ename, m.ename From emp e, emp m Where e.mgr = m.empno List name of employees and their managers EMP e EMP m INPUT INPUT output

Outer Join • Take union of rows in two tables even when they are not union compatible. • For example (CAP database), we want the agent name listed together with their orders they placed. However, if the agent has no orders, we also want the agent listed.

Outer join in Oracle • Insert agent john in agent table. • List agents and their corresponding orders. ANAME DOLLARS ------------- ---------- Brown 1104 Smith 460 Smith 500 John

SQL syntax and processing SQL syntax: How SQL is processed: SELECT identify columns FROM identify tables WHERE rows meeting conditions GROUP BY organizes rows into groups ORDER BY sorts rows HAVING further subsets the groups

Insert • One insert command can: 1) Insert one row or 2) Insert millions of rows; . Also, may write the insert commands in a batch file.

Update • Update products set price = price * 1.05; • Update products set price = price * 1.10 where pid in (select pid from orders where aid in (select aid from agents where aname = ‘Smith’));

Integrity constraints (DDL) • Created with create table or alter table command • Removed with alter table or drop table command. • On delete ….

Creating Table with FK (DDL) • When creating a table Employee that has a FK did referencing Department, one must make sure the Department table has already been created first.

Removing Table with FK (DDL) • When removing the tables, the order must be the opposite order in which they were created. First remove table Employee that has a FK did referencing Department, then remove table Department.

Grant/Revoke (DCL) • Create user corporate_data; • Create user john mary and joe • Create tables accounts and transactions under the user corporate data • Grant select to everyone on accounts • Grant insert on transactions to mary • Grant select on transactions to joe, jone • Revoke joe’s right to select the accounts

SQL = Sequel = Structured Query Language The Standard Language for Relational Databases