1 information retrieval and use (iru) an introduction to sql part 2
TRANSCRIPT
![Page 1: 1 Information Retrieval and Use (IRU) An Introduction To SQL Part 2](https://reader035.vdocuments.us/reader035/viewer/2022062309/5697bff01a28abf838cba961/html5/thumbnails/1.jpg)
1
Information Retrieval and Use (IRU)
An Introduction To SQL
Part 2
![Page 2: 1 Information Retrieval and Use (IRU) An Introduction To SQL Part 2](https://reader035.vdocuments.us/reader035/viewer/2022062309/5697bff01a28abf838cba961/html5/thumbnails/2.jpg)
2
SQL Data Retrieval
Recap: The SQL Data Manipulation Language
(DML)has four commands: SELECT: retrieving data INSERT: creating data UPDATE: altering data DELETE: removing data
![Page 3: 1 Information Retrieval and Use (IRU) An Introduction To SQL Part 2](https://reader035.vdocuments.us/reader035/viewer/2022062309/5697bff01a28abf838cba961/html5/thumbnails/3.jpg)
3
Select Statement: displaying data
You can display data in a different format: Show all the department names found in the dept table
but rename the column with the heading diff_dept:
– SQL> select dname as diff_dept from dept;
DIFF_DEPT--------------ACCOUNTINGRESEARCHSALESOPERATIONS
![Page 4: 1 Information Retrieval and Use (IRU) An Introduction To SQL Part 2](https://reader035.vdocuments.us/reader035/viewer/2022062309/5697bff01a28abf838cba961/html5/thumbnails/4.jpg)
4
The Order By Clause: Key principles
Oracle will display your rows of data in an unordered fashion.
We use the ORDER BY clause to order the rows that are retrieved.
The ORDER BY clause should always be placed last in the query.
The default ordering is ascending Numeric ascending by order value Dates chronological order Char alphabetically DESC is used to reverse the order
![Page 5: 1 Information Retrieval and Use (IRU) An Introduction To SQL Part 2](https://reader035.vdocuments.us/reader035/viewer/2022062309/5697bff01a28abf838cba961/html5/thumbnails/5.jpg)
5
The Order By Clause: Example 1
List all departments in order of their location:
SELECT * FROM dept
ORDER BY loc;
DEPTNO DNAME LOC
--------- -------------- -------------
40 OPERATIONS BOSTON
30 SALES CHICAGO
20 RESEARCH DALLAS
10 ACCOUNTING NEW YORK
![Page 6: 1 Information Retrieval and Use (IRU) An Introduction To SQL Part 2](https://reader035.vdocuments.us/reader035/viewer/2022062309/5697bff01a28abf838cba961/html5/thumbnails/6.jpg)
6
The order by Clause: example 2
Show details of employees in department 10 with the earliest hire dates first:
SELECT empno, ename, hiredate FROM emp WHERE deptno = 10 ORDER BY hiredate;
EMPNO ENAME HIREDATE----- ---------- --------- 7782 CLARK 09-JUN-81 7839 KING 17-NOV-81 7934 MILLER 23-JAN-82
![Page 7: 1 Information Retrieval and Use (IRU) An Introduction To SQL Part 2](https://reader035.vdocuments.us/reader035/viewer/2022062309/5697bff01a28abf838cba961/html5/thumbnails/7.jpg)
7
The order by Clause: example 3
Show all employees in job order but within each job place in descending order of salary:
SELECT ename, job, sal, deptno
FROM emp
ORDER BY job, sal DESC;
14 rows will be selected.
![Page 8: 1 Information Retrieval and Use (IRU) An Introduction To SQL Part 2](https://reader035.vdocuments.us/reader035/viewer/2022062309/5697bff01a28abf838cba961/html5/thumbnails/8.jpg)
8
The order by Clause: example 4
SELECT ename,job,sal, comm, comm/sal as ratio,deptnoFrom emp Where deptno = 30Order by ratio;
ENAME JOB SAL COMM RATIO DEPTNO---------- --------- --------- --------- --------- ---------TURNER SALESMAN 1500 0 0 30ALLEN SALESMAN 1600 300 .1875 30WARD SALESMAN 1250 500 .4 30MARTIN SALESMAN 1250 1400 1.12 30BLAKE MANAGER 2850 30JAMES CLERK 950 30(NB: Nulls sort high)
![Page 9: 1 Information Retrieval and Use (IRU) An Introduction To SQL Part 2](https://reader035.vdocuments.us/reader035/viewer/2022062309/5697bff01a28abf838cba961/html5/thumbnails/9.jpg)
9
Group By Function
Results can be grouped according to a common attribute value. We can find the sum of the salaries by department.SELECT deptno, sum(sal) FROM emp
GROUP BY deptno; DEPTNO SUM(SAL)------- --------- 10 8750 20 10875 30 9400
![Page 10: 1 Information Retrieval and Use (IRU) An Introduction To SQL Part 2](https://reader035.vdocuments.us/reader035/viewer/2022062309/5697bff01a28abf838cba961/html5/thumbnails/10.jpg)
10
Processing Data: Aggregate Functions
Some simple functions include: Count: returns a count of rows Min: returns the lowest value for an attribute Max: returns the highest value for an attribute Sum: returns the sum of values Avg: returns the average value
SELECT COUNT (*) FROM EMP
COUNT(*)-------- 14
RETURNS THE NUMBER OF ROWS IN THE EMP TABLE.
![Page 11: 1 Information Retrieval and Use (IRU) An Introduction To SQL Part 2](https://reader035.vdocuments.us/reader035/viewer/2022062309/5697bff01a28abf838cba961/html5/thumbnails/11.jpg)
11
Processing Data: min/max
You can find the highest salary:Select max(sal) from emp;
MAX(SAL)
--------
5000
Select min (sal) as min_sal from emp;
MIN_SAL
-------
800
![Page 12: 1 Information Retrieval and Use (IRU) An Introduction To SQL Part 2](https://reader035.vdocuments.us/reader035/viewer/2022062309/5697bff01a28abf838cba961/html5/thumbnails/12.jpg)
12
Processing Data: sum/average
Sum is used on numeric functions to return the sum of all the values: SELECT sum(sal), sum (comm) from emp;
SUM(SAL) SUM(COMM)-------- --------- 29025 2200
SELECT avg(SAL) from emp; AVG(SAL)---------2073.2143
![Page 13: 1 Information Retrieval and Use (IRU) An Introduction To SQL Part 2](https://reader035.vdocuments.us/reader035/viewer/2022062309/5697bff01a28abf838cba961/html5/thumbnails/13.jpg)
13
Having Clause
We can constrain the subgroups that appear by using a HAVING CLAUSE.
We can find the sum of the salaries of each department, but only request those departments with more than 5 employees.SELECT deptno, sum(sal) FROM emp
GROUP BY deptno
HAVING count (*) >5;
DEPTNO SUM(SAL)
----- ---------
30 9400
![Page 14: 1 Information Retrieval and Use (IRU) An Introduction To SQL Part 2](https://reader035.vdocuments.us/reader035/viewer/2022062309/5697bff01a28abf838cba961/html5/thumbnails/14.jpg)
14
The self-join Useful to put more than one aggregate
function on the same row of output. E.g. “Count the number of people in dept 10
who receive salary, and the number in dept 10 who receive commission”
SELECT COUNT(A.SAL) AS COUNTSAL, COUNT(B.COM) AS COUNTCOMMFROM EMP A, EMP BWHERE A.EMPNO=B.EMPNO AND
A.DEPTNO=10;
![Page 15: 1 Information Retrieval and Use (IRU) An Introduction To SQL Part 2](https://reader035.vdocuments.us/reader035/viewer/2022062309/5697bff01a28abf838cba961/html5/thumbnails/15.jpg)
15
SQL DATA DEFINITION : DDL
Create Table CREATE TABLE customer as follows:
CREATE TABLE CUSTOMER (CUSTOMER_ID NUMBER (5), NAME
VARCHAR2 (10), HOUSE_NUMBER NUMBER (2), STREET VARCHAR2 (15), TOWN VARCHAR2
(15));
Each column has a name, data type and column width.
![Page 16: 1 Information Retrieval and Use (IRU) An Introduction To SQL Part 2](https://reader035.vdocuments.us/reader035/viewer/2022062309/5697bff01a28abf838cba961/html5/thumbnails/16.jpg)
16
DDL: Creating Tables continued.
Here’s an example. What’s it doing?Create Table account(account_num number (6),Account_name varchar2 (20),Branch_name varchar2(20),Date_opened date,
Balance number (10,2));
The Balance column is a decimal number with a maximum Column width of ten digits and a precision of two digits i.e.10.251 is stored as 10.25.
![Page 17: 1 Information Retrieval and Use (IRU) An Introduction To SQL Part 2](https://reader035.vdocuments.us/reader035/viewer/2022062309/5697bff01a28abf838cba961/html5/thumbnails/17.jpg)
17
Simple Constraints: Primary keys
A correct definition of the table ‘customer’ might be:
CREATE TABLE CUSTOMER
(CUSTOMER_ID NUMBER (5), PRIMARY KEY NOT NULL,
NAME VARCHAR2 (10),
HOUSE_NUMBER NUMBER (2),
STREET VARCHAR2 (15),
TOWN VARCHAR2 (15));
![Page 18: 1 Information Retrieval and Use (IRU) An Introduction To SQL Part 2](https://reader035.vdocuments.us/reader035/viewer/2022062309/5697bff01a28abf838cba961/html5/thumbnails/18.jpg)
18
Simple Constraints: Primary keys
The PRIMARY KEY indicates that all values for these columns must be unique.
NOT NULL is used to designate that the column must have a value for all its rows.
Therefore in the ‘account’ table the first two lines should read:Create Table account
(account_num number (6) primary key not null,
![Page 19: 1 Information Retrieval and Use (IRU) An Introduction To SQL Part 2](https://reader035.vdocuments.us/reader035/viewer/2022062309/5697bff01a28abf838cba961/html5/thumbnails/19.jpg)
19
Simple Constraints:
Customer and account has a many to many relationship, we can resolve this with a table constraint by creating a composite primary key.
CREATE table customer_account
(CUSTOMER_ID NOT NULL,
ACCOUNT_NUM NOT NULL,
PRIMARY KEY (CUSTOMER_ID,ACCOUNT_NUM));
![Page 20: 1 Information Retrieval and Use (IRU) An Introduction To SQL Part 2](https://reader035.vdocuments.us/reader035/viewer/2022062309/5697bff01a28abf838cba961/html5/thumbnails/20.jpg)
20
Simple Constraints: Foreign keys
In the CUSTOMER_ACCOUNT table that we have just created we have two foreign keys CUSTOMER_ID and ACCOUNT_NUM. We can specify these at the time of defining the table as:
CREATE table CUSTOMER_ACCOUNTCUSTOMER_ID NOT NULL REFERENCES CUSTOMER,ACCOUNT_NUM NOT NULL REFERENCES ACCOUNT,PRIMARY KEY(REFNO,ACCNO));
(In order to get rid of the old table use the command DROP table customer_account. Take care when using this command because when you have dropped a table it has gone for good!)
![Page 21: 1 Information Retrieval and Use (IRU) An Introduction To SQL Part 2](https://reader035.vdocuments.us/reader035/viewer/2022062309/5697bff01a28abf838cba961/html5/thumbnails/21.jpg)
21
Simple Constraints: Foreign keys
When we have created a table we may find that the original definition of the table is now unsatisfactory. For example we may create another table BANK, and the account table has a foreign key reference to the table Bank in the attribute BRANCHNAME. Therefore we need to create a relation between Account and Bank. We can use the ALTER command to change the original definition of the Account table. ALTER TABLE ACCOUNT ADD(FOREIGN KEY(BRANCHNAME) REFERENCES
BANK);
![Page 22: 1 Information Retrieval and Use (IRU) An Introduction To SQL Part 2](https://reader035.vdocuments.us/reader035/viewer/2022062309/5697bff01a28abf838cba961/html5/thumbnails/22.jpg)
22
Dropping & altering objects
DROP <object type> <object name DROP TABLE EMP; (NOT undoable!) ALTER <object type> object name>
ADD <column name> <data type> /DROP COLUMN <column name>
ALTER TABLE EMP ADD NICKNAME VARCHAR(30)
![Page 23: 1 Information Retrieval and Use (IRU) An Introduction To SQL Part 2](https://reader035.vdocuments.us/reader035/viewer/2022062309/5697bff01a28abf838cba961/html5/thumbnails/23.jpg)
23
Inserting Data Using SQL
We use the SQL command INSERT to create rows.
INSERT INTO CUSTOMER
VALUES(12345, ‘DAWES’, 21, ‘BACK LANE’, ‘STAFFORD’);
Remember non-numerics require quotation
marks around their values.
These values have been entered into the Customer table in the order in which the columns appeared in the original create table statement.
![Page 24: 1 Information Retrieval and Use (IRU) An Introduction To SQL Part 2](https://reader035.vdocuments.us/reader035/viewer/2022062309/5697bff01a28abf838cba961/html5/thumbnails/24.jpg)
24
Deleting/UPDATING Data Using SQL
Use the DELETE command to remove data from a relation (i.e. table).
DELETE FROM CUSTOMER
WHERE REFNO = 12345;
Use the UPDATE command to alter the data held in the rows.
i.e. Change the name in the account table from Dawes to Dalby.
UPDATE CUSTOMER
SET NAME = ‘DALBY’
WHERE CUSTOMER_ID = 12345;
![Page 25: 1 Information Retrieval and Use (IRU) An Introduction To SQL Part 2](https://reader035.vdocuments.us/reader035/viewer/2022062309/5697bff01a28abf838cba961/html5/thumbnails/25.jpg)
25
SQL SUMMARY
You have worked through some simple SQL expressions and have learnt how to : Use the SELECT command for retrieving data. Use INSERT, UPDATE, AND DELETE commands to insert,
amend and delete data. Use aggregate functions to assist with processing data, i.e. COUNT, MIN, MAX, SUM, AVG. Use DDL to create, alter and drop tables. Create some simple constraints using the WHERE clause. Use the GROUP BY, HAVING and ORDER BY functions to
assist with selecting and displaying data. Create simple primary and foreign key constraints.
![Page 26: 1 Information Retrieval and Use (IRU) An Introduction To SQL Part 2](https://reader035.vdocuments.us/reader035/viewer/2022062309/5697bff01a28abf838cba961/html5/thumbnails/26.jpg)
26
Further Reading
Rolland chapter 5 Patrick chapters 1-3, 9-11, 13 SQL at w3schools - click to follow th
e link