Yam Code
Sign up
Login
New paste
Home
Trending
Archive
English
English
Tiếng Việt
भारत
Sign up
Login
New Paste
Browse
-- Ngo Tuan Anh -- CREATE DATABASE SE1431_HE141655 USE SE1431_HE141655 CREATE TABLE Departments( DeptID VARCHAR(4) NOT NULL PRIMARY KEY, Name NVARCHAR(50), NoOfStudents INT ); CREATE TABLE Students( StudentID VARCHAR(4), LastName NVARCHAR(30), FirstName NVARCHAR(30), Sex VARCHAR(1), DateOfBirth DATE, PlaceOfBirth NVARCHAR(30), DeptID VARCHAR(4), Scholarship FLOAT, AverageScore NUMERIC(4,2), CONSTRAINT Check_Sex CHECK(Sex IN ('M','F')) ); CREATE TABLE Courses( CourseID VARCHAR(4), Name NVARCHAR(35), Credits TINYINT ); CREATE TABLE Results( StudentID VARCHAR(4), CourseID VARCHAR(4), Year INT, Semester INT, Mark FLOAT(1), Grade VARCHAR(6) ); INSERT INTO Departments (DeptID, Name) VALUES ('IS','Information Systems'), ('NC','Network and Communication'), ('SE','Software Engineering'), ('CE','Computer Engineering'), ('CS','Computer Science'); INSERT INTO Students (StudentID, LastName, FirstName, Sex, DateOfBirth, PlaceOfBirth, DeptID, Scholarship) VALUES ('S001',N'Lê',N'Kim Lan','F','1990-02-23',N'Hà Nội','IS','130000'), ('S002',N'Trần',N'Minh Chánh','M','1992-12-24',N'Bình Định','NC','150000'), ('S003',N'Lê',N'An Tuyết','F','1991-02-12',N'Hải Phòng','IS','170000'), ('S004',N'Trần',N'Anh Tuấn','M','1993-12-20',N'TpHCM','NC','80000'), ('S005',N'Trần',N'Thị Mai','F','1991-08-12',N'TpHCM','SE','0'), ('S006',N'Lê',N'Thị Thu Thủy','F','1991-01-02',N'An Giang','IS','0'), ('S007',N'Nguyễn',N'Kim Thư','F','1990-02-02',N'Hà Nội','SE','180000'), ('S008',N'Lê',N'Văn Long','M','1992-12-08',N'TpHCM','IS','190000'); INSERT INTO Courses( CourseID, Name, Credits) VALUES ('DS01','Database Systems','3'), ('AI01','Artificial Intelligence','3'), ('CN01','Computer Network','3'), ('CG01','Computer Graphics ','4'), ('DSA1','Data Structures and Algorithms','4'); INSERT INTO Results(StudentID, CourseID, Year, Semester, Mark) VALUES ('S001','DS01','2017','1','3'), ('S001','DS01','2017','2','6'), ('S001','AI01','2017','1','4.5'), ('S001','AI01','2017','2','6'), ('S001','CN01','2017','3','5'), ('S002','DS01','2016','1','4.5'), ('S002','DS01','2017','1','7'), ('S002','CN01','2016','3','10'), ('S002','DSA1','2016','3','9'), ('S003','DS01','2017','1','2'), ('S003','DS01','2017','3','5'), ('S003','CN01','2017','2','2.5'), ('S003','CN01','2017','3','4'), ('S004','DS01','2017','3','4.5'), ('S004','DSA1','2018','1','10'), ('S005','DS01','2017','2','7'), ('S005','CN01','2017','2','2.5'), ('S005','CN01','2018','1','5'), ('S006','AI01','2018','1','6'), ('S006','CN01','2018','2','10'); -- Q2 -- UPDATE Departments SET NoOfStudents=(SELECT COUNT(1) FROM Students s WHERE s.DeptID=Departments.DeptID); -- Q3 -- CREATE TABLE avgscore( studentID VARCHAR(4) PRIMARY KEY, avgs NUMERIC(4,2) ) INSERT INTO avgscore SELECT a.StudentID, avg(MaxMarks) FROM (SELECT StudentID, CourseID, max(Mark) MaxMarks FROM Results GROUP BY StudentID, CourseID) AS a GROUP BY a.StudentID UPDATE Students SET AverageScore = avgscore.avgs FROM Students, avgscore WHERE Students.StudentID = avgscore.studentID; -- Q4 -- UPDATE Result SET Grade = 'Pass' Where Mark >= '5' AND Mark <= '10'; UPDATE Result SET Grade = 'Failed' WHERE Mark >= '0' AND Mark < '5'; -- Q5 -- SELECT StudentID, Fullname = (FirstName + ' ' + LastName), DateOfBirth, PlaceOfBirth, DeptID, Schoolarship FROM Students WHERE Schoolarship > '160000' ORDER BY Schoolarship DESC; -- Q6 -- SELECT Departments.DeptID, DepartmentName = (Name), FirstName, LastName FROM Departments FULL OUTER JOIN Students ON Departments.DeptID = Students.DeptID; -- Q7 -- SELECT Students.StudentID, LastName, FirstName, COUNT(Courses.CourseID) AS NumberOfCourse FROM Students, Courses, Results WHERE Students.StudentID = Results.StudentID AND Courses.CourseID = Results.CourseID GROUP BY Students.StudentID, LastName,FirstName ORDER BY NumberOfCourse -- Q8 -- SELECT Departments.DeptID, Departments.Name, SUM(CASE WHEN Sex = 'F' THEN 1 ELSE 0 END) AS NumberOfFemaleStudents, SUM(CASE WHEN Sex = 'M' THEN 1 ELSE 0 END) AS NumberOfMaleStudents FROM Departments, Students WHERE Departments.DeptID = Students.DeptID GROUP BY Departments.DeptID, Departments.Name -- Q9 -- SELECT DISTINCT Students.* FROM Students, Results WHERE Students.StudentID = Results.StudentID AND Results.CourseID = 'DS01' AND Students.DeptID <> 'IS' GROUP BY Students.StudentID, Students.LastName, Students.Firstname, Students.Sex, Students.DateOfBirth, Students.PlaceOfBirth HAVING Mark > any (SELECT Mark FROM Results, Students WHERE Students.StudentID = Results.StudentID AND Results.CourseID = 'DS01' AND Students.DeptID LIKE 'IS')
Paste Settings
Paste Title :
[Optional]
Paste Folder :
[Optional]
Select
Syntax Highlighting :
[Optional]
Select
Markup
CSS
JavaScript
Bash
C
C#
C++
Java
JSON
Lua
Plaintext
C-like
ABAP
ActionScript
Ada
Apache Configuration
APL
AppleScript
Arduino
ARFF
AsciiDoc
6502 Assembly
ASP.NET (C#)
AutoHotKey
AutoIt
Basic
Batch
Bison
Brainfuck
Bro
CoffeeScript
Clojure
Crystal
Content-Security-Policy
CSS Extras
D
Dart
Diff
Django/Jinja2
Docker
Eiffel
Elixir
Elm
ERB
Erlang
F#
Flow
Fortran
GEDCOM
Gherkin
Git
GLSL
GameMaker Language
Go
GraphQL
Groovy
Haml
Handlebars
Haskell
Haxe
HTTP
HTTP Public-Key-Pins
HTTP Strict-Transport-Security
IchigoJam
Icon
Inform 7
INI
IO
J
Jolie
Julia
Keyman
Kotlin
LaTeX
Less
Liquid
Lisp
LiveScript
LOLCODE
Makefile
Markdown
Markup templating
MATLAB
MEL
Mizar
Monkey
N4JS
NASM
nginx
Nim
Nix
NSIS
Objective-C
OCaml
OpenCL
Oz
PARI/GP
Parser
Pascal
Perl
PHP
PHP Extras
PL/SQL
PowerShell
Processing
Prolog
.properties
Protocol Buffers
Pug
Puppet
Pure
Python
Q (kdb+ database)
Qore
R
React JSX
React TSX
Ren'py
Reason
reST (reStructuredText)
Rip
Roboconf
Ruby
Rust
SAS
Sass (Sass)
Sass (Scss)
Scala
Scheme
Smalltalk
Smarty
SQL
Soy (Closure Template)
Stylus
Swift
TAP
Tcl
Textile
Template Toolkit 2
Twig
TypeScript
VB.Net
Velocity
Verilog
VHDL
vim
Visual Basic
WebAssembly
Wiki markup
Xeora
Xojo (REALbasic)
XQuery
YAML
HTML
Paste Expiration :
[Optional]
Never
Self Destroy
10 Minutes
1 Hour
1 Day
1 Week
2 Weeks
1 Month
6 Months
1 Year
Paste Status :
[Optional]
Public
Unlisted
Private (members only)
Password :
[Optional]
Description:
[Optional]
Tags:
[Optional]
Encrypt Paste
(
?
)
Create New Paste
You are currently not logged in, this means you can not edit or delete anything you paste.
Sign Up
or
Login
Site Languages
×
English
Tiếng Việt
भारत