Compute the average number of hours for the courses offered by each college.
Q: Table name is : Clothing 1. Give me the sum, max and min for sales unit price. 2. Order item name…
A: Algorithm : Answer 1) find the sum , min and max of the sales unit price from the table Clothing.…
Q: Show the average salary for each different job_id group in the employee table
A: Code: SELECT JOB_ID, AVG(SALARY) FROM Employees WHERE JOB_ID <> 'IT_PROG' GROUP BY JOB_ID;
Q: Measured by average standard price, what is the least expensive product finish?
A: SELECT ProductFinish FROM Product_T WHERE ProductStandardPrice= ( SELECT…
Q: To find the visa processing fee for tourists according to the following calculation: Read the number…
A: The required C++ program code for given problem is as follows: #include <iostream>using…
Q: For each employee, show their full name and the region name of their department
A: Assuming Sql query for given statement is asked, considering table employee, with attributes…
Q: List the number of customers living at each state that is included in the Customer_T table.
A: Query:-
Q: Read the marks of a student for 3 courses. Calculate the average marks for the student. Check the…
A: 1. Take marks for the three courses as input from the student 2. Take average of the three numbers…
Q: Normalize the below given Student Course Plan to First Normal Form, Second Normal Form and Third…
A: From the given question we can identify the below functional dependencies which are violating the…
Q: Make a list of salespeople who made no sales on a Monday (Hint: your answer to question 1 is a good…
A: SELECT Salesperson, count(Sales.Date) from Sales,Calendar where DayofWeek="Monday" and…
Q: a) name of the advisor of the student who takes highest number of courses?
A: According to the question we have to find the student id who takes the highest no of courses: SELECT…
Q: Company ABC in Kenya pays its employees based on a commission of the sales they make as illustrated…
A:
Q: ployees ase. Calculate across all employees in this table, the average age, as of today, rounded to…
A: Here we will find average age of employee using AVG keyword and DATEDIFF function
Q: Every student is taking at least one course. There is a part-time student who is not taking any math…
A: 1)Every student is taking at least one course. every student -∀x taking at least one…
Q: Show students’ first name and last name, the course numbers the student took (don’t include…
A: Data is coming from 3 tables . So apply inner join on 3 tables with joining condition. student…
Q: Q16) List the V_CODE and V_NAME of vendors that provide products Q8) List the product description,…
A: Structured Query Language is abbreviated as SQL or also pronounced as See-Qwell. It is developed for…
Q: List the name of all departments that have more than four courses (you are not allowed to use join).
A: The SQL query is given in next step without using JOIN
Q: List details of all customers from Canberra that have enrolled in a course order by Customer Number.
A: Answer
Q: Find the rank of each department in terms of total salary
A: since question has not provided name of sample tables so we assume table1 and table2 as name of…
Q: Calculate the total working wage during the first week?
A: 1) In Excel first row starts with A1, B1, C1 .... where we have headers like, week, week hours,…
Q: What SQL query is required to determine the total quantity of beers?
A: Introduction: SQL (Structured Query Language) is a standardised computer language used to manage…
Q: Print each employee, their wages, Hours and gross pay.
A: namesList=['Juan','Rae','Ivanna','Lilly','Robert']; #list to store names…
Q: 1. Display all details of the item table. 2. Display item name, section and price of all garments.…
A: Queries: 1) SELECT * FROM Item;
Q: Determine which types of books are currently available in the current inventory. Each category…
A: Real-world examples can make inventory models easier to understand. The following examples…
Q: For each customer, list the CustomerID and the total number of orders placed in 2015.
A: Answer: Query: select Order_T.Customer ID,sum(OrderLine_T.Ordered Quant from OrderLine_T inner join…
Q: balance that exceeds their credit limit?
A: How many customers have a balance that exceeds their credit limit? Below is the query :
Q: Read the marks of a student for 3 courses. Calculate the average marks for the student. Check the…
A: Start Read marks of 3 courses Find average Initialize CGPA to 0 if average is in range 8-10 then…
Q: Find the names of all patients who have not consulted any doctor
A: Patient Table: create table Patient(PID Integer Primary Key,Name varchar2(30),Gender…
Q: Find the 5 earliest hired employees
A: the query to find the 5 earliest hired employees from the table is mentioned in step 2. please note…
Q: The name of the students that has the highest total credit. table =student students have >>…
A: Table name - student The table student has id,name, department_name, total_credit are the entites.…
Q: Classify students according to the total number of hours they have completed. (Less than…
A: Note:- There are multiple hours completed columns, two are visible in the first screenshot, assuming…
Q: Provide a summary of customer balance characteristics for customers who made purchases. Include the…
A: REQUIRED : Executable query to find average, minimum, maximum balance. INTRODUCTION : QUERY is used…
Q: What city does Mark Dean reside in
A: Introduction: Mark E. Dean (born March 2, 1957) is an inventor and computer engineer from the United…
Q: Q14) how many products with price < 10
A: You have asked multiple questions so we will solve the first one for you. If you want any other…
Q: Find the total of the balances for all customers represented by sales rep 10 with balances that are…
A: Note- As I solved this question in one part I know that here I have to write the SQL query for the…
Q: Display the state of customers, the total and the average purchase amounts of each state in 2017…
A: Solution is in Step 2.
Q: List all customers who were referred to the bookstore by another customer. List each customer's last…
A: The way the question is put up. It is very likely that the question is of SQL language. Also, you…
Q: Give an alphabetical list of manufacturers who have had products sold in June of 2015? Only show…
A: Before retrieving a list of manufacturers, we need to create a table named SimplifiedSales in the…
Q: Find the name of the organization that has the smallest grant contribution.Using mysql
A: Given: Find the name of the organization that has the smallest grant contribution.Using mysql
Q: Show the employees who were in the engineering department as of Jan. 1, 2005 (using SQL)
A: Given : Show the employees who were in the engineering department as of Jan. 1, 2005 (using SQL)…
Q: Q17) List the V_CODE and V_NAME of vendors that do not provide products Q18) ?How much is the total…
A: Part(17) Following is the query for the listing V_CODE and V_NAME of those vendors that does not…
Q: 8. List the names of hotels who has no booking in them
A: As per our guidelines, we are supposed to answer only one question. Kindly repost the remaining…
Q: List all book titles published between year 2012 to 2020.
A: We are going to write SQL query which will list all book titles published between year 2012 to 2020.
Q: Determine the sorts of books that are presently available in the inventory. Only one of each…
A: Introduction: The DISTINCT clause can be used to delete duplicate records from a result set of…
Q: Normalize the below given Student Course Plan to First Normal Form, Second Normal Form and Third…
A: The given table is: Student_Car(CarPlateNo, CarDesc, CarType, BillNo, BillDate, Penalty, FinalBill)…
Q: 1. Get top 5 list of most liked and expensive products sorted by retailer name for every city
A: Explanation: The below query selects the Top 5 table data based on city, rating and price. The…
Q: Which product is ordered most frequently?
A: Answer; SQL queries The command used to display the product that is ordered most frequently is as…
Q: An apartment owner rents one-bedroom apartments for $575 and two-bedroom apartments for $650. A…
A: Find out the number of one-bedroom and two-bedroom apartments from the given values of rent for the…
Find the relation algebra query based on the SQL given.
Step by step
Solved in 2 steps
- The following attributes fom1 a relation that includes information about individua l computers, their vendors, software packages running on the computers, computer users, and user authorizations. Users are authorized to use a specific software package on a specific computer d uring a specific time frame (characterized with attributes UserAuthorization Starts and UserAuthoriza tionEnds and secured with UserAuthorizationPassword}. Software is licensed to be used on specific computers (potentially multip le software packages at the san1e time) until an expira tion time (SoftwarelicensePrice) a t a specific price. Computers are sold by vendors, and each vendor has a support person with an ID, nan1e, and phone extension. Each individual computer has a specific purchase price. The attributes are as follows: Based on this information, a. Identify the functional dependencies between the attributes. b. Identify the reasons why this relation is not in 3NF. c. Present the a ttributes organized so….Here it isstudent Relation Student_ID Student_Name Dept_Name 0000 AAAAA Computer Science 1111 BBBBB Computer Science 2222 CCCCC Business Administration 3333 DDDDD Education 4444 EEEEE Physics 5555 FFFFF History Student_Courses Relation Student_ID Course_ID Section Year Grade 0000 CS 2203 2 2020 A 0000 CS 1101 1 2019 A 1111 CS 1101 5 2019 B 1111 CS 1102 1 2019 B 1111 CS 2204 1 2020 A 1111 CS 2401 1 2020 A 2222 BUS 1101 3 2019 C 2222 BUS 2201 1 2020 B 2222 BUS 3302 1 2020 B 3333 EDUC 5010 1 2019 A 3333 EDUC 5210 1 2020 B 4444 PHY 1101 4 2019 A 4444 PHY 2202 1 2020 C 4444 PHY 3304 1 2020 A Considering Student and Student_Courses relations shown above, discuss when Union, Inner Join, Left Outer Join, Right Outer Join, and Full Outer Join should be used and provide examples using the above relations. I AM GETTING USED TO MYSQL WORKBENCH. (Kindly take me through the path)?need help to draw the ERD diagram for this: Revised Entity-Relationship Diagram (ERD) Proposal: Entities and Attributes: Users: UserID [PK] Username Password (Hashed and Salted) Email ProfilePictures: (New Entity) PictureID [PK] UserID [FK] PictureData (Reference/Path to Image File) Recipes: RecipeID [PK] UserID [FK] Title Description Image (Reference/Path to Image File) Ingredients: (Potential New Entity) IngredientID [PK] RecipeID [FK] Name Quantity Unit Comments: CommentID [PK] UserID [FK] RecipeID [FK] CommentText Timestamp Relationships: Users to ProfilePictures: One-to-One Users to Recipes: One-to-Many Recipes to Ingredients: One-to-Many Users to Comments: One-to-Many Recipes to Comments: One-to-Many
- Many-to-Many ERD CUSTOMER has: customer_id first and last names address TAPE has: tape_id title description length in minutes A CUSTOMER may rent many TAPEs. A TAPE can be rented by many CUSTOMERs. Draw a Crow’s Foot ERD that includes the bridge table between Customer and Tape to account fully for the many-to-many relationship. Course and Class ERD Table name: COURSE COURSE_ID TITLE DESCRIPTION CREDITS ACCT-211 Basic Accounting An introduction to accounting. Required of all business majors. 3 CIS-380 Database Systems I Database design and implementation issues. Uses CASE tools to generate designs and implement in a major RDBMS. 3 CIS-490 Database Systems II The second half of CIS-380. Basic Web database application development and management issues. 4 Table name: CLASS CLASS_ID COURSE_ID SECTION TIME LOCATION 100 ACCT-211 1 8:00 a.m. – 9:30 a.m. T-Th. AA 325 101 ACCT-211 2…A new sales ordering system needs a relational database that contains a customer table, a product inventory table, and an order table. Use a word-processing, graphics, or spread-sheet program to draw an entity-relationship diagram showing the relationships among these entities. Next, design each of these tables showing the keys and attributes to be included in each table. Include five sample records for each table.Draw an Entity Relationship Diagram for all of those below. (a simple one) Each lecturer is identified by a unique number, name and phone. A lecturer teaches several courses, and for each course there is a unique code and title (not necessarily unique). There might be several lecturers in a course. Each course requires one or more text books. Each text book is used in at least one course. A text book is identified by a unique ISBN, and its title, authors, publisher and year of publication are known. Each course has a unique code. The bank has many customers. Every customer has at least one account with the bank. An account may be shared by several customers. Each customer has a unique number, type, the opening date and the balance. A flight carries many passengers on a specific airplane. Each airplane has a unique number, while each flight has a code (unique), destination and origin. A passenger has a name, passport number and nationality
- JUST DRAW ME THE Entity-Relationship Diagram (ERD) Before 7:20 PM pleaseHere is all the entities, attributes and their relationshipsEntities: Customer Item Purchase Payment Coffee Card Attributes for Each Entity: Customer: CustomerID (Primary Key) FirstName LastName Email Phone Address Item: ItemID (Primary Key) ItemType (e.g., Beverage, Bakery, Coffee Card) ItemName Description Price Purchase: PurchaseID (Primary Key) CustomerID (Foreign Key) PurchaseDate TotalAmount Payment: PaymentID (Primary Key) PurchaseID (Foreign Key) PaymentMethod (e.g., Cash, Credit Card) PaymentAmount Coffee Card: CardID (Primary Key) CustomerID (Foreign Key) Balance Relationships: Customer-Item (Many-to-Many): A customer can purchase multiple items. An item can be purchased by multiple customers. Customer-Purchase (One-to-Many): A customer can have multiple purchases. Each purchase is associated with one customer. Purchase-Item (Many-to-Many): Each purchase can include multiple items. An…create an Entity Relationship Diagram using the following Entities and their respective attritubes for an online programming competition registration for both leaners and university students. 1. Host it's attributes (host Id, hostFirstName,hostLastName, hostTelNum,hostEmail,hostPassword) 2. Student(StudentId, StudentfirstName, Student Email, StudentPassword, InstitutionName) 3. Learners(leaner Id, leanerFirstName, leanerLastName, leanerPassword, SchoolName) 4. Competition(compId, comName, compDate,comTime, compRules, compRequirements) 5. Qualified Student/Leaner (id, firstName, compId,email)An entity in X is connected numerous entity in Y, however an entity in Y is associated to only one entity in X. What it the type of relationship? Select one: a. One to many b. One to One c. None d. Many to Many
- Problem⦁ Identify the entity⦁ Identify the entity’s attribute⦁ Identify the primary and foreign Keys⦁ Identify relationship between Entities⦁ Identify the Cardinality constraints Question 2⦁ a professor teaches zero, one or many classes and a class is taught by one professor⦁ a course may generate zero, one or many classes and a class comes from one course⦁ a class is held in one room but a room has many classesstudent Relation Student_ID Student_Name Dept_Name 0000 AAAAA Computer Science 1111 BBBBB Computer Science 2222 CCCCC Business Administration 3333 DDDDD Education 4444 EEEEE Physics 5555 FFFFF History Student_Courses Relation Student_ID Course_ID Section Year Grade 0000 CS 2203 2 2020 A 0000 CS 1101 1 2019 A 1111 CS 1101 5 2019 B 1111 CS 1102 1 2019 B 1111 CS 2204 1 2020 A 1111 CS 2401 1 2020 A 2222 BUS 1101 3 2019 C 2222 BUS 2201 1 2020 B 2222 BUS 3302 1 2020 B 3333 EDUC 5010 1 2019 A 3333 EDUC 5210 1 2020 B 4444 PHY 1101 4 2019 A 4444 PHY 2202 1 2020 C 4444 PHY 3304 1 2020 A Considering Student and Student_Courses relations shown above, discuss when Union, Inner Join, Left Outer Join, Right Outer Join, and Full Outer Join should be used and provide examples using the above relations. I AM GETTING USED TO MYSQL WORKBENCH. (Kindly take me through the path)Draw ERD and Map to Relations Map the ERD you have created to relations including all keys (PKs and FKs) Galaxy is an international Airline company. The company has several Airports eachAirport is located at a different country. Each Airport has a name and capacity.Each Airport can hold several Aircrafts at the same time. Each Aircraft has ID,Type and Capacity. The Company has many Employees working in Airports. EachAirport has many employees working in it. Every employee working in an Airportcan only one of three Jobs: Pilots, Admins and Mechanics. Each Employee has anID, Name and salary. Pilots have Number of Flight hours and Mechanics haveyears of experience. The company keeps information of its customers. Eachcustomer has Name and Passport ID. A customers can book one or more tickets.The company keeps information of its tickets such as flight date, Flight ID andTicket ID. Each Ticket is associated with one customer and Aircraft