SQL

(Standard
Query
Language)
prpared by Tilahun
Unit One:Fundamental concepts
of relational database
This unit is developed to provide you the
necessary information regarding the
following content coverage and topics:
• Data management approaches
• Relational Database management system
• Structured query language

prpared by Tilahun
 Data management approaches
 The way in which computers manage
data has come a long way over the last
few decades. Today’s users take for
granted the many benefits found in a
database system. However, it wasn’t that
long ago that computer relied on a much
less elegant and costly approach to data
management called the file-based
system.

prpared by Tilahun
 1. File based approach
 One way to keep information on a
computer is to store it in permanent files.
A company system has a number of
application programs; each of them is
designed to manipulate data files. These
application programs have been written
at the request of the users in the
organization.
 New applications are added to the system
as the need arises. The system just
described is called the file-based system.
Consider a traditional banking system
that uses the file-based system to
prpared by Tilahun
Disadvantages of the file-based approach
 Using the file-based system to keep

organizational information has a number of

disadvantages. Listed below are five
examples.
1. Data redundancy

Often, within an organization, files and

applications are created by different
programmers from various departments over long
periods of time. This can lead to data redundancy,
a situation that occurs in a database when a field
needs to be updated in more than one table.
prpared by Tilahun
2. Data isolation
 Data isolation is a property that determines when and how
changes made by one operation become visible to other
concurrent users and systems. This issue occurs in a
concurrency situation. This is a problem because: It is difficult
for new applications to retrieve the appropriate data, which
might be stored in various files.
3. Integrity problems
 Problems with data integrity is another disadvantage of using
a file-based system. It refers to the maintenance and
assurance that the data in a database are correct and
consistent.
4.Security problems
 Security can be a problem with a file-based approach because:
 There are constraints regarding accessing privileges.
 Application requirements are added to the system in an ad-
hoc manner so it is difficult to enforce constraints.
5. Concurrency access
 Concurrency is the ability of the database to allow multiple
users access to the same record without adversely affecting
transaction processing. A file-based system must manage or
prpared by Tilahun
prevent, concurrency by the application programs.
 Database Approach

 The difficulties that arise from using the file-based

system have prompted the development of a new
approach in managing large amounts of organizational
information called the database approach.
 Most companies keep track of customer information
by storing it in a database. This data may include
customers, employees, products, orders or anything
else that assists the business with its operations.
What is a database?
 A database is a shared collection of related data used
to support the activities of a particular organization. A
database can be viewed as a repository of data that is
defined once and then accessed by various users.

prpared by Tilahun
 Database Properties

A database has the following properties:

 It is a representation of some aspect of the real
world or a collection of data elements (facts)
representing real world information.
 A database is logical, coherent and internally
consistent.
 A database is designed, built and populated with

data for a specific purpose.

 Each data item is stored in a field. A combination
of fields makes up a table. For example, each field
in an employee table contains data about an
individual employee.
 A database can contain many tables.

prpared by Tilahun
Characteristics and Benefits of a Database

 There are a number of characteristics that

distinguish the database approach from the file-
based system or approach.
 Self-describing nature of a database system
 Insulation between program and data
 Support for multiple views of data
 Sharing of data and multiuser system
 Control of data redundancy
 Data sharing
 Enforcement of integrity constraints
 Restriction of unauthorized access
 Data independence
 Transaction processing
 Backup and recovery facilities
prpared by Tilahun
 Relational Database Management System

 Relational Database Management System (RDBMS)

is a type of Database Management System (DBMS)
based on the relational model developed by E. F.
Codd. It serves as the foundation for SQL and
various modern database systems such as MS SQL
Server, IBM DB2, Oracle, MySQL, PostgreSQL, and
Sybase.
 Purpose of DBMS: DBMS emerged in the late 1960s
to assist in storing and managing data. Initially
designed for mainframes, DBMS popularity
extended to various computing platforms,
including minicomputers, personal computers,
workstations, and specialized servers.
prpared by Tilahun
 Definition of DBMS:
 A DBMS is a collection of programs enabling users to create,
maintain, and control access to databases. Its primary goal is
to provide a convenient and efficient environment for users to
retrieve and store information.
 Abstraction Layer:
 The DBMS abstracts the physical details of database storage,
allowing applications to focus on logical data characteristics. It
organizes and structures data, defining its personality through
a data model.
 Data Model and SQL:
 The data model, determined by a DBMS, dictates its
characteristics and suitability for specific applications. SQL, a
language for relational databases, aligns with the relational
data model where data is organized as tables.
 Schemas, Domains, and Constraints:
 A database's structure is its schema, describing its complete
logical view. Domains represent the possible values an
attribute can assume, and constraints are rules governing
attribute values.
prpared by Tilahun
 Relations and Keys:
 A relational database comprises one or more relations,
essentially tables. Keys, such as primary keys, uniquely
identify records. Foreign keys establish relationships
between tables.
 Primary Keys:
 Primary keys uniquely identify each row and cannot
have duplicate or NULL values. A table typically has
one primary key, which may be a composite key if
multiple fields are involved.
 Foreign Keys:
 Foreign keys link tables, referencing primary keys from
another table. They establish relationships between
tables, contributing to data integrity and consistency.

prpared by Tilahun
 Objectiv
 e
Create the database & relation
structures;
 Perform basic data management
tasks, such as:
 Insertion
 Modification
 Deletion of data
 Perform both simple and complex
queries prpared by Tilahun
SQL
 Transform-oriented language
A language to use relations to
transform input into required
output
 Non-procedural language
 Specify what you require than how to get
it
 Free format
 Command consists English word,
such as:
 CREATE, INSERT, SELECT,
prpared by Tilahun etc
Function of SQL
 Execute queries against a
database
 Retrieve data from a database
 Insert records into a database
 Update records in a database
 Delete records from a database
 Create new databases
 Create new tables in a database
 Set permissions
prpared by Tilahun
Categories
of SQL
 DDL (Data Definition
Language):
 DML (Data Manipulation
Language):
 DCL (Data Control
Language):

prpared by Tilahun
DDL
 DDL and DCL statements are
commonly used by a database
designer and database
administrator for establishing the
database structures.
 The DDL part permits database
tables to be created or deleted.
 It also defines indexes (keys),
specify links between tables, and
impose constraints between tables.
prpared by Tilahun
Most important DDL statements :
 CREATE DATABASE - creates a new
database
 ALTER DATABASE - modifies a database
 CREATE TABLE - creates a new table
 ALTER TABLE - modifies a table
 Adds or removes a column from a table.
 Used in connection with ADD, MODIFY and
DROP.
 DROP TABLE - deletes a table
 Deletes all rows & removes the table from the
database.
 CREATE INDEX - creates an index
prpared by Tilahun
DML
 SELECT - extracts data from a database
 - retrieves rows from a table.
 Specifies which columns to include in the
result set.
 UPDATE - updates data in a database
 Modifies existing rows in a table.
 DELETE - deletes data from a database
 Removes a specified row or a set of rows from
a table.
 INSERT INTO/ insert - inserts new data
into a database
 Adds rows to a table.
prpared by Tilahun
DCL
 GRANT: to allow specified users to
perform specified tasks
 REVOKE: to cancel previously granted
or denied permissions
 DENAY: restricts users not to perform
tasks
 The operations for which
privileges may be granted to or
revoked from a user or role may
include CONNECT, SELECT, INSERT,
UPDATE, DELETE, EXECUTE, and USAGE
prpared by Tilahun
 SQL key words
ADA DEC GRANT NUMERIC
ALL DECIMAL GROUP OF
AND DECLARE HAVING ON
ANY DEFUALT IN OPEN
AS DELETE INDICATOR OPTION
ASC DESC INSERT OPEN
AUTHORIZATION DISTNICT INT OPTION
AVG DOUBLE INTEGER OR
BEGIN END INTO ORDER
BETWEEN ESCAPE IS PRIMARY
BY EXEC KEY REAL
C EXISTS LANGAUGE SELECT
CHAR FETCH LIKE SET
CHARACTER FLOAT MAX SOME
CHECK FOR MIN SUM
CLOSE FOREIGN MODULE TABLE
COBOL FORTRAN NOT TO UNION
COMMIT FOUND NULL UPDATE
CONTINUE FROM USER
COUNT GO VIEW
CREATE GOTO WHERE
CURRENT WITH
CURSOR WORK

prpared by Tilahun
 Name
 To identify database object
on which the statement
should act
 Tablename
 Column name

prpared by Tilahun
 Data Types
 Character strings:
Data type Description
Fixed-length character string. Maximum 8,000 characters
char(n) or
character(n)

varchar(n) Variable-length character string. Maximum 8,000 characters

varchar(max) Variable-length character string. Maximum 1,073,741,824
characters
text Variable-length character string. Maximum 2GB of text data

prpared by Tilahun
 Number types:
Data type Description Storag
e
tinyint Allows whole numbers from 0 to 255 1 byte
smallint Allows whole numbers between -32,768 and 32,767 2 bytes
int Allows whole numbers between -2,147,483,648 and 4 bytes
2,147,483,647
bigint Allows whole numbers between - 8 bytes
9,223,372,036,854,775,808 and
9,223,372,036,854,775,807
decimal(p,s Fixed precision and scale numbers. 5-17
) Allows numbers from -10^38 +1 to 10^38 –1. bytes
numeric(p,s Fixed precision and scale numbers. 5-17
) Allows numbers from -10^38 +1 to 10^38 –1. bytes
smallmone Monetary data from -214,748.3648 to 214,748.3647 4 bytes
y
money Monetary data from -922,337,203,685,477.5808 to 8 bytes
922,337,203,685,477.5807
float(n) Floating precision number data from -1.79E + 308 4 or 8
to 1.79E + 308. bytes
real Floating precisionprpared
number data from -3.40E + 38 to
by Tilahun
4 bytes
Date types:
Data Description Stora
type ge
datetime From January 1, 1753 to December 31, 9999 8 bytes
with an accuracy of 3.33 milliseconds
datetime From January 1, 0001 and December 31, 6-8
2 9999 with an accuracy of 100 nanoseconds bytes
smalldatet From January 1, 1900 to June 6, 2079 with an 4 bytes
ime accuracy of 1 minute
date Store a date only. From January 1, 0001 to 3 bytes
December 31, 9999
time Store a time only to an accuracy of 100 3-5
nanoseconds bytes
datetimeof The same as datetime2 with the addition of a 8-10
fset time zone offset bytes
timestam Stores a unique number that gets updated
p every time a row gets created or modified.
The timestamp value is based upon an
internal clock and does not correspond to real
time. Each tableprpared
maybyhave
Tilahunonly one
Install SQL server
 Identify the hardware &
software requirements
before installation.

prpared by Tilahun
 Requirement for Installing MS
SQL Server DBMS Software
The following system
requirements cover SQL
Server 2012 Standard Edition
on 32-bit and x64 platforms,
as well as Itanium-based
systems.

prpared by Tilahun
 SQL Server 2012 64-bit
Hardware Requirements:
Component Minimum Requirement Recommended Requirement

Processor x64 Processor with at least 1.4 GHz 2.0 GHz or faster, 4 or more
cores

RAM (64-bit) 1 GB for Express, 2 GB for all others 4 GB or more

Hard Disk Space 3.5 GB minimum, 10 GB or more 40 GB or more

(Database Engine) recommended

Hard Disk Space (Analysis 250 MB minimum, 2 GB or more 40 GB or more

Services) recommended

Hard Disk Space (Reporting 3 GB minimum, 10 GB or more 40 GB or more

Services) recommended

Hard Disk Space (Integration 1.5 GB minimum, 2.5 GB or more 40 GB or more

Services) recommended

Hard Disk Space (Full 3.8 GB minimum, 10 GB or more 40 GB or more

Installation) recommended

prpared by Tilahun
SQL Server 2012 32-bit Software Requirements:
 • Operating System: SQL Server 2012 32-bit
supports a variety of 32-bit and 64-bit Windows Server
and Windows client operating systems. The specific
editions of SQL Server 2012 (Enterprise, Standard,
etc.) may have different supported operating systems.
Check the official documentation for exact details.
 • .NET Framework: SQL Server 2012 requires .NET
Framework 3.5 SP1 for installation. The installer will
typically install this for you. You may also need .NET
Framework 4.0 or later for some features.
 • SQL Server 2012 Edition: Make sure you have the
correct edition of SQL Server 2012 (e.g., Enterprise,
Standard, Express) for your needs and licensing.
 • SQL Server 2012 Service Pack: It's advisable to
install the latest service pack or cumulative update for
SQL Server 2012 for bug fixes and enhancements.
prpared by Tilahun
 Working
with SQL
Commands
prpared by Tilahun
 Unit Two:
Data definition language

 The Data Definition Language (DDL) is part of SQL that

you use to create (completely define) a database, modify
its structure, and destroy it when you no longer need it.
 It contains SQL commands you use to create, change, or
destroy the basic elements of a relational database.
prpared by Tilahun
 Table Name
 When you specify a table name in a SQL statement,
SQL assumes that you are referring to one of your
own tables (that is, a table that you created). With
the proper permission, you can also refer to tables
owned by other users using a qualified table name.
Column Name
 When you specify a column name in a SQL
statement, SQL can normally determine from the
context which column you intend. A column name
should not be blank and the same in the same
database.
Data Types
 There is a standard that specifies various types of
data that can be stored in SQL-based database and
manipulated by the SQL languages.
prpared by Tilahun
prpared by Tilahun
Con...
The Create
command:-
Create SQL DB
Create table

prpared by Tilahun
 Create Database
Statement
 Syntax

CREATE DATABASE database_name

 Example: Now we want to create a
database called "my_db".

CREATE DATABASE my_db

prpared by Tilahun
 CREATE TABLE Statement
 Database Tables

A database contains one/more

tables. Each table is identified
by a name (e.g. "Customers"
or "Orders").
Tables contain records/rows
with data.CREATE
(
TABLE table_name

 Syntax column_name1 data_type,

column_name2 data_type,
column_name3 data_type,
....
) prpared by Tilahun
Example
: table
 Create by the name Student with
columns: Id, Name, Gender, section.

CREATE TABLE Student

(
Id int,
Name varchar(25),
Gender char(1),
Section varchar(25)
)

Id Name Gender Section

prpared by Tilahun
 Exercise
 Create database by the name
SQLStudent
 Create the following table using access
procedures.
IDNo StudentDetail
Fname Lname Age Gender
001 Derartu Gudeta 19 F
002 Kebron Solomon 21 M
003 Alemu Ebisa 20 M

 Create the following table using SQL

statement
CseCode CourseDetail
CseName Crdthrs Instractor
IT010 Database 80 Selam
C202 SPSS 90 Hlina
M220 Maintenanc
prpared by90
Tilahun Teshome
 SQL

Constraints
Used to limit the type of data that can go into a
table.
 Can be specified when a table is created (with
the CREATE TABLE statement) or after the table
is created (with the ALTER TABLE statement).
 Major constraints
 NOT NULL
 UNIQUE
 PRIMARY KEY
 FOREIGN KEY
 CHECK
 DEFAULT

prpared by Tilahun
 NOT NULL Constraint
 Enforces a column to NOT accept NULL
values.
 Enforces a field to always contain a
value. TABLE Employee
CREATE
(Id int NOT NULL,
LastName varchar(25) NOT NULL,
FirstName varchar(25),
Address varchar(10),
City varchar(8))
prpared by Tilahun
PRIMARY KEY Constraint
 Primary keys must contain
unique values.
 Can’t contain NULL values.
 Each table should have a
primary key.

prpared by Tilahun
Primary key on create table
CREATE TABLE Employee
(Id int NOT NULL PRIMARY KEY,
LastName varchar(25) NOT NULL,
FirstName varchar(25),
Address varchar(10),
City varchar(8)) CREATE TABLE Employee
(Id int NOT NULL,
LastName varchar(25) NOT NULL,
FirstName varchar(25),
Address varchar(10),
City varchar(8),
CONSTRAINT pk_ID PRIMARY KEY
(Id,LastName))
prpared by Tilahun
Primary key on Alter table
ALTER TABLE Studentdetail ADD PRIMARY KEY (Id)

ALTER TABLE Persons ADD CONSTRAINT pk_ID

PRIMARY KEY (Id, LastName)

prpared by Tilahun
DROP a PRIMARY
KEY Constraint
ALTER TABLE Employee DROP
CONSTRAINT pk_ID

prpared by Tilahun
 Books
SerialNo Title Author Publisher

 Borrower

BID Bname Bdate RDate

prpared by Tilahun
Composite key constraints
CREATE TABLE employee
(
Empno char(5),
fname varchar(15),
lname varchar(15),
salary numeric (8,2),
pno char(2), primary key(Empno, pno)
)

prpared by Tilahun
 Foreign key
constraints
 A FOREIGN KEY in one table points
to a PRIMARY KEY in another table.
 FK is used to prevent actions that
would destroy link between tables.

prpared by Tilahun
Example
"Persons" table:
P_Id LastName FirstName Address City

1 Hansen Ola Timoteivn 10 Sandnes

2 Svendson Tove Borgvn 23 Sandnes

3 Pettersen Kari Storgt 20 Stavanger

"Orders" table:
O_Id OrderNo P_Id
1 77895 3
2 44678 3
3 22456 2
4 24562
prpared by Tilahun 1
 On CREATE table
CREATE TABLE Orders
(O_Id int PRIMARY KEY, OrderNo int NOT NULL,
P_Id int FOREIGN KEY REFERENCES Persons(P_Id))
 On ALTER table
ALTER TABLE Orders
ADD FOREIGN KEY (P_Id) REFERENCES Persons (P_Id)

ALTER TABLE Orders

ADD CONSTRAINT fk_PerOrders FOREIGN KEY
(P_Id)REFERENCES Persons(P_Id)
 To DROP FK

ALTER TABLE Orders

DROP CONSTRAINT fk_PerOrders
prpared by Tilahun
DEFAULT Constraint
 on CREATE TABLE
CREATE TABLE Persons
(P_Id int NOT NULL, LastName varchar(255) NOT NULL,
FirstName varchar(255), Address varchar(255), City
varchar(255) DEFAULT 'Sandnes')

 on ALTER TABLE
ALTER TABLE Persons
ALTER COLUMN City SET DEFAULT 'SANDNES'

prpared by Tilahun
Example
ID FName Gender
Edu01 Weinshet F
Edu02 Tadesse M
Edu03 Beti F
Edu04 Truwork F

CREATE table studinfo (ID char(5)

primary key, Fname varchar(15) not null,
Gender char(1) DEFAULT ‘F’)
OR
ALTER TABLE studinfo
ALTER COLUMN Gender SET DEFAULT ‘F’
prpared by Tilahun
 Check constraints
On create table
CREATE TABLE employee3
(
empno char(5) primary key,
fname varchar(15),
lname varchar(15),
salary numeric(8,2)
constraint sal_chk check(salary between 200 and 5000)
)

On alter
table
ALTER TABLE employee3
between 200 and 5000)
ADD CONSTRAINT sal_chk CHECK (salary

prpared by Tilahun
Insert Data Only in Specified
Columns

INSERT INTO StudInfo (ID,

Fname)
VALUES (‘IT005’, ‘Habtamu’)

prpared by Tilahun
 Drop SQL constraints
Alter table customer Alter column Gender
drop default

Alter table customer Alter column MobileNo

drop Unique

Alter table Orders Alter column OrdDate drop

Check

prpared by Tilahun
 SQL SELECT Statement
 Used to select data from a
database
Syntax
SELECT column_name(s)FROM
table_name
SELECT * FROM table_name

prpared by Tilahun
 SQL SELECT DISTINCT
Statement
SELECT DISTINCT column_name(s)FROM
table_name

Example: student, course table

prpared by Tilahun
 WHERE Clause
Used to filter records or to
extract only those records that
fulfill a specified criterion.
Syntax
SELECT column_name(s)
FROM table_name
WHERE column_name
operator value
prpared by Tilahun
 Examples
 Select * FROM StudInfo WHERE
Gender=‘F’
 SELECT * FROM StudInfo
WHERE Fname ‘T%’
 SELECT EmpID, EmpName
WHERE Salary > 4000

prpared by Tilahun
 Operators in WHERE
Clause
Operator Description
= Equal
<> Not equal
> Greater than
< Less than
>= Greater than or equal
<= Less than or equal
BETWEE
Between an inclusive range
N
LIKE Search for a pattern
IN If you know the exact value you want to
return for at least one of the columns
prpared by Tilahun
 AND Operator
 SELECT * FROM Persons WHERE
FirstName='Tove‘ AND
LastName='Svendson‘
Example
SELECT *
FROM StudInfo
WHERE ID ‘E%’
AND Gender=‘F’

prpared by Tilahun
 OR Operator
Example
 SELECT * FROM Persons

WHERE FirstName='Tove'OR
FirstName='Ola‘
 SELECT * FROM StudInfo

WHERE Fname=‘B%’
OR Gender=‘M

prpared by Tilahun
 AND & OR Operators
 SELECT * FROM StudInfo
WHERE Gender=‘F’
AND (Fname=‘T%’ OR Fname=‘W%’

prpared by Tilahun
 SQL ORDER BY Keyword
 Used to sort the result-set by
a specified column
 Sort the records in ascending
order by default.
 If you want to sort the
records in a descending
order, you can use the DESC
keyword. prpared by Tilahun
 Syntax
 SELECTcolumn_name(s)
FROM table_name
ORDER BY column_name(s) ASC|
DESC
 Example
 SELECTFname, Gender
FROM StudInfo
ORDER BY Fname ASC/DESC
prpared by Tilahun
 SQL UPDATE Statement
 Used to update existing records in a
table
Syntax
 UPDATE table_name

SET column1=value, column2=value2,...

WHERE some_column=some_value

prpared by Tilahun
 Example
UPDATEStudInfo
SET Fname=‘Asnake’,
Gender=‘M’
WHERE ID= ‘Edu04’

prpared by Tilahun
 SQL DELETE Statement
 Used to delete records/ rows in a table.
 Syntax
 DELETE FROM table_name
WHERE some_column=some_value
 Example
 DELETE FROM Project
WHERE PjtNo=N08

prpared by Tilahun
 Delete All Rows
DELETE FROM
table_name or
DELETE * FROM
table_name

prpared by Tilahun
SQL ALTER TABLE Statement
 The ALTER TABLE statement is used to
add, delete, or modify columns in an
existing table.

prpared by Tilahun
SQL ALTER TABLE Syntax
To add a column in a table, use the following syntax:

ALTER TABLE table_name ADD column_name

Example
datatype

alter table student add section char (8)

prpared by Tilahun
 To change the data type of a column in a table,
use the following syntax:

ALTER TABLE table_nameALTER COLUMN

column_name data type

 Example
 Alter table student alter column age int

prpared by Tilahun
To delete a column in a table, use the following syntax
(notice that some database systems don't allow deleting a column):

ALTER TABLE table_name DROP COLUMN

column_name

Example:
ALTER TABLE student drop COLUMN Gender

prpared by Tilahun
AUTO INCREMENT Field
 Auto-increment allows a unique number
to be generated when a new record is
inserted into a table.

CREATE TABLE Studinfo (P_Id int PRIMARY KEY

IDENTITY,
LastName varchar(25) NOT NULL,
FirstName varchar(25),
Levels char(15))

prpared by Tilahun
Unit Three:
Data manipulation
language

prpared by Tilahun
 SELECT - extracts data from a database
 - retrieves rows from a table.
 Specifies which columns to include in the
result set.
 UPDATE - updates data in a database
 Modifies existing rows in a table.
 DELETE - deletes data from a database
 Removes a specified row or a set of rows
from a table.
 INSERT INTO/ insert - inserts new data into
a database
 Adds rows to a table.

prpared by Tilahun
 SELECT QUERY
 Assume we have a table called Employees with the
following columns:
• EmployeeID (Primary Key)
• FirstName
• LastName
• Department
• Salary
 This query selects all columns from the
Employees table
SELECT EmployeeID, FirstName,
LastName, Department, Salary FROM
Employees;
Or
SELECT * FROM Employees;prpared by Tilahun
 Filter rows using a condition
(WHERE clause):
SELECT FirstName, LastName, Department
FROM Employees
WHERE Department = 'IT';
 Display Employees with Salary
Greater than 50000
SELECT EmployeeID, FirstName,
LastName, Department, Salary
FROM Employees
WHERE Salary > 50000;
prpared by Tilahun
 UPDATE Query Example
 Let's assume you want to update the salary of
a specific employee, say John Doe, by
increasing it to 65000.
##Update the salary of an employee with EmployeeID =
1
UPDATE Employees
SET Salary = 65000
WHERE EmployeeID = 1;
You can update multiple columns at once, like so:
UPDATE Employees SET Salary = 65000,
Department = 'Engineering'
WHERE EmployeeID = 1;
prpared by Tilahun
 DELETE Query Example
##Delete the employee with EmployeeID =
1 DELETE FROM Employees
WHERE EmployeeID = 1;
## Deletes all rows from the Employees
table (use with caution)

DELETE FROM Employees;

prpared by Tilahun
 INSERT INTO Query
Example
INSERT INTO Employees (FirstName,
LastName, Department, Salary)
VALUES (‘john', ‘doe', ‘IT', 55000);

prpared by Tilahun
 Unit Four:
Data query language
 This unit is developed to provide you the
necessary information regarding the following
content
 coverage and topics:
• Selection of data from a single table
• Selection of data from multiple tables
• Retrieval of data selectively
• Working with functions
• Working with sub-queries
prpared by Tilahun
 Tables name Student, Course, and
Department.
 example:
 Student: Contains information about
students (e.g., StudentID, FirstName,
LastName, DepartmentID).
 Course: Contains information about courses
(e.g., CourseID, CourseName,
DepartmentID).
 Department: Contains information about
departments (e.g., DepartmentID,
DepartmentName).
prpared by Tilahun
 1. Selection of Data from a
Single Table
To select all student data from the
Student table:
SELECT * FROM Student;
To select specific columns (e.g.,
FirstName and LastName) from
the Student table:
SELECT FirstName, LastName
FROM Student;
prpared by Tilahun
2. Selection of Data from Multiple Tables

 To join data from the Student and

Department tables (using DepartmentID as
the linking field):
SELECT s.StudentID, s.FirstName, s.LastName,
d.DepartmentName
FROM Student s
JOIN Department d
ON s.DepartmentID = d.DepartmentID;

prpared by Tilahun
 3. Retrieval of Data Selectively
 To retrieve students from the Student table where the
DepartmentID is 1 (for example, students in the IT
department):
SELECT StudentID, FirstName, LastName
FROM Student
WHERE DepartmentID = 1;
 You can also use comparison operators:
SELECT FirstName, LastName
FROM Student
WHERE StudentID > 10;
Or combine conditions using AND, OR:
SELECT FirstName, LastName
FROM Student
WHERE DepartmentID =prpared
1 AND StudentID > 5;
by Tilahun
4. Working with Functions
 SQL Server supports various
functions, such as aggregate
functions (COUNT, SUM, AVG, MAX,
MIN) and string manipulation
functions.
 For example, to count how many

students are in the database:

SELECT COUNT(*) AS StudentCount
FROM Student;
prpared by Tilahun
To calculate the average number
of students per department:
SELECT d.DepartmentName,
COUNT(s.StudentID) AS
TotalStudents
FROM Department d
JOIN Student s
ON d.DepartmentID =
s.DepartmentID
GROUP BY d.DepartmentName;
prpared by Tilahun
 5. Working with Sub-
Queries
 A sub-query is a query within a query.
Let’s say you want to retrieve the names
of students who are in the same
department as student with StudentID =
5. You can do this using a sub-query:
SELECT FirstName, LastName
FROM Student
WHERE DepartmentID = (SELECT
DepartmentID FROM Student WHERE
StudentID = 5);
prpared by Tilahun
 Another example: Find students
who are enrolled in the same
courses as StudentID = 3:

SELECT FirstName, LastName

FROM Student
WHERE StudentID IN (SELECT StudentID
FROM Course WHERE CourseID IN
(SELECT CourseID FROM
Course WHERE StudentID = 3));

prpared by Tilahun
End
prpared by Tilahun