SQL (Structured Query Language) is the structured query language used to manipulate relational databases. The concept of relational database was first proposed by Codd in 1970, and a language to extract and manipulate the data in it was developed theoretically during the following years. All SQL statements are instructions to the database. SQL is a non-procedural language, which means that commands are not executed step by step according to how they were written, but they are retained in memory, read through and executed in the most effective way. In relational database terminology, SQL provides 'automatic navigation' to the data in the database.
In these notes, you see an indication of two possible interfaces to SQL, namely sqlplus and activeSQL. Sqlplus is the standard Oracle interface while activeSQL is an experimental interface which Napier students have access to. For our tutorials we will all be using activeSQL.
Command Endings
Note that all SQL commands typed into sqlplus MUST end in a ; (semicolon) character. It will not work without it. The activeSQL interface is more forgiving, but even then if you enter more than 1 command into the interface at a time you MUST separate the commands with a semicolon.When entering SQL, you can have as many space characters and return characters as you like. They are completely ignored by Oracle. Sqlplus will, when you hit return, tell you what line you are currently on. These numbers are not part of the command so do not let them confuse you. In sqlplus, if you hit return twice (return on a blank line) the current command is cancelled.
In activeSQL, no command is executed until you hit the submit button.
SELECT
SQL command SELECT is used to retrieve information from a table. SELECT informs Oracle which table(s) to use and which column(s) and row(s) to retrieve.The asterisk can be used to denote all fields.
To list all fields and all records from the table employee.
SELECT * FROM employee ;To display only the fields empno and depno but all records
SELECT empno,depno FROM employee;
SPECIAL TABLE
There is a special table called CAT, which contains the name and type of all tables in your namespace. RunningSELECT * from CAT:produces the name and type of all local tables. This includes the 5 tables used in these tutorials: employee, empcourse, jobhistory, course, and department.
To find out about a particular table, you can look at the commands which created it, or you can use the DESCRIBE command. This tells you the attributes of the table in question. In sqlplus, this description does not include the Foreign Keys (the links to other tables - more of this in tutorial 2) but in activeSQL Foreign Keys ARE shown. For instance:
DESCRIBE employee; empno integer primary key surname varchar(15) forenames varchar(30) dob date address varchar(50) telno varchar(20) depno integer references department(depno)varchar(20) indicates a string which can be up to 20 characters long.
Date indicates that field is an Oracle date.
Integer indicates that that attribute is a number.
Depno integer References department(depno) tells us that depno in employee is a link to the department table's depno attribute. In this confusing case there are two attributes called depno, one in employee and one in department. These attributes are different attributes in different tables.
Predicates
Search conditions are made up of predicates. These are then combined together with ANDs, ORs, and NOTs.There are seven types of predicate:
- comparison
- BETWEEN predicate
- IN predicate
- LIKE predicate
- ANY or ALL predicate
- EXISTS predicate
- IS NULL
Comparisons
The comparisons available are= | equal to |
!= | not equal to |
> | greater than |
>= | greater than or equal to |
< | less than |
<= | less than or equal to |
SELECT empno, surname, telno FROM employee WHERE surname = 'Wright'List all current salaries in the range £20000 to £30000, listing their empno values.
SELECT empno, enddate, salary FROM jobhistory WHERE enddate IS NULL AND salary >= 20000 AND salary <= 30000List all the employees working in the company on January 1st 1980 and their position
SELECT empno, position, startdate, enddate FROM jobhistory WHERE (startdate < '01-JAN-1980' AND enddate > '01-JAN-1980') OR (startdate < '01-JAN-1980' AND enddate IS NULL)
BETWEEN (Inclusive)
List all the courses which occurred during 1988SELECT * FROM course WHERE cdate BETWEEN '01-JAN-1988' AND '31-DEC-1988'List all the courses which did not occur in 1988
SELECT * FROM course WHERE cdate NOT BETWEEN '01-JAN-1988' AND '31-DEC -1988'
LIKE
The LIKE predicate provides the only pattern matching capability in SQL for the character data types. It takes the following formcolumnname [NOT] LIKE pattern-to-matchThe pattern match characters are the percent sign (%) to denote 0 or more arbitrary characters, and the underscore (_) to denote exactly one arbitrary character.
List the employee numbers and surnames of all employees who have a surname beginning with C.
SELECT empno,surname FROM employee WHERE surname LIKE 'C%'List all course numbers and names for any course to do with accounting.
SELECT courseno,cname FROM course WHERE cname LIKE '%ccount%'List all employees who have r as the second letter of their forename.
SELECT surname, forenames FROM employee WHERE forenames LIKE '_r%'
IS NULL
List all employees numbers and their current positionSELECT empno,position FROM jobhistory WHERE enddate IS NULLThe remaining predicates will be dealt with at a later stage.
Set Functions
A set function is a function that operates on an entire column of values, not just a single value.List the total wage bill for the company at the moment.
SELECT SUM(salary) FROM jobhistory WHERE enddate IS NULLThis will retrieve the total salary for employees, where the enddate is empty or NULL.
The following are the set functions supported
Table 1: Set Functions
Name | Description | |
COUNT | Count of occurrences | |
SUM | Summation | |
AVG | Average (Sum/Count) | zero if Count = zero |
MAX | Maximum value | |
MIN | Minimum value |
SELECT COUNT(*) FROM jobhistory WHERE enddate IS NULLThe COUNT(*) function is used to count rows in a table, and is the exception to the following rule.
NULL values are ignored by the set functions.
Count how many jobs that employee number 25 has had previously.
SELECT COUNT(enddate) FROM jobhistory WHERE empno = 25Count how many jobs employee number 25 has had, including current job.
SELECT COUNT(startdate) FROM jobhistory WHERE empno = 25Calculate the average salary for all employees.
SELECT AVG(salary) FROM jobhistory WHERE enddate IS NULLNote that a 'column label' might be usefully added. This will be output in uppercase format unless enclosed in double quotes as follows :--
Find out the greatest salary.
SELECT MAX(salary) "Highest Salary" FROM jobhistory WHERE enddate IS NULL
Ordering rows of a query result
The order in which the selected rows are displayed is changed by adding an ORDER BY clause to the end of your SELECT command. The ordering is done numerically or alphabetically and can be ascending or descending.List all the employee numbers and salaries, ordered by their salary.
SELECT empno, salary FROM jobhistory WHERE enddate IS NULL ORDER BY salaryTo order by descending order you need to add DESC in the ORDER BY command
SELECT empno, salary FROM jobhistory WHERE enddate IS NULL ORDER BY salary DESC
Prevention of duplicate rows
If you print all the jobs in the jobhistory table you will get duplicate rows.SELECT position FROM jobhistoryTo print out only one for each different job you need to add DISTINCT in the SELECT clause.
SELECT DISTINCT position FROM jobhistory
Counting unique rows
Often you would like to count how many different rows exist in the result of a query. Doing:SELECT DISTINCT count(position) FROM jobhistoryresult in an answer which is no different from the same query without the DISTINCT. This is caused by the fact that count() is done before DISTINCT, and therefore in this case DISTINCT does nothing. What is actually needed is a way of forcing DISTINCT to be done before the count. This can be achieved by doing:
SELECT count(DISTINCT position) FROM jobhistory
0 nhận xét:
Post a Comment