Skip to content

Latest commit

 

History

History
94 lines (47 loc) · 6.75 KB

index.md

File metadata and controls

94 lines (47 loc) · 6.75 KB

Solutions

  1. Find the model number, speed and hard drive capacity for all the PCs with prices below $500.

  2. List all printer makers. Result set: maker.

  3. Find the model number, RAM and screen size of the laptops with prices over $1000.

  4. Find all records from the Printer table containing data about color printers.

  5. Find the model number, speed and hard drive capacity of PCs cheaper than $600 having a 12x or a 24x CD drive

  6. For each maker producing laptops with a hard drive capacity of 10 Gb or higher, find the speed of such laptops.

  7. Get the models and prices for all commercially available products (of any type) produced by maker B.

  8. Find the makers producing PCs but not laptops.

  9. Find the makers of PCs with a processor speed of 450 MHz or more. Result set: maker.

  10. Find the printer models having the highest price. Result set: model, price.

  11. Find out the average speed of PCs.

  12. Find out the average speed of the laptops priced over $1000.

  13. Find out the average speed of the PCs produced by maker A.

  14. For the ships in the Ships table that have at least 10 guns, get the class, name, and country.

  15. Get hard drive capacities that are identical for two or more PCs.Result set: hd.

  16. Get pairs of PC models with identical speeds and the same RAM capacity. Each resulting pair should be displayed only once, i.e. (i, j) but not (j, i).Result set: model with the bigger number, model with the smaller number, speed, and RAM.

  17. Get the laptop models that have a speed smaller than the speed of any PC.Result set: type, model, speed.

  18. Find the makers of the cheapest color printers.Result set: maker, price.

  19. For each maker having models in the Laptop table, find out the average screen size of the laptops he produces.Result set: maker, average screen size.

  20. Find the makers producing at least three distinct models of PCs.Result set: maker, number of PC models.

  21. Find out the maximum PC price for each maker having models in the PC table. Result set: maker, maximum price.

  22. For each value of PC speed that exceeds 600 MHz, find out the average price of PCs with identical speeds.Result set: speed, average price.

  23. Get the makers producing both PCs having a speed of 750 MHz or higher and laptops with a speed of 750 MHz or higher.Result set: maker

  24. List the models of any type having the highest price of all products present in the database.

  25. Find the printer makers also producing PCs with the lowest RAM capacity and the highest processor speed of all PCs having the lowest RAM capacity.Result set: maker.

  26. Find out the average price of PCs and laptops produced by maker A.Result set: one overall average price for all items.

  27. Find out the average hard disk drive capacity of PCs produced by makers who also manufacture printers.Result set: maker, average HDD capacity.

  28. Using Product table, find out the number of makers who produce only one model.

  29. Under the assumption that receipts of money (inc) and payouts (out) are registered not more than once a day for each collection point [i.e. the primary key consists of (point, date)], write a query displaying cash flow data (point, date, income, expense).Use Income_o and Outcome_o tables.

  30. Under the assumption that receipts of money (inc) and payouts (out) can be registered any number of times a day for each collection point [i.e. the code column is the primary key], display a table with one corresponding row for each operating date of each collection point.Result set: point, date, total payout per day (out), total money intake per day (inc).Missing values are considered to be NULL.

  31. For ship classes with a gun caliber of 16 in. or more, display the class and the country.

  32. One of the characteristics of a ship is one-half the cube of the calibre of its main guns (mw).Determine the average ship mw with an accuracy of two decimal places for each country having ships in the database.

  33. Get the ships sunk in the North Atlantic battle.Result set: ship.

  34. In accordance with the Washington Naval Treaty concluded in the beginning of 1922, it was prohibited to build battle ships with a displacement of more than 35 thousand tons.Get the ships violating this treaty (only consider ships for which the year of launch is known).List the names of the ships.

  35. Find models in the Product table consisting either of digits only or Latin letters (A-Z, case insensitive only.Result set: model, type.

  36. List the names of lead ships in the database (including the Outcomes table).

  37. Find classes for which only one ship exists in the database (including the Outcomes table).

  38. Find countries that ever had classes of both battleships (‘bb’) and cruisers (‘bc’).

  39. Find the ships that survived for future battles; that is, after being damaged in a battle, they participated in another one, which occurred later.

  40. Get the makers who produce only one product type and more than one model. Output: maker, type.

  41. For each maker who has models at least in one of the tables PC, Laptop, or Printer, determine the maximum price for his products.Output: maker; if there are NULL values among the prices for the products of a given maker, display NULL for this maker, otherwise, the maximum price.

  42. Find the names of ships sunk at battles, along with the names of the corresponding battles.

  43. Get the battles that occurred in years when no ships were launched into water.

  44. Find all ship names beginning with the letter R.

  45. Find all ship names consisting of three or more words (e.g., King George V). Consider the words in ship names to be separated by single spaces, and the ship names to have no leading or trailing spaces.

  46. For each ship that participated in the Battle of Guadalcanal, get its name, displacement, and the number of guns.