¡GoConqr estrena diseño! Cuéntanos qué te parece.

Regístrate gratis

Fichas

Advanced SQL

L H SQL Advanced Alemán 28 fichas

Fichas del conjunto (28)

  1. 1Equi Join(=Inner join) Joining condition is based on equality
  2. 2What are the customer IDs and names of all customers, along with the order IDs for all the orders they have placed?SELECT Customer_T.CustomerID, Customer_T.CustomerName, OrderID
    FROM Customer_T, Order_T
    WHERE Customer_T.CustomerID = Order_T. CustomerID
    ORDER BY Order ID
  3. 3Cartesian joinCROSS JOIN
    Display all rows of both tables
  4. 4Inner joinEstablishes an equi join in the FROM clause
  5. 5Inner JoinSELECT Customer_T.CustomerID, Order_T. CustomerID, CustomerName, OrderID
    FROM Customer_T INNER JOIN Order_T
    ON Customer_T.CustomerID= Order_T.CustomerID
    ORDER BY OrderID;
  6. 6Natural Join=Inner join but duplicate column is eliminated
  7. 7Natural joinSELECT Customer_T.CustomerID, CustomerName, OrderID
    FROM Customer_T NATURAL JOIN Order_T ON Customer_T.CustomerID = Order_T.CustomerID ORDER BY Customer ID
  8. 8Outer joinJoins rows which do not have matching values
  9. 9List customer name, ID, and Order ID for all customers listed in the Customer table. Even if there is no order availableSELECT CustomerName, Customer_T.CustomerID, OrderID
    FROM Customer_T LEFT OUTER JOIN Order_T WHERE Customer_T.CustomerID = Order_T.CustomerID
  10. 10List customer name, identification number and order number for all orders listed in order table, include the order table even if there is no customer nameSELECT CustomerName, Customer_T.CustomerID, OrderID
    FROM Customer_T RIGHT OUTER JOIN Order_T WHERE Customer_T.CustomerID = Order_T.CustomerID
  11. 11Assemble all information necessary to create an invoice for order number 1006SELECT Customer_T.CustomerID, CustomerName, , Order_T.OrderID, Order Date, ProductDescription, ProductID FROM Customer_T, Order_T, OrderLine_T, Product_T
    WHERE Order_T.CustomerID = Customer_T.CustomerID
    AND Order_T.OrderID = OrderLine_T.OrderID
    AND OrderLine_T.ProductID = Product_T.ProductID
    AND Order_T.OrderID = 1006;
  12. 12SELF JoinMatching rows in the same table
  13. 13What are the employee ID and name of each employee and the name of his supervisorSELECT E.EmployeeID, E.EmployeeName, M.EmployeeName AS Manager
    FROM Employee_T E, Employee_T M
    WHERE E.EmployeeSupervisor= M.EmployeeName;
  14. 14What are the name and address of the customer who placed order number 1008SELECT CustomerName, CustomerAddress, CustomerCity, CustomerState FROM Customer_T WHERE (SELECT Order_T.CustomerID FROM Order_T WHERE OrderID= 1008);
  15. 15Names of Customers who have placed an orderSELECT CustomerName FROM Customer_T WHERE Customer ID IN (SELECT DISTINCT Order_T.CustomerID FROm Order_T);
  16. 16Names of customers who have not placed any order for computer desksSELECT CustomerName FROM Customer_T WHERE Customer ID NOT IN (SELECT Order_T.CustomerID FROM Order_T, OrderLine_T, Product_T WHERE Order_T.OrderID = OrderLine_T.OrderID AND OrderLine.ProductID = Product_T.ProductID AND ProductDescription ='Computer Desk');
  17. 17What are the order IDs for all orders that have included furniture finished in natural ash?SELECT DISTINCT OrderID FROM OrderLine_T WHERE EXISTS (SELECT * FROM Product_T WHERE Product_T. ProductID = OrderLine_T.ProductID AND ProductFinish = 'Natural Ash');
  18. 18Correlated subqueryInner query depends on outer query
  19. 19List the details about the products with the highest standard priceSELECT ProductDescription, ProductFinish, ProductStandardPrice FROM Product_T PA WHERE PA.ProductStandardPrice > ALL (SELECT ProductStandardPrice FROM Product_T PB WHERE PB.ProductID != PA.ProductID);
  20. 20Derived TablesSubqueries in FROM clause references in SELECT & WHERE
  21. 21Display all products where Product StandardPrice > AvgPriceSELECT ProductDescription, ProductStandardPrice FROM (SELECT AVG (ProductStandardPrice) AvgPrice FROM Product_T), Product_T
    WHERE ProductStandardPrice> Avg Price;
  22. 22Combining queryUse UNION
    Conditions: same data type, same grain
  23. 23Select Customers with the highest number of goods purchasedSELECT Customer_T.CustomerID, Customer Name, Ordered Quantity, 'Largest Quantity' AS Quantity FROM Customer_T C1, Order_T O1, OrderLine_T Q1 WHERE LINKS tables AND Ordered Quantity = (SELECT MAX (Ordered Quantity) FROM OrderLine_T)
  24. 24Select Customers with the lowest number of goods purchasedSELECT Customer_T.CustomerID, Customer Name, Ordered Quantity, 'Largest Quantity' AS Quantity FROM Customer_T C1, Order_T O1, OrderLine_T Q1 WHERE LINKS tables AND Ordered Quantity = (SELECT MIN (Ordered Quantity) FROM OrderLine_T)
  25. 25Select Customers with the highest and lowest number of goods purchasedHighest
    UNION
    Lowest
  26. 26Conditional expressions (Tableau)CASE .... WHEN ... THEN...ELSE
  27. 27TriggerSet of SQL statements that are triggered when a data modification occurs
    *Powerful to prevent unauthorized change, takes action based on event evaluation, automatic, can CASCADE to other triggers)
  28. 28Routines*Must be explicitly called
    + flexibility: can be used in more situations than triggers
    +efficiency: run faster
    +applicability: might apply to whole database instead of only application

Información

Descripción
SQL Advanced Fichas sobre Advanced SQL, creado por L H el 20/12/2016.
Creado por
L H
Visitas
2
Valoración

Escríbenos

¿Sobre qué es tu mensaje?

¿Qué te parece el nuevo diseño?

¿Te gusta?