Упражнение - Условия

Exercise: EntityFramework Advanced Querying - Databases Advanced - Entity Framework - октомври 2016

 

Материали от темата

Съдържание на документа

Exercises: Advanced Querying

This document defines the exercise assignments for the "Databases Advanced – Entity Framework" course @ Software University.

* Optimizing Previous Homeworks

You can open your previous homeworks and optimize them by using select where it is possible in order to take less data from the database.

Bookshop System

For the following tasks use the BookhopSystem database from the previous exercise. Make sure it has proper connections between tables and its populated with any sample data.

Books Titles by Age Restriction

Write a program that selects and prints titles of all books where their age restriction matches the given input (minor, teen or adult). Ignore casing of the input.

Example

Input

Output

miNor

A che punto è la note

After Many a Summer Dies the Swan

Ah

…

teEN

All Passion Spent

Wide Sea

Antic Hay

…

Golden Books

Write a program that selects and prints titles of the golden edition books and have less than 5000 copies.

Example

Output

Behold the Man

Bury My Heart at Wounded Knee

The Cricket on the Hearth

…

Books by Price

Write a program that selects prints titles and price of books with price lower than 5 and higher than 40.

Example

Output

A che punto è la note - $45.78

All the King's Men - $45.60

An Evil Cradling - $3.30

Beyond the Mexique Bay - $45.45

…

Not Released Books

Write a program that selects and prints titles of all books that are NOT released on given year.

Example

Input

Output

2000

Absalom

A che punto è la note

After Many a Summer Dies the Swan

…

1998

A che punto è la note

Ah

Wilderness!

…

Book Titles by Category

Write a program that selects and print titles of books by given list of categories. The list of categories will be given in a single one separated with one or more spaces. NOTE: The results here may not be like the one above, because of the random attachment of an category to the book.

Example

Input

Output

fantasy thriller crime

Thrones

Dominations

Tiger! Tiger!

Number the Stars

Now Sleeps the Crimson Petal

When the Green Woods Laugh

Books Released Before Date

Write a program that selects and prints title, edition type and price of books that are released before given date as an input from the console. The date will be in format dd-MM-yyyy.

Example

Input

Output

12-04-1992

All Passion Spent - Promo - 7.18

Bury My Heart at Wounded Knee - Gold - 3.86

A Catskill Eagle - Normal - 15.78

…

30-12-1989

Bury My Heart at Wounded Knee - Gold - 3.86

Consider the Lilies - Promo - 30.89

The Curious Incident of the Dog in the Night-Time - Normal - 23.41

…

Authors Search

Write a program that selects and prints names of those authors whose first name end with given string.

Example

Input

Output

e

George Powell

Jane Ortiz

Julie Washington

dy

Randy Morales

Randy Graham

Books Search

Write a program that selects and prints titles of books which contains given string (regardless of the casing).

Example

Input

Output

sK

A Catskill Eagle

The Daffodil Sky

The Skull Beneath the Skin

WOR

Great Work of Time

Terrible Swift Sword

Book Titles Search

Write a program that selects and prints titles of books which are written by authors whose last name start with given string. NOTE: The results here may not be like the one above, because of the random attachment of an author to the book.

Example

Input

Output

R

A Time of Gifts (Amanda Rice)

To Sail Beyond the Sunset (Amanda Rice)

To Say Nothing of the Dog (Amanda Rice)

gr

What's Become of Waring (Randy Graham)

Vanity Fair (Randy Graham)

Dominations (Chris Graham)

Eyeless in Gaza (Brenda Griffin)

	Count Books

Write a program that selects and prints number of books whose title is longer than a number given as an input.

Example

Input

Output

Comments

12

174

There are 174 books with longer title than 12 symbols

40

2

There are 2 books with longer title than 40 symbols

	Total Book Copies

Write a program that selects and prints the total number of book copies by author. Order the results descending by total book copies.

Example

Output

Amanda Rice – 87819

Amy Porter – 29366

Christina Jordan – 18708

Earl Bennett – 12978

…

	Find Profit

Write a program that selects and print the total profit of all books by category. Profit for a book can be calculated by multiplying its number of copies with price per single book. Order the results descending by total profit for category and ascending by category name.

Example

Output

Fantasy - $1100443.04

Crime - $796916.04

Thriller - $688182.75

…

	Most Recent Books

Get the most recent books by categories. The categories should be ordered by total book count. Only take the top 3 most recent books from each category - ordered by date (descending), then by title (ascending). Select and print the category name, total book count and for each book - its title and release date. Get only those categories that have total book count more than 35.

Note: Books may appear in several categories.

Example

Output

--Mystery: 58 books

Brandy of the Damned (2015)

The Parliament of Man (2014)

Look Homeward (2014)

--Science Fiction: 50 books

Alien CornA (play) (2014)

How Sleep the Brave (2014)

Look Homeward (2014)

--Crime: 45 books

Look Homeward (2014)

The Doors of Perception (2013)

A Monstrous Regiment of Women (2013)

--Romance: 38 books

Alien CornA (play) (2014)

Look Homeward (2014)

The Wealth of Nations (2013)

--Fiction: 37 books

Great Work of Time (2014)

A Many-Splendoured Thing (2012)

The Last Temptation (2012)

	Increase Book Copies

Write a program that increases the copies of all books released after given date with given number. Print the total amount of book copies that were added.

Input

On the first line – date in format dd-MMM-yyyy. If a book is released after that date (exclusive) increase her book copies with the provided number from the second line of input

On the second line – number of book copies each book should be increased

Output

Total number of books that was added to the database

Example

Input

Output

Comments

12 Oct 2005

100

6100

61 books are released after 12 Oct 2005 so total of 6100 book copies were added

06 Jun 2013

44

572

13 books are released after 6 Jun 2013 so total of 572 book copies were added

	Remove Books

Write a program that removes from the database those books whose copies are lower than given number. Print on the console the number of books that were deleted from the database.

Example

Input

Output

300

4 books were deleted

4200

34 books were deleted

	Stored Procedure

Using SQL Server Management Studio create stored procedure that receives authors first and last name and returns the total number of books that author has written. Then write a program that receives author name from the console and prints the total number of books that author has written by using the stored procedure you’ve just created.

Example

Input

Output

Amanda Rice

Amanda Rice has written 4 books

Christina Jordan

Christina Jordan has written 1 book

Wanda Morales

Wanda Morales has not written any books yet

	Call a Stored Procedure

Your task is to create a stored procedure in the SoftUni database for finding all projects for given employee. The procedure should receive first name and last name as arguments. 

Using EF implement a C# method that calls the stored procedure and returns the result projects' name, description and start date.





	Employees Maximum Salaries

Write a program to find the max salary for each department. Filter those which have max salaries not in the range 30000 and 70000.

Example

Output

Tool Design - 29800.00

…

	Deposits Sum for Ollivander Family

Use the Gringotts database. Write a program that print all deposit groups and its total deposit sum but only for the wizards who has their magic wand crafted by Ollivander family.

Output

Output

Human Pride - 188366.86

…

	Deposits Filter

Use the Gringotts database. Write a program that print all deposit groups and its total deposit sum but only for the wizards who has their magic wand crafted by Ollivander family. After this filter total deposit amounts lower than 150000. Order by total deposit amount in descending order.

Example

Output

Troll Chest - 126585.18

…