SQL

What is SQL?
 Stands for Standard Query Language.
 Lets you access and manipulate
databases.
 Is an ANSI (American National Standards
Institute) standard.
 Is an open source software.
◦ Open source means “free software”
shared freely to the public.
Facts about MySQL
 First release:
◦ May 23, 1995
 Originally developed by:
◦ Michael Widenius and David Axmark
 Developed by:
◦ MySQLAB
Facts about MySQL
 2008:
◦ Sun Microsystems acquired MySQLAB
including the copyrights for MySQL
 2009:
◦ Oracle corporation acquired the copyrights
for MySQL code base
 Developed in:
◦ C/C++
 License:
◦ GNU/General Public License
SQL capabilities
 Queries against the database can be done
using SQL
 Retrieval of records in a database
 Insertion of records in a database
 Updating of records in a database
 Deletion of records in a database
 Creation of new databases
 Creation of new tables in a database
 Creation of stored procedures in a database
 Creation of views in a database
 Permissions set on tables, procedures, and
views
Data Manipulation Language
(DML)
Is a set of syntax for querying ,
inserting, and updating of data in a
database. DML is the most popular
approach of retrieving information which
includes the following:

 SELECT – extracts data from a database

 UPDATE – updates data in a database
 DELETE – deletes data from a database
 INSERT INTO – inserts new data into a
database
Writing SQL statements
1. SQL statements are not case-
sensitive
2. SQL statements can be on one or
more lines
3. Keywords cannot be abbreviated or
split across lines
4. Clauses are usually placed on
separate lines
5. Indents are used to enhanced
redability
Creating a table

SYNTAX:

CREATE TABLE table_name (column_name type, column_name

type,…);

Example:
CREATE TABLE Employee1 (EmployeeNumber INT, Name
VARCHAR(15),Salary SMALLMONEY, Address VARCHAR(20));
Inserting values to the table

SYNTAX:

INSERT INTO table_name VALUES

(‘char_Attrib_val’,’num_attrib_val’);

Example:
INSERT INTO Employee1 VALUES
(‘1’,’razel’,’10000’,’guihing’);
Extracts data from the
database
SELECT statement

Includes:
◦ SELECT clause – specifies the
columns to be displayed
◦ FROM clause – specifies the table
containing the columns that are to
be listed in the SELECT clause.
SELECT all columns

SYNTAX:

SELECT column_name(s) FROM table_name;

Example:
SELECT User_id, LastName, FirstName, Address,
City FROM Persons;
SELECT specific columns

SYNTAX:

SELECT column_name(s) FROM table_name;

Example:
SELECT User_id, LastName, FirstName FROM
Persons;

SELECT FirstName, Lastname FROM Persons;

SELECT DISTINCT statement

DISTINCT keyword can be used to return

only distinct (different) values

SYNTAX:

SELECT DISTINCT column_name(s) FROM

table_name;

Example:
SELECT DISTINCT city FROM Persons;
How to use arithmetic operators

Operators: + , - , * , /
Example:
SELECT LastName, FirstName,Salary, Salary + 300 FROM
Employees;

◦ Operator precedence:
 Multiplication and division occur before addition and
subtraction
 Operators of the same priority are evaluated from left to right
 Parenthesis are used to override the default precedence or to
clarify the statement

Example:
SELECT LastName, Salary, 12*Salary + 300 FROM
Employees;
How to use arithmetic operators

Using parenthesis

Example:

SELECT LastName, Salary, 12* (Salary + 300)

FROM Employees;
How to define column ALIAS

Column Alias:
 Renames a column heading
 Is useful with calculations
 Immediately follows the column name

SYNTAX:

SELECT column_name(s) AS column_alias

FROM table_name;

Example:
SELECT User_id, Salary AS
Annual_Compensation FROM Employees;
Sorting and restricting Data
how to limit rows:
 If you want to display records that fulfill a specified condition,
WHERE clause can be used.
 Column name
 Comparison condition
 Colum name, constant, or list values

SYNTAX:

SELECT column_name(s) FROM table_name WHERE

colum_name
operator value ;

Example:
SELECT E_id, Lastname FROM Employees WHERE E_id = 2;
Sorting and restricting Data

For character strings:

 Single quotation marks ( ‘ ‘ )are used to
enclose character strings and date values
 Character values are case-sensitive and date
values are format-sensitive

Example:
SELECT E_id, Lastname FROM Employees WHERE E_id
= 2;

SELECT E_id, Lastname FROM Employees WHERE city

= ‘Manila’;
Sorting and restricting Data

how to use comparison conditions:

 Comparison condition are used in conditions
that compare one expression to another or
expression.

SYNTAX:

SELECT column_name(s) FROM table_name WHERE

colum_name
operator value ;

Example:
SELECT E_id, Lastname FROM Employees WHERE
salary >= 3000;
Sorting and restricting Data

how to use the BETWEEN condition:

 Comparison condition are used in
conditions that compare one expression
to another or expression.

Example:
SELECT E_id, Lastname FROM Employees
WHERE salary BETWEEN 2000 AND 3000;

SELECT E_id, Lastname FROM Employees

WHERE lastname BETWEEN ‘Fernandez’ AND
‘Hernandez’;
Sorting and restricting Data

how to use the IN condition:

Example:

SELECT E_id, Lastname FROM Employees

WHERE E_id IN (100, 101, 102);
Sorting and restricting Data

how to use the LIKE condition:

 Use like condition to perform wildcard searches or
patterns of valid search string values. Search
condition can contain either literal characters or
numbers
 % denotes zero or many characters
 _ denotes one character

Example:

SELECT E_id, Lastname FROM Employees

WHERE Lastname LIKE ‘F%’;
Sorting and restricting Data

LOGICAL condition:
 AND – returns true if both component
conditions are true

 OR – returns true id either component

conditions is true

 NOT – returns true if the following

condition is false
Sorting and restricting Data

how to use the AND operator:

Example:

SELECT E_id, Lastname FROM Employees WHERE

Lastname LIKE ‘Fernandez’ AND Firstname LIKE
‘Maribeth’ ;
Sorting and restricting Data

how to use the OR operator:

Example:

SELECT E_id, Lastname FROM Employees WHERE

salary >=10000 OR Lastname LIKE ‘%nan%’ ;
Sorting and restricting Data

how to use the NOT operator:

Example:

SELECT E_id, Lastname FROM Employees

WHERE dep_id NOT in (100, 201);
Sorting and restricting Data

how to use the ORDER BY clause:

 ASC: ascending order, default
 DESC: descending order

Example:

SELECT Lastname FROM Employees ORDER BY E_id

ASC;

SELECT Lastname FROM Employees ORDER BY E_id

DESC;.
Sorting and restricting Data

how to sort by COLUMN alias:

Example:

SELECT User_id, Salary AS Annual

Compensation FROM Employees ORDER BY
Annual Compensation;
Sorting and restricting Data

how to sort by MULTIPLE COLUMNS:

Example:

SELECT User_id, FirstName, Salary FROM

Employees ORDER BY User_id,Salary DESC;