SQLSy Base Programming A Database is A collection

SQL/Sy. Base Programming

A Database is. . . A collection of related tables containing data and definitions of database objects. The “table” is a paradigm which describes the data stored within the database.

The Relational Model • Codd’s 12 Rules – Information rule - everything is a table – Data independence – Comprehensive data sublanguage – Support relational operations (select, project, join) – View updating rule – Systematic null value support – Integrity independence

Tables • Tables contain: – Rows = Records – Columns = Fields – Primary Key = Unique Identifier – Data Elements = Value

Occupation Table

Two Kinds of Tables • User Tables – contain user loaded data – primary query tables – created, modified, maintained by user • System Tables – contain database and table descriptions – maintained by database system – can be queried like any other table

Structured Query Language (SQL) • SQL specifies syntax features for retrieval update and definition of the database. – SELECT - query data in the database – INSERT - Add a single row – UPDATE - Alter attribute values in rows – DELETE - Delete rows of data from a table

Select Statement Its syntax is : SELECT (select-list - attributes or derived data) FROM (table name or names) WHERE (sets up conditions) SELECT and FROM are required SELECT Name FROM OCCUPATIONS WHERE ID = 101;

Select Examples PARTNO P 1 P 2 P 3 PNAME NUT BOLT CARAVAN PRICE $0. 20 $1. 00 $5000. 00 QOH 20 40 3 SELECT * FROM PART; Selects all column values for all rows SELECT PARTNO, PNAME FROM PART WHERE PRICE >= 0. 2; Selects rows where price >=. 2 SELECT PARTNO, PNAME FROM PART WHERE PNAME LIKE (’CAR%'); Selects rows where pname has a value starting with CAR

Insert Statement This command allows data to be inserted, one row at a time, into an existing table Syntax: Insert into <tablename> (list of attributes) values (list of values) Note: The list of attributes can be ignored providing the order of attribute values, and completeness of attribute instances, is as per the table list. example: insert into emp(name, sal, byear) values(‘Jones, Bill’, 45000, 1967); or(see note) insert into emp values(‘Jones, Bill’, 45000, 1967);

Update Statement UPDATE tablename SET colname = expression { , colname = expression} [ WHERE search_condition ] • Replaces values of the specified columns with expression values for all rows satisfying the search-condition. • Expressions in the set clause may be constants or column values from the UPDATE tablename or FROM tablename Example: UPDATE PART SET price = price * 1. 1 WHERE price < 20;

Delete Statement DELETE FROM tablename [ WHERE search-condition ] Delete one or many rows in a table. In general search-condition and qualification may not involve a sub select on tablename. DELETE FROM PART WHERE qoh < 4. 00;

Joining Tables EMPNO E 1 E 2 E 3 DEP ENAME RED BLUE BROWN MGRNO E 1 E 1 DEPTNO D 1 D 2 D 3 DNAME TAX PAY LEAVE SELECT e. empno AS Number, e. ename AS Name, d. dname AS Department FROM emp e, dep d WHERE e. deptno = d. deptno; Number E 1 E 2 E 3 Name RED BLUE BROWN Department TAX PAY

A Search and Join Condition For each prime minister born in or after 1900, give his name, birth year and party. SELECT FROM WHERE P. PM_NAME, BIRTH_YR, PARTY PRIME_MINISTER P, MINISTRY M P. PM_NAME = M. PM_NAME AND BIRTH_YR >= 1900; PM_NAME Holt H E Mc. Ewen J Gorton J G BIRTH_YR 1908 1900 1911 PARTY Liberal Country Liberal