Using the Schema - data/statistics. Transform following SQL queries intoRelational Algebraic queries.ItemsSalesID|_NamePriceSupplierS DateSID1CIDKetchup50Block Factory2012-5-2312012-5-242016-4-232016-2-122016-1-10222Salt40UniqueManufacturerS&s45364Soap301International4Toothpaste33BrothersEnterpriseGeneralSugar20BillTraders6Detergent34AlibabaSID1ID1Express1231Customer4CIDNameCity1AqibLahore22.SulemanPeshawar233NadeemSahiwal2PeshawarPeshawarOkaraPeshawar434SarimSohail436.Nadir42Nasir448.KamalLahorea) Select * from Items i, Bill b, Sales s, Customer c where c.City='Peshawar'b) Select c.Name from Customer c inner join Sales s on s.CID=c.CID inner joinBill b on s.SID=b.SIDc) (Select * from Customer) Except (Select c.* from Customer c inner join Saless on c.CID=s.CIID)d) Select * from Customer where City='Sahiwal' or City='Peshawar'e) Select I_Name from Items I, Bill b where ID in(1,4,2)

Required : SQL query to relational algebra query Note : As per guidelines of company, we are…

Answered: Using the Schema - data/statistics.…

Engineering

Computer Science

Using the Schema - data/statistics. Transform following SQL queries into Relational Algebraic queries.

Oracle 12c: SQL

3rd Edition

ISBN:9781305251038

Author:Joan Casteel

Publisher:Joan Casteel

Chapter12: Subqueries And Merge Statements

Section: Chapter Questions

Problem 19MC

See similar textbooks

Related questions

Q: What are the key capabilities of NoSQL that extend what SQL can do?

A: key capabilities of NoSQL that extend what SQL can do ( 1 ) Flexible : - NoSQL is highly…

Q: Compare two versions of SQL to which you have access, such as Microsoft Access and Oracle SQL*Plus.…

A: Dissimilairities: MySQL: MySQL is an open-source relational database management system. Just like…

Q: I want SQL query to update/insert data from table 1 to table 2 where the dates from first table…

A: Solution Before writing the required query, first i am creating 2 tables similiar to requirement.…

Q: What is the SQLcommand to list the total sales by customer and by product,with subtotals by customer…

A: Structured Query Language is abbreviated as SQL or also pronounced as See-Qwell. It is developed for…

Q: What does it mean when you say a field is calculated? In a SQL query, how should I use it? What's…

A: An expression may be tested to see whether it matches any value in a list of values using the SQL IN…

Q: Consider the following database tables: Employee (empNo, empName, street, city) Works (empNo,…

A: The answer for the given question is given below:

Q: Create the following SQL queries with the university schema in mind. Find the lowest maximum salary…

A: Query for determining the minimum wage Using the select statement, you may get data from a database.…

Q: Write an SQL query to print all Worker details from the Worker table order by FIRST_NAME Ascending.…

A: Syntax for order by :- By default when you don't mention ascending or descending it takes…

Q: Write an SQL query to remove the product "RADAR" from the client_details.

A: SQL Query is provided in step 2.

Q: a cross join and metada

A: SQL CROSS JOIN produces a result set which is the number of rows in the first table multiplied by…

Q: Q.1: Write SQL queries, using "Movie Rental Database" schema to extract following information i.…

A: Note: There are multiple questions are given in one question. According to the rule, you will get…

Q: Def

A: UNION ALL In Oracle, the UNION ALL operator is used for amalgamating the result sets of 2 or more…

Q: Is it likely that wholeworldband will use SQL, NOSQL, or a combination of the two? Your response…

A: The database technology is used by the whole globe and derived from SQL NoSQL. SQL is a query…

Q: Create a separate table in sql for each entity like job_history , jobs, employees, departments,…

A: The queries of creating and inserting data into tables are given in next steps

Q: Justify your preference for embedded SQL versus SQL alone or a general-purpose programming language.

A: Scenarios you can choose to use embedded SQL over SQL alone or general-purpose programming language.…

Q: Can you help for a sql and a code that shows the records such as firstname, lastname, middlename,…

A: i give this solution in step 2 first of all create database in mysql and connect after run this code…

Q: ssign a name to a computed field? give few examples

A: What is a computed field? How can you use one in an SQL query? How do you assign a name to a…

Q: Determine the output of the following SQL queries. 1. SELECT DATEADD(minute, 3, DATEADD(second, 30,…

A: As per the guidelines, we are advised to answer only the first three subparts of a question. Please…

Q: Write SQL server query to retrieve all the students who have grade A and are in class 12 Student…

A: SELECT * FROM STUDENT WHERE GRADE = 'A' AND CLASS = 12;

Q: Write SQL query to select eName of all employees from table Employee (eName, eID, Address).

A: We will use the SELECT statement in SQL to select any column names. Syntax SELECT Column_One,…

Q: How to write a SQL sentence by grouping and aggregation? What is a null value? How to find…

A: write a SQL sentence by grouping and aggregation? What is a null value? How to find information with…

Q: SELECT FROM ( SELECT MAX(count) er.trainee_id, COUNT(er.score) AS count

A: Considering a Table "exam_results" populated with some Sample Data:

Q: What is the query used to see if the date format is same and find values other than dates in the…

A: I am giving some examples along with queries to explain this. Suppose:…

Q: Write SQL query to fetch the Department of all employees with Name "Andrew" from table…

A: This query can be solved using the WHERE clause of SQL.

Q: get PL/SQL language to operate correctly in MySQL

A: get PL/SQL language to operate correctly in MySQL

Q: List and describe the five SQL privileges available with the GRANT or REVOKE statements.

A: answer is

Q: Please give a detailed answer for the below three questions along with code examples. How would you…

A: Given: How would you retrieve a count of all items in an SQL table? What command allows you to…

Q: Write an SQL query to report the name, population, and area of the big countries. Return the result…

A: logic:- here we have to apply two condition. I.e (i)area>=3000000 (II) population>=25000000…

Q: Write SQL Server Query to retrieve students with highest marks from below table Student(Sid,…

A: SQL MAX Command Sometimes, there will be a requirement to find the highest value in a field of any…

Q: Create an OLAP model and sample cases with MS SQL Server’s (2014) SSIS (Integration Services), SSAS…

A: SQL Server Reporting Services (SSRS) is part of Microsoft SQL Server services since version 2000. If…

Q: Discuss the reason of converting the SQL query into relational algebra before optimization…

A: Query: A query is a request for information from a database. A single query can be executed through…

Q: What is the aggregation function provided by the SQL standard for ordering the results of a query…

A: Actually, aggregation is a process of combining things.

Q: Write a sample SQL query to look at data by week for the created_at date, where you're summing…

A: SELECT DATEPART(week, created_at) AS Week, Sum(revenue) AS Revenue FROM country WHERE '20180101'…

Q: Discuss the T-SQL analytics functions and how they're used to generate perspective values. Give an…

A: It is a standard programming language that is used to manage relational databases and perform…

Q: Define INTERSECT and give one (1) example in ORACLE SQL commands.

A: INTERSECT operator, which returns only those unique rows returned by both queries:

Q: Write the following queries in SQL, using the university schema. Find the lowest, across all…

A: Query for selecting minimum salary The select statement is used to select data from a database.…

Q: Write an SQL query to extract the answer directly.

A: Dear Student, The SQL command is given below If you have any doubt feel free to ask - SELECT…

Q: Write SQL query to fetch the Names of all employees in IT department from table Employee (Name, Id,…

A: Given: Write SQL query to fetch the Names of all employees in IT department from table Employee…

Q: Using the university schema, write an SQL query to find the names of thosedepartments whose budget…

A: QUERY USED:- select d2.dept_name,d2.budget from depart d1,depart d2 where d1.dept_name='philosophy'…

Q: LIKE operator in SQL? Support your answer with examples.

A: Answer: If the programmer wants to compare a value with similar values that are present in a…

Q: 4) Write an SQL query to display Name, address,transaction_date, fixed_deposit from bank_masterand…

A: Subject: DBMS Write an SQL query to display Name ,address,transaction_date, fixed_deposite from…

Q: Write an SQL query to list all salespersons who work in the territory where the most end tables have…

A: GIVEN: Write an SQL query to list all salespersons who work in the territory where the most end…

Q: Write a SQL query to print Name of all selected candidates from table: Job(JobId, Name, AppId,…

A: Algorithm : Step 1 : select the name of candidates those who are selected. Since Selected(Yes/No)…

Q: Write a SQL query to list the product code, vendor code, and vendor name for all products and…

A: Use a right outer join or left outer join based on where we are specifying the VENDOR and PRODUCT If…

Q: Question: Which colleges offer courses? (Change the SQL query to an relational algebra query.)…

A: Given Sql query will provide college name from course relation with department code in course is…

Q: SQL query select with condition of multiple values from column?

A: What is SQL query Ans :- An SQL SELECT statement gets records from a database table based on…

Q: Using HR Schema, write a query to find first name, last name and department ids for employees in…

A: Solution in Step 2.

Q: What is the definition of a calculated field? What is the best way to utilise one in a SQL query?…

A: Given that: What is a computed field? How can you use one in an SQL query? How do you assign a name…

Concept explainers

Single Table

In a database management system, a single table is designed to obtain data of heterogeneous items. A single-table design with DynamoDB resolves the issue of raising multiple requests for the relevant data of an entity.

Question

Using the Schema - data/statistics. Transform following SQL queries into
Relational Algebraic queries.
Items
Sales
ID
|_Name
Price
Supplier
S Date
SID
1
CID
Ketchup
50
Block Factory
2012-5-23
1
2012-5-24
2016-4-23
2016-2-12
2016-1-10
2
2
2
Salt
40
Unique
Manufacturer
S&s
4
5
3
6
4
Soap
30
1
International
4
Toothpaste
33
Brothers
Enterprise
General
Sugar
20
Bill
Traders
6
Detergent
34
Alibaba
SID
1
ID
1
Express
1
2
3
1
Customer
4
CID
Name
City
1
Aqib
Lahore
2
2.
Suleman
Peshawar
2
3
3
Nadeem
Sahiwal
2
Peshawar
Peshawar
Okara
Peshawar
4
3
4
Sarim
Sohail
4
3
6.
Nadir
4
2
Nasir
4
4
8.
Kamal
Lahore
a) Select * from Items i, Bill b, Sales s, Customer c where c.City='Peshawar'
b) Select c.Name from Customer c inner join Sales s on s.CID=c.CID inner join
Bill b on s.SID=b.SID
c) (Select * from Customer) Except (Select c.* from Customer c inner join Sales
s on c.CID=s.CIID)
d) Select * from Customer where City='Sahiwal' or City='Peshawar'
e) Select I_Name from Items I, Bill b where ID in(1,4,2)

Expert Solution

This question has been solved!

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

SEE SOLUTION Check out a sample Q&A here

Step 1

VIEW

Step 2 : Relational algebra query symbols

VIEW

Step 3 : a,b,c

VIEW

Step by step

Solved in 3 steps with 1 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

Write the SQL code to delete the row for William Smithfield, who was hired on June 22, 2004, and whose job code is 500. (Hint: Use logical operators to include all of the information given in this problem.)
CONVERT the following sql(apache derby) code to relational algebra. select P.fuser,P.luser, B.TIME, S.DATE from PATIENTS as P join BD_SAM BS on P.P_ID = BS.P_ID join SUB S on P.P_ID = S.P_ID join SUB_N SN on SN.SUB_C = S.SUB_C where lower(SN.NAME) like 'chemical' and S.AMOUNT > 190;
In advanced SQL, which of the following is a SQL query that performs execution of outer query for once in a row similar to looping? Choose an answer A Attribute list Subqueries B HAVING Subqueries C FROM Subqueries D Correlated Subqueries
Create a PL/SQL query to display the combined customer name, coin purchased, coin price andthe delivery notes. In your query only display the coins purchased with a price less than or equal to R 8 000. Sample Output:CUSTOMER: Mat, SmithCOIN: 1oz Gold Kruger RandPRICE: R 5 999NOTES: Double packaging requested-------------------------------------------------------CUSTOMER: Lucy, WilliamsCOIN: 1oz Gold Kruger RandPRICE: R 5 999NOTES: No notes-------------------------------------------------------CUSTOMER: Sam, ClarkCOIN: 1oz Silver PalaeontologyPRICE: R 7 999NOTES: Delivery to work address-------------------------------------------------------
SELECT P.BRAND_ID, B.BRAND_NAME, B.BRAND_TYPE,MAX(AVGPRICE) FROM LGPRODUCT P INNER JOIN LGBRAND B ON P.BRAND_ID = B.BRAND_ID (SELECT P.BRAND_ID, AVG (P.PROD_PRICE) AS AVGPRICE FROM LGPRODUCT P GROUP BY P.BRAND_ID)AS AVG_PRICE GROUP BY P.BRAND_ID, B.BRAND_NAME, B.BRAND_TYPE ERROR 1064 (42000) at line 1: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SELECT P.BRAND_ID, AVG (P.PROD_PRICE) AS AVGPRICE FROM LGPRODUCT P GROUP BY P.' at line 3
Please assist to fix the query below: use SQLBook; WITH oz as (SELECT zc.zcta5,(COUNT(DISTINCT c.HouseholdId) /MAX(zc.tothhs*1.0) ) as penetration,zc.tothhs, zc.medianhhinc, zc.pctbachelorsormore),ozm as (SELECT AVG(medianhhinc) as avgmedianhhinc,STDEV(medianhhinc) as stdmedianhhinc,AVG(pctbachelorsormore) as avgpctbachelorsormore,STDEV(pctbachelorsormore) as stdpctbachelorsormoreFROM oz),ozs as (SELECT oz.*,((oz.medianhhinc - ozm.avgmedianhhinc) / ozm.stdmedianhhinc) as z_medianhhinc,((oz.pctbachelorsormore - ozm.avgpctbachelorsormore) /ozm.stdpctbachelorsormore) as z_pctbachelorsormoreFROM oz CROSS JOIN ozm)SELECT TOP 10 ozs,*,SQRT(SQUARE(ozs.z_medianhhinc - ozs10021.z_medianhhinc) +SQUARE(ozs.z_pctbachelorsormore –ozs10021.z_pctbachelorsormore)) as distFROM ozs CROSS JOIN(SELECT ozs.* FROM ozs WHERE ozs.zcta5 = '10021') ozs10021ORDER BY dist ASC
Write an appropriate SQL query to display all the STUDENT_SURNAMES and STUDENT_NAMES, as well as the MODULE_NAMES that the student is registered for. Sort results according to student surname in ascending order.Sample results:STUDENT MODULEMoore, Derek Database IntermediateNtaba, Pedro Database IntermediateNtaba, Pedro Desktop PublishingNtaba, Pedro Web Development (Intermediate)Petlele, Neo Programming 2APetlele, Neo Programming 2BSmith, Dominique Programming 2ASmith, Dominique Programming 2BSmith, Dominique Web Development (Intermediate)
Use T-SQL Query to list down the above users and their respective permissions and write the output of your query below. Hint: Use the T-SQL Query from the link below and make the necessary adjustment. https://sqlgeekspro.com/query-to-retrieve-all-permissions-for-a-user-in-selective-databases/
Your colleague, John, has been struggling with a PL/SQL query that should display the Customer ID and the House Number, including the Materials used in the construction of the townhouses. John doesn’t seem to be able to determine why the query is not working and has asked for yourassistance. Identify the errors and re‐write the correct code: set serveroutput on c_id CUSTOMER.cust_id%Type; t TOWNHOUSE.house_num%Type; m_name MATERIALS.material_name%Type;cursor info isselect cu.cust_id, t.house_num, m.material_namefrom CUSTOMER cu, TOWNHOUSE t, MATERIALS m, PURCHASE_AGREEMENT pawhere cu.cust_id = pa.cust_idand t.house_num = pa.house_numand m.material_id = pa.material_idbeginfor rec in info c_id:=rec.cust_id; t:= rec.house_num; m_name:=rec.material_name; dbms_output_put_line('CUSTOMER ID: ' || c_id || ', ' || chr(13) ||'HOUSE NUM: ' || t ||chr(13) || 'MATERIALS: ' || m_name);…
Please help me to transcribe SQL codes to python codes create table ab asselect *from aliWHERE itemprice IS NOT NULL; SELECT COUNT(MerchandiseCode), UserIDFROM abGROUP BY useridORDER BY COUNT(MerchandiseCode) DESC; select sum(totalsales), useridfrom abgroup by useridorder by sum(totalsales) desc; SELECT COUNT(transactiondate), UserIDFROM abGROUP BY useridORDER BY COUNT(transactiondate) DESC;
Write SQL retrieval commands for each of the following queries: Display the course lD and course name for all courses with an ISM prefix. Display all courses for which Professor Berndt has been qualified. Display the class roster, including student name, for all students enrolled in section 2714 of ISM 4212. Write an SQL query to answer the following question: Which instructors are qualified to teach ISM 3113? Write an SQL query to answer the following question: ls any instructor qualified to teach ISM 3113 and not qualified to teach ISM 4930? Write SQL queries to answer the following questions: How many students were enrolled in section 2714 during semester I-2008? How many students were enrolled in lSM 3113 during semester l-2008? Write an SQL query to answer the following question: Which students were not enrolled in any courses during semester l-2008?
Please answer the following in SQL: Find all pairs of customers who have purchased the exact same combination of cookie flavors. For example, customers with ID 1 and 10 have each purchased at least one Marzipan cookie and neither customer has purchased any other flavor of cookie. Report each pair of customers just once, sort by the numerically lower customer ID. The MySQL-specific GROUP_CONCAT and JSON_ARRAYAGG functions are not permitted. customers dataset CId: unique identifier of the customerLastName: last name of the customerFirstName: first name of the customer goods dataset GId: unique identifier of the baked goodFlavor: flavor/type of the good (e.g., "chocolate", "lemon") Food: category of the good (e.g., "cake", "tart")Price: price (in dollars) items dataset Receipt : receipt number Ordinal : position of the purchased item on the receipts. (i.e., first purchased item, second purchased item, etc...)Item : identifier of the item purchased (see…

SEE MORE QUESTIONS

Recommended textbooks for you

Oracle 12c: SQL

Computer Science

ISBN:

9781305251038

Author:

Joan Casteel

Publisher:

Cengage Learning

Database Systems: Design, Implementation, & Manag…

Computer Science

ISBN:

9781305627482

Author:

Carlos Coronel, Steven Morris

Publisher:

Cengage Learning

Database Systems: Design, Implementation, & Manag…

Computer Science

ISBN:

9781285196145

Author:

Steven, Steven Morris, Carlos Coronel, Carlos, Coronel, Carlos; Morris, Carlos Coronel and Steven Morris, Carlos Coronel; Steven Morris, Steven Morris; Carlos Coronel

Publisher:

Cengage Learning

Oracle 12c: SQL

Computer Science

ISBN:

9781305251038

Author:

Joan Casteel

Publisher:

Cengage Learning

Database Systems: Design, Implementation, & Manag…

Computer Science

ISBN:

9781305627482

Author:

Carlos Coronel, Steven Morris

Publisher:

Cengage Learning

Database Systems: Design, Implementation, & Manag…

Computer Science

ISBN:

9781285196145

Author:

Steven, Steven Morris, Carlos Coronel, Carlos, Coronel, Carlos; Morris, Carlos Coronel and Steven Morris, Carlos Coronel; Steven Morris, Steven Morris; Carlos Coronel

Publisher:

Cengage Learning

SEE MORE TEXTBOOKS