Structured Query Language SQL
Overview of SQL
It is a 4 GL Oracle9i in specific DML DDL Triggers and integrity constraints Embedded and Dynamic SQL Client-Server Execution and remote database access Transaction management Security OODBMS features, Data mining, spatial data, and XML data management
2
Basic SQL Commands
Statement SELECT INSERT UPDATE DELETE CREATE ALTER DROP RENAME COMMIT ROLLBACK SAVEPOINT GRANT REVOKE Description Data retrieval statement. Data Manipulation Language (DML). Add rows, change data, and delete few rows. Create new tables/views, remove tables/ views, and change the schema. Modified values of database are permanently written into disk, rollback the changes made. Access control can be assigned or changed.
3
Basic data types in Oracle
Data type CHAR (size) VARCHAR2(size DATE BLOB CLOB BFILE LONG LONG RAW NUMBER(size) NUMBER(size,d) DECIMAL FLOAT INTEGER SMALLINT Description Fixed length character. Max = 2000. Variable length character string. Max = 4000. Date. Vaild range is from Jan 1, 4712 B.C. to Dec 31, 4712 A.D. Binary large object. Max = 4GB. Character large object. Max = 4GB. Pointer to binary OS file. Character data of variable size. Max = 2GB. Raw binary data. Rest is same as LONG. Numbers. Max. size = 40 digits. Numbers. Range = 1.0E-130 to 9.9E125. Same as NUMBER. size/d can't be specified. Same as NUMBER. Same as NUMBER. size/d can't be specified. Same as NUMBER.
4
Example tables
Employee
SSN 1111 2222 3333 4444 5555 Name Deepak Nandagopal Pooja Prasad Reena BDate 5-Jan-62 10-Dec-60 22-Jan-65 11-Jan-57 15-Jan-85 Salary 22000 30000 18000 32000 8000 MgrSSN 4444 4444 2222 Null 4444 DNo 1 3 2 3 3
Department
DNo 1 2 3 DName Admin Research Accounts Loc Chennai Bangalore Bangalore
5
DDL
CREATE TABLE Department( DNo number(3) not null, DName varchar2(10) not null, Loc varchar2(15), primary key (DNo)); CREATE TABLE Employee( SSN number(4) not null, Name varchar2(20) not null, BDate date, Salary number(10,2), MgrSSN number(4), DNo number(2) not null, primary key (SSN), foreign key (MgrSSN) references Employee(SSN), foreign key (DNo) references Department(DNo));
6
Data Retrieval Statement (SELECT)
Syntax
SELECT *|{[DISTINCT] column | expression} FROM table(s);
The basic SELECT statement must include the following: - A SELECT clause. - A FROM clause.
7
Example-1 SELECT * FROM Employee; The * indicates that it should retrieve all the columns from Employee table. The output of this query is shown below:
Output-1
SSN NAME BDATE SALARY MGRSSN DNO ---- -------------------- --------- --------- --------- --------4444 Prasad 11-JAN-57 32000 3 5555 Reena 15-JAN-85 8000 4444 3 1111 Deepak 05-JAN-62 22000 4444 1 2222 Nandagopal 10-DEC-60 30000 4444 3 3333 Pooja 22-JAN-65 18000 2222 2
8
Example-2 SELECT * FROM ORDER BY SSN; Output-2
Employee
SSN NAME BDATE SALARY MGRSSN DNO ----- -------------------- --------- --------- --------- --------------------------------1111 Deepak 05-JAN-62 22000 4444 1 2222 Nandagopal 10-DEC-60 30000 4444 3 3333 Pooja 22-JAN-65 18000 2222 2 4444 Prasad 11-JAN-57 32000 3
5555 Reena 15-JAN-85 8000 4444
3
9
Using arithmetic operators
SELECT Name, Salary, Salary * 12 FROM Employee;
Using aliases An alias when used for a column: Renames a column heading It is useful in arithmetic calculations. AS keyword can optionally be used between column name and alias name. Example-3
SELECT FROM OR SELECT FROM
Name, Salary, Salary * 12 AS YRLY_SALARY Employee; Name, Salary, Salary * 12 "YRLY_SALARY" Employee;
10
Example-4
DESCRIBE Employee;
OR
DESC Employee;
Output-4
Name ------------------------------SSN NAME BDATE SALARY MGRSSN DNO
Null? -------NOT NULL NOT NULL
Type ---NUMBER(4) VARCHAR2(20) DATE NUMBER(10,2) NUMBER(4) NOT NULL NUMBER(2)
11
Select Statement with Where
Example-5 SELECTName, Salary FROM Employee WHERE Salary > 25000; Example-6 SELECTDName, Loc FROM Department WHERE Loc = 'Bangalore'; Example-7 SELECTName, BDate FROM Employee WHERE BDate = '11-Jan-57';
12
Example-8 SELECT Name, BDate FROM Employee WHERE Salary BETWEEN 25000 AND 30000; Example-9 SELECT SSN, Name FROM Employee WHERE DNo IN (1, 2); Example-10 SELECT Name FROM Employee WHERE Name LIKE 'P%'; Example-11 SELECT Name, DNo FROM Employee WHERE BDate LIKE '__-JAN-__';
13
Example-12 SELECT Name FROM Employee WHERE MgrSSN IS NULL; Example-13 SELECT Name, Salary, DNo FROM Employee WHERE Salary > 30000 AND DNo = 3; Example-14 SELECT Name, Salary FROM Employee WHERE Name LIKE 'P%' OR Salary 10000;
27
MULTITABLE QUERIES
Simple Equi-Joins : guidelines
Table names in the FROM clause is separated
with commas. Use appropriate joining condition. This means that the foreign key of table1 will be made equal to the primary key of table2. When the attributes or columns have the same names, tag them with table names using dot notation. Without proper joining condition or attributes the SQL will display the Cartesian product of the tables in the FROM clause.
28
Example-28: Display the employee names and the department names for which they work. SELECT Name, DName FROM Employee, Department WHERE Employee.DNo = Department.DNo; Example-29 : Display only employees working for Accounts department. SELECT Name, Salary, DName FROM Employee, Department WHERE (Employee.DNo = Department.DNo) AND (DName = 'Accounts');
29