Section 1

Preview this deck

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

Star 0%
Star 0%
Star 0%
Star 0%
Star 0%

0.0

0 reviews

5
0
4
0
3
0
2
0
1
0

Active users

0

All-time users

0

Favorites

0

Last updated

4 years ago

Date created

Mar 1, 2020

Cards (54)

Section 1

(50 cards)

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

Back