Using the SP database, give the names of the suppliers who supplies the most parts and least parts Can the following SQL code generate the correct result Select subq1.sname, subq1.s#, subq1.Most_parts, subq2.sname, subq2.s#, subq2.Least_parts From (Select sname, s.s#, a.most_parts, a.rank rank from sp.s s. (select s#,count(p#) Most_parts,sum(qty) qty, rank()over (order by count(p#) desc) rank From sp.sp Group by s#)a Where s.s#=a.s# and a.rank=1) subq1, (Select sname, s.s#, b.Least_parts, b.rank rank from sp.s s, (select s#,count(p#) Least_parts,sum(qty) qty, rank()over (order by count(p#)) rank From sp.sp True False Group by s#) b Where s.s#=b.s# and b.rank=1) subq2 Where subq1.rank=subq2.rank;

Using the SP database, give the names of the suppliers who supplies the most parts and least parts Can the following SQL code generate the correct result Select subq1.sname, subq1.s#, subq1.Most_parts, subq2.sname, subq2.s#, subq2.Least_parts From (Select sname, s.s#, a.most_parts, a.rank rank from sp.s s. (select s#,count(p#) Most_parts,sum(qty) qty, rank()over (order by count(p#) desc) rank From sp.sp Group by s#)a Where s.s#=a.s# and a.rank=1) subq1, (Select sname, s.s#, b.Least_parts, b.rank rank from sp.s s, (select s#,count(p#) Least_parts,sum(qty) qty, rank()over (order by count(p#)) rank From sp.sp True False Group by s#) b Where s.s#=b.s# and b.rank=1) subq2 Where subq1.rank=subq2.rank;

Database System Concepts

7th Edition

ISBN:9780078022159

Author:Abraham Silberschatz Professor, Henry F. Korth, S. Sudarshan

Publisher:Abraham Silberschatz Professor, Henry F. Korth, S. Sudarshan

Chapter1: Introduction

Section: Chapter Questions

Problem 1PE

See similar textbooks

Related questions

Q: 1. Table : ASSIGNMENT D ASSIGNMENT N NMAON DATE MM EMP MUN AiNO AlON CHO AONHOURI NON CHA 35 42 t00…

A: Explanation In SQL, the SELECT query is used to fetch or retrieve the data from the database table.…

Q: What did each card cost? Show the card number, player last name, team name, price, price with 5%…

Q: Write the SQL code that will compute the total number of hours worked and total amount billed by all…

A: To compute the total number of hours worked and total amount billed by all workers, we need to have…

Q: Write the T-SQL scripts for the following tasks: - Write a query to list all the Employee's details…

A: let us see the answer:- Introduction:- We are going to use a select query to list all the Employees…

Q: Create the following SQL Server queries that access the Northwind database. Place them in txt file…

A: This question comes from MySQL which is a topic in computer science. In this question we are going…

Q: I need help figuring out the SQL quereys for the questions above using table joining method. 1)…

A: Please refer to the following step for the complete solution to the problem above.

Q: The following figure is part of a university database. LNAME and FNAME stands for last name and…

A: Project operation (denoted by pi (∏) symbol) is used to select desired columns (or attributes) from…

Q: Write the SQL queries for the following questions and show results. List all the section numbers of…

A: In the structured query language, the query consists of the three clauses that are used to retrieve…

Q: Write all the sql code (including create, insert and select)

A: #use the below query to create users tablecreate table users(account int primary key,name…

Q: the HR department needs the department name, manager first name, manager last name, and job title.…

A: SQL: SQL stands for Structuref Query Language. It is used to access and manipulate the database…

Q: Create the following table in your database with the following schema: Table: Products Column Name 1…

A: As per our guidelines, only 3 sub parts will be answered. So, please repost the remaining questions…

Q: Write PL/SQL or T-SQL procedures to accomplish the following tasks: A: obtain the name and credit…

A: Actually, SQL stands for Structured Query Language.

Q: How do I list List the ISBN, title, author last name(s), retail price, and the promotional gift for…

A: We have to ginen an Er Diagram in this ER diagram 8 entities And each entities hold some properties.…

Q: Write a SQL query. List names of all employees who have sold to customer Rachel Patterson (Note: You…

A: In this database problem, we are tasked with creating an SQL query that lists the names of all…

Q: Using DML SQL, write the appropriate SQL query for each of the following: Insert A new customer…

A: In SQL, DML stands for Data manipulation language which contains Select, Insert, Delete and Update…

Q: salary is above 4000 and job_id starts with a letter “S”

A: 1. SELECT * FROM employee WHERE (salary > 4000) AND job_id LIKE 'S%' 2. SELECT *,…

Q: Create a function to increment the salary of employees of an organization by 15%, and for employees…

A: Create a function in PL/SQL to increment the salary of employees of an organization by 15%, and for…

Q: Write all the sql code (including create, insert and select)

A: in the following section we will be writing the queries based on information provided in the above…

Q: By using this table answer this question in SQL: Show the department id, department name and the…

A: Given: Show the department id, department name, and the number of employees working in any…

Q: I am trying to find the average of data and Floor the results. How would you use AVG and FLOOR…

A: avg() is a function used to return the average of entire column floor() is a function used to…

Q: what sql statements could be use to find the following: A simple database composed of the following…

A: 1. SELECT * FROM DOCTORS WHERE DOC_SPECIALTY IN ('Dermatology', 'Neurology') ORDER BY DOC_FNAME…

Q: How do I do this question in SQL command? Use the Oracle database employees table and CASE…

A: Given, Use the Oracle database employees table and CASE expression to decode the department id.…

Concept explainers

SQL Query

Structured Query Language is the full form of SQL. It is one of the standard languages to deal with the relational database. It is used to insert, update, delete, and search the records stored in a RDBMS. The relational databases are created and managed by using SQL.

Question

Using the SP database, give the names of the suppliers who supplies the most parts and least parts
Can the following SQL code generate the correct result
Select subq1.sname,
subq1.s#,
subq1.Most_parts,
subq2.sname,
subq2.s#,
subq2.Least_parts
From (Select sname, s.s#, a.most_parts, a.rank rank
from sp.s s.
(select s#,count(p#) Most_parts,sum(qty) qty,
rank()over (order by count(p#) desc) rank
From sp.sp
Group by s#)a
Where s.s#=a.s# and a.rank=1) subq1,
(Select sname, s.s#, b.Least_parts, b.rank rank
from sp.s s,
(select s#,count(p#) Least_parts,sum(qty) qty,
rank()over (order by count(p#)) rank
From sp.sp
True
False
Group by s#) b
Where s.s#=b.s# and b.rank=1) subq2
Where subq1.rank=subq2.rank;

Expert Solution

This question has been solved!

Explore an expertly crafted, step-by-step solution for a thorough understanding of key concepts.

This is a popular solution!

SEE SOLUTION Check out a sample Q&A here

Step 1

VIEW

Step 2

VIEW

Trending now

This is a popular solution!

Step by step

Solved in 2 steps

SEE SOLUTION Check out a sample Q&A here

Knowledge Booster

Learn more about

Need a deep-dive on the concept behind this application? Look no further. Learn more about this topic, computer-science and related others by exploring similar questions and additional content below.

Similar questions

Write queries in SQL to answer each of the following questions: 1. Find all students in Comp. Sci. dept 2. Find all students with total credits > 100 3. Find all students who took course in Spring 2010 (Remove duplicates please) 4. Find all courses taken by the student whose ID is 76543 and also find his name create table student (ID varchar(5), name varchar(20) not null, dept_name varchar(20), tot_cred numeric(3,0), primary key (ID) ); create table takes (ID varchar(5), course_id varchar(8), sec_id varchar(8), semester varchar(6), year numeric(4,0), grade varchar(2), primary key (ID, course_id, sec_id, semester, year) ); insert into student values ('00128', 'Zhang', 'Comp. Sci.', '102'); insert into student values ('12345', 'Shankar', 'Comp. Sci.', '32'); insert into student values ('19991', 'Brandt', 'History', '80'); insert into student values ('23121', 'Chavez', 'Finance', '110'); insert into student values ('44553', 'Peltier', 'Physics', '56'); insert into student values…
Write the SQL code that will produce the results shown as below. Write the SQL code to calculate the ASSIGN_CHARGE values in the ASSIGNMENT table. Note that ASSIGN_CHARGE is a derived attribute that is calculated by multiplying ASSIGN_CHG_HR by ASSIGN_HOURS. Write the SQL code that will yield the total number of hours worked for each employee and the total charges stemming from those hours worked. The result is shown as below.
8) Display the firstname, lastname of the employees and the customerID that the employee served such that the employees are born after '1960-01-01'. Arrange the results by the last name. 6) Write a SQL query to output the product name and the quantity of the Orders of that product for those orders with more than 70 quantity. (hint: don’t have to use GROUP BY) NOTE: Please use only table joining to write the SQL quereys.
Include both SQL commands and SQL results in your answers: KimTay Pet Supplies is running a promotion that is valid for up to 20 days after an order is placed. List the invoice number, customer ID, customer first and last names, and the promotion date for each invoice. The promotion date is 20 days after the invoice was placed. Write PL/SQL (or MySQL) procedures to accomplish the following task: The procedure accepts the ID of a customer stored in the input variable named I_CUST_ID and displays the name (first name concatenated with last name) and credit limit of for this customer.