Consider the following database schema designed to store information about Books, and Chapters. Book (BookID, BookTitle, Price) BookChapter (ChapterID, BooklD, ChapterTitle, NoPages) Write the following queries in SQL. 1. Display BooklD of all books with price less than the average price of all books. 2. Display ChapterlD, BooklD, ChapterTitle of all chapters that include the word 'Database' in the ChapterTitle.
Q: You are working with a database that stores information about suppliers, parts and projects. The…
A: Ans:) The queries for above parts are given below.
Q: Q1/ Consider the fruit database with fruit and market tables, where the primary keys are underlined.…
A: Hello Student, Warm greetings from my side. Hope you are doing great. I am using a reference table…
Q: The following relational schema forms part of an Employees database. The primary keys are underlined…
A: Note: As per the policy I answered as much as I can. Please resubmit the question separately which…
Q: SQL DDL corresponding to this schema (i.e. the CREATE TABLE statements Database Schema: branch (…
A: To create primary key, create table tableName(colName datatype primary key); To create foreign…
Q: Consider the relational database below, where the primary keys are underlined. employee…
A: Given four tables are employee, works, company and manages. The attributes of employee are person…
Q: Specify the following queries on the database schema shown in Figure attached using queries in SQL…
A: INSERT query is used to insert a record into the table and here we specify the column name and…
Q: We have the following database schema: Computer (vendor, model, machinetype) Model (num,…
A: Objective: Given three database relations Computer, Model, and Vendor. We need to obtain the phone…
Q: QI) Consider the employees database, where the primary keys are underlined. Give an expression in…
A: 1. Find all the employees who work in ministry of foreign affairs and their age less than 33. SELECT…
Q: Consider the following database schema designed to store information about Books, and Chapters. Book…
A: Here, I have to write queries for the above question.
Q: Consider the following Relational Schema: Employee (employee_name, street, city) Works…
A: A relational schema is a set of relational tables and associated items that are related to one…
Q: Define the following SQL queries in the database schema in Figure 1.2. (a) Recall the main names…
A: Solution: Given, a. Retrieve the names and major departments of all straight-A students…
Q: Student(studentNumber, StudentName, studentAddress) Book(bookNumber, bookTitle, #authorNumber,…
A: The relational algebra for the given query is as follows,
Q: Consider the following relations database schema. The primary key is underlined, and all attributes…
A: Dear Student, The SQL queries are given below, SELECT branch_name FROM branch JOIN customer WHERE…
Q: Consider the following relational database schema: • Movies (title, year, length, studioName) A…
A: (a) SELECT title, year FROM Stars WHERE Stars.starName=Stars.starName (b) SELECT COUNT(title) as…
Q: Q1) Consider the employees database , where the primary keys are underlined. Give an expression in…
A: 1- select * from Employee where address='baghdad' and ministry_name='foriegn affairs' and…
Q: Write the SQL statements for the following querries: 1. List the employee names for all the…
A: List the employee names for all the employees not working for First Bank Corporation SELECT…
Q: The following is a relational schema for a database to document shark sightings for a research…
A: Answer: Relational Algebra Expressions: Relational Algebra Tree:
Q: 2. Specify the following queries in SQL on the LIBRARY relational database schema shown in Figure 1.…
A: Q.1 Retrieve the titles of all books in the LIBRARY database. Answer :- Select * from tablename…
Q: magine you are asked to design a simple database for an airport. You should accommodate for: -…
A: Database is a tool to store data in the computer system and the language to communicate with the…
Q: QUESTION 1 Consider the following database schema designed to store information about Books, and…
A: To display highest price we need to use MAX() with select command in sql. An average of the number…
Q: Given the following relational schema (primary keys are underlined, optional attributes are…
A: b. Given relational schema: TRAINER (SSN,NameT, Surname, City) GYM (CodG, NameG, Address, City)…
Q: A) The following relations kind a part of a on-line database (the relations area unit self-…
A: The physical ER(Entity-Relationship) diagram is it is physically connected with the database schemas…
Q: For example, here are some statements that would describe the sample data and show that the database…
A: Hey there, I am writing the required solution of the above stated question.Please do find the…
Q: Consider the following database schema and specify the following queries or views in SQL.…
A: a)select SSN,name from EMPLOYEE where SSN in(select SSN from WORKON group by SSN having…
Q: A car-rental company maintains a database for all vehicles in its current fleet. For all vehicles,…
A: For this problem, table inheritance will be used. Suppose that Color, MyDate, and DriveTrainType are…
Q: Consider the 2 tables TI and T2 shown in Figure. Show the results of the subsequent operations with…
A: Relational algebra is a procedural query language that takes relational inputs and gives relations…
Q: Consider the following schema: Sailors(sid, sname, rating, age) Reserve(sid, bid, day) Boats(bid,…
A: ans is given in below
Q: Solve This SQL For the following operations, write the appropriate SQL statement. a) Obtain the…
A: Please see the next step for solution:-
Q: Give two reasons why a database could include null values.
A: Answer : 1. If the value is unknown.For example, if you have a form where the zip code is not…
Q: Provide relational algebra (NOT SQL) queries to find the following information. NOTE: • You can use…
A: π is used to select column σ is used to select raw x represent cartesian product p is alias used…
Q: Enroll(SIN: Integer, CID: String, Term: String, Grade: Integer); Teaches(CID: String, Term: String,…
A: Constraints are the rules enforced on the data columns of a table. These are used to limit the type…
Q: n you answer this two question as MYSQL statement using database that I uploaded Thank you…
A: 1. CREATE VIEW Atlanta_Charter_Crew_V AS ( SELECT i.char_date,i.char_distance,c.emp_num,…
Q: Consider the relational schema shown in Figure 8.8. Write each of the following queries on the…
A: SQL Queries for the given schema: (i) The SQL query to find the company with most employees is given…
Q: Answer any IHREE out of the FOUR questions in this section in the answer sheet provided. Each…
A: Relationship between tables: There is M: N relationship between the Trainer table and the Programme…
Q: Consider a database with the following relations: Employee (Employee ID, Name, salary, startDate)…
A: The following SQL Query can be broken down into sub-queries: Sub-Query 1: Display Project by name…
Q: has many teams:, each team has a name, a city, a coach, a captain, and a set of players; 24…
A:
Q: The following relational schema forms part of an Employees database. The primary keys are underlined…
A: Actually, database is a used to stores the data\information.
Q: Consider the database schema below and answer the questions that follow using SQL.…
A: 1. Retrieve all employees in “sales department” whose salary is between Ksh 30,000 and Ksh…
Q: Consider the following database schema designed to store information about Books, and Chapters.…
A: 1. Query: SELECT BookID FROM Book WHERE Price<(SELECT AVG(Price) FROM Book)
Q: onsider the database shown in Figure 1.2, whose schema is shown in Figure 2.1. What are the…
A: The given problem is related to Database SQL where the relational schemas are given and the…
Q: Let the following database schema be given. Person(idnumber, name, dob, mother, father) primary key…
A: a) Will use CTE to prepare a hierarchical result set WITH #results AS(SELECT idnumber,nameFROM…
Q: The following relational schema forms part of an Employees database. The primary keys are underlined…
A: Question. The following relational schema forms part of an Employees database. The primary keys are…
Q: 1. Specify the following queries in SQL on the database schema in Figure 1.2 (below). Retrieve the…
A:
Q: Consider the following schema of hotel reservations database: Hotel (Hotel No. Name, City) Room…
A: Here is the Schema of above problem. See below step.
Q: Your job as a database designer is to design a database with a schema that captures all the…
A:
Q: Suppose a database has the following relations: - ORDER_T (Order_ID, Order_Date, Customer_ID,…
A: Given database consists of three tables Order_T, Product_T, Order_line with some attributes. (a)…
Q: Question Two Write SQL statements to do the following on the database schema shown below. a)…
A: Queries solution given below :
Q: The following database is maintained by a bookstore: the database structure is shown below.…
A: CREATE TABLE Author(AuthorName text primary key); INSERT INTO Author VALUES('Author1');INSERT INTO…
Q: The following relational schema forms part of an Employees database. The primary keys are underlined…
A: NOTE: As per our company guidelines we are supposed to answer only the first three sub-parts. Kindly…
Q: Consider the following schema: Books(bookID, title, publisher, year, genre) Students(studentID,…
A: ] SELECT S,NAME FROM STUDENTS S INNER JOIN BORROWS B ON S.STUDENTID=B.STUDENTID WHERE B,DATE…
Step by step
Solved in 4 steps with 4 images
- Consider a database with the following schema: Person ( name, age, gender ) name is a keyFrequents ( name, pizzeria ) (name, pizzeria) is a keyEats ( name, pizza ) (name, pizza) is a keyServes ( pizzeria, pizza, price ) (pizzeria, pizza) is a key Write relational algebra/SQL expressions for the following:a. Find the names of all females who eat both mushroom and pepperoni pizza.b. For each person, find all pizzas the person eats that are not served by any pizzeria the personfrequents. Return all such person (name) / pizza pairs.c. Find all pizzerias that are frequented by only females or only males.Consider the following Relational Schema: Employee (employee_name, street, city) Works (employee_name, company_name, salary) Company (company_name, city) Manages (employee_name, manager_name) Write the SQL statements for the following querries: 1. List the employee names for all the employees not working for First Bank Corporation. 2. List the employee names with the salary greater that all the salaries from the Small Bank Corporation.You are working with a database that stores information about suppliers, parts and projects. The Supply relation records instances of a Supplier supplying a Part for a Project.The schema for the database used in this question is as follows: (note that primary keys are shown underlined, foreign keys in bold).SUPPLIER (SNo, SupplierName, City)PART (PNo, PartName, Weight)PROJECT (JobNo, JobName, StartYear, Country)SUPPLY (SNo, PNo, JobNo, Quantity)Provide relational algebra (NOT SQL) queries to find the following information. Each question is worth 2 marks.NOTE:• You can use the symbols etc or the words ‘PROJECT’, ‘RESTRICT’ etc as you prefer. • You do not need to try to make efficient queries – just correct ones. • Where you use a join, always show the join condition.a. List the quantity of parts supplied on JobNo 4745.b. List the weight of the Part named “Left-handed screwdriver”.c. List the Project name and Part name of any Parts where fewer than 500 of the part has been supplied to a…
- You are working with a database that stores information about suppliers, parts and projects. The Supply relation records instances of a Supplier supplying a Part for a Project.The schema for the database used in this question is as follows: (note that primary keys are shown underlined, foreign keys in bold). SUPPLIER (SNo, SupplierName, City)PART (PNo, PartName, Weight)PROJECT (JobNo, JobName, StartYear, Country)SUPPLY (SNo, PNo, JobNo, Quantity) Provide relational algebra (NOT SQL) queries to find the following information. NOTE:• You can use the symbols σ, π, etc or the words ‘PROJECT’, ‘RESTRICT’ etc as you prefer. • You do not need to try to make efficient queries – just correct ones. • Where you use a join, always show the join condition.a. List the quantity of parts supplied on JobNo 4745.b. List the weight of the Part named “Left-handed screwdriver”.c. List the Project name and Part name of any Parts where fewer than 500 of the part has been supplied to a particular project.d.…You are working with a database that stores information about suppliers, parts and projects. The Supply relation records instances of a Supplier supplying a Part for a Project.The schema for the database used in this question is as follows: (note that primary keys are shown underlined, foreign keys in bold). SUPPLIER (SNo, SupplierName, City)PART (PNo, PartName, Weight)PROJECT (JobNo, JobName, StartYear, Country)SUPPLY (SNo, PNo, JobNo, Quantity) Provide relational algebra (NOT SQL) queries to find the following information.NOTE:• You can use the symbols σ, π, etc or the words ‘PROJECT’, ‘RESTRICT’ etc as you prefer.• You do not need to try to make efficient queries – just correct ones.• Where you use a join, always show the join condition.a. List the Project name and Part name of any Parts where fewer than 500 of the part has been supplied to a particular project.b. List the name of any Suppliers who have supplied Parts to the Project called “New Academic Building” or to the Project…Consider the following Relational Schema: Employee (employee_name, street, city) Works (employee_name, company_name, salary) Company (company_name, city) Manages (employee_name, manager_name) Write the SQL statements for the following querries: 1. Assume that an employee works at different companies so its salary is a combinations of different amounts. Find the employees with total salaries greater that all the salaries of employees that work for Small Bank Corporation. 2. Find the company with the most number of employees
- Given the following relational schema of a database library (primary keys are underlined and foreign keys are preceded by #): Student(studentNumber, StudentName, studentAddress) Book(bookNumber, bookTitle, #authorNumber, #editorNumber, yearEdition) Author(authorNumber, authorName, authorAddress) Editor(editorNumber, editorName, editorAddress) Borrow(#studentNumber, #bookNumber, borrowDate, returnDate) Write the following queries in Relational Algebra: Find the details of students who borrowed the book titled ‘Database Management Systems’Given the following relational schema of a database library (primary keys are underlined and foreign keys are preceded by #): Student(studentNumber, StudentName, studentAddress) Book(bookNumber, bookTitle, #authorNumber, #editorNumber, yearEdition) Author(authorNumber, authorName, authorAddress) Editor(editorNumber, editorName, editorAddress) Borrow(#studentNumber, #bookNumber, borrowDate, returnDate) Write the following queries in Relational Algebra: List the details (bookNumber, bookTitle) of books whose author’s name is ‘Ramakrishnan’This question is related to the Database:- Suppose you are creating a database for a library management system. Explain how you would make a table for the "books" entity and insert some sample data, and then alter the table to add a new column for the "authorID" attribute and update "authorID" with some data. Finally, make a SQL query to retrieve all the books that were published after the year 2000. Note that this book schema has several columns, including "book_id" as the primary key, "title", "authorN", "publisher", "publication_year", "isbn", "language", and "num_pages". It also includes "available" column that is set to "true" by default and can be used to track the availability of the book. Please don't use handwriting...
- given the following requirements for a simple database for the football League (FL) Write the needed SQL Statements to create the database: the FL has many teams:, each team has a name, a city, a coach, a captain, and a set of players; 24 players, each player belongs to only one team, each player has a name, a position (such as left wing or goalie), a skill level, and a set of injury records, a team captain is also a player, a game is played between two teams (host team and guest team) and has a date (such as May 11th, 2020) and a score (such as 4 to 2).Answer the following questions 1.Explain by using a practical scenario what the difference between a variable and a parameter are. 2. Using a practical scenario explain the difference between Desktop databases and Server Databases. 3. You have been hired as a Database Developer and you have been given the option to use a trigger and a procedure. When would it be best to use procedures? 4. Write a SQL statement that will create a database named Namibian_Team that will store data for all sporting events represented in the country using the Master database in MS SQL Server. 5. As a database developer, you have to explain with visual representations the difference between a logical design and a physical designWrite SQL update statements to do the following on the database schema shown in Figure 1.i. Insert a new student, <‘Johnson’, 25, 1, ‘Math’>, in the database.ii. Change the class of student ‘Smith’ to 2.