Sql 1

Structured Query Language (SQL) SQL allow user to access data in relational database management systems, such as Oracle, Access, SQL Server, Foxpro, Infomix and others, by allowing users to describe the data the user wishes to see.

SQL statements are issued for the purpose of:



Data definition - Defining tables and structures in the database .



Data manipulation - Inserting new data,

Updating existing data, Deleting existing data, and Querying the Database ( Retrieving existing data from the database).



The following is list of SQL statements that can be issued against an Oracle database schema. These commands are available to any user of the Oracle database.

DDL Statements 

CREATE - Create new database objects such as tables or views



DROP

- Drop a database object such as a table, view or index



ALTER

- Change an existing table, view or index definition



GRANT - Allow another user to access database objects such as tables or views (For Data Control )



REVOKE - Disallow a user access to database objects such as tables and views (For Data Control )

DML Statements 

SELECT

- Retrieve data from a database table



INSERT

- Insert new data into a database table



UPDATE

- Change the values of some data items in a database table



DELETE

- Delete rows from a database table



TRUNCATE - Delete all rows from a database table (can not be rolled back)



ROLLBACK - Undo any recent changes to the database (DML Transactional)



COMMIT

- Make all recent changes permanent (DML transactional)

Creating Tables Tables are defined with the CREATE TABLE command.This command creates an empty table, a table with no rows.  Basically defines a table name as describing a set of named columns.  Defines the data types and sizes of the columns.  Each table must have at least one column.

The main SQL data types Type description variable-length char. string fixed-length char. string number date

Oracle SQL VARCHAR2(l) CHAR(l) NUMBER(p,s) DATE

More Data Types … dec(p, s) decimal(p, s) numeric(p, s) long

Variable-length strings (Up to 2 gigabytes )

bfile

File locators that point to a read-only binary object outside of the database (Up to 4 gigabytes )

clob

LOB locators that point to a large character object within the database (Up to 4 gigabytes)

Creating a table Eg:- 1) CREATE TABLE Employee ( Emp_No

Char(4),

Name

Varchar(15),

Address

Varchar(15),

DOB

Date,

Salary

Number(9,2) );

Creating table with constrains eg:- 2) CREATE TABLE Employee ( Emp_No

Char(4)

Name

Varchar(15)

Address

Varchar(15),

DOB

Date,

Salary

Number(9,2)

Primary Key, Not Null,

Check (Salary >

3500) );

Primary Key

Name field Cannot be

Always Salary must be > 3500

Altering a Table Once created, tables can be altered to accommodate changing needs.

Add new column to the table

Eg:- 1) ALTER TABLE Employee ADD Sex char(1);

+

Delete a existing column from the table 2) ALTER TABLE Employee DROP COLUMN Sex ;

Displaying Table Structure Eg:-

DESCRIBE Employee;

Name --------------EMP_NO NAME

Null? --------------

Type -------------

NOT NULL

CHAR(4)

VARCHAR2(15)

ADDRESS

VARCHAR2(15)

DOB

DATE

SALARY

NUMBER(9,2)

SEX

CHAR(1)

Entering Values to a table SQL provides the key word INSERT to add data to a table. Eg:- 1) INSERT INTO Employee VALUES (‘E001’,’Ranjith’,’Matara’,’12-Feb76’,7400);  

Entering Values to a table Naming columns for insert 2) INSERT INTO Employee (Emp_No, Name, Salary) VALUES (‘E001’,’Ranjith’,7400.00);

INSERT INTO Employee VALUES ('&Emp_No','&Name','&Address','&DOB',&Salary);

Enter value for emp_no

: E015

Enter value for name

: Thushara

Enter value for address

: Gampaha

Enter value for dob

: 12-Sep-67

Enter value for salary:

Indexes An Index is an ordered (alphabetic or numeric) list of the contents of a column or group of columns in a table.          - Improve the performance of queries. Eg:- CREATE INDEX EmpIndx ON Employee(Emp_No);

Retrieving Data The SELECT command simply instructs the database to retrieve information from a table.  

Get full details of a table Eg:-

SELECT * FROM Employee;

Get data from specified columns Eg:- SELECT

Emp_No, Name, Salary FROM Employee;

Removing duplicate data DISTINCT is an argument that provides a way for you to eliminate duplicate values from your select clause. Eg:- SELECT DISTINCT Address FROM Employee;

Ordering Output by fields SQL uses the ORDER BY command to allow you to impose an order on your output. - Ascending (ASC) - Descending (DESC)

Eg:- 1) SELECT Emp_No, Name, Salary FROM Employee ORDER BY Emp_No DESC; 2) SELECT Emp_No, Name, Salary FROM Employee ORDER BY 1 DESC;

Retrieving data using expressions Eg:- SELECT Name, Salary, Salary*110/100 “New Salary” FROM Employee;

Retrieving data which satisfy a condition. The WHERE clause of the SELECT command allows you to define a criteria to determine which rows are selected for output.

Eg:-

SELECT * FROM Employee WHERE

Emp_No =’ E002’;

A condition with logical operator Eg:- SELECT name, Salary

FROM Employee WHERE (Salary > 7000) AND (Address = ‘Matara’);

Using Wild Card characters (%) is used to represent any possible character (number, letter, or punctuation) or set of characters. (-) is used to represent any single Character.

Eg:- SELECT *

FROM Employee WHERE Name LIKE ‘R%’;

Built-In Functions SQL supports special built-in functions to perform operations on a collection of values in a column of a table. These operations produce a single value as the result of the query. COUNT

–

Number of values in the column.

SUM

-

Sum of the values in the column.

AVG

-

Average Sum of the values in the column.

MAX

-

Maximum value in the column.

MIN

-

Minimum value in the column.

Eg:-

SELECT COUNT(*) “No_Of_Rec” FROM Employee WHERE Address = ‘Matara’;

2

Eg:- SELECT MAX(Salary) “MaxSal” FROM Employee

MaxSal 10000

GROUP BY Clause SQL produce the key word GROUP BY to use in SELECT statement to group the result of the function. When a GROUP BY column is included in the SELECT statement each column-name in the SELECT clause must be single valued for each group in the result.

Eg:- SELECT Address, AVG(Salary) “Average” FROM Employee GROUP BY Address;

ADDRESS

Average

Colombo

4900

Galle

8900

Kalutara

6500

Matara

7966.67

Tangalle

8200

HAVING Clause SQL provides the clause HAVING to limit the retrieval only to group which satisfy a condition. This is similar to the WHERE clause. Expressions in a having clause must have a single value for each group.

Eg:- SELECT Address, COUNT(Emp_No) “Number” FROM Employee GROUP BY Address HAVING Address IN (‘Matara’,’Galle’);

ADDRESS

Number

Galle

2

Matara

3

Joining Tables SQL provides a “join” operation, which joins two or more tables temporarily, in the WHERE clause of the SELECT statement. To create a join between tables they must each contain at least one column with common values and data types.

Eg:- 1) SELECT customer.*, contract.* FROM customer, contract WHERE customer.cus_no=contract.cus_no;

Eg 2) SELECT A.*, B.* FROM customer A, contract B WHERE A.cus_no=B.cus_no;

3) SELECT A.cus_no, A.name, B.item FROM customer A, contract B WHERE B.mon_rental > 10000 AND A.cus_no=B.cus_no;