SQL/day2.sql at master · ZeroToHero-2014/SQL · GitHub

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
USE [master]
GO

/****** creeare baza de date ******/
CREATE DATABASE [Orar]


/****** Object:  Table [dbo].[Student]******/


CREATE TABLE [dbo].[Student](
	[IDStudent] [int] IDENTITY(1,1) NOT NULL,
	[Nume] [nvarchar](25) NULL,
 CONSTRAINT [PK_Student] PRIMARY KEY CLUSTERED
(
	[IDStudent] ASC
))

GO


/****** Object:  Table [dbo].[Profesor]******/
CREATE TABLE [dbo].[Profesor](
	[IDProfesor] [int] IDENTITY(1,1) NOT NULL,
	[Nume] [nvarchar](50) NOT NULL,
 CONSTRAINT [PK_NumeProfesor] PRIMARY KEY CLUSTERED
(
	[IDProfesor] ASC
)) ON [PRIMARY]

GO

/****** Object:  Table [dbo].[Curs]    Script Date: 10/28/2014 9:08:13 PM ******/
CREATE TABLE [dbo].[Curs](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[IDProfesor] [int] NOT NULL,
	[IDStudent] [int] NOT NULL,
	[NumeCurs] [nvarchar](50) NULL,
	CONSTRAINT [PK_Curs] PRIMARY KEY CLUSTERED  --------PRIMARY KEY
	(
		[ID] ASC
	)
)
GO


---FOREIGN KEY
ALTER TABLE [dbo].[Curs]  WITH CHECK ADD  CONSTRAINT [FK_Curs_Student] FOREIGN KEY([IDStudent])
REFERENCES [dbo].[Student] ([IDStudent])
GO

ALTER TABLE [dbo].[Curs] CHECK CONSTRAINT [FK_Curs_Student]
GO


------
INSERT INTO [dbo].[Profesor] ([Nume]) VALUES ('Profesor1')
GO


------
INSERT INTO [dbo].[Student]([Nume])VALUES('Nume prenume')
GO

------
INSERT INTO [dbo].[Curs]([IDProfesor],[IDStudent],[NumeCurs])
VALUES (1,1,'Curs de test')


/******************Create View********************************/
CREATE VIEW v_Test_Orar AS
	SELECT dbo.Curs.NumeCurs, dbo.Profesor.IDProfesor,
	dbo.Student.IDStudent,
	dbo.Student.Nume AS Nume_Student,
	dbo.Profesor.Nume AS Nume_Profesor
FROM     dbo.Curs
INNER JOIN dbo.Profesor ON dbo.Curs.IDProfesor = dbo.Profesor.IDProfesor INNER JOIN
           dbo.Student ON dbo.Curs.IDStudent = dbo.Student.IDStudent
GO


SELECT [NumeCurs],[IDProfesor],[IDStudent],[Nume_Student], [Nume_Profesor] FROM v_Test_Orar