WGU C170, Top Exam Question with answers, exam preview, Pass Easy with this Guide. SELECT Statement

Document Content and Description Below

WGU C170, Top Exam Question with answers, exam preview, Pass Easy with this Guide. SELECT Statement SELECT * (SELECT ALL) FROM products; (TABLE NAME) WHERE productId = 25 (SELECT ONLY PRODUC... TID 25) ROWS A horizontal line of cells in a table. ROWS = RECORDS,TUPLES COLUMNS columns = fields, attributes fields/attributes are the categories used to define the data within the record(row). DATABASE Composed of tables, and tables are composed of records. Relational Database A database that represents data as a collection of tables in which all data relationships are represented by common values in related tables Primary Key acts as the unique identifier for a record in the table. Foreign key a field within a table that is acting as a primary key in another table in the database. Composite key A primary key that consists of more than one attribute. Boolean Values A variable to represent true or false. RDBMS Relational Database Management System ex: MySQL SQLite ORACLE PostgreSQL Comments to Queries -- or Start with / ending with / Alias ( AS) SQL aliases are used to give a table, or a column in a table, a temporary name. Aliases are often used to make column names more readable. An alias only exists for the duration of that query. ex: SELECT FirstName AS 'First Name', LastName AS 'Last Name', Email AS 'Contact Email' FROM Customers; ORDER BY clause The clause that allows query results to be sorted in a specified order. ex: SELECT FirstName AS [First Name], LastName AS [Last Name], FROM customers ORDER BY LastName ASC Using LIMIT SELECT FirstName AS [First Name], LastName AS [Last Name], FROM customers ORDER BY LastName ASC LIMIT 10 Arthimetic operators SELECT Total AS [Original Amount], Total + 10 AS [Addition Operator], Total - 10 AS [Substraction Operator], Total / 10 AS [Division Operator], Total * 10 AS [Multiplication Operator], Total % 10 AS [Modulo Operator] FROM invoices ORDER BY Total DESC BETWEEN clause selects values within a given range. The values can be numbers, text, or dates. ex: SELECT InvoiceDate, BillingAddress, BillingCity, total FROM invoices WHERE Total BETWEEN 1.98 AND 5.00 ORDER BY InvoiceDate <= operator SELECT InvoiceDate, BillingAddress, BillingCity, total FROM invoices WHERE Total <= 5.00 ORDER BY InvoiceDate WHERE clause WHERE clause always comes after the FROM but before the ORDER BY. SELECT InvoiceDate, BillingAddress, BillingCity, total FROM invoices WHERE Total =1.98 ORDER BY InvoiceDate IN operator is used to find a list of precise values. ex: SELECT InvoiceDate, BillingAddress, BillingCity, total FROM invoices WHERE Total IN (1.98, 3.96) ORDER BY InvoiceDate Filtering Records by Text ex: 1 SELECT InvoiceDate, BillingAddress, BillingCity, total FROM invoices WHERE BillingCity = 'Tucson' ORDER BY Total ex: 2 SELECT InvoiceDate, BillingAddress, BillingCity, total FROM invoices WHERE BillingCity IN ('Tucson', 'Paris', 'London') ORDER BY Total LIKE operator - It allows you to specify partial character strings in a "wildcard" sense. SELECT InvoiceDate, BillingAddress, BillingCity, total FROM invoices WHERE BillingCity LIKE 't%' ORDER BY Total Filtering Records by Date SELECT InvoiceDate, BillingAddress, BillingCity, total FROM invoices WHERE InvoiceDate = '2009-01-03 00:00:00' ORDER BY Total Using the AND Operators with Two Separate Fields SELECT InvoiceDate, BillingAddress, BillingCity, total FROM invoices WHERE DATE(InvoiceDate) > '2010-01-02' AND Total < 3 ORDER BY Total Using the OR Operators with Two Separate Fields SELECT InvoiceDate, BillingAddress, BillingCity, total FROM invoices WHERE BillingCity LIKE 'p%' OR BillingCity LIKE 'd%' ORDER BY Total Using Parentheses with AND and OR to Specify the Order of Operations SELECT InvoiceDate, BillingAddress, BillingCity, total FROM invoices WHERE Total > 1.98 AND (BillingCity LIKE 'p%' OR BillingCity LIKE 'd%') ORDER BY Total CASE Statement allows you to create a new, temporary field in your database that serves as a label for your data based on unique user-specified conditions. ex: 1 SELECT InvoiceDate, BillingAddress, BillingCity, total, CASE WHEN TOTAL < 2.00 THEN 'Baseline Purchase' WHEN TOTAL BETWEEN 2.00 AND 6.99 THEN 'Low Purchase' WHEN TOTAL BETWEEN 7.00 AND 15.00 THEN 'Target Purchase' ELSE 'Top Performers' END AS PurchaseType FROM invoices ORDER BY BillingCity ex: 2 In this example, we want to result in an order by 'Top Performers". SELECT InvoiceDate, BillingAddress, BillingCity, total, CASE WHEN TOTAL < 2.00 THEN 'Baseline Purchase' WHEN TOTAL BETWEEN 2.00 AND 6.99 THEN 'Low Purchase' WHEN TOTAL BETWEEN 7.00 AND 15.00 THEN 'Target Purchase' ELSE 'Top Performers' END AS PurchaseType FROM invoices WHERE PurchaseType = 'Top Performers' ORDER BY BillingCity How many invoices from Domestic Sales were over $15? SELECT InvoiceDate, BillingAddress, BillingCity, BillingCountry, Total, CASE WHEN BillingCountry = 'USA' THEN 'Domestic Sales' ELSE 'ForeignSales' END AS SalesType FROM invoices Where SalesType = "Domestic Sales" AN [Show More]

Last updated: 2 years ago

Preview 1 out of 40 pages