SQL-Example 01:

Below is some queries about creating database, and relational tables. Please read each part carefully then write and execute it in MS SQL server management studio.

/*

The slash-star (/*) and star-slash (*/) pair is used to have a block of comment,

or in another words, we use /* */ for multi-line comments

just like this very comment I put here.

*/

-- Double dash (--) can be used to have a single-line comment, just like this line.

-- You can use (--) for multi-line comments like the following:

--

-- The slash-star (/*) and star-slash (*/) pair is used to have a block of comment,

-- or in another words, we use /* */ for multi-line comments

--

/*

First things first!

In order to work with a DB we have to create one:

*/

-- NOTE: Use your lastname for database name

create database Chavoshi

go

-- This means run all the SQL statements from last "go" to this point as a batch

-- A batch is a block of statements that run altogether.

--------------------------------------------------------------------------------

/*

In order to use a database, we have to say so!

*/

use Chavoshi

go

---------------------------------------

/*

Now it's time to create some tables in our DB.

The point is you have to create reference tables first and then dependent tables.

- A reference table is the one that has no FK, i.e. it does not point to another table.

- A dependent table is the one that includes one or more FK.

*/

create table Address

(

-- First let's define all the columns we have in our table diagram

-- We call this section the Column Definition section:

ID int not null, -- By default, columns are null, which means it is OK to not insert a value for that column when we want to add a row to the table

Street varchar(50) not null,

City varchar(50) not null,

State char(2) not null,

ZIP char(10) not null,

-- After we defined all columns, it's time to talk about PKs and FKs

-- We call this section PK-FK section:

primary key (ID)

)

--------------------------------------------------------------------------------

create table ContactInfo

(

ID int not null,

Email varchar(50) not null unique, -- "unique" means the values of this column should be unique throughout the table.

Telephone char(10) not null unique,

Address_ID int not null,

primary key (ID),

foreign key (Address_ID) references Address (ID)

)

--------------------------------------------------------------------------------

create table Student

(

ID integer not null,

Name varchar(50) not null,

SSN char(11) not null unique,

GPA real not null,

ContactInfo_ID int not null,

primary key (ID),

foreign key (ContactInfo_ID) references ContactInfo (ID)

)

------------------------------------------------

create table BankAccount

(

ID int not null IDENTITY(1, 1),

OwnerName varchar(50) not null,

BankName varchar(50) not null,

RoutingNumber char(15) not null,

AccountNumber char(15) not null,

primary key (ID)

)

--------------------------------------------------------------------------------

create table Instructor

(

ID int not null IDENTITY(1, 1),

Name varchar(50) not null,

DOB date not null,

SSN char(11) not null,

HireDate datetime not null,

isFullTime binary not null,

BankAccount_ID int not null,

ContactInfo_ID int not null,

primary key (ID),

foreign key (BankAccount_ID) references BankAccount (ID),

foreign key (ContactInfo_ID) references ContactInfo (ID)

)

--------------------------------------------------------------------------------

create table Course

(

ID int not null IDENTITY(1, 1),

Code char(10) not null,

Description varchar(100) not null,

CreditHour int not null,

primary key (ID)

)

--------------------------------------------------------------------------------

create table CoursesTaught

(

Instructor_ID int not null,

Course_ID int not null,

primary key (Instructor_ID, Course_ID),

foreign key (Instructor_ID) references Instructor (ID),

foreign key (Course_ID) references Course (ID)

)

--------------------------------------------------------------------------------

create table CourseRequirement

(

Course_ID int not null,

Prerequisite_ID int not null,

primary key (Course_ID, Prerequisite_ID),

foreign key (Course_ID) references Course (ID),

foreign key (Prerequisite_ID) references Course (ID)

)

--------------------------------------------------------------------------------