API tools faq
paste
lena-my

quete TSQL - Les jointures

lena-my
Jul 11th, 2024 (edited)
36
0
Never
Add comment
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
SQL 2.56 KB | None | 0 0
raw download clone embed print report
  1. CREATE DATABASE intergalactique;
  2.  
  3. CREATE TABLE base (
  4.     id INT PRIMARY KEY IDENTITY(1,1),
  5.     name VARCHAR(80) NOT NULL,
  6. );
  7.  
  8. CREATE TABLE continent (
  9.     id INT PRIMARY KEY IDENTITY(1,1),
  10.     name VARCHAR(80) NOT NULL,
  11. );
  12.  
  13. CREATE TABLE terrestrial (
  14.     id INT PRIMARY KEY IDENTITY(1,1),
  15.     name VARCHAR(80) NOT NULL,
  16.     fkContinent INT,
  17.     CONSTRAINT fkTerrestrialContinent
  18.         FOREIGN KEY (fkContinent)
  19.         REFERENCES continent(id)
  20. );
  21.  
  22. CREATE TABLE martian (
  23.     id INT PRIMARY KEY IDENTITY(1,1),
  24.     name VARCHAR(80) NOT NULL,
  25.     fkBase INT,
  26.     CONSTRAINT fkBase
  27.         FOREIGN KEY (id)
  28.         REFERENCES base(id),
  29.     fkTerrestrial INT,
  30.     CONSTRAINT fkmartianTerrestrial
  31.         FOREIGN KEY (fkTerrestrial)
  32.         REFERENCES terrestrial(id)
  33. );
  34.  
  35. -- filling the table base
  36. INSERT INTO base (name)
  37.     VALUES ('base1');
  38.  
  39. INSERT INTO base (name)
  40.     VALUES ('base BABA');
  41.  
  42. INSERT INTO base (name)
  43.     VALUES ('base Luna');
  44.  
  45. INSERT INTO base (name)
  46.     VALUES ('base Lola');
  47.  
  48. -- filling table continent
  49. INSERT INTO continent (name)
  50.     VALUES ('Africa');
  51.  
  52. INSERT INTO continent (name)
  53.     VALUES ('North America');
  54.  
  55. INSERT INTO continent (name)
  56.     VALUES ('Europe');
  57.  
  58. INSERT INTO continent (name)
  59.     VALUES ('Asia');
  60.  
  61. INSERT INTO continent (name)
  62.     VALUES ('South America');
  63.  
  64. INSERT INTO continent (name)
  65.     VALUES ('Central America');
  66.  
  67. INSERT INTO continent (name)
  68.     VALUES ('Oceania');
  69.  
  70. -- filling terrestrial table
  71. INSERT INTO terrestrial (name, fkContinent)
  72.     VALUES ('Tata', 2);
  73.  
  74. INSERT INTO terrestrial (name, fkContinent)
  75.     VALUES ('Tete', 1);
  76.  
  77. INSERT INTO terrestrial (name, fkContinent)
  78.     VALUES ('Titi', 3);
  79.  
  80. INSERT INTO terrestrial (name, fkContinent)
  81.     VALUES ('Toto', 5);
  82.  
  83. INSERT INTO terrestrial (name, fkContinent)
  84.     VALUES ('Tutu', 4);
  85.  
  86. -- filling martian table
  87. INSERT INTO martian (name, fkBase, fkTerrestrial)
  88.     VALUES ('Gogo', 2, 4);
  89.  
  90. INSERT INTO martian (name, fkBase, fkTerrestrial)
  91.     VALUES ('Gaga', 1, 2);
  92.  
  93. INSERT INTO martian (name, fkBase, fkTerrestrial)
  94.     VALUES ('Gigi', 3, 1);
  95.  
  96. INSERT INTO martian (name, fkBase, fkTerrestrial)
  97.     VALUES ('Gugu', 1, 3);
  98.  
  99.  
  100. -- joins
  101.  
  102. SELECT martian.id, martian.name AS martianName, base.name AS baseName, terrestrial.name AS terrestrialReferent, continent.name AS terrestrialContinent
  103.     FROM martian
  104.     INNER JOIN base
  105.         ON base.id = martian.fkBase
  106.     INNER JOIN terrestrial
  107.         ON martian.fkTerrestrial = terrestrial.id
  108.     INNER JOIN continent
  109.         ON continent.id = terrestrial.fkContinent;
Add Comment
Please, Sign In to add comment
Public Pastes
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy.  OK, I Understand
Not a member of Pastebin yet?
Sign Up, it unlocks many cool features!