How to Create Table View in SQL Server

Let's continue with the SQL Server database tutorial, here I will share how to create a table view in SQL Server. Table view is often used by programming developers which is used to combine two or more tables for the purpose of system requirements.

You can use Table View on RDBMS such as SQL Server, Oracle or MySQL which of course has its own characteristics in each RDMS, but in the same function, namely to make queries and make it easier to build applications or can be called data manipulation.

Following are the steps in creating a Table View in SQL Server.

1. Create a Database and Two SQL Server Tables

Please open your computer

Please enter SQL Server Management Studio

Click Connect

Create a database with the name: DB_JNM

Then create two tables with the names: TBL_JURUSAN and TBL_MAHASISWA

As we learned in the previous chapter, I will create a table and fill in the data with Syntax

TBL_JURUSAN

USE [DB_JNM]
GO

CREATE TABLE [dbo].[TBL_JURUSAN](
 [KodeJurusan] [varchar](6) NOT NULL,
 [NamaJurusan] [varchar](100) NULL,
 CONSTRAINT [PK_TBL_JURUSAN] PRIMARY KEY CLUSTERED 
(
 [KodeJurusan] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
Go

INSERT INTO [dbo].[TBL_JURUSAN]
           ([KodeJurusan]
           ,[NamaJurusan])
     VALUES
       ('JUR001','HUKUM'),
    ('JUR002','SASTRA'),
    ('JUR003','EKONOMI'),
    ('JUR004','MANAGEMENT')
Go

TBL_MAHASISWA :

USE [DB_JNM]
GO

CREATE TABLE [dbo].[TBL_MAHASISWA](
 [NIM] [varchar](6) NOT NULL,
 [NamaMahasiswa] [varchar](100) NULL,
 [JenisKelamin] [varchar](20) NULL,
 [AlamatMahasiswa] [varchar](100) NULL,
 [TeleponMahasiswa] [varchar](20) NULL,
 [KodeJurusan] [varchar](6) NULL,
 CONSTRAINT [PK_TBL_MAHASISWA] PRIMARY KEY CLUSTERED 
(
 [NIM] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
Go

INSERT INTO [dbo].[TBL_MAHASISWA]
           ([NIM],[NamaMahasiswa],[JenisKelamin],[AlamatMahasiswa],[TeleponMahasiswa],[KodeJurusan])
     VALUES
       ('NIM001','Ahmad Santoso','LAKI-LAKI','Jalan Jeruk No 1','02111111','JUR001'),
    ('NIM002','Budi Hartono','LAKI-LAKI','Jalan Salak No 2','02122222','JUR001'),
    ('NIM003','Carles Bona','LAKI-LAKI','Jalan Anggur No 3','02133333','JUR002'),
    ('NIM004','Dodi Riyadi','LAKI-LAKI','Jalan Durian No 4','02144444','JUR003'),
    ('NIM005','Edi Wibowo','LAKI-LAKI','Jalan Manga No 5','02155555','JUR002'),
    ('NIM006','Fenty Yuliana','PEREMPUAN','Jalan Kelengkeng No 6','02166666','JUR002'),
    ('NIM007','Gita Cita','PEREMPUAN','Jalan Pisang No 7','02177777','JUR004')
Go

If you have done the Query above, there should be TBL JURUSAN and TBL_MAHASISWA along with their contents.

2. Creating Table View With SQL Server

If you have successfully created a database and two tables in the above step

Please select DB_JNM then select views

Right-click New Views

Add TBL_JURUSAN and TBL_MAHASIWA

If the naming of the Jurusan Code and the data type is the same, you should immediately create a link like the image above

Select or check the desired data

For example, I chose: NIM, Student Name, Sex, Student Address, Student Phone and Department Name. In the green box you can also use the Alias name as shown above

Save with the name: VIEW_MHS or customize to your liking

There should already be dbo.VIEW_MHS like the picture above.

Please right click: Edit Top 200 Rows should appear as shown above

Easy enough, right? what's the difference?

In this view table, I use Inner Join and the Department Name has appeared in the View Table even though the contents are the direction code, but because it is a Foreign Key, the result is that you can bring up the Department Name.

Apart from being a programming information medium, we also share articles related to Android trick tips.

Android Trick Tips Article

Share on Facebook

Share on Twitter

Share on Google+

Share on LinkedIn

Subscribe to receive free email updates: