Data Types
numeric, decimal, smallmoney or money
This SQL clause allows you to list the fields you want to see in the results.
A join type that will pull records from one table that having matching tables on the joining table.
INNER JOIN
SELECT order_id, order_date, product_id
FROM Orders inner join OrderItems on Orders.order_id = OrderItems.order_id results in an "ambigous column error". What is the fix?
What property allows you to auto-generate a numeric primary key?
Identity Specification
A column that stores zip code would be best set to this data type.
varchar or another string data type to help hold leading 0
This SQL clause allows you to show the tables where you are retrieving the data.
FROM
ON
They query, SELECT * FROM Products inner join Brands on Products.product_id = Brands.brand_id, has the wrong results. What is the fix?
the linking field must be the PK/FK that joins both tables. products.brand_id = brands.brand_id
Primary Key
A column that stores date of birth would be best set to this data type.
date
This clause allows you to add criteria to an aggregate function field.
HAVING
FROM Orders Inner Join OrderItems
ON Orders.OrderID = OrderItems.OrderID
SELECT customer_id, COUNT(order_id)
FROM orderitems
results in an error, what is the fix?
Add a group by statement for customer_id.
When joining tables, a primary key on one table will link to what type of field on another table?
Foreign key
A column that is tracking when an order is placed would be best set to this data type.
datetime
This clause allows you to sort your data that is output.
ORDER BY
How do you fix this FROM clause?
FROM Orders, OrderItems
FROM Orders Inner Join OrderItems on Orders.OrderID = OrderItems.OrderID
INSERT INTO Customer (CustLast, CustFirst)
VALUES ('Mouse', 'Mickey','mm@gmail.com') results in the error, why?
column count does not match count of values being input, need to remove email address in values list
A database object that stores data is called a ____.
table
A column that is storing the quantity of pizzas that are ordered would be best set to this data type.
tinyint, (int and small int would get 1/2 points)
This clause is required when using an aggregate function field, but you include non-aggregate fields in your SELECT list.
GROUP BY
What type of fields do you join between tables?
Primary Key and Foreign Key
Want to delete all the customers from OH. Run this statement, DELETE * FROM Customers. What is the problem?
Deletes everything. Need to add a WHERE State = 'OH'
A diagram that illustrates the data model of a database is called ________.
E-R diagram/entity relationship diagram