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 PRODUCTID 25)
ROWS
A hor
...
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 PRODUCTID 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]