Introduction

SQL:Structured Query Language

SQL is a standard computer language for accessing and manipulating databases.

SQL commands

• Data retrieval : SELECT
• Data manipulation language(DML) : INSERT, UPDATE, DELETE
• Data definition language(DDL) : CREATE, ALTER, DROP, RENAME, TRUNCATE
• Transaction control : COMMIT, ROLLBACK, SAVEPOINT
• Data control language(DCL) : GRANT, REVOKE

Data retrieval

Selecting Rows

• The basic query block

  SELECT [DISTINCT] {*,column [alias], ...}
  FROM  table;
  

• Selecting all columns, all rows

  SELECT *
  FROM  table_exam;
  

• Selecting specific columns

  SELECT col1, col2
  FROM  table_exam;
  

• Create expressions on NUMBER and DATE datatypes by using operators +, -, *, /

  SELECT salary * 12 , start_date +7
  FROM  table_salary;
  

• NVL Function
• • Convert NULL to an actual value with NVL.
  • Datatypes must match.

  SELECT salary * NVL(commission , 0)/100 ?Comm?
  FROM  table_salary;
  

• DISTINCT
• • Eliminate duplicate rows by using DISTINCT in the SELECT

  SELECT DISTINCT name
  FROM  table_salary;
  

• Limiting Selected Rows
• • Sort row output using the ORDER BY clause.
  • Enter search criteria using the WHERE clause.
• The ORDER BY clause
• • ASC ? ascending order, default.
  • DESC ? descending order.

    	SELECT Name , start_date
    	FROM  table_salary
    	ORDER BY start_date DESC;
    	

  • ORDER BY clause is the last in SELECT command.
  • Null values are displayed
    • Last for ascending sequences.
    • First for descending sequences.
  • You can order by position to save time.
    

    	SELECT Name , start_date
    	FROM  table_salary
    	ORDER BY 2;
    	

  • You can sort by a column that is not in the SELECT list.
    

    	SELECT Name , start_date
    	FROM  table_salary
    	ORDER BY salary DESC;
    	

• Comparison and Logical Operators
  • Logical comparison operators : = , > , >= , < , <=
  • SQL comparison operator
    • BETWEEN … AND…
    • IN(list)
    • LIKE
    • IS NULL
  • Logical operators
    • AND
    • OR
    • NOT
• Negative Expressions
  • Logical Operators : != , <> , ^=
  • SQL Operators
    • NOT BETWEEN
    • NOT IN
    • NOT LIKE
    • IS NOT NULL
• Character Functions
  • LOWER : Converts to lowercase
  • UPPER : Converts to uppercase
  • INTCAP : Converts to Initial capitalization
  • CONCAT : Concatenates values
  • SUBSRT : Returns substring
  • LENGTH : Returns number of characters
  • NVL : Converts a null value
• Number Functions
  • ROUND : Rounds value to specified decimal
  • TRUNC : Truncates value to specified decimal
  • MOD : Returns remainder of division
• Date Functions
  • MONTHS_BETWEEN : Number of months between two dates
  • ADD_MONTHS : Add calendar months to date
  • NEXT_DAY : Next day of the date specified
  • LAST_DAY : Last day of the month
  • ROUND : Round to date at midnight
  • TRUNC : Remove time portion from date
• Join
  • Equijoin

    	SELECT e.Name , s.salary
    	FROM  table_salary s , table_employee e
    	WHERE s.id = e.emp_id;
    	

  • Non-Equijoins

    	SELECT e.name , s.salary
    	FROM  table_salary s , table_employee e
    	WHERE e.salary BETWEEN s.max_salary AND s.min_salary;
    	

  • Outer Joins

    	SELECT e.id, e.name , c.name
    	FROM  table_customer c , table_employee e
    	WHERE e.id(+) = c.sale_id
    	ORDER BY e.id;
    	

    Place the operator on the side of the join where there is no value to join to.

  • Self Joins

    	SELECT worker.last_name||' works for '||manager.last_name
    	FROM  s_emp worker, s_emp manager
    	WHERE worker.manager_id  = manager.id;
    	

• Group Functions
  • COUNT Function

    Count(*) returns the number of rows in a table.

    	SELECT COUNT(*)
    	FROM  s_emp
    	WHERE dept_id = 31;
    	

    Count(expr) returns the number of non-null rows

    	SELECT COUNT(commisstion_pct)
    	FROM  s_emp
    	WHERE dept_id = 31;
    	

  • Group by clause

    	SELECT credit_rating, COUNT(*) ?#Cust?
    	FROM  s_customer
    	GROUP BY credit_rating ;
    	

    	SELECT title, SUM(salary) PAYROLL
    	FROM  s_emp
    	WHERE title NOT LIKE 'VP%'
    	GROUP BY title
    	ORDER BY SUM(salary);
    	

  • The HAVING Clause

    	SELECT title, SUM(salary) PAYROLL
    	FROM  s_emp
    	WHERE title NOT LIKE 'VP%'
    	GROUP BY title
    	HAVING SUM(salary) > 5000
    	ORDER BY SUM(salary);
    	

• Subquery
  • Single Row subqueries

    	SELECT last_name, title
    	FROM  s_emp
    	WHERE title =
    	( SELECT title
    	FROM s_emp
    	WHERE last_name = 'Smith');
    	

  • Multiple Row Subqueries

    	SELECT last_name, first_name, title
    	FROM  s_emp
    	WHERE dept_id IN
    	   (SELECT ID
    		FROM s_dept
    		WHERE name = 'Finance'
    		OR region_id = 2 );
    	

  • Having Clause with subaueries

    	SELECT dept_id, AVG(salary)
    	FROM  s_emp
    	GROUP dept_id
    	HAVING AVG(salary) >
    	  (SELECT AVG(salary)
    		FROM s_emp
    		WHERE dept_id = 32);
    	

  • Exists condition

    	SELECT *
    	FROM s_dept d
    	WHERE EXISTS
    	  (select 'T'
    		from s_order o
    		where d.dept_id = r.dept_id);
    	

    	SELECT *
    	FROM s_dept d
    	WHERE NOT EXISTS
    	  (select 'T'
    		from s_order o
    		where d.dept_id = r.dept_id);