DATABASE ESSENTIALS
Lecture 7
[email protected]
INTRODUCTION TO SQL
• SQL is the standard relational database language.
• SQL is a standard language for storing, manipulating and retrieving
data in databases.
• Parts of SQL language
• Data Definition Language (DDL)
• Data Manipulation Language (DML)
• Embedded SQL and Dynamic SQL
INTRODUCTION TO SQL…
Data Definition Language (DDL)
• DDL provide commands for defining the relations, deleting the
relations and
modifying the existing relation schemas.
• Types of DDL
• View Definition Language (VDL)
• The DDL that provide commands for defining and dropping the views.
• Integrity
• The DDL that provide commands for specifying integrity constraints that
must be satisfied by the data stored in the database.
• Authorization
• The DDL provide commands for specifying access rights to the relations and
views.
INTRODUCTION TO SQL…
Data Manipulation Language (DML)
• DML provides the ability to query information from the database and
to insert tuples into, delete tuples from, and modify tuples in the
database.
Embedded SQL and Dynamic SQL
• Embedded SQL defines how SQL statements can be embedded
within general purpose programming languages like C, C++, Java
etc.
• The language in which SQL queries are embedded is referred to as a
host language.
• Dynamic SQL allows programs to construct and submit SQL queries at
run time.
DATA DEFINITION…
Some SQL data types are:-
• char(n): A fixed-length character string with user-specified length n.
• varchar(n): A variable-length character string with user-specified
maximum length n.
• int: An integer.
• smallint: A small integer, a subset of integer type.
DATA DEFINITION…
Some SQL data types are:-
• numeric(p, d): A fixed-point number with user-specified precision.
The number consists of p digits (plus a sign), and d is the number of digits after
the decimal point.
Example, numeric(3,1) allows 44.5 to be stored exactly. Neither 444.5 nor
0.32 can be stored exactly in a field of this type.
• date: It is used to store date and time values.
DATA DEFINITION…
Schema Definition
• An SQL database is defined by using the create database
command.
• Command: CREATE DATABASE database;
• Example: CREATE DATABASE college;
DATA DEFINITION…
Schema Definition
• An SQL relation is defined by using the create table command.
• Example: Create a relation department in the database.
CREATE TABLE department
(dept_name varchar (20),
building varchar (15),
budget numeric (12,2),
primary key (dept_name));
• To remove a relation from an SQL database, use the drop table
command.
• Example: DROP TABLE department;
DATA DEFINITION…
• Tuples are deleted from a relation using a delete command.
• Example:
DELETE FROM department;
This command delete all tuples from the department relation.
DATA MANIPULATION…
• Data is loaded into a relation using the insert command.
• Example:
INSERT INTO department VALUES(‘ICT’, ‘Block 1’,20000.00);
• The values are specified in the order in which the corresponding
attributes are
listed in the relation schema.
BASIC STRUCTURE OF SQL QUERIES
• The basic structure of an SQL query consists of three clauses: select,
from, and where.
• The select clause is used to list the attributes desired in the result of a
query.
• The from clause is a list of the relations to be accessed in the
evaluation of the query.
• The where clause is a predicate involving attributes of the relation in
the from clause.
BASIC STRUCTURE OF SQL QUERIES…
• Example:
SELECT name FROM department
• To eliminate duplicates, insert the keyword distinct after select.
• Example:
SELECT DISTINCT first_name FROM student;
BASIC STRUCTURE OF SQL QUERIES…
• The select clause may contain arithmetic expressions involving the
operators +, -, ∗, and / operating on attributes of tuples.
• Example
SELECT ID, name, dept_name, salary * 1.1 FROM instructor;
• The where clause allows the selection of only those rows in the result
relation that satisfy a specified predicate.
• Example:
• “Find the names of all instructors in the ICT department who have salary
greater than Tsh. 3,000,000.”
• The query will be written as:
SELECT name FROM instructor WHERE dept_name = ’ICT’ and salary >
3000000;
BASIC STRUCTURE OF SQL QUERIES…
• Queries on multiple relations
• Example
Consider the relations below
ID name dept_nam salary
dept_nam building budget
e
e
10 James ICT 12000
ICT Block A 1200000
11 Hassan BA 10000
Metrology Met House 1000000
12 Neem ICT 20000
BA Block B 2000000
a
department 13 Brenda ICT 43000
14 Salma Metrology 50000
instructor
To select the instructor name, department name and the building, the query is as follows:-
SELECT name, instructor.dept_name, building FROM instructor, department WHERE
instructor.dept_name= department.dept_name;
“If you are working on
something that you really care
about, you don’t have to be
pushed. The vision pulls you.”
– Steve Jobs
