GoConqr has a new look! Let us know what you think.

Sign up free

Flashcards

SQL

L H SQL German 43 cards

Cards in this deck (43)

  1. 1CatalogSet of schemas
  2. 2character attributesCHAR
  3. 3attributes with varying lengthVARCHAR2
  4. 4IntegerINT
  5. 5Creating a table and columnsCREATE TABLE Customer_T
    (CustomerID NUMBER (11,0) NOT NULL
    Customer Name VARCHAR2 (25) NOT NULL
    Customer Address VARCHAR (25) etc.
  6. 6Adding a PKCONSTRAINT Order_PK PRIMARY KEY (Order ID)
  7. 7Adding a FKCONSTRAINT Order_FK1 FOREIGN KEY (Customer ID) REFERENCES Customer_T (Customer ID)
    CONSTRAINT Order_FK2 FOREIGN KEY (Product ID) REFERENCES Product_T (Product ID)
  8. 8Adding a CHECK constraintChecks if added value is within pre-defined range
    CHECK ProductFinish IN ('Cherry', 'Natural_Ash')
  9. 9Adding a column to a tableALTER TABLE
    ADD COLUMNS Customer Type VARCHAR2 (10) DEAFAULT "Commercial";
  10. 10Erasing created columnALTER TABLE
    DROP COLUMN Customer Type
  11. 11Removing tablesDROP TABLE Customer_T
    can be qualified further by restrict
  12. 12Eliminate data but keep original structureTRUNCATE
  13. 13Inserting valuesINSERT INTO Customer_T (c1, c2, c3)
    VALUES (001, 'Contemporary Casuals' etc)
  14. 14Inserting values where some values will be NULLEither define columns or enter "NULL"
  15. 15Populate table by using subset of another tableINSERT INTO CaCustomer_T
    SELECT * FROM Customer_T
    WHERE CustomerState='CA'
  16. 16Increment statementCREATE TABLE Customer_T
    (CustomerID INTEGER
    GENERATED ALWAYS AS IDENTITY
    (START WITH 1 INCREMENT BY 1
    MINVALUE 1, MAXVALUE 10000
    NO CYCLE) + rest
  17. 17Deleting rows (criteria) in TableDELETE FROM Customer_T
    WHERE CustomerID= '1234'
  18. 18Delete all rowsDELETE FROM Customer_T
  19. 19Update Database contentUPDATE Product_T
    SET StandardPrice = 18276
    WHERE ProductID= 6;
  20. 20Update & insert in one stepMERGE INTO Product_T AS PROD USING (SELECT attributes) AS PURCH ON (PROD.ProductID= PURCH.ProductID)
    WHEN MATCHED THEN UPDATE
    PROD.ProductStandardPrice =
    PURCH. ProductStandardPrice
    WHEN NOT MATCHED THEN INSERT VALUES (etc.)
  21. 21creating indexesCREATE INDEX ABC_INDX ON Customer_T (Customer Name)
  22. 22Which products have a standard price higher than 275?SELECT ProductDescription, ProductStandardPrice
    FROM Product_T
    WHERE StandardPrice>275;
  23. 23Avoiding duplicate valuesSELECT DISTINCT column name
  24. 24select all (also duplicates)SELECT *
  25. 25What are Standard Price and standard price if increased by 10%?SELECT StandardPrice, StandardPrice * 1.1 AS plus 10%
    FROM Product_T,
  26. 26String manipulationsLOWER
    INITCAP
    UPPER
    CONCAT (concatenate)
  27. 27Average standardprice of all products?SELECT AVG (StandardPrice) AS Average Price
    FROM Product_T
  28. 28How many different items where ordered on order number 1004SELECT COUNT (*)
    FROM Order_Line_T
    WHERE OrderID=1004;
  29. 29SELECT ProductStandardPrice - AVG (ProductStandardPrice) FROM Product_TProblematic: AGG
    SELECT Product StandardPrice - Average Price AS Difference
    FROM Product_T, SELECT AVG(ProductStandardPrice) AS Average Price FROM Product_T);
  30. 30Alphabetical order with MIN/ MAX First Product Name in the product table?SELECT MIN (ProductDescription)
    FROM Product_T;
  31. 31Which orders have been placed since 10/24/2015?SELECT OrderID; OrderDate
    FROM Order_T
    WHERE OrderDate> '24-OCT_2015';
  32. 32What furniture does Pine Valley carry that isnt made of Cherry?SELECT ProductDescription, ProductFinish
    FROM Product_T
    WHERE ProductFinish != 'Cherry';
  33. 33USING NULL valuesSELECT * FROM Customer_T WHERE PostalCode IS NULL;
  34. 34Boolean HierarchyAND processed before OR
  35. 35Standard price between 200 and 300SELECT ProductID, StandardPrice
    FROM Product_T
    WHERE StandardPrice>=200 AND StandardPrice=<300;
  36. 36List all customers who live in warmer statesSELECT CustomerID, CustomerName
    FROM Customer_T
    WHERE CustomerState IN ('FL', 'TX', 'CA')
  37. 37ORDER BYascending / descending order
  38. 38GROUP BYUsed in combination with aggregates group values based on a condition HAVING
  39. 39Customers per stateORDER BY CustomerState, CustomerName
  40. 40Count number of customers with addresses in each state to which we shipSELECT CustomerState COUNT (Customer State)
    FROM Customer_T
    GROUP BY Customer State
  41. 41Find only those states with more than one customerSELECT CustomerState, COUNT (CustomerState)
    FROM Customer_T
    GROUP BY CustomerState
    HAVING COUNT (CustomerState) >1;
  42. 42List Finish, AVG SP for each finish having an AVP below 750SELECT ProductFinish, AVG (ProductStandardPrice)
    FROM Product_T
    WHERE ProductFinish IN ('Cherry' etc) GROUP BY ProductFinish
    HAVING AVG (SP) <750
    ORDER BY Product Finish;
  43. 43—

Information

Description
SQL Flashcards on SQL, created by L H on 20/12/2016.
Created by
L H
Views
1
Rating

Feedback

What is it about?

What do you think of the new look?

How do you like it?