This is for my SQL class any help is appreciated. 1. Update your S table to set
ID: 3698887 • Letter: T
Question
This is for my SQL class any help is appreciated.
1. Update your S table to set the rating of Supplier s3 to 60 and its city to Ajo. Show your result by returning S.
2. Write the select statement that will select all the rows from table S having a NULL in the ratings column
3. 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.
4. 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...
5. 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
Query 1.
UPDATE S
SET rating = 60,
city = 'Ajo'
WHERE Supplier = 's3' -- Update data for s3 supplier only
SELECT * FROM S
Query 2:
SELECT * FROM S
WHERE rating IS NULL -- clause to check if rating is null
Query 3:
Select pid, 'weight > 15 lb' -- Query returns all the parts that have weight more than 15 pounds
From SP
Where weight > 15 -- weight is greater than 15 pounds
Union -- Merges vertically data of both queries
Select pid , 'supplied by s1' -- Query returns all the parts which are supplied by supplier one
from SP
Where supplier = 's1'
Query 4:
Standard syntax to create a table from another one in SQL server is:
Query for your question:
SELECT *
INTO X
FROM S
This will create the table and copy the data as well.
Query 5: View definition:
CREATE VIEW TopSuppliers
AS
SELECT S.sid, S.status, S.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.