Midterm Flashcards
Definition: A logical collection of database objects
Schema
What keyword eliminated duplicates?
DISTINCT
What is the maximum number of columns you can ORDER BY?
246
By default, ORDER BY is ________
Ascending
What keyword is used to create an alias?
AS
T/F: SQL is not case sensitive.
TRUE
What is the default format for dates?
YYYY-MM-DDTHH:MM:SS:FF
What function returns the current date timestamp?
GetDate()
What keyword is used to find data from one date to another?
BETWEEN
If you want to avoid using multiple OR statements, what keyword can be use?
IN
ex. WHERE province IN (‘ON’, ‘BC’)
What operator is “not equals”
<> OR !=
Which keyword is used when specifying a pattern?
LIKE
When using LIKE, what does the % mean?
Any number of characters
How do you test for NULL?
The keyword IS
ex. IS NULL
ex. IS NOT NULL
What order are logical operators tested in?
NOT, AND, OR
What is returned if you omit an INNER JOIN or an EQUI-JOIN?
Cartesian Product
ex. FROM products, customers
Why is it bad to use an equality join?
Ambiguity leads to weird results
What keyword can be used to find the cartesian product?
CROSS JOIN
How many INNER JOINs are needed for “n” number of tables?
n-1
What is DML?
Data Manipulation Language
What is DDL?
Data Definition Language
What is DCL?
Data Control Language
When creating tables, how many characters can be in the name?
1-128
Why are you able to make a table without a Primary Key specified?
Artificial Key
What is an artificial key?
A hidden key made up of a physical address (page# + fileid)
What keyword is used to delete a table?
DROP
What keyword is used to make a table?
CREATE
How many characters can a VARCHAR be?
8000
What is a VARCHAR datatype?
Variable length string
What’s the difference between VARCHAR and CHAR?
CHAR adds padding to unused space
How do you specify an apostrophe when specifying a CHAR variable?
Two single quotes, NOT a double quote
How many bits in an INT?
32
How do you get the data dictionary?
INFORMATION_SCHEMA.
What is a data dictionary?
Metadata on the database
How do you create a table with a SELECT statement?
SELECT column INTO new_table FROM existing_table
What are the 3 commands that can be used with ALTER
1) ADD
2) MODIFY
3) DROP
What do constraints do?
Throw exceptions
What are the two ways to specify constraints?
1) Column
2) Table
Which type of constraint can ONLY be specified through a column constraint?
NOT NULL
What are the 5 types of constraints?
1) Primary Key
2) Foreign Key
3) Unique
4) Check
5) NOT NULL
What is the syntax for a primary key table constraint?
CONSTRAINT table_name_column_PK PRIMARY KEY(column)
What is the syntax for a foreign key table constraint?
CONSTRAINT table_name_column_FK FOREIGN KEY (column) REFRENCES refrenced_table (column)
What is the syntax for a unique table constraint?
CONSTRAINT table_name_column_UK UNIQUE(column)
What is the syntax for a check constraint?
CONSTRAINT table_name_column_CK CHECK(boolean check)
What is the syntax for table creation?
CREATE TABLE table_name
(
column_name DATATYPE,
column_name DATATYPE,
CONSTRAINT
);What is the syntax for an INSERT statement?
INSERT INTO table_name(column1, column2…column(n)) VALUES(value1, value2, … value(n))
