By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0. 3601434 fiddles created (48008 in the last week).
CREATE TABLE Company (
CompanyId INT NOT NULL,
Name TEXT NOT NULL,
FoundingDate DATE NOT NULL,
Etcetera TEXT NOT NULL,
CreatedDateTime TIMESTAMP NOT NULL DEFAULT NOW(),
--
CONSTRAINT Company_PK PRIMARY KEY (CompanyId),
CONSTRAINT Company_AK UNIQUE (Name)
);
CREATE TABLE Person (
PersonId INT NOT NULL,
FirstName TEXT NOT NULL,
LastName TEXT NOT NULL,
GenderCode TEXT NOT NULL,
BirthDate DATE NOT NULL,
BirthPlace TEXT NOT NULL,
Address TEXT NOT NULL,
PhoneNumber TEXT NOT NULL,
CreatedDateTime TIMESTAMP NOT NULL DEFAULT NOW(),
--
CONSTRAINT Person_PK PRIMARY KEY (PersonId),
CONSTRAINT Person_AK UNIQUE (
FirstName,
LastName,
GenderCode,
BirthDate,
BirthPlace
)
);
CREATE TABLE CompanyOwner (
CompanyId INT NOT NULL,
OwnerId INT NOT NULL,
StockShareQuantity INT NOT NULL,
Etcetera TEXT NOT NULL,
CreatedDateTime TIMESTAMP NOT NULL DEFAULT NOW(),
--
CONSTRAINT CompanyOwner_PK PRIMARY KEY (CompanyId, OwnerId),
CONSTRAINT CompanyOwnerToCompany_FK FOREIGN KEY (CompanyId)
REFERENCES Company (CompanyId),
CONSTRAINT CompanyOwnerToPerson_FK FOREIGN KEY (OwnerId)
REFERENCES Person (PersonId),
CONSTRAINT StockShareQtyIsValid_CK CHECK (StockShareQuantity >= 0)
);
CREATE VIEW CompanyAndOwner AS
SELECT C.CompanyId,
C.Name AS CompanyName,
P.PersonId,
P.FirstName,
P.LastName,
P.BirthDate,
P.BirthPlace,
P.GenderCode,
P.Address,
P.PhoneNumber,
CO.StockShareQuantity,
P.CreatedDateTime
FROM Person P
JOIN CompanyOwner CO
ON CO.OwnerId = P.PersonId
JOIN Company C
ON C.CompanyId = CO.CompanyId;
✓
✓
✓
✓
hidden batch(es)
INSERT INTO Company
(CompanyId, Name, FoundingDate, Etcetera)
VALUES
(1748, 'Database Modeling Inc.', '1985-06-30', 'Foo'),
(1750, 'Application Programming Co.', '1987-10-14', 'Bar');
INSERT INTO Person
(PersonId, FirstName, LastName, BirthDate, BirthPlace, GenderCode, Address, PhoneNumber)
VALUES
(1, 'Edgar', 'Codd', '1923-08-19', 'Fortuneswell, UK', 'M', 'IBM Research Laboratory K01/282, 5600 Cottle Road, San Jose, CA, USA', '01-800-17-50-17-50'),
(2, 'Alan', 'Turing', '1912-06-23', 'Maida Vale, UK','M', 'National Physical Laboratory, Hampton Road, Teddington, TW11 0LW, England', '01-800-17-48-17-48'),
(3, 'Grace', 'Hopper', '1906-12-09', 'New York City, USA', 'F', 'Navy’s Office of Information Systems Planning, USA.', '01-800-17-50-17-50'),
(4, 'Diego', 'Velázquez', '1599-06-06', 'Seville, Spain', 'M', 'Palacio Real, Madrid, Spain', '01-800-17-50-17-50'),
(5, 'Michelangelo', 'Buonarroti', '1475-03-06', 'Caprese, Italy', 'M', 'Sistine Chapel, Vatican City State', '01-800-17-50-17-50');
INSERT INTO CompanyOwner
(CompanyId, OwnerId, StockShareQuantity, Etcetera)
VALUES
(1748, 1, 2500, 'V'),
(1750, 2, 8000, 'W'),
(1750, 3, 3580, 'X'),
(1748, 4, 12899, 'Y'),
(1750, 5, 12899, 'Z');
2 rows affected
5 rows affected
5 rows affected
hidden batch(es)
SELECT *
FROM CompanyAndOwner
WHERE CompanyId = 1750;
companyid
companyname
personid
firstname
lastname
birthdate
birthplace
gendercode
address
phonenumber
stocksharequantity
createddatetime
1750
Application Programming Co.
2
Alan
Turing
1912-06-23
Maida Vale, UK
M
National Physical Laboratory, Hampton Road, Teddington, TW11 0LW, England
01-800-17-48-17-48
8000
2022-05-07 03:40:37.03482
1750
Application Programming Co.
3
Grace
Hopper
1906-12-09
New York City, USA
F
Navy’s Office of Information Systems Planning, USA.
01-800-17-50-17-50
3580
2022-05-07 03:40:37.03482
1750
Application Programming Co.
5
Michelangelo
Buonarroti
1475-03-06
Caprese, Italy
M
Sistine Chapel, Vatican City State
01-800-17-50-17-50
12899
2022-05-07 03:40:37.03482
…
hidden batch(es)
SELECT CompanyName,
FirstName AS OwnerFirstName,
LastName AS OwnerLastName,
StockShareQuantity
FROM CompanyAndOwner
WHERE CompanyId = 1750;
companyname
ownerfirstname
ownerlastname
stocksharequantity
Application Programming Co.
Alan
Turing
8000
Application Programming Co.
Grace
Hopper
3580
Application Programming Co.
Michelangelo
Buonarroti
12899
…
hidden batch(es)
SELECT *
FROM CompanyAndOwner
WHERE CompanyId = 1748;
companyid
companyname
personid
firstname
lastname
birthdate
birthplace
gendercode
address
phonenumber
stocksharequantity
createddatetime
1748
Database Modeling Inc.
1
Edgar
Codd
1923-08-19
Fortuneswell, UK
M
IBM Research Laboratory K01/282, 5600 Cottle Road, San Jose, CA, USA
01-800-17-50-17-50
2500
2022-05-07 03:40:37.03482
1748
Database Modeling Inc.
4
Diego
Velázquez
1599-06-06
Seville, Spain
M
Palacio Real, Madrid, Spain
01-800-17-50-17-50
12899
2022-05-07 03:40:37.03482
…
hidden batch(es)
SELECT CompanyName,
FirstName AS OwnerFirstName,
LastName AS OwnerLastName,
StockShareQuantity
FROM CompanyAndOwner
WHERE CompanyId = 1748;
companyname
ownerfirstname
ownerlastname
stocksharequantity
Database Modeling Inc.
Edgar
Codd
2500
Database Modeling Inc.
Diego
Velázquez
12899
…
hidden batch(es)
SELECT COUNT(OwnerId) AS OwnedCompaniesQuantity
FROM CompanyOwner
WHERE OwnerId = 1;