Assignments
Retrieve All rows from the HumanResources.Employee table.
Return only the NationalIDNumber column.
Retrieve All rows from the HumanResources.Employee table.
Return the NationalIDNumber and JobTitle columns.
Write the SQL SELECT statement that returns the FirstName column of Person.Person casted
as the VARCHAR data type.
Cast the FirstName column of Person.Person as the VARCHAR(3) data type. What happens?
Many of the values in the Size column of the Production.Product table contain numeric
values.
Write a SELECT statement that returns the Size column casted as the integer data type. What
is the result.
From the HumanResources.vEmployeeDepartment, return the FirstName, LastName and
JobTitle columns. Sort the results by the FirstName column in ascending order.
From the HumanResources.vEmployeeDepartment - Modify the Query from question 1 to
sort the results by the FirstName column in ascending order and then by the LastName
column in descending order.
From the Sales.vIndividualCustomer, return the FirstName, LastName and
CountryRegionName columns. Sort the results by the CountryRegionName column. Use the
column ordinal in the ORDER BY clause.
From the Sales.vIndividualCustomer, return the FirstName, LastName And
CountryRegionName columns for Those rows with a CountryRegionName that is either
“UnitedStates” or “France”.Sort the results by the CountryRegionName column in ascending
order.
--------------------------------------------------------------------------------------------------------------------------------------
Create new table and insert below data, Delete the record where customer_name are
Andrew Smith and Michael Smith.
Return the Customer_name, city and order_amount from the Orders_Testing table where
order_date < '2017-04-06'
Delete the record where Order_amount greater than < 15000
CREATE TABLE [dbo].[Order Details]
order_id INT,
order_date DATE,
customer_name VARCHAR(250),
city VARCHAR(100),
order_amount MONEY
)
INSERT INTO [dbo].[Orders_Details]
SELECT '1001','04/01/2017','David Smith','GuildFord',10000
UNION ALL
SELECT '1002','04/02/2017','David Jones','Arlington',20000
UNION ALL
SELECT '1003','04/03/2017','John Smith','Shalford',5000
UNION ALL
SELECT '1004','04/04/2017','Michael Smith','GuildFord',15000
UNION ALL
SELECT '1005','04/05/2017','David Williams','Shalford',7000
UNION ALL
SELECT '1006','04/06/2017','Paum Smith','GuildFord',25000
UNION ALL
SELECT '1007','04/10/2017','Andrew Smith','Arlington',15000
UNION ALL
SELECT '1008','04/11/2017','David Brown','Arlington',2000
UNION ALL
SELECT '1009','04/20/2017','Robert Smith','Shalford',1000
UNION ALL
SELECT '1010','04/25/2017','Peter Smith','GuildFord',500
Case Statement Question
Return the FirstName and LastName column from Emp table. Return a third column that
outputs “Promotion 1” if the "position" column value is Manager, “Manager_Asia” if the
EmailPromotion value is Senior Associate, and “Senior Associate Euro” Else it should be
represent the column values "position".
Using the employees table, complete a CASE statement that returns the string “Long Name”
if the FirstName column is at least five(5) characters long. If the FirstName column is less
than ten characters, return the string “Short Name”.
Joins -
Using the Person.Person and Person.Password tables, INNER JOIN the two tables using the
BusinessEntityID column and return the FirstName and LastName columns from
Person.Person and then PasswordHash column from Person.Password.
Return the BusinessEntityID and SalesYTD column from Sales.SalesPerson.Join this table to
the Sales.SalesTerritory table in such a way that every in Sales.SalesPerson will be returned
regardless of whether or not they are assigned to a territory. Also, return the Name column
from Sales.SalesTerritory. Give this column the column alias “Territory Name”.
Using the previous example as your foundation, join to the Person.Person table to return the
sales person’s first name and last name. Now, only include those rows where the territory’s
name is either “Northeast” or “Central”
