WGU c170 The Most Important SQL Commands(SQL study guide)
Notes for C170
Some of The Most Important SQL Commands
SELECT - extracts data from a database
UPDATE - updates data in a database
DELETE - deletes data from
...
WGU c170 The Most Important SQL Commands(SQL study guide)
Notes for C170
Some of The Most Important SQL Commands
SELECT - extracts data from a database
UPDATE - updates data in a database
DELETE - deletes data from a database
INSERT INTO - inserts new data into a database
CREATE DATABASE - creates a new database
ALTER DATABASE - modifies a database
CREATE TABLE - creates a new table
ALTER TABLE - modifies a table
DROP TABLE - deletes a table
CREATE INDEX - creates an index (search key)
DROP INDEX - deletes an index
'*' select all the columns
Example: SELECT * FROM Customers;
'FROM' pulling info from database
'DISTINCT' = Different
Example: SELECT DISTINCT or SELECT FROM
Note: The WHERE clause is not only used in SELECT statements, it is also used in
UPDATE, DELETE, etc.!
***GOOD EXAMPLES:**
SELECT * FROM Customers
WHERE City = 'Berlin'
OR City = 'London';
SELECT * FROM Customers
WHERE Country='Germany' OR Country='Spain';
SELECT * FROM Customers
WHERE Country='Germany' AND (City='Berlin' OR City='München');
SELECT * FROM Customers
WHERE NOT Country='Germany' AND NOT Country='USA';
SELECT * FROM Customers
ORDER BY Country DESC;
SELECT * FROM Customers
ORDER BY Country, CustomerName;
SELECT * FROM Customers
ORDER BY Country ASC, CustomerName DESC;
INSERT INTO Customers (CustomerName, ContactName, Address, City, PostalCode,
Country)
VALUES ('Cardinal', 'Tom B. Erichsen', 'Skagen 21', 'Stavanger', '4006', 'Norway');
INSERT INTO Customers (CustomerName, City, Country)
VALUES ('Cardinal', 'Stavanger', 'Norway');SELECT CustomerName, ContactName, Address
FROM Customers
WHERE Address IS NULL;
SELECT CustomerName, ContactName, Address
FROM Customers
WHERE Address IS NOT NULL;
UPDATE Customers
SET ContactName = 'Alfred Schmidt', City= 'Frankfurt'
WHERE CustomerID = 1;
UPDATE Customers
SET ContactName='Juan'
WHERE Country='Mexico';
Be careful when updating records. If you omit the WHERE clause, ALL records will be
updated!
UPDATE Customers
SET ContactName='Juan';
DELETE FROM Customers
WHERE CustomerName='Alfreds Futterkiste';
SELECT TOP 3 * FROM Customers;
SELECT * FROM Customers
LIMIT 3;
SELECT TOP 50 PERCENT * FROM Customers;
SELECT TOP 3 * FROM Customers
WHERE Country='Germany';
SELECT MIN(Price) AS SmallestPrice
FROM Products;
SELECT MAX(Price) AS LargestPrice
FROM Products;
SELECT COUNT(ProductID)
FROM Products;
SELECT AVG(Price)
FROM Products;
SELECT SUM(Quantity)
FROM OrderDetails;Chapter 1
Key types:
Superkey: An attribute or combination of attributes that uniquely identifies each
row in a table
Candidate key: A minimal (irreducible) superkey; a superkey that does not contain a
subset of attributes that is itself a superkey
Primary key: A candidate key selected to uniquely identify all other attribute
values in any given row; cannot contain null entries
Foreign key: An attribute or combination of attributes in one table whose values
must either match the primary key in another table or be null
Secondary key: An attribute or combination of attributes used strictly for data
retrieval purposes
Entity Integrity:
Requirement: All primary key entries are unique, and no part of a primary key may
be null.
Purpose: Each row will have a unique identity, and foreign key values can properly
reference primary key values.
Example: No invoice can have a duplicate number, nor can it be null; in short, all
invoices are uniquely identified by their invoice number.
Referential Integrity:
Requirement: A foreign key may have either a null entry, as long as it is not a
part of its table's primary key, or an entry that matches the primary key value in
a table to which it is related; (every non-null foreign key value must reference an
existing primary key value).
Purpose: It is possible for an attribute not to have a corresponding value, but it
will be impossible to have an invalid entry; the enforcement of the referential
integrity rule makes it impossible to delete a row in one table whose primary key
has mandatory matching foreign key values in another table.
Example: A customer might not yet have an assigned sales representative (number),
but it will be impossible to have an invalid sales representative (number).
FK = Foreign key
PK = Primary key
CHAR = Fixed character length data (1 - 255 characters)
VARCHAR = Variable character length data (1 - 2,000 characters)
NUMBER = Numeric data. NUMBER (9,2) is used to specify numbers with up to nine
digits, including two digits to the right of the decimal place. Some RDBMS permit
the use of a MONEY or CURRENCY data type.
You already know that relationships are classified as one-to-one (1:1), one-to-many
(1:M), and many-to-many (M:N or M:M). This section explores those relationships
further to help you apply them properly when you start developing database designs.
This section focuses on the following points:
The 1:M relationship is the relational modeling ideal. Therefore, this relationship
type should be the norm in any relational database design.
The 1:1 relationship should be rare in any relational database design.
M:N relationships cannot be implemented as such in the relational model. Later in
this section, you will see how any M:N relationship can be changed into two 1:M
relationships.
Unary: It associates occurrences of the same entity type where one is the parent.
Ternary: It involves three different entity types.
[Show More]
