Task 4 (computer)

–The database scheme consists of four tables:

–Product(maker, model, type)

–PC(code, model, speed, ram, hd, cd, price)

–Laptop(code, model, speed, ram, hd, screen, price)

–Printer(code, model, color, type, price)

–The table "Product" includes information about the maker, model number, and type (‘PC’, ‘Laptop’, or ‘Printer’).

–It is assumed that model numbers in the Product table are unique for all the makers and product types.

–Each PC uniquely specifying by a code in the table "PC" is characterized

–by model (foreign key referencing to Product table), speed (of the processor in MHz), total amount of RAM – ram (in Mb),

–hard disk drive capacity – hd (in Gb), CD ROM speed – cd (for example, ‘4x’), and the price.

–The table "Laptop" is similar to that one of PCs except for the CD ROM speed,

–which is replaced by the screen size – screen (in inches). For each printer in the table "Printer" it is told whether

–the printer is color or not (color attribute is ‘y’ for color printers; otherwise it is ‘n’),

–printer type (laser, jet, or matrix), and the price.

–Calculate the sum of digits in each model’s ID (model column) from Product table.

–Result set: model, sum of digits

–It should take into account that model column may contain not only digits, but also non-digit symbols according to the data type of the column – varchar (50).

–If a model does not contain digits at all , deduce 0.

select model,

(

LEN(REPLACE(model, ‘1’,’11’)) LEN(model)) +

2

*(LEN(REPLACE(model, ‘2’,’22’)) LEN(model)) +

3

*(LEN(REPLACE(model, ‘3’,’33’)) LEN(model)) +

4

*(LEN(REPLACE(model, ‘4’,’44’)) LEN(model)) +

5

*(LEN(REPLACE(model, ‘5’,’55’)) LEN(model)) +

6

*(LEN(REPLACE(model, ‘6’,’66’)) LEN(model)) +

7

*(LEN(REPLACE(model, ‘7’,’77’)) LEN(model)) +

8

*(LEN(REPLACE(model, ‘8’,’88’)) LEN(model)) +

9

*(LEN(REPLACE(model, ‘9’,’99’)) LEN(model))

from

Product

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s