Sql server® 2012 t-sql fundamentals

92  

Microsoft SQL Server 2012 T-SQL Fundamentals

2

Write a query against the Sales.Orders table that returns orders placed on the last day of the month.

■

■

Tables involved: TSQL2012 database and the Sales.Orders table

■

■

Desired output (abbreviated):

orderid     orderdate               custid      empid 

----------- ----------------------- ----------- ----------- 

10269       2006-07-31 00:00:00.000 89          5 

10317       2006-09-30 00:00:00.000 48          6 

10343       2006-10-31 00:00:00.000 44          4 

10399       2006-12-31 00:00:00.000 83          8 

10432       2007-01-31 00:00:00.000 75          3 

10460       2007-02-28 00:00:00.000 24          8 

10461       2007-02-28 00:00:00.000 46          1 

10490       2007-03-31 00:00:00.000 35          7 

10491       2007-03-31 00:00:00.000 28          8 

10522       2007-04-30 00:00:00.000 44          4 

... 

 

(26 row(s) affected)

3

Write a query against the HR.Employees table that returns employees with last name containing the 

letter a twice or more.

■

■

Tables involved: TSQL2012 database and the HR.Employees table

■

■

Desired output:

empid       firstname  lastname 

----------- ---------- -------------------- 

9           Zoya       Dolgopyatova 

 

(1 row(s) affected)

4

Write a query against the Sales.OrderDetails table that returns orders with total value (quantity * unit-

price) greater than 10,000, sorted by total value.

■

■

Tables involved: TSQL2012 database and the Sales.OrderDetails table

■

■

Desired output:

orderid     totalvalue 

----------- --------------------- 

10865       17250.00 

11030       16321.90 

10981       15810.00 

10372       12281.20 

www.it-ebooks.info

---