Session 2 - Creating, querying data Flashcards
What is the syntax for adding a column to the end of a table?
ALTER TABLE tableName
ADD columnName;
What is the syntax for updating the column type?
ALTER TABLE tableName
ALTER COLUMN columnName datatype
What is the DELETE Syntax?
DELETE
FROM
WHERE
Syntax for adding Unique constraint to column
Alter table MyTable
ADD CONSTRAINT nameOfConstraint UNIQUE (columns)
Syntax for adding a Default Constraint to a new table
columnName dataType (not) null CONSTRAINT constraintName DEFAULT defaultValue
Syntax for adding a Default constraint to an existing column
ALTER TABLE tblName
ADD CONSTRAINT constraintName DEFAULT defaultValue FOR columnName
Syntax for adding a check constraint but to not check the previous rows to verify that they validate the check
ALTER TABLE myTable WITH NOCHECK
ADD CONSTRAINT myConstraint CHECK (col1 = ‘test’)
What is an identity?
Used with PRIMARY KEY and will automatically set a number value. IDENTITY(startingValue, incrementValue)
What is a Primary Key?
Identifying value of a table. It will cluster (order) the table based on this value.
Not Nullable
One Per table
–Surrogate keys are typically numbers that have nothing to do with real life
–Natural Key (Such as Email Address) are naturally keys
Syntax for creating Primary Key
CREATE TABLE tableName (
employeeNumber int CONSTRAINT constraintName PRIMARY KEY IDENTITY(startingValue, incrementValue)
)
Difference between DELETE TABLE and TRUNCATE TABLE regarding Primary Key IDENTITY
DELETING all records allows IDENTITY to keep the next value and TRUNCATE TABLE starts the Identity over
Syntax for adding Primary Key to existing column
ALTER TABLE tableName
ADD CONSTRAINT constraintName PRIMARY KEY (columnName)
You can add NONCLUSTERED if you don’t want it to be clustered (ordered)
ALTER TABLE tableName
ADD CONSTRAINT constraintName PRIMARY KEY NONCLUSTERED (columnName)
What are the effects that you can set when changing a primary key that has foreign keys attached?
NO ACTION
CASCADE
SET NULL
SET DEFAULT
what is NO ACTION for Primary Key change?
Nothing will change in the foreign keys referencing the primary key. Raises error
what is CASCADE for Primary Key change?
Changes will reflect on the foreign keys columns
what is SET NULL for Primary Key change?
The foreign key column will be set to null
what is SET DEFAULT for Primary Key change?
It will be set to the default value specified
How do you produce Sub Total Rows from query?
SELECT COL1, COL2, COL3
FROM TABLE
GROUP BY ROLLUP(COL1, COL2, COL3)
How do you show if a column is grouped in a ROLLUP row?
SELECT COL1, COL2, COL3, GROUPING(COL1) AS COL1_GRP
FROM TABLE
GROUP BY ROLLUP(COL1, COL2, COL3)
WILL SHOW 1 IF COLUMN
What are the two types of primary keys?
Surrogate keys are typically numbers that have nothing to do with real life Natural Keys (Such as Email Address) are naturally keys
How can you tell which columns are being grouped when using ROLLUP, CUBE OR GROUPING SETS?
USE GROUPING_ID(COL1, COL2, COL3)
The return will be binary from right to left:
COL3 grouped would be 1,
COL2 - 2
COL1 - 4
if COL1 and COL3 were grouped then the return value would be 5.
How can you group according to sets specified?
use GROUP BY GROUPING SETS((COL1, COL2, COL3), (COL2, COL3), ())
*() denotes all columns
What is the SQL-92 Syntax for joins?
That is the currently accepted standard:
SELECT *
FROM TABLE1 A
JOIN TABLE2 B
ON A.COL1 = B.COL1
What is the SQL-89 Syntax for Joins
That is the older, legacy join syntax:
SELECT *
FROM TABLE1 A, TABLE2 B
WHERE A.COL1 = B.COL1
What are the benefits of using SQL-92 for joins over SQL-89?
SQL-92 syntax is easier to understand
SQL-92 is the current best practice
SQL-92 is less error prone
Is SQL-92 more efficient than SQL-89 for joining two or more tables?
No, there is no proven efficiency and it is no less code than SQL-89.
