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 l Data independence l Comprehensive data sublanguage l Support relational operations (select, project, join) l View updating rule l Systematic null value support l Integrity independence l

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

Occupation Table

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

Structured Query Language (SQL) SQL specifies syntax features for retrieval update and definition of the database. l l 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) FROM (table_list) 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