[S12L3] Foreign Keys Flashcards
Was ist ein Foreign Key?
-Ein Schlüssel in einem Table, welcher auf einen PrimaryKey eines anderen Tables referenziert
Können Tables in der DB mehrere oder auch keien Foreign Keys besitzen?
-Ja
Was ist ein Join?
- Eine Query um zusammengehörige Daten aus mehreren Tables zu erhalten
- Datensätze mit ForeignKeys aus mehreren Tables werden verbunden
Wie schreibt man einen Join?
-Alles was man joined muss auf den ersten Table auf den man selected linken.
SELECT OrderID, CustomerName, OrderDate FROM Orders AS O JOIN Customers AS C ON O.CustomerId = C.CustomerId Join Shippers AS S ON O.ShipperId = S.ShipperID;
Was ist eine INNER JOIN?
- Der JOIN selected nur Datensätze aus den Tables bei welchen der zusammenhängende Daten findet
- Eine Order die keinen Customer zugehört wird nicht aufgelistet werden
Was ist ein LEFT JOIN?
-Ein JOIN bei welchem alle Daten aus dem ersten SELECT aufgelistet werden, egal ob diese einen zugehörige Order aus dem zweiten JOIN Table haben
Welche JOINS werden am meisten genutzt?
- INNER JOIN (alle Daten die in beiden Tables gelinked sind und zugehörige Primarykeys haben, aber manche Daten werden nicht aufgelistet)
- LEFT JOIN (Alle Daten aus erster Table egal ob zugehörige in zweitem JOIN Table sind und die welche gejoined werden können)
Wie macht man JOINS in knex?
knex(‘users’).join(‘contacts’, ‘user.id’, ‘=’, ‘contacts.user_id’).select(‘user.id’, ‘contacts.phone’)
Was sind database access methods?
-
Wie sieht ein JOIN aus?
--Foreign Key SELECT SupplierName, ProductName FROM Products AS P INNER JOIN Suppliers AS S ON P.supplierId = S.supplierId ORDER BY SupplierName
select suppliers.SupplierName , categories.CategoryName, products.ProductName
from products inner
join suppliers on products.SupplierID = suppliers.SupplierID
join categories on products.CategoryID = categories.CategoryID
order by supplierName
--Foreign Key SELECT p.ProductName, c.CategoryName FROM Products as p INNER JOIN Categories as c ON p.CategoryID = c.CategoryID ORDER BY p.ProductName
Warum speichert man nicht alle Daten der Datenbank in einem großen Table?
- Viele redundante Daten wie die Herstellerdaten vieler Produkte hunderte Male vorhanden
- Wenn man die Adresse des Hersteller ändern möchte muss man nun hunderte von Datensätzen ändern anstatt nur eine
Wie sieht ein JOIN mit ALIAS aus?
select s.SupplierName , c.CategoryName, p.ProductName
from products AS P inner
join suppliers AS S on p.SupplierID = s.SupplierID
join categories AS C on p.CategoryID = c.CategoryID
order by supplierName
Gibt es in SQL zwischen distinct s.supplierid und distinct(s.supplierid) einen Unterschied?
-Nein, man kann distinct als Function oder Keyword benutzen
Welcher ist der Left (1.)Table bei einem JOIN?
-Der nach dem FROM
Warum ist das Denken in Datenbanken schwierig?
- Weil man immer im Sets denkt und nicht in Objects oder Variablen
- Man erhält immer Collections zurück und muss mit VENN Logik die richtigen Subsets herauslesen
Wie kann man aus der DB das günstigste Product erhalten?
SELECT ProductId, MIN(Price) FROM [Products]
Select * FROM products
order by price
limit 1
Was ist SQL GROUP BY?
- Reducing der doppelten Einträge als Collections
- Nehme eine Collection und alle die du davon findet mache darauf eine Operation und gebe mir nur das Ergebnis für die Collection
Wie kann man in SQL den Kategorienamen des günstigten Produktes finden?
SELECT CategoryName, min(Price) AS Cheapest FROM Categories AS C INNER JOIN Products AS P ON c.categoryID = p.categoryID GROUP BY CategoryName
SELECT CategoryName, max(Price) AS Priciest FROM Categories AS C INNER JOIN Products AS P ON c.categoryID = p.categoryID GROUP BY CategoryName
Wie erhält man in SQL die total Quantity per OrderID und davon die höchsten 5?
-- Total Quantity per ORDER ID höchsten 5 SELECT OrderID, sum(Quantity) AS Sum FROM OrderDetails GROUP BY OrderID order by sum desc limit 5
Was ist ein Problem mit SQLite?
-ForeignKeys werden nicht direkt enforced, sondern es muss explizit erwähnt werden in der knexfile
-Ab dann werden ForeignKeys contrained und keine inkonsistenten Daten mehr erlaubt zu speichern
pool: {
afterCreate: (conn, done) => {
// runs after a connection is made to the sqlite engine
conn.run(‘PRAGMA foreign_keys = ON’, done); // turn on FK enforcement
},
},
Was ist wenn die functionalitäten von knex nicht ausreichen und man spezielle SQL schreiben will?
-Es gibt joinRaw, whereRaw etc bei welchen man besondere Anweisungen definieren kann
Was ist der größte Angriffspunkt von Datenbanken und den meisten IT-Systemen?
-SQL Injection, welche entstehen wenn man Queries konkateniert
-Knex Query Builder schützt und größtenteils davor und geben einen Alert wenn man eine Query schreibt die anfällig ist
-ORMs können ebenfalls genutzt werden wenn man mehr und volle Funktionalitäten für SQL haben möchte
(Sequelizejs)
-Query Builder sind dabei eher der Sweet Spot zwischen Funktionalität und Schutz
