SQL l l 1 SQL Structured Query Language

SQL l l 1 SQL (Structured Query Language) : is a database language that is used to create, modify and update database design and data. Good Example of DBMS’s sub language.

SQL…. Con’t l Consist of 3 Sub languages: – DDL (Data Definition Language). l l l – DML (Data Manipulation Language). l l – 2 Create Table. Drop Table. Alter Table. Insert. Update. Delete. Select. DCL (Data Control Language).

DDL (Data Definition Language) l Available Data Types: – – – – – 3 Integer/ Int. Single / Float. Counter / autoincrement Text/ String / char. Long char / memo. Date / Time / Datetime. Currency. Long binary / OLEObject. Boolean.

DML (Data Manipulation Language) l l DML in SQL is used to deal with the data only and it is not concerned with the structure of the database. Commands that will be considered in DML are: – – 4 Insert Into. Delete. Update. Select.

DML (Insert Into) Insert Into command is used to insert data inside tables. l Syntax is: Insert into table_name [(field_name, …)] values (value 1, value 2, …. ) l - If you didn’t specify the field names, then you must include values for each field in the table. 5

DML (Insert Into) l E. g. Given this table: (Books table) ISBN Title Price Pub_date To Insert a new row inside this table, this command should be written: Insert Into Books (ISBN, Title, Price, pub_date) values (“ 1 -000 -0010 -8”, ”Access SQL”, 150, ” 01/01/2005”) OR Insert Into Books values (“ 1 -000 -0010 -8”, ”Access SQL”, 150, ” 01/01/2005”) 6

DML (Insert Into) l l 7 In the previous example, if specific fields need to be added within the row (Not the entire record) so field names should be specified in the Insert Into Command. E. g. Insert a new record inside the books table by adding ISBN, Title and the price only. Insert Into Books (ISBN, Title, Price) values (“ 1 -023 -0010 -8”, ”Database Systems”, 200)

DML (Delete) Delete command is used to delete records from tables. l Syntax is : Delete from table_name where criteria; criteria : is used to determine which rows to delete. l 8

DML (Delete) Stno KIC-9870 KIC-5643 KIC-2341 Stname Ali Ahmad Saeed Moh’d Naser Hasan Address AD AD Dub Delete from Students; This command will delete the whole records Stno 9 Stname Address Students Table

DML (Delete) Stno KIC-9870 KIC-5643 KIC-2341 Stname Ali Ahmad Saeed Moh’d Naser Hasan Address AD AD Dub Students Table Delete from Students where Address = “AD”; 10 This Command will delete all the students whose Address is AD. Stno Stname Address KIC-2341 Naser Hasan Dub

DML (Update) Update command is used to update a specific data inside the tables. l Syntax is : Update table_name set new_value_expression, … where criteria; where clause: is used to restrict updating a specific rows. l 11

DML (Update) ISBN Title Price Pub_date 1 -002 -032 -1 Data. Base Design 120 2/1/2004 1 -412 -002 -6 Networking 150 3/6/2004 110 1/8/2005 1 -0302 -046 -7 C++ Books Table Update Books set price = 200; This command without any criteria (condition) will update all the books’ price to 200 12

DML (Update) All The prices is updated to 200 13 ISBN Title Price Pub_date 1 -002 -032 -1 Data. Base Design 200 2/1/2004 1 -412 -002 -6 Networking 200 3/6/2004 1 -0302 -046 -7 C++ 200 1/8/2005

DML (Update) ISBN Title Price Pub_date 1 -002 -032 -1 Data. Base Design 120 2/1/2004 1 -412 -002 -6 Networking 150 3/6/2004 110 1/8/2005 1 -0302 -046 -7 C++ Books Table Update Books set price = 200 where ISBN=“ 1 -002 -032 -1”; This command will update the price to 200 for the book whose ISBN = 1 -002 -032 -1 14

DML (Update) 15 ISBN Title Price Pub_date 1 -002 -032 -1 Data. Base Design 200 2/1/2004 1 -412 -002 -6 Networking 150 3/6/2004 1 -0302 -046 -7 C++ 110 1/8/2005

Capabilities of SQL SELECT Statements Projection Selection Table 1 Join 16 Table 1 Table 2

Basic SELECT Statement SELECT FROM l l 17 *|{[DISTINCT] column|expression [alias], . . . } table; SELECT identifies what columns FROM identifies which table

Selecting All Columns SELECT * FROM departments; 18

Selecting Specific Columns SELECT department_id, location_id FROM departments; 19

Arithmetic Expressions Create expressions with number and date data by using arithmetic operators. Operator 20 Description + Add - Subtract * Multiply / Divide

Using Arithmetic Operators SELECT last_name, salary + 300 FROM employees; … 21

Using Parentheses SELECT last_name, salary, 12*(salary+100) FROM employees; … 22

Duplicate Rows SELECT department_id FROM employees; The default display of queries is all rows, including duplicate rows. … 23

Eliminating Duplicate Rows Eliminate duplicate rows by using the DISTINCT keyword in the SELECT clause. SELECT DISTINCT department_id FROM employees; 24

Limiting Rows Using a Selection EMPLOYEES … “retrieve all employees in department 90” 25

Limiting the Rows Selected l Restrict the rows returned by using the WHERE clause. SELECT FROM [WHERE l 26 *|{[DISTINCT] column|expression [alias], . . . } table condition(s)]; The WHERE clause follows the FROM clause.

Using the WHERE Clause SELECT employee_id, last_name, job_id, department_id FROM employees WHERE department_id = 90 ; 27

Character Strings and Dates l Character strings and date values are enclosed in double quotation marks. SELECT last_name, job_id, department_id FROM employees WHERE last_name = “Ahmad”; 28

Comparison Conditions Operator 29 Meaning = Equal to > Greater than >= Greater than or equal to < Less than <= Less than or equal to <> Not equal to

Using Comparison Conditions SELECT last_name, salary FROM employees WHERE salary <= 3000; 30

Other Comparison Conditions 31 Operator Meaning BETWEEN. . . AND. . . Between two values (inclusive), IN(set) Match any of a list of values LIKE Match a character pattern IS NULL Is a null value

Using the BETWEEN Condition Use the BETWEEN condition to display rows based on a range of values. SELECT last_name, salary FROM employees WHERE salary BETWEEN 2500 AND 3500; Lower limit 32 Upper limit

Using the IN Condition Use the IN membership condition to test for values in a list. SELECT employee_id, last_name, salary, manager_id FROM employees WHERE manager_id IN (100, 101, 201); 33

Using the LIKE Condition l l Use the LIKE condition to perform wildcard searches of valid search string values. Search conditions can contain either literal characters or numbers: – * denotes zero or many characters. SELECT FROM WHERE 34 first_name employees first_name LIKE 'S*';

Using the NULL Conditions Test for nulls with the IS NULL operator. SELECT last_name, manager_id FROM employees WHERE manager_id IS NULL; 35

Logical Conditions Operator Meaning AND Returns TRUE if both component conditions are true OR Returns TRUE if either component condition is true NOT 36 Returns TRUE if the following condition is false

Using the AND Operator AND requires both conditions to be true. SELECT FROM WHERE AND 37 employee_id, last_name, job_id, salary employees salary >=10000 job_id LIKE ‘*MAN*';

Using the OR Operator OR requires either conditions to be true. SELECT FROM WHERE OR 38 employee_id, last_name, job_id, salary employees salary >= 10000 job_id LIKE ‘*MAN*';

Using the NOT Operator SELECT last_name, job_id FROM employees WHERE job_id NOT IN ('IT_PROG', 'ST_CLERK', 'SA_REP'); 39

ORDER BY Clause l Sort rows with the ORDER BY clause ASC: ascending order, default – DESC: descending order The ORDER BY clause comes last in the SELECT statement. – l SELECT last_name, job_id, department_id, hire_date FROM employees ORDER BY hire_date ; … 40

Sorting in Descending Order SELECT last_name, job_id, department_id, hire_date FROM employees ORDER BY hire_date DESC ; … 41

Sorting by Multiple Columns l The order of ORDER BY list is the order of sort. SELECT last_name, department_id, salary FROM employees ORDER BY department_id, salary DESC; … l 42 You can sort by a column that is not in the SELECT list.

Summary • Use the WHERE clause to restrict rows of output – Use the comparison conditions – Use the BETWEEN, IN, LIKE, and NULL conditions – Apply the logical AND, OR, and NOT operators • Use the ORDER BY clause to sort rows of output SELECT FROM [WHERE [ORDER BY 43 *|{[DISTINCT] column|expression [alias], . . . } table condition(s)] {column, expr, alias} [ASC|DESC]];

Obtaining Data from Multiple Tables EMPLOYEES DEPARTMENTS … … 44

Joining Tables Use a join to query data from more than one table. SELECT FROM WHERE l l 45 table 1. column, table 2. column table 1, table 2 table 1. column 1 = table 2. column 2; Write the join condition in the WHERE clause. Prefix the column name with the table name when the same column name appears in more than one table.

What is an Equijoin (Inner Join)? EMPLOYEES DEPARTMENTS … … Foreign key 46 Primary key

Retrieving Records with Equijoins SELECT employees. employee_id, employees. last_name, employees. department_id, departments. location_id FROM employees, departments WHERE employees. department_id = departments. department_id; … 47

Using Table Aliases SELECT e. employee_id, e. last_name, e. department_id, d. location_id FROM employees e , departments d WHERE e. department_id = d. department_id; l l 48 Simplify queries by using table aliases. Improve performance by using table prefixes.

Joining More than Two Tables EMPLOYEES DEPARTMENTS LOCATIONS … l 49 To join n tables together, you need a minimum of n-1 join conditions. For example, to join three tables, a minimum of two joins is required.

Non-Equijoins EMPLOYEES … 50 JOB_GRADES Salary in the EMPLOYEES table must be between lowest salary and highest salary in the JOB_GRADES table.

Retrieving Records with Non-Equijoins SELECT e. last_name, e. salary, j. grade_level FROM employees e, job_grades j WHERE e. salary BETWEEN j. lowest_sal AND j. highest_sal; … 51

What Are Group Functions? Group functions operate on sets of rows to give one result per group. EMPLOYEES The maximum salary in the EMPLOYEES table. … 52

Types of Group Functions l l l 53 AVG COUNT MAX MIN SUM

Group Functions Syntax SELECT FROM [WHERE [GROUP BY [ORDER BY 54 [column, ] group_function(column), . . . table condition] column];

Using the AVG and SUM Functions You can use AVG and SUM for numeric data. SELECT AVG(salary), MAX(salary), MIN(salary), SUM(salary) FROM employees WHERE job_id = ‘ Rep 1'; 55

Using the MIN and MAX Functions SELECT MIN(hire_date), MAX(hire_date) FROM employees; You can use MIN and MAX for any data type. 56

Using the COUNT Function SELECT COUNT(*) FROM employees WHERE department_id = 50; COUNT(*) returns the number of rows in a table. 57

Creating Groups of Data EMPLOYEES 4400 The average salary 3500 in EMPLOYEES table 6400 for each department. 9500 10033 … 58

Creating Groups of Data: The GROUP BY Clause Syntax SELECT FROM [WHERE [GROUP BY [ORDER BY column, group_function(column) table condition] group_by_expression] column]; Divide rows in a table into smaller groups by using the GROUP BY clause. 59

Using the GROUP BY Clause All columns in the SELECT list that are not in group functions must be in the GROUP BY clause. SELECT department_id, AVG(salary) FROM employees GROUP BY department_id ; 60

Grouping by More Than One Column EMPLOYEES … 61 “Add up the salaries in the EMPLOYEES table for each job, grouped by department.

Using the GROUP BY Clause on Multiple Columns SELECT department_id, job_id, SUM(salary) FROM employees GROUP BY department_id, job_id ; 62

Illegal Queries Using Group Functions l You cannot use the WHERE clause to restrict groups. You use the HAVING clause to restrict groups. l You cannot use group functions in the WHERE clause. l SELECT FROM WHERE GROUP BY WHERE department_id, AVG(salary) employees AVG(salary) > 8000 department_id; AVG(salary) > 8000 * ERROR at line 3: ORA-00934: group function is not allowed here 63

Excluding Group Results: The HAVING Clause Use the HAVING clause to restrict groups: 1. Rows are grouped. 2. The group function is applied. 3. Groups matching the HAVING clause are displayed. SELECT FROM [WHERE [GROUP BY [HAVING [ORDER BY 64 column, group_function table condition] group_by_expression] group_condition] column];

Using the HAVING Clause SELECT FROM GROUP BY HAVING 65 department_id, MAX(salary) employees department_id MAX(salary)>10000 ;

Using the HAVING Clause SELECT FROM WHERE GROUP BY HAVING ORDER BY 66 job_id, SUM(salary) PAYROLL employees job_id NOT LIKE '%REP%' job_id SUM(salary) > 13000 SUM(salary);

DDL…Cont’d l Constraints on tables (Fields): – – – 67 Unique. Not Null. Check. Primary key. Foreign key.

DDL (Creating Tables) Create table_name (Column name 1 Data. Type (size), Column name 2 Data. Type (size), . . Multicolumn constraint ); l 68

DDL (Creating Tables) E. g. Create these two tables using SQL Language? Dept_tbl (Deptno, Dname, Location) Employees (eno, ename, DOB, salary, dno) l 69

DDL (Creating Tables) l Solun: Create table Dept_tbl ( deptno Integer(2), dname text(10), location text(15), constraint dept_pk primary key (deptno)); Dept_tbl table: Deptno 70 dname location

DDL (Creating Tables) Create table employees ( eno integer(4), ename text(20), DOB date, salary float (6, 2), dno integer(2), constraint emp_pk primary key (eno), constraint emp_dept_fk foreign key (dno) references dept_tbl (deptno) on update cascade on delete cascade); 71

DDL (Dropping Tables) To drop ( delete) the structure of any table in SQL, this command is used: Drop table_name (cascade / restrict) l e. g. Drop table employees cascade. 72

DDL (Altering Tables) l To add, modify , delete specific fields from an existing table, this command is used: Alter table_name add column colname datatype (size) | drop column colname | add constraint ……. . | drop constraint ……. . | modify colname new_Data. Type; 73

DDL (Altering Tables) Employees table before modifying it : Eno ename DOB Salary dno E. g. Modify the employees table as illustrated below: -Add 2 fields (Certification and Major). -Modify the (eno) field to accept numbers of 5 digits. -Modify the table so that it will not accept salaries < 2000. 74

DDL (Altering Tables) - Alter table employees add column major text (10); - Alter table employees add column certification text (5); - Alter table employees modify eno integer(5); - Alter table employees add constraint chk_sal check (salary >=2000); 75