Write a SELECT statement to retrieve the custid and contactname columns from the Sales.Customers table and the orderid column from the Sales.Orders table. Assign the aliases mgrlastname and mgrfirstname, respectively, to distinguish the manager names from the employee names. Copy the SELECT statement from upper task and modify it to include additional columns for the manager information (lastname, firstname) using a self-join. Select empid, lastname, firstname, title, mgrid In order to better understand the needed tasks, you will first write a SELECT statement against the HR.Employees table showing the empid, lastname, firstname, title, and mgrid columns.
This column will show the description ∼ampaign Products for the categories Beverages, Produce, and Seafood and the description Non-Campaign Products for all other categories. Modify the SELECT statement in task 2 by adding a new column named iscampaign. Use the value Other for any category IDs not found in the mapping table. The new column should hold the translation of the category ID to its respective category name based on the mapping table supplied earlier. Enhance the SELECT statement in upper task by adding a CASE expression that generates a result column named categoryname.
Write a SELECT statement to display the categoryid and productname columns from the Production.Products table. Select contactname as 'Name ', contacttitle as 'Title ', companyname as 'Company Name ' Assign these columns with the aliases Name, Title, and Company Name, respectively, in order to return more human-friendly column titles for reporting purposes. Write a SELECT statement to return the contactname, contacttitle, and companyname columns from the Sales.Customers table. CREATE FULLTEXT INDEX ON Book(Description) KEY INDEX PK_Book_BookID CREATE FULLTEXT CATALOG LibraryFullTextCatalog AS DEFAULT define 3 any views (must be useful and logical)ĬONSTRAINT PK_Books_BookID PRIMARY KEY CLUSTERED (BookID),īookReviewsID int IDENTITY ( 1, 1) NOT NULLĬONSTRAINT PK_BookReviews_BookReviewsID PRIMARY KEY CLUSTERED (BookReviewsID),ĬONSTRAINT DF_BooksReviews_ReviewDate DEFAULT (GETDATE()),ĬONSTRAINT CK_BookReviews_Rating CHECK (Rating BETWEEN 1 AND 5),ĬONSTRAINT DF_BookReviews_ModifiedDate DEFAULT (GETDATE()),įOREIGN KEY ( BookID ) REFERENCES Books( BookID )ĬONSTRAINT PK_Author_AuthorID PRIMARY KEY CLUSTERED (AuthorID)ĬONSTRAINT PK_Lenders_LenderID PRIMARY KEY CLUSTERED (LenderID)ĬONSTRAINT PK_Lend_LendID PRIMARY KEY CLUSTERED (LendID)ĬONSTRAINT DF_Lend_LendDate DEFAULT (GETDATE())įOREIGN KEY ( LendBookID ) REFERENCES Books( BookID ) add tables, keys, constraints (default, unique, etc) Create simple database for Library (5 or six tables) Learn more about bidirectional Unicode characters To review, open the file in an editor that reveals hidden Unicode characters. This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below.
0 kommentar(er)
