Structured Query Language SQL LtitlTrngSqlV 1 0 SQL

Structured Query Language (SQL) Ltitl/Trng/Sql/V 1. 0

SQL • Non-Procedural Structured Query Language. (4 GL) • Data Sub language. • Developed by IBM in the late 1970’s. • In 1986, the ANSI made SQL as standard for all RDBMS. • SQL does not support any programming language constructs like if…else OR do…while. • SQL can be embedded in other programming languages like C, COBOL. Ltitl/Trng/Sql/V 1. 0 2

Features of SQL • SQL is a non-procedural language. • SQL is used for all types of Database activities by all ranges of users including, – System administrators. – Database administrators. – Application Programmers – End users. Ltitl/Trng/Sql/V 1. 0 3

DBMS LANGUAGES • Data Definition Language (DDL) – Creating & Altering the structure of the database. • Data Manipulation Language (DML) – Insert, Update & Delete. • Data Control Language (DCL) – Controlling access to the database(Grant, Revoke) Ltitl/Trng/Sql/V 1. 0 4

• Transaction Control … COMMIT, ROLLBACK • Data Retrieval … SELECT Ltitl/Trng/Sql/V 1. 0 5

DATA TYPES SUPPORTED IN SQL Ltitl/Trng/Sql/V 1. 0 6

Tables used : Ltitl/Trng/Sql/V 1. 0 7

Data Retrieval Using SQL*PLUS • SQL*PLUS provides a query capability in the form of SELECT statement. • One can view the current information in the tables by using this statement. Ltitl/Trng/Sql/V 1. 0 8

5 parts of basic SQL query • SELECT • … – list the columns you want (more than one table, use table. field) • FROM – list of tables used • WHERE – selection/join criteria (opt) • GROUP BY – how to summarize (opt) • ORDER BY – sorting order (opt) Ltitl/Trng/Sql/V 1. 0 9

SELECT Command • Displaying some OR all the columns from a table. SELECT <col 1, col 2, …. > OR <*> FROM <table_name> ; Ltitl/Trng/Sql/V 1. 0 1 0

1. List the Department table. SELECT * FROM Dept ; Ltitl/Trng/Sql/V 1. 0 1 1

A Better Way! 2. List the Department table SELECT Deptcode, Deptname, Deptmanager, Deptbudget FROM Dept ; Ltitl/Trng/Sql/V 1. 0 1 2

Only Some Columns 3. List all department managers with the names of their department SELECT Deptmanager, Deptname FROM Dept ; Ltitl/Trng/Sql/V 1. 0 1 3

WHERE Predicate ( = ) 4. List all employees of the Accounts department. SELECT Empname, Deptcode FROM Emp WHERE Deptcode = ‘Acct’ ; Ltitl/Trng/Sql/V 1. 0 1 4

WHERE Predicate ( < ) 5. List all officers. SELECT Empname, Gradecode FROM Emp WHERE Gradecode < 10 ; Ltitl/Trng/Sql/V 1. 0 1 5

Date Comparison 6. List all young employees. SELECT Empname, Birthdate FROM Emp WHERE Birthdate > ‘ 01 -Jan-70’ ; Ltitl/Trng/Sql/V 1. 0 1 6

Eliminating Redundant Data DISTINCT is an argument that provides a way for you to eliminate duplicate values. * e. g. SELECT DISTINCT deptcode FROM emp ; Ltitl/Trng/Sql/V 1. 0 1 7

Use of Boolean operators 1. SELECT * FROM emp WHERE Basicpay >= 2500 AND Basicpay <= 4500 ; 2. SELECT * FROM emp WHERE Desigcode = ‘Engg’ OR Basicpay > 3000 ; 3. SELECT empname FROM emp WHERE NOT deptcode = ‘Acct’ ; Ltitl/Trng/Sql/V 1. 0 1 8

In Operator IN operator can be used to select rows that match one of the values included in the list. * 1. SELECT empname FROM emp WHERE deptcode IN(‘Acct’, ‘Pers’) ; 2. SELECT empname FROM emp WHERE deptcode NOT IN(‘Acct’, ‘Pers’) ; Ltitl/Trng/Sql/V 1. 0 1 9

Between construct Defines a range that values must fall into make the predicate true. * List level staff List all middle level SELECT Empname, Gradecode FROM Emp WHERE Gradecode BETWEEN 10 AND 15; Ltitl/Trng/Sql/V 1. 0 2 0

Like construct * Used for pattern searching. Pattern consists of characters to be matched & the characters. * Wild. Card chars. _ (under score) % Ltitl/Trng/Sql/V 1. 0 wildcard’ Matches Any single character. Any sequence of zero or more characters. 2 1

Like construct List all employees with UMA in their names SELECT Empname FROM Emp WHERE Upper(Empname) LIKE ‘%UMA%’ ; Ltitl/Trng/Sql/V 1. 0 2 2

Null values List the employees who have not been assigned to any supervisor SELECT Empname, Supcode FROM Emp WHERE Supcode IS NULL ; Ltitl/Trng/Sql/V 1. 0 2 3

Compound Predicate - Interval List the female employees who have just completed 5 years SELECT Empname, Sex, Joindate FROM Emp WHERE Sex = “F” AND Joindate BETWEEN(sysdate - 5*365) AND (sysdate - 6*365) ; Ltitl/Trng/Sql/V 1. 0 2 4

Predicate using OR List employees who are either 50 years or more or have more than 20 years experience. SELECT Emp. Name FROM Emp WHERE Birth. Date < (sysdate - 50*365) OR Join. Date < (sysdate - 20*365) ; Ltitl/Trng/Sql/V 1. 0 2 5

Parentheses in Predicate List foremen who are either 50 years or more or have more than 20 years experience. SELECT Emp. Name FROM Emp WHERE Desig. Code = ‘FRMN’ AND ((Birth. Date < (sysdate - 50*365) OR Join. Date < (sysdate - 20*365)); Ltitl/Trng/Sql/V 1. 0 2 6

Expressions in SELECT List 1% of take-home pay of all employees. SELECT Empcode, (Basic + Allow -Deduct ) * 0. 01 FROM Salary WHERE Sal. Month = ‘ 1 -Mar-97’; Ltitl/Trng/Sql/V 1. 0 2 7

EMPCODE -------7129 7233 7345 7369 7844 7876 7900 7902 7934 (BASIC+ALLOW-DEDUCT) * 0. 01 ----------------------440 143 66 198 44 55 330 77 17 rows selected. Ltitl/Trng/Sql/V 1. 0 2 8

Aliasing List the present age of all the employees. SELECT Emp. Name, TRUNC(SYSDATE - Birth. Date) / 365) AGE FROM Emp ; Ltitl/Trng/Sql/V 1. 0 2 9

Ltitl/Trng/Sql/V 1. 0 3 0

Order By • For getting the resultant rows in a specified order. • Ascending order is default. List all employees ordered by age SELECT Emp. Name, TRUNC(SYSDATE - Birth. Date)/365 AGE FROM Emp ORDER BY Birth. Date ; Ltitl/Trng/Sql/V 1. 0 3 1

Ltitl/Trng/Sql/V 1. 0 3 2

Sorting Ascending / Descending List middle level staff according to seniority SELECT Emp. Name, Grade. Code, Grade. Level FROM Emp WHERE Grade. Code BETWEEN 10 AND 25 ORDER BY Grade. Code, Grade. Level DESC ; Ltitl/Trng/Sql/V 1. 0 3 3

Ltitl/Trng/Sql/V 1. 0 3 4

Sorting by Result of Expression List all middle according level staff to age according and seniority to seniority SELECT Emp. Name, TRUNC(SYSDATE - Birth. Date/365) AGE, Grade. Code, Grade. Level FROM Emp ORDER BY 2 DESC, Grade. Code, Grade. Level DESC ; Ltitl/Trng/Sql/V 1. 0 3 5

Ltitl/Trng/Sql/V 1. 0 3 6

AGGREGATE Functions • Act on a group or set of rows and return one row of summary information per set. * SUM Computes the total value of the group. * AVG Computes the average value of the group. * MIN Computes the minimum value of the group. * MAX Computes the maximum value of the group * COUNT Counts the no. of NON-NULL values for the specified group. * COUNT(*) Counts the no. of rows including those having NULLvalues for the given condition. Ltitl/Trng/Sql/V 1. 0 3 7

AGGREGATE Functions Count employees reporting to Singh. SELECT COUNT(*) FROM Emp WHERE Supcode = ‘ 7844’ ; Ltitl/Trng/Sql/V 1. 0 3 8

GROUP BY - Aggregate Functions • The grouping of rows is achieved by using the GROUP BY clause. • Allows you to define a subset of the values in a particular field in terms of another field, and apply an aggregate functions to the subset. • Enables you to combine fields and aggregate functions in a single SELECT statement Ltitl/Trng/Sql/V 1. 0 3 9

GROUP BY - Aggregate Functions List the number of staff reporting to each supervisor. SELECT Supcode, COUNT(*) FROM Emp GROUP BY Supcode ORDER BY Supcode ; Ltitl/Trng/Sql/V 1. 0 4 0

GROUP BY - Sum List the total take-home pay during 96 -97 for all employees. SELECT Emp. Code, SUM(Basic + Allow - Deduct) PAY FROM Salary WHERE Sal. Month BETWEEN ‘ 1 -Apr-96’ AND ‘ 31 -Mar-97’ GROUP BY Emp. Code ORDER BY Emp. Code ; Ltitl/Trng/Sql/V 1. 0 4 1

Ltitl/Trng/Sql/V 1. 0 4 2

GROUP BY - Max & Min List the maximum & minimum salaries in grades. SELECT Grade. Code, MAX(Basic), MIN(Basic) FROM Grade GROUP BY Grade. Code ORDER BY Grade. Code ; Ltitl/Trng/Sql/V 1. 0 4 3

Ltitl/Trng/Sql/V 1. 0 4 4

HAVING • Defines criteria used to eliminate certain groups from the output, just as the WHERE clause does for individual rows. • HAVING can take only arguments that have a single value per output group. Ltitl/Trng/Sql/V 1. 0 4 5

HAVING List the number of staff reporting to each supervisor having more than 3 people working under them. SELECT Sup. Code, COUNT(*) FROM Emp GROUP BY Sup. Code HAVING COUNT(*) > 3 ORDER BY Sup. Code ; Ltitl/Trng/Sql/V 1. 0 4 6

Where - Group By - Having List the total take-home pay during 96 -97 for all employees getting a total take-home-pay < Rs. 20000 SELECT Emp. Code, SUM(Basic + Allow - Deduct) PAY FROM Salary WHERE Sal. Month BETWEEN ‘ 1 -Apr-96’ AND ‘ 31 -Mar-97’ GROUP BY Emp. Code HAVING SUM(Basic + Allow - Deduct) ORDER BY Emp. Code ; Ltitl/Trng/Sql/V 1. 0 4 7

Where - Group By - Having List the maximum and minimum basic salary in each grade for grades with start < Rs. 4000 SELECT Grade. Code, MAX(Basic), MIN(Basic) FROM Grade GROUP BY Grade. Code HAVING MIN(Basic) < 4000 ORDER BY Grade. Code ; Ltitl/Trng/Sql/V 1. 0 4 8

Joining Table • The process of forming rows from two or more tables by comparing the contents of related cloumns is called ‘Joining Tables’. • Joins are the foundation of multi-table query processing in SQL. Syntax : SELECT <col 1, col 2, . . . > FROM <table_name> WHERE <logical expr. > ; Ltitl/Trng/Sql/V 1. 0 4 9

Joining Table - Types of Joins • • Equi-Join Cartesian Join Self Join Outer Join Ltitl/Trng/Sql/V 1. 0 5 0

Natural Join - Equi-Join Check the basic salary of all employees. SELECT Emp. Code, Emp. Name, Grade. Basic, Emp. Basic FROM EMP, GRADE WHERE Emp. Grade. Code = Grade. Code AND Emp. Grade. Level = Grade. Level ; Ltitl/Trng/Sql/V 1. 0 5 1

Self Join • Match and retrieve rows that have a matching value in different cloumns of the same table. List employees along with the names of their supervisors SELECT E. Empname, S. Emp. Name Supervisor, FROM EMP E, EMP S WHERE E. Sup. Code = S. Emp. Code ; Ltitl/Trng/Sql/V 1. 0 5 2

Cartesian Join • A cartesian product matches every row of one table to every row of the other table. e. g. SELECT Emp. Name, Dept. Name FROM EMP, DEPT WHERE Desig. Code = ‘clerk’ ; Ltitl/Trng/Sql/V 1. 0 5 3

Outer Join • Retrieving selected rows from one table that don’t match rows in the other table. To find department names & the employees in them, as well as those departments which do not have employees in them SELECT Dept. Name, Emp. Name FROM EMP, DEPT WHERE EMP. Dept. Code(+) = DEPT. Dept. Code ; Ltitl/Trng/Sql/V 1. 0 5 4

List the number of officers reporting to each supervisors having more than 3 people working under them. SELECT Sup. Code, COUNT(*) FROM EMP WHERE Grade. Code < 10 GROUP BY Sup. Code HAVING COUNT(*) > 3 ; Ltitl/Trng/Sql/V 1. 0 5 5

Nested Queries ( Subquery ) • Placing a query inside the predicate of another query, and using the inner query’s output in the predicate’s true or false condition. Subqueries divided into two groups. 1. Single-Row Subquery – Returns only one value to the outer query. 2. Multi-Row Subquery – Returns multiple values to the outer query Ltitl/Trng/Sql/V 1. 0 5 6

Single-Row Subquery To find out all the employees who have the same job as ‘scott’. SELECT Emp. Name FROM EMP WHERE Desig. Code = (SELECT Desig. Code FROM EMP WHERE Emp. Name = ‘scott’) ; Ltitl/Trng/Sql/V 1. 0 5 7

Single-Row Subquery To list the names of all employees working in sales dept; assuming that dept no. of sales dept is not known. SELECT Emp. Name FROM EMP WHERE Dept. Code = (SELECT Dept. Code FROM DEPT WHERE Dept. Name = ‘sales’); Ltitl/Trng/Sql/V 1. 0 5 8

Multi-Row Subquery List the name, job and salary of people in dept 20 who have the same job as people in dept. 30. SELECT Emp. Name, Desig. Code, Salary FROM EMP WHERE Dept. Code = 20 AND Desig. Code IN (SELECT Desig. Code FROM EMP WHERE Dept. Code = 30) ; Ltitl/Trng/Sql/V 1. 0 5 9

Multiple levels of Nesting Find name, job and salary of people in dept 20 who have the same job as people in the sales dept. SELECT Emp. Name, Desig. Code, Salary FROM EMP WHERE Dept. Code = 20 AND Desig. Code IN (SELECT Desig. Code FROM EMP WHERE Dept. Code = (SELECT Dept. Code FROM DEPT WHERE Dept. Name = ‘sales’) ; Ltitl/Trng/Sql/V 1. 0 6 0

Subqueries returning Multiple values Find out who are the highest paid employees in each dept. SELECT Dept. Code, Emp. Name, Salary FROM EMP WHERE (Dept. Code, Salary) IN (SELECT Dept. Code, MAX(Salary) FROM EMP GROUP BY Dept. Code) ; Ltitl/Trng/Sql/V 1. 0 6 1

EXISTS Operator Used to base a predicate on whether a subquery produces output or not. * * EXISTS is an operator that produces a True or False value. It takes a subquery as an argument and evaluates to True if it produces any output or false if it does not. Ltitl/Trng/Sql/V 1. 0 6 2

e. g. To list data from customer table if and only if one or more of the customers in the customers table are located in ‘San Jose’. Select cnum, cname, city From customer Where Exists (Select * From customer Where city = ‘San Jose’) ; Ltitl/Trng/Sql/V 1. 0 6 3

EXISTS Operator To list all department details from Dept. table which has atleast one employee. SELECT * FROM DEPT A WHERE EXISTS (SELECT * FROM EMP B WHERE B. Dept. Code = A. Dept. Code) ; Ltitl/Trng/Sql/V 1. 0 6 4

Inserting rows of data • insert into tablename [(col 1, col 2, …)] values (exp 1, exp 2, …); • If the col names are not listed, the data is assigned to all cols in order • To insert only into certain cols, name them … cols not named are given null values Ltitl/Trng/Sql/V 1. 0 6 5

INSERT one complete row INSERT INTO HISTORY VALUES (‘ 7369’, ‘ 01 -Aug-96’, ‘SLMN’, ‘ 12’, 5000) ; Ltitl/Trng/Sql/V 1. 0 6 6

INSERT one partial row Employ Hussein as a temporary employee. INSERT INTO EMP (Emp. Code, Emp. Name, Basic) VALUES (‘ 9123’, ‘Hussein’, 250) ; Ltitl/Trng/Sql/V 1. 0 6 7

INSERT thru Subquery Update the SALARY table for the month. INSERT INTO SALARY SELECT Emp. Code, trunc(Sysdate, mon), Basic*1. 5, Basic*0. 3 FROM EMP ; Ltitl/Trng/Sql/V 1. 0 6 8

Delete one row Delete employee record of Kaul. DELETE FROM EMP WHERE Emp. Code = ‘ 7934’ ; Ltitl/Trng/Sql/V 1. 0 6 9

Bulk Delete all employee records of Filing Department. DELETE FROM EMP WHERE Dept. Code = ‘FLNG’ ; Ltitl/Trng/Sql/V 1. 0 7 0

Delete entire Table Delete the entire SALARY Table. DELETE FROM SALARY ; Ltitl/Trng/Sql/V 1. 0 7 1

Update one Row Promote Gupta as Manager (Exports) UPDTAE EMP SET Grade. Code = ‘ 4’, Desig. Code = ‘MNGR’, Basic = 15000 WHERE Emp. Code = ‘ 7654’ ; Ltitl/Trng/Sql/V 1. 0 7 2

Bulk Update Raise the budget by 25% for all the departments except Facilities department. UPDTAE DEPT SET Dept. Budget = Dept. Budget * 1. 25 WHERE Dept. Code != ‘FACL’ ; Ltitl/Trng/Sql/V 1. 0 7 3

Creating a table with SQL • CREATE TABLE tablename (colname datatype [default value] [colconstrt] … tblconstraint, …, tblconstraint) [TABLESPACE tablespacename]; • Only one primary key per table (mult cols OK) Ltitl/Trng/Sql/V 1. 0 7 4

Constraint examples • default 50 – use 50 if no value is given • Column constraints: • …not null (default is to allow nulls) • …check condition • …unique • …primary key • …references Ltitl/Trng/Sql/V 1. 0 tblname [colname] 7 5

Common datatypes • (p is precision, s is scale) • VARCHAR 2(p) – variable length string …… p can be 1 to 4000 • NUMBER(p, s) - p is # of digits …… s is # of decimal digits (like Fortran) • DATE - + time, many possible formats … from 1/1/4712 BC to 12/31/4712 AD Ltitl/Trng/Sql/V 1. 0 7 6

• CHAR(p) – older fixed length format … p from 1 to 255, pads with blanks • LONG – variable length text string to 2 G … use is limited in queries • LOB – replacing LONG, up to 4 G (video) • …”Large Ltitl/Trng/Sql/V 1. 0 Object Block” 7 7

DDL for Table Dept Create table dept ( Deptcode char(4) constraint dept_pk primary key, Deptname char(25) not null, Deptmanager char(6), Deptbudget number Ltitl/Trng/Sql/V 1. 0 not null ); 7 8

DDL for Table Emp Create table emp ( Empcode char(6) constraint emp_pk primary key, Empname char(20) not null, Deptcode char(4) constraint emp_dept_rc references dept(deptcode) , Birthdate not null, Joindate not null, Sex char(1) not null check(sex in(‘M’, ‘F’)), Desigcode char(4) not null constraint emp_desig_rc references desig(desigcode) Ltitl/Trng/Sql/V 1. 0 7 9

Contd. . . Supcode Gradecode Basic Ltitl/Trng/Sql/V 1. 0 char(6) constraint emp_sup_rc references emp(empcode), number(2), number(5)) ; 8 0

DDL for Table Grade Create table grade ( Gradecode number(2) not null, Gradelevel number not null, Basic number(5) not null, constraint grade_pk primary key(Gradecode, Gradelevel) ; Ltitl/Trng/Sql/V 1. 0 8 1

To remove a table • DROP TABLE tablename … (ROLLBACK cannot restore this) • DROP removes the table definition • DELETE just removes the rows it leaves the table Ltitl/Trng/Sql/V 1. 0 8 2

Alter table • Best to get your tables correct the first time • But if alteration is needed, you can: • … add a new column • … change a column’s definition Ltitl/Trng/Sql/V 1. 0 8 3

Example adding new columns • alter table customers add (fax char(12), ctype char(1) check(ctype in (‘I’, ’B’))); • Cannot add a new column as not null … it starts out all null (empty)! Ltitl/Trng/Sql/V 1. 0 8 4

Example modifying a column Alter table customers modify ( street varchar 2(50)); • • Can increase (not decrease) width • Restrictions also exist on changes to: • … datatypes and constraints (cur violated) Ltitl/Trng/Sql/V 1. 0 8 5

Views - Creation u View is a logical table based on one or more tables. u View can be used as if it is a table. u View does not contain data of their own. Whenever a view is accessed, the query is evaluated. Thus view is dynamic. u Any changes made in the view affect the tables on which the view is based. u View helps to hide the ownership details of a table and complexity of query used to retrieve data, from the user. u Ltitl/Trng/Sql/V 1. 0 8 6

Views - Creation Create a view for Employee-Age. CREATE VIEW EMPAGE (Emp. Code, Age) AS (SELECT Emp. Code, TRUNC((SYSDATE - Birth. Date)/365) FROM EMP) ; Ltitl/Trng/Sql/V 1. 0 8 7

Views - Creation Create a view for Employee-Pay. CREATE VIEW EMPPAY (Emp. Code, Net. Pay, Sal. Month) AS (SELECT Emp. Code, (Basic + Allow - Deduct), Sal. Month FROM SALARY) ; Ltitl/Trng/Sql/V 1. 0 8 8

Use of Views List employees who are older than their supervisors. SELECT E. Emp. Code, Emp. Name FROM EMP E, EMPAGE A WHERE E. Emp. Code = A. Emp. Code AND age > (SELECT Age FROM EMPAGE WHERE E. Sup. Code = Emp. Code ) ; Ltitl/Trng/Sql/V 1. 0 8 9

Commit • Makes permanent all changes made since last Commit or since the beginning of the user’s session. Syntax : COMMIT [work] ; work - is optional & only provided for readability. Ltitl/Trng/Sql/V 1. 0 9 0

Roll. Back • Roll. Back undoes all the changes that has been done since the last commit or last Rollback or the named savepoint. Syntax : ROLLBACK [work] ; ROLLBACK TO Savepoint <savepoint_name> ; Ltitl/Trng/Sql/V 1. 0 9 1

Savepoint l Savepoint <savepoint_name> ; l Savepoint helps in breaking a transaction into parts. l Useful if only a part of changes made are to be discarded. If the name of an existing savepoint is reused, the old savepoint is erased. l Commit or Rollback without any parameters will erase all savepoints. l Rollback To Savepoint will undo all changes made after the Savepoint. l Ltitl/Trng/Sql/V 1. 0 9 2