University of Gondar Computer Science Department
FUNDAMMETAL Database Systems
Exercise: Do the following exercise carefully
Create……… command used to create database and table
Ques1. Create database called faculty
Create database CNCS
Ques2. Create a table called student, course and Teacher like below under the exercise database
Create table student
(
Studid int not null,
Lname varchar (10),
Fname varchar (10),
Sex varchar (2),
);
Create table course
(
crsID int not null ,
crsName varchar (10),
Credithrs varchar (10),
studID int not null ,
TeachID int not null,
);
Create table Teacher
(
TeachID int not null,
Lname varchar (10),
Fname varchar (10),
Salary numeric (6),
Sex varchar (2),
);
Ques3. To create or add constraint
3.1 primary key
Alter table student
Add constraint Pk_stud primary key (studID)
Alter table course
Add constraint Pk_crs primary key (crsID)
Alter table teacher
Add constraint Pk_Teach primary key (TeachID)
3.2 foreign key
Alter table course
Add constraint FK_crsstud foreign key (studID) references student (studID)
Alter table course
Add constraint FK_crsTeach foreign key (TeachID) references Teacher (teachID)
Page 1 of 7
University of Gondar Computer Science Department
FUNDAMMETAL Database Systems
3.2 To create relationship
Click on database diagram -----yes--------right click on database diagram and click create new diagram
-------- Add the table as you went to create relationship then save
The relationship must be shown like diagram 1
Diagram 1
Insert ……………….command used to enter data into table
Ques4. Insert the data from respective tables
From student table
Insert into student(studid ,Lname ,Fname ,sex )
Values (01,'Hana','kebede','F')
Insert into student(studid ,Lname ,Fname ,sex )
Values (02,'abebe','tesema','m')
Insert into student(studid ,Lname ,Fname ,sex )
Values (03, 'elsa','kassa','F')
From teacher table
Insert into teacher (TeachId,LName,Fname,salary,sex)
Values (111,'Bini','kebede', 1000,'F')
Insert into teacher (TeachId,LName,Fname,salary,sex)
Values (222,'geremew','ayele', 1200,'M')
Insert into teacher (TeachId,LName,Fname,salary,sex)
Values (333,'Tesema','kassa',1300,'F')
From course table
Page 2 of 7
University of Gondar Computer Science Department
FUNDAMMETAL Database Systems
Insert into course (crsId,crsName,Credithrs,studID,TeachID)
Values (001,'RAD',100,01,111)
Insert into course (crsId,crsName,Credithrs,studID,TeachID)
Values (002,'IT',120,02,222)
Insert into course (crsId,crsName,Credithrs,studID,TeachID)
Values (003,'IQ',45,03,333)
Student table 1.1
studId LastName FirstName sex
01 Hana kebede F
02 abebe tesema M
03 elsa kassa F
Course table 1.2
TeachID
crsId crsName Credit hrs studID
001 RAD 100 01 111
002 IT 120 02 222
003 IQ 45 03 333
Teacher table 1.3
TeachId LastName FirstName salary sex
111 Bini kebede 1000 F
222 geremew ayele 1200 M
333 Tesema kassa 1300 F
The table must be the above shown
[Link] SELECT Statement
The SELECT statement is used to select data from a database; the result is stored in a result table, called the result-
set.
To select all value from the table
Page 3 of 7
University of Gondar Computer Science Department
FUNDAMMETAL Database Systems
SELECT * FROM course
Or
SELECT crsID,crsName,Credithrs,studID,TeachID
FROM course
SELECT studID,studid,TeachID
FROM course
The SQL SELECT DISTINCT Statement
In a table, some of the columns may contain duplicate values. This is not a problem; however, sometimes you will want to
list only the different (distinct) values in a table. The DISTINCT keyword can be used to return only distinct (different)
values.
SELECT DISTINCT fname,lname
FROM student
The WHERE Clause
The WHERE clause is used to extract only those records that fulfill a specified criterion
SELECT crsID,crsName,Credithrs,studID,TeachID
FROM course
where crsID=001
Quotes around Text Fields
Page 4 of 7
University of Gondar Computer Science Department
FUNDAMMETAL Database Systems
SQL uses single quotes around text values (most database systems will also accept double quotes).Although, numeric
values should not be enclosed in quotes.
For text values: SELECT * FROM student WHERE lName='hana'
For numeric values: SELECT * FROM student WHERE studid=02
The ORDER BY Keyword
The ORDER BY keyword is used to sort the result-set by a specified column. The ORDER BY keyword sorts the records
in ascending order by default. If you want to sort the records in a descending order, you can use the DESC keyword, and
ascending use ASC.
SQL ORDER BY Syntax
SELECT studid,fname,lname
FROM student
ORDER BY fname ASC
SELECT studid,fname,lname
FROM student
ORDER BY fname desc
The UPDATE Statement The UPDATE statement is used to update existing records in a table. For example to increase
the teacher salary by 4times
UPDATE teacher
SET salary=(salary*4)
Page 5 of 7
University of Gondar Computer Science Department
FUNDAMMETAL Database Systems
Note: Notice the WHERE clause in the UPDATE syntax. The WHERE clause specifies which record or records that
should be updated. If you omit the WHERE clause, all records will be updated! For example to update the salary of Id 222
UPDATE teacher
SET salary=(salary*4)
where teachID=222
Delete the single record For example if you went to delete the data on the students where
student Id 01 you can use
Delete from student
Where studid=01
Msg 547, Level 16, State 0, Line 1
The DELETE statement conflicted with the REFERENCE constraint "FK_crsstud". The conflict occurred in database
"ITE", table "[Link]", column 'studID'.
The statement has been terminated.
This error massage is shown
But you cant delete this record b/se the relationship b/n two tables are exit due to
foreign key referencional integrity so to delete the above record first delete the main
chain table record the try again
Delete from course
Where crsid=001
Then now you can delete the first record
Delete from student
Where studid=01
To backup your database
backup database CNCS
to disk ='d:\[Link]'
To drop the database ,Close all opend database object explorer or query sheet then type
drop database CNCS
To restore the deleted database from your backup
restore database CNCS
from disk ='d:\[Link]'
To delete table
Page 6 of 7
University of Gondar Computer Science Department
FUNDAMMETAL Database Systems
drop table course
Page 7 of 7