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.
