SQL-1 Introduction

Structured Query Language
(SQL)
Note:
The slides are adapted from “Oracle Corporation”
Introduction
 SQL pronounced as Sequel is a language for RDBMS or SQL

makes RDBMS possible.
 It was first defined by Chamberlin and others at IBM’s research
laboratory in San Jose, California (Late 1970’s)
 ANSI and ISO have worked for the standardization of SQL.
 SQL is a non procedural language.
 SQL does much more and is more powerful than just asking
queries from the database.
 Using SQL, one can:
Introduction
• Create Relations or Tables.
• Insert, Delete and Update Records.
• Apply Validation Checks.
• Create users.
• Apply Security Restrictions etc. etc.
Communicating with a RDBMS Using SQL
SQL statement
is entered Statement is sent to
SQL> SELECT loc database
2 FROM dept;
Database
Data is displayed
LOC
-------------
NEW YORK
DALLAS
CHICAGO
BOSTON
SQL Statements
Tables Used in the Course
EMP
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
--------- ---------- --------- --------- --------- --------- --------- ---------
7839 KING PRESIDENT 17-NOV-81 5000 10
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
7566 JONES MANAGER 7839 02-APR-81 2975 20
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
DEPT 7900 JAMES CLERK 7698 03-DEC-81 950 30
DEPTNO 7521
DNAMEWARD SALESMAN
LOC 7698 22-FEB-81 1250 500 30
--------- 7902 FORD
-------------- ANALYST
---------- 7566 03-DEC-81 SALGRADE
3000 20
10 7369 SMITH
ACCOUNTING CLERK
NEW YORK 7902 17-DEC-80 800
GRADE LOSAL 20
HISAL
20 7788 SCOTT
RESEARCH ANALYST
DALLAS 7566 09-DEC-82 3000 --------- ---------
--------- 20
30 7876
SALESADAMS CLERK
CHICAGO 7788 12-JAN-83 1100 1 700 20
1200
40 7934 MILLER
OPERATIONS CLERK
BOSTON 7782 23-JAN-82 1300 2 1201 10
1400
3 1401 2000
4 2001 3000
5 3001 9999
Querying the Tables
• The Select statement is the building block for querying the
tables:
• Syntax:
SELECT [DISTINCT] {* | column [alias],...}
FROM table (s)
[WHERE clause]
[ORDER BY clause]
[GROUP BY clause]
[HAVING clause]
• SELECT identifies what columns
• FROM identifies which table
Capabilities of SQL SELECT Statements
Selection Projection
Table 1 Table 1
Join
Table 1 Table 2
Writing SQL Statements
• SQL statements are not case sensitive.

• SQL statements can be on one or
more lines.
• Keywords cannot be abbreviated or split across
lines.
• Clauses are usually placed on separate lines.
• Tabs and indents are used to enhance readability.
Writing SQL Statements
 Examples (Querying emp table):
• Select * from emp;
• Select empno from emp;
• Select empno,ename,job from emp;
• Select ename,sal+100 from emp;
• Select ename, sal*2/100 “Tax” from emp;
• Select empno,ename,job,sal,sal*12 “Annual Sal” from emp;
• Select distinct job from emp;
Selecting All Columns
SQL> SELECT *
2 FROM dept;
DEPTNO DNAME LOC

--------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
Selecting Specific Columns
SQL> SELECT deptno, loc

2 FROM dept;
DEPTNO LOC
--------- -------------
10 NEW YORK
20 DALLAS
30 CHICAGO
40 BOSTON
Arithmetic Expressions
• Create expressions on NUMBER and DATE data by

using arithmetic operators.
Operator Description
+ Add
- Subtract
* Multiply
/ Divide
Using Arithmetic Operators
SQL> SELECT ename, sal, sal+300

2 FROM emp;
ENAME SAL SAL+300

---------- --------- ---------
KING 5000 5300
BLAKE 2850 3150
CLARK 2450 2750
JONES 2975 3275
MARTIN 1250 1550
ALLEN 1600 1900
...
14 rows selected.
Operator Precedence
_
* / +
• Multiplication and division take priority over
addition and subtraction.
• Operators of the same priority are evaluated from
left to right.
• Parentheses are used to force prioritized evaluation
and to clarify statements.
Defining a Column Alias
• Renames a column heading

• Is useful with calculations
• Immediately follows column name; optional AS
keyword between column name and alias
• Requires double quotation marks if it contains
spaces or special characters or is case sensitive
Using Column Aliases
SQL> SELECT ename AS name, sal salary
2 FROM emp;
NAME SALARY
------------- ---------
...
SQL> SELECT ename "Name",

2 sal*12 "Annual Salary"
3 FROM emp;
Name Annual Salary

------------- -------------
...
Duplicate Rows
• The default display of queries is all rows, including
duplicate rows.
SQL> SELECT deptno
2 FROM emp;
DEPTNO
---------
10
30
10
20
...
14 rows selected.
Eliminating Duplicate Rows
Eliminate duplicate rows by using the

DISTINCT keyword in the SELECT clause.
SQL> SELECT DISTINCT deptno
2 FROM emp;
DEPTNO
---------
10
20
30

SQL-1 Introduction

Uploaded by

Copyright:

Available Formats

SQL-1 Introduction

Uploaded by

Document Information

Copyright

Available Formats

Share this document

Share or Embed Document

Sharing Options

Did you find this document useful?

Is this content inappropriate?

Copyright:

Available Formats

SQL-1 Introduction

Uploaded by

Copyright:

Available Formats

Structured Query Language

 SQL pronounced as Sequel is a language for RDBMS or SQL

• SQL statements are not case sensitive.

DEPTNO DNAME LOC

SQL> SELECT deptno, loc

• Create expressions on NUMBER and DATE data by

SQL> SELECT ename, sal, sal+300

ENAME SAL SAL+300

• Renames a column heading

SQL> SELECT ename "Name",

Name Annual Salary

Eliminate duplicate rows by using the

You might also like