Fundamentals of Database system

Lab Practice

Structured Query Language (SQL)

 What is SQL?
 SQL is the standard language used to communicate with a
relational database.
 SQL is the language you use to express your needs to the
database.
 you can request specific information from the database in the
form of a query using SQL.
 SQL is an ANSI standard.
 ANSI, stands for American National Standards Institute, is an
organization that is responsible for devising standards for various
products and concepts.

2
 What Can SQL do?
 SQL can execute queries against a database
 SQL can retrieve data from a database
 SQL can insert records in a database
 SQL can update records in a database
 SQL can delete records from a database
 SQL can create new databases
 SQL can create new tables in a database
 SQL can create stored procedures in a database
 SQL can create views in a database
 SQL can set permissions on tables, procedures, and views
3
 Types of SQL Commands

 The basic categories of commands used in a SQL to perform

various functions are:
1. DDL (Data Definition Language)
2. DML(Data Manipulation Language)
3. DQL (Data Query Language)
4. DCL (Data Control Language)
5. DAC (Data administration commands)
6. TCC (Transactional control commands)

4
 Data Definition Language (DDL)
 DDL, is the part of SQL that allows a database user to create and
restructure database objects, such as the creation or
the deletion of a table.
 i.e. Defining Database Structures
 The main DDL commands include:
 CREATE
 ALTER
 DROP
 The principal logical data definition statements are:
 CREATE TABLE, CREATE VIEW, CREATE INDEX, ALTER
TABLE, DROP TABLE, DROP VIEW, and DROP INDEX

5
 Data Manipulation Language (DML):
 DML, is the part of SQL used to manipulate data within objects of a
relational database.
 There are three basic DML commands:
 INSERT
 UPDATE
 DELETE

6
 Data Query Language (DQL):

 DQL is the most commonly used SQL statements for

a relational database user.
 This SQL statement enables the users to query one or
more tables to get the information they want.
 SQL has only one data query statement:
 SELECT

7
 Data Control Language (DCL):
 DCL in SQL allow you to control access to data within the
database.
 DCL is mainly related to the security issues,
 i.e. who has access to the database objects and what operations they can
perform on them.
 The task of DCL is to prevent unauthorized access to data.
 These DCL commands are normally used to create objects
related to user access and also control the distribution of
privileges among users.
 Some data control commands are as follows:
 ALTER PASSWORD
 GRANT
 REVOKE
 CREATE SYNONYM
8
 Data Administration Commands (DAC):
 DAC allows the user to perform audits and perform analyses
on operations within the database.
 They can also be used to help analyze system performance.
 Two general data administration commands are:
 START AUDIT
 STOP AUDIT
 Do not get Data Administration confused with Database
Administration.
 Database Administration is the overall administration of a
database, which envelops the use of all levels of commands.

9
 Transactional Control Commands (TCC):
 TCC allows the user to manage database transactions.
 COMMIT Used to save database transactions
 ROLLBACK Used to undo database transactions
 SAVEPOINT Creates points within groups of transactions in which to
ROLLBACK
 SET TRANSACTION Places a name on a transaction

10
 Lets start SQL query
 We will start our SQL query with the common SQL
commands:
 CREATE
 INSERT
 SELECT
 DELETE
 UPDATE
 Procedures to open Microsoft SQL server
 Start -> All program -> Microsoft SQL Server 2008 -> click
on SQL server management studio -> Select Server Type to
“Database Engine” -> Select Server name -> Click Connect.

11
 Creating and Dropping Database
 The Create statement:
 Syntax:
 CREATE <object type> <object name>
 Object type- can be database or table
 Object name- can be database name or table name
 CREATING DATABASE
 The most basic syntax for the CREATE DATABASE statement looks like :
 CREATE DATABASE <database name>
Example:
Create database myFirstDB;

12
 The Drop statement:
 Used to remove the databases.
 Syntax:
 Drop <object type> <object name>
 Object type- can be database or table
 Object name- can be database name or table name
 Dropping Database
 The most basic syntax for the DROP DATABASE statement looks like :
 DROP DATABASE <database name>
Example:
Drop database myFirstDB;

13
 Creating Tables
The ‘CREATE TABLE’ Statement
used to create a table in a database.
Syntax:
Use Database_Name
CREATE TABLE table_name
(
column_name1 data_type,
column_name2 data_type,
column_name3 data_type,
....
);
 The data type specifies what type of data the
column can hold.
14
 Creating Tables …cont‟d

 Example:
use myFirstDB
create table Student
(
stud_id varchar(10),
stud_Fname varchar(50),
stud_Lname varchar(50),
stud_age integer,
stud_sex varchar(50)
);

15
 Adding Attributes

 Syntax:
ALTER TABLE Table_Name
ADD column_name data_type;

 The ALTER TABLE command allows the user to change the

structure of a table.
 Example:
ALTER Table student
Add Stud_grade varchar(1);

16
 Deleting Attributes
 Syntax:
ALTER TABLE Table_Name
DROP COLUMN column_name;

 Example:
ALTER Table student
DROP COLUMN Stud_age;

17
 Deleting The Table

Syntax:
DROP TABLE table_name;

 Example:
Drop table student;

18
 Inserting Values to the Table
 Syntax:
INSERT INTO table_Name (column 1, column 2, …) Values(value
1, value2, …);
 Example:
insert into student (stud_id, stud_Fname,
stud_Lname, stud_sex,)
values (‘201’,’Mr.X’,’Mr.Y’,’M’);
Or
insert into student
values (‘201’,’Mr.X’,’Mr.Y’,’M’);

19
 Displaying Data
 Syntax :

Select * from Table_name;

 Example:

Select * from student;

20
 Exercise
1. Create a database called Injibarau2.
2. Delete the database Injibarau2.
3. Re-create the database Injibarau2.
4. Create tables called DEPARTMENT and COURSE.
Department (dept_ID,dept_Name,dept_Location).
Course (C_Code,C_Title).
5. Add the attribute C_Description for course table.
6. Remove the attribute dept_Location from department table.

21
SQL Lab 2
 Lesson Topics

 SQL Data Types

 SQL Constraints

 SQL SELECT Statement

SQL WHERE clause

23
 Lesson objectives
 To know the different SQL Data types.

 To have a good understanding on SQL Constraints.

 To know how those constraints are implemented on

the database.

 To understand how data is retrieved from the database.

24
 SQL Data types
 SQL data type is a characteristics that specifies type of data of
any object.
 Each column, variable and expression has related data type in
SQL.
 You would use these data types while creating your tables.
 You would choose a particular data type for a table column
based on your requirement.
 SQL Server offers the following categories of data types for your
use:

25
 Data types…
Character String Data Types:
Data Type Description

CHARACTER (n), Fixed-length character string, where n is the number of

characters.
CHAR(n)

CHARACTER VARYING(n),
Varying-length character string, where n is the
CHAR VARYING(n), maximum number of characters.

VARCHAR (n)

Variable-length character string with a maximum length

TEXT of 2,147,483,647 characters.

26
 Data types…
Numeric Data Types:
Data Type Description
INTEGER,
INT, integer numbers of various sizes
SMALLINT
FLOAT,
Real numbers of various precision.
REAL,
Formatted numbers can be declared by using DECIMAL(i,j )—
DOUBLE PRECISION, or DEC( i,j ) or NUMERIC( i,j )

NUMERIC, —where i, the precision, is the total number of decimal

digits and j, the scale, is the number of digits after the
DECIMAL decimal point.

Example: 34.33 inserted into a DECIMAL(3,1) is typically

rounded to 34.3.

27
 Date and Time Data Types:
Data Type Description

DATE The DATE data type has ten positions, and its
components are YEAR, MONTH, and DAY typically in the form
YYYY-MM-DD.

TIME The TIME data type has at least eight positions, with the
components HOUR, MINUTE, and SECOND, typically in the
form HH:MM:SS.

TIMESTAMP includes both the DATE and TIME fields, plus a minimum of six
positions for fractions of seconds and an optional WITH TIME
ZONE qualifier.

28
 Data types…
Binary Data Types:
Data Type Description

BIT(n), Fixed-length binary data,

BINARY(n) where n is the maximum number of bits.

BIT VARYING(n) , varying length binary data,

VARBINARY(n) where n is the maximum number of bits.

29
 SQL Constraints
 Constraints are the rules enforced on data columns on table.
 These are used to limit the type of data that can go into a table.
 This ensures the accuracy and reliability of the data in the
database.
 Constraints could be column level or table level.
 Column level constraints are applied only to one column where
as table level constraints are applied to the whole table.

30
 Constraints…
 Following are commonly used constraints available in SQL:

1. NOT NULL Constraint

2. DEFAULT Constraint
3. UNIQUE Constraint
4. PRIMARY KEY
5. FOREIGN KEY
6. CHECK Constraint

31
 Constraints…
1. NOT NULL Constraint:
 By default, a column can hold NULL values.

 If you do not want a column to have a NULL value then you

need to define such constraint on this column specifying that
NULL is now not allowed for that column.

 i.e. it ensures that a column cannot have NULL value.

 A NULL is not the same as no data, rather, it represents

unknown data.

32
 Constraints…
 Example: the following SQL creates a new table called
STUDENT and adds six columns, three of which, ID and NAME
and AGE, specify not to accept NULLs:

CREATE TABLE STUDENT

(
STUD_ID INT NOT NULL,
STUD_NAME VARCHAR (20) NOT NULL,
STUD_AGE INT NOT NULL,
STUD_ADDRESS CHAR (25) ,
STUD_SEX CHAR (5),
STUD_GPA DECIMAL (3, 2)
);
33
 Constraints…
2. DEFAULT Constraint :
 This constraint provides a default value to a column when the INSERT
INTO statement does not provide a specific value.
 Example:
CREATE TABLE STUDENT
(
STUD_ID INT NOT NULL,
STUD_NAME VARCHAR (20) NOT NULL,
STUD_AGE INT NOT NULL,
STUD_ADDRESS CHAR (25) ,
STUD_SEX CHAR (5),
STUD_GPA DECIMAL (3, 2) DEFAULT 3.50,
);
 Here STUD_GPA column is set to 3.50 by default, so in
case INSERT INTO statement does not provide a value for
this column then by default this column would be set to
3.50 .
34
 Constraints…
3. UNIQUE Constraint :
 This Constraint prevents two records from having identical
values in a particular column.
 i.e. Ensures that all values in a column are different
 Example:
CREATE TABLE STUDENT
(
STUD_ID INT NOT NULL UNIQUE,
STUD_NAME VARCHAR (20) NOT NULL,
STUD_AGE INT NOT NULL,
STUD_ADDRESS CHAR (25) ,
STUD_SEX CHAR (5),
STUD_GPA DECIMAL (3, 2) DEFAULT 3.50,
);
 Here STUD_ID column is set to UNIQUE, so that you can not
have two records with same STUD_ID.
35
 Constraints…
4. PRIMARY KEY (PK):
 A PK is a field in a table which uniquely identifies each
rows/records in a database table.
 Primary keys must contain unique values.
 A primary key column cannot have NULL values.
 A table can have only one PK which may consist of single or
multiple fields.
 When multiple fields are used as a PK, they are called a
composite key.
 If a table has a PK defined on any field(s) then you can not have
two records having the same value of that field(s).

36
 Constraints…
 Example:
 Creating a table STUDENT with a PK of student Identification
Number.

CREATE TABLE STUDENT

(
STUD_ID INT NOT NULL,
STUD_NAME VARCHAR (20) NOT NULL,
STUD_AGE INT NOT NULL,
STUD_ADDRESS CHAR (25) ,
STUD_SEX CHAR (5),
STUD_GPA DECIMAL (3, 2) DEFAULT 3.50,
PRIMARY KEY (STUD_ID)
);
37
 Constraints…
 To create a PK constraint on the “STUD_ID" column when
STUDENT table already exists:

 ALTER TABLE STUDENT ADD PRIMARY KEY (STUD_ID);

 For defining a PK constraint on multiple columns:

CREATE TABLE STUDENT

(
STUD_ID INT NOT NULL,
STUD_NAME VARCHAR (20) NOT NULL,
STUD_AGE INT NOT NULL,
STUD_ADDRESS CHAR (25) ,
STUD_SEX CHAR (5),
STUD_GPA DECIMAL (3, 2) DEFAULT 3.50,
PRIMARY KEY (STUD_ID, STUD_NAME)
);
38
 Constraints…
5. FOREIGN KEY (FK):
 A FK is a key used to link two tables together. This is sometimes
called a Referencing key.

 A FK is a field in a table which uniquely identifies each

rows/records in any another database table.

 FK is a column or a combination of columns whose values match

a PK in a different table.
 The relationship between two tables matches the PK in one of
the table with a FK in the second table

39
 Constraints…
Example:
 Consider the following two tables: STUDENT and DEPARTMENT
CREATE TABLE STUDENT
(
STUD_ID INT,
STUD_NAME VARCHAR (20),
STUD_AGE INT,
STUD_ADDRESS CHAR (25) ,
STUD_SEX CHAR (5),
STUD_GPA DECIMAL (3, 2),
DEPT_ID INT REFERENCES DEPARTMENT(DEPT_ID),
PRIMARY KEY (STUD_ID)
);

CREATE TABLE DEPARTMENT

(
DEPT_ID INT,
DEPT_NAME VARCHAR (20),
DEPT_LOCATION VARCHAR (20),
PRIMARY KEY (DEPT_ID)
);
40
 Constraints…
 If DEPARTMENT table has already been created, and the
foreign key has not yet been, use the syntax for specifying a
foreign key by altering a table.

ALTER TABLE DEPARTMENT

ADD FOREIGN KEY (STUD_ID) REFERENCES STUDENT(STUD_ID);

41
 Constraints…
6. CHECK Constraint:
 It enables a condition to check the value being entered into a
record.

 i.e. It ensures that all values in a column satisfy certain conditions.

 If the condition evaluates to false, the record violates the

constraint and isn‟t entered into the table.

42
 Constraints…
 Example:

CREATE TABLE STUDENT

(
STUD_ID INT,
STUD_NAME VARCHAR (20),
STUD_AGE INT NOT NULL,
STUD_ADDRESS CHAR (25) ,
STUD_SEX CHAR (5),
STUD_GPA DECIMAL (3, 2) CHECK (STUD_GPA >= 2.00),
PRIMARY KEY (STUD_ID)
);
 Here we add a CHECK with STUD_AGE column, so that you
cannot have any student below 18 years.
43
 Exercise
Create two tables called DEPARTMENT and EMPLOYEE

DEPARTMENT(Dept_ID, Dept_Name, Dept_Location)

EMPLOYEE(Emp_ID, Emp_Fname, Emp_Lname, Emp_Sex, Emp_Age,
Emp_Salary, Dept_ID)
Note:
- Bold and underline attribute (s)refers that it is a primary key.
- bold and italics attribute(s) refers that it is a foreign key.
Make sure that:
 The Employee salary, first name and Department name should not allow a null
value.
 The employee age should not be null and less than 18 years.
 Set a default value for the Department name to “computer science”.
And finally insert at least 4 records of data to both tables.
44
 SQL SELECT STATEMENT
 used to retrieve data or information from table (database).
 used to fetch the data from a database table which returns data
in the form of result table.

 Syntax:
SELECT <attribute list> FROM <table list>

 <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.

45
 SELECT…
Example: to fetch only ID, First name and Sex of Employee
from employee table:

 SELECT Emp_ID,Emp_Fname,Emp_Sex FROM EMPLOYEE

 If you want to fetch all the fields available in the table then you
can use following syntax:

 SELECT * FROM table_name;

 Example: to fetch all fields available in the table DEPARTMENT

 SELECT * FROM DEPARTMENT;

The Astrix (*) symbol stands for all the attributes.

46
 SQL DISTINCT keyword
 To eliminate duplicate tuples in a query result, the keyword
DISTINCT is used.
 For example, the result of Q1 may have duplicate SALARY values
where as Q2 does not have any duplicate values.

Q1: SELECT Emp_Salary

FROM EMPLOYEE

Q2: SELECT DISTINCT Emp_Salary

FROM EMPLOYEE

47
SQL Lab 3
 Lesson Topics

1. SQL WHERE Clause

2. SQL UPDATE Statement
3. SQL DELETE Statement

49
 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.

50
 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.

51
 WHERE…
 Example: STUDENT relation:

 Q1: Retrieve all female students from student relation.

SELECT *
FROM STUDENT
WHERE Stud_Sex='F';
52
 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;

53
 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.

54
 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.
55
 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;

56
 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.
57
 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;

58
 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.
59
 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;
60
 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;

61
 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.
62
 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‟ ;

63
 Exercise
 Create the following two tables:
 STUDENT Relation:

 DEPARTMENT Relation:

64
 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.

65
 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;
66
 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;

67
 END OF THE LESSON

68