The ER diagram of the database is given above. Write the following queries. 1.Write a query to get the Average salary of all employees working as Programmers. Hint: Use the employees table.
Q: 4) How many instructors are advising more than three students? 5) Which department has the highest…
A: Here, we have to fetch result based on id present in advisors table select i.* from instructors i…
Q: Consider the following schema and answer the following SQL queries. Employee ( person_name , street,…
A: Hey, since multiple sub part questions posted, we will answer first three sub part questions…
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: Write SQL queries DDL Queries to implement the following Relational Model i.e. create the Nurse…
A: DDL SQL Query: Data Definition Language DDL Commands: Create Drop Alter Truncate Comment Rename
Q: Write the following queries in SQL on the relational database schema for COMPANY database given in…
A: Solution: Given: Given the relational database schema for the COMPANY database To Find: Find…
Q: . database by graphically or by using query. 3. Create table in your database by graphically and by…
A: The database is an organized collection of structured records or data and these data are retrieved,…
Q: Use Microsoft SQL Server Management Studio for this practical i. Create a database called ‘ORG’…
A: Structured Query Language is abbreviated as SQL or also pronounced as See-Qwell. It is developed for…
Q: The relational database schemas are as follows: department(dept-name, building, budget)…
A: Given : The relational database schemas are as follows:department(dept-name, building,…
Q: In the book Database Systems: Design, Implementation & Management - 13th edition Problem 5…
A: “ORDER BY” Clause: SQL contains "ORDER BY” clause in order to sort rows. The values get sorted in…
Q: Implement the following clauses on a database of your choice and produce results. (Write query and…
A: The BETWEEN operator is used to select the values of the queries within a given range. The JOIN rule…
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: 1- Draw an ERD about a restaurant on Oracle SQL developer data modeler, it should contain at least 8…
A: Answer: I have given answered in the handwritten format in brief explanation.
Q: For the database of Figure 4.12, write a query to find the ID of each employee with no manager. Note…
A: Query using an outer join is as follows, select ID from employee natural left outer join manages…
Q: Find the last name of the supervisors who do not have dependents.
A: SELECT LNAMEFROM EMPLOYEEWHERE NOT EXISTS (SELECT *FROM DEPENDENTWHERE SSN = ESSN);
Q: query in relational algebra to find the ID and the brand of those trucks whose type is S.
A: It is procedural query language. It collects instance of relation as input and fives occurrences of…
Q: Write the following queries in SQL on the relational database schema for COMPANY database given in…
A: We need to write a query to find the total number of dependents of each employee who has dependents.…
Q: For the following Employee database, write a query to find the ID of each employee with no manager.…
A: With outer join Without outer join Without outer join : nested query
Q: 1. Display records of employee id, last name, first name of employees' table.
A: The employee table consists of 8 columns. SQL(Structured Query Language) has three basic commands…
Q: Consider the education database , where the primary keys are underlined. Give an expression in SQL…
A: 1) SELECT full_name, school_name FROM Student NATURAL JOIN School WHERE age>=10 AND age<=15
Q: Write the following queries in SQL on the relational database schema for COMPANY database given in…
A: Answer: select sum(Hours) from WORKS_ON group by Essn having Essn = (select Mgr_ssn from…
Q: The following database will be used in this question: SALESREP SalesRepNo 654 734 345 434 RepName…
A: MySQL code for the CUSTOMER table: CREATE TABLE CUSTOMER(CustNo integer PRIMARY KEY, CustName text,…
Q: Show the result of the following queries as it would apply to the database state given above. 1.…
A: I cannot see any database here, Please repost the question with the database.
Q: Consider the library database of Figure 3.20. Write the following queries in SQL.For each publisher,…
A: Here, Three tables are created. member book borrowed The values are inserted into the tables.
Q: Write a Query to get the First Name, Last Name and Salary of the employee who is paid the lowest…
A: In SQL, the subquery is used to return data that will be used in the main query as a condition to…
Q: For the database of Figure 4.12, write a query to find the ID of each employee with no manager. Note…
A: Database: The database contains the collection of information organized in such a way that can be…
Q: q/ Consider the employees database where the primary keys are underlined. Give an expression in SQL…
A: 1. Query: SELECT e.ID, e.Name, e.age, e.address, e.salary, e.department, e.position,…
Q: In a sql database give an example of a search problem that will work with an inner join and will not…
A: Find the answer given as below :
Q: You are working with a database that stores information about suppliers, parts and projects. The…
A: Answer : 1. ∏ Quantity (σ JobNo = 4745 (SUPPLY)) 2. ∏ Weight (σ PartName = "Left-handed screwdriver"…
Q: The ER diagram of the database is given above. Write the following queries. 1.Write a query to get…
A: As said in the question, we need to use employees table
Q: Write the following queries in SQL on the relational database schema for COMPANY latabase given in…
A: Note: As per our guidelines we are allowed answer only first 3 subparts. Kindly repost the remaining…
Q: use national join plz Consider the education database with student and scho ,where the primary keys…
A: We have created student and school tables, where the primary keys are underlined. Using below Given…
Q: For the database Figure, write a query to find the ID of each employee with no manager. Note that…
A: Query using an outer join is as follows, select ID from employee natural left outer join manages…
Q: Consider the following database. Write a SQL query to express the query student (person_name,…
A: finding sql query for the given equation
Q: Using ERdPlus, Construct the ERD for the above rental database. Do not omit the relationship…
A: ERD stands for Entity Relationship Diagram and this is a conceptual model which is understand by…
Q: For the database of Figure 4.12, write a query to find the ID of each employee with no manager. Note…
A: Query for finding the employees with no manager: Consider the “Employee database” given in Figure…
Q: write SQL commands to create tables of a Bank database, where these tables will have the following…
A: HI THEREI AM ADDING SQL COMMAND BELOW TO CREATE GIVEN SCHEMA PLEASE GO THROUGH ITTHANK YOU
Q: The ER diagram of the database is given above. Write the following queries. 1. Write a Query to…
A: As said in the question, we need to use employee table
The ER diagram of the
Write the following queries.
1.Write a query to get the Average salary of all employees working as Programmers.
Hint: Use the employees table.
Step by step
Solved in 2 steps
- Q2: A-Given the following tables: Student Subject Std_ID 001 002 003 004 Fname Smith Smith Beal Thomson Lname Jolin Susan Fred Marie Std_ID 001 001 002 002 003 Semester 004 Falls03 Spro4 Falls03 C_ID Eng01 Ger01 Eng01 Ger01 Ger01 CSection Spro4 Spro4 Spro4 1. For each table in the database, identify the primary key and the foreign key(s). 2. Do the tables exhibit entity integrity? Explain your answer? 3. Do the tables exhibit referential integrity? Explain your answer? Ita01 ZZZZZZ IN 2N IN 2N IN CName English IN German English German German Italian Grade A B A B A BHaving this information. StudentsAttribute Name Data TypeStudentID char(11)FirstName varchar(20)LastName varchar(20)Gender char(1)DateofBirth date CoursesAttribute Name Data TypeCourseCode varchar(6)CourseName varchar(70)Level char(2)Credits int RegistrationAttribute Name Data TypeStudentID char(11)CourseCode varchar(6)Grade decimal(2, 1) Write SQL code to: 1. How many courses are there on each level? 2. What is the average grade of courses that have been taken by the student with student ID 861103-2438? 3. Which students (studentID only) have the highest grade for the course ‘CS052’? 4. Find the courses (course codes only) that have been taken by both the student 861103-2438 and the student 123456-0980.Having this information. StudentsAttribute Name Data TypeStudentID char(11)FirstName varchar(20)LastName varchar(20)Gender char(1)DateofBirth date CoursesAttribute Name Data TypeCourseCode varchar(6)CourseName varchar(70)Level char(2)Credits int RegistrationAttribute Name Data TypeStudentID char(11)CourseCode varchar(6)Grade decimal(2, 1) Write SQL code to: 1. What are the student ID, the names, and birthday of the female students who were born before the last day of 1992? 2. How many students are registered in the course “PHY210”? 3. How many courses are offered by the department of Computer Science (i.e. course codes CS***)? 4. Which course(s) (couseCode and courseName) of level G1 give 5 credits?
- Update the DETAILRENTAL table to set the values in DETAIL_RETURNDATE to include a time component. Make each entry match the values shown in the following table. TABLE P8.53 UPDATES FOR THE DETAILRENTAL TABLE RENT_NUM VID_NUM DETAIL_RETURNDATE 1001 34342 02-MAR-18 10:00am 1001 61353 03-MAR-18 11:30am 1002 59237 04-MAR-18 03:30pm 1003 54325 09-MAR-18 04:00pm 1003 61369 09-MAR-18 04:00pm 1003 61388 09-MAR-18 04:00pm 1004 44392 07-MAR-18 09:00am 1004 34367 07-MAR-18 09:00am 1004 34341 07-MAR-18 09:00am 1005 34342 05-MAR-18 12:30pm 1005 44397 05-MAR-18 12:30pm 1006 34366 04-MAR-18 10:15pm 1006 61367 1007 34368 1008 34369 05-MAR-18 09:30pm 1009 54324 1001 34366 02-MAR-18 10:00amA unique identifier for a data row that consists of more than one field is commonly called a: primary plus key composite primary key foreign key none of the aboveAssume that the Authors table is already built in the database with the following specifications: Authors: Column Name Data Type constrains Authors ID Author Name Varchar2(10) |Varchar2(30) Primary key Unique You Need to Create the Books table with the following specifications Column Name Data Type Constrains ISBN Book Title Book Price Author id Number (10) Varchar2(40) Number (4,2) Varchar2(10) Primary key Not Null referencing the Author_ID in the Author Table Complete the create statement by drag and drop the correct word: Create Table lblank (ISBN Number (10) plank, Book Title Varchar2blank ) Not Null, Book Price Number(4, 2), Auther IR blank (10) references Jolank (blank ); Author ID Books Number||Authors Not Nul Varchar2| Primary Key 40 h (United States)
- MySql Workbench CREATE TABLE students ( id INT PRIMARY KEY, first_name VARCHAR(50), last_name VARCHAR(50), age INT, major VARCHAR(50), faculty VARCHAR(50)); CREATE TABLE location ( id INT PRIMARY KEY, name VARCHAR(50), rooms INT); CREATE TABLE faculty ( id INT PRIMARY KEY, name VARCHAR(50), department_id INT); 1. List last name of all students whose first name is longer than 4 letters in ascending order accordingto the last name. Duplicated rows should be removed from the output.2. Count the total number of rooms in Location.3. Find the number of students in each major.4. Find the number of employees in each department who get no commission or have salary less than5000.5. Find the maximum salary of employees in each department that the employee was hired 15 yearsbefore now. *hint: user TIMESTAMPDIFF(<unit type>,<Date_value 1>,<Date_value 2>), the unitcan be YEAR, MONTH, DAY, HOUR, etc...Name the candidate keys in the following table: Student L number Student Name Student_SS_Number Student_Address Student City Student State LO005485 Leigh 811-47-4449 5744 KENSINGTON Ave. Boston MD LO004812 Tammy 441-79-7448 30936 OAK Alley Colorado Springs TX LO004415 Alfredo 375-78-2633 5115 BOWEN St. Kansas City VT LO003478 Ramona 360-82-2835 5097 HUBBARD LOWER Blvd. Miami PA LO003058 Crystal 528-16-2635 73192 RANDOLPH SUB Dr. Riverside MD LO004588 Angie 765-16-4433 5115 GROVELAND PARK Dr. Cincinnati FL L0005484 Tonya 410-76-3632 5115 BISHOP FORD Blvd. Irving MS LO003161 Preston 451-47-2634 95289 MONROE Ave. Boston NC HTM B I A - A - I E E E E E X X, E E D T T 12pt ParagraphThe data in flat files has been provided: INVOICE TABLE INVOICE_NUM CUSTOMER_ID INVOICE_DATE EMPLOYEE_ID COIN_ID DELIVERY_ID 8111 11011 15 May 2021 emp103 7111 511 8112 11013 15 May 2021 emp101 7116 512 8113 11012 17 May 2021 emp101 7112 513 8114 11015 17 May 2021 emp102 7111 514 8115 11011 17 May 2021 emp102 7115 515 8116 11015 18 May 2021 emp103 7115 516 8117 11012 19 May 2021 emp105 7112 517 8118 11013 19 May 2021 emp105 7112 517 COIN_RETURNS TABLE RETURN_ID RETURN_DATE REASON CUSTOMER_ID COIN_ID EMPLOYEE_ID ret001 25 May 2021 Customer not satisfied with product 11011 7116 emp101 ret002 25 May 2021 Product missing part 11013 7114 emp103 COIN TABLE COIN_ID PRODUCT PRICE QTY 7111 1oz Gold Kruger Rand R 5 999 10 7112 1oz Silver Kruger Rand R 12 999 8 7113 Gold Big 5 Uncirculated R 15 999 8 7114 Silver Big 5 Pack R 7 999 5 7115 1oz Gold Palaeontology R 11 999 15 7116 1oz Silver Palaeontology R 7 999 12 COIN_DELIVERY TABLE…
- Write Mysql code The database has three tables for tracking horse-riding lessons: Horse with columns: ID - primary key RegisteredName Breed Height BirthDate Student with columns: ID - primary key FirstName LastName Street City State Zip Phone EmailAddress LessonSchedule with columns: HorseID - partial primary key, foreign key references Horse(ID) StudentID - foreign key references Student(ID) LessonDateTime - partial primary key Write a SELECT statement to create a lesson schedule with the lesson date/time, horse ID, and the student's first and last names. Order the results in ascending order by lesson date/time, then by horse ID. Unassigned lesson times (student ID is NULL) should not appear in the schedule. Hint: Perform a join on the Student and LessonSchedule tables, matching the student IDs.Use Mysql code The database has three tables for tracking horse-riding lessons: Horse with columns: ID - primary key RegisteredName Breed Height BirthDate Student with columns: ID - primary key FirstName LastName Street City State Zip Phone EmailAddress LessonSchedule with columns: HorseID - partial primary key, foreign key references Horse(ID) StudentID - foreign key references Student(ID) LessonDateTime - partial primary key Write a SELECT statement to create a lesson schedule for Feb 1, 2020 with the lesson date/time, student's first and last names, and the horse's registered name. Order the results in ascending order by lesson date/time, then by the horse's registered name. Make sure unassigned lesson times (student ID is NULL) appear in the results. Hint: Perform a join on the LessonSchedule, Student, and Horse tables, matching the student IDs and horse IDs.Find the Primary Keys and P3,6 for the database: Make Model Engine ID Ford Mustang Ford Fiesta Chevy Camaro Chevy Caprice Olds Cutlass Olds Cutlass Volvo P1800 Volvo 240 Volvo 760 Year 1972 A1222 1989 C54322 1991 754342AH 1989 442355CC 1992 ANDU33 1992 ANGH28 1969 44325XX 1986 53526PD 1992 578868R Vehicle ID FO13579 FO24245 CH172389 CH156738 OL64332 OL61998 V044526 VO64690 V083529 Color Black Yellow Black Yellow Blue White White Black Blue