Question

SQL QUERIES

1) Using the Product and PC relations, create both tables in your database and insert all the data. Show the SQL statements to create each table and show a representative SQL insert statement for the data in each table (i.e. you do not need to show insert statements for all the data).

– For the remaining questions, assume that your SQL is part of a program function and the function provides the input needed for your SQL query.

2) Create a SQL read-only transaction to display the maker, model, and price for all PCs of a given maker with a minimum quantity of RAM. Show the SQL statement and result with the following input values - maker:D, ram:512.

3) Create a SQL transaction to delete all tuples with a given maker and model from both the PC and Product relations. Use an isolation level that allows dirty reads. Show the SQL statement and result with the following input values - model:A, model:1003.

4) Create a SQL statement to update the price of every model by 3% for a given maker. Use an isolation level that disallows reading modified, uncommitted data. Show the SQL statement and result with the following input values - model:E.

5) Create a SQL transaction to insert a new tuple in the Product and PC tables. Use the default isolation level. Show the SQL statement and result with the following input values - maker:X, model:9001, type:PC, speed:3.02, ram:1024, hd:300, price:1011.

maker type A A A A pc pc pc laptop laptop laptop PC PC pc laptop PC B B B C D D D D D model 1001 1002 1003 2004 2006 2006 1004 1005 1006 2007 1007 1008 1009 1010 3004 3005 1011 1012 1013 2001 2002 2003 3001 3002 3003 2008 2009 2010 3006 3007 E E E E E F F G H H pc ре pc printer printer pc pc pc laptop laptop laptop printer printer printer laptop laptop laptop printer printer Figure 2.20: Sample data for Product

Answer 1

Note: I would be able to answer only the first question as per the Chegg guidelines. Thank you for your understanding.

1.

The SQL query to create the Product table would be:

CREATE TABLE Product
(
maker varchar(1),
model int,
type varchar(15)
);

Representative SQL Query to insert data into Product table is given below:

INSERT INTO Product VALUES('A',1001,'pc');

Output:

maker model type A 1001 pc

The SQL query to create the PC table would be:

CREATE TABLE PC
(
model int,
speed DOUBLE(3,2),
ram int,
hd int,
price int
);

Representative SQL Query to insert data into PC table is given below:

INSERT INTO PC VALUES (1001,2.66,1024,250,2114);   

Output for INSERT INTO PC:

model speed ram hd price 1001 2.66 1024 250 2114

Solution ends.

Please comment and let me know if you have any further doubts. Please upvote this answer if you like it.

Thank you.

Have a good day.