Introduction to DatabaseSEM I, AY 2011-12

Department of Information TechnologySalalah College of Technology

Chapter No.3SQL

SQL (Structured Query Language)

Definition:Structured Query Language is a database computer language designed for managing data in relational database management systems (RDBMS). SQL was developed at IBM in the early 1970s.

SQL (Structured Query Language)

What Can SQL do? – SQL can execute queries against a database– SQL can retrieve data from a database– SQL can insert records in a database– SQL can update records in a database– SQL can delete records from a database– SQL can create new databases– SQL can create new tables in a database etc.

3

Languages of SQL

SQL commands are divided into four Languages. 1. DDL ( Data Definition Language)

(CREATE, ALTER, DROP)2. DML (Data Manipulation Language)

(INSERT, UPDATE, DELETE)3. DQL ( Data Query Language)

(SELECT)4. DCL (Data Control Language)

(COMMIT, ROLLBACK)

SQL Data TypesData type Storage size Description

BIT 1 byte Yes and No values and fields that contain only one of two values.

MONEY 8 bytes A scaled integer between – 922,337,203,685,477.5808 and 922,337,203,685,477.5807.

DATETIME 8 bytes A date or time value between the years 100 and 9999.

REAL 4 bytes A single-precision floating-point value with a range of – 3.402823E38 to – 1.401298E-45 for negative values, 1.401298E-45 to 3.402823E38 for positive values, and 0.

FLOAT 8 bytes A double-precision floating-point value with a range of – 1.79769313486232E308 to – 4.94065645841247E-324 for negative values, 4.94065645841247E-324 to 1.79769313486232E308 for positive values, and 0.

SMALLINT 2 bytes A short integer between – 32,768 and 32,767. (See Notes)

INTEGER 4 bytes A long integer between – 2,147,483,648 and 2,147,483,647. (See Notes)

TEXT 2 bytes per character (See Notes)

Zero to a maximum of 2.14 gigabytes.

DDL (Data Definition Language) (CREATE, ALTER, DROP)

CREATE TABLE table_name(column_name1 data_type [constraints…],column_name2 data_type [constraints…],column_name3 data_type [constraints…],....);

DDL (Data Definition Language)

CREATE TABLE Persons(P_ID integer, LastName char(50),FirstName char(50),Address char(255),City char(255));

DDL (Data Definition Language)Example:Create table tbldept(Dept_id smallint primary key, Dept_Name text(50) not null, Dateofstart datetime);

Create table tblpos(Pos_code smallint primary key,position_Name text(50) not null);

DDL (Data Definition Language)HOW TO ADD FOREIGN KEY (RELATIONSHIP)

Create table empTbl(emp_id smallint primary key, emp_Name text(50) not null,dept_id smallint references deptTbl,pos_code smallint references posTbl(pos_code));

DDL (Data Definition Language)

DROP COMMANDSYNTAX DROP TABLE table EXAMPLE drop table studentDrop table Employee

DDL (Data Definition Language)ALTERCOMMAND SYNTAXALTER TABLE tableADD COLUMN field type (size)DROP COLUMN fieldALTER COLUMN field type (size)ADD PRIMARY KEY (field)

Example ALTER TABLE Employees ADD COLUMN Notes TEXT(25)ALTER TABLE Employees DROP COLUMN Salary

DML ( Data Manipulation Language)(INSERT, UPDATE, DELETE)

INSERT Syntax:

INSERT INTO TABLE (FIELD1, FIELD2…….FIELDN) VALUES (VALUE1,VALUE2……..VALUEN);

Example:

INSERT INTO TBLDEPT (Dept_id, Dept_Name, Dateofstart) VALUES (212,’IT’,’12/12/2001’);INSERT INTO TBLDEPT (Dept_id, Dept_Name) VALUES (213,’bUSINESS’);

SHORTCUT FOR: INSERT ALL FIELDS

INSERT INTO TABLE VALUES (VALUE1,VALUE2……..VALUEN);

INSERT INTO DEPTBL VALUES (212,’IT’,’12/12/2001’);

DML ( Data Manipulation Language)

UPDATE TABLE

Syntax:SET FIELDNAME1=VALE1, FIELDNAME2=VALE2,…..WHERE FIELDNAME=VALUE….;

Example:

UPDATE TBLDEPTSET DATEOSSTART=#21/08/2010#WHERE Dept_id =213;

DML ( Data Manipulation Language)

DELETESYNTAX:DELETE FROM tableWHERE condition;

Example:Delete from TBLDEPTWhere dept_id = 213;

DQL (Data Query Language)SELECT

Syntax:

SELECT fields…FROM tableWHERE condition;

Example:• SELECT * FROM tblDept;• SELECT department, DateofStart FROM TBLDEPT;

DQL (Data Query Language)SELECT

SELECT * FROM PersonsWHERE City=“Sandnes”

SELECT department, DateofStart FROM tblDeptWHERE department like "M*" and department like "*S";

16

SELECT DISTINCT Example

SELECT DISTINCT City FROM Persons

OPERATORS IN SQL

OPERATORS Allowed in the WHERE Clause Operator Description

= Equal

<> Not equal

> Greater than

< Less than

>= Greater than or equal

<= Less than or equal

BETWEEN Between an inclusive range

LIKE Search for a pattern

IN If you know the exact value you want to return for at least one of the columns

Note: In some versions of SQL the <> operator may be written as != 17

OPERATORS IN SQL

SQL AND & OR OperatorsThe AND & OR OperatorsThe AND operator displays a record if both the first condition and the second condition is true.

The OR operator displays a record if either the first condition or the second condition is true.

AND Operator Example

SELECT * FROM PersonsWHERE FirstName='Tove'AND LastName='Svendson'

OR Operator Example

SELECT * FROM PersonsWHERE FirstName='Tove'OR FirstName='Ola'

18

ORDER BY CLAUSE

Syntax:SELECT field_1, field_2….. FROM tableWHERE conditionOrder by field [asc|desc]

Example:SELECT department, DateofStart FROM tblDeptWHERE department like "*S"Order by DateofStart;

Join QueryExamples:

– SELECT tblEmp.emp_id, tblEmp.name_1, tblEmp.name_4FROM tblEmp, tblposWHERE tblEmp.pos_code=tblPos.pos_code;

– SELECT tblEmp.emp_id, tblEmp.name_1, tblEmp.name_4, tblpos.position

FROM tblEmp, tblposWHERE tblEmp.Gender and tblEmp.pos_code=tblPos.pos_code

– SELECT tblEmp.emp_id, tblEmp.name_1, tblEmp.name_4, tblpos.position, tblDept.department

FROM tblEmp, tblpos, tblDeptWHERE tblEmp.gender="m" And tblEmp.pos_code=tblPos.pos_code And tblEmp.dept_code=tblDept.dept_code;

GROUP BY CLAUSESyntax:

SELECT field_1, field_2,… AGG_Fun(field_n)FROM tableWHERE ConditionGROUP BY field_1, field_2,… Order by field [asc|desc],….

Example:Q.1 Display no. of Male employee in each department. Sort the result by department in Z-A order.

SELECT tblDept.department, Count(tblEmp.emp_id) AS CountOfemp_idFROM tblemp, tblDeptWHERE tblDept.dept_code=tblEmp.dept_code And tblEmp.gender="m“GROUP BY tblDept.departmentORDER BY tblDept.department DESC;

GROUP BY CLAUSE (Continued)Q.2 Display no. of Male employee in each department having the number less than 5

SELECT tblDept.department, Count(tblEmp.emp_id) AS CountOfemp_idFROM tblemp, tblDeptWHERE tblDept.dept_code=tblEmp.dept_code And tblEmp.gender="m"GROUP BY tblDept.departmentHAVING Count(tblEmp.emp_id)<5ORDER BY tblDept.department DESC;

Introduction to SQLSQL CREATE TABLE Statement

CREATE TABLE Example

Now we want to create a table called "Persons" that contains five columns: P_ID, LastName, FirstName, Address, and City.

We use the following CREATE TABLE statement:

CREATE TABLE Persons(P_ID integer,LastName char(50),FirstName char(50),Address char(255),City char(255))

23

Introduction to SQL

SQL SELECT Statement Another SQL SELECT Examples

SELECT * FROM Persons

SELECT DISTINCT Example

SELECT DISTINCT City FROM Persons

24