BSc CSIT in Nepal: DBMS Lab report 1

Practical part:

SQL Syntax:

To create database:

CREATE DATABASE my_deptvk(043);

To create relational schema with necessary constraints:

Create table teachers_detail

(

Tid integer not null,

Tname varchar(20),

Address varchar(20),

Phno varchar(20),

Qualification varchar(20),

Major varchar(20),

Minor varchar(20),

Type varchar(20)

)

Create table student_detail

(

Roll integer not null,

Name varchar(20),

Address varchar(20),

Telephone varchar(20),

Email varchar(20),

DOB varchar(20),

Faculty varchar(20),

Yearsem varchar(20),

Batch varchar(20),

Level varchar(20)

)

Create table subject_detail

(

Subid integer not null,

Tid integer,

Faculty varchar(20),

Yearsem varchar(20),

Level varchar(20),

Title varchar(20),

Level varchar(20),

Hours varchar(20),

Fm integer,

Pm integer,

Type varchar(20)

)

Create table exam_detail

(

Examyear integer not null,

Term integer not null,

Start_date varchar(20),

End_date varchar(20),

Result_day varchar(20)

)

 Create table marks_detail

(

Examyear integer,

Term varchar(20) not null,

Roll varchar(20) not null,

Subid varchar(20) not null,

Fm integer,

Marks integer

)

To describe the formats of table:

DESC  teachers_detail

DESC  student_detail

DESC subject_detail

DESC exam_detail

DESC marks_detail

To insert the sample information on different tables:

1.                To  insert data in  teachers_detail table:

insert into teachers_detail

values("07","Bishnu Poudel","Mareng-7","9847111121","Master","English","Math","Full time")

 (insert similarly other 5 data)

2.                To insert data in student_detail table:

insert into student_detail

values("001","VK","Kathmandu","9808323278","search2cactus@gmail.com","2046/09/25","Science","Fourth","07","Bachelor")

(insert similarly other 5 data)

3.                To insert data  in  subject_detail table:

insert into subject_detail

values("001","01","Science","Fourth","Bachelor","CSC_150","12","100","40","Compulsary")

(insert similarly other 5 data)

4.                To insert data in marks_detail

insert into marks_detail

values("2007","1st","001","CSC-150","100","80")

(insert similarly other 5 data)

5.                To insert data in exam_detail

insert into exam_detail

values("66","first","2066/02/22","2066/02/27","2066/03/07")

(insert similarly other 5 data)

Display the content of all the relations.

SELECT * FROM `teachers_detail` WHERE 1

SELECT * FROM `subject_detail` WHERE 1

SELECT * FROM `student_detail` WHERE 1

SELECT * FROM `marks_detail` WHERE 1

SELECT * FROM `exam_detail` WHERE 1