Yam Code
Sign up
Login
New paste
Home
Trending
Archive
English
English
Tiếng Việt
भारत
Sign up
Login
New Paste
Browse
USE master; CREATE DATABASE NML01; GO USE NML01; CREATE TABLE sailors ( Sid int, Sname varchar(50), rating int, age int, sex bit, constraint pk_sailors Primary key(Sid) ); CREATE TABLE boats ( Bid int, Bname varchar(50), Color varchar(10), Price decimal, constraint pk_boats Primary key(Bid) ); CREATE TABLE reserves ( Sid int, Bid int, ReservesDate date, constraint pk_reserves Primary key(Sid, Bid, ReservesDate) ); ALTER TABLE reserves ADD CONSTRAINT fk_sailors_reserves FOREIGN KEY (Sid) REFERENCES sailors(Sid), CONSTRAINT fk_boats_reserves FOREIGN KEY (Bid) REFERENCES boats(Bid); INSERT INTO sailors VALUES(22, 'Dustin', 7, 45, 1); INSERT INTO sailors VALUES(29, 'Sara', 1, 33, 0); INSERT INTO sailors VALUES(31, 'Lubber', 8, 55, 1); INSERT INTO sailors VALUES(32, 'Andy', 8, 25, 1); INSERT INTO sailors VALUES(58, 'Adele', 10, 35, 0); INSERT INTO sailors VALUES(64, 'Horatio', 7, 35, 1); INSERT INTO sailors VALUES(71, 'Amy', 10, 16, 0); INSERT INTO sailors VALUES(74, 'Hora', 9, 35, 1); INSERT INTO sailors VALUES(85, 'Jane', 3, 25, 0); INSERT INTO sailors VALUES(95, 'Bob', 3, 63, 1); INSERT INTO sailors VALUES(96, 'Frodo', 3, 25, 1); INSERT INTO boats VALUES(101, 'Interlake', 'Blue', 30000); INSERT INTO boats VALUES(102, 'Interlake', 'Red', 29000); INSERT INTO boats VALUES(103, 'Clipper', 'Green', 42000); INSERT INTO boats VALUES(104, 'Marine', 'Red', 18000); INSERT INTO reserves VALUES(22, 101, '2019-10-10'); INSERT INTO reserves VALUES(22, 102, '2019-10-10'); INSERT INTO reserves VALUES(22, 103, '2019-10-08'); INSERT INTO reserves VALUES(22, 104, '2019-10-07'); INSERT INTO reserves VALUES(31, 102, '2019-11-10'); INSERT INTO reserves VALUES(31, 103, '2019-11-06'); INSERT INTO reserves VALUES(31, 104, '2019-11-12'); INSERT INTO reserves VALUES(64, 101, '2019-09-05'); INSERT INTO reserves VALUES(64, 102, '2019-09-08'); INSERT INTO reserves VALUES(74, 103, '2019-09-08'); --Câu 1 Tìm tên thuỷ thủ đặt thuyền màu đỏ SELECT DISTINCT Sname FROM sailors s INNER JOIN reserves r ON s.Sid like r.Sid INNER JOIN boats b ON r.Bid like b.Bid WHERE b.Color like 'Red'; SELECT DISTINCT Sname FROM sailors s, reserves r, boats b WHERE s.Sid like r.Sid AND r.Bid like b.Bid AND b.Color like 'Red'; --Câu 2 Tìm tên thuỷ thủ đặt thuyền màu đỏ hoặc xanh SELECT DISTINCT Sname FROM sailors s INNER JOIN reserves r ON s.Sid like r.Sid INNER JOIN boats b ON r.Bid like b.Bid WHERE b.Color IN ('Red', 'Blue', 'Green'); SELECT DISTINCT Sname FROM sailors s, reserves r, boats b WHERE s.Sid like r.Sid AND r.Bid like b.Bid AND b.Color IN ('Red', 'Blue', 'Green'); --Câu 3 Tìm tên thuỷ thủ đặt tất cả thuyền SELECT Sname FROM sailors s INNER JOIN reserves r ON s.Sid like r.Sid GROUP BY Sname HAVING COUNT(*) >= (SELECT COUNT(*) FROM boats); SELECT Sname FROM sailors s, reserves r WHERE s.Sid like r.Sid GROUP BY Sname HAVING COUNT(*) >= (SELECT COUNT(*) FROM boats); --Câu 4 Tìm tên thuỷ thủ chưa đặt bao giờ SELECT DISTINCT Sname FROM sailors s LEFT JOIN reserves r ON s.Sid like r.Sid WHERE r.Sid IS NULL; SELECT DISTINCT Sname FROM sailors WHERE Sid NOT IN (SELECT DISTINCT Sid FROM reserves); --Câu 5 Tìm tên thuyền buồm có nhiều thuỷ thủ đặt nhất SELECT Bname FROM boats b INNER JOIN reserves r ON b.Bid like r.Bid GROUP BY Bname HAVING COUNT(*) like (SELECT TOP 1 COUNT(*) FROM reserves GROUP BY Bid ORDER BY COUNT(*) DESC); SELECT Bname FROM boats b, reserves r WHERE b.Bid like r.Bid GROUP BY Bname HAVING COUNT(*) like (SELECT TOP 1 COUNT(*) FROM reserves GROUP BY Bid ORDER BY COUNT(*) DESC); --Câu 6 Tìm ngày tháng năm có thuỷ thủ đặt nhiều nhất SELECT ReservesDate FROM reserves GROUP BY ReservesDate HAVING COUNT(*) like (SELECT TOP 1 COUNT(*) FROM reserves GROUP BY ReservesDate ORDER BY COUNT(*) DESC); --Câu 7 Tìm các loại màu của các thuyền buồm được thuỷ thủ Dustin đặt SELECT DISTINCT Color FROM boats b INNER JOIN reserves r ON b.Bid like r.Bid INNER JOIN sailors s ON r.Sid like s.Sid WHERE s.Sname like 'Dustin'; SELECT DISTINCT Color FROM boats b, reserves r, sailors s WHERE b.Bid like r.Bid AND r.Sid LIKE s.Sid AND s.Sname like 'Dustin'; --Câu 8 Tìm tất cả các thuỷ thủ (Sid) có ít nhất là hạng 8 và đặt thuyền buồm (Bid) 103 SELECT s.Sid FROM sailors s INNER JOIN reserves r ON r.Sid like s.Sid WHERE rating >= 8 AND r.Bid like 103; SELECT s.Sid FROM sailors s, reserves r WHERE r.Sid like s.Sid AND rating >= 8 AND r.Bid like 103; --Câu 9 Tìm tên thuỷ thủ không đặt thuyền nào mà trong tên có chứa chuỗi 'do', sắp xếp tăng dần SELECT Sname FROM sailors s LEFT JOIN reserves r ON s.Sid like r.Sid WHERE r.Sid IS NULL AND Sname LIKE '%do%' ORDER BY Sname DESC; SELECT Sname FROM sailors WHERE Sid NOT IN (SELECT DISTINCT Sid FROM reserves) AND Sname LIKE '%do%' ORDER BY Sname DESC; --Câu 10 Tìm thuỷ thủ (Sid) không đặt thuyền nào có tuổi >= 20 SELECT s.Sid FROM sailors s LEFT JOIN reserves r ON s.Sid like r.Sid WHERE r.Sid IS NULL AND age >= 20; SELECT Sid FROM sailors WHERE Sid NOT IN (SELECT DISTINCT Sid FROM reserves) AND age >= 20; --Câu 11 Tìm tên thuỷ thủ có đặt ít nhất 2 thuyền buồm SELECT Sname FROM sailors s INNER JOIN reserves r ON s.Sid like r.Sid GROUP BY r.Sid, Sname HAVING COUNT(r.Bid) >= 2 SELECT Sname FROM sailors s, reserves r WHERE s.Sid like r.Sid GROUP BY r.Sid, Sname HAVING COUNT(r.Bid) >= 2 --Câu 12 Tìm tên thuỷ thủ có đặt thuyền buồm trong ngày 10/10/2019 SELECT DISTINCT Sname FROM sailors s INNER JOIN reserves r ON s.Sid like r.Sid WHERE ReservesDate like '2019-10-10'; SELECT DISTINCT Sname FROM sailors s, reserves r WHERE s.Sid like r.Sid AND ReservesDate like '2019-10-10';
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
भारत