Use this code to make a database and answer the questions below: drop table S; d
ID: 3699192 • Letter: U
Question
Use this code to make a database and answer the questions below:
drop table S;
drop table P;
drop table SP;
create table S(
sid Char(2) not null primary key,
sname Char(4) not null,
rating Int not null,
city Char(4) not null
);
insert into S values
('s1', 'SSSS', 20, 'Tucs'),
('s2', 'JJJJ', 10, 'Phox'),
('s3', 'BBBB', 30, 'Phox'),
('s4', 'CCCC', 20, 'Tucs'),
('s5', 'AAAA', 30, 'Flag')
;
create table P(
pid Char(2) not null primary key,
pname Char(4) not null,
weight Int not null,
city Char(4) not null
);
insert into P values
('p1', 'Nut', 12, 'Tucs'),
('p2', 'Bolt', 17, 'Phox'),
('p3', 'Scrw', 17, 'Casa'),
('p4', 'Scrw', 14, 'Tucs'),
('p5', 'Cam', 12, 'Phox'),
('p6', 'Cog', 19, 'Tucs')
;
create table SP(
sid Char(2) not null,
pid Char(2) not null,
qty Int not null,
Primary Key (sid, pid)
);
insert into SP values
('S1', 'P1', 300),
('S1', 'P2', 200),
('S1', 'P3', 400),
('S1', 'P4', 200),
('S1', 'P5', 100),
('S1', 'P6', 100),
('S2', 'P1', 300),
('S2', 'P2', 400),
('S3', 'P2', 200),
('S4', 'P2', 200),
('S4', 'P4', 300),
('S4', 'P5', 400)
;
QUESTIONS
Write the SQL code for the following:
QA(20 pt): Update your S table to set the rating of Supplier s3 to 60 and its city to Ajo. Show your result by returning S.
QB(20): Write the select statement that will select all the rows from table S having a NULL in the ratings column
QC(20): Get part numbers that either weight more than 15 pounds or are supplied by Supplier s1
Include a constant in the column headings to indicate where that select came from:
The two constant column heading should be: ‘weight > 16’, ‘supplied by s1’
Hint this should get you started…
Select pid, ‘weight > 16 lb’
From xxxx
Where xxxx
Union
Select pid , xxxx
from SP
Where xxxx.
QD(20) The “Select Into” Alternative
As I described in the study guide, SQL-Server uses a non-standard approach to creating one table from another. So, using standard SQL code, create a new table, ‘X’, based on S, and populate it with the data from S. Use the two step approach laid out in the study guide. Note the syntax necessary at each step...
QE(20): A better view..
Create a view table, TopSuppliers, showing S.sid, S.status, and S.city, from the S table where rating is >20.
Explanation / Answer
QA:
UPDATE S
SET rating = 60, city = 'Ajo'
WHERE sname = 's3';
QB:
SELECT *
FROM S
WHERE rating IS NULL;
QC:
SELECT pid, ‘weight > 15 lb’
From P
Where weight > 15
Union
Select pid , 'supplied by s1'
from SP
Where sid = 's1';
QD:
QE:
CREATE VIEW TopSuppliers
AS SELECT sid, sname, city
FROM S
WHERE rating > 20;
Related Questions
drjack9650@gmail.com
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.