12/8/2015

 The SQL is a standardized query language for

requesting information from a database.
 The original version called SEQUEL (Structured
English query language) was designed by an
IBM research center in 1974 and 1975.
 SQL was first introduced as a commercial
database system in 1979 by Oracle
Corporation

 MS SQL relation attributes

 MySQL
schema primary
 Postgre
 Firebird
AUTHORS
key t
 Oracle idno fname lname address
95023 Juan Dela Cruz NY u
95924 C. J.
95025 John
Date
Doe
California
Texas
p
l
relation name e
s

Database Definition
table columns 

 Database Data Manipulation

definition primary  Defining and Maintaining Integrity
key  Maintaining Security
AUTHORS
idno fname lname address r
95023 Juan
95924 C. J.
Dela Cruz
Date
NY
California
o
95025 John Doe Texas w
s
table name

1
 12/8/2015

Rules
Command Description  SQL commands may be on one or more lines.
SELECT This is the most commonly used command; it used to  Clauses are usually placed on separate lines.
 Tabulation can be used.
retrieve data from the database.
 Command words cannot be split across lines
INSERT These three commands are used to enter new rows, change  SQL commands are not case sensitive (unless indicated
otherwise).
UPDATE existing rows and remove unwanted rows from tables in the
 An SQL command is entered at the SQL prompt, and
DELETE database respectively. They are sometimes collectively subsequent lines are numbered. This is called the SQL buffer.
known as DML commands.  Only one statement can be current at any time within the
buffer, and it can be run in a number of ways:
CREATE These three commands are used dynamically to set up
 Place a semi-colon (;) at the end of last clause .
ALTER change and remove any data structure for example, tables,  Place a semi-colon/forward slash on the last line in the
DROP views, indexes. They are sometimes collectively known as buffer.
DDL commands.  Place a forward slash at the SQL prompt.
 Issue a R(UN) command at the SQL prompt
GRANT These two commands are used to give or remove access
REVOKE rights to both the database and the structures within it.

 Any one of the following statements is valid:  Syntax:

√ SELECT * FROM EMPLOYEE;

CREATE DATABASE database-name;
√ SELECT
*
FROM  Example:
EMPLOYEE;

√ SELECT * CREATE DATABASE LIBRARY;

FROM EMPLOYEE;

 In mysql prompt type:  Create a database.

 Database name: dbEnrollment.
mysql> create database Library;  Change database to dbEnrollment by typing:

mysql> Use dbEnrollment;

Note: It should display Query OK, 1 row
affected as a result.

2
 12/8/2015

 Identify the table name  Syntax:

 Identify the column names CREATE TABLE table-name
 Identify whether the attributes are NULL or (column-name type(size),
column-name type(size),
NOT NULL column-name type(size),
…);
 Identify the attributes’ data types
 Identify the length of the attributes  Example:
 Identify keys
CREATE TABLE DEPT
(deptno int(4) not null auto_increment,
dname char(12) not null,
loc char(12),
primary key (deptno));

 Using the mysql command prompt:  Construct a table “tblEnrollment” with the
mysql > Create Table DEPT ( following data schema
- > deptno int(4) not null auto_increment, Field Type Description
- > dname char(12) not null, EnrollmentNo int Auto increment, Primary key
- > loc char(12)); Edate Date
- > PRIMARY KEY (deptno)); IDNo int Not null
Tuition int
Term Int
Misc Char(3)
SYFrom Int (4)
SYTo Int (4)

Removing an Index (DROPINDEX) Database Definition

Modifying tables:
Syntax:
ALTER TABLE AUTHORS
ADD COLUMN phone char(10);
DROP INDEX index-name;
AUTHOR idno fname lname address phone
Example:
S 95023 Milan Milenkovic NY +
95924 C. J. Date California
95025 Ramez Elmasri Texas

DROP INDEX idno_ind; ALTER TABLE BOOKS

Or ALTER COLUMN bookno char(13);
DROP INDEX bookno_ind; ALTER TABLE TEMPTABLE
DROP COLUMN uselesscolumn;

3
 12/8/2015

Data Manipulation: Update Operations Data Manipulation: Update Operations

Inserting new rows into tables: Inserting new rows into tables:
 Single-row insert:

• Multiple Row Insert:

INSERT INTO BOOKS (bookno, title, year)
VALUES (‘0-201-55822-X’,
INSERT INTO BOOKS (bookno, title, year,publication)
‘The SQL Standard’, 1993);
SELECT *
 Insert a new record into the table supplying data for all columns: FROM TEMPBOOKTABLE
WHERE year >= 1980;
INSERT INTO BOOKS
VALUES (‘0-202-33333-Y’, ‘C++ Programming’,
1998, ‘New York’);

Data Manipulation: Update Operations Data Manipulation: Update Operations

Modifying rows: Modifying rows:
STAFF(sno, fname, lname, address, tel_no, position, sex,
DOB, salary, TIN) • UPDATE multiple columns:
• UPDATE all rows
UPDATE STAFF UPDATE STAFF
SET salary = salary * 1.03; SET position=‘Manager’,
• UPDATE specific rows
salary=18,000
UPDATE STAFF WHERE sno=‘SG01’;
SET salary = salary * 1.03
WHERE position = ‘Manager’;

Data Manipulation: Update Operations

Deleting rows: SELECT column_expression [AS Alias]
FROM table name
• DELETE specific rows: [WHERE condition]
[GROUP BY column_list] [HAVING condition]
DELETE FROM BOOKS [ORDER BY column_list];
WHERE year < 1940;

• DELETE all rows:

DELETE FROM BOOKS;

4
 12/8/2015

mysql > select * from tblemployee;

Rows to return of MySQL after execution:

EMPNO ENAME JOB MGR HIREDATE SAL COM DEPTNO

mysql > Select * (Date) (Int)
(Int) (Text) (Text) (Int) (Double) (Double)
- > from <tablename>;
1000 Dizon Manager 1002 11/11/87 5000 0 10

1002 Blake Analyst 1003 09/08/78 9000 1400 30

1003 Reyes Clerk 2000 02/03/98 2000 0 10

1004 Santos Salesma 1000 02/09/99 2500 300 12

n
1005 Miller Clerk 1002 11/12/89 1400 6 20

5 rows in set

mysql > select empno, ename, job, mgr, hiredate, deptno

- > from tblemployee;
Rows to return of MySQL after execution:

EMPNO ENAME JOB MGR HIREDATE DEPTNO

mysql > Select <column1, column2…> (Int) (Text) (Text) (Int) (Date) (Int)

- > from <tablename>;

1000 Dizon Manager 1002 11/11/87 10

1002 Blake Analyst 1003 09/08/78 30

1003 Reyes Clerk 2000 02/03/98 10

1004 Santos Salesma 1000 02/09/99 12

n
1005 Miller Clerk 1002 11/12/89 20
Try to display all records of tblEmployee ONLY hide SAL and COM columns.

5 rows in set

To Change Column Captions or

Headings
mysql > SELECT column_caption =
- > column_name [,column_name…] mysql > select “salary” = SAL, “commission” - > =
- > FROM <tablename>; COM
- > FROM tblemployee;

5
 12/8/2015

Guidelines in creating queries with a condition: Operator Description

= Equal to
1. Identify conditions to be applied.
> Greater than
2. Determine comparison operators to use.
< Less than
3. Write a the statement with the WHERE clause.
>= Greater than or equal to
<= Less than or equal to
<> Not equal to
!= Not equal to

!> Not greater than

!< Not less than
() Controls precedence

mysql > SELECT <column_expression>

- > FROM <tablename>
- > WHERE <condition1 [, condition2,…]>; Try to display employees
earning more than 2000

mysql > SELECT *

- > FROM tblemployee
- > WHERE SAL > 2000;
mysql > SELECT <column_expression>
EMPNO ENAME JOB MGR HIREDATE SAL COM DEPTNO
(Int) (Text) (Text) (Int) (Date) (Double) (Double) (Int) - > FROM <tablename>
- > WHERE NOT condition {AND|OR}
1000 Dizon Manager 1002 11/11/87 5000 0 10 [NOT] condition;
1002 Blake Analyst 1003 09/08/78 9000 1400 30

1004 Santos Salesman 1000 02/09/99 2500 300 12

3 rows in set

6
 12/8/2015

Operator Description

Or Either condition must be

Try to display employees earning
satisfied more than 2000 working for
DeptNo. 10
And ALL conditions must be
satisfied
Not Negates the expressions
that follows it

mysql > SELECT *

- > FROM tblemployee
- > WHERE SAL > 2000 AND DEPTNO = 10;
mysql > SELECT <column_expression>
EMPNO ENAME JOB MGR HIREDATE SAL COM DEPTNO
(Int) (Text) (Text) (Int) (Date) (Double) (Double) (Int) - > FROM <tablename>
- > WHERE column_name IS [NOT] NULL;
1000 Dizon Manager 1002 11/11/87 5000 0 10

1 row in set

mysql > SELECT <column_expression>

- > FROM <tablename>
Try to display employees with - > [ORDER BY column_name
unassigned department. [ASC|DESC]..];

7
 12/8/2015

EMPNO ENAME JOB MGR HIREDATE SAL COM DEPTNO

(Date) (Int)
(Int) (Text) (Text) (Int) (Double) (Double)

Try to arrange employees based

on hiring date starting from the 1002 Blake Analyst 1003 09/08/78 9000 1400 30

newly hired. 1000 Dizon Manager 1002 11/11/87 5000 0 10

1005 Miller Clerk 1002 11/12/89 1400 6 20

1003 Reyes Clerk 2000 02/03/98 2000 0 10

1004 Santos Salesman 1000 02/09/99 2500 300 12

Function Name Description mysql > SELECT

AVG Average of values - > AggregateFunction(column_name)
- > [,AggregateFunction(column_name) …]
COUNT Number of selected - > FROM <tablename>;
rows
MAX Highest value in the
expression
MIN Lowest value in the
expression
SUM Total of values in the
expression

Count (*)

Try to count the total number of 2

employees working for MGR 1 row set

1002.

8
 12/8/2015

mysql > SELECT

- > AggregateFunction(column_name) Try to count the number of
- > [,AggregateFunction(column_name) …] employees working for the most
- > FROM <tablename>
sought after position – the
- > [GROUP BY [ALL] aggregate_free
- > expression [, aggregate_free-
manager.
- > expression …]]
- > [HAVING search_condition];

Wildcard Description

Count (*)
% Any string of zero or more
1 characters
1 row set
_ (underscore) Any single character

[] Any single character within the

specified range or set

[^] Any single character not within

the specified range or set

mysql > SELECT column_expression

- > FROM tablename
- > WHERE conditions Try to display employee records
- > LIKE values_to_match;
having names starting with “D”.

9
 12/8/2015

mysql > SELECT *

- > FROM tblEmployee
- > WHERE EName
- > LIKE “D%”;  It formats data to fit specific requirements.

EMPNO ENAME JOB MGR HIREDATE SAL COM DEPTNO

1000 Dizon Manager 1002 11/11/87 5000 0 10

1 row in set

Function Expressions
CHAR (char_expression)

LEFT (char_expression, integer_expression)

LEFT (char_expression)

LEN (char_expression)
Try to display employee names in ALL CAPS.
LOWER (char_expression)

LTRIM (char_expression)

RIGHT (char_expression, integer_expression)

RTRIM (char_expression)

STR (float_expression, [length, [decimal]])

UPPER (char_expression)

SUBSTRING (expression, start, length)

mysql > SELECT UPPER(Ename)

- > FROM tblEmployee;

Function Syntax
ENAME
Adddate ADDDATE(date,INTERVAL expr type)
DIZON

BLAKE Addtime ADDTIME(expr,expr2)

REYES
Date_add DATE_ADD(date,INTERVAL expr type)
SANTOS
Date_sub DATE_SUB(date,INTERVAL expr type)
MILLER

5 rows in set
Datediff DATEDIFF(expr,expr2)

10
 12/8/2015

mysql > SELECT name, adddate (hiredate, 330) mysql > SELECT DISTINCT column_name
- > FROM tblEmployee - > FROM table_name;
- > WHERE JOB = “Clerk”;

mysql > SELECT DISTINCT JOB

- > FROM tblemployee;

JOB

Manager

Try to display unique job description. Analyst

Clerk

Salesman

4 rows in set

 Inner mysql > SELECT column_name

 Outer [,column_name]
◦ Left - > FROM table_name JOIN table_name
◦ Right - > ON table_name.ref_column_name
- > join_operator
- > table_name.ref_column_name;

11
 12/8/2015

mysql > SELECT column_name [,column_name]

- > FROM table_name [LEFT|RIGHT] OUTER JOIN
mysql > SELECT Ename, Description - > table_name

- > FROM tblEmployee JOIN tblDepartment - > ON table_name.ref_column_name

- > join_operator
- > ON tblEmployee.DeptNo =
- > table_name.ref_column_name;
tblDepartment.DeptNo

mysql > SELECT column_name [,column_name]

- > FROM table_name
mysql > SELECT DeptNo, Description - > UNION [ALL]
- > FROM tblEmployee RIGHT OUTER JOIN - > SELECT column_name [,column_name]
tblDepartment - > FROM table_name;
- > ON tblEmployee.DeptNo =
tblDepartment.DeptNo

Try to execute this

statement:
mysql > SELECT deptno  Also called nested select statements or a
- > FROM tblemployee query within a query.
- > UNION
- > SELECT deptno
- > FROM tblDepartment;
mysql > SELECT column_name [,column_name…]
-> FROM table_name
-> WHERE column_name = (SELECT column_name FROM table_name
-> where column_name = <value>);

12
 12/8/2015

mysql > SELECT ename

- > FROM tblEmployee
- > WHERE sal = (SELECT MAX(sal)

- > From tblEmployee);

13