Transcript Document

SQL Basics
What is SQL?
• SQL stands for Structured Query
Language .
• SQL lets you access and manipulate
databases .
What Can SQL do?
• Can execute queries against a
database.
• Can retrieve data from a database.
• Can insert records in a database.
• Can update records in a database.
What Can SQL do?
• Can delete records from a database.
• Can create new databases.
• Can create new tables in a database.
SQL Statements
• Most of the actions you need to
perform on a database are done with
SQL statements.
• Ex: select all the records in the "Person" table.
 SELECT * FROM Person
Keep in Mind That...
• SQL is not case sensitive
SQL
SQL
•
•
•
•
DML
Select
Insert
Update
Delete
DDL
• Create
• Alter
• Drop
DCL
• Grant
• Revoke
Transaction control
• Commit
• Rollback
7
Data Definition Language
(DDL)
 Is used to change structure of database
 Create Table
 Drop Table
 Alter Table
8
Create Table
• The Create table statement is used to
create a table in a database and to add
constraints .
– Constraints:
• NOT NULL
• UNIQUE
• PRIMARY KEY
• FOREIGN KEY
• CHECK
• DEFAULT
The data types
• Ex: some popular data types used in creating fields.
Data Type Name
INT
DOUBLE
CHAR(n)
VARCHAR(n)
DATE
TIME
Create Table Example
CREATE TABLE person( id int,
name varchar(50) not null,
email varchar(50),
age int default 18,
Dept_ID int,
primary key(id),
check(age>17),
unique(email),
foreign key (Dept_ID) REFERENCES
Department(Dept_ID))
Drop Table
• Used to remove a relation (base table) and
its definition
• The relation can no longer be used in
queries, updates, or any other commands
since its description no longer exists
• Example:
DROP TABLE
DEPENDENT;
Alter Table
• Used to modify the table design like add a
column, modify a column or delete a
column.
• Examples:
ALTER TABLE EMPLOYEE
VARCHAR(12)
ALTER TABLE EMPLOYEE
JOB
VARCHAR(50)
ALTER TABLE EMPLOYEE
ADD
JOB
MODIFY COLUMN
DROP COLUMN
JOB
Data Manipulation Language
(DML)
 Used to manipulate database data
14
Data Manipulation Language
(DML)
 SELECT
o Extracts data from a database
 INSERT INTO
o Inserts new data into a database
 UPDATE
o Updates data in a database
 DELETE
o Deletes data from a database
15
INSERT INTO Statement
• First form:
It doesn't specify the column names where
the data will be inserted, only their values.
INSERT INTO Employee VALUES (4,'Ahmed',
‘Ali', 112233,
'1965-01-01 ', '15 Ali fahmy St.Giza‘,
‘M’,1300,223344,10)
– The previous form the attribute values should be
listed in the same order as the attributes were
specified in table.
16
INSERT INTO Statement,
(cont.)
• Second form:
It specifies both the column names and the
values to be inserted
INSERT INTO Employee (SSN, Lname, fname)
VALUES (5, ‘Ahmed', ‘Ali')
17
INSERT INTO Statement,
(cont.)
• To insert a new row into table with identity value,
we will not have to specify a value for the identity
column (a unique value will be added
automatically):
insert into Department values('DP2','56733')
insert into Department(Dname,MGRSSN)
values('DP2','56733')
UPDATE Statement
• The UPDATE statement is used to update existing
records in a table.
• SQL Syntax
– UPDATE table_name
SET column1=value, column2=value2,...
WHERE some_column=some_value
• Note: The WHERE clause specifies which record or
records that should be updated. If you omit the
WHERE clause, all records will be updated!
UPDATE Person SET Address='18 Abaas El 3akaad
St. Nasr City.Cairo', City='Cairo' WHERE
Lname=‘Amr' AND Fname='Ali'
DELETE Statement
• The DELETE statement is used to delete
rows in a table.
delete from Employee
where Lname='Brown’
delete from Employee
20
SELECT Statement
• Basic form of the SQL SELECT statement is
called a mapping or a SELECT-FROM-WHERE
block
SELECT
FROM
WHERE
<attribute list>
<table list>
<condition>
– <attribute list> is a list of attribute names whose
values are to be retrieved by the query
– <table list> is a list of the relation names required
to process the query
– <condition> is a conditional (Boolean) expression
that identifies the tuples to be retrieved by the
query
SELECT Statement Examples
SELECT Lname,Fname FROM Person
Or:
SELECT Person.Lname, Person.Fname FROM Person
SELECT * FROM Person WHERE City='Sandnes'
LIKE Operator Example
SELECT * FROM Person WHERE City LIKE
's%'
SELECT * FROM Person WHERE City LIKE
'%s'
Used to represent one character
SELECT * FROM Person WHERE City LIKE
'_andnes'
SQL AND & OR Operators
• AND operator displays records when all
conditions are true.
• OR operator displays records when any condition is
true.
SQL AND & OR Operators
Example
IN Operator Example
• Retrieve data of all employees whose social
security numbers number is 112233, or 512463.
– Query :
SELECT * FROM Employee WHERE SSN IN
(112233,512463)
IN Operator Example
• Retrieve data of all employees whose social
security numbers number is not 112233, or
512463.
– Query :
SELECT * FROM Employee WHERE SSN not IN
(112233,512463)
The BETWEEN Operator
• The BETWEEN operator is used in a WHERE clause
to select a range of data between two values.
• Retrieve the employees with salary between 1000
and 2000
– Query :
SELECT * FROM EMPLOYEE
WHERE SALARY BETWEEN 1000 AND 2000
NULLS IN SQL QUERIES
• SQL allows queries that check if a value is NULL.
• SQL uses IS or IS NOT to compare Nulls.
• Retrieve the names of all employees who do not
have supervisors.
– Query :
SELECT FNAME, LNAME
FROM
EMPLOYEE
WHERE SUPERSSN IS
NULL
DISTINCT Keyword
• The keyword DISTINCT is used to eliminate
duplicate tuples in a query result.
Thank You