MS SQL - Table Relations

--Exercises: Table Relations

--01. One-To-One Relationship

CREATE TABLE Passports (
    PassportID INT PRIMARY KEY IDENTITY,
    PassportNumber VARCHAR(10) NOT NULL)

CREATE TABLE Persons (
    PersonID INT PRIMARY KEY IDENTITY,
    FirstName NVARCHAR(20) NOT NULL,
    Salary DECIMAL(6,2) NOT NULL,
    PassportID INT FOREIGN KEY REFERENCES Passports(PassportID) UNIQUE NOT NULL)

--02. One-To-Many Relationship

CREATE TABLE Manufacturers(
ManufacturerID INT PRIMARY KEY IDENTITY,
[Name] VARCHAR(6) UNIQUE NOT NULL,
EstablishedOn DATE NOT NULL)

CREATE TABLE Models(
ModelID INT PRIMARY KEY IDENTITY(101, 1),
[Name] VARCHAR(8) UNIQUE NOT NULL,
ManufacturerID INT FOREIGN KEY REFERENCES Manufacturers(ManufacturerID) NOT NULL)

--03. Many-To-Many Relationship

CREATE TABLE Students(
StudentID INT PRIMARY KEY IDENTITY,
[Name] VARCHAR(10) NOT NULL,)

CREATE TABLE Exams(
ExamID INT PRIMARY KEY IDENTITY(101, 1),
[Name] VARCHAR(15) NOT NULL,)

CREATE TABLE StudentsExams(
StudentID INT FOREIGN KEY REFERENCES Students(StudentID) NOT NULL,
ExamID INT FOREIGN KEY REFERENCES Exams(ExamID) NOT NULL,
PRIMARY KEY(StudentID, ExamID))

--04. Self-Referencing

CREATE TABLE Teachers(
TeacherID INT PRIMARY KEY IDENTITY(101, 1),
[Name] VARCHAR(15) NOT NULL,
ManagerID INT FOREIGN KEY REFERENCES Teachers(TeacherID))

--05. Online Store Database

CREATE DATABASE OnlineStore

GO

USE OnlineStore

GO

CREATE TABLE Cities(
CityID INT PRIMARY KEY IDENTITY,
[Name] VARCHAR(50) NOT NULL)

CREATE TABLE Customers(
CustomerID INT PRIMARY KEY IDENTITY,
[Name] VARCHAR(50) NOT NULL,
Birthday DATE NOT NULL,
CityID INT FOREIGN KEY REFERENCES Cities(CityID) NOT NULL)

CREATE TABLE Orders(
OrderID INT PRIMARY KEY IDENTITY,
CustomerID INT FOREIGN KEY REFERENCES Customers(CustomerID) NOT NULL)

CREATE TABLE ItemTypes(
ItemTypeID INT PRIMARY KEY IDENTITY,
[Name] VARCHAR(50) NOT NULL)

CREATE TABLE Items(
ItemID INT PRIMARY KEY IDENTITY,
[Name] VARCHAR(50) NOT NULL,
ItemTypeID INT FOREIGN KEY REFERENCES ItemTypes(ItemTypeID) NOT NULL)

CREATE TABLE OrderItems(
OrderID INT FOREIGN KEY REFERENCES Orders(OrderID) NOT NULL,
ItemID INT FOREIGN KEY REFERENCES Items(ItemID) NOT NULL,
PRIMARY KEY(OrderID, ItemID))

--06. University Database

CREATE DATABASE University
GO
USE University
GO

CREATE TABLE Majors(
MajorID INT PRIMARY KEY IDENTITY,
[Name] NVARCHAR(50) UNIQUE NOT NULL)

CREATE TABLE Students(
StudentID INT PRIMARY KEY IDENTITY,
StudentNumber INT UNIQUE NOT NULL,
StudentName NVARCHAR(50) NOT NULL,
MajorID INT FOREIGN KEY REFERENCES Majors(MajorID) NOT NULL)

CREATE TABLE Payments(
PaymentID INT PRIMARY KEY IDENTITY,
PaymentDate DATETIME2 NOT NULL,
PaymentAmount MONEY NOT NULL,
StudentID INT FOREIGN KEY REFERENCES Students(StudentID) NOT NULL)

CREATE TABLE Subjects(
SubjectID INT PRIMARY KEY IDENTITY,
SubjectName NVARCHAR(70) NOT NULL)

CREATE TABLE Agenda(
StudentID INT FOREIGN KEY REFERENCES Students(StudentID) NOT NULL,
SubjectID INT FOREIGN KEY REFERENCES Subjects(SubjectID) NOT NULL,
PRIMARY KEY(StudentID, SubjectID))

--09. *Peaks in Rila

USE Geography

SELECT
    m.MountainRange
    ,p.PeakName
    ,p.Elevation
FROM Mountains AS m
JOIN Peaks AS p ON p.MountainId = m.Id
AND m.MountainRange = 'Rila'
ORDER BY p.Elevation DESC