0% found this document useful (0 votes)
5 views

SQL Lesson 3 tutorial

Uploaded by

sisayassefaw123
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
0% found this document useful (0 votes)
5 views

SQL Lesson 3 tutorial

Uploaded by

sisayassefaw123
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
You are on page 1/ 21

Structural Query Language (SQL)

Lesson 3

WLDU , DEPARTMENT OF INFORMATION TECHNOLOGY 1


Lesson Topics

1. SQL WHERE Clause


2. SQL UPDATE Statement
3. SQL DELETE Statement

WLDU , DEPARTMENT OF INFORMATION TECHNOLOGY 2


SQL WHERE Clause
 The SQL WHERE clause is used to specify a condition while
fetching the data from single table or joining with multiple
tables.
 If the given condition is satisfied then only it returns specific
value from the table.
 You would use WHERE clause to filter the records and fetching
only necessary records.
 The WHERE clause not only used in SELECT statement, but it
is also used in UPDATE, DELETE statement.

WLDU , DEPARTMENT OF INFORMATION TECHNOLOGY 3


WHERE…
Syntax:
The basic syntax of SELECT statement with WHERE clause is as
follows:
SELECT column1, column2, columnN
FROM table_name
WHERE [condition]

You can specify a condition using comparison or logical operators like


>, <, =, LIKE, NOT etc.

WLDU , DEPARTMENT OF INFORMATION TECHNOLOGY 4


WHERE…
Example: STUDENT relation:

Q1: Retrieve all female students from student relation.


SELECT *
FROM STUDENT
WHERE Stud_Sex='F';

WLDU , DEPARTMENT OF INFORMATION TECHNOLOGY 5


WHERE…
Q2: Retrieve all students whose age is above 25 years old from
student relation.
SELECT *
FROM STUDENT
WHERE stud_Age > 25;
Q3: Fetch students ID, First name, Sex and GPA fields from Student
table for a student with GPA of 3.50.
SELECT Stud_ID,Stud_Fname,stud_Sex,Stud_GPA
FROM STUDENT
WHERE stud_GPA =3.50;
WLDU , DEPARTMENT OF INFORMATION TECHNOLOGY 6
SQL AND and OR clause
The SQL AND and OR operators are used to
compile multiple conditions to narrow data in an
SQL statement.
These two operators are called conjunctive
operators.
These operators provide a means to make
multiple comparisons with different operators in
the same SQL statement.

WLDU , DEPARTMENT OF INFORMATION TECHNOLOGY 7


The AND Operator:
The AND operator allows the existence of multiple conditions in an SQL statement's
WHERE clause.

Syntax:
The basic syntax of AND operator with WHERE clause is as follows:
◦ SELECT column1, column2, columnN
◦ FROM table_name
◦ WHERE [condition1] AND [condition2]...AND [conditionN];

You can combine N number of conditions using AND operator.


For an action to be taken by the SQL statement, whether it be a transaction or query, all
conditions separated by the AND must be TRUE.

WLDU , DEPARTMENT OF INFORMATION TECHNOLOGY 8


And…
Example:
Q1: Retrieve all students from student Relation where students age is less than 27
and whose GPA is above 3.00.
SELECT *
FROM STUDENT
WHERE stud_Age < 27 AND Stud_GPA >3.00;

WLDU , DEPARTMENT OF INFORMATION TECHNOLOGY 9


The OR Operator:
The OR operator is used to combine multiple conditions in an SQL statement's
WHERE clause.

Syntax:
The basic syntax of OR operator with WHERE clause is as follows:
SELECT column1, column2, columnN
FROM table_name
WHERE [condition1] OR [condition2]...OR [conditionN]

You can combine N number of conditions using OR operator.


For an action to be taken by the SQL statement, whether it be a transaction or query,
only any ONE of the conditions separated by the OR must be TRUE.

WLDU , DEPARTMENT OF INFORMATION TECHNOLOGY 10


or
Example:
Q1: Retrieve all students from student Relation where students age is less than 27 or
whose GPA is above 3.00.
SELECT *
FROM STUDENT
WHERE stud_Age < 27 OR Stud_GPA >3.00;

WLDU , DEPARTMENT OF INFORMATION TECHNOLOGY 11


SQL Update query
The SQL UPDATE Query is used to modify the existing records in a table.
You can use WHERE clause with UPDATE query to update selected rows otherwise
all the rows would be effected.

Syntax:
The basic syntax of UPDATE query with WHERE clause is as follows:
UPDATE table_name
SET column1 = value1, column2 = value2...., columnN = valueN
WHERE [condition];

You can combine N number of conditions using AND or OR operators.

WLDU , DEPARTMENT OF INFORMATION TECHNOLOGY 12


Update…
An additional SET-clause specifies the attributes to be modified
and their new values.
Example:
Change the age of the student whose ID is 104 to 30.
UPDATE Student
SET Stud_Age=30
WHERE stud_ID =104;
Change the First name and GPA of Student ID 102 to ‘Kebede' and 3.00, respectively.
UPDATE Student
SET Stud_Fname =‘Kebede’ ,Stud_GPA = 3.00
WHERE stud_ID =102;

WLDU , DEPARTMENT OF INFORMATION TECHNOLOGY 13


Update…
If you want to modify all GPA column values in STUDENT table, you do not need to
use WHERE clause.
i.e. :
UPDATE STUDENT
SET stud_GPA =3.85;

WLDU , DEPARTMENT OF INFORMATION TECHNOLOGY 14


SQL Delete Query
The SQL DELETE Query is used to delete the existing records from a table.
You can use WHERE clause with DELETE query to delete selected rows, otherwise all
the records would be deleted.
Syntax:
The basic syntax of DELETE query with WHERE clause is as follows:
DELETE FROM table_name
WHERE [condition];
You can combine N number of conditions using AND or OR operators.

WLDU , DEPARTMENT OF INFORMATION TECHNOLOGY 15


Delete…
Example:
Delete a student record of whose ID is 103.
DELETE FROM Student
WHERE stud_ID=103;
Delete a record of a student either whose ID is 102 or whose Sex is Female.
DELETE FROM Student
WHERE stud_ID=102 OR Stud_Sex=‘F’ ;

WLDU , DEPARTMENT OF INFORMATION TECHNOLOGY 16


Exercise
Create the following two tables:
STUDENT Relation:

DEPARTMENT Relation:

WLDU , DEPARTMENT OF INFORMATION TECHNOLOGY 17


Exercise…

Q1:select all computer science department students.


Q2: select all female computer science department students.
Q3: change the department of a student ID 106 to Biology.
Q4: Delete the students record where whose Sex is Male and whose GPA is
Greater than 3.50
Q5: Retrieve all department names which is not assigned to the students.

WLDU , DEPARTMENT OF INFORMATION TECHNOLOGY 18


Exercise…
Solution:
Q1:
select *
from Student
where Student.dept_Id=1;
Q2:
select *
from Student
where Student.dept_Id=1 AND Stud_Sex='F';

Q3:
update Student
set Student.dept_ID=2
where stud_ID=106;

WLDU , DEPARTMENT OF INFORMATION TECHNOLOGY 19


Exercise…
Q4:
DELETE FROM Student
WHERE Stud_Sex='M’ and Stud_GPA > 3.50;

Q5:
SELECT Dept_name
FROM Department, Student
WHERE Department.Dept_ID <> Student.Dept_Id;

WLDU , DEPARTMENT OF INFORMATION TECHNOLOGY 20


END OF THE LESSON

WLDU , DEPARTMENT OF INFORMATION TECHNOLOGY 21

You might also like