Exercise: Do The Following Exercise Carefully Create Command Used To Create Database and Table

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
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')
Values (02,'abebe','tesema','m')
Values (03, 'elsa','kassa','F')
From teacher table

Insert into teacher (TeachId,LName,Fname,salary,sex)
Values (111,'Bini','kebede', 1000,'F')
Values (222,'geremew','ayele', 1200,'M')
Values (333,'Tesema','kassa',1300,'F')
From course table
Page 2 of 7
Insert into course (crsId,crsName,Credithrs,studID,TeachID)
Values (001,'RAD',100,01,111)
Values (002,'IT',120,02,222)
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

Ques4.The 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
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
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
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 "dbo.course", 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:\program.bkf'
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:\program.bkf'
To delete table
Page 6 of 7
drop table course
Page 7 of 7

Exercise: Do The Following Exercise Carefully Create Command Used To Create Database and Table

Uploaded by

Copyright:

Available Formats

Exercise: Do The Following Exercise Carefully Create Command Used To Create Database and Table

Uploaded by

Document Information

Original Title

Copyright

Available Formats

Share this document

Share or Embed Document

Sharing Options

Did you find this document useful?

Is this content inappropriate?

Copyright:

Available Formats

Exercise: Do The Following Exercise Carefully Create Command Used To Create Database and Table

Uploaded by

Copyright:

Available Formats

University of Gondar Computer Science Department

FUNDAMMETAL Database Systems

Exercise: Do the following exercise carefully

Create table course

Ques3. To create or add constraint

3.1 primary key

Alter table student

Alter table course

Alter table teacher

3.2 foreign key

Alter table course

3.2 To create relationship

The relationship must be shown like diagram 1

Insert ……………….command used to enter data into table

Ques4. Insert the data from respective tables

From teacher table

From course table

Student table 1.1

studId LastName FirstName sex

Course table 1.2

001 RAD 100 01 111

Teacher table 1.3

111 Bini kebede 1000 F

The table must be the above shown

To select all value from the table

The SQL SELECT DISTINCT Statement

SELECT DISTINCT fname,lname

Quotes around Text Fields

For text values: SELECT * FROM student WHERE lName='hana'

For numeric values: SELECT * FROM student WHERE studid=02

The ORDER BY Keyword

SQL ORDER BY Syntax

Delete from student

Msg 547, Level 16, State 0, Line 1

 This error massage is shown

Delete from course

Then now you can delete the first record

Delete from student

To backup your database

backup database CNCS

drop database CNCS

To restore the deleted database from your backup

restore database CNCS

You might also like