List the department IDs for those departments that have less than 5 employees:
Q: List the vendor names and address for all the vendors who have active invoices. And the invoice…
A: SQL Inner Join Query
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: Based on the ERD below, display the assignment number, employee number and assigned hours for either…
A: Here we are joining three tables ASSIGNMENT, EMPLOYEE, and JOB and the assigned job is either 501 or…
Q: No plagarism please! Write your own answer. Correct and detailed answer will be Upvoted else…
A: Let’s take few data samples to work on this query . Here is customer table with some data . Our…
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: sign a query to show the accounts related contact in the salesforce Inspe d also explain what is…
A: Program in APEX using Salesforce Object query Language to Fetch Contacts and Opportunities Records…
Q: 4. List all customers (last name, first name and zip code) whose zip code is missing (unknown).
A: 4. Answer: SELECT LastName, FirstName, ZipCode FROM Customer WHERE ZipCode IS NULL;
Q: SQL 1.Show the first name of employees and their related department name. 2.For each employee,…
A: Given: SQL 1.Show the first name of employees and their related department name. 2.For each…
Q: Student(Id, name, age, class, grade) Retrieve all students who have grade 'B'
A: Structural Query Language Structural Query Language also called SQL is a query language use for…
Q: 1. List the address, square footage, owner number, services ID, number of estimated hours, and…
A: List the address, square footage, owner number, service ID, number of estimated hours, and number of…
Q: Address Book First Name: Last Name: City: Province: Postal Code: Add First Next Previous Last Update
A: Solution:-- 1)A layout is been given in the question for the topic of Address Book. 2)To do the…
Q: Display first name, last name and job title of all employees.
A: NOTE - Here I have Oracle SQL to solve the given problem. I have used the Select statement to…
Q: Display details of employee with ID 150 or 160. (Database Systems)
A: Since here no database table is provided. So, we are assuming the below-given table: Table - emp
Q: NOTE; language SQL List the office number, address, and monthly rent for properties whose owners…
A: Database: A database is used to store a huge amount of data records. Generally, most industries or…
Q: Find the number of (unique) products that have been sold to more than 10 customers. SQL Question
A: We need to write a SQL query for finding the number of (unique) products that have been sold to more…
Q: Task 6: Find the ID, first name, and last name of each customer for which an invoice was not created…
A: The above question is solved in step 2 :-
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: Using SQL Show the distinct location_id for all the locations where the company has departments
A: SQL SQL stands for Structured Query Language, and it is a programming language used to store,…
Q: For each reservation for customer Ryan Goff , list the reservation id, trip id and number of…
A: AND Operator: In Structured query Language (SQL), "AND" keyword is used in order to check two and…
Q: Please explain how to set the mining “difficulty”.
A: The difficulties of mining association rules from large databases: 1. Security and social issues:…
Q: List the department name, manager id and location id of all the departments that location id is 2500…
A: SELECT department_name, manager_id, location_id FROM departments where manager_id=124 ||…
Q: For every completed or 'open service request for janitorial work', list the condo id, description,…
A: The querry has given
Q: Queries: 1. List the first and last name of all patients who had blood cancer. 2. List names and…
A: As per Bartleby policy, we can only answer 3 sub parts per question. Please post remaining sub parts…
Q: SELECT firstNAME,City FROM students WHERE AGE 20;
A: convert to relational algebra
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: Display the product ID and the number of orders placed for each product. Show the results in…
A: SQL (Structured Query Language) is a database computer language primarily designed for the…
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: Checkout items from the inventory. (item name, count, price, remaining number of items in the…
A: Checkout items from the inventory. (item name, count, price, remaining number of items in the…
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: 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: SQL Find the guide last name and guide first name of all guides who can lead a paddling trip
A: To merge the data between the tables: guide, trip guide, and trip tables SQL join method is used. It…
Q: List the index number, and course number for all classes taught by Dr. Smith during the Fall 02…
A: The query provided is done using SQL and considering all the data is present in a single table as…
Q: What are subqueries, and why are they used?
A: Subqueries A subquery may be a question inside another question, conjointly referred to as a nested…
Q: List the names of those products that have been sold to more than 10 customers. Sort your results by…
A: SQL is also known as Structured Query Language. SQL is a standard language for storing, manipulating…
Q: Explain how to use SQL's identity column feature.
A: In SQL we can create an Identity column for auto-generating the increasing value. As per my learning…
Q: SELECT e.Last_Name, m.Last_Name AS "Manager Name", e.Salary*12 AS "Annual Salary", 1.City FROM…
A: Please find all supporting table details and the output in the following steps.
Q: List the item ID, description, and on-hand value of each item whose number of units on hand is more…
A: Subquery : A subquery is a query that is nested inside a SELECT , INSERT , UPDATE , or DELETE…
Q: Display the territory ID and the number of salespersons in the territory for all territories that…
A: SQL (Structured Query Language) is designed primarily for managing data in a relational database…
Q: nter Employee Id : 101 Enter Enployee Id : nter Employee Name : John Hathew Enter Employee Nane ter…
A: I have written code below:
Q: List the owner number, last name and first name of every condo owner.
A: Use a nested query where we are including the condition to include only the OwnerNum which is…
Q: d)' List property location, owner name and maintenance date for property managed by Property Pete.…
A: The correct query is given below with output screenshot in the below step
Q: Using SQL find the number of employees from the table:
A: We just need to count all the rows in the given table that will result in the number of employess.
Q: Show a list of employee id, names, department, years and job of any employee in the staff table who…
A: First you have to make a table called Employee after this you just have to enter random values to…
Q: Display the highest, lowest, sum, and average salary of all employees. Label the columns Maximum,…
A: max - function to get the maximum value of column min - function to get the minimum value of column…
Q: Display last_name from students table who obtained below 75 algebra_grade or trigonometry_grade.
A: Since the question did not provide a table and its columns, a sample table Students created with…
Q: • Select the FirstName of customers with the highest Total in Invoice.
A: We have to select first-name of customers with the highest total invoice
Q: Display details of employee with ID 150 or 160. using sql
A: To find the details of the employee having ID 150 or 160. Note: The detail of the table is given…
Q: Select all the staff full names with the customer full names of the consignments they have worked…
A: Hey there , I am writing the required SQL query for the above mentioned question. Please do find the…
Q: display the first and last names, hire dates, and number of years employed for all employees who…
A: ORDER BY is the keyword used to sort the list and to make it in descending order, we use DESC…
Step by step
Solved in 2 steps
- Table name: ADVISORS ADVISORID ADVISORNAME A102 Sami A103 Alice A104 Suad A105 Tamil A101 Arun Table name: DEPARTMENTS DEPTID DEPTNAME D102 ELC D101 IT D103 BSD D104 ENG Table name: STUDENTS STUDENTID STUDENTNAME DOB GENDER ALLOWANCE DEPTID ADVISORID STUDENTLEVEL AGE Hisham 6/1/1999 Male 90 D102 S103 A102 Level 1 22 12/30/1994 Female 45 D103 S102 Sara A104 Diploma 27 S101 Salim 6/1/1999 Male 45 | D103 A104 Diploma 22 5107 Khalil 5/6/1995 Male 45 D101 A101 Blech 26 45 | D104 Advanced Diploma S109 Said 10/15/1992 Male A101 29 5/1/1995 Male 45 D101 90 D104 5100 Ahmad BTech Advanced Diploma A103 26 4/5/1998 Male 2/23/1992 Female 9/12/1999 Female S104 Faisal A101 23 0 D102 45 D102 S105 Fatima 90 A102 Level 2 29 S106 Arwa A102 Level 3 22 S108 Ibtisam 8/13/1991 Female 90 D103 A104 Diploma 30 Write the SQL statement for the following: 1. Using the ON clause, display the advisor name and student name for all students in the BTech level 2. Display the student name, gender and department name of…Table name: ADVISORS ADVISORID ADVISORNAME A102 Sami A103 Alice A104 Suad A105 Tamil A101 Arun Table name: DEPARTMENTS DEPTID DEPTNAME ELC D102 D101 IT D103 BSD D104 ENG Table name: STUDENTS STUDENTID STUDENTNAME DOB GENDER ALLOWANCE DEPTID ADVISORID STUDENTLEVEL AGE 6/1/1999 Male 12/30/1994 Female 6/1/1999 Male 5/6/1995 Male 10/15/1992 Male S103 Hisham 90 D102 A102 Level 1 22 S102 Sara 45 D103 A104 Diploma 27 S101 Salim 45 D103 A104 Diploma 22 S107 Khalil 45 D101 A101 BTech 26 S109 Said 45 D104 A101 Advanced Diploma 29 s100 Ahmad 5/1/1995 Male 45 D101 A103 BTech 26 S104 Faisal 4/5/1998 Male 90 D104 A101 Advanced Diploma 23 S105 Fatima 2/23/1992 Female 90 D102 A102 Level 2 29 9/12/1999 Female 8/13/1991 Female S106 Arwa 45 D102 A102 Level 3 22 S108 Ibtisam 90 D103 A104 Diploma 30 Write the SQL statement for the following: 1. Using the ON clause, display the advisor name and student name for all students in the BTech levelINVOICES INVOICE LINE INVOICE NUM INVOICE DATE CUST ID 11/15/2021 INVOICE NUM ITEM ID QUANTITY 14216 QUOTED PRICE 14216 125 CA75 $37.99 14219 11/15/2021 227 14219 AD72 $79.99 14222 11/16/2021 294 14219 DT12 4. $39.99 14224 11/16/2021 182 14222 LD14 $47.99 14228 11/18/2021 435 14224 KH81 4. $18.99 14231 14233 14237 11/18/2021 11/18/2021 125 14228 FS42 $124.99 435 14228 PF19 1. $74.99 11/19/2021 616 14231 UF39 2. S189.99 14233 KH81 $19.99 QB92 WB49 LP73 14233 $109.95 14233 $74.95 14237 $54.95 ТЕМ ITEM ID DESCRIPTION ON HAND CATEGORY LOCATION PRICE Dog Feeding Station Fenthers Bird Cage (12x24x18) AD72 12 DOG B. $79.99 10 BRD B $79.99 CA75 Enclosed Cat Litter Station 15 CAT $39.99 Dog Toy Gift Set Fly Mask with FRars Folding Saddle Stand Aquarium (55 Gallon) DT12 27 DOG B $39.99 FM23 41 HOR $24.95 FS39 12 HOR $39.99 FS42 FSH A. $124.99 KHS1 LD14 Wild Bird Food (25 Ib) 24 BRD $19.99 Locking Small Dog Door 14 DOG $49.99 LP73 Lange Pot Carrier 23 DOG $59.99 Pump & Filter Kit Quilted Stable…
- PET ID PET NAME PET TYPE PET AGE OWNER VISIT DATE PROCEDURE 246 ROVER DOG 12 SAM COOK JAN 13/2002MAR 27/2002APR 02/2002 01 - RABIES VACCINATION10 - EXAMINE and TREAT WOUND05 - HEART WORM TEST 298 SPOT DOG 2 TERRY KIM JAN 21/2002MAR 10/2002 08 - TETANUS VACCINATION05 - HEART WORM TEST 341 MORRIS CAT 4 SAM COOK JAN 23/2001JAN 13/2002 01 - RABIES VACCINATION01 - RABIES VACCINATION 519 TWEEDY BIRD 2 TERRY KIM APR 30/2002APR 30/2002 20 - ANNUALCHECK UP12 - EYE WASH The functional dependencies that I note are as follows: Pet ID --> Pet Name, Pet Type, Pet Age, Owner Pet ID, Visit Date --> {Visit Procedure} (the set of visit procedures), note the use of procedure codes that repeat, but are listed in their own column So, Is there are other functional dependencies? If so, what are they? If not, why not? Do you believe it would help to create additional attributes during normalization? If so, what are they? If not, justify why it's not needed. Please help me understand this.1. Write a database description for each of the relations shown, using SQL DDL (shorten, abbreviate, or change any data names, as needed for your SQL version). Assume the following attribute data types: StudentID (integer, primary key) StudentName (25 characters) FacultylD (integer, primary key) FacultyName (25 characters) CourselD (8 characters, primary key)46 Refer to the given table. Salesperson Number 137 137 137 106 106 106 106 204 204 361 361 361 Product Salesperson Number Name 19440 Baker 24013 Baker 26722 Baker 16386 Adams 19440 Adams 21765 Adams 24013 Adams 21765 Dickens 26722 Dickens 16386 Caryle 21765 Caryle 26722 Caryle SALESPERSON/PRODUCT table Commission Year of Department Manager Product Unit Percentage Hire Number Name Name Price 10 1995 73 Scott Hammer 17.50 10 1995 73 Scott Saw 26.25 10 1995 73 Scott Pliers 11.50 15 2001 59 Lopez Wrench 12.95 15 2001 59 Lopez Hammer 17.50 15 2001 59 Lopez 32.99 15 2001 59 Lopez 26.25 10 1998 73 Scott 32.99 10 1998 73 Scott 11.50 20 2001 73 Scott 12.95 20 2001 73 Scott 32.99 20 2001 73 Scott 11.50 4 Which quality of the table demonstrates why it is not in first normal form? O There are only non-key attributes visible. O There is data redundancy. There is no primary key. Each attribute has multiple values. Drill Saw Drill Pliers Wrench Drill Pliers Quantity 473 170 688 1475 2529 1962 3031…
- Table name: EMPLOYEE EMP_NUM EMP_LNAME 215 Smith 216 Romero 217 Randall 218 Aarden Table name: EMP_EDUC EMP_NUM EDU_CODE EE YEAR 2002 1991 1994 1989 2004 2002 1985 1992 2000 1990 2000 2005 1984 1995 215 84 215 HS 215 JC 216 B4 216 CDP 216 CNP 216 HS 217 HS 217 JC 218 B4 218 CDP 218 CNP 218 HS 218 M2 Table name: EDUCATION EDU_CODE EDU_DESCRIPTION 34 CDP CNP CPA DR HS JC M2 Bachelor's degree Certified data Processing Certified Network Professional Certified Public Accountant Earned Doctorate degree Highschool diploma Junior College degree Master's degreeTable name: EMPLOYEE EMP_NUM EMP_LNAME 215 Smith 216 Romero 217 Randall 218 Aarden Table name: EMP_EDUC EMP_NUM EDU_CODE EE_YEAR 2002 1991 1994 1989 2004 2002 1985 1992 2000 1990 2000 2005 1984 1995 215 84 215 HS 215 JC 216 B4 216 CDP 216 CNP 216 HS 217 HS 217 JC 218 B4 218 CDP 218 CNP 218 HS 218 M2 Table name: EDUCATION EDU_CODE EDU_DESCRIPTION 34 CDP CNP CPA DR HS JC M2 Bachelor's degree Certified data Processing Certified Network Professional Certified Public Accountant Earned Doctorate degree Highschool diploma Junior College degree Master's degreeTable EMP Column name Column type Integer Up to 10 characters Example entry EMPNO ENAME Description Employee ID number Employee surname 7369 SMITH Integer Integer Employee's job title No ID no. of employee's JOBNO 10 MGR 7566 manager HIREDATE Date 17/12/1980 Date employment started Monthly salary SAL Integer 1600 Table JOB Column name Column type Integer Up to 15 characters Example entry Description job title No Employee's job title JOBNO 10 JOB TITLE SALESMAN a) Write SQL statements to create EMP and JOB tables. Define appropriate data types and check constraints for the tables' fields. Write SQL statements to populate these tables with 3 rows of sample data.
- Given the Employee, Class, and Training tables. Employee Emp_id (PK) EM8765 Address USA Name Phone Email Ali ali@yahoo.com nazire@gmail.com katrina@gmail.com lemi@hotmail.com alican@yahoo.com mustafa@hotmail.com hasan@gmail.com 98733211 ЕМ3456 Nazire Dubai 98764533 90857464 EM7658 Katrina USA EM1987 Lemi USA 99663425 EM8009 Alican UK 95583372 |EM7698 Mustafa Dubai Jordan 92234452 EM6666 Hasan 92123366 Class Class_id (PK) Class_name Multimedia Credit СOM106 COM206 Database сомз06 Public Speaking 9. Training Table Emp_id (FK) EM8765 Date Class_id (FK) Result 05-May-2010 08-May-2011 08-Feb-2010 08-Feb-2010 соM106 Pass EM3456 COM206 Pass EM7658 сомз06 Fail EM1987 сомзо6 Fail 28-Jan-2011 10-March-2012 EM8009 сомз06 Pass EM7698 сом106 Fail EM8765 08-May-2011 сOM206 Pass Write the appropriate SQL queries a) Get the address of employee whose name is Alican b) Get the name and email of employees located in Dubai. Sort the results in alphabetical order of name. c) Show the count of employees…DESPATCH_LIST Order no Order Cus_name Dave E GONES 6023 6023 6028 6152 6152 date 28 Feb 28 Feb 02 Mar 04 Mar Dave E GONES Nick F JENS CHENG Chui 04 Mar CHENG Chui Order amount $1,248 $1,248 $1,290 $999 $999 Prod no EH06 DL21 EH06 SM17 SP38 Description Electric heater Desk lamp Electric heater Quantity Depot no Location 3 • Depot no determines Location (Order_no, Prod_no) determines Quantity, Depot_no 3 6 Sewing machine 1 Sewing pack 10 where, (Order_no, Prod_no) is the Primary Key • Order_no determines Order_date, Cus_name, Order_amount • Prod_no determines Description N24 K10 НОЗ N24 K10 Shatin Hunghom Aberdeen Shatin Hunghom Q4.Draw the dependency diagram for the DESPATCH_LIST table above. Identify all dependencies including functional, partial and transitive dependencies.TABLE 4-6 Parking Tickets at Millennium College Parking Ticket Table St ID L Name F Name Phone No St Lic Lic No Ticket # Date Code Fine 38249 Brown Thomas 111-7804 FL BRY 123 15634 10/17/12 2 $25 16017 11/13/12 1 $15 82453 Green Sally 391-1689 AL TRE 141 14987 10/05/12 $100 16293 11/18/12 1 $15 17892 12/13/12 2 $25 1. Convert to INF Relation 2. Draw dependency diagram showing all functional dependencies 3. Identify anomalies 4. Convert to 3NF Relations 5. Develop EER Diagram with appropriate cardinalities