A. Write the following queries in SQL: i. List the Part Names which are not used to service the vehicle of any customer. ii. List the customer number who has got his vehicle serviced maximum number of times. iii. List the customer number whose vehicle service used all the parts of vehicle type V1.

A. Write the following queries in SQL: i. List the Part Names which are not used to service the vehicle of any customer. ii. List the customer number who has got his vehicle serviced maximum number of times. iii. List the customer number whose vehicle service used all the parts of vehicle type V1.

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: Write a query in SQL to list the titles of the employees for a specific date

A: Hello student Greetings Hope you are doing great. Thank You!!!

Q: Just let me know what to write for the SQL theory: Question: Write a query to display the first…

A: Solution: Given,

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 SQL queries to answer the following questions:a. Which studen ts have an ID number that is…

A: SQL queries are given below

Q: Using the relations given in the appendix, write the SQL statements to perform the following…

A: i. The INSERT command is used to add a new record row to an existing table. The following statement…

Q: Write the following queries in SQL, using the relations in the appendix. i. Find the full names of…

A: i. The required SQL statement is given below: SELECT PAT_FNAME, PAT_INITIAL, PAT_LNAME, DRUG_NAME,…

Q: Create a sql procedure which takes part of a book title as input. When the procedure is called, it…

A: The database is the collection of data and information. SQL is the Structured Query language that is…

Q: Write a SQL query to count the number of orders that have a purchase amount greater than 1000

A: SUMMARY: - hence we discussed all the points

Q: the each customer total annual premium and order by the highest total annual premium to the least.…

A: I have written the query below:

Q: Write a stored procedure in MySQL, PL/SQL, or T-SQL that changes the price of an item with a given…

A: Query: CREATE OR REPLACE PROCEDURE ITEM_CATGRY_PROC ( I_ITEM_NUM OUT ITEM.ITEM_NUM%TYPE,…

Q: Express the following queries in SQL: a) List first name, last name and department ID of all…

A: Find the required SQL queries given as below :

Q: t the title of all books in the same category as books previously purchased by customer 1007. Don’t…

A: Lets see the solution.

Q: I need help with an SQL: Write an SQL query to display every therapist’s first name and last name…

A: Given: Create a SQL query that displays each therapist's first and last name as their complete name,…

Q: Write the T-SQL scripts for the following tasks: Write a query to select the employees whose…

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

Q: Write SQL code that will return the total number of hours worked for each employee and the total…

A: SQL code: The COUNT function calculates the number of numeric values in a list of inputs. COUNT…

Q: List the LastName, FirstName, and the total number of orders placed by each customer (as OrderCount)…

A: SQL is a structured query language, that's designed to facilitate the extraction the specific…

Q: Show the product names for products that have been ordered in quantities equal to or exceeding 120.…

A: SQL, or Structured Query Language, is a specialized programming language used for managing and…

Q: Using the information in the ASSIGNMENT table, write SQL code that will return the total number of…

A: Introduction: SQL syntaxThe SQL code for connecting the tables "Employee" and "Assignment" to get…

Q: Database course: Write SQL queries to do the following A. List names of IT students.…

A: The SQL queries are given in step 2.

Q: How do I create a query that includes first name, last name, email and state for all customers and…

A: Query:- SELECT first_name, last_name, email, state, CASE WHEN customer_type = 'Customer' THEN 'C'…

Q: Customer(cid,name,surname,city,address,phone); Account(aid,cid,amount,created,comment);…

A: - We need to write a sql query to get the names, surnames and phone number according to the transfer…

Q: Find the service ID and property ID for each service request whose estimated hours is greater than…

A: Hello student Greetings Hope you are doing great. Thank You!!!

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: Question 1: Show (in one table) customer id and first name from Customers table together with…

A: SOLUTION -1)Query -SELECT CT.customer_id, CT.first_name, ST.shipping_id, ST.statusFROM Customers AS…

Q: By using this table answer this question in SQL: Show last_name, department_id, and the date when…

A: The SQL query is written below:SELECT last_name, department_id, TO_CHAR(ADD_MONTHS(hire_date, 240),…

Q: Use SQL to write the following queries: a) Find the last names of students born after January 1,…

A: In this question we have to understand about the given SQL queries and suggest if these queries are…

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: 2. Determine which orders haven’t yet shipped and the name of the customer who placed the order.…

A: According to the information given:- We have to follow the instruction in order to perform the…

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 to calculate the total purchase amount of orders that have an even ID number

A: SQL Query are very useful to fetch the required data from database in the table form In this…

Q: Write the SQL code to generate the total hours worked and the total charges made by all employees.…

A: See the explanation section for a complete answer.Explanation:To calculate the total hours worked…

Q: in SQL, Create a Stored Function to identify the total number of staff assigned to each ward (the…

A: A stored function (also known as a consumer feature or user-described characteristic) is a fixed of…

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: For each band that currently has a female member: Return the name of the band and each artist's…

A: The question has asked us to join three tables, namely, p_artist, p_band, and p_member, based upon…

Q: Write the SQL code to list the customer ID, first name, last name, street, city, state, zip and the…

A: SQL stands for Structured Query Language. It is a standard programming language designed for…

Q: List the names of the production companies and the titles of their movies. This time, include all…

A: Given that, The names of the tables are Movie and Production Assume that, The attributes of Movie…

Q: Solmaris is offering a monthly discount for owners who pay their condo fees on a quarterly basis.…

A: Actually,the given information is The discount is 1.75 percent of the monthly fee. For each condo,…

Q: use sql to answer the following question Find all orders that have been issued after the order date…

A: SQL is an ANSI/ISO standard, there are various versions of the SQL language. Still, to be compliant…

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: QUESTION 2. Write a PL/SQL PROCEDURE that can list the Top-N customers (id and name) and the amounts…

A: ANSWER: PL/SQL Procedure:

Q: (a) How do you take union of two tables in SQL? (b) How do you take the intersection of two tables…

A: SQL stands for Structured Query Language. It is a standardized programming language used for…

Q: Write a query in SQL to display all the data of customers, in Table customers, that live in New York…

A: If any of the conditions separated by OR is TRUE then the OR operator displays a record. All fields…

Q: Use SQL to write the following queries: 1. a) Find the last names of students born after January 1,…

A: a) Find the last names of students born after January 1, 2000:1SELECT last_name 2FROM students…

Q: Write a SQL query, which shows a list of all employees last name, their department names and their…

A: SELECT e.last_name, COALESCE(d.department_name, 'N/A') as department_name,…

Q: Retrieve the PRODUCT_NAME that product UNIT_PRICE less than the 10 (use subque

A: Always specify the tables to get the correct answer Answer

Concept explainers

Multiple Table

In a database, a table is used to store data in the form of rows and columns. Rows are aligned horizontally and identified by one or more values in a specific column. Columns are vertically aligned and identified by a unique column name.

Question

Schema:
Part(PartNo, PartName, VehicleType, UnitPrice, SalesPrice)
Service(ServiceNo, PartNo, ServiceDate, CustomerNo, Qty)
Constraints: i. Vehicle Type should be V1', V2', 'V3', 'V4', or 'V5'
ii. UnitPrice and SalesPrice should be greater than zero
iii. PartNo, FK in Service refers to PartNo, PK in Part
Data:
Part
Service
P1
V1
10
12
1
01-Jan-17
C1
P2
V2
100
110
3
01-Jan-17
C1
4
P3
V1
150
175
2
3
05-Feb-18
C2
10
P4
V3
200
250
3
1
15-May-18 Сз
9.
P5
V2
75
90
1
03-Jun-19
C1
A. Write the following queries in SQL:
i. List the Part Names which are not used to service the vehicle of any customer.
ii. List the customer number who has got his vehicle serviced maximum number of
times.
iii.
List the customer number whose vehicle service used all the parts of vehicle type V1.
B. Write a PL/SQL program to find the total profit done in the sales of a given part number in
the service of different customer vehicles. Raise an exception for invalid part number.
12345n

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

Introduction

A. i.

A. ii.

A. iii.

Conclusion

Trending now

This is a popular solution!

Step by step

Solved in 5 steps with 3 images

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

How do I do this question in SQL command? Use the Oracle database employees table and CASE expression to decode the departmentid. Display the department id, last name, salary, and a column called “New Salary” whose value is based on the following conditions: If the department id is 10 then 1.25 * salary If the department id is 90 then 1.5 * salaryIf the department id is 130 then 1.75 * salary Otherwise, display the old salary.
4) Display the firstname, lastname of the employees and the customerID that the employee served. Arrange the results by the last name. 5) Write a query to the Name of the Product and the corresponding name of the supplier of that Product for all the products that start with a “P”. 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) I need help figuring out the SQL quereys for the questions above using table joining method
Write a query in SQL code for: "List the book categories and the average book price of each category." Tablename is "BOOKS" and column names are: ISBN TITLE PUBDATE PUBID COST RETAIL DISCOUNT CATEGORY
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…
1. Write an SQL query to list the name of all Departments that have 2 more more words in the Department name and whose employees have the designation of a ‘staff’ and are from Houston. (write subqueries) 2. Write an SQL query to display the Project Name of all projects where employees have worked for 5 hours or less or 25 hours or more and the Project Names start with a “T” or a “C”. (write subqueries)
answer with proper explanation and step by step solution. Question write a PL/ SQL program to display the number of products whose price is 100 dh more or less than the price defined the for substitution variable use the define command to specify the price pass this value to the PL/SQL black using a substitution variable
I need to write a SELECT query in SQL using these instructions. Attached is the code that created the tables. SHOW name-of-tech part which are less than $300 and The Name of the Supplier. This will show which supplier has what parts under $300. FOR [Supplier] QUERY WILL USE: JOIN yes SUBQUERY no AGGREGATION no
Write a SQL code that returns the name of aircraft (MOD_NAME in MODEL table), number of trips each mode flied (based on the CHARTER table, name this Trip_Num), and Total distance of each mode (based on the CHARTER table, name this Total_Distance).
SQL Database Write PL/SQL or T-SQL procedures to retrieve and output the reservation ID, trip ID, number of persons, and trip price for every reservation whose customer number is stored in I_CUSTOMER_NUM
Write the SQL syntax to create a table in the database called Employee with the employee first and last name, employee hire date, employee number, employee initial, and employee years.
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.