Week 4

Altering a View

--4 alters the view by adding a column ALTER VIEW dbo.vw_Customer AS SELECT c.CustomerID,c.AccountNumber,c.StoreID, c.TerritoryID, p.FirstName,p.MiddleName, p.LastName, p.Title FROM Sales.Customer AS c INNER JOIN Person.Person AS p ON c.PersonID = p.BusinessEntityID; GO

CAST is part of the _______ specification; whereas, CONVERT is ________.

ANSI-SQL not

Convert it offers some flexibility over ______. You can format output of your result set with the _________. You can apply styles to ______, ______, _____, ____, _____, ____ and ________

CAST style argument. date, time, real, float, money, xml, and binary data types.

Another way to find rows that are included or missing from another table is by using a subquery along with ___________. This usually performs better

EXISTS

·Using a subquery in an IN list is similar to the hard-coded ______________

IN list in a WHERE clause. SELECT <select list> FROM <table1> WHERE <col1> IN (SELECT <col2> FROM <table2>)

Although you can use several techniques and methods to insert data into SQL Server tables, the best approach is to start with the two simplest ways

INSERT INTO statement SELECT INTO statement

A subquery in the WHERE clause can also be used to find rows that don't match the values from another table by adding the _____________

NOT operator.

You can find the customers who have not placed an order by adding the word______________

NOT to the previous query.

You can create views using most ________statements, including those made with common table expressions.

SELECT ·CREATE VIEW <view name> AS SELECT <col1>, <col2> FROM <table> ·ALTER VIEW <view name> AS SELECT <col1>, <col2> FROM <table> DROP VIEW <view name>

EXISTS

SELECT CustomerID, AccountNumber FROM Sales.Customer WHERE EXISTS (SELECT* FROM Sales.SalesOrderHeader AS SOH WHERE SOH.CustomerID = Customer.CustomerID);

There are other ways to combine tables in a query. Such as

Subqueries Common Table Expressions (CTE) and Union

There are other ways to combine tables in a query. _____________, __________ N ___________

Subqueries, Common Table Expressions (CTE), and Union

Use IF along with the optional ELSE keyword to control code flow in your ____________

T-SQL scripts.

Use IF just as you would in any other programming language to execute a statement or group of statements based on an expression that must evaluate to ____ or ____

TRUE or FALSE

SELECT INTO

This method actually creates a new table and inserts all the rows from the SELECT statement into that newly created table.

MERGE Example

USE AdventureWorks2012; MERGE dbo.Department destination USING HumanResources.Department source ON destination.Name = source.Name WHEN MATCHED THEN UPDATE SET destination.Name = source.Name, destination.GroupName = source.GroupName, destination.ModifiedDate = source.ModifiedDate WHEN NOT MATCHED BY TARGET THEN INSERT (Name, GroupName, ModifiedDate) VALUES (source.Name, source.GroupName, source.ModifiedDate);

You should be cautious when issuing an UPDATE statement, as it is highly unlikely that every row in a table needs updating. Therefore, always consider including a __________ with every UPDATE statement. If WHERE is not included, you could____________

WHERE clause accidentally update every row, which could cost you your job

Using insert method, you can insert data into _____ or ______

all columns or specific columns.

·For example, without actually joining the order table, you could use a subquery to display a list of the __________________________________

customers who have placed an order.

Cast it converts one ______ to ________

data type another

A subquery in the WHERE clause can also be used to find rows that _____________from another table by adding the ________

don't match the values NOT operator.

MERGE checks for the __________, and if the row does not exist, a ________. If the row does exist, then it is _________.

existence of a row new row is added updated

The MERGE statement is a hybrid that can ______, _____ N _______ in a single query.

insert, update, and delete data

MERGE Depending on how you write the query, it can perform almost any combination of____, ____ N _____

insert, update, and delete.

·The most common way to use more than one table in a query is by ______them.

joining

The most common way to use more than one table in a query is by __________

joining them.

The IN operator is a shorthand for

multiple OR conditions.

The IN command allows you to specify _________ in a _______

multiple values WHERE clause.

An INSERT INTO statement can add _____or ______

one row or multiple rows.

SQL Server includes an UPDATE statement that you can use to modify ____ or ____

one row or several rows.

UPDATE SQL Server includes an UPDATE statement that you can use to modify___________ OR ______

one row or several rows.

One reason to use a subquery is to find the ___________________________without actually joining ________________

rows in one table that match the rows in another table the second table.

Subqueries One reason to use a subquery is to find the _______________

rows in one table that match the rows in another table without actually joining the second table.

They enhance ________. You can give the end user permission to select from a _______without giving permission to the ____________

security view underlying tables.

For example, without actually joining the order table, you could use a ______ to display a list of the customers _________________

subquery who have placed an order.

SQL Server stores data in ________, but you can create objects, called ________, that you query just like tables. Views don't _________; they are just saved _______

tables views store data query definitions.

A MERGE statement is typically used to perform an ________, which is a logical combination of ____ N ______

upsert inserting and updating data.

Subqueries

·The most common way to use more than one table in a query is by joining them. ·There are other ways to combine tables in a query. Subqueries, Common Table Expressions (CTE), and Union ·One reason to use a subquery is to find the rows in one table that match the rows in another table without actually joining the second table. ·For example, without actually joining the order table, you could use a subquery to display a list of the customers who have placed an order.

You should be cautious when issuing an UPDATE statement, as it is highly unlikely that every row in a table needs updating. Therefore, always consider including a WHERE clause with every UPDATE statement. If WHERE is not included, you could accidentally update every row, which could cost you your job.

·You should be cautious when issuing an UPDATE statement, as it is highly unlikely that every row in a table needs updating. Therefore, always consider including a WHERE clause with every UPDATE statement. If WHERE is not included, you could accidentally update every row, which could cost you your job.