WGU c170 The Most Important SQL Commands(SQL study guide). Notes for C170

Document Content and Description Below

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]

Last updated: 3 years ago

Preview 1 out of 8 pages