How do you encrypt a view and what should you make sure you do before you encrypt a view and why?
For security reasons how you be able to hide the base table names and why?
How do you encrypt a view and what should you make sure you do before you encrypt a view and why?
For security reasons how you be able to hide the base table names and why?
Front
USE NuggetDemoDB
GO
ALTER VIEW vEmployees
Encryption
AS
SELECT
*
FROM
GO
This is a one way operation so make sure you have the structure saved someone to run again if need be.
a.
USE NuggetDemoDB
GO
ALTER VIEW vEmployees
Encryption, View_METADATA
AS
SELECT
*
FROM
GO
Many developers use tools to scan the view for certain information. The View_METADATA command hides the base table names so devs can't know that info, just the column names.
Back
What does the WHERE part of a SELECT statement do?
Front
this is just how we put a filter on the list of data that's coming back. So we would filter column or multiple columns out, and that'll just affect the data that's coming out.
Back
Defaults
Front
means if no data is supplied when data is coming in than use a default value
[VacationHours] [int] NOT NULL DEFAULT (0),
CONSTRAINT DF_DiscontinuedFlag DEFAULT (0),
Back
SELECT If you want all data from a specific table
Front
you do table dot star, and that'll give us everything from that table. If you don't use a star, you can use specific column followed by commas, those will return all of those columns there based on what we have on our list.
Back
1. How do you reach Help Viewer
a. Inside of brackets
b. Inside <>
Front
1. Highlight text then Shift+F1
a. Means entries are optional
You can find more info further in the text
Back
How to get the top records ordered in a way
Front
SELECT, TOP 10, star from table name, and that'll give us the top 10 records that are returned in this table. And this is usually used with the ORDER BY clause, if we need to order it descending by the quantity, and that'll give us the top 10 by the quantity. So TOP is usually used with ORDER BY if you're going to do something of that nature.
Back
1. Syntax to alter table
a. Syntax to alter table column
b. Rename table
Rename column
Front
1. ALTER TABLE Employees
a. ADD
i. ActiveFlag bit NOT NULL,
ii. ModifiedDate datetime NOT NULL;
2. ALTER TABLE Products
a. ALTER COLUMN Price money;
3. EXEC sp_rename 'tablename', 'newname'
4. EXEC sp_rename 'columnname', 'newname', 'COLUMN'
Back
What are the different types of JOINS and what do they mean?
Front
We have an INNER JOIN, which is the default. So if you don't specify a type of JOIN, it's going to default here to INNER JOIN, which just means if I'm joining two tables together on a common column, only give me the data where it matches in both tables. A LEFT JOIN means give me the data-- all the data-- in the left hand table, and only the data that matches the left hand table in the right hand table. And then a RIGHT JOIN is the opposite of that, where it's just saying give me all the data from the right hand table and only the records that match from the left hand.
Back
MDAC/ WDAC
Front
Microsoft Data Access Components, or Windows Data Access Components. And this is just consists of a bundle of technologies that are ADO, OLEDB, and ODBC
Back
How are schema's represented in the table names?
a. Where can you view all the schema's for a database?
What can schemas be described as?
Front
When view tables the prefix to the table name is the schema, ex dbo.Databaselog, HumanResources.employeeName
a. You can view them in Security, Schemas
As a why to group and organize our dbase objects, similar to a namespace.When view tables the prefix to the table name is the schema, ex dbo.Databaselog, HumanResources.employeeName
a. You can view them in Security, Schemas
As a way to group and organize our dbase objects, similar to a namespace.
Back
How do you access Query designer and why would you use it.
Front
Open a new query and right click >Query designer, or Highlight some code and right click> Query designer. You could use this while learning to create query scripts or trying to figure out some code.
Back
ODBC
Front
that was driver based. You would have to set up a driver and then, again, ADO could tap in and use that driver to speak to the database.
Back
OLEDB
Front
OLEDB is just an API, it's a programming interface that Microsoft built that made it easy to connect all these different sources.
Back
How do you create a login at the server level?
How do you apply user to database
Front
Within Database expand Security>Rightclick on Logins>New Login or
USE master
Go
CREATE LOGIN username (optional) WITH PASSWORD= , DEFUALT_DATABASE=[DATABASENAME]
CHECK_EXPIRATION=OFF, CHECK__POLICY=OFF;
a. Expand database, expand Security>rightclick users>New user
CREATE USER username (optional) WITH DEFUALT_SCHEMA=[dbo]
CHECK_EXPIRATION=OFF, CHECK__POLICY=OFF;
Back
1. Syntax for single line comments
Multi-line
Front
1. --Single
a. /Data should be in between /
Back
COALESCE Statement
Front
It turns the first non-NULL expression amongst it's argument.
Back
What does the ORDER BY part of a SELECT statement do?
Front
And ORDER BY is how we do sorting
Back
Check
Front
Allows us to ensure that a value for data is what we need it to be.
CONSTRAINT CHK_Price CHECK (Price > 0),
Back
How do you drop a view?
Front
USE NuggetDemoDB
GO
DROP VIEW vEmployees,vTop10ProductSalesByQuantity
Back
What are synonyms and why do they help
Front
Synonyms are variables for fully qualified names. allow us to save on typing, but it can also provide us with a layer of abstraction. if we want to reference a table or a table in another database, or even a table in a database on another server, you have to use the fully qualified name, Which is servername.databasename.schemaname.objectname.
it provides a layer of abstraction because it protects the schema of the underlying object. So if someone cannot do an ALTER statement on our AW employee, because it's a synonym, but they can do Insert, Selects, Updates, and Deletes of data.
Back
Foreign key
Front
a way to enforce data integrity (referential integrity) across tables; these keys may be in other tables but they originate in other tables creating a link between our data tables.
CONSTRAINT FK_ProductsSales FOREIGN KEY (ProductID) REFERENCES Products (ProductID),
Back
Syntax for creating an insert trigger
Front
CREATE TRIGGER iProductNotification ON Products
FOR INSERT
AS
DECLARE @ProductInformation nvarchar(255);
SELECT
@ProductInformation = 'New product ' + Name + ' is now available for ' + CAST(Price as nvarchar(20)) + '!'
FROM
inserted;
Back
How to create a indexed view
Front
USE NuggetDemoDB
GO
CREATE VIEW dbo.vEmployeeSalesOrders
WITH SCHEMABINDING
AS
SELECT
FROM
CREATE UNIQUE CLUSTERED INDEX CIDX_vEmployeesSalesOrder
ON dbo.vEmployeeSalesOrders(EmployeeID, ProductID, SaleDate
GO
Back
Syntax to create a synonyms
Front
CREATE SYNONYM AWEmployee
FOR AdventureWorks2012.HumanResources.Employee
GO
Back
How do you turn on recursive triggers
Front
Go the database properties and options>other options
Back
Case Statement
Front
The CASE statement is SQL's way of handling if/then logic. The CASE statement is followed by at least one pair of WHEN and THEN statements—SQL's equivalent of IF/THEN in Excel. It must end with the END statement. The ELSE statement is optional, and provides a way to capture values not specified in the WHEN/THEN statements.
https://community.modeanalytics.com/sql/tutorial/sql-case/
Back
Unique indexes
Front
is a way to ensure that a column or multiple columns have unique values; really relies on a unique nonclustered index
[Name] [nvarchar](255) NOT NULL UNIQUE NONCLUSTERED,
CONSTRAINT U_Employee UNIQUE NONCLUSTERED (FirstName, LastName, HireDate)
Back
Delete a constraint from a table
Front
ALTER TABLE dbo.Sales
DROP CONSTRAINT FK_EmployeesSales
Back
What does the FROM part of a SELECT statement do
Front
the FROM part of the SELECT statement just tells us what table we're pulling the data from, what columns from what table. And the JOIN part of this is if we want to bring in multiple tables.
Back
Null/Not Null
Front
Constraint - If we allow a null in the field that means this data is optional, Not Null means it is required data.
[FirstName] [nvarchar](50) NOT NULL,
Back
ADO.net
Front
the one that any current day applications that are being developed right now, and accessing a SQL Server database, is probably using ADO.NET. traditional ADO redesigned-- it's much easier for programmers to write, and it just supports a large amount of features compared to traditional ADO.
Back
SQL Server Native Client
Front
a standalone API, if you ever use integration services and you create a connection object, and you're pointing to a SQL Server, it's going to default to the SQL Server Native Client.
Back
SELECT if we only wanted only the unique values from list
Front
we were to do a SELECT, DISTINCT job title from human resources dot employee, m it would only give us research and development here one time. And design engineer one time, and then each one of these one time because they only occur once.
Back
Add a constraint to a table
Front
ALTER TABLE dbo.Sales
ADD CONSTRAINT FK_EmployeesSales FOREIGN KEY (EmployeeID) REFERENCES Employees (EmployeeID)
GO
Back
Syntax to create table
Front
USE (Type the database name here)
Go
CREATE TABLE (Enter name of table here)
( Within these parenthesis's list the columns
MinionID int Not Null,
FirstName nvarchar(50) Null,
Salary decimal(19,4) Not Null
HireDate datetime NotNull
)
Back
How to turn off or on nested triggers?
Front
sp_configure 'nested_triggers', 0
GO
RECONFIGURE
GO
Back
Syntax for creating an update trigger
Front
CREATE TRIGGER uProductPriceChange ON Products
FOR UPDATE
AS
INSERT ProductPriceHistory (PriceHistoryID, ProductID, PreviousPrice, NewPrice, PriceChangeDate)
SELECT
NEWID(), p.ProductID, d.price, i.Price, GETDATE()
FROM
Back
1. What is the syntax to grant users permissions on schema level db objects
a. How to create a schema?
b. Change default USER schema
c. Give SELECT permission to User in the certain schema
Front
--Create Nuggets schema
CREATE SCHEMA Nuggets AUTHORIZATION dbo
--Change default NuggetUser schema
ALTER USER NuggetUser WITH DEFAULT_SCHEMA=[Nuggets]
--Give SELECT permission to NuggetUser in the Nuggets schema
GRANT SELECT ON SCHEMA::Nuggets TO NuggetUser;
Back
Syntax to alter triggers
Front
ALTER TRIGGER dbo.udEmployeeAudit ON Employees
FOR UPDATE, DELETE
AS
INSERT EmployeeAuditTrail
SELECT
e.EmployeeID, d.FirstName, d.MiddleName, d.LastName,
d.Title, d.HireDate, d.VacationHours, d.Salary,
GETDATE(), SYSTEM_USER
FROM
Employees e
Back
The HAVING keyword does what?
Front
HAVING is pretty much your WHERE clause for groups. So think of that as it allows us to apply a filter to the group.
Back
1. Syntax to delete column from table
a. Syntax to delete table
Front
USE NuggetDemoDB
GO
ALTER TABLE Products
DROP COLUMN price
DELETE TABLE tablename
Back
What does the GROUP BY part of a SELECT statement do?
Front
The GROUP BY is how we do aggregates. So if we want to do something like SUM, or AVERAGE, or MIN, or MAX, those are all aggregate functions. And GROUP BY will allow us to take a field that'll group by similar data, and then we can run aggregates on the other data in that group.
Back
What is the syntax to grant users permissions on server level db objects
a. Remove permissions
Front
GRANT SELECT ON dbo.vProductAndDescription TO NuggetUser;
REVOKE SELECT ON dbo.vProductAndDescription FROM NuggetUser;
Back
1. What are the steps to have SQL script out objects?
Front
Right-click object > Script Table as > then select whichever manipulation or definition needed.
Back
Syntax for instead trigger
Front
CREATE TRIGGER [HumanResources].[dEmployee] ON [HumanResources].[Employee]
INSTEAD OF DELETE
AS
BEGIN
DECLARE @Count int;
SET @Count = @@ROWCOUNT;
IF @Count = 0
RETURN;
SET NOCOUNT ON;
BEGIN
RAISERROR
(N'Employees cannot be deleted. They can only be marked as not current.', -- Message
10, -- Severity.
1); -- State.
-- Rollback any active or uncommittable transactions
IF @@TRANCOUNT > 0
BEGIN
ROLLBACK TRANSACTION;
END
END;
END;
Back
ADO
Front
ActiveX Data Objects. This is what programmers would use to tap into one of these underlying technologies, OLEDB and ODBC. So ADO was really kind of the programming language, the middle layer that programmers would manipulate to speak to OLEDB and ODBC.
Back
1. What's the point of the GO?
a. Whats the point of a semicolon
Front
Go is a batch terminator sending each statement to the database seperately
It separates statements
Back
Primary key
Front
allows sql to uniquely identify the rows within our table. Simply a clustered indexed
[EmployeeID] [int] NOT NULL IDENTITY(1,1) PRIMARY KEY,
CONSTRAINT PK_ProductID PRIMARY KEY CLUSTERED (ProductID ASC)
Back
SELECT
Front
just starts the statement off, and it's usually followed by a star or a splat *-- as we call it-- and this just means return all the columns. If you have multiple tables that you're selecting from, a star by itself will grab all columns from all tables-- if for instance, if you're joining multiple tables together.
Back
1. What is the syntax of creating a view?
a. Alter a view
b. How to do data modifications through view
What does WITH CHECK OPTION do
Front
Use dbname
Go
Create View nameofview
AS
Select
Object
From
GO
USE NuggetDemoDB
GO
ALTER VIEW vEmployees
AS
SELECT
*
FROM
GO
With the insert statement
INSERT vEmployees SELECT
Allows any data modification to follow a where clause
Use dbname
Go
Create View nameofview
AS
Select
Object
From
WHERE
Title = 'Sales Person'
WITH CHECK OPTION
GO
Back
Section 2
(4 cards)
System metadata
Front
System metadata is just information about all the objects inside of SQL Server that we can work with, and not the objects inside of our database.
Object_ID
ObjectProperty
Back
Pivoting rows to columns
Front
to take data that is formatted for analysis and pivot it for presentation or charting. the pivot statement, what data you want to store or view inside of the matrix, and then what you're pivoting against, what row are you turning into columns.
Back
Common Table Expressions
Front
t's just simply a named query that we can reference from our main query down below.
Back
Dynamic SQL
Front
Dynamic SQL is a programming technique that enables you to build SQL statements dynamically at runtime. You can create more general purpose, flexible applications by using dynamic SQL because the full text of a SQL statement may be unknown at compilation.
EXEC statement
sp_ExecuteSQL