Advertisement
iliyazzSU

Databases MSSQL Server Retake Exam - 10 Dec 2021, 01. DDL

Jan 29th, 2023
1,824
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
T-SQL 1.36 KB | None | 0 0
  1. --01. DDL
  2. CREATE TABLE Passengers(
  3. Id INT PRIMARY KEY IDENTITY,
  4. FullName VARCHAR(100) UNIQUE NOT NULL,
  5. Email VARCHAR(50) UNIQUE NOT NULL
  6. )
  7. CREATE TABLE Pilots(
  8. Id INT PRIMARY KEY IDENTITY,
  9. FirstName VARCHAR(30) UNIQUE NOT NULL,
  10. LastName VARCHAR(30) UNIQUE NOT NULL,
  11. Age TINYINT NOT NULL CHECK(Age >= 21 AND Age<=62),
  12. Rating DECIMAL(3,1) CHECK(Rating >= 0 AND Rating <= 10)
  13. )
  14. CREATE TABLE AircraftTypes(
  15. Id INT PRIMARY KEY IDENTITY,
  16. TypeName VARCHAR(30) UNIQUE NOT NULL
  17. )
  18. CREATE TABLE Aircraft(
  19. Id INT PRIMARY KEY IDENTITY,
  20. Manufacturer VARCHAR(25) NOT NULL,
  21. Model VARCHAR(30) NOT NULL,
  22. [Year] INT NOT NULL,
  23. FlightHours INT,
  24. Condition VARCHAR(1) NOT NULL,
  25. TypeId INT NOT NULL FOREIGN KEY REFERENCES AircraftTypes(Id)
  26. )
  27. CREATE TABLE PilotsAircraft(
  28. AircraftId INT NOT NULL FOREIGN KEY REFERENCES Aircraft(Id),
  29. PilotId INT NOT NULL FOREIGN KEY REFERENCES Pilots(Id),
  30. PRIMARY KEY(AircraftId, PilotId)
  31. )
  32. CREATE TABLE Airports(
  33. Id INT PRIMARY KEY IDENTITY,
  34. AirportName VARCHAR(70) UNIQUE NOT NULL,
  35. Country VARCHAR(100) UNIQUE NOT NULL
  36. )
  37. CREATE TABLE FlightDestinations(
  38. Id INT PRIMARY KEY IDENTITY,
  39. AirportId INT NOT NULL FOREIGN KEY REFERENCES Airports(Id),
  40. [Start] DATETIME NOT NULL,
  41. AircraftId INT NOT NULL FOREIGN KEY REFERENCES Aircraft(Id),
  42. PassengerId INT NOT NULL FOREIGN KEY REFERENCES Passengers(Id),
  43. TicketPrice DECIMAL(18, 2) NOT NULL DEFAULT 15
  44. )
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement