clear markdown compare help best fiddles feedback dbanow.uk a leap of faith?
clear markdown donate comments/suggestions/bugs a leap of faith? bible365 diddy dollings
By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0. 984296 fiddles created (11703 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 2017-10-04 21:49:32.925342
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 2017-10-04 21:49:32.925342
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 2017-10-04 21:49:32.925342
 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 2017-10-04 21:49:32.925342
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 2017-10-04 21:49:32.925342
 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;
ownedcompaniesquantity
1
 hidden batch(es)