USE [QL_dangky]
GO
/****** Object:  FullTextCatalog [1]    Script Date: 10/30/2014 11:26:29 ******/
CREATE FULLTEXT CATALOG [1]WITH ACCENT_SENSITIVITY = ON
AUTHORIZATION [dbo]
GO
/****** Object:  Table [dbo].[MonHoc]    Script Date: 10/30/2014 11:26:29 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[MonHoc](
    [MaMon] [VARCHAR](10) NOT NULL,
    [TenMon] [nvarchar](50) NOT NULL,
    [STC] [SMALLINT] NOT NULL,
 CONSTRAINT [PK_MonHoc] PRIMARY KEY CLUSTERED
(
    [MaMon] 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
SET ANSI_PADDING OFF
GO
INSERT [dbo].[MonHoc] ([MaMon], [TenMon], [STC]) VALUES (N'M0001', N'Hệ quản trị cơ sở dữ liệu', 4)
INSERT [dbo].[MonHoc] ([MaMon], [TenMon], [STC]) VALUES (N'M0002', N'Cơ sở dữ liệu', 3)
INSERT [dbo].[MonHoc] ([MaMon], [TenMon], [STC]) VALUES (N'M0003', N'Tin đại cương', 5)
INSERT [dbo].[MonHoc] ([MaMon], [TenMon], [STC]) VALUES (N'M0004', N'Toán cao cấp', 4)
INSERT [dbo].[MonHoc] ([MaMon], [TenMon], [STC]) VALUES (N'M0005', N'Giai thuật', 5)
INSERT [dbo].[MonHoc] ([MaMon], [TenMon], [STC]) VALUES (N'M0006', N'C#', 4)
/****** Object:  Table [dbo].[Lop]    Script Date: 10/30/2014 11:26:29 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Lop](
    [MaLop] [VARCHAR](10) NOT NULL,
    [TenLop] [nvarchar](50) NOT NULL,
    [SoSV] [INT] NOT NULL,
 CONSTRAINT [PK_Lop] PRIMARY KEY CLUSTERED
(
    [MaLop] 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
SET ANSI_PADDING OFF
GO
INSERT [dbo].[Lop] ([MaLop], [TenLop], [SoSV]) VALUES (N'ML0001', N'Công nghệ phần mềm', 50)
INSERT [dbo].[Lop] ([MaLop], [TenLop], [SoSV]) VALUES (N'ML0002', N'Tin địa chất', 52)
INSERT [dbo].[Lop] ([MaLop], [TenLop], [SoSV]) VALUES (N'ML0003', N'Trắc địa', 60)
INSERT [dbo].[Lop] ([MaLop], [TenLop], [SoSV]) VALUES (N'ML0004', N'Tin Kinh Tế', 70)
INSERT [dbo].[Lop] ([MaLop], [TenLop], [SoSV]) VALUES (N'ML0005', N'Dầu khí', 80)
/****** Object:  Table [dbo].[SV]    Script Date: 10/30/2014 11:26:29 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[SV](
    [MaSV] [VARCHAR](10) NOT NULL,
    [TenSV] [nvarchar](50) NOT NULL,
    [MaLop] [VARCHAR](10) NOT NULL,
    [NamSinh] [smalldatetime] NULL,
    [Gtinh] [bit] NULL,
 CONSTRAINT [PK_SV] PRIMARY KEY CLUSTERED
(
    [MaSV] 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
SET ANSI_PADDING OFF
GO
INSERT [dbo].[SV] ([MaSV], [TenSV], [MaLop], [NamSinh], [Gtinh]) VALUES (N'1221050211', N'NHẬT CƯỜNG', N'ML0002', CAST(0x7F990000 AS SmallDateTime), 0)
INSERT [dbo].[SV] ([MaSV], [TenSV], [MaLop], [NamSinh], [Gtinh]) VALUES (N'1221050221', N'LÊ ĐÌNH HẢI', N'ML0001', CAST(0x861D0000 AS SmallDateTime), 1)
INSERT [dbo].[SV] ([MaSV], [TenSV], [MaLop], [NamSinh], [Gtinh]) VALUES (N'1221050232', N'LÊ VĂN HỢP', N'ML0001', CAST(0x84B20000 AS SmallDateTime), 1)
INSERT [dbo].[SV] ([MaSV], [TenSV], [MaLop], [NamSinh], [Gtinh]) VALUES (N'1221050243', N'TRUNG TỬ ĐƠN', N'ML0004', CAST(0x7F980000 AS SmallDateTime), 1)
INSERT [dbo].[SV] ([MaSV], [TenSV], [MaLop], [NamSinh], [Gtinh]) VALUES (N'1221050289', N'LÊ ĐÌNH TÙNG', N'ML0003', CAST(0x86790000 AS SmallDateTime), 0)
/****** Object:  StoredProcedure [dbo].[PR_lietke1]    Script Date: 10/30/2014 11:26:27 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[PR_lietke1]
@MaLop VARCHAR(10)
AS
BEGIN
    SELECT MaSV,TenSV FROM SV
    WHERE MaLop=@MaLop

END
GO
/****** Object:  Table [dbo].[DangKy]    Script Date: 10/30/2014 11:26:29 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[DangKy](
    [MaSV] [VARCHAR](10) NOT NULL,
    [MaMon] [VARCHAR](10) NOT NULL,
    [HocKy] [SMALLINT] NOT NULL,
 CONSTRAINT [PK_DangKy] PRIMARY KEY CLUSTERED
(
    [MaSV] ASC,
    [MaMon] 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
SET ANSI_PADDING OFF
GO
INSERT [dbo].[DangKy] ([MaSV], [MaMon], [HocKy]) VALUES (N'1221050211', N'M0005', 1)
INSERT [dbo].[DangKy] ([MaSV], [MaMon], [HocKy]) VALUES (N'1221050221', N'M0002', 2)
INSERT [dbo].[DangKy] ([MaSV], [MaMon], [HocKy]) VALUES (N'1221050232', N'M0001', 1)
INSERT [dbo].[DangKy] ([MaSV], [MaMon], [HocKy]) VALUES (N'1221050243', N'M0004', 4)
INSERT [dbo].[DangKy] ([MaSV], [MaMon], [HocKy]) VALUES (N'1221050289', N'M0003', 2)
/****** Object:  StoredProcedure [dbo].[PR_lietke4]    Script Date: 10/30/2014 11:26:27 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
---- Hãy viết một Stored Procedure với tham số đầu vào
-- là Học kỳ  Liệt kê danh sách các SV
----đăng ký nhiều hơn 10 tín chỉ trong học kỳ tương ứng

 CREATE PROCEDURE [dbo].[PR_lietke4]
 @HocKy SMALLINT
 AS
 BEGIN
     --IF   ((SELECT SUM( STC) FROM MonHoc )>10)
    SELECT SV.MaSV,TenSV FROM SV INNER JOIN DangKy
    ON SV.MaSV=DangKy.MaSV INNER JOIN MonHoc
    ON MonHoc.MaMon=DangKy.MaMon
    WHERE @HocKy=HocKy AND STC>10


 END
GO
/****** Object:  StoredProcedure [dbo].[PR_lietke3]    Script Date: 10/30/2014 11:26:27 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
--3>..Hãy viết một Stored Procedure với các
--tham số đầu vào là Mã SV và Học kỳ  Hãy tính
--tổng số tín chỉ Sinh viên đó đã đăng ký
--trong học kỳ tương ứng và lưu vào 1 tham số dạng đầu ra.

CREATE PROCEDURE [dbo].[PR_lietke3]
@MaSV VARCHAR(10),
@HocKy SMALLINT,
@tong SMALLINT OUTPUT
AS
BEGIN
    SELECT @tong=( SUM(STC))   FROM SV INNER JOIN DangKy
    ON SV.MaSV=DangKy.MaSV INNER JOIN MonHoc
    ON MonHoc.MaMon=DangKy.MaMon
    WHERE SV.MaSV=@MaSV AND HocKy=@HocKy

END
GO
/****** Object:  StoredProcedure [dbo].[PR_lietke2]    Script Date: 10/30/2014 11:26:27 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[PR_lietke2]
@MaMon VARCHAR(10),
@HocKy SMALLINT
AS
BEGIN
        SELECT SV.MaSV,TenSV,Lop.TenLop,
        CASE  Gtinh
        WHEN 1 THEN 'Nam'
        ELSE 'NỮ'
        END AS Gtinh
        FROM SV INNER JOIN Lop
        ON SV.MaLop=Lop.MaLop INNER JOIN DangKy
        ON DangKy.MaSV=SV.MaSV
        WHERE @MaMon=MaMon AND @HocKy=HocKy


END
GO
/****** Object:  ForeignKey [FK_DangKy_MonHoc]    Script Date: 10/30/2014 11:26:29 ******/
ALTER TABLE [dbo].[DangKy]  WITH CHECK ADD  CONSTRAINT [FK_DangKy_MonHoc] FOREIGN KEY([MaMon])
REFERENCES [dbo].[MonHoc] ([MaMon])
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[DangKy] CHECK CONSTRAINT [FK_DangKy_MonHoc]
GO
/****** Object:  ForeignKey [FK_DangKy_SV]    Script Date: 10/30/2014 11:26:29 ******/
ALTER TABLE [dbo].[DangKy]  WITH CHECK ADD  CONSTRAINT [FK_DangKy_SV] FOREIGN KEY([MaSV])
REFERENCES [dbo].[SV] ([MaSV])
ON UPDATE CASCADE
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[DangKy] CHECK CONSTRAINT [FK_DangKy_SV]
GO
/****** Object:  ForeignKey [FK_SV_Lop]    Script Date: 10/30/2014 11:26:29 ******/
ALTER TABLE [dbo].[SV]  WITH CHECK ADD  CONSTRAINT [FK_SV_Lop] FOREIGN KEY([MaLop])
REFERENCES [dbo].[Lop] ([MaLop])
ON UPDATE CASCADE
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[SV] CHECK CONSTRAINT [FK_SV_Lop]
GO