loading..
Русский    English
01:41
go

Exercise #151 page 3

Notes:

In second query from UNION in the solution 3.5.2 DATEPART function, specific for  SQL(Structured Query Language) is a database computer language designed for the retrieval and management of data in relational database management systems (RDBMS), database schema creation and modification, and database object access control management.SQL Server, is used. It is necessary due to date of battle (the "date" column has temporal data type - datetime), from which we should take a year of battle; otherwise the predicate 

  1. date < 1941
will give us comparison with date of year 1905, as a result of converting of an integer to datetime type (as number of days from the beginning of 1900 hat is a reference point of dates for settings by default in SQL Server). It is easy to be convinced of it by executing the query:

Console
Execute
  1. SELECT CAST(1941 AS DATETIME);
which gives

  1. 1905-04-26 00:00:00.000.

To remain within the Standard's frame, it would be possible to use the following predicate:

  1. date < '1941'

Then implicit transformation of type would give the result required. Again we shall check up:

Console
Execute
  1. SELECT CAST('1941' AS DATETIME);
That gives

  1. 1941-01-01 00:00:00.000

Naturally, the predicate containing a full date corresponding to the beginning of 1941 (January, 1) will be correct also:

  1. date < '19410101'

However let's return to our solution. It is incorrect. As it has been said, the situation when the head ship with unknown launch year is present only in Ships table here is not considered. To consider this situation we should add one more query to the union.

Bookmark and Share
Pages 1 2 3 4
Tags
aggregate functions Airport ALL AND AS keyword ASCII AVG Battles Bezhaev Bismarck C.J.Date calculated columns Cartesian product CASE cast CHAR CHARINDEX Chebykin check constraint classes COALESCE common table expressions comparison predicates Computer firm CONSTRAINT CONVERT correlated subqueries COUNT CROSS APPLY CTE data type conversion data types database schema DATEADD DATEDIFF DATENAME DATEPART DATETIME date_time functions DDL DEFAULT DEFAULT VALUES DELETE DISTINCT DML duplicates edge equi-join EXCEPT exercise (-2) More tags
The book was updated
month ago
©SQL-EX,2008 [Evolution] [Feedback] [About] [Links] [Team]
All right reserved.