SQL

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 <= 20000; Example-15 SELECT Name, Salary, DNo FROM Employee ORDER BY DNo DESC, Name; 14 SQL Functions             ROUND(column | expr, n) TRUNC(column | expr, n) MOD(m, n) ABS(n) CEIL(n) FLOOR(n) EXP(n) POWER(n, m) SQRT(n) SIGN(n) LN(n) LOG(n) SIN(n) COS(n) TAN(n) ASIN(n) ACOS(n) ATAN(n) SINH(n) COSH(n) TANH(n) NVL(n, m) VSIZE(n)            Rounds to n decimal places. If n is negative, numbers to the left are rounded. Truncates to n decimal places. Returns the remainder of m/n. Absolute value of n. Smallest integer larger than n. Largest integer smaller than n. en nm Square root of n. 1 if n is positive, -1 if negative, 0 if zero. Natural log of n (lg n) log10 n Sine of n. Cosine of n. Tangent of n. Arc sine of n (in radians). Arc cosine of n (in radians). Arc tangent of n (in radians). Hyperbolic sine value of n. Hyperbolic cosine value of n. Hyperbolic tan value of n. Null Value – Substitute m for n if n = null. Storage size of n. 15 Working with Dates Century Year Month Day Hour Minute Second 19 99 07 23 4 10 53 SELECT SYSDATE FROM DUAL; 16   Example-16 (MONTHS_BETWEEN) SELECT MONTHS_BETWEEN(SYSDATE, '09-JAN-1983') "Experience" FROM DUAL; Output-16 Experience --------------247.73471 Example-17 (GREATEST & LEAST) The function GREATEST finds the earliest date and LEAST finds the oldest date in the list. SELECT GREATEST('10-JAN-93', '10-JAN-98'), LEAST('10-JAN-93', '10-JAN-98') FROM DUAL; Output-17 GREATEST( LEAST('10 --------- --------10-JAN-98 10-JAN-93   17 Conversion Functions  VARCHAR2 or CHAR is converted to NUMBER  VARCHAR2 or CHAR is converted to DATE  NUMBER is converted to VARCHAR2 DATE is converted to VARCHAR2  18 Use of TO_CHAR    TO_CHAR function converts a date format to a character string Example-18 SELECT Name, Bdate, TO_CHAR(BDate, 'DD/MM/YY') AS "Formatted" FROM Employee WHERE SSN = 2222; Example-19 SELECT FROM WHERE Name, Bdate, TO_CHAR(BDate, 'Month, ddth, YYYY') AS "Formatted" Employee Name = 'Pooja';  Output-19 NAME BDATE Formatted -------------------- --------- --------------------Pooja 22-JAN-65 January , 22nd, 1965 19  Example-20 SELECT Name, Bdate, TO_CHAR(BDate, 'fmMonth, fmddth, YYYY') AS "Formatted" FROM Employee;  Output Prasad 11-JAN-57 January, 11th, 1957  Example-21 SELECT Name, Bdate, TO_CHAR(BDate, 'fmMonth, fmddth, YYYY "at" HH:MI P.M.')AS "Formatted" FROM Employee WHERE SSN = 2222;  Output Nandagopal 10-DEC-60 December, 10th, 1960 at 12:00 A.M. Note: “fm” suppresses unnecessary blanks Use HH12 for 12 hrs clock and HH24 for 24 hrs clock 20 Use of TO_DATE     TO_DATE function is to convert any character literal string into a valid date format. Example-22 SELECT TO_DATE('1-Sep-2003', 'DD/MM/YYYY') FROM DUAL; Output-22 TO_DATE(' --------01-SEP-03   Example-23 SELECT TO_DATE('08/30/2003', 'DD/MM/YYYY') FROM DUAL; Output-23 ERROR at line 1: ORA-01843: not a valid month 21 Character Functions Program SELECT LOWER('Bangalore') FROM DUAL; SELECT UPPER('Bangalore') FROM DUAL; SELECT INITCAP('bangalore institute of technology') FROM DUAL; SELECT CONCAT('Database ', 'Management') FROM DUAL; SELECT SUBSTR('Database', 5, 4) FROM DUAL; SELECT LENGTH('Database') FROM DUAL; SELECT FROM SELECT FROM INSTR('Database', 'b') DUAL; INSTR('Database', 'x') DUAL; Output bangalore BANGALORE Bangalore Institute Of Technology Database Management base 8 5 0 SELECT LPAD(Salary, 8, '*') FROM Employee WHERE SSN = 1111; SELECT FROM WHERE SELECT RPAD(Salary, 8, '*') Employee SSN = 1111; LTRIM(' Database', ' ') ***22000 22000*** Database 22 Aggregate Functions COUNT  AVG  MAX  MIN  STDDEV  SUM  VARIANCE  23  Example-24 SELECT COUNT(*) AS "No. of Employees" FROM Employee;  Example-25 SELECT SUM(Salary) AS Total FROM Employee;  Example-26 SELECT Name, MAX(Salary), MIN(Salary) FROM Employee; 24 GROUP BY Clause     The rules to be followed while using GROUP BY clause are given below: You can't have non-group function or column in SELECT clause. Group functions ignore nulls. By default the result of GROUP BY clause sort the data in ascending order. Example: SELECT DNo, SUM(Salary), COUNT(*), AVG(Salary) FROM Employee GROUP BY DNo; 25 Example-27 SELECT DNo, SUM(Salary), COUNT(*), AVG(Salary) FROM Employee GROUP BY DNo, MgrSSN;  HAVING clause  SELECT DNo, AVG(Salary) FROM Employee GROUP BY DNo HAVING DNo = 3; 26 The order of evaluation when all the clauses are specified is given below: 1. First all rows matching the WHERE conditions are retrieved. 2. These rows are grouped using the column(s) in GROUP BY clause. 3. Finally, groups matching the HAVING clause condition are retained. SELECT DNo, AVG(Salary) FROM Employee WHERE BDate LIKE '__-JAN-__' GROUP BY DNo HAVING MAX(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